Saturday, February 23, 2013

0 SQL Tuning Advisor (STA)

We can use STA to analysis a single query or group of the query ( SQL Tuning set or STA). The basic steps are list below:

  • Specify a query or create the STA ( from AWR or Shared Pool).
  • Create the profile ( optional)
  • Create the tuning task and execute
  • View the report

In below, I am going to demo:

  • Use STA tune specific SQL from PL/SQL
  • Load AWR and Create STS
  • Load SQL from Shared Pool and create STS
  • Drop the tuning task
  • Drop the sql set

 

Use STA tune specific SQL from PL/SQL

Create test table

create table statest1 as select  * from  dba_objects;
create table statest2 as select  * from  dba_objects;

Test the Query

set timing on
set autot on
select count(*) from statest1,statest2 where statest1.object_id = statest2.object_id;

As you can see, both table are full table scan.

image

The SQL text can not include semi column ; , otherwise, you would get  ORA-00911: invalid character in the report .

variable sts_task  VARCHAR2(64);
 EXEC :stmt_task :=  DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select count(*) from statest1,statest2 where statest1.object_id = statest2.object_id', scope => 'COMPREHENSIVE', task_name => 'MY_STA_TUNE', description => 'Task to tune my test SQL');
 EXECUTE DBMS_SQLTUNE.EXECUTE_TUNING_TASK('MY_STA_TUNE');

image

 select task_name,status from USER_ADVISOR_LOG
 where task_name like 'MY_STA_TUNE';

image

General the report

 set long 100000
 set longchunksize 1000
 set linesize 100
 select DBMS_SQLTUNE.report_tuning_task('MY_STA_TUNE') from dual;

 

 
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : MY_STA_TUNE
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 02/22/2013 00:05:31
Completed at       : 02/22/2013 00:06:08
 
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 09qk9drcaaups
SQL Text   : select count(*) from statest1,statest2 where statest1.object_id
             = statest2.object_id
 
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
 
1- Statistics Finding
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
---------------------
  Table "SYS"."STATEST2" was not analyzed.
 
  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'STATEST2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');
 
  Rationale
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.
 
2- Statistics Finding
---------------------
  Table "SYS"."STATEST1" was not analyzed.
 
  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'STATEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');
 
  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 2829981982
 
----------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    26 |       |   775   (1)| 00:00:10 |
|   1 |  SORT AGGREGATE     |          |     1 |    26 |       |            |          |
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
|*  2 |   HASH JOIN         |          | 71775 |  1822K|  1760K|   775   (1)| 00:00:10 |
|   3 |    TABLE ACCESS FULL| STATEST1 | 71862 |   912K|       |   301   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| STATEST2 | 71775 |   911K|       |   301   (1)| 00:00:04 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("STATEST1"."OBJECT_ID"="STATEST2"."OBJECT_ID")
 
-------------------------------------------------------------------------------
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_STA_TUNE')
----------------------------------------------------------------------------------------------------
 
 
SQL>

LOAD AWR and Create STS

STS is group of Query. We can create this group of query from AWR using snapshot id and base line. We can even add or remove the query from this group. Once this group is created. We can create the tasks to analysis them together.

Get the snapshot ID from DBA_HIST_SNAPSHOT

exec DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name=>'my_sts');
 
DECLARE
  snap_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN snap_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORy (
                    90,  -- begin_snap
                    100,  -- end_snap
                    NULL, -- basic_filter
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    10)   -- result_limit
                  ) p;
 
  DBMS_SQLTUNE.LOAD_SQLSET (
    sqlset_name     => 'my_sts',
    populate_cursor => snap_cursor);
END;
/

image

List the Query from the STS

SELECT *
FROM   TABLE(DBMS_SQLTUNE.select_sqlset ('my_sts'));

imageCreate the tune task

variable sts_task  VARCHAR2(64);
EXEC :sts_task :=  DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'my_sts', scope => 'COMPREHENSIVE', task_name => 'MY_STS_TUNE', description => 'Task to tune my STS');
EXECUTE DBMS_SQLTUNE.EXECUTE_TUNING_TASK('MY_STS_TUNE');
 
select task_name,status from USER_ADVISOR_LOG
where task_name like 'MY_STS_TUNE';

image

Get the report

 set long 100000
 set longchunksize 1000
 set linesize 100
 select DBMS_SQLTUNE.report_tuning_task('MY_STS_TUNE') from dual;

image

LOAD SQL from Shared Pool and create STS

Create SQL Set

exec DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name=>'MY_STS2');
 
DECLARE
  cache_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN cache_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.SELECT_cursor_cache ( NULL,NULL,NULL,NULL,NULL,NULL,1) ) p;
 
  DBMS_SQLTUNE.LOAD_SQLSET (
    sqlset_name     => 'MY_STS2',
    populate_cursor => cache_cursor);
END;
/

image

List the SQL in the SQL Set

SELECT *
FROM   TABLE(DBMS_SQLTUNE.select_sqlset ('MY_STS2'));

imageCreate the tuning task and execute

variable sts_task  VARCHAR2(64);
EXEC :sts_task :=  DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'MY_STS2', scope => 'COMPREHENSIVE', task_name => 'MY_STS_TUNE2', description => 'Task to tune my STS2');
EXECUTE DBMS_SQLTUNE.EXECUTE_TUNING_TASK('MY_STS_TUNE2');
select task_name,status from USER_ADVISOR_LOG
where task_name like 'MY_STS_TUNE2';

List the analysis result

 set long 100000
 set longchunksize 1000
 set linesize 100
 select DBMS_SQLTUNE.report_tuning_task('MY_STS_TUNE2') from dual;

image

 

Drop the tuning task

 exec DBMS_SQLTUNE.drop_tuning_task(task_name => 'MY_STS_TUNE')

image

Drop the sql set

EXEC DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'my_sts');

image

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates