How to use the Sql Tuning Advisor. [ID 262687.1]

  修改时间 15-SEP-2011     类型 BULLETIN     状态 PUBLISHED  
Checked for relevance on 07-Sep-2010

PURPOSE
-------

To show an introduction on how to use the SQL TUNING ADVISOR feature.
The STA consists of functionality built into the Oracle OPTIMIZER to assist
in making tuning Of sql queries easy.

SCOPE & APPLICATION
-------------------

DBAs and Support Analysts

Using the SQL TUNING ADVISOR (STA)
-------------------------------------

Introduction:
--------------

This is a new feature introduced in Oracle 10g . This automates
the entire SQL tuning process. The automatic process replaces manual SQL tuning.

'SQL Tuning Advisor' analyzes candidate SQL statements, and execute a complete
analysis of the statement including:
 - Determining stale or missing statistics
 - Determining better execution plan
 - Detecting better access paths and objects required to satisfy them (indexes, materialized views)
 - Restructuring SQL

While the primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control,
the advisor can be administered with procedures in the DBMS_SQLTUNE package. To use the APIs the user must
have been granted the DBA role and the ADVISOR privilege. If using the SQL Tuning Advisor in Oracle Enterprise
Manager, the user must have been granted the select_catalog_role role.

Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1. Create a SQL tuning task
2. Execute a SQL tuning task

Note: starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for
usage of SQL Tuning Advisor from the command line

Example:
--------

You can use STA through the PL/SQL API and query the various advisory views in SQL*Plus to examine how to
solve performance issues.

The example is based on the SH account executing the various tasks. To
allow SH user to both create task and execute it user SH needs to be granted proper access:
        CONNECT / AS SYSDBA
        GRANT ADVISOR TO SH;
        GRANT SELECT_CATALOG_ROLE TO SH;
        GRANT EXECUTE ON DBMS_SQLTUNE TO SH;

The example presented makes use of a table called SALES, residing in the
SH schema. The table Is not analyzed.

I)  First step is to create the tuning task.

You can create tuning tasks from the following:

-  SQL statement selected by SQL identifier from the cursor cache
-  SQL Tuning Set containing multiple statements
-  Text of a single SQL statement
-  SQL statement selected by SQL identifier from the Automatic Workload Repository.

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
BEGIN
  my_sqltext := 'SELECT * '   ||
                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                           sql_text => my_sqltext,
                           user_name => 'SH',
                           scope => 'COMPREHENSIVE',
                           time_limit => 60,
                           task_name => 'TEST_sql_tuning_task',
                           description => 'Task to tune a query on a specified PRODUCT');
END;

User_name: User under which the CREATE_TUNING_TASK function analyzes the SQL statement.
Scope: COMPREHENSIVE which means that the advisor also performs SQL Profiling analysis
Time_limit: Time in seconds that the function can run. 

The CREATE_TUNING_TASK function returns the task name that you have provided
or generates a unique task name. You can use the task name to specify this task
when using other APIs. To view the task names associated with a specific owner,
you can run the following:

select task_name from dba_advisor_log where owner='SH';

II)  Execute the tuning task.

Begin
dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
end;

OR

Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');

III)  You can check the status of the tas using following query:

select status from dba_advisor_log where task_name='TEST_sql_tuning_task';

iV)  View the Recommendation

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;

The output of above will be like this:

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TEST_sql_tuning_task
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 02/04/2004 23:17:49
Completed at       : 02/04/2004 23:18:19

-------------------------------------------------------------------------------
SQL ID  : 9bxw71yp99fr6

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------

SQL Text: SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100

-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Index "SH"."SALES_PROMO_BIX" was not analyzed.

  Recommendation

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
    Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => 'SH', indname =>
            'SALES_PROMO_BIX', estimate_percent =>

SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS,
and DBA_SQLTUNE_PLANS views can also be queried to get this information.

Note: it is possible for the SQL Tuning Advisor to return no recommendations for
a particular SQL statement e.g. in cases where the plan is already optimal or the
Automatic Tuning Optimization mode cannot find a better plan.

RELATED DOCUMENTS
-----------------

Oracle10g Database Performance Guide Release 1 (10.1)
Oracle10g Database Reference Release 1 (10.1)
PL/SQL Packages and Types Reference Release 1 (10.1)
==================
如何使用脚本方式实现SQL TUNNING?

[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL> @?/rdbms/admin/sqltrpt

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
9uk39qjga3344   3,372.30 declare     WHO_ERROR   exception;     WHO_NONERROR exc
265s5md6h9nqt   3,345.16 begin Tlgcheck.lgcheck; end;
g2cvz8zqq6h8n   2,381.91 SELECT TABLE_NAME NAME FROM DBA_TABLES WHERE OWNER= :B2
2nu2nxy2m0cm4   1,679.26 select * from tpcc.abc where id=1
f5x46yk88jtkc     214.03 DECLARE job BINARY_INTEGER := :job; next_date DATE := :
8bq792t7971f4     197.80 SELECT UPPER(CONTROL_STATUS) CONTROL_STATUS,VMODE_ID,UP
dn8r9qg0fh778      84.58 select value from v$parameter where name='audit_file_de
7zk5x7mh67a19      78.35 SELECT SID,SERIAL#,AUDSID,PROCESS, NVL(UPPER(TLGCHECK.G
7xspsxmnq9mcm      54.38 BEGIN :1 := Tauditpkg.JavaReceiveApi(:2,:3,:4); END;
fwcgw7bygtnxn      49.66 begin                         Tauditpkg.LogonAuditFunct
cvn54b7yz0s8u      48.60 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
cqgv56fmuj63x      47.53 select owner#,name,namespace,remoteowner,linkname,p_tim
7ng34ruy5awxq      31.66 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,
0k8522rmdzg4k      23.82 select privilege# from sysauth$ where (grantee#=:1 or g
db78fxqxwxt7r      23.75 select /*+ rule */ bucket, endpoint, col#, epvalue from

15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
9uk39qjga3344   3,493.54
declare     WHO_ERROR   exception;     WHO_NONERROR exc

265s5md6h9nqt   3,462.40
begin Tlgcheck.lgcheck; end;

g2cvz8zqq6h8n   2,438.87
SELECT TABLE_NAME NAME FROM DBA_TABLES WHERE OWNER= :B2

SQL_ID           ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
2nu2nxy2m0cm4   1,679.55
select * from tpcc.abc where id=1

f5x46yk88jtkc     273.20
DECLARE job BINARY_INTEGER := :job; next_date DATE := :

8bq792t7971f4     252.83
SELECT UPPER(CONTROL_STATUS) CONTROL_STATUS,VMODE_ID,UP

SQL_ID           ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
6gvch1xu9ca3g     237.83
DECLARE job BINARY_INTEGER := :job; next_date DATE := :

b6usrg82hwsa3     237.57
call dbms_stats.gather_database_stats_job_proc (  )

1uk5m5qbzj1vt     171.38
BEGIN dbms_workload_repository.create_snapshot; END;

SQL_ID           ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
6mcpb06rctk0x     167.98
call dbms_space.auto_space_advisor_job_proc (  )

8szmwam7fysa3     121.95
insert into wri$_adv_objspace_trend_data select timepoi

dn8r9qg0fh778     105.08
select value from v$parameter where name='audit_file_de

SQL_ID           ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
bunssq950snhf      99.00
insert into wrh$_sga_target_advice   (snap_id, dbid, in

7zk5x7mh67a19      89.68
SELECT SID,SERIAL#,AUDSID,PROCESS, NVL(UPPER(TLGCHECK.G

3g2z0wdat6r8h      60.42
declare   -- local variables here begin   --null;   exe

Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 9uk39qjga3344 输入指定的SQL ID,脚本会自动分析ORACLE调用,如果为具体的SQL 则会显示执行计划

Sql Id specified: 9uk39qjga3344

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_2111
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 02/14/2012 13:57:27
Completed at                      : 02/14/2012 13:57:27

-------------------------------------------------------------------------------
Schema Name: ASSET
SQL ID     : 9uk39qjga3344

SQL Text   : declare
                 WHO_ERROR   exception;
                 WHO_NONERROR exception;
                 WHERE_ERROR exception;
                 WHERE_NONERROR exception;
                 APP_ERROR   exception;
                 APP_NONERROR exception;
                 WHEN_ERROR  exception;
                 WHEN_NONERROR exception;
                 NORMAL_ERROR exception;
                 BYPASS_ERROR exception;
                 CONFLICT_ERROR exception;
                 USER_ERROR     exception;

                 USER_NONERROR  exception;
                 VERSION_ERROR  exception;
                 LG_ERROR exception;
                 PRIVS_ERROR exception;
                 pragma exception_init(WHO_ERROR,-20001);
                 pragma exception_init(WHERE_ERROR,-20002);
                 pragma exception_init(APP_ERROR,-20003);
                 pragma exception_init(WHEN_ERROR,-20004);
                 pragma exception_init(WHO_NONERROR,-20005);
                 pragma exception_init(WHERE_NONERROR,-20006);
                 pragma exception_init(APP_NONERROR,-20007);
                 pragma exception_init(WHEN_NONERROR,-20008);
                 pragma exception_init(CONFLICT_ERROR,-20009);

                 pragma exception_init(USER_ERROR,-20010);
                 pragma exception_init(USER_NONERROR,-20011);
                 pragma exception_init(VERSION_ERROR,-20012);
    --通用错误,用户名和密码错误
                 pragma exception_init(LG_ERROR,-1017);
                 pragma exception_init(PRIVS_ERROR,-1031);
             begin
               execute immediate 'begin Tlgcheck.lgcheck; end;';
               --dbms_lock.sleep(30);
             exception
               when WHO_ERROR then
                 raise;
               when WHERE_ERROR then

                 raise;
               when APP_ERROR then
                 raise;
               when WHEN_ERROR then
                 raise;
               when WHO_NONERROR then
                 raise;
               when WHERE_NONERROR then
                 raise;
               when APP_NONERROR then
                 raise;
               when WHEN_NONERROR then
                 raise;

               when CONFLICT_ERROR then
                 raise;
               when VERSION_ERROR then
                 raise;
               when LG_ERROR then
                 raise;
               when PRIVS_ERROR then
                 raise;
               when others then
                 null;
             end LogonEvent;

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

ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Type of SQL statement not supported.

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

2、Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: g2cvz8zqq6h8n

Sql Id specified: g2cvz8zqq6h8n

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_2112
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 02/14/2012 13:59:35
Completed at                      : 02/14/2012 13:59:37
Number of SQL Restructure Findings: 1

-------------------------------------------------------------------------------
Schema Name: ASSET

SQL ID     : g2cvz8zqq6h8n
SQL Text   : SELECT TABLE_NAME NAME FROM DBA_TABLES WHERE OWNER= :B2 AND
             TABLE_NAME=:B1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.

  Recommendation
  --------------
  - Consider removing the disconnected table or view from this statement or
    add a join condition which refers to it.

  Rationale
  ---------
    A cartesian product should be avoided whenever possible because it is an
    expensive operation and might produce a large amount of data.

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

1- Original
-----------
Plan hash value: 1891431045

--------------------------------------------------------------------------------
-----------------------
| Id  | Operation                            | Name           | Rows  | Bytes |
Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
-----------------------
|   0 | SELECT STATEMENT                     |                |     1 |   179 |
    9  (12)| 00:00:01 |

|*  1 |  HASH JOIN                           |                |     1 |   179 |
    9  (12)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN               |                |     1 |   166 |
    8   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                |                |     1 |   111 |
    8   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER               |                |     1 |   108 |
    7   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER              |                |     1 |   100 |
    6   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER             |                |     1 |    95 |
    6   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                  |                |     1 |    84 |

    5   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                 |                |     1 |    81 |
    4   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                |                |     1 |    51 |
    3   (0)| 00:00:01 |
|  10 |           TABLE ACCESS BY INDEX ROWID| USER$          |     1 |    15 |
    1   (0)| 00:00:01 |
|* 11 |            INDEX UNIQUE SCAN         | I_USER1        |     1 |       |
    0   (0)| 00:00:01 |
|* 12 |           TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |    36 |
    2   (0)| 00:00:01 |
|* 13 |            INDEX RANGE SCAN          | I_OBJ2         |     1 |       |
    1   (0)| 00:00:01 |

|* 14 |          TABLE ACCESS CLUSTER        | TAB$           |     1 |    30 |
    1   (0)| 00:00:01 |
|* 15 |           INDEX UNIQUE SCAN          | I_OBJ#         |     1 |       |
    0   (0)| 00:00:01 |
|  16 |         TABLE ACCESS CLUSTER         | TS$            |     1 |     3 |
    1   (0)| 00:00:01 |
|* 17 |          INDEX UNIQUE SCAN           | I_TS#          |     1 |       |
    0   (0)| 00:00:01 |
|  18 |        TABLE ACCESS CLUSTER          | SEG$           |     1 |    11 |
    1   (0)| 00:00:01 |
|* 19 |         INDEX UNIQUE SCAN            | I_FILE#_BLOCK# |     1 |       |
    0   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN              | I_OBJ1         |     1 |     5 |

    0   (0)| 00:00:01 |
|  21 |      TABLE ACCESS BY INDEX ROWID     | OBJ$           |     1 |     8 |
    1   (0)| 00:00:01 |
|* 22 |       INDEX UNIQUE SCAN              | I_OBJ1         |     1 |       |
    0   (0)| 00:00:01 |
|  23 |     TABLE ACCESS CLUSTER             | USER$          |     1 |     3 |
    1   (0)| 00:00:01 |
|* 24 |      INDEX UNIQUE SCAN               | I_USER#        |     1 |       |
    0   (0)| 00:00:01 |
|  25 |    BUFFER SORT                       |                |     1 |    55 |
    7   (0)| 00:00:01 |
|* 26 |     FIXED TABLE FULL                 | X$KSPPI        |     1 |    55 |
    0   (0)| 00:00:01 |

|  27 |   FIXED TABLE FULL                   | X$KSPPCV       |   100 |  1300 |
    0   (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------

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

   1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
  11 - access("U"."NAME"=:B2)
  12 - filter(BITAND("O"."FLAGS",128)=0)
  13 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1)
  14 - filter(BITAND("T"."PROPERTY",1)=0)

  15 - access("O"."OBJ#"="T"."OBJ#")
  17 - access("T"."TS#"="TS"."TS#")
  19 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
              "T"."BLOCK#"="S"."BLOCK#"(+))
  20 - access("T"."BOBJ#"="CO"."OBJ#"(+))
  22 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  24 - access("CX"."OWNER#"="CU"."USER#"(+))
  26 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

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



Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

only 1 comment untill now

  1. pioneering@parkhouse.bartons” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

Add your comment now

无觅相关文章插件