ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用Oracle 10g SQL优化器(STA)优化语句[转]

利用Oracle 10g SQL优化器(STA)优化语句[转]

原创 Linux操作系统 作者:great_li 时间:2011-07-01 16:33:48 0 删除 编辑

 

<!--

jackjw | 04 十一月, 2008 16:03

利用Oracle 10g SQL优化器(STA)优化语句


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: 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)| 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  BEGIN
  5    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');
 13
 14    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';
 
TASK_NAME                      STATUS
------------------------------        -----------
tuning_sql_test                COMPLETED

第二步:查看优化结果

通过函数可以查看优化结果。

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> 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_test
Tuning Task Owner                 : DEMO
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 11/30/2005 13:16:43
Completed at                      : 11/30/2005 13:16:44
Number of Index Findings          : 1
 
Schema Name: DEMO
SQL ID     : 6p64dnnsqf9pm
SQL Text   : select count(*) from bigtab a, smalltab b where
             a.object_name=b.table_name
 
-------------------------------------------------------------------------------
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 more
  indices.
 
  Recommendation (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');
 
  Rationale
  ---------
    Creating 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 SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
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)| 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")
 
2- Using New Indices
--------------------
Plan hash value: 494801882
 
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |    36 |  1108   (3)| 00:00:14 |
|   1 |  SORT AGGREGATE        |                |     1 |    36 |            |        |
|*  2 |   HASH JOIN            |                |   155K|  5462K|  1108   (3)| 00:00:14 |
|   3 |    INDEX FAST FULL SCAN| IDX$$_06C50001 |  1223 | 22014 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX$$_06C50002 |  1205K|    20M|  1093   (2)| 00:00:14 |
-----------------------------------------------------------------------------------------
 
Predicate 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值大大下降。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7969839/viewspace-701143/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-03-27

  • 博文量
    59
  • 访问量
    73190