Oracle的STS(SQL Tuning Set)是一个种用于保存需要优化的SQL和相关信息的数据库对象。这里介绍一下它的使用方法。
01
—
管理STS
创建一个新的STS，注意执行之前要先向用户赋予ADMINISTER SQL TUNING SET权限，如果不记得这个权限可以在视图dba_sys_privs中查询。
代码语言：javascript复制SQL> grant ADMINISTER SQL TUNING SET to tpcc;
Grant succeeded.
BEGIN
DBMS_SQLSET.CREATE_SQLSET (
sqlset_name => 'SQLT_WKLD_STS'
, description => 'STS to store SQL from the private SQL area'
);
END;
/检查已经创建好的STS。
代码语言：javascript复制set linesize 200
COLUMN NAME FORMAT a20
COLUMN COUNT FORMAT 99999
COLUMN DESCRIPTION FORMAT a51
SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM USER_SQLSET;
NAME SQLCNT DESCRIPTION
-------------------- ---------- ---------------------------------------------------
SQLT_WKLD_STS 0 STS to store SQL from the private SQL area
现在可以向这个STS中导入需要优化的SQL，在导入之前先在视图V$SQL中查询将会导入的SQL，注意需要将角色SELECT_CATALOG_ROLE赋予用户。
代码语言：javascript复制SQL> grant SELECT_CATALOG_ROLE to tpcc;
select sql_text,elapsed_time,buffer_gets,module from v$sql where parsing_schema_name = 'TPCC';期中elapsed_time的单位是微秒 (microseconds) ，也就是 一百万分之一秒。
也可以使用DBMS_SQLSET.SELECT_CURSOR_CACHE查询将会导入的SQL。
代码语言：javascript复制SELECT sql_text,elapsed_time,buffer_gets,module FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE(' parsing_schema_name = ''TPCC'' '));对于已经导入到STS中的SQL，可以使用DBMS_SQLTUNE.SELECT_SQLSET进行查询，例如：
代码语言：javascript复制COLUMN SQL_TEXT FORMAT a30
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999
SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) );将TPCC用户执行的SQL导入到STS中：
代码语言：javascript复制DECLARE
c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
OPEN c_sqlarea_cursor FOR
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLSET.SELECT_CURSOR_CACHE(
' parsing_schema_name = ''TPCC'' ')
) p;
-- load the tuning set
DBMS_SQLSET.LOAD_SQLSET (
sqlset_name => 'SQLT_WKLD_STS'
, populate_cursor => c_sqlarea_cursor
);
END;
/
完成后再次查询STS，发现里面有52条记录
代码语言：javascript复制SQL> SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM USER_SQLSET;
NAME SQLCNT DESCRIPTION
-------------------- ---------- ---------------------------------------------------
SQLT_WKLD_STS 52 STS to store SQL from the private SQL area
02
—
传输STS
有时我们需要在另外一个数据库上对STS进行优化，比较常见的是在生产库上收集需要优化的SQL，然后再测试库上进行优化，这时就要用到传输STS。传输STS有以下步骤：
- 在生产数据库中，使用DBMS_SQLTUNE.PACK_STGTAB_SQLSET或DBMS_SQLSET.PACK_STGTAB将STS打包到一个临时表中。
- 使用Oracle Data Pump将STS从临时表导出到一个.dmp文件。
- 使用传输工具（如ftp）将.dmp文件从生产主机传输到测试主机。
- 在测试数据库中，使用Oracle Data Pump从.dmp文件导入STS到一个临时表中。
- 使用DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET或DBMS_SQLSET.UNPACK_STGTAB从临时表中解包STS。
创建一个stage table做为临时表。
代码语言：javascript复制BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
table_name => 'my_10g_staging_table'
, schema_name => 'tpcc'
, db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
);
END;
/
使用存储过程PACK_STGTAB_SQLSET将STS中的SQL导入到stage table中。
代码语言：javascript复制BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'SQLT_WKLD_STS',
staging_table_name => 'my_10g_staging_table',
db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
);
END;
/
将stage table中的数据导出：
代码语言：javascript复制$ expdp tpcc/tpcc@pdb1 DIRECTORY=DATA_PUMP_DIR DUMPFILE=sts.dmp TABLES=my_10g_staging_table
...
/u01/app/oracle/admin/orcl/dpdump/B048E106237F5A41E055655E831F9BAC/sts.dmp
Job "TPCC"."SYS_EXPORT_TABLE_01" successfully completed at Wed Sep 6 14:59:10 2023 elapsed 0 00:00:51
将导出的文件拷贝到另外一个数据库的DATA_PUMP_DIR目录：
代码语言：javascript复制cp /u01/app/oracle/admin/orcl/dpdump/B048E106237F5A41E055655E831F9BAC/sts.dmp /u01/app/oracle/admin/small/dpdump/03F9F6DBB7925A5AE063B257A8C0F566再将stage table中的数据导入到新的数据库中：
代码语言：javascript复制impdp tpcc/tpcc@pdba DIRECTORY=DATA_PUMP_DIR DUMPFILE=sts.dmp TABLES=my_10g_staging_table
使用DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET把从stage table中将SQL导入到STS中：
代码语言：javascript复制$ sql tpcc/tpcc@pdba
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
sqlset_name => 'SQLT_WKLD_STS',
replace => false,
staging_table_name => 'my_10g_staging_table');
END;
/
PL/SQL procedure successfully completed.03
—
在优化任务中使用STS
基于一个STS创建SQL优化任务
代码语言：javascript复制VARIABLE sts_task VARCHAR2(64);
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sqlset_name => 'SQLT_WKLD_STS', -
rank1 => 'BUFFER_GETS', -
time_limit => 3600, -
description => 'tune my workload ordered by buffer gets');
执行这个SQL优化任务：
代码语言：javascript复制BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>:sts_task);
END;
/
SQL> PRINT :sts_task
STS_TASK
----------------------------------------------------------------------------------------------------
TASK_1507
检查任务的当前状态
代码语言：javascript复制 select * from user_advisor_tasks where task_name='TASK_1507';
查看任务报告
代码语言：javascript复制SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task)
FROM DUAL;注意这个优化任务要用到数据库中的对象，也就是说测试环境的数据要和生产环境的数据尽量一致，不然会出现下面的错误。
代码语言：javascript复制- ORA-00942: table or view does not exist04
—
删除STS
当STS不需要时，可以使用DBMS_SQLSET.DROP_SQLSET删除STS。
代码语言：javascript复制BEGIN
DBMS_SQLSET.DROP_SQLSET( sqlset_name => 'SQLT_WKLD_STS' );
END;
/
BEGIN
*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "SQLT_WKLD_STS" owned by user "SYS" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14910
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 17745
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14881
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7315
ORA-06512: at "SYS.DBMS_SQLSET", line 50
ORA-06512: at line 2
但是遇到了错误，因为这个STS有关联的任务，检查一下：
代码语言：javascript复制select description, created, owner
from DBA_SQLSET_REFERENCES
where sqlset_name = 'SQLT_WKLD_STS';
DESCRIPTION CREATED OWNER
--------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
created by: SQL Tuning Advisor - task: TASK_1507 2023-09-05 SYS
先删除相关联的SQL优化任务：
代码语言：javascript复制exec DBMS_SQLTUNE.DROP_TUNING_TASK('TASK_1507');再次删除STS，执行成功
代码语言：javascript复制BEGIN
DBMS_SQLSET.DROP_SQLSET( sqlset_name => 'SQLT_WKLD_STS' );
END;
4 /
PL/SQL procedure successfully completed.
