Oracle 19c OCM授课笔记：STS(SQL Tuning Set)的使用

oracleace 2023-09-08

655

关于号主，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云最有价值专家
《MySQL 8.0运维与优化》的作者
拥有 Oracle 10g、12c和19c OCM等数十项数据库认证
曾任IBM公司数据库部门经理
20+年DBA经验，服务2万+客户
精通C和Java，发明两项计算机专利

Oracle的STS(SQL Tuning Set)是一个种用于保存需要优化的SQL和相关信息的数据库对象。这里介绍一下它的使用方法。这里的内容是姚远老师讲授Oracle OCM课程的讲义摘选。

—

管理STS

创建一个新的STS，注意执行之前要先向用户赋予ADMINISTER SQL TUNING SET权限，如果不记得这个权限可以在视图dba_sys_privs中查询。

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。

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赋予用户。

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。

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进行查询，例如：

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中：

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条记录

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

—

传输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做为临时表。

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中。

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中的数据导出：

$ 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目录：

cp  u01/app/oracle/admin/orcl/dpdump/B048E106237F5A41E055655E831F9BAC/sts.dmp u01/app/oracle/admin/small/dpdump/03F9F6DBB7925A5AE063B257A8C0F566

再将stage table中的数据导入到新的数据库中：

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中：

$ 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.

—

在优化任务中使用STS

基于一个STS创建SQL优化任务

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优化任务：

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>:sts_task);
END;
/


SQL>   PRINT :sts_task


STS_TASK
----------------------------------------------------------------------------------------------------
TASK_1507

检查任务的当前状态

 select * from user_advisor_tasks where task_name='TASK_1507';

查看任务报告

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task)
FROM   DUAL;

注意这个优化任务要用到数据库中的对象，也就是说测试环境的数据要和生产环境的数据尽量一致，不然会出现下面的错误。

- ORA-00942: table or view does not exist

—

删除STS

当STS不需要时，可以使用DBMS_SQLSET.DROP_SQLSET删除STS。

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有关联的任务，检查一下：

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优化任务：

exec DBMS_SQLTUNE.DROP_TUNING_TASK('TASK_1507');

再次删除STS，执行成功

BEGIN
  DBMS_SQLSET.DROP_SQLSET( sqlset_name => 'SQLT_WKLD_STS' );
END;
  4     


PL/SQL procedure successfully completed.

欢迎加我的微信，拉你进数据库微信群👇

推荐文章👇

试看《MySQL 8.0运维与优化》（清华大学出版社）

托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

最后修改时间：2023-09-19 14:27:31

文章转载自oracleace，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 19c OCM授课笔记：STS(SQL Tuning Set)的使用

传输STS

在优化任务中使用STS

删除STS

评论