Oracle SQL TUNING SET FROM AWR

我们现在测试 从AWR导入


DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
  begin_snap        IN NUMBER,
  end_snap          IN NUMBER,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL,
  attribute_list    IN VARCHAR2 := NULL,
  recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
 RETURN sys.sqlset PIPELINED;


DBMS_SQLTUNE.SELECT_WORKLOAD REPOSITORY (
  baseline_name     IN VARCHAR2,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL,
  attribute_list    IN VARCHAR2 := NULL,
  recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
 RETURN sys.sqlset PIPELINED;


 
上面是函数定义


先查看一下 snap 14219 到 14220 的内容信息


SQL> SELECT sql_id, sql_text
  2  ,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_name
  3  FROM table(
  4  DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
  5   begin_snap => 14219
  6  ,end_snap => 14220
  7  ,basic_filter => 'parsing_schema_name <> ''SYS'''
  8  ,ranking_measure1 => 'disk_reads'
  9  ,result_limit => 10
 10  ));


SQL_ID        SQL_TEXT                                                                         DISK_READS CPU_SECONDS ELAPSED_TIME BUFFER_GETS PARSING_SCHEMA_NAME
------------- -------------------------------------------------------------------------------- ---------- ----------- ------------ ----------- ------------------------------
cw57wyqzxtk90 select sysdate + 0.05/24 from dual                                                        0        1000         1578      0 TDS_HQ
cq2x4ny79m0r6 INSERT INTO DCRMC04D1 SELECT * FROM DCRMC04D1_H WHERE CBIZTYPE = '2'                      0    11355268     11367954 4






1.创建一个 SQL TUNING SET


BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'awrdisktop10', 
    sqlset_owner => 'SYS',
    description  => 'awrdisktop10_14219_14220');
END;
/




2.从awr 加载


DECLARE
  awr_cur dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN awr_cur FOR
    SELECT value(x)
    FROM table(dbms_sqltune.select_workload_repository(
      14219,14220, null, null,'disk_reads',
      null, null, null, 10)) x;
  --
  dbms_sqltune.load_sqlset(
   sqlset_owner =>'SYS',
    sqlset_name => 'awrdisktop10',
    populate_cursor => awr_cur);
END;
/


3.查看 SQL TUNING SET 内容


SQL> SELECT sqlset_name, elapsed_time,cpu_time, buffer_gets, disk_reads, sql_text FROM dba_sqlset_statements WHERE sqlset_name = 'awrdisktop10';


SQLSET_NAME                    ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS SQL_TEXT
------------------------------ ------------ ---------- ----------- ---------- --------------------------------------------------------------------------------
awrdisktop10                           5597       7000         530          1 insert into wrm$_snapshot_details     (snap_id, dbid, instance_number, table_id,
awrdisktop10                           5590       7998          43          0 select count(*) from sys.job$ where next_date < :1 and (field1 = :2 or (field1 =




我们看到 STS FROM AWR 创建成功了