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

最新推荐文章于 2023-03-23 20:23:36 发布

原创

最新推荐文章于 2023-03-23 20:23:36 发布 · 508 阅读

1 ·

CC 4.0 BY-SA版权

本文作者：zzt_2009

文章标签：

#SQL Tuning Set #STS #SQL调优 #STS架构图

本文详细介绍了SQL调优集(STS)的概念、组成及使用方法，包括STS的创建、加载、读取及如何利用SQL性能分析器(SPA)进行性能对比和优化。适合初学者和Oracle优化人员参考。

说明：本文为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架构图

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

--使用SCOTT用户登录并测试SPA
drop table scott.zzt_sts_t;
create table scott.zzt_sts_t as select * from scott.emp;
--收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'ZZT_STS_T',method_opt=> 'for all columns',estimate_percent => '100',degree=>'2',granularity=>'all',cascade=>TRUE);
--查看STS中SQL的执行计划
set line 170
set pages 200
set serveroutput off
ALTER SESSION SET STATISTICS_LEVEL=ALL;
select count(*) from scott.zzt_sts_t;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

--删除SPA
exec DBMS_SQLPA.drop_ANALYSIS_TASK(task_name => 'ZZT_SPA_TASK');

--删除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; --定义游标参数和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   &nb