Archive

Archive for the ‘Tuning’ Category

Oracle: How to easily display the execution plan of a statement located in the shared pool (command line style)

Problem description:

You want to review the execution plan of a statement currently or previously executed by a session and Oracle Enterprise Manager is not use. Querying V$SQL_PLAN would give you the requested information, but not quiet comfortable.

 
Problem resolution:

As with “EXPLAIN PLAN” generated execution plans, you can use DBMS_XPLAN in order to display the execution plan for a statement in the shared pool. To be more specific, the function DISPLAY_CURSOR will provide you the information by passing the sql_id and cursor child number of the statement in concern:

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

In addition DBMS_XPLAN.DISPLAY_CURSOR can provide you various plan statistics such as I/O, memory and timing by specifying the format:

  • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
  • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).
  • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
  • ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

In addition following keywords can be added to above standard formats in order to customize the output. These keywords must be separated by a comma or a space:

  • ROWS – if relevant, shows the number of rows estimated by the optimizer
  • BYTES – if relevant, shows the number of bytes estimated by the optimizer
  • COST – if relevant, shows optimizer cost information
  • PARTITION – if relevant, shows partition pruning information
  • PARALLEL – if relevant, shows PX information (distribution method and table queue information)
  • PREDICATE – if relevant, shows the predicate section
  • PROJECTION -if relevant, shows the projection section
  • ALIAS – if relevant, shows the “Query Block Name / Object Alias” section
  • REMOTE – if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
  • NOTE – if relevant, shows the note section of the explain plan
  • IOSTATS – assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for ALL (or only for the LAST as shown below) executions of the cursor.
  • MEMSTATS – Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators.
  • ALLSTATS – A shortcut for ‘IOSTATS MEMSTATS’
  • LAST – By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution.

 
Examples:

  • Display the execution for your the last executed statement of your session

    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID	dt0hqkzcdq5ny, child NUMBER 1
    -------------------------------------
    SELECT * FROM TABLE(dbms_xplan.display_cursor)
     
    Plan hash VALUE: 3713220770
     
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation			  | Name	   | ROWS  | Bytes | Cost (%CPU)| TIME	   |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT		  |		   |	   |	   |	24 (100)|	   |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |	 4 |	 8 |	24   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------
     
    Note
    -----
       - cardinality feedback used FOR this statement
     
     
    17 ROWS selected.
     
    SQL>

     

  • Display the execution plan for a specific statement executed by another session:

    SQL> SELECT sid, sql_id, sql_child_number FROM v$session WHERE sid = 1934;
     
           SID SQL_ID	 SQL_CHILD_NUMBER
    ---------- ------------- ----------------
          1934 2rk5134pp7s3g		1
     
    SQL> 
    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('2rk5134pp7s3g', 1));
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID	2rk5134pp7s3g, child NUMBER 1
    -------------------------------------
    UPDATE myschema.bycicle SET comment = :comment
    WHERE id = :id
     
    Plan hash VALUE: 960803932
     
    -------------------------------------------------------------------------------------
    | Id  | Operation	   | Name	    | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT   |		    |	    |	    |	  1 (100)|	    |
    |   1 |  UPDATE 	   | BYCICLE	    |	    |	    |		 |	    |
    |*  2 |   INDEX UNIQUE SCAN| BYCICLE#ID     |	  1 |	167 |	  1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
     
       2 - access("ID"=:ID)
     
     
    20 ROWS selected.
     
    SQL>
  • Display execution plan for all children of a specific SQL_ID:

    SELECT * FROM TABLE(dbms_xplan.display_cursor('2rk5134pp7s3g'));

 
References:

Categories: Oracle, SQLPlus, Tuning Tags:

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: