************************************************************
1.н¨µ÷Óż¯¶ÔÏó
************************************************************


---ÊÚȨ

grant ADMINISTER ANY SQL TUNING SET  to scott;


---ɾ³ý´æÔÚµÄSTS



BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'OCPYANG_STS'
    );
END;
/


---н¨STS


BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'OCPYANG_STS', 
    sqlset_owner => 'SCOTT',
    description  => 'ocpyangtest');
END;
/


---²é¿´Êý¾Ý¿âÒѾ­´´½¨µÄSQLSET

select owner, name, id, created, statement_count from dba_sqlset;






************************************************************
2.²é¿´AWR×ÊÔ´Ãܼ¯ÐÍSQLÓï¾ä
************************************************************

---2.1²é¿´¿ÉÓõĿìÕÕ·¶Î§

SELECT snap_id, instance_number, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;


---2.2 ²é¿´¿ìÕÕ±àºÅ820-840Ö®¼ä´ÅÅÌʹÓÃÂÊÇ°10µÄsql

SELECT sql_id
,substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(820,841,
            null, null, 'disk_reads',null, null, null, 10))
ORDER BY disk_reads DESC;


---2.3²é¿´Ã»Óб»sysÓû§½âÎöµÄsql

SELECT sql_id, substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(820,841,
'parsing_schema_name <> ''SYS''',
NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));


---2.4 ²é¿´¿ìÕÕ±àºÅ820-840Ö®¼ä·ÇsqlÓû§ÅÅÐòµÄÇ°10µÄsql
SELECT sql_id, substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
 begin_snap => 820
,end_snap => 841
,basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'buffer_gets'
,result_limit => 10
));



COL bsnap NEW_VALUE begin_snap
COL esnap NEW_VALUE end_snap
--
SELECT MAX(snap_id) bsnap
FROM dba_hist_snapshot
WHERE begin_interval_time < sysdate-7;
--
SELECT MAX(snap_id) esnap
FROM dba_hist_snapshot;


--
COL sql_text            FORMAT A40
COL sql_id              FORMAT A15
COL parsing_schema_name FORMAT A15
COL cpu_seconds         FORMAT 999,999,999,999,999
SET LONG 10000 LINES 132 PAGES 100 TRIMSPOOL ON
--


SELECT sql_id, sql_text
,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
 begin_snap => &begin_snap
,end_snap => &end_snap
,basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));



************************************************************
3.ʹÓÃAWRÖиß×ÊÔ´ÏûºÄµÄSQLÀ´Ìî³äÓÅ»¯¼¯:
************************************************************





---3.1н¨STS


BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'OCPYANG_STS', 
    sqlset_owner => 'SCOTT',
    description  => 'ocpyangtest');
END;
/

---3.2²é¿´AWR¿ìÕÕÆðÖ¹

select snap_id, begin_interval_time
from dba_hist_snapshot order by 1;

---3.3ʹÓÃAWRÖиß×ÊÔ´µÄsqlÀ´Ìî³äsqlÓÅ»¯¼¯

DECLARE
  test_cur dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN test_cur FOR
    SELECT value(x)
    FROM table(dbms_sqltune.select_workload_repository(
      820,841, null, null,'disk_reads',
      null, null, null, 15)) x;
  --
  dbms_sqltune.load_sqlset(
   sqlset_owner =>'SCOTT',
    sqlset_name => 'OCPYANG_STS',
    populate_cursor => test_cur);
END;
/

*------------------³£¼û´íÎó

µÚ 1 ÐгöÏÖ´íÎó:
ORA-13774: ȨÏÞ²»×ã, ÎÞ·¨´Ó¹¤×÷Á¿×ÊÁϵµ°¸¿âÖÐÑ¡ÔñÊý¾Ý ORA-06512: ÔÚ
"SYS.DBMS_SQLTUNE", line 4715
ORA-06512: ÔÚ line 10

ʹÓÃsysÕË»§¼´¿É£¬ÔÚDBMS_SQLTUNE.LOAD_SQLSETÖ¸¶¨sqlset_owner


DBMS_SQLTUNE.LOAD_SQLSET (
   sqlset_name       IN  VARCHAR2,
   populate_cursor   IN  sqlset_cursor,
   load_option       IN VARCHAR2 := 'INSERT', 
   update_option     IN VARCHAR2 := 'REPLACE', 
   update_condition  IN VARCHAR2 :=  NULL,
   update_attributes IN VARCHAR2 :=  NULL,
   ignore_null       IN BOOLEAN  :=  TRUE,
   commit_rows       IN POSITIVE :=  NULL,
   sqlset_owner      IN VARCHAR2 := NULL);
--------------------