Home > Database, Enterprise Manager, Oracle, Tuning > Oracle: HOWTO run the SQL Tuning Advisor without Enterprise Manager DB Console or Grid Control

Oracle: HOWTO run the SQL Tuning Advisor without Enterprise Manager DB Console or Grid Control

Problem description:

You want to run the SQL Tuning Advisor, because of a very resource consuming SQL statement, but no Enterprise Manager component is installed or configured for performance reasons or because of troubles in the past.

Problem resolution:
It is not necessary to run Enterprise Manager in order to make use of the SQL Tuning Advisor. Here is a short how to for running the SQL Tuning Advisor on a SQL statement currently using a lot of resources after updating the schema statistics.

  • First of all create a tuning task:

    DECLARE
      l_sql_tune_task_id  VARCHAR2(100);
    BEGIN
      l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                              sql_id      => '&&my_sql_id',
                              scope       => DBMS_SQLTUNE.scope_comprehensive,
                              time_limit  => 60,
                              task_name   => 'sql_tuning_task_&&my_sql_id',
                              description => 'Tuning task for statement &&my_sql_id.');
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
    END;
    /

    Output:

    SQL> SET serveroutput ON
    SQL> 
    SQL> DECLARE
      2   l_sql_tune_task_id  VARCHAR2(100);
      3  BEGIN
      4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
      5                            sql_id      => '&&my_sql_id',
      6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
      7                            time_limit  => 60,
      8                            task_name   => 'sql_tuning_task_&&my_sql_id',
      9                            description => 'Tuning task for statement &&my_sql_id.');
     10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
     11  END;
     12  /
     
    Enter VALUE FOR my_sql_id: 2vbmgruzfy58y
    old   5:			   sql_id      => '&&my_sql_id',
    NEW   5:			   sql_id      => '2vbmgruzfy58y',
    old   8:			   task_name   => 'sql_tuning_task_&&my_sql_id',
    NEW   8:			   task_name   => 'sql_tuning_task_2vbmgruzfy58y',
    old   9:			   description => 'Tuning task for statement &&my_sql_id.');
    NEW   9:			   description => 'Tuning task for statement 2vbmgruzfy58y');
    l_sql_tune_task_id: sql_tuning_task_2vbmgruzfy58y
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

  • If you want, you can check the status of your newly created tuning task by querying dba_advisor_log:

    SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

    Output:

    SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
    OLD   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
    NEW   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2vbmgruzfy58y'
     
    TASK_NAME		       STATUS
    ------------------------------ -----------
    sql_tuning_task_2vbmgruzfy58y  INITIAL
     
    SQL>

  • Next step is to actually execute your SQL Tuning task:

    EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

    Output:

    SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

  • Status will change from INITIAL to COMPLETED:

    SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

    Output:

    SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
    OLD   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
    NEW   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2vbmgruzfy58y'
     
    TASK_NAME		       STATUS
    ------------------------------ -----------
    sql_tuning_task_2vbmgruzfy58y  COMPLETED
     
    SQL>

  • Now you can review the recommendations generated by the SQL Tuning Advisor:

    SET LINES 150
    SET pages 50000
    SET long 5000000
    SET longc 5000000
     
    SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

    Output:

    SQL> SET LINES 150
    SQL> SET pages 50000
    SQL> SET long 5000000
    SQL> SET longc 5000000
    SQL> 
    SQL> SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;
    OLD   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual
    NEW   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_2vbmgruzfy58y') AS recommendations FROM dual
     
    RECOMMENDATIONS
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name   : sql_tuning_task_2vbmgruzfy58y
    Tuning Task Owner  : SYS
    Workload TYPE	   : Single SQL Statement
    Scope		   : COMPREHENSIVE
    TIME LIMIT(seconds): 60
    Completion STATUS  : COMPLETED
    Started at	   : 05/20/2011 09:44:13
    Completed at	   : 05/20/2011 09:44:33
     
    -------------------------------------------------------------------------------
    Schema Name: SYS
    SQL ID	   : 2vbmgruzfy58y
    SQL Text   : SELECT COUNT(*) FROM dba_tables, dba_users
     
    -------------------------------------------------------------------------------
    FINDINGS SECTION (3 findings)
    -------------------------------------------------------------------------------
     
    1- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 24 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    2- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 17 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    3- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 15 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
     
    1- Original
    -----------
    Plan hash VALUE: 674672025
     
    ------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation				     | Name		       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT			     |			       |     1 |   227 |   380	 (3)| 00:00:05 |
    |   1 |  SORT AGGREGATE 			     |			       |     1 |   227 |	    |	       |
    |*  2 |   HASH JOIN				     |			       | 15530 |  3442K|   380	 (3)| 00:00:05 |
    |   3 |    TABLE ACCESS FULL			     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |*  4 |    HASH JOIN RIGHT OUTER		     |			       | 15530 |  3397K|   376	 (3)| 00:00:05 |
    |   5 |     INDEX FULL SCAN			     | I_USER2		       |    45 |   180 |     1	 (0)| 00:00:01 |
    |*  6 |     HASH JOIN RIGHT OUTER		     |			       | 15530 |  3336K|   374	 (3)| 00:00:05 |
    |   7 |      INDEX FAST FULL SCAN		     | I_OBJ1		       | 15494 |   121K|    16	 (0)| 00:00:01 |
    |*  8 |      HASH JOIN RIGHT OUTER		     |			       | 15530 |  3215K|   357	 (2)| 00:00:05 |
    |   9 |       INDEX FAST FULL SCAN		     | I_OBJ1		       | 15494 | 77470 |    16	 (0)| 00:00:01 |
    |* 10 |       HASH JOIN RIGHT OUTER		     |			       | 15530 |  3139K|   341	 (3)| 00:00:05 |
    |  11 |        TABLE ACCESS FULL		     | SEG$		       |  4081 | 44891 |    34	 (0)| 00:00:01 |
    |* 12 |        HASH JOIN			     |			       |  9971 |  1908K|   306	 (2)| 00:00:04 |
    |* 13 | 	TABLE ACCESS FULL		     | TAB$		       |  1200 | 33600 |   223	 (0)| 00:00:03 |
    |* 14 | 	HASH JOIN			     |			       |   128K|    20M|    82	 (7)| 00:00:01 |
    |  15 | 	 MERGE JOIN CARTESIAN		     |			       |   374 | 58718 |    28	(11)| 00:00:01 |
    |* 16 | 	  HASH JOIN			     |			       |     8 |  1224 |    24	(13)| 00:00:01 |
    |  17 | 	   MERGE JOIN CARTESIAN 	     |			       |     8 |  1120 |    24	(13)| 00:00:01 |
    |* 18 | 	    HASH JOIN			     |			       |     8 |   680 |    24	(13)| 00:00:01 |
    |* 19 | 	     HASH JOIN			     |			       |     8 |   656 |    19	(11)| 00:00:01 |
    |* 20 | 	      HASH JOIN 		     |			       |     8 |   632 |    15	(14)| 00:00:01 |
    |* 21 | 	       HASH JOIN OUTER		     |			       |     8 |   608 |    12	 (9)| 00:00:01 |
    |* 22 | 		HASH JOIN		     |			       |     8 |   376 |    10	(10)| 00:00:01 |
    |  23 | 		 NESTED LOOPS		     |			       |     8 |   360 |     7	 (0)| 00:00:01 |
    |  24 | 		  MERGE JOIN CARTESIAN	     |			       |     1 |    16 |     4	 (0)| 00:00:01 |
    |* 25 | 		   TABLE ACCESS FULL	     | PROFILE$ 	       |     1 |     8 |     2	 (0)| 00:00:01 |
    |  26 | 		   BUFFER SORT		     |			       |     1 |     8 |     2	 (0)| 00:00:01 |
    |* 27 | 		    TABLE ACCESS FULL	     | PROFILE$ 	       |     1 |     8 |     2	 (0)| 00:00:01 |
    |* 28 | 		  TABLE ACCESS BY INDEX ROWID| USER$		       |    12 |   348 |     3	 (0)| 00:00:01 |
    |* 29 | 		   INDEX SKIP SCAN	     | I_USER2		       |    12 |       |     1	 (0)| 00:00:01 |
    |  30 | 		 TABLE ACCESS FULL	     | PROFNAME$	       |     1 |     2 |     2	 (0)| 00:00:01 |
    |* 31 | 		TABLE ACCESS FULL	     | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2	 (0)| 00:00:01 |
    |  32 | 	       TABLE ACCESS FULL	     | USER_ASTATUS_MAP        |     9 |    27 |     2	 (0)| 00:00:01 |
    |  33 | 	      TABLE ACCESS FULL 	     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |  34 | 	     TABLE ACCESS FULL		     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |  35 | 	    BUFFER SORT 		     |			       |     1 |    55 |    20	(15)| 00:00:01 |
    |* 36 | 	     FIXED TABLE FULL		     | X$KSPPI		       |     1 |    55 |     0	 (0)| 00:00:01 |
    |  37 | 	   FIXED TABLE FULL		     | X$KSPPCV 	       |   100 |  1300 |     0	 (0)| 00:00:01 |
    |  38 | 	  BUFFER SORT			     |			       |    45 |   180 |    28	(11)| 00:00:01 |
    |  39 | 	   INDEX FAST FULL SCAN 	     | I_USER2		       |    45 |   180 |     1	 (0)| 00:00:01 |
    |* 40 | 	 TABLE ACCESS FULL		     | OBJ$		       | 15494 |   166K|    52	 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
     
       2 - access("T"."TS#"="TS"."TS#")
       4 - access("CX"."OWNER#"="CU"."USER#"(+))
       6 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
       8 - access("T"."BOBJ#"="CO"."OBJ#"(+))
      10 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+))
      12 - access("O"."OBJ#"="T"."OBJ#")
      13 - FILTER(BITAND("T"."PROPERTY",1)=0)
      14 - access("O"."OWNER#"="U"."USER#")
      16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
      18 - access("U"."TEMPTS#"="TTS"."TS#")
      19 - access("U"."DATATS#"="DTS"."TS#")
      20 - access("U"."ASTATUS"="M"."STATUS#")
      21 - access("CGM"."VALUE"(+)="U"."NAME")
      22 - access("U"."RESOURCE$"="P"."PROFILE#")
      25 - FILTER("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
      27 - FILTER("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
      28 - FILTER("U"."RESOURCE$"="PR"."PROFILE#")
      29 - access("U"."TYPE#"=1)
           FILTER("U"."TYPE#"=1)
      31 - FILTER("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')
      36 - FILTER("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
      40 - FILTER(BITAND("O"."FLAGS",128)=0)
     
    -------------------------------------------------------------------------------
     
     
    SQL>

  • If you have reviewed your recommendations, you can remove the task by simply calling drop_tuning_task:

    BEGIN
      DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
    END;
    /

    Output:

    SQL> BEGIN
      2    DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
      3  END;
      4  /
     
    old   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
    NEW   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_2vbmgruzfy58y');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
Categories: Database, Enterprise Manager, Oracle, Tuning Tags:
  1. No comments yet.
  1. No trackbacks yet.

Connect with Facebook