利用Oracle 0g SQL优化器(STA)优化语句.docVIP

SQL优化器 SQL Tuning Advisor STA 是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。 使用STA一定要保证优化器是CBO模式下。 一、利用STA优化语句 对于问题语句的收集，可以通过Oracle10g的另一个自动化工具：数据库自动诊断监视工具（ADDM）。它的使用可以参照我的另一篇文章《Oracle10g数据库自动诊断监视工具 ADDM 使用指南》。 我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的，STA是用起来很简单（只要你会调用存储过程，都能使用这个工具），三个步骤就可以完成一次语句调优。 测试环境创建： 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 DECLARE 2 n NUMBER; 3??? BEGIN 4 FOR n IN 1..100 5 LOOP 6 INSERT INTO bigtab SELECT rownum as id, a.* FROM dba_objects a; 7 COMMIT; 8 END LOOP; 9?? END; / PL/SQL procedure successfully completed. 这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询： SQL set timing on SQL set autot on SQL select count * from bigtab a, smalltab b where a.object_name b.table_name; ? COUNT * 135000 Elapsed: 00:00:05.59 Execution Plan Plan hash value: 座机电话号码80 | Id? | Operation | Name | Rows? | Bytes | Cost %CPU | Time | |?? 0 | SELECT STATEMENT??? | | 1 |??? 36 |? 3550?? 2 | 00:00:43 | |?? 1 |? SORT AGGREGATE | | 1 |??? 36 | | | |*? 2 |?? HASH JOIN | |?? 155K| ?5462K|? 3550?? 2 | 00:00:43 | |?? 3 |??? TABLE ACCESS FULL| SMALLTAB |? 1223 | 22014 |??? 11?? 0 | 00:00:01 | |?? 4 |??? TABLE ACCESS FULL| BIGTAB?? |? 1205K|??? 20M|? 3526?? 1 | 00:00:43 | Predicate Information identified by operation id : 2 - access A.OBJECT_NAME B.TABLE_NAME Statistics 0? recursive calls 0? db block gets 16013? consistent gets 14491? physical reads 0? redo size 412? bytes sent via SQL*Net to client 385? bytes received via SQL*Net from client 2? SQL*Net roundtrips to/from client 0? sorts memory 0? sorts disk 1? rows processed 可以看出这个语句执行性能很差：16013 consistent gets。 第一步：创建优化任务并执行 通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务： SQL set autot off SQL set timing off SQL DECLARE ? 2??? my_task_name VARCHAR2 30 ; ? 3??? my_sqltext?? CLOB; ? 4? B