利用Oracle 10g SQL优化器优化语句.docx

SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具， 它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。 下面介绍一下它的使用。使用STA 一定要保证优化器是CBO模式下。一、利用STA优化语句对于问题语句的收集，可以通过Oracle10g的另一个自动化工具：数据库自动诊断 监视工具(ADDM)。它的使用可以参照我的另一篇文章Oracle10g数据库自动诊断监视工 具(ADDM )使用指南。我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的，STA是 用起来很简单(只要你会调用存储过程，都能使用这个工具)，三个步骤就可以完成一次语 句调优。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：SYSning>grant advisor to ning;Grant succeeded.使用DBMS_SQLTUNE包进行SQL优化，大致可以分为四个步骤：创建优化任务执行优化任务显示优化任务的结果按照建议执行优化测试环境创建:SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;Table created.SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;Table created.SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);Table altered.SQL> DECLARE2 n NUMBER;3 BEGIN4 FOR n IN 1.100LOOPINSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;COMMIT;8 END LOOP;9 END;PL/SQL procedure successfully completed.这里创建一张大表和一张小表，并且都没有索引，下面执彳丁一个查询:SQL> set timing onSQL> set autot onSQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;Plan hash value: 3089226980I 0 I SELECT STATEMENT |I 11 SORT AGGREGATE |I 1 I 36 I 3550 (2)I 00:00:431I 11 361 I II* 21 HASH JOINI 155KI 5462KI 3550 (2)100:00:431I 31 TABLE ACCESS FULL SMALLTAB | 12231 22014 |11 (0)I 00:00:011I 41 TABLE ACCESS FULLI BIGTAB | 1205KI 20MI 3526 (1)100:00:431Predicate Information (identified by operation id):2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")Statistics0 recursive calls0 db block gets16013 consistent gets14491 physical reads0 redo size412 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed可以看出这个语句执行性能很差：16013 consistent gets。第一步：创建优化任务并执行通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程 EXECUTE_TUNING_TASK 执行该任务：SQL> set autot offSQL> set timing offSQL> DECLARE2 my_task_name VARCHAR2(30);3 my_sqltext CLOB;4 BEGIN5 my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name'6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(7 sql_text => my_sqltext,8 user_name => 'DEMO',9 scope=> 'COMPREHENSIVE',10 time_limit => 60,11 task_name => 'tuning_sql_test',12 description => 'Task to tune a query on a specified table');1314 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');15 END;16 /PL/SQL procedure successfully completed.在函数CREATE_TUNING_TASK, sql_text是需要优化的语句，user_name是该语句通 过哪个用户执行，scope是优化范围(limited或comprehensive), time_limit优化过程的 时间限制，task_name优化任务名称，description优化任务描述。可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务。SQL> select task_name, status from USER_ADVISOR_LOG where task_name='tuning_sql_ test'ITASK_NAMESTATUStuning_sql_testCOMPLETED第二步：查看优化结果通过函数可以查看优化结果。SQL> set long 10000SQL> set longchunksize 1000SQL> set linesize 100SQL> SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')GENERAL INFORMATION SECTION:tuning_sql_test:DEMOTuning Task NameTuning Task OwnerScope: COMPREHENSIVETime Limit(seconds)Completion StatusStarted at:60:COMPLETED:11/30/2005 13:16:43Completed at:11/30/2005 13:16:44Number of Index Findings : 1ISchema Name: DEMOSQL ID : 6p64dnnsqf9pmSQL Text : select count(*) from bigtab a, smalltab b wherea.object_name=b.table_nameFINDINGS SECTION (1 finding)1- Index Finding (see explain plans section below)The execution plan of this statement can be improved by creating one or more indices.IRecommendation (estimated benefit: 100%)-Consider running the Access Advisor to improve the physical schema design or creating the recommended index.create index DEMO.IDX$_06C50001 on DEMO.SMALLTAB('TABLE_NAME');-Consider running the Access Advisor to improve the physical schema design or creating the recommended index.create index DEMO.IDX$_06C50002 on DEMO.BIGTAB('OBJECT_NAME');RationaleCreating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.EXPLAIN PLANS SECTION1- OriginalPlan hash value: 3089226980I Id I Operation / Name / Rows / Bytes / Cost (%CPU)I Time /I 0 I SELECT STATEMENT II 11 SORT AGGREGATE II 1 I 361 3550 (2)100:00:43 /I 11 361/* 2 / HASH JOINI 155KI 5462KI 3550 (2)100:00:431/ 31 TABLE ACCESS FULLI SMALLTAB I 1223122014 I 11 (0)I 00:00:011TABLE ACCESS FULLI BIGTAB I 1205KI 20MI 3526 (1)I 00:00:431Predicate Information (identified by operation id):2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")2- Using New IndicesPlan hash value: 494801882I Id I OperationI NameI Rows I Bytes I Cost (%CPU)I Time II 1 I 361 1108 (3)100:00:141I 1 I 361 I II 0 I SELECT STATEMENT II 11 SORT AGGREGATE II* 21 HASH JOINII 155KI 5462KI 1108 (3)100:00:141I 31 INDEX FAST FULL SCAN IDX$06C500011 1223122014 I 3 (0)I 00:00:011I 4 I INDEX FAST FULL SCANI IDX$_06C500021 1205KI 20MI 1093 (2)I 00:00:14 IPredicate Information (identified by operation id):2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")看一下这个优化建议报告：第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到 的语句等等。第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了 问题描述：可以通过建立更多的所引来提高性能；然后是建议的具体内容：在表smalltab 的字段table_name上创建索引，在表bigtab的字段object_name上创建索引；最后是相关 注意事项：此次优化虽然给出了创建索引的建议，但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析，那样就能给出考虑了索引维护和空间消 耗等因素的更加合理的建议。最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。 可以看出COST值大大下降。第三步：按照优化建议进行优化首先要说明一点的是，最好不要直接按照优化器给出的建议直接优化。因为像建索 引这种操作影响可不是这一条语句。二是可以利用sql profile这对某条语句优化或者针对某 些会话进行优化(下一章会给出sql profile如何使用)。我们这里只是验证一下优化建议的效 果。按照建议，创建两个索引：SQL> create index smalltab_idx1 on smalltab(table_name);IIndex created.SQL> create index bigtab_idx1 on bigtab(object_name);Index created.SQL> analyze table smalltab compute statistics;Table analyzed.SQL> analyze table bigtab compute statistics;Table analyzed.SQL> set timing onSQL> set autot onSQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;Plan hash value: 2594317117I/ Id I Operation / Name / Rows / Bytes / Cost (%CPU)I Time /I 0 / SELECT STATEMENT/1 / 36 / 1119 (3)/ 00:00:14 / 1 / SORT AGGREGATE /1 / 36 /* 2 / HASH JOIN/ 155K/ 5463K/ 1119 (3)/ 00:00:14 / 3 / INDEX FAST FULL SCAN/ SMALLTAB_IDX1 / 1223 / 22014 / 3 (0)/ 00:00:01 / 4 / INDEX FAST FULL SCAN/ BIGTAB_IDX1 / 1205K/ 20M/ 1104 (2)/ 00:00:14 /Predicate Information (identified by operation id):2 - access("A”.”OBJECT_NAME"="B”.”TABLE_NAME")Statistics332 recursive calls0 db block gets4999 consistent gets1 physical reads0 redo size412 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client12 sorts (memory)0 sorts (disk)1 rows processed可以看出，consistent gets比优化前大大下降了，优化建议确实提高了性能。Oracle10g让优化变得如此简单。二、利用sql profile存储优化策略利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给 出优化后的查询计划。你可以按照STA给出的建议重写语句。但是，有些情况下，你可能无 法重写语句（比如在生产环境中，你的语句又在一个包中）。这个时候就可以利用sql profile， 将优化策略存储在profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统 计数据，而使用profile的策略，生成新的查询计划。第一部分：profile的使用SQL Profile对于一下类型语句有效：SELECT 语句；UPDATE 语句；INSERT语句（仅当使用SELECT子句时有效）；DELETE 语句；CREATE语句（仅当使用SELECT子句时有效）；MERGE语句（仅当作UPDATE和INSERT操作时有效）。另外，使用 SQL Profile 还必须有 CREATE ANY SQL PROFILE. 和 ALTER ANY SQL PROFILE 等系统权限。还是举例说明吧：第一步：给用户赋权限DROP ANY SQL PROFILESQL> conn sys/sys as sysdbaConnected.SQL> GRANT CREATE ANY SQL PROFILE TO DEMO;Graint succeeded.ISQL> GRANT DROP ANY SQL PROFILE TO DEMO;Grant ;succeeded.ISQL> GRANT ALTER ANY SQL PROFILE TO DEMO;Grant ;succeeded.SQL> conn demo/demoConnected.ISQL> create index smalltab_idx1 on smalltab(table_name);IIndex created.ISQL> analyze table smalltab compute statistics;ITable analyzed.SQL> set autot onSQL> select/*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta ble_name = 'TAB$'COUNT(*)Execution PlanPlan hash value: 2298554444I Id I Operation / Name / Rows / Bytes / Cost (%CPU)/ Time / 0 / SELECT STATEMENT /1 /18 /11 (0)/ 00:00:01 / 1 / SORT AGGREGATE /1 /18 /* 2 / TABLE ACCESS FULL / SMALLTAB /1 /18 / 11 (0)/ 00:00:01 /第二步，创建、执行优化任务SQL> DECLARE2 my_task_name VARCHAR2(30);3 my_sqltext CLOB;4 BEGIN5 my_sqltext := 'select/*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = "TAB$"'6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(7 sql_text => my_sqltext,8 user_name => 'DEMO',9 scope=> 'COMPREHENSIVE',10 time_limit => 60,11 task_name => 'sql_profile_test',12 description => 'Task to tune a query on a specified table');1314 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');15 END;16 /PL/SQL procedure successfully completed.第三步：查看优化建议SQL> set autot offSQL> set long 10000SQL> set longchunksize 1000SQL> set linesize 100SQL> SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')GENERAL INFORMATION SECTION:sql_profile_test:DEMOTuning Task NameTuning Task OwnerScope: COMPREHENSIVETime Limit(seconds)Completion StatusStarted at:60:COMPLETED:11/29/2005 14:52:09:11/29/2005 14:52:09Completed atNumber of SQL Profile Findings : 1DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')Schema Name: DEMOSQL ID : 3kta54ycuqccbSQL Text : select/*+no_index(smalltab smalltab_idx1)*/count(*) fromsmalltab where table_name = 'TAB$'FINDINGS SECTION (1 finding)1- SQL Profile Finding (see explain plans section below)DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')A potentially better execution plan was found for this statement.Recommendation (estimated benefit: 90.94%) -Consider accepting the recommended SQL profile.I Id I Operation / Name / Rows / Bytes / Cost (%CPU)I Time /I 0 / SELECT STATEMENT /1 /18 /11 (0)/ 00:00:01 / 1 / SORT AGGREGATE / 1 / 18 /IDBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')/* 2/ TABLE ACCESS FULL/ SMALLTAB /1 / 18 / 11 (0)/ 00:00:01 /Predicate Information (identified by operation id):2 - filter("TABLE_NAME"='TAB$')2- Using SQL ProfilePlan hash value: 2664476518DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time/ 0 / SELECT STATEMENT / 1 / 18 /1 (0)/ 00:00:01 / 1 / SORT AGGREGATE / 1 / 18 / /* 2 / INDEX RANGE SCAN/ SMALLTAB_IDX1 /1 / 18 /1 (0)/ 00:00:01 /这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议， 并且不重写语句。第三步：接受profileSQL> DECLARE2 my_sqlprofile_name VARCHAR2(30);3 BEGIN4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (5 task_name => 'sql_profile_test：6 name=> 'my_sql_profile');7 END;8 /PL/SQL procedure successfully completed.在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数 task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名 称。此外这个函数还有其他一些函数，下面是这个函数的原型：DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name IN VARCHAR2, objectjd IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL; task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE) RETURN VARCHAR2;Description是profile的描述信息；task_owner是优化建议任务的所有者；replace 为TRUE时，如果这个profile已经存在，就代替它；force_match为TURE时，表示与语句 强制匹配，即强制使用绑定变量，和系统参数cursor_sharing设置为FORCE时类似，为FALSE 时，与cursor_sharing设置为EXACT时类似，即完全匹配。这里要特别提到的是category这个参数，你可以通过设置这个参数，制定特定会话 使用这个profile。在10g中，每个会话都有一个新参数SQLTUNE_CATEGORY，他的默认值是 DEFAULT0而我们在调用这个函数时，如果没有指定这个参数，那它的值也是DEFAULT，而 如果我们给这个profile指定了一个其它的CATEGORY值，如FOR_TUNING，那么只有会话参 数SQLTUNE_CATEGORY也为FOR_TUNING时，才会使用这个porfile。为什么说这个参数很有 用呢？试想一个这样的环境：你在一个生产系统上利用STA调优一条语句，STA已经给出了 优化建议，但是你又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你 就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE_CATEGORY 为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。此外可以通过视图DBA_SQL_PROFILES来查看已经创建的profile。第四步：查看profile的效果I Id I Operation / Name / Rows / Bytes / Cost (%CPU)I Time /I 0 / SELECT STATEMENT /1 / 18 /1 (0)/ 00:00:01 / 1 / SORT AGGREGATE /1 /18 / /* 2 / INDEX RANGE SCAN SMALLTAB_IDX1 /1 / 18 / 1 (0)/ 00:00:01 /Predicate Information (identified by operation id):2 - access("TABLE_NAME"=，TAB$)Note-SQL prolile "my_sql_profile" used for this statement可以看到，语句采用了 profile中的数据，创建了新的查询计划。并且在查询计划中 还有一些附加信息，表明这个语句是采用了'my_sql_profile'这个profile，而不是根据对象上 面的统计数据来生成的查询计划。第二部分：profile的控制对profile的控制比较简单：修改和删除。包DBMS_SQLTUNE提供了两个存储过程来 完成这两个操作：ALTER_SQL_PROFILE 和 DROP_SQL_PROFILE。BEGINDBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');END;/1. Profile的修改ALTER_SQL_PROFILE 的原型是：DBMS_SQLTUNE.ALTER_SQL_PROFILE ( nameIN VARCHAR2,attribute_name IN VARCHAR2, valueIN VARCHAR2);其中，name就是profile的名字；attribute_name是需要修改的属性的名字；value 是修改后的值。例如，需要使my_sql_profile7失效，可以修改STATUS属性为DISABLED：SQL> BEGIN2 DBMS_SQLTUNE.ALTER_SQL_PROFILE(3 name=> 'my_sql_profile：4 attribute_name => 'STATUS',5 value=> DISABLED);6 END;7 /PL/SQL procedure successfullycompleted.SQL>SQL> set autot on expSQL> select/*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = 'TAB$'COUNT(*)Execution PlanPlan hash value: 2298554444I Id I OperationI Name | Rows | Bytes | Cost (%CPU)I Time |I 0 | SELECT STATEMENT| | 1118111(0) 00:00:01 |11 SORT AGGREGATE| | 11181| |I*21 TABLE ACCESS FULL| SMALLTAB |1118111 (0)I 00:00:011IPredicate Information (identified by operation id):2 - filter("TABLE_NAME"='TAB$')2 .删除 profileDROP_SQL_PROFILE 的原型是：dbms_sqltune.drop_sql_profile (nameIN VARCHAR2,ignoreIN BOOLEAN := FALSE);其中，name是profile的名字，ignore为TRUE时，当指定的profile不存在时不报错。3 .确认某条语句是否已经有相应profile当然我们也希望能确认某条语句是否已经形成了 profile,看是否有必要在对它进行 tuning。这时就可以利用 SQLTEXT_TO_SIGNATlUR数:SQL> set serveroutput onSQL> declare2 v_signature number;3 begin4 v_signature:=DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (5 sql_text => 'select/*+no_index(smalltab smalltab_idx1)*/count(*) fromsmalltab where table_name = "TAB$"',6 force_match => FALSE);7 if v_signature is null then8 DBMS_OUTPUT.put_line('no such sql text in profile');9 else10 DBMS_OUTPUT.put_line('The sql text is in profile);11 end if;、2 end;13 /The sql text is in profilePL/SQL procedure successfully completed.其中，sql_text就是需要检测的内容；force_match的解释与ACCEPT_SQL_PROFILE中 相应参数是一样的。第三部分：profile的转储与移植在某些环境下，比