用 SQL Tuning Advisor (STA) 优化SQL语句

最新推荐文章于 2024-07-31 14:41:34 发布

深圳gg

最新推荐文章于 2024-07-31 14:41:34 发布

阅读量981

点赞数

分类专栏： Oracle SQL优化文章标签： sql 优化 access table statistics

Oracle SQL优化专栏收录该内容

130 篇文章 7 订阅

订阅专栏

转自http://blog.csdn.net/tianlesoftware/article/details/5630888

在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在10g中，Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。

执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：

SQL> create table bigtab as select rownum as "id",a.* from sys.all_objects a;

SQL> create table smalltab as select rownum as "id", a.* FROM sys.all_tables a;

SQL> set autot on
SQL> select count(1) from bigtab a,smalltab b where a.object_name=b.table_name;

COUNT(1)
----------
1675

执行计划
----------------------------------------------------------
Plan hash value: 3089226980

--------------------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    34 |   178   (3)| 00:00:03 |
|   1 | SORT AGGREGATE     |          |     1 |    34 |            |          |
|* 2 |   HASH JOIN         |          | 15405 |   511K|   178   (3)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| SMALLTAB | 1535 | 26095 |    13   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIGTAB   | 48623 |   807K|   163   (2)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

Note------ dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          9 recursive calls
          0 db block gets
        881 consistent gets
          0 physical reads
          0 redo size
        409 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          1 rows processed

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   => 'TEST',   -- 注意是大写，不然会报错，用户无效
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 过程已成功完成。

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED

SQL> SET LONG 999999
SQL> set serveroutput on size 999999
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                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 08/22/2012 20:24:44
Completed at                      : 08/22/2012 20:24:45
Number of Statistic Findings      : 2
Number of Index Findings          : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : 6p64dnnsqf9pm
SQL Text   : select count(*) from bigtab a, smalltab b where
             a.object_name=b.table_name

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "TEST"."SMALLTA

Recommendation
--------------
- 考虑收集此表的优
    execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>
            'SMALLTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Rationale
---------
为了选择好的执行计划, 优化程序需

2- Statistics Finding
---------------------
尚未分析表 "TEST"."BIGTA

Recommendation
--------------
- 考虑收集此表的优

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
    execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>
            'BIGTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
为了选择好的执行计划, 优化程序需

3- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------

Recommendation (estimated benefit: 100%)
----------------------------------------
-考虑运行可以改进物理方案设计的 Access Advi
create index TEST.IDX$$_00330001 on TEST.SMALLTAB('TABLE_NAME');

-考虑运行可以改进物理方案设计的 Access Advi
create index TEST.IDX$$_00330002 on TEST.BIGTAB('OBJECT_NAME');

Rationale
---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的
可能比单个语句更可取。通过这种方法可以获得全面的索引建

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3089226980

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    34 |   178   (3)| 00:00:03 |
|   1 | SORT AGGREGATE     |          |     1 |    34 |            |          |
|* 2 |   HASH JOIN         |          | 15405 |   511K|   178   (3)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| SMALLTAB | 1535 | 26095 |    13   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIGTAB   | 48623 |   807K|   163   (2)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
---------------------------------------------------

2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

2- Using New Indices
--------------------
Plan hash value: 105601825

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |    34 |    62   (5)| 00:00:01 |
|   1 | SORT AGGREGATE        |                |     1 |    34 |            |          |
|* 2 |   HASH JOIN            |                | 15405 |   511K|    62   (5)| 00:00:01
|
|   3 |    INDEX FAST FULL SCAN| IDX$$_00330001 | 1535 | 26095 |     6   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX$$_00330002 | 48623 |   807K|    55   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------

2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

-------------------------------------------------------------------------------

通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.