调优集对象

************************************************************
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 查看快照编号1404-1471之间磁盘使用率前10的sql
 
SELECT sql_id
,substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1404,1471,
            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(1404,1471,
'parsing_schema_name <> ''SYS''',
NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));
 
 
---2.4 查看快照编号1404-1471之间非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 => 1404
,end_snap => 1471
,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(
      1404,1471, 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);
-----------------------------*
 
 
---3.4 查看优化集相信信息
SELECT sqlset_name, elapsed_time
,cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'OCPYANG_STS';
 
 
 
 
 
************************************************************
4.查看内存中资源密集型的sql
************************************************************
---4.1 语法
DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
  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)
 RETURN sys.sqlset PIPELINED;
 
---4.2 从内存中选择读取磁盘超过1000000
 
 SELECT sql_id, substr(sql_text,1,20), disk_reads
,cpu_time, elapsed_time
,buffer_gets, parsing_schema_name
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads>1000000')
)
order by sql_id;
 
 
---4.3 查看内存中非sys账户用户CPU时间最长的10个查询
 
SELECT sql_id, substr(sql_text,1,120), disk_reads
,cpu_time, elapsed_time
,buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
 basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));
 
 
---4.4 查看内存中非sys账户运行返回时间超过1秒的
SELECT sql_id, substr(sql_text,1,120)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''
                                             AND elapsed_time > 1000000'))
ORDER BY sql_id;
 
---4.5 查看具体sql_id执行细节
 
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''a4ct9tx8f9d4a'''));
 
 
----4.6 各种案例
 
-- Select all statements in the cursor cache.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
END;/
 
 
-- Look for statements not parsed by SYS.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur for
    SELECT VALUE(P)
    FROM table(
     DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
end;/
 
 
-- All statements from a particular module/action.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
         'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;/
 
 
-- all statements that ran for at least five seconds
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
end;/
 
 
-- select all statements that pass a simple buffer_gets threshold and
-- are coming from an APPS user
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
        'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
end;/
 
 
-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(dbms_sqltune.select_cursor_cache(
      'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
      'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;/
 
 
-- Select the top 100 statements in the cursor cache ordering by elapsed_time.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
                                                NULL,
                                                'ELAPSED_TIME', NULL, NULL,
                                                1,
                                                100)) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
end;/
 
 
-- Select the set of statements which cumulatively account for 90% of the
-- buffer gets in the cursor cache.  This means that the buffer gets of all
-- of these statements added up is approximately 90% of the sum of all
-- statements currently in the cache.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
                                                NULL,
                                                'BUFFER_GETS', NULL, NULL,
                                                .9)) P;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
END;
/
 
 
 
************************************************************
5.用内存中高资源消耗的sql填充调优集
************************************************************
 
 
---5.0 删除存在的STS
 
 
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'OCPYANG_STS'
    );
END;
/
 
--5.1新建调优集
 
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'OCPYANG_STS',
    sqlset_owner => 'SCOTT',
    description  => 'ocpyangtest');
END;
/
 
---5.2  通过游标缓存从内存中读取sql填充
 
DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
  SELECT VALUE(x)
  FROM table(
  DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
  'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000',
  NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;
--
  DBMS_SQLTUNE.LOAD_SQLSET(
   sqlset_owner =>'SCOTT',
  sqlset_name => 'OCPYANG_STS',
    populate_cursor => cur);
END;
/
 
/**********常见错误
 
第 1 行出现错误:
ORA-13761: 过滤器无效
ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 4715
ORA-06512: 在 line 11
 
使用SYS账户执行即可.
 
************************************/
 
SELECT sqlset_name, elapsed_time
,cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'OCPYANG_STS';
 
 
 
---5.3  将内存中指定时间内的所有sql加载
 
 
--语法:
 
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
    sqlset_name         IN VARCHAR2,
    time_limit          IN POSITIVE := 1800,
    repeat_interval     IN POSITIVE := 300,
    capture_option      IN VARCHAR2 := 'MERGE',
    capture_mode        IN NUMBER   := MODE_REPLACE_OLD_STATS,
    basic_filter        IN VARCHAR2 := NULL,
    sqlset_owner        IN VARCHAR2 := NULL);
 
 
 
 
 
BEGIN
  DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
   sqlset_owner =>'SCOTT'
   , sqlset_name     => 'PROD_WORKLOAD'
   ,time_limit      => 3600  --3600秒
   ,repeat_interval => 20);   --每隔20秒
END;
/
 
 
 
 
************************************************************
6.选择性的从sql调优集中删除sql
************************************************************
 
select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;
 
BEGIN
  DBMS_SQLTUNE.DELETE_SQLSET(
    sqlset_owner => 'SCOTT',
    sqlset_name  => 'IO_STS'
   ,basic_filter => 'disk_reads < 2000000');
END;
/
 
 
 
 
************************************************************
7.传输sql调优集-STS
************************************************************
 
 
 
1.新建一个STS
 
---删除存在的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;
/
 
 
---2.加载STS(可以参考STS收集的方法)
 
 
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
    &begin_snap_id,
    &end_snap_id,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'ALL')) p;
    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set',
             populate_cursor => baseline_cursor);
end;
/
 
 
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
   'my_sql_tuning_set',
   '(disk_reads/buffer_gets) >= 0.75'));
 
 
 
---3.新建搜集baseline的表
 
BEGIN
  dbms_spm.create_stgtab_baseline(
    table_name => 'BASELINE_STG01',
    table_owner => 'SCOTT',
    db_version  => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
 
);  --不能新建在SYS账户下
END;
/
 
 
 
----4.把Baseline数据填到表
 
BEGIN
  DBMS_SQLTUNE.pack_stgtab_sqlset(      
    sqlset_name          => 'OCPYANG_STS',     
    sqlset_owner         => 'SCOTT',     
    staging_table_name   => 'BASELINE_STG01',
    staging_schema_owner => 'SCOTT',
    db_version           => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION );
END;
/
 
 
/**********语法
 
 
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
   sqlset_name          IN VARCHAR2,
   sqlset_owner         IN VARCHAR2 := NULL,
   staging_table_name   IN VARCHAR2,
   staging_schema_owner IN VARCHAR2 := NULL,
   db_version           IN NUMBER := NULL);
 
 
***********/
 
 
---5.传递数据到目标服务器
 
使用Oracle Data Pump or database link or expdp等将表BASELINE_STG01
迁移到目标服务器.
 
 
 
---6.目标服务器新建STS
 
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'OCPYANG_STS01',
    sqlset_owner => 'SCOTT',
    description  => 'ocpyangtest');
END;
/
 
 
 
----7.导入数据到目标服务器的STS
 
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
      sqlset_name  => 'OCPYANG_STS01',
      replace  => TRUE,
      staging_table_name => 'BASELINE_STG01');
END;
/
 
 
 
---8.通过SPM BASELINE的包来把SQL调优集里的SQL都批量的生成BASELINE
declare
ret number;
begin
ret := dbms_spm.load_plans_from_sqlset(
    sqlset_name => 'OCPYANG_STS01',
    sqlset_owner => 'SCOTT');
end;
/