首页 > 代码库 > [terry笔记]Oracle SQL 优化之sql tuning advisor (STA)
[terry笔记]Oracle SQL 优化之sql tuning advisor (STA)
前言：经常可以碰到优化sql的需求，开发人员直接扔过来一个SQL让DBA优化，然后怎么办？
当然，经验丰富的DBA可以从各种方向下手，有时通过建立正确索引即可获得很好的优化效果，但是那些复杂SQL错综复杂的表关联，却让DBA们满头大汗。
如下特别介绍一种oracle官方提供的科学优化方法STA，经过实践，不敢说此特性绝对有效，但是可以开阔思路，并且从中学到许多知识，不再用“猜”的方式去创建索引了。
SQL优化器SQL Tuning Advisor （STA)，是oracle的sql优化补助工具。
其实优化sql主要有两个方案，其一是改写sql本身，改写sql需要对sql语法、数据库的执行方式都要有较好地理解。
其二就是这个STA，它属于DBMS_SQLTUNE包，它的主要作用是对于sql使用到的表创建正确的索引。
使用STA前提：
要保证优化器是CBO模式下。show parameter OPTIMIZER_MODEall_rows /*CBO，sql所有返回行都采用基于成本的方式运行*/first_rows /*CBO，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行*/first_rows_n /*CBO，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录*/choose /*如果有统计信息，采用CBO，否则采用RBO*/rule /*RBO*/执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：grant advisor to scott;
如下是STA使用例子：
1.首先创建两个练习表obj与ind，仅创建表，无需创建索引：
SQL> create table obj as select * from dba_objects;表已创建。SQL> create table ind as select * from dba_indexes;表已创建。SQL> insert into obj select * from obj;已创建 74603 行。SQL> insert into obj select * from obj;已创建 149206 行。SQL> insert into obj select * from obj;已创建 298412 行。SQL> insert into ind select * from ind;已创建 5134 行。SQL> insert into ind select * from ind;已创建 10268 行。SQL> insert into ind select * from ind;已创建 20536 行。
2.然后对这两个表，obj与ind进行联合查询，并通过autotrace查看其执行计划：
SQL> set timing onSQL> set autot traceSQL> select count(*) from obj o, ind i where o.object_name=i.index_name;已用时间: 00: 00: 00.15执行计划----------------------------------------------------------Plan hash value: 380737209------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 83 | | 5063 (1)| 00:01:01 || 1 | SORT AGGREGATE | | 1 | 83 | | | ||* 2 | HASH JOIN | | 5861K| 463M| 1272K| 5063 (1)| 00:01:01 || 3 | TABLE ACCESS FULL| IND | 44789 | 743K| | 379 (1)| 00:00:05 || 4 | TABLE ACCESS FULL| OBJ | 577K| 36M| | 2472 (1)| 00:00:30 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 9 recursive calls 4 db block gets 10406 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
通过执行计划，可以清晰的看到，在执行以上两个表的联合查询的时候，两张表走的全表扫和hash join。
正式使用STA进行优化：
第一步：创建优化任务
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务，调用存储过程DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行该任务：
SQL> set autot offSQL> set timing offDECLAREmy_task_name VARCHAR2(30);my_sqltext CLOB;BEGINmy_sqltext := ‘select count(*) from obj o, ind i where o.object_name=i.index_name‘;my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,user_name => ‘SCOTT‘, scope => ‘COMPREHENSIVE‘,time_limit => 30,task_name => ‘tuning_sql_test‘,description => ‘tuning‘);DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_sql_test‘);END;/PL/SQL 过程已成功完成。
如下是参数解释：
函数CREATE_TUNING_TASK，
sql_text是需要优化的语句，
user_name是该语句通过哪个用户执行，用户名大写，
scope是优化范围（limited或comprehensive），
time_limit优化过程的时间限制，
task_name优化任务名称，
description优化任务描述。
第二步： 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task(‘tuning_sql_test‘);PL/SQL 过程已成功完成。
第三步：检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =‘tuning_sql_test‘;TASK_NAME STATUS------------------------------ -----------tuning_sql_test COMPLETED
第四步：查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
SQL> set long 999999SQL> set serveroutput on size 999999SQL> set line 120SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_sql_test‘) from dual;
如下是显示优化的结果：
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_SQL_TEST‘)--------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : tuning_sql_testTuning Task Owner : SCOTTWorkload Type : Single SQL StatementExecution Count : 2Current Execution : EXEC_112Execution Type : TUNE SQLScope : COMPREHENSIVETime Limit(seconds): 30Completion Status : COMPLETEDStarted at : 08/29/2013 11:10:10Completed at : 08/29/2013 11:10:12-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID : 6wruu2mxyu8g3SQL Text : select count(*) from obj o, ind i where o.object_name=i.index_name-------------------------------------------------------------------------------FINDINGS SECTION (3 findings)-------------------------------------------------------------------------------1- Statistics Finding--------------------- 尚未分析表 "SCOTT"."IND"。 Recommendation -------------- - 考虑收集此表的优化程序统计信息。 execute dbms_stats.gather_table_stats(ownname => ‘SCOTT‘, tabname => ‘IND‘, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO‘); Rationale --------- 为了选择好的执行计划, 优化程序需要此表的最新统计信息。2- Statistics Finding--------------------- 尚未分析表 "SCOTT"."OBJ"。 Recommendation -------------- - 考虑收集此表的优化程序统计信息。 execute dbms_stats.gather_table_stats(ownname => ‘SCOTT‘, tabname => ‘OBJ‘, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO‘); Rationale --------- 为了选择好的执行计划, 优化程序需要此表的最新统计信息。3- Index Finding (see explain plans section below)-------------------------------------------------- 通过创建一个或多个索引可以改进此语句的执行计划。 Recommendation (estimated benefit: 75.74%) ------------------------------------------ - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 create index SCOTT.IDX$$_00790001 on SCOTT.OBJ("OBJECT_NAME"); - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 create index SCOTT.IDX$$_00790002 on SCOTT.IND("INDEX_NAME"); Rationale --------- 创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导" 可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 380737209------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time|------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 83 | | 5063 (1)| 00:01:01 || 1 | SORT AGGREGATE | | 1 | 83 | | | ||* 2 | HASH JOIN | | 5861K| 463M| 1272K| 5063 (1)| 00:01:01 || 3 | TABLE ACCESS FULL| IND | 44789 | 743K| | 379 (1)| 00:00:05 || 4 | TABLE ACCESS FULL| OBJ | 577K| 36M| | 2472 (1)| 00:00:30 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")2- Using New Indices--------------------Plan hash value: 4048334321--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 83 | | 1228 (2)| 00:00:15 || 1 | SORT AGGREGATE | | 1 | 83 | | ||| 2 | MERGE JOIN | | 5861K| 463M| | 1228 (2)| 00:00:15 || 3 | INDEX FULL SCAN | IDX$$_00790001 | 577K| 36M| | 944 (1)| 00:00:12 ||* 4 | SORT JOIN | | 44789 | 743K| 2120K| 268 (1)| 00:00:04 || 5 | INDEX FAST FULL SCAN| IDX$$_00790002 | 44789 | 743K| | 18 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("O"."OBJECT_NAME"="I"."INDEX_NAME") filter("O"."OBJECT_NAME"="I"."INDEX_NAME")
报告如上，
仔细阅读此报告，主要给出了两种建议：1.收集两张表obj与ind的统计信息；2.在这两张表上面创建相应索引。
并且特别贴心的把语句都给了出来，完全可以拿去直接运行，
更加贴心的是，报告中把优化前后的预计效果都展示了出来，完全一目了然，更加方便了DBA考虑是否进行建议优化方案。
五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务，可以释放资源。
SQL>exec dbms_sqltune.drop_tuning_task(‘tuning_sql_test‘);
结语：如上就是SQL优化器SQL Tuning Advisor （STA）的使用方法，可能语法有些复杂，但是确实给优化的工作带来了很大帮助，经常使用多加练习即可熟练。
[terry笔记]Oracle SQL 优化之sql tuning advisor (STA)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。