Oracle 优化篇+STS+输入源(5/5)STS

说明：本文为STS（SQL Tuning Set）使用参考手册
用途：本文仅供初学者熟悉了解SQL Tuning Set或优化参考
标签：SQL Tuning Set、STS、STS使用方法、Oracle优化、SQL调优、STS架构图
温馨提示：如果您发现本文哪里写的有问题或者有更好的写法请留言或私信我进行修改优化
高级内容：由于篇幅原因，部分高级内容和详情没有在此展现，如有需要可以留言或私信

★ 相关文章（※ STS的5种输入源）
    ①Cursor Cache
    ②AWR（Automatic Workload Repository）
    ③SQL Trace
    ④SQLPA（SQL Performance Analyzer Task）
    ⑤STS（SQL Tuning Set）​​​​​​​

★ 知识点
※ 名词解释：SQL调优集(STS)是一个数据库对象，可以用作调优工具的输入。
※ STS包括以下组件：
    ①一组SQL语句
    ②关联的执行上下文，例如用户架构，应用程序模块名称和操作，绑定值列表以及游标的 SQL编译环境
    ③相关的基本执行统计信息，例如经过时间，CPU时间，缓冲区获取，磁盘读取，已处理的行，游标提取，执行次数，完整执行次数，优化器成本和命令类型
    ④每个SQL语句的关联执行计划和行源统计信息（可选）

★ STS架构图

---

★ 输入源E【SQL Tuning Set】 → dbms_sqltune.select_sqlset
※ 温馨提示以下所有操作均在scott用户下执行（请临时给scott授予DBA角色）

--模拟DML操作产生CURSOR_CACHE
sqlplus scott/tiger
set line 170
set pages 200
select * from scott.emp;
select * from scott.dept;
select * from scott.salgrade;

--删除STS
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(sqlset_name  => 'ZZT_SQL_TUNING_SET_OLD',
                           sqlset_owner => 'SCOTT');
END;
/

--创建STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name  => 'ZZT_SQL_TUNING_SET_OLD', 
    sqlset_owner => 'SCOTT',
    description  => 'test');
END;
/

--查看STS
select * from dba_sqlset;

--加载符合条件的SQL到STS
--从18C开始STS系统包发生了变化DBMS_SQLTUNE→DBMS_SQLSET
DECLARE
  zzt_cur_sqlarea DBMS_SQLTUNE.SQLSET_CURSOR; --定义游标参数
  --zzt_cur_sqlarea sys_refcursor;    --也可以直接使用系统游标
BEGIN
  OPEN zzt_cur_sqlarea FOR --将符合条件的SQL加载到游标中，然后放入STS
    SELECT VALUE(p)
      FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(basic_filter   => 'parsing_schema_name = ''SCOTT''',
                                                  attribute_list => 'all')) p;
  -- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name       => 'ZZT_SQL_TUNING_SET_OLD',
                           populate_cursor   => zzt_cur_sqlarea,
                           sqlset_owner      => 'SCOTT',
                           load_option       => 'INSERT', 
                           update_option     => 'REPLACE',
                           update_condition  => 'new.executions >= old.executions',
                           update_attributes => 'ALL',
                           ignore_null       => TRUE,
                           commit_rows       => NULL);
END;
/

--读取最新的STS详情
--COLUMN SQL_TEXT FORMAT a30   
--COLUMN SCH FORMAT a3
--COLUMN ELAPSED FORMAT 999999999
SELECT *
  FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name  => 'ZZT_SQL_TUNING_SET_OLD',
                                        sqlset_owner => 'SCOTT'))
 where lower(SQL_TEXT) like 'select * from scott.%'
 order by FORCE_MATCHING_SIGNATURE;

--删除STS
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(sqlset_name  => 'ZZT_SQL_TUNING_SET_NEW',
                           sqlset_owner => 'SCOTT');
END;
/

--创建STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name  => 'ZZT_SQL_TUNING_SET_NEW', 
    sqlset_owner => 'SCOTT',
    description  => 'test');
END;
/

--查看STS
select * from dba_sqlset;

--加载符合条件的SQL到STS
--从18C开始STS系统包发生了变化DBMS_SQLTUNE→DBMS_SQLSET
DECLARE
  zzt_cur_sqlarea DBMS_SQLTUNE.SQLSET_CURSOR; --定义游标参数
  --zzt_cur_sqlarea sys_refcursor;    --也可以直接使用系统游标
BEGIN
  OPEN zzt_cur_sqlarea FOR --将符合条件的SQL加载到游标中，然后放入STS
    SELECT VALUE(p)
      FROM TABLE(DBMS_SQLTUNE.select_sqlset(
                        sqlset_name       => 'ZZT_SQL_TUNING_SET_OLD',
                        basic_filter      => 'parsing_schema_name = ''SCOTT''',
                        object_filter     => null,
                        ranking_measure1  => 'elapsed_time',
                        ranking_measure2  => 'cpu_time',
                        result_percentage => 1,
                        result_limit      => 9,
                        attribute_list    => 'all',
                        plan_filter       => null,
                        sqlset_owner      => 'scott',
                        recursive_sql     => 'HAS_RECURSIVE_SQL'
                )) p;
  -- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name       => 'ZZT_SQL_TUNING_SET_NEW',
                           populate_cursor   => zzt_cur_sqlarea,
                           sqlset_owner      => 'SCOTT',
                           load_option       => 'INSERT', 
                           update_option     => 'REPLACE',
                           update_condition  => 'new.executions >= old.executions',
                           update_attributes => 'ALL',
                           ignore_null       => TRUE,
                           commit_rows       => NULL);
END;
/

--读取最新的STS详情
--COLUMN SQL_TEXT FORMAT a30   
--COLUMN SCH FORMAT a3
--COLUMN ELAPSED FORMAT 999999999
SELECT *
  FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name  => 'ZZT_SQL_TUNING_SET_NEW',
                                        sqlset_owner => 'SCOTT'))
 where lower(SQL_TEXT) like 'select * from scott.%'
 order by FORCE_MATCHING_SIGNATURE;

--查看STS
select * from dba_sqlset;

★ 参考文档
※ Oracle官方文档>>>Capturing Workloads in SQL Tuning Sets
※ lhdz_bj>>>Oracle 11g 中SQL性能优化新特性之SQL性能分析器（SQLPA）

---

※ 如果您觉得文章写的还不错， 别忘了在文末给作者点个赞哦 ~

over