《利用oracle 10g sql优化器(sta)优化语句》由会员分享，可在线阅读，更多相关《利用oracle 10g sql优化器(sta)优化语句（6页珍藏版）》请在金锄头文库上搜索。
1、SQL 优化器(SQL Tuning Advisor STA)是 Oracle10g 中推出的帮助 DBA 优化工具， 它的特点是简单、智能，DBA 值需要调用函数就可以给出一个性能很差的语句的优化结果。 下面介绍一下它的使用。 使用 STA 一定要保证优化器是 CBO 模式下。一、利用一、利用 STA 优化语句优化语句对于问题语句的收集，可以通过 Oracle10g 的另一个自动化工具：数据库自动诊断 监视工具（ADDM） 。它的使用可以参照我的另一篇文章Oracle10g 数据库自动诊断监视工 具(ADDM)使用指南 。 我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的，S
2、TA 是 用起来很简单（只要你会调用存储过程，都能使用这个工具） ，三个步骤就可以完成一次语 句调优。测试环境创建：测试环境创建：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.S
3、QL DECLARE2 n NUMBER;3 BEGIN4 FOR n IN 1.1005 LOOP6 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 onSQL set autot onSQL select count(*) from bigtab a, smalltab b where
4、a.object_name=b.table_name;COUNT(*)-135000Elapsed: 00:00:05.59Execution Plan-Plan hash value: 3089226980-| 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)|
5、 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 calls0 db block gets16013 consiste
6、nt 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 来创建优化任务，调用存储过程
7、 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_na
8、me = 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 是需要优化
9、的语句，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;TASK_NAME STATUS- -tuning_sql_test CO
10、MPLETED第二步：查看优化结果第二步：查看优化结果通过函数可以查看优化结果。SQL set long 10000SQL set longchunksize 1000SQL set linesize 100SQL 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 Ow
11、ner : DEMOScope : COMPREHENSIVETime Limit(seconds) : 60Completion Status : COMPLETEDStarted at : 11/30/2005 13:16:43Completed at : 11/30/2005 13:16:44Number of Index Findings : 1Schema Name: DEMOSQL ID : 6p64dnnsqf9pmSQL Text : select count(*) from bigtab a, smalltab b wherea.object_name=b.table_nam
12、e-FINDINGS SECTION (1 finding)-1- Index Finding (see explain plans section below)The execution plan of this statement can be improved by creating one or moreindices.Recommendation (estimated benefit: 100%)- Consider running the Access Advisor to improve the physical schema designor creating the recommended index.create index DEMO.IDX$_06
