2 Replies Latest reply: Jul 5, 2013 10:05 AM by L-MachineGun RSS

    SQL PROFILE USED/NOT USED

    swapnil kambli

      Hi All,

      We have slow runnig query problem with below queries:

      QUERY 1
      =======

      SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".PXINSNAME AS "pxInsName"
       ,  "PC0".WORKTYPENAME AS "WorkTypeName" ,  "PC0".PYSTATUSWORK AS
       "pyStatusWork" ,  "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
       "PC0".LINKEDREFTO as "pxInsHandle"  FROM  V_FORM_RELATIONSHIPS_R_1_0
       "PC0"  WHERE ( "PC0".LINKEDREFFROM = :1  ) AND (  "PC0".pxObjClass = :2
         )  ORDER BY  "PC0".PXINSNAME  DESC  ,  "PC0".PXCREATEDATETIME
      
      
      

        
      QUERY 2
      =======

         SELECT PYID AS "pyID" ,  PXOBJCLASS AS "pxObjClass" ,  PZINSKEY AS
       "pzInsKey", PZINSKEY as "pxInsHandle"  FROM  V_WORK_R_1_0   WHERE (
       PYID = :1  ) AND (  pxObjClass = :2   ) 
      
      
      


      V_WORK_R_1_0 is a view which union all of two table without any where clause
      V_FORM_RELATIONSHIPS_R_1_0 is another view that is made up of V_WORK_R_1_0 view and one more table.

       

      Production DBA accepted both the SQL PROFILE created for these two queries.

       

      FROM DBA_HIST_SQLSTATS it looks like sometime the profile getting used and sometime old plan is being used.How to ensure that correct SQLPROFILE once accepted is used?

       

      This is 4 node RAC oracle 11.2.0.2.0 on Linux

       

      For first query below are sql stats

       

       

      col BEGIN_INTERVAL_TIME for a26
        col end_INTERVAL_TIME for a26
        col ROWS_PROCESSED_TOTAL for 99999
        col ROWS_PROCESSED_delta for 99999
        select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode 
        --,ROWS_PROCESSED_TOTAL,ROWS_PROCESSED_delta--,CPU_TIME_TOTAL,CPU_TIME_DELTA,ELAPSED_TIME_TOTAL,ELAPSED_TIME_DELTA
        from dba_hist_sqlstat q, dba_hist_snapshot s 
        where q.dbid = 4026476544 and q.sql_id = '1um96ykvtwrh4'
        and q.snap_id = s.snap_id
        and s.begin_interval_time between sysdate-2 and sysdate 
        order by 1,s.snap_id desc;
        BEGIN_INTERVAL_TIME        END_INTERVAL_TIME             SNAP_ID       DBID SQL_ID        PLAN_HASH_VALUE OPTIMIZER_COST OPTIMIZER_
        -------------------------- -------------------------- ---------- ---------- ------------- --------------- -------------- ----------
        03-JUL-13 06.00.33.805 AM  03-JUL-13 07.00.21.439 AM       17316 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        .
        03-JUL-13 06.00.33.843 AM  03-JUL-13 07.00.21.475 AM       17316 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        03-JUL-13 06.00.33.843 AM  03-JUL-13 07.00.21.475 AM       17316 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        03-JUL-13 06.00.33.843 AM  03-JUL-13 07.00.21.474 AM       17316 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        .
        .
        03-JUL-13 07.00.21.475 AM  03-JUL-13 08.00.06.051 AM       17317 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        03-JUL-13 08.00.06.023 AM  03-JUL-13 09.00.11.626 AM       17318 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        03-JUL-13 08.00.06.023 AM  03-JUL-13 09.00.11.626 AM       17318 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        03-JUL-13 08.00.06.051 AM  03-JUL-13 09.00.11.596 AM       17318 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        03-JUL-13 08.00.06.051 AM  03-JUL-13 09.00.11.623 AM       17318 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        03-JUL-13 08.00.06.051 AM  03-JUL-13 09.00.11.596 AM       17318 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        .
        .
        03-JUL-13 11.00.10.502 AM  03-JUL-13 12.00.16.034 PM       17321 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        03-JUL-13 11.00.10.502 AM  03-JUL-13 12.00.16.033 PM       17321 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        03-JUL-13 11.00.10.503 AM  03-JUL-13 12.00.16.034 PM       17321 4026476544 1um96ykvtwrh4       377865450             20 ALL_ROWS
        .
        .
        03-JUL-13 12.00.16.034 PM  03-JUL-13 01.00.42.715 PM       17322 4026476544 1um96ykvtwrh4       377865450                ALL_ROWS
        03-JUL-13 01.00.42.685 PM  03-JUL-13 02.00.10.200 PM       17323 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        03-JUL-13 01.00.42.714 PM  03-JUL-13 02.00.10.200 PM       17323 4026476544 1um96ykvtwrh4      1122713586         179350 ALL_ROWS
        .
        .
        03-JUL-13 03.00.15.887 PM  03-JUL-13 04.00.05.570 PM       17325 4026476544 1um96ykvtwrh4      1122713586         179347 ALL_ROWS
        03-JUL-13 03.00.15.887 PM  03-JUL-13 04.00.05.570 PM       17325 4026476544 1um96ykvtwrh4       377865450          30585 ALL_ROWS
        03-JUL-13 03.00.15.914 PM  03-JUL-13 04.00.05.541 PM       17325 4026476544 1um96ykvtwrh4      1122713586         179347 ALL_ROWS
        .
        .
        03-JUL-13 03.00.15.915 PM  03-JUL-13 04.00.05.570 PM       17325 4026476544 1um96ykvtwrh4       377865450          30585 ALL_ROWS
        03-JUL-13 04.00.05.541 PM  03-JUL-13 05.00.09.865 PM       17326 4026476544 1um96ykvtwrh4      1122713586         179347 ALL_ROWS
        .
        .
        03-JUL-13 05.00.09.866 PM  03-JUL-13 06.00.26.710 PM       17327 4026476544 1um96ykvtwrh4      1122713586         179347 ALL_ROWS
        .
        .
        03-JUL-13 08.00.12.716 PM  03-JUL-13 09.00.00.960 PM       17330 4026476544 1um96ykvtwrh4      1122713586         179347 ALL_ROWS
        03-JUL-13 08.00.12.716 PM  03-JUL-13 09.00.00.989 PM       17330 4026476544 1um96ykvtwrh4      1122713586         179347 ALL_ROWS
      
      
      

        377865450 is the sql_hash of explain plan given by SQL_PROFILE

        

      select
          2  extractvalue(value(d), '/hint') as outline_hints
          3  from
          4  xmltable('/*/outline_data/hint'
          5  passing (
          6  select
          7  xmltype(other_xml) as xmlval
          8  from
          9  dba_hist_sql_plan
         10  where
         11  sql_id = '&sql_id'
         12  and plan_hash_value = &plan_hash_value
         13  and other_xml is not null
        )
         14   15  ) d;
        Enter value for sql_id: 1um96ykvtwrh4
        old  11: sql_id = '&sql_id'
        new  11: sql_id = '1um96ykvtwrh4'
        Enter value for plan_hash_value: 377865450
        old  12: and plan_hash_value = &plan_hash_value
        new  12: and plan_hash_value = 377865450
        OUTLINE_HINTS
        --------------------------------------------------------------------------------
        IGNORE_OPTIM_EMBEDDED_HINTS
        OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
        DB_VERSION('11.2.0.2')
        ALL_ROWS
        OUTLINE_LEAF(@"SEL$B01C6807")
        OUTLINE_LEAF(@"SEL$8E13D68A")
        OUTLINE_LEAF(@"SET$5715CE2E")
        PUSH_PRED(@"SEL$F5BB74E1" "VW"@"SEL$2" 3)
        OUTLINE_LEAF(@"SEL$F5BB74E1")
        MERGE(@"SEL$2")
        OUTLINE(@"SEL$3")
        OUTLINE_HINTS
        --------------------------------------------------------------------------------
        OUTLINE(@"SEL$4")
        OUTLINE(@"SET$1")
        OUTLINE(@"SEL$F5BB74E1")
        MERGE(@"SEL$2")
        OUTLINE(@"SEL$1")
        OUTLINE(@"SEL$2")
        INDEX_RS_ASC(@"SEL$F5BB74E1" "LR"@"SEL$2" ("LINK_RELATIONSHIP"."PXLINKEDREFFROM"
        ))
        NO_ACCESS(@"SEL$F5BB74E1" "VW"@"SEL$2")
        LEADING(@"SEL$F5BB74E1" "LR"@"SEL$2" "VW"@"SEL$2")
        OUTLINE_HINTS
        --------------------------------------------------------------------------------
        USE_NL(@"SEL$F5BB74E1" "VW"@"SEL$2")
        INDEX_RS_ASC(@"SEL$8E13D68A" "W"@"SEL$4" ("WORK_COMMON"."PZINSKEY"))
        INDEX_RS_ASC(@"SEL$B01C6807" "WORK_BATCH"@"SEL$3" ("WORK_BATCH"."PZINSKEY"))
      
      
      

       


       

      select id, operation, options, object_name, cost
        from dba_hist_sql_plan
        where dbid = 4026476544 and sql_id = '1um96ykvtwrh4'
        and plan_hash_value = 377865450  2    3    4
          5  ;
          ID OPERATION                      OPTIONS                        OBJECT_NAME                     COST
        ---------- ------------------------------ ------------------------------ ------------------------- ----------
           0 SELECT STATEMENT                                                                                20
           1 SORT                           ORDER BY                                                         20
           2 FILTER
           3 NESTED LOOPS                                                                                    19
           4 TABLE ACCESS                   BY INDEX ROWID                 LINK_RELATIONSHIP                  5
           5 INDEX                          RANGE SCAN                     RELATIONSHIP_REFFROM               3
           6 VIEW                                                          V_WORK_R_1_0                       7
           7 UNION ALL PUSHED PREDICATE
           8 TABLE ACCESS                   BY INDEX ROWID                 WORK_BATCH                         4
           9 INDEX                          UNIQUE SCAN                    WORK_BATCH_PK                      3
          10 TABLE ACCESS                   BY INDEX ROWID                 WORK_COMMON                        3
          11 INDEX                          UNIQUE SCAN                    WORK_COMMON_PK                     2
      
      
      

         
         
        EXPLAIN PLAN OF SQL_PROFILE PROVIDED

       

      select * from table(dbms_xplan.display_awr('1um96ykvtwrh4',377865450,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
        PLAN_TABLE_OUTPUT
        ------------------------------------------------------------------------------------------------------------------------
        SQL_ID 1um96ykvtwrh4
        --------------------
        SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".PXINSNAME AS "pxInsName"
        ,  "PC0".WORKTYPENAME AS "WorkTypeName" ,  "PC0".PYSTATUSWORK AS
        "pyStatusWork" ,  "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
        "PC0".LINKEDREFTO as "pxInsHandle"  FROM  V_FORM_RELATIONSHIPS_R_1_0
        "PC0"  WHERE ( "PC0".LINKEDREFFROM = :1  ) AND (  "PC0".pxObjClass = :2
          )  ORDER BY  "PC0".PXINSNAME  DESC  ,  "PC0".PXCREATEDATETIME
        Plan hash value: 377865450
        ----------------------------------------------------------------------------------------------------------
        | Id  | Operation                        | Name                 | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
        ----------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT                 |                      |        |       |    20 (100)|          |
        |   1 |  SORT ORDER BY                   |                      |      2 |  1362 |    20   (5)| 00:00:01 |
        |   2 |   FILTER                         |                      |        |       |            |          |
        |   3 |    NESTED LOOPS                  |                      |      2 |  1362 |    19   (0)| 00:00:01 |
        |   4 |     TABLE ACCESS BY INDEX ROWID  | LINK_RELATIONSHIP    |      2 |   204 |     5   (0)| 00:00:01 |
        |   5 |      INDEX RANGE SCAN            | RELATIONSHIP_REFFROM |      2 |       |     3   (0)| 00:00:01 |
        |   6 |     VIEW                         | V_WORK_R_1_0         |      1 |   579 |     7   (0)| 00:00:01 |
        |   7 |      UNION ALL PUSHED PREDICATE  |                      |        |       |            |          |
        |   8 |       TABLE ACCESS BY INDEX ROWID| WORK_BATCH           |      1 |    89 |     4   (0)| 00:00:01 |
        |   9 |        INDEX UNIQUE SCAN         | WORK_BATCH_PK        |      1 |       |     3   (0)| 00:00:01 |
        |  10 |       TABLE ACCESS BY INDEX ROWID| WORK_COMMON          |      1 |   109 |     3   (0)| 00:00:01 |
        |  11 |        INDEX UNIQUE SCAN         | WORK_COMMON_PK       |      1 |       |     2   (0)| 00:00:01 |
        ----------------------------------------------------------------------------------------------------------
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
           1 - SEL$F5BB74E1
           4 - SEL$F5BB74E1 / LR@SEL$2
           5 - SEL$F5BB74E1 / LR@SEL$2
           6 - SET$5715CE2E / VW@SEL$2
           7 - SET$5715CE2E
           8 - SEL$B01C6807 / WORK_BATCH@SEL$3
           9 - SEL$B01C6807 / WORK_BATCH@SEL$3
          10 - SEL$8E13D68A / W@SEL$4
          11 - SEL$8E13D68A / W@SEL$4
        Peeked Binds (identified by position):
        --------------------------------------
           1 - :1 (VARCHAR2(30), CSID=873): 'xxxx-xxx-SERVICEREQUEST-WORK-ACCTMAINT AM-13640'
        Note
        -----
           - Warning: basic plan statistics not available. These are only collected when:
            * hint 'gather_plan_statistics' is used for the statement or
            * parameter 'statistics_level' is set to 'ALL', at session or system level
      
      
      

           
        OLD EXPLAIN PLAN IS


       

      select * from table(dbms_xplan.display_awr('1um96ykvtwrh4',1122713586,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
        PLAN_TABLE_OUTPUT
        ------------------------------------------------------------------------------------------------------------------------
        SQL_ID 1um96ykvtwrh4
        --------------------
        SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".PXINSNAME AS "pxInsName"
        ,  "PC0".WORKTYPENAME AS "WorkTypeName" ,  "PC0".PYSTATUSWORK AS
        "pyStatusWork" ,  "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
        "PC0".LINKEDREFTO as "pxInsHandle"  FROM  V_FORM_RELATIONSHIPS_R_1_0
        "PC0"  WHERE ( "PC0".LINKEDREFFROM = :1  ) AND (  "PC0".pxObjClass = :2
          )  ORDER BY  "PC0".PXINSNAME  DESC  ,  "PC0".PXCREATEDATETIME
        Plan hash value: 1122713586
        ------------------------------------------------------------------------------------------------------------
        | Id  | Operation                 | Name                  | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
        ------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT          |                       |        |       |       |   171K(100)|          |
        |   1 |  SORT ORDER BY            |                       |  32220 |    20M|    22M|   171K  (1)| 00:34:18 |
        |   2 |   FILTER                  |                       |        |       |       |            |          |
        |   3 |    HASH JOIN              |                       |  32220 |    20M|  3560K|   166K  (1)| 00:33:22 |
        |   4 |     VIEW                  | index$_join$_002      |  31931 |  3180K|       |  1648   (1)| 00:00:20 |
        |   5 |      HASH JOIN            |                       |        |       |       |            |          |
        |   6 |       INDEX RANGE SCAN    | RELATIONSHIP_REFFROM  |  31931 |  3180K|       |   430   (1)| 00:00:06 |
        |   7 |       INDEX FAST FULL SCAN| AK1_LINK_RELATIONSHIP |  31931 |  3180K|       |  1096   (1)| 00:00:14 |
        |   8 |     VIEW                  | V_WORK_R_1_0          |   3946K|  2178M|       | 54733   (1)| 00:10:57 |
        |   9 |      UNION-ALL            |                       |        |       |       |            |          |
        |  10 |       TABLE ACCESS FULL   | WORK_BATCH            |   3910K|   331M|       | 53959   (1)| 00:10:48 |
        |  11 |       TABLE ACCESS FULL   | WORK_COMMON           |  35334 |  3761K|       |   774   (1)| 00:00:10 |
        ------------------------------------------------------------------------------------------------------------
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
           1 - SEL$F5BB74E1
           4 - SEL$838CAA44 / LR@SEL$2
           5 - SEL$838CAA44
           6 - SEL$838CAA44 / indexjoin$_alias$_001@SEL$838CAA44
           7 - SEL$838CAA44 / indexjoin$_alias$_002@SEL$838CAA44
           8 - SET$1        / VW@SEL$2
           9 - SET$1
          10 - SEL$3        / WORK_BATCH@SEL$3
          11 - SEL$4        / W@SEL$4
        Peeked Binds (identified by position):
        --------------------------------------
           1 - :1 (VARCHAR2(30), CSID=873): 'xxxxxx-xxx-SERVICEREQUEST-WORK-BD-DOCUMENTREQUEST ACE_BD_DR-166646'
        Note
        -----
           - Warning: basic plan statistics not available. These are only collected when:
            * hint 'gather_plan_statistics' is used for the statement or
            * parameter 'statistics_level' is set to 'ALL', at session or system level
      
        52 rows selected.
      
      
      


       

      Select sql_id,plan_hash_value,old_hash_value,child_number,OUTLINE_CATEGORY,CPU_TIME,ELAPSED_TIME,SQL_PROFILE from gv$sql where sql_id='1um96ykvtwrh4';
        SQL_ID        PLAN_HASH_VALUE OLD_HASH_VALUE CHILD_NUMBER OUTLINE_CA   CPU_TIME ELAPSED_TIME SQL_PROFILE
        ------------- --------------- -------------- ------------ ---------- ---------- ------------ ------------------------------
        1um96ykvtwrh4       377865450     3763442152            2                 81989       198629 SYS_SQLPROF_013fa7a18f7c0001
        1um96ykvtwrh4      1122713586     3763442152            0              44147289    172322738
        1um96ykvtwrh4      1122713586     3763442152            1            1905933242   6101663681
        1um96ykvtwrh4       377865450     3763442152            3                 55988       108690 SYS_SQLPROF_013fa7a18f7c0001
      
      
      


      FOR QUERY 2

       

      col BEGIN_INTERVAL_TIME for a26
       col end_INTERVAL_TIME for a26
       select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id,q.VERSION_COUNT, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode 
       from dba_hist_sqlstat q, dba_hist_snapshot s 
       where q.dbid = 4026476544 and q.sql_id = '4prk4w6sdtxa5'
       and q.snap_id = s.snap_id
       and s.begin_interval_time between sysdate-2 and sysdate 
       order by 1,s.snap_id desc;
       BEGIN_INTERVAL_TIME        END_INTERVAL_TIME             SNAP_ID       DBID SQL_ID        PLAN_HASH_VALUE OPTIMIZER_COST OPTIMIZER_
       -------------------------- -------------------------- ---------- ---------- ------------- --------------- -------------- ----------
       04-JUL-13 12.00.01.427 PM  04-JUL-13 01.00.02.453 PM       17346 4026476544 4prk4w6sdtxa5      3168544609          56710 ALL_ROWS
       04-JUL-13 12.00.01.430 PM  04-JUL-13 01.00.02.413 PM       17346 4026476544 4prk4w6sdtxa5       455675889            985 ALL_ROWS
       04-JUL-13 12.00.01.430 PM  04-JUL-13 01.00.02.413 PM       17346 4026476544 4prk4w6sdtxa5      3168544609          56710 ALL_ROWS
       04-JUL-13 12.00.01.430 PM  04-JUL-13 01.00.02.413 PM       17346 4026476544 4prk4w6sdtxa5       455675889            985 ALL_ROWS
       04-JUL-13 12.00.01.430 PM  04-JUL-13 01.00.02.413 PM       17346 4026476544 4prk4w6sdtxa5      3168544609          56710 ALL_ROWS
      
      
      

      OLD EXPLAIN PAL

       

      select * from table(dbms_xplan.display_awr('4prk4w6sdtxa5',3168544609,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
      
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        SQL_ID 4prk4w6sdtxa5
        --------------------
        SELECT PYID AS "pyID" ,  PXOBJCLASS AS "pxObjClass" ,  PZINSKEY AS
        "pzInsKey", PZINSKEY as "pxInsHandle"  FROM  V_WORK_R_1_0   WHERE (
        PYID = :1  ) AND (  pxObjClass = :2   )
        Plan hash value: 3168544609
        ---------------------------------------------------------------------------------------------------
        | Id  | Operation                      | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
        ---------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT               |                 |        |       | 53846 (100)|          |
        |   1 |  VIEW                          | V_WORK_R_1_0    |      2 |  1238 | 53846   (1)| 00:10:47 |
        |   2 |   UNION-ALL                    |                 |        |       |            |          |
        |   3 |    FILTER                      |                 |        |       |            |          |
        |   4 |     TABLE ACCESS FULL          | WORK_BATCH      |      1 |    67 | 53844   (1)| 00:10:47 |
        |   5 |    FILTER                      |                 |        |       |            |          |
        |   6 |     TABLE ACCESS BY INDEX ROWID| WORK_COMMON     |      1 |   578 |     2   (0)| 00:00:01 |
        |   7 |      INDEX RANGE SCAN          | AK1_WORK_COMMON |      1 |       |     1   (0)| 00:00:01 |
        ---------------------------------------------------------------------------------------------------
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
           1 - SET$1 / V_WORK_R_1_0@SEL$1
           2 - SET$1
           3 - SEL$2
           4 - SEL$2 / WORK_BATCH@SEL$2
           5 - SEL$3
           6 - SEL$3 / W@SEL$3
           7 - SEL$3 / W@SEL$3
        Peeked Binds (identified by position):
        --------------------------------------
           1 - :1 (VARCHAR2(30), CSID=873): 'CM-22180'
        Note
        -----
           - dynamic sampling used for this statement (level=2)
           - Warning: basic plan statistics not available. These are only collected when:
            * hint 'gather_plan_statistics' is used for the statement or
            * parameter 'statistics_level' is set to 'ALL', at session or system level
      
      
      
      

      NEW EXPLAIN PLAN IS
         
       

       select * from table(dbms_xplan.display_awr('4prk4w6sdtxa5',455675889,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        SQL_ID 4prk4w6sdtxa5
        --------------------
        SELECT PYID AS "pyID" ,  PXOBJCLASS AS "pxObjClass" ,  PZINSKEY AS
        "pzInsKey", PZINSKEY as "pxInsHandle"  FROM  V_WORK_R_1_0   WHERE (
        PYID = :1  ) AND (  pxObjClass = :2   )
        Plan hash value: 455675889
        --------------------------------------------------------------------------------------------------------------------------------------
        | Id  | Operation                            | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |
        --------------------------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT                     |                 |        |       |   985 (100)|          |        |      |            |
        |   1 |  PX COORDINATOR                      |                 |        |       |            |          |        |      |            |
        |   2 |   PX SEND QC (RANDOM)                | :TQ10001        |      2 |  1238 |   985   (1)| 00:00:12 |  Q1,01 | P->S | QC (RAND)  |
        |   3 |    BUFFER SORT                       |                 |      2 |  1238 |            |          |  Q1,01 | PCWP |            |
        |   4 |     VIEW                             | V_WORK_R_1_0    |      2 |  1238 |   985   (1)| 00:00:12 |  Q1,01 | PCWP |            |
        |   5 |      UNION-ALL                       |                 |        |       |            |          |  Q1,01 | PCWP |            |
        |   6 |       FILTER                         |                 |        |       |            |          |  Q1,01 | PCWC |            |
        |   7 |        PX BLOCK ITERATOR             |                 |      1 |    67 |   983   (1)| 00:00:12 |  Q1,01 | PCWC |            |
        |   8 |         TABLE ACCESS FULL            | WORK_BATCH      |      1 |    67 |   983   (1)| 00:00:12 |  Q1,01 | PCWP |            |
        |   9 |       BUFFER SORT                    |                 |        |       |            |          |  Q1,01 | PCWC |            |
        |  10 |        PX RECEIVE                    |                 |      1 |    59 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
        |  11 |         PX SEND ROUND-ROBIN          | :TQ10000        |      1 |    59 |     2   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
        |  12 |          FILTER                      |                 |        |       |            |          |        |      |            |
        |  13 |           TABLE ACCESS BY INDEX ROWID| WORK_COMMON     |      1 |    59 |     2   (0)| 00:00:01 |        |      |            |
        |  14 |            INDEX RANGE SCAN          | AK1_WORK_COMMON |      1 |       |     1   (0)| 00:00:01 |        |      |            |
        --------------------------------------------------------------------------------------------------------------------------------------
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
           1 - SEL$1
           4 - SET$1 / V_WORK_R_1_0@SEL$1
           5 - SET$1
           6 - SEL$2
           8 - SEL$2 / WORK_BATCH@SEL$2
          12 - SEL$3
          13 - SEL$3 / W@SEL$3
          14 - SEL$3 / W@SEL$3
        Peeked Binds (identified by position):
        --------------------------------------
           1 - :1 (VARCHAR2(30), CSID=873): 'AM-15042'
        Note
        -----
           - automatic DOP: Computed Degree of Parallelism is 64
           - SQL profile "SYS_SQLPROF_013fa79ff2ee0000" used for this statement
           - Warning: basic plan statistics not available. These are only collected when:
            * hint 'gather_plan_statistics' is used for the statement or
            * parameter 'statistics_level' is set to 'ALL', at session or system level
      
        53 rows selected.
      
      
      
      

      HINT USED IN EXPLAIN PLAN GIVEN BY SQL_PROFILE

       

      select
         extractvalue(value(d), '/hint') as outline_hints
         from
         xmltable('/*/outline_data/hint'
         passing (
         select
         xmltype(other_xml) as xmlval
         from
         dba_hist_sql_plan
         where
         sql_id = '&sql_id'
         and plan_hash_value = &plan_hash_value
         and other_xml is not null
        )) d;
         
         OUTLINE_HINTS
        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        IGNORE_OPTIM_EMBEDDED_HINTS
        OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
        DB_VERSION('11.2.0.2')
        OPT_PARAM('optimizer_dynamic_sampling' 5)
        ALL_ROWS
        SHARED(64)
        OUTLINE_LEAF(@"SEL$2")
        OUTLINE_LEAF(@"SEL$3")
        OUTLINE_LEAF(@"SET$1")
        OUTLINE_LEAF(@"SEL$1")
        NO_ACCESS(@"SEL$1" "V_WORK_R_1_0"@"SEL$1")
        INDEX_RS_ASC(@"SEL$3" "W"@"SEL$3" ("WORK_COMMON"."PYID"))
        FULL(@"SEL$2" "WORK_BATCH"@"SEL$2")
      
      
      

       

       

      col OUTLINE_CATEGORY for a10
        col SQL_PROFILE for a30
        select sql_id,plan_hash_value,old_hash_value,child_number,OUTLINE_CATEGORY,CPU_TIME,ELAPSED_TIME,SQL_PROFILE from gv$sql where sql_id='4prk4w6sdtxa5';
        SQL_ID        PLAN_HASH_VALUE OLD_HASH_VALUE CHILD_NUMBER OUTLINE_CA   CPU_TIME ELAPSED_TIME SQL_PROFILE
        ------------- --------------- -------------- ------------ ---------- ---------- ------------ ------------------------------
        4prk4w6sdtxa5      3168544609      353644632            0            2633226703   4.4271E+10
        4prk4w6sdtxa5       455675889      353644632            1              27225852    759830164 SYS_SQLPROF_013fa79ff2ee0000
        4prk4w6sdtxa5       455675889      353644632            2             119988890   9664144040 SYS_SQLPROF_013fa79ff2ee0000
        4prk4w6sdtxa5       455675889      353644632            3              85872011   5942746451 SYS_SQLPROF_013fa79ff2ee0000
        4prk4w6sdtxa5      3168544609      353644632            0             335776954    337580538
        4prk4w6sdtxa5      3168544609      353644632            1             399056329    401261240
        4prk4w6sdtxa5      3168544609      353644632            2             484240383    486679352
        4prk4w6sdtxa5      3168544609      353644632            3             401375979    492360355
        4prk4w6sdtxa5      3168544609      353644632            4             930830490   2.2156E+10
        4prk4w6sdtxa5      3168544609      353644632            5             403837610    472368680
        4prk4w6sdtxa5      3168544609      353644632            6            1136393235   2.5397E+10
        4prk4w6sdtxa5      3168544609      353644632            7             858015552   1.6636E+10
        4prk4w6sdtxa5       455675889      353644632            8              68884598   6001624174 SYS_SQLPROF_013fa79ff2ee0000
        4prk4w6sdtxa5       455675889      353644632            9              21730702    629077530 SYS_SQLPROF_013fa79ff2ee0000
        4prk4w6sdtxa5       455675889      353644632           10              93504893   8216578447 SYS_SQLPROF_013fa79ff2ee0000
      
      OTHER INFO 
      
        SQL> select table_name,owner,index_name,INDEX_TYPE from dba_indexes where table_name in ('WORK_BATCH','WORK_COMMON','LINK_RELATIONSHIP');
        TABLE_NAME                     OWNER                          INDEX_NAME                     INDEX_TYPE
        ------------------------------ ------------------------------ ------------------------------ ---------------------------
        LINK_RELATIONSHIP              ACE                            SYS_IL0000120332C00018$$       LOB
        WORK_BATCH                     ACE                            SYS_IL0000120308C00085$$       LOB
        WORK_COMMON                    ACE                            SYS_IL0000243612C00085$$       LOB
        LINK_RELATIONSHIP              ACE                            LINK_RELATIONSHIP_PK           NORMAL
        LINK_RELATIONSHIP              ACE                            AK1_LINK_RELATIONSHIP          NORMAL
        LINK_RELATIONSHIP              ACE                            RELATIONSHIP_REFFROM           NORMAL
        WORK_BATCH                     ACE                            WORK_BATCH_PK                  NORMAL
        WORK_BATCH                     ACE                            IDX1_WORK_BATCH                NORMAL
        WORK_COMMON                    ACE                            AK2_WORK_COMMON                NORMAL
        WORK_COMMON                    ACE                            AK1_WORK_COMMON                NORMAL
        WORK_COMMON                    ACE                            WORK_COMMON_PK                 NORMAL
      
        col object_name for a25
        select created,object_name,object_type,owner,LAST_DDL_TIME from dba_objects where object_name in ('WORK_BATCH','WORK_COMMON','WORK_BATCH_PK','WORK_COMMON_PK','AK1_LINK_RELATIONSHIP','RELATIONSHIP_REFFROM')
        ;
      
          2
        CREATED     OBJECT_NAME               OBJECT_TYPE         OWNER                          LAST_DDL_TI
        ----------- ------------------------- ------------------- ------------------------------ -----------
        28-JUN-2012 WORK_BATCH                TABLE               ACE                            15-JUN-2013
        28-JUN-2012 WORK_BATCH_PK             INDEX               ACE                            29-MAY-2013
        30-NOV-2012 RELATIONSHIP_REFFROM      INDEX               ACE                            03-JUL-2013
        23-FEB-2013 AK1_LINK_RELATIONSHIP     INDEX               ACE                            23-FEB-2013
        15-JUN-2013 WORK_COMMON               TABLE               ACE                            15-JUN-2013
        15-JUN-2013 WORK_COMMON_PK            INDEX               ACE                            15-JUN-2013
        09-MAR-2013 WORK_BATCH                SYNONYM             ACE_USER                       09-MAR-2013
      
      
      
      
        col COLUMN_NAME for a20
        Select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name in ('WORK_BATCH','WORK_COMMON','LINK_RELATIONSHIP');
          
        INDEX_NAME                     TABLE_NAME                     COLUMN_NAME          COLUMN_POSITION
        ------------------------------ ------------------------------ -------------------- ---------------
        RELATIONSHIP_REFFROM           LINK_RELATIONSHIP              PXLINKEDREFFROM                    1
        AK1_LINK_RELATIONSHIP          LINK_RELATIONSHIP              PXLINKEDREFTO                      1
        LINK_RELATIONSHIP_PK           LINK_RELATIONSHIP              PZINSKEY                           1
        IDX1_WORK_BATCH                WORK_BATCH                     CAPTUREUNIQUEID                    1
        WORK_BATCH_PK                  WORK_BATCH                     PZINSKEY                           1
        WORK_COMMON_PK                 WORK_COMMON                    PZINSKEY                           1
        AK1_WORK_COMMON                WORK_COMMON                    PYID                               1
        AK2_WORK_COMMON                WORK_COMMON                    PXOBJCLASS                         1
      
        SQL>  select distinct tablespace_name from dba_tablespaces where tablespace_name like '%INDEX%';
        TABLESPACE_NAME
        ------------------------------
        ACE_INDEXES_M
        RBS_INDEXES_S
        SPS_INDEXES_S
        ACE_INDEXES_S
        ACE_INDEXES_L
        FI_INDEXES_S
        FI_INDEXES_M
        RBS_INDEXES_M
        8 rows selected.
      
         col SEARCH_CONDITION for a25
         col OWNER for a10
         select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION,INDEX_NAME from dba_constraints where TABLE_NAME in ('WORK_BATCH','WORK_COMMON','LINK_RELATIONSHIP')
         OWNER      CONSTRAINT_NAME                C TABLE_NAME                     SEARCH_CONDITION          INDEX_NAME
        ---------- ------------------------------ - ------------------------------ ------------------------- ------------------------------
        ACE        SYS_C00150550                  C WORK_COMMON                    "PZINSKEY" IS NOT NULL
        ACE        WORK_BATCH_PK                  P WORK_BATCH                                               WORK_BATCH_PK
        ACE        LINK_RELATIONSHIP_PK           P LINK_RELATIONSHIP                                        LINK_RELATIONSHIP_PK
        ACE        WORK_COMMON_PK                 P WORK_COMMON                                              WORK_COMMON_PK
      
        select attr_val hint
        from dba_sql_profiles p, sqlprof$attr h
        where p.signature = h.signature
        and name like ('SYS_SQLPROF_013fa7a18f7c0001')
        order by attr#
        /
      
      
         select LAST_ANALYZED,SAMPLE_SIZE,num_rows from dba_tables where table_name in ('WORK_BATCH','WORK_COMMON','LINK_RELATIONSHIP');
        LAST_ANALYZ SAMPLE_SIZE   NUM_ROWS
        ----------- ----------- ----------
        02-JUL-2013     4122880    4122880
        02-JUL-2013       81108      81108
        03-JUL-2013       40802      40802
      
        SQL> select count(pzinskey) from ace.WORK_BATCH;
        COUNT(PZINSKEY)
        ---------------
          4138044
        SQL> select count(pzinskey) from ace.WORK_COMMON;
        COUNT(PZINSKEY)
        ---------------
            40805