说明：本文为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架构图
★ 输入源C【SQL Trace】 → dbms_sqltune.select_sql_trace
※ 温馨提示以下所有操作均在scott用户下执行（请临时给scott授予DBA角色）
--开启event事件trace sql
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
select * from scott.emp;
select * from scott.dept;
select * from scott.salgrade;
--关闭event事件trace sql
alter session set events '10046 trace name context off' ;
--查看当前event生成的trc文件（适用于pl/sql）
select db_name.value || '_ora_' || v$process.spid ||
nvl2(v$process.traceid, '_' || v$process.traceid, null) || '.trc' "Trace File"
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where u_dump.name = 'user_dump_dest'
and db_name.name = 'db_name'
and v$session.audsid = sys_context('userenv', 'sessionid');
--创建目录
select * from dba_directories;
drop directory zzt_sql_tuning_set_dir;
create directory zzt_sql_tuning_set_dir as 'D:\app\zzt\virtual\diag\rdbms\win\win\trace\';
grant read,write on directory zzt_sql_tuning_set_dir to scott;
--创建映射表（从捕获数据库创建映射表，以存储trace SQL）
CREATE TABLE scott.mapping AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
FROM dba_objects
WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
'LOB', 'OPERATOR', 'PACKAGE',
'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
'RESOURCE PLAN', 'TRIGGER', 'TYPE',
'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
FROM dba_users;
--删除STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'ZZT_SQL_TUNING_SET',
sqlset_owner => 'SCOTT' );
END;
/
--创建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'ZZT_SQL_TUNING_SET',
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_SQL_TRACE(directory => 'ZZT_SQL_TUNING_SET_DIR',
file_name => '%.trc',
mapping_table_name => 'mapping',
mapping_table_owner => 'scott'
)) p;
-- load the tuning set
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'ZZT_SQL_TUNING_SET',
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',
sqlset_owner => 'SCOTT'))
where lower(SQL_TEXT) like 'select * from scott.%'
order by FORCE_MATCHING_SIGNATURE;
--输出样例
SQL_ID FORCE_MATCHING_SIGNATURE SQL_TEXT OBJECT_LIST BIND_DATA PARSING_SCHEMA_NAME MODULE ACTION ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV PRIORITY COMMAND_TYPE FIRST_LOAD_TIME STAT_PERIOD ACTIVE_STAT_PERIOD OTHER PLAN_HASH_VALUE SQL_PLAN BIND_LIST CON_DBID LAST_EXEC_START_TIME
------------- ------------------------ -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- --------------- -------- --------- ---------- --------------------
c9znpgntj9uf5 0 select * from scott.salgrade <Object> SCOTT 60 0 8 0 5 2 1 3 4294967295 <Object> <Object> 1400682370
f6hhpzwv5jrna 0 select * from scott.dept <Object> SCOTT 70 0 8 0 4 2 1 3 4294967295 <Object> <Object> 1400682370
ggqns3c1jz86c 0 select * from scott.emp <Object> SCOTT 60 0 8 0 14 2 1 3 4294967295 <Object> <Object> 1400682370
--清理环境
--删除STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'ZZT_SQL_TUNING_SET',
sqlset_owner => 'SCOTT' );
END;
/
--删除目录
drop directory zzt_sql_tuning_set_dir;
--删除表
drop table scott.mapping;
★ 参考文档
※ Oracle官方文档>>>Capturing Workloads in SQL Tuning Sets
※ lhdz_bj>>>Oracle 11g 中SQL性能优化新特性之SQL性能分析器（SQLPA）
※ 如果您觉得文章写的还不错， 别忘了在文末给作者点个赞哦 ~
over
493
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
