Tuning paramon.sql

I know no one really likes the term “tuning” these days, but it’s a short catchy word that gets the idea across. So I’ll just stick with it for the title of this post.

Note that this is one of those posts that’s not really supposed to be about how to solve a particular problem. It’s really just a story about a distraction that I ran into and I how I thought about getting around the issue and then ultimately resolving the root cause. Maybe you will find it instructive to see the process.

So I have this script that I use occasionally (paramon.sql) to see what parallel query slaves are doing. Unfortunately the script doesn’t have a header in it, but I’m pretty sure I lifted it from Randolf Geist. I can’t find it on his blog anywhere, but it looks like his style of writing SQL, and PX Query is something he’s written a lot about, so I’m pretty sure that’s where I got it. (Update: see Jonathan Lewis’s comment below attributing the script to Andy Brooker) Anyway, the script has worked great for me in the past but I recently noticed that it was really sluggish on a couple of 11gR2 DB’s running on Exadata. Here’s an example:

-bash-3.2$ !sql
sqlp
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 14 12:23:15 2013
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS
---------------- -------------------------- -------------------------- ------- ----------
dbm1             09-JAN-2013 03:25          14-JAN-2013 12:23             5.37     464246
 
SYS@dbm1> set SQLPROMPT "11.2.0.3> "
11.2.0.3>
11.2.0.3> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66
 
 
32 rows selected.
 
Elapsed: 00:00:23.11

So on this 11g DB it took 23 seconds to run the query. On one of my 10g DB’s though the performance was stellar.

[osborne@homer scripts]$ rlwrap sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 14 11:20:28 2013
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1024          19-DEC-2012 14:44 14-JAN-2013 11:20   25.86    2234138
 
SYS@LAB1024> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     19
      P001 AVAILABLE     20
      P002 AVAILABLE     21
      P003 AVAILABLE     22
      P004 AVAILABLE     23
      P005 AVAILABLE     24
      P006 AVAILABLE     25
      P007 AVAILABLE     26
      P008 AVAILABLE     27
      P009 AVAILABLE     28
      P010 AVAILABLE     29
      P011 AVAILABLE     30
      P012 AVAILABLE     31
      P013 AVAILABLE     32
      P014 AVAILABLE     33
      P015 AVAILABLE     36
      P016 AVAILABLE     37
      P017 AVAILABLE     38
      P018 AVAILABLE     39
      P019 AVAILABLE     40
      P020 AVAILABLE     41
      P021 AVAILABLE     42
      P022 AVAILABLE     43
      P023 AVAILABLE     44
      P024 AVAILABLE     45
      P025 AVAILABLE     46
      P026 AVAILABLE     47
      P027 AVAILABLE     48
      P028 AVAILABLE     49
      P029 AVAILABLE     50
      P030 AVAILABLE     51
      P031 AVAILABLE     52
 
 
32 rows selected.
 
Elapsed: 00:00:00.03

So it’s sub-second on the 10g DB and the relatively long 25-30 second execution time on 11g became irritating very quickly. Some day I’ll do a post about the different thought processes involved in solving problems, but for now just let me say that since I am a fairly experienced Oracle Guy, I usually indulge myself in a few quick checks on known suspects before jumping into any kind of methodical thinking. And in my experience, the most common cause of performance degradation is a plan change. That takes about 5 seconds to check, which I did, and sure enough the plan was different.

11.2.0.3> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 1
-------------------------------------
 select distinct v.inst_id              , x.server_name       ,
x.status as x_status       , x.pid as x_pid       , x.sid as x_sid
 , w2.sid as p_sid       , v.osuser       , v.schemaname       ,
w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      ,
v$session_wait w1      , v$session_wait w2  where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)  and   x.sid =
w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)
     and substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 2444082350
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |     6 (100)|          |        |      |            |
|   1 |  SORT UNIQUE                       |                 |     1 |   219 |     5 (100)| 00:00:01 |        |      |            |
|*  2 |   HASH JOIN OUTER                  |                 |     1 |   219 |     4 (100)| 00:00:01 |        |      |            |
|*  3 |    HASH JOIN OUTER                 |                 |     1 |   172 |     3 (100)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER                |                 |     1 |   125 |     3 (100)| 00:00:01 |        |      |            |
|*  5 |      FILTER                        |                 |       |       |            |          |        |      |            |
|*  6 |       HASH JOIN OUTER              |                 |     1 |    65 |     2 (100)| 00:00:01 |        |      |            |
|   7 |        VIEW                        | V$PX_PROCESS    |     1 |    36 |     1 (100)| 00:00:01 |        |      |            |
|*  8 |         HASH JOIN OUTER            |                 |     1 |   124 |     1 (100)| 00:00:01 |        |      |            |
|*  9 |          HASH JOIN                 |                 |     1 |    97 |     1 (100)| 00:00:01 |        |      |            |
|* 10 |           FIXED TABLE FULL         | X$KXFPDP        |     1 |    44 |     0   (0)|          |        |      |            |
|* 11 |           FIXED TABLE FULL         | X$KSUPR         |     1 |    53 |     0   (0)|          |        |      |            |
|  12 |          VIEW                      | V$SESSION       |     1 |    27 |     0   (0)|          |        |      |            |
|  13 |           NESTED LOOPS             |                 |     1 |   105 |     0   (0)|          |        |      |            |
|  14 |            NESTED LOOPS            |                 |     1 |    92 |     0   (0)|          |        |      |            |
|* 15 |             FIXED TABLE FULL       | X$KSUSE         |     1 |    66 |     0   (0)|          |        |      |            |
|* 16 |             FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |        |      |            |
|* 17 |            FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |        |      |            |
|  18 |        VIEW                        | V$LOCK          |     1 |    29 |     1 (100)| 00:00:01 |        |      |            |
|* 19 |         HASH JOIN                  |                 |     1 |    79 |     1 (100)| 00:00:01 |        |      |            |
|  20 |          MERGE JOIN CARTESIAN      |                 |   100 |  6700 |     0   (0)|          |        |      |            |
|* 21 |           FIXED TABLE FULL         | X$KSUSE         |     1 |    32 |     0   (0)|          |        |      |            |
|  22 |           BUFFER SORT              |                 |   100 |  3500 |     0   (0)|          |        |      |            |
|  23 |            FIXED TABLE FULL        | X$KSQRS         |   100 |  3500 |     0   (0)|          |        |      |            |
|  24 |          VIEW                      | GV$_LOCK        |    10 |   120 |     0   (0)|          |        |      |            |
|  25 |           UNION-ALL                |                 |       |       |            |          |        |      |            |
|* 26 |            FILTER                  |                 |       |       |            |          |        |      |            |
|  27 |             VIEW                   | GV$_LOCK1       |     2 |    24 |     0   (0)|          |        |      |            |
|  28 |              UNION-ALL             |                 |       |       |            |          |        |      |            |
|* 29 |               FIXED TABLE FULL     | X$KDNSSF        |     1 |    64 |     0   (0)|          |        |      |            |
|* 30 |               FIXED TABLE FULL     | X$KSQEQ         |     1 |    64 |     0   (0)|          |        |      |            |
|* 31 |            FIXED TABLE FULL        | X$KTADM         |     1 |    64 |     0   (0)|          |        |      |            |
|* 32 |            FIXED TABLE FULL        | X$KTATRFIL      |     1 |    64 |     0   (0)|          |        |      |            |
|* 33 |            FIXED TABLE FULL        | X$KTATRFSL      |     1 |    64 |     0   (0)|          |        |      |            |
|* 34 |            FIXED TABLE FULL        | X$KTATL         |     1 |    64 |     0   (0)|          |        |      |            |
|* 35 |            FIXED TABLE FULL        | X$KTSTUSC       |     1 |    64 |     0   (0)|          |        |      |            |
|* 36 |            FIXED TABLE FULL        | X$KTSTUSS       |     1 |    64 |     0   (0)|          |        |      |            |
|* 37 |            FIXED TABLE FULL        | X$KTSTUSG       |     1 |    64 |     0   (0)|          |        |      |            |
|* 38 |            FIXED TABLE FULL        | X$KTCXB         |     1 |    64 |     0   (0)|          |        |      |            |
|  39 |      PX COORDINATOR                |                 |     1 |    60 |     0   (0)|          |        |      |            |
|  40 |       PX SEND QC (RANDOM)          | :TQ10000        |     1 |   125 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|  41 |        VIEW                        | GV$SESSION      |       |       |            |          |  Q1,00 | PCWP |            |
|  42 |         NESTED LOOPS               |                 |     1 |   125 |     0   (0)|          |  Q1,00 | PCWP |            |
|  43 |          NESTED LOOPS              |                 |     1 |   112 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 44 |           FIXED TABLE FULL         | X$KSUSE         |     1 |    86 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 45 |           FIXED TABLE FIXED INDEX  | X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 46 |          FIXED TABLE FIXED INDEX   | X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |  Q1,00 | PCWP |            |
|  47 |     VIEW                           | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  48 |      NESTED LOOPS                  |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 49 |       FIXED TABLE FULL             | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 50 |       FIXED TABLE FIXED INDEX      | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
|  51 |    VIEW                            | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  52 |     NESTED LOOPS                   |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 53 |      FIXED TABLE FULL              | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 54 |      FIXED TABLE FIXED INDEX       | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("L"."SID"="W2"."SID")
   3 - access("X"."SID"="W1"."SID")
   4 - access("X"."SID"="V"."SID")
   5 - filter(NVL("L"."TYPE",'PS')='PS')
   6 - access("L"."ID2"=TO_NUMBER(SUBSTR("X"."SERVER_NAME",2)))
       filter("X"."SID"<>"L"."SID")
   8 - access("A"."KXFPDPSPID"="C"."PROCESS")
   9 - access("A"."KXFPDPSPID"="KSUPRPID")
  10 - filter(("A"."INST_ID"=USERENV('INSTANCE') AND BITAND("KXFPDPFLG",8)<>0 AND SUBSTR("A"."KXFPDPNAM",2,1)<>'Z' AND
              DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE') LIKE DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE')))
  11 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  15 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  16 - filter("S"."INDX"="W"."KSLWTSID")
  17 - filter("W"."KSLWTEVT"="E"."INDX")
  19 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."
              ADDR"))
  21 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  26 - filter(USERENV('INSTANCE') IS NOT NULL)
  29 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  30 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  31 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  32 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  33 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  34 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  35 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  36 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  37 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  38 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  44 - filter((BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  45 - filter("S"."INDX"="W"."KSLWTSID")
  46 - filter("W"."KSLWTEVT"="E"."INDX")
  49 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  50 - filter("S"."KSLWTEVT"="E"."INDX")
  53 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  54 - filter("S"."KSLWTEVT"="E"."INDX")
 
Note
-----
   - statement not queuable: gv$ statement
 
 
118 rows selected.
 
Elapsed: 00:00:00.06
 
=================================================
switching to 10g
=================================================
 
SYS@LAB1024> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 0
-------------------------------------
 select distinct v.inst_id              , x.server_name       , x.status as x_status       ,
x.pid as x_pid       , x.sid as x_sid       , w2.sid as p_sid       , v.osuser       ,
v.schemaname       , w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      , v$session_wait w1      ,
v$session_wait w2  where x.sid <> l.sid(+)  and   to_number (substr(x.server_name,2)) =
l.id2(+)  and   x.sid = w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)         and
substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 2160357371
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |       |       |     6 (100)|          |
|   1 |  SORT UNIQUE                      |                 |     1 |   219 |     5 (100)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                 |                 |     1 |   219 |     4 (100)| 00:00:01 |
|*  3 |    HASH JOIN OUTER                |                 |     1 |   172 |     3 (100)| 00:00:01 |
|*  4 |     HASH JOIN OUTER               |                 |     1 |   125 |     3 (100)| 00:00:01 |
|*  5 |      FILTER                       |                 |       |       |            |          |
|*  6 |       HASH JOIN OUTER             |                 |     1 |    65 |     2 (100)| 00:00:01 |
|   7 |        VIEW                       | V$PX_PROCESS    |     1 |    36 |     1 (100)| 00:00:01 |
|*  8 |         HASH JOIN OUTER           |                 |     1 |   106 |     1 (100)| 00:00:01 |
|*  9 |          HASH JOIN                |                 |     1 |    85 |     1 (100)| 00:00:01 |
|* 10 |           FIXED TABLE FULL        | X$KXFPDP        |     1 |    38 |     0   (0)|          |
|* 11 |           FIXED TABLE FULL        | X$KSUPR         |     1 |    47 |     0   (0)|          |
|  12 |          VIEW                     | V$SESSION       |     1 |    21 |     0   (0)|          |
|  13 |           NESTED LOOPS            |                 |     1 |    86 |     0   (0)|          |
|* 14 |            FIXED TABLE FULL       | X$KSUSE         |     1 |    73 |     0   (0)|          |
|* 15 |            FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |
|  16 |        VIEW                       | V$LOCK          |     1 |    29 |     1 (100)| 00:00:01 |
|  17 |         NESTED LOOPS              |                 |     1 |    79 |     1 (100)| 00:00:01 |
|* 18 |          HASH JOIN                |                 |     1 |    57 |     1 (100)| 00:00:01 |
|* 19 |           FIXED TABLE FULL        | X$KSUSE         |     1 |    32 |     0   (0)|          |
|* 20 |           VIEW                    | GV$_LOCK        |    10 |   250 |     0   (0)|          |
|  21 |            UNION-ALL              |                 |       |       |            |          |
|* 22 |             VIEW                  | GV$_LOCK1       |     2 |   178 |     0   (0)|          |
|  23 |              UNION-ALL            |                 |       |       |            |          |
|* 24 |               FIXED TABLE FULL    | X$KDNSSF        |     1 |   102 |     0   (0)|          |
|* 25 |               FIXED TABLE FULL    | X$KSQEQ         |     1 |   102 |     0   (0)|          |
|* 26 |             FIXED TABLE FULL      | X$KTADM         |     1 |   102 |     0   (0)|          |
|* 27 |             FIXED TABLE FULL      | X$KTATRFIL      |     1 |   102 |     0   (0)|          |
|* 28 |             FIXED TABLE FULL      | X$KTATRFSL      |     1 |   102 |     0   (0)|          |
|* 29 |             FIXED TABLE FULL      | X$KTATL         |     1 |   102 |     0   (0)|          |
|* 30 |             FIXED TABLE FULL      | X$KTSTUSC       |     1 |   102 |     0   (0)|          |
|* 31 |             FIXED TABLE FULL      | X$KTSTUSS       |     1 |   102 |     0   (0)|          |
|* 32 |             FIXED TABLE FULL      | X$KTSTUSG       |     1 |   102 |     0   (0)|          |
|* 33 |             FIXED TABLE FULL      | X$KTCXB         |     1 |   102 |     0   (0)|          |
|* 34 |          FIXED TABLE FIXED INDEX  | X$KSQRS (ind:1) |     1 |    22 |     0   (0)|          |
|  35 |      VIEW                         | GV$SESSION      |     1 |    60 |     0   (0)|          |
|  36 |       NESTED LOOPS                |                 |     1 |   112 |     0   (0)|          |
|* 37 |        FIXED TABLE FULL           | X$KSUSE         |     1 |    99 |     0   (0)|          |
|* 38 |        FIXED TABLE FIXED INDEX    | X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |
|  39 |     VIEW                          | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |
|  40 |      NESTED LOOPS                 |                 |     1 |   125 |     0   (0)|          |
|* 41 |       FIXED TABLE FULL            | X$KSUSECST      |     1 |    78 |     0   (0)|          |
|* 42 |       FIXED TABLE FIXED INDEX     | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |
|  43 |    VIEW                           | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |
|  44 |     NESTED LOOPS                  |                 |     1 |   125 |     0   (0)|          |
|* 45 |      FIXED TABLE FULL             | X$KSUSECST      |     1 |    78 |     0   (0)|          |
|* 46 |      FIXED TABLE FIXED INDEX      | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("L"."SID"="W2"."SID")
   3 - access("X"."SID"="W1"."SID")
   4 - access("X"."SID"="V"."SID")
   5 - filter(NVL("L"."TYPE",'PS')='PS')
   6 - access("L"."ID2"=TO_NUMBER(SUBSTR("X"."SERVER_NAME",2)))
       filter("X"."SID"<>"L"."SID")
   8 - access("A"."KXFPDPSPID"="C"."PROCESS")
   9 - access("A"."KXFPDPSPID"="KSUPRPID")
  10 - filter((SUBSTR("A"."KXFPDPNAM",2,1)<>'Z' AND "A"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("KXFPDPFLG",8)<>0 AND DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE') LIKE
              NVL('',DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE'))))
  11 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  14 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0))
  15 - filter("S"."KSUSEOPC"="E"."INDX")
  18 - access("SADDR"="S"."ADDR")
  19 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  20 - filter("INST_ID"=USERENV('INSTANCE'))
  22 - filter("INST_ID"=USERENV('INSTANCE'))
  24 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  25 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  26 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  27 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  28 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  29 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  30 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  31 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  32 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  33 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0))
  34 - filter("RADDR"="R"."ADDR")
  37 - filter((BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  38 - filter("S"."KSUSEOPC"="E"."INDX")
  41 - filter(("S"."KSUSSSEQ"<>0 AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  42 - filter("S"."KSUSSOPC"="E"."INDX")
  45 - filter(("S"."KSUSSSEQ"<>0 AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  46 - filter("S"."KSUSSOPC"="E"."INDX")
 
 
107 rows selected.

Clearly the plans are different because the plan_hash_values are different, but the plans are a little long and on the ugly side. And since I had wanted to use the script, I decided to see if I could just get the old plan back. An easy way to do this is to use Carlos Seirra’s coe_xfr_sql_profile.sql script which is distributed by Oracle on MOS as part of SQLT. (note that I renamed the script coe.sql because the full name was too long for me to type) To use the script, all you have to do is run it and give it a sql_id and a plan_hash_value and it will create another script which you can use to create a SQL Profile with all the necessary hints to recreate the plan. So I did that.

SYS@LAB1024> @coe
 
Parameter 1:
SQL_ID (required)
 
Enter value for 1: 9b2hgfwj6bnu1
 
 
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2160357371        .018
 
Parameter 2:
PLAN_HASH_VALUE (required)
 
Enter value for 2: 2160357371
 
Values passed:
~~~~~~~~~~~~~
SQL_ID         : "9b2hgfwj6bnu1"
PLAN_HASH_VALUE: "2160357371"
 
 
Execute coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql
on TARGET system in order to create a custom SQL Profile
with plan 2160357371 linked to adjusted sql_text.
 
 
COE_XFR_SQL_PROFILE completed.

Now all I need to do is run the script (coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql) on the 11g DB to create the SQL Profile and then test to make sure the plan could be reproduced and that it fixed the performance issue. Note that the output of the specific coe script is a bit verbose, but it gets the job done. :)

11.2.0.3> !scp homer:/home/osborne/scripts/coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql .
osborne@homer's password: 
coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql                                                                                                                     100% 6862     6.7KB/s   00:00    
 
11.2.0.3> @coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql
11.2.0.3> REM
11.2.0.3> REM $Header: 215187.1 coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql 11.4.1.4 2013/01/14 csierra $
11.2.0.3> REM
11.2.0.3> REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
11.2.0.3> REM
11.2.0.3> REM AUTHOR
11.2.0.3> REM   carlos.sierra@oracle.com
11.2.0.3> REM
11.2.0.3> REM SCRIPT
11.2.0.3> REM   coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql
11.2.0.3> REM
11.2.0.3> REM DESCRIPTION
11.2.0.3> REM   This script is generated by coe_xfr_sql_profile.sql
11.2.0.3> REM   It contains the SQL*Plus commands to create a custom
11.2.0.3> REM   SQL Profile for SQL_ID 9b2hgfwj6bnu1 based on plan hash
11.2.0.3> REM   value 2160357371.
11.2.0.3> REM   The custom SQL Profile to be created by this script
11.2.0.3> REM   will affect plans for SQL commands with signature
11.2.0.3> REM   matching the one for SQL Text below.
11.2.0.3> REM   Review SQL Text and adjust accordingly.
11.2.0.3> REM
11.2.0.3> REM PARAMETERS
11.2.0.3> REM   None.
11.2.0.3> REM
11.2.0.3> REM EXAMPLE
11.2.0.3> REM   SQL> START coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql;
11.2.0.3> REM
11.2.0.3> REM NOTES
11.2.0.3> REM   1. Should be run as SYSTEM or SYSDBA.
11.2.0.3> REM   2. User must have CREATE ANY SQL PROFILE privilege.
11.2.0.3> REM   3. SOURCE and TARGET systems can be the same or similar.
11.2.0.3> REM   4. To drop this custom SQL Profile after it has been created:
11.2.0.3> REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9b2hgfwj6bnu1_2160357371');
11.2.0.3> REM   5. Be aware that using DBMS_SQLTUNE requires a license
11.2.0.3> REM      for the Oracle Tuning Pack.
11.2.0.3> REM
11.2.0.3> WHENEVER SQLERROR EXIT SQL.SQLCODE;
11.2.0.3> REM
11.2.0.3> VAR signature NUMBER;
11.2.0.3> REM
11.2.0.3> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6          select distinct v.inst_id
  7  , x.server_name
  8               , x.status as x_status
  9               , x.pid as x_pid
 10               , x.sid as x_sid
 11               , w2.sid as p_sid
 12               , v.osuser
 13               , v.schemaname
 14               , w1.event as child_wait
 15               , w2.event as parent_wait
 16          from  v$px_process x
 17              , v$lock l
 18              , gv$session v
 19              , v$session_wait w1
 20              , v$session_wait w2
 21          where x.sid <> l.sid(+)
 22          and   to_number (substr(x.server_name,2)) = l.id2(+)
 23          and   x.sid = w1.sid(+)
 24          and   l.sid = w2.sid(+)
 25          and   x.sid = v.sid(+)
 26          and   nvl(l.type,'PS') = 'PS'
 27  and   x.status like nvl('',x.status)
 28  and substr(x.server_name,2,1) != 'Z'
 29          order by p_sid, 1,2
 30  ]';
 31  h := SYS.SQLPROF_ATTR(
 32  q'[BEGIN_OUTLINE_DATA]',
 33  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 34  q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
 35  q'[ALL_ROWS]',
 36  q'[OUTLINE_LEAF(@"SEL$68B588A0")]',
 37  q'[MERGE(@"SEL$7")]',
 38  q'[OUTLINE_LEAF(@"SEL$71D7A081")]',
 39  q'[MERGE(@"SEL$C8360722")]',
 40  q'[OUTLINE_LEAF(@"SEL$13")]',
 41  q'[OUTLINE_LEAF(@"SEL$14")]',
 42  q'[OUTLINE_LEAF(@"SET$2")]',
 43  q'[OUTLINE_LEAF(@"SEL$42DFC41A")]',
 44  q'[MERGE(@"SEL$12")]',
 45  q'[OUTLINE_LEAF(@"SEL$15")]',
 46  q'[OUTLINE_LEAF(@"SEL$16")]',
 47  q'[OUTLINE_LEAF(@"SEL$17")]',
 48  q'[OUTLINE_LEAF(@"SEL$18")]',
 49  q'[OUTLINE_LEAF(@"SEL$19")]',
 50  q'[OUTLINE_LEAF(@"SEL$20")]',
 51  q'[OUTLINE_LEAF(@"SEL$21")]',
 52  q'[OUTLINE_LEAF(@"SEL$22")]',
 53  q'[OUTLINE_LEAF(@"SET$1")]',
 54  q'[OUTLINE_LEAF(@"SEL$2F35337B")]',
 55  q'[MERGE(@"SEL$0EE6DB63")]',
 56  q'[OUTLINE_LEAF(@"SEL$23")]',
 57  q'[OUTLINE_LEAF(@"SEL$DC3B0B0A")]',
 58  q'[MERGE(@"SEL$25")]',
 59  q'[OUTLINE_LEAF(@"SEL$FFAA604D")]',
 60  q'[MERGE(@"SEL$27")]',
 61  q'[OUTLINE_LEAF(@"SEL$1")]',
 62  q'[OUTLINE(@"SEL$6")]',
 63  q'[OUTLINE(@"SEL$7")]',
 64  q'[OUTLINE(@"SEL$2")]',
 65  q'[OUTLINE(@"SEL$C8360722")]',
 66  q'[MERGE(@"SEL$7286615E")]',
 67  q'[OUTLINE(@"SEL$13")]',
 68  q'[OUTLINE(@"SEL$14")]',
 69  q'[OUTLINE(@"SET$2")]',
 70  q'[OUTLINE(@"SEL$11")]',
 71  q'[OUTLINE(@"SEL$12")]',
 72  q'[OUTLINE(@"SEL$15")]',
 73  q'[OUTLINE(@"SEL$16")]',
 74  q'[OUTLINE(@"SEL$17")]',
 75  q'[OUTLINE(@"SEL$18")]',
 76  q'[OUTLINE(@"SEL$19")]',
 77  q'[OUTLINE(@"SEL$20")]',
 78  q'[OUTLINE(@"SEL$21")]',
 79  q'[OUTLINE(@"SEL$22")]',
 80  q'[OUTLINE(@"SET$1")]',
 81  q'[OUTLINE(@"SEL$8")]',
 82  q'[OUTLINE(@"SEL$0EE6DB63")]',
 83  q'[MERGE(@"SEL$10")]',
 84  q'[OUTLINE(@"SEL$23")]',
 85  q'[OUTLINE(@"SEL$24")]',
 86  q'[OUTLINE(@"SEL$25")]',
 87  q'[OUTLINE(@"SEL$26")]',
 88  q'[OUTLINE(@"SEL$27")]',
 89  q'[OUTLINE(@"SEL$1")]',
 90  q'[OUTLINE(@"SEL$3")]',
 91  q'[OUTLINE(@"SEL$7286615E")]',
 92  q'[MERGE(@"SEL$5")]',
 93  q'[OUTLINE(@"SEL$9")]',
 94  q'[OUTLINE(@"SEL$10")]',
 95  q'[OUTLINE(@"SEL$4")]',
 96  q'[OUTLINE(@"SEL$5")]',
 97  q'[NO_ACCESS(@"SEL$1" "X"@"SEL$1")]',
 98  q'[NO_ACCESS(@"SEL$1" "L"@"SEL$1")]',
 99  q'[NO_ACCESS(@"SEL$1" "V"@"SEL$1")]',
100  q'[NO_ACCESS(@"SEL$1" "W1"@"SEL$1")]',
101  q'[NO_ACCESS(@"SEL$1" "W2"@"SEL$1")]',
102  q'[LEADING(@"SEL$1" "X"@"SEL$1" "L"@"SEL$1" "V"@"SEL$1" "W1"@"SEL$1" "W2"@"SEL$1")]',
103  q'[USE_HASH(@"SEL$1" "L"@"SEL$1")]',
104  q'[USE_HASH(@"SEL$1" "V"@"SEL$1")]',
105  q'[USE_HASH(@"SEL$1" "W1"@"SEL$1")]',
106  q'[USE_HASH(@"SEL$1" "W2"@"SEL$1")]',
107  q'[FULL(@"SEL$71D7A081" "A"@"SEL$3")]',
108  q'[FULL(@"SEL$71D7A081" "X$KSUPR"@"SEL$5")]',
109  q'[NO_ACCESS(@"SEL$71D7A081" "C"@"SEL$3")]',
110  q'[LEADING(@"SEL$71D7A081" "A"@"SEL$3" "X$KSUPR"@"SEL$5" "C"@"SEL$3")]',
111  q'[USE_HASH(@"SEL$71D7A081" "X$KSUPR"@"SEL$5")]',
112  q'[USE_HASH(@"SEL$71D7A081" "C"@"SEL$3")]',
113  q'[FULL(@"SEL$2F35337B" "S"@"SEL$9")]',
114  q'[NO_ACCESS(@"SEL$2F35337B" "GV$_LOCK"@"SEL$10")]',
115  q'[FULL(@"SEL$2F35337B" "R"@"SEL$9")]',
116  q'[LEADING(@"SEL$2F35337B" "S"@"SEL$9" "GV$_LOCK"@"SEL$10" "R"@"SEL$9")]',
117  q'[USE_HASH(@"SEL$2F35337B" "GV$_LOCK"@"SEL$10")]',
118  q'[USE_NL(@"SEL$2F35337B" "R"@"SEL$9")]',
119  q'[FULL(@"SEL$23" "S"@"SEL$23")]',
120  q'[FULL(@"SEL$23" "E"@"SEL$23")]',
121  q'[LEADING(@"SEL$23" "S"@"SEL$23" "E"@"SEL$23")]',
122  q'[USE_NL(@"SEL$23" "E"@"SEL$23")]',
123  q'[FULL(@"SEL$DC3B0B0A" "S"@"SEL$25")]',
124  q'[FULL(@"SEL$DC3B0B0A" "E"@"SEL$25")]',
125  q'[LEADING(@"SEL$DC3B0B0A" "S"@"SEL$25" "E"@"SEL$25")]',
126  q'[USE_NL(@"SEL$DC3B0B0A" "E"@"SEL$25")]',
127  q'[FULL(@"SEL$FFAA604D" "S"@"SEL$27")]',
128  q'[FULL(@"SEL$FFAA604D" "E"@"SEL$27")]',
129  q'[LEADING(@"SEL$FFAA604D" "S"@"SEL$27" "E"@"SEL$27")]',
130  q'[USE_NL(@"SEL$FFAA604D" "E"@"SEL$27")]',
131  q'[FULL(@"SEL$22" "X$KTCXB"@"SEL$22")]',
132  q'[FULL(@"SEL$21" "X$KTSTUSG"@"SEL$21")]',
133  q'[FULL(@"SEL$20" "X$KTSTUSS"@"SEL$20")]',
134  q'[FULL(@"SEL$19" "X$KTSTUSC"@"SEL$19")]',
135  q'[FULL(@"SEL$18" "X$KTATL"@"SEL$18")]',
136  q'[FULL(@"SEL$17" "X$KTATRFSL"@"SEL$17")]',
137  q'[FULL(@"SEL$16" "X$KTATRFIL"@"SEL$16")]',
138  q'[FULL(@"SEL$15" "X$KTADM"@"SEL$15")]',
139  q'[NO_ACCESS(@"SEL$42DFC41A" "GV$_LOCK1"@"SEL$12")]',
140  q'[FULL(@"SEL$14" "X$KSQEQ"@"SEL$14")]',
141  q'[FULL(@"SEL$13" "X$KDNSSF"@"SEL$13")]',
142  q'[FULL(@"SEL$68B588A0" "S"@"SEL$7")]',
143  q'[FULL(@"SEL$68B588A0" "E"@"SEL$7")]',
144  q'[LEADING(@"SEL$68B588A0" "S"@"SEL$7" "E"@"SEL$7")]',
145  q'[USE_NL(@"SEL$68B588A0" "E"@"SEL$7")]',
146  q'[END_OUTLINE_DATA]');
147  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
148  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
149  sql_text    => sql_txt,
150  profile     => h,
151  name        => 'coe_9b2hgfwj6bnu1_2160357371',
152  description => 'coe 9b2hgfwj6bnu1 2160357371 '||:signature||'',
153  category    => 'DEFAULT',
154  validate    => TRUE,
155  replace     => TRUE,
156  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
157  END;
158  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.04
11.2.0.3> WHENEVER SQLERROR CONTINUE
11.2.0.3> SET ECHO OFF;
 
            SIGNATURE
---------------------
  6396643439679565602
 
 
... manual custom SQL Profile has been created
 
 
COE_XFR_SQL_PROFILE_9b2hgfwj6bnu1_2160357371 completed
11.2.0.3> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66
 
 
32 rows selected.
 
Elapsed: 00:00:00.65
11.2.0.3> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 1
-------------------------------------
 select distinct v.inst_id              , x.server_name       ,
x.status as x_status       , x.pid as x_pid       , x.sid as x_sid
 , w2.sid as p_sid       , v.osuser       , v.schemaname       ,
w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      ,
v$session_wait w1      , v$session_wait w2  where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)  and   x.sid =
w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)
     and substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 1754705978
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |       |       |     6 (100)|          |        |      |            |
|   1 |  SORT UNIQUE                      |                 |     1 |   219 |     5 (100)| 00:00:01 |        |      |            |
|*  2 |   HASH JOIN OUTER                 |                 |     1 |   219 |     4 (100)| 00:00:01 |        |      |            |
|*  3 |    HASH JOIN OUTER                |                 |     1 |   172 |     3 (100)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER               |                 |     1 |   125 |     3 (100)| 00:00:01 |        |      |            |
|*  5 |      FILTER                       |                 |       |       |            |          |        |      |            |
|*  6 |       HASH JOIN OUTER             |                 |     1 |    65 |     2 (100)| 00:00:01 |        |      |            |
|   7 |        VIEW                       | V$PX_PROCESS    |     1 |    36 |     1 (100)| 00:00:01 |        |      |            |
|*  8 |         HASH JOIN OUTER           |                 |     1 |   124 |     1 (100)| 00:00:01 |        |      |            |
|*  9 |          HASH JOIN                |                 |     1 |    97 |     1 (100)| 00:00:01 |        |      |            |
|* 10 |           FIXED TABLE FULL        | X$KXFPDP        |     1 |    44 |     0   (0)|          |        |      |            |
|* 11 |           FIXED TABLE FULL        | X$KSUPR         |     1 |    53 |     0   (0)|          |        |      |            |
|  12 |          VIEW                     | V$SESSION       |     1 |    27 |     0   (0)|          |        |      |            |
|  13 |           NESTED LOOPS            |                 |     1 |   105 |     0   (0)|          |        |      |            |
|  14 |            NESTED LOOPS           |                 |     1 |    79 |     0   (0)|          |        |      |            |
|* 15 |             FIXED TABLE FULL      | X$KSUSE         |     1 |    66 |     0   (0)|          |        |      |            |
|  16 |             FIXED TABLE FULL      | X$KSLED         |   100 |  1300 |     0   (0)|          |        |      |            |
|* 17 |            FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |        |      |            |
|  18 |        VIEW                       | V$LOCK          |     1 |    29 |     1 (100)| 00:00:01 |        |      |            |
|  19 |         NESTED LOOPS              |                 |     1 |    92 |     1 (100)| 00:00:01 |        |      |            |
|* 20 |          HASH JOIN                |                 |     1 |    57 |     1 (100)| 00:00:01 |        |      |            |
|* 21 |           FIXED TABLE FULL        | X$KSUSE         |     1 |    32 |     0   (0)|          |        |      |            |
|* 22 |           VIEW                    | GV$_LOCK        |    10 |   250 |     0   (0)|          |        |      |            |
|  23 |            UNION-ALL              |                 |       |       |            |          |        |      |            |
|* 24 |             VIEW                  | GV$_LOCK1       |     2 |   178 |     0   (0)|          |        |      |            |
|  25 |              UNION-ALL            |                 |       |       |            |          |        |      |            |
|* 26 |               FIXED TABLE FULL    | X$KDNSSF        |     1 |   102 |     0   (0)|          |        |      |            |
|* 27 |               FIXED TABLE FULL    | X$KSQEQ         |     1 |   102 |     0   (0)|          |        |      |            |
|* 28 |             FIXED TABLE FULL      | X$KTADM         |     1 |   102 |     0   (0)|          |        |      |            |
|* 29 |             FIXED TABLE FULL      | X$KTATRFIL      |     1 |   102 |     0   (0)|          |        |      |            |
|* 30 |             FIXED TABLE FULL      | X$KTATRFSL      |     1 |   102 |     0   (0)|          |        |      |            |
|* 31 |             FIXED TABLE FULL      | X$KTATL         |     1 |   102 |     0   (0)|          |        |      |            |
|* 32 |             FIXED TABLE FULL      | X$KTSTUSC       |     1 |   102 |     0   (0)|          |        |      |            |
|* 33 |             FIXED TABLE FULL      | X$KTSTUSS       |     1 |   102 |     0   (0)|          |        |      |            |
|* 34 |             FIXED TABLE FULL      | X$KTSTUSG       |     1 |   102 |     0   (0)|          |        |      |            |
|* 35 |             FIXED TABLE FULL      | X$KTCXB         |     1 |   102 |     0   (0)|          |        |      |            |
|* 36 |          FIXED TABLE FULL         | X$KSQRS         |     1 |    35 |     0   (0)|          |        |      |            |
|  37 |      PX COORDINATOR               |                 |     1 |    60 |     0   (0)|          |        |      |            |
|  38 |       PX SEND QC (RANDOM)         | :TQ10000        |     1 |   125 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|  39 |        VIEW                       | GV$SESSION      |       |       |            |          |  Q1,00 | PCWP |            |
|  40 |         NESTED LOOPS              |                 |     1 |   125 |     0   (0)|          |  Q1,00 | PCWP |            |
|  41 |          NESTED LOOPS             |                 |    25 |  2475 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 42 |           FIXED TABLE FULL        | X$KSUSE         |     1 |    86 |     0   (0)|          |  Q1,00 | PCWP |            |
|  43 |           FIXED TABLE FULL        | X$KSLED         |   100 |  1300 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 44 |          FIXED TABLE FIXED INDEX  | X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|  45 |     VIEW                          | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  46 |      NESTED LOOPS                 |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 47 |       FIXED TABLE FULL            | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 48 |       FIXED TABLE FIXED INDEX     | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
|  49 |    VIEW                           | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  50 |     NESTED LOOPS                  |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 51 |      FIXED TABLE FULL             | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 52 |      FIXED TABLE FIXED INDEX      | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("L"."SID"="W2"."SID")
   3 - access("X"."SID"="W1"."SID")
   4 - access("X"."SID"="V"."SID")
   5 - filter(NVL("L"."TYPE",'PS')='PS')
   6 - access("L"."ID2"=TO_NUMBER(SUBSTR("X"."SERVER_NAME",2)))
       filter("X"."SID"<>"L"."SID")
   8 - access("A"."KXFPDPSPID"="C"."PROCESS")
   9 - access("A"."KXFPDPSPID"="KSUPRPID")
  10 - filter(("A"."INST_ID"=USERENV('INSTANCE') AND BITAND("KXFPDPFLG",8)<>0 AND SUBSTR("A"."KXFPDPNAM",2,1)<>'Z' AND
              DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE') LIKE NVL('',DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN
              USE','AVAILABLE'))))
  11 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  15 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  17 - filter(("S"."INDX"="W"."KSLWTSID" AND "W"."KSLWTEVT"="E"."INDX"))
  20 - access("SADDR"="S"."ADDR")
  21 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  22 - filter("INST_ID"=USERENV('INSTANCE'))
  24 - filter("INST_ID"=USERENV('INSTANCE'))
  26 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  27 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  28 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  29 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  30 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  31 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  32 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  33 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  34 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  35 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0))
  36 - filter(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR"))
  42 - filter((BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  44 - filter(("S"."INDX"="W"."KSLWTSID" AND "W"."KSLWTEVT"="E"."INDX"))
  47 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  48 - filter("S"."KSLWTEVT"="E"."INDX")
  51 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  52 - filter("S"."KSLWTEVT"="E"."INDX")
 
Note
-----
   - statement not queuable: gv$ statement
   - SQL profile coe_9b2hgfwj6bnu1_2160357371 used for this statement
 
 
117 rows selected.
 
Elapsed: 00:00:00.04

So as you can see, the statement now executes in under a second (0.65) and uses the original plan as encouraged by the newly created SQL Profile. You can also see in the note section of xplan output that the SQL Profile was used. This all took only a couple of minutes by the way and allowed me to continue my investigation on the parallel query issue I was interested in without too much distraction due to the sidetrack.

But that’s not the end of the story. After I was finished messing with the PX stuff, I wanted to know how and why the plan had changed in the first place and so I looked into it further. The first thing I did was to look at the Report SQL Monitor output to see where the bad plan was spending all it’s time.

11.2.0.3> @sm
Enter value for sid: 
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for sql_exec_id: 
 
       SID    SERIAL# SQL_ID        SQL_EXEC_ID SQL_EXEC_START       PLAN_HASH_VALUE      ETIME BUFFER_GETS DISK_READS
---------- ---------- ------------- ----------- -------------------- --------------- ---------- ----------- ----------
       397        955                  16777230 14-Jan-13 13:22:42        2444082350  22.895054           0       70579
 
11.2.0.3> @rsm
Enter value for sid: 397
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for sql_exec_id: 16777230
 
REPORT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select distinct v.inst_id , x.server_name , x.status as x_status , x.pid as x_pid , x.sid as x_sid , w2.sid as p_sid , v.osuser , v.schemaname , w1.event as child_wait , w2.event as parent_wait from v$px_process x , v$lock l , gv$session v , v$session_wait w1 , v$session_wait w2 where x.sid <> l.sid(+) and to_number (substr(x.server_name,2)) = l.id2(+) and x.sid = w1.sid(+) an
d l.sid = w2.sid(+) and x.sid = v.sid(+) and nvl(l.type,'PS') = 'PS' and x.status like nvl('',x.status) and
substr(x.server_name,2,1) != 'Z' order by p_sid, 1,2
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (397:955)
 SQL ID              :  9b2hgfwj6bnu1
 SQL Execution ID    :  16777230
 Execution Started   :  01/14/2013 13:22:42
 First Refresh Time  :  01/14/2013 13:22:48
 Last Refresh Time   :  01/14/2013 13:23:05
 Duration            :  23s
 Module/Action       :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)
 Fetch Calls         :  4
 
Global Stats
=================================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Read | Read  | Write | Write |  Cell   |
| Time(s) | Time(s) | Waits(s) | Calls | Reqs | Bytes | Reqs  | Bytes | Offload |
=================================================================================
|      23 |      15 |     8.39 |     4 | 7057 | 827MB |  7056 | 827MB | -49.25% |
=================================================================================
 
Parallel Execution Details (DOP=2 , Servers Allocated=2)
=======================================================================================================================================
|      Name      | Type | Server# | Elapsed |   Cpu   |    IO    | Read | Read  | Write | Write |  Cell   |        Wait Events        |
|                |      |         | Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Reqs  | Bytes | Offload |        (sample #)         |
=======================================================================================================================================
| PX Coordinator | QC   |         |      23 |      15 |     8.39 | 7057 | 827MB |  7056 | 827MB | -49.25% | direct path read temp (7) |
=======================================================================================================================================
 
Instance Drill-Down
================================================================================================================================
| Instance | Process Names | Elapsed |   Cpu   |    IO    | Read | Read  | Write | Write |  Cell   | Wait Events               |
|          |               | Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Reqs  | Bytes | Offload |                           |
================================================================================================================================
|    1     | QC            |      23 |      15 |     8.39 | 7057 | 827MB |  7056 | 827MB | -49.25% | direct path read temp (7) |
================================================================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=2444082350)
==============================================================================================================================================================================================================
| Id |              Operation               |      Name       |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Temp  | Activity |      Activity Detail      |
|    |                                      |                 | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |        (# samples)      |
==============================================================================================================================================================================================================
|  0 | SELECT STATEMENT                     |                 |         |      |         1 |    +23 |     1 |       32 |      |       |       |       |       |       |          |                         |
|  1 |   SORT UNIQUE                        |                 |       1 |    5 |         1 |    +23 |     1 |       32 |      |       |       |       |  4096 |       |          |                         |
|  2 |    HASH JOIN OUTER                   |                 |       1 |    4 |         1 |    +23 |     1 |       32 |      |       |       |       |  409K |       |          |                         |
|  3 |     HASH JOIN OUTER                  |                 |       1 |    3 |         1 |    +23 |     1 |       32 |      |       |       |       |  422K |       |          |                         |
|  4 |      HASH JOIN OUTER                 |                 |       1 |    3 |         1 |    +23 |     1 |       32 |      |       |       |       |  443K |       |          |                         |
|  5 |       FILTER                         |                 |         |      |         1 |    +23 |     1 |       32 |      |       |       |       |       |       |          |                         |
|  6 |        HASH JOIN OUTER               |                 |       1 |    2 |        18 |     +6 |     1 |       32 |      |       |       |       |    1M |       |          |                         |
|  7 |         VIEW                         | V$PX_PROCESS    |       1 |    1 |         1 |     +6 |     1 |       32 |      |       |       |       |       |       |          |                         |
|  8 |          HASH JOIN OUTER             |                 |       1 |    1 |         1 |     +6 |     1 |       32 |      |       |       |       |    1M |       |          |                         |
|  9 |           HASH JOIN                  |                 |       1 |    1 |         1 |     +6 |     1 |       32 |      |       |       |       |    1M |       |          |                         |
| 10 |            FIXED TABLE FULL          | X$KXFPDP        |       1 |      |         1 |     +6 |     1 |       32 |      |       |       |       |       |       |          |                         |
| 11 |            FIXED TABLE FULL          | X$KSUPR         |       1 |      |         1 |     +6 |     1 |       89 |      |       |       |       |       |       |          |                         |
| 12 |           VIEW                       | V$SESSION       |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 13 |            NESTED LOOPS              |                 |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 14 |             NESTED LOOPS             |                 |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 15 |              FIXED TABLE FULL        | X$KSUSE         |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 16 |              FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) |       1 |      |         1 |     +6 |    55 |       55 |      |       |       |       |       |       |          |                         |
| 17 |             FIXED TABLE FIXED INDEX  | X$KSLED (ind:2) |       1 |      |         1 |     +6 |    55 |       55 |      |       |       |       |       |       |          |                         |
| 18 |         VIEW                         | V$LOCK          |       1 |    1 |         4 |    +20 |     1 |       34 |      |       |       |       |       |       |          |                         |
| 19 |          HASH JOIN                   |                 |       1 |    1 |        24 |     +0 |     1 |       34 | 7057 | 827MB |  7056 | 827MB |  922M |  925M |   100.00 | Cpu (16)                |
|    |                                      |                 |         |      |           |        |       |          |      |       |       |       |       |       |          | direct path read temp (7) |
| 20 |           MERGE JOIN CARTESIAN       |                 |     100 |      |         9 |     +6 |     1 |      14M |      |       |       |       |       |       |          |                         |
| 21 |            FIXED TABLE FULL          | X$KSUSE         |       1 |      |         9 |     +6 |     1 |     1568 |      |       |       |       |       |       |          |                         |
| 22 |            BUFFER SORT               |                 |     100 |      |         9 |     +6 |  1568 |      14M |      |       |       |       |  434K |       |          |                         |
| 23 |             FIXED TABLE FULL         | X$KSQRS         |     100 |      |         1 |     +6 |     1 |     8928 |      |       |       |       |       |       |          |                         |
| 24 |           VIEW                       | GV$_LOCK        |      10 |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 25 |            UNION-ALL                 |                 |         |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 26 |             FILTER                   |                 |         |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 27 |              VIEW                    | GV$_LOCK1       |       2 |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 28 |               UNION-ALL              |                 |         |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 29 |                FIXED TABLE FULL      | X$KDNSSF        |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 30 |                FIXED TABLE FULL      | X$KSQEQ         |       1 |      |         7 |    +14 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 31 |             FIXED TABLE FULL         | X$KTADM         |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 32 |             FIXED TABLE FULL         | X$KTATRFIL      |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 33 |             FIXED TABLE FULL         | X$KTATRFSL      |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 34 |             FIXED TABLE FULL         | X$KTATL         |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 35 |             FIXED TABLE FULL         | X$KTSTUSC       |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 36 |             FIXED TABLE FULL         | X$KTSTUSS       |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 37 |             FIXED TABLE FULL         | X$KTSTUSG       |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 38 |             FIXED TABLE FULL         | X$KTCXB         |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 39 |       PX COORDINATOR                 |                 |       1 |      |         1 |    +23 |     1 |      109 |      |       |       |       |       |       |          |                         |
| 40 |        PX SEND QC (RANDOM)           | :TQ10000        |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 41 |         VIEW                         | GV$SESSION      |         |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 42 |          NESTED LOOPS                |                 |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 43 |           NESTED LOOPS               |                 |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 44 |            FIXED TABLE FULL          | X$KSUSE         |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 45 |            FIXED TABLE FIXED INDEX   | X$KSLWT (ind:1) |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 46 |           FIXED TABLE FIXED INDEX    | X$KSLED (ind:2) |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 47 |      VIEW                            | V$SESSION_WAIT  |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 48 |       NESTED LOOPS                   |                 |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 49 |        FIXED TABLE FULL              | X$KSLWT         |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 50 |        FIXED TABLE FIXED INDEX       | X$KSLED (ind:2) |       1 |      |         1 |    +23 |    55 |       55 |      |       |       |       |       |       |          |                         |
| 51 |     VIEW                             | V$SESSION_WAIT  |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 52 |      NESTED LOOPS                    |                 |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 53 |       FIXED TABLE FULL               | X$KSLWT         |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 54 |       FIXED TABLE FIXED INDEX        | X$KSLED (ind:2) |       1 |      |         1 |    +23 |    55 |       55 |      |       |       |       |       |       |          |                         |
==============================================================================================================================================================================================================

If you scroll to the right and look at the Activity % column you’ll see that 100% of the time was spent on step 19 (a Hash Join) and if you look at lines 20-23 you’ll see the following:

| 19 |          HASH JOIN                   |                 |       1 |    1 |        24 |     +0 |     1 |       34 | 7057 | 827MB |  7056 | 827MB |  922M |  925M |   100.00 | Cpu (16)                |
|    |                                      |                 |         |      |           |        |       |          |      |       |       |       |       |       |          | direct path read temp (7) |
| 20 |           MERGE JOIN CARTESIAN       |                 |     100 |      |         9 |     +6 |     1 |      14M |      |       |       |       |       |       |          |                         |
| 21 |            FIXED TABLE FULL          | X$KSUSE         |       1 |      |         9 |     +6 |     1 |     1568 |      |       |       |       |       |       |          |                         |
| 22 |            BUFFER SORT               |                 |     100 |      |         9 |     +6 |  1568 |      14M |      |       |       |       |  434K |       |          |                         |
| 23 |             FIXED TABLE FULL         | X$KSQRS         |     100 |      |         1 |     +6 |     1 |     8928

Note that the cardinality estimates are way off for X$KSQRS (100 vs. 8928) and X$KSUSE (1 vs. 1568). This is what tricks the optimizer into doing a MERGE JOIN CARTESIAN which basically joins every row from one table with every row from the other – resulting in 14M rows instead of the 100 the optimizer thought it would get.

So what did the 10g version do instead? If you go back and look closely at the plan you will see that it did a Nested Loop instead of the Merge Join Cartesian. Another trick that can be used to help locate the differences in plans is to do a diff on the set of hints that Oracle thinks are necessary to reproduce each plan. This could be done by spooling a file with the hints as generated by my sql_hints.sql script or by using a new script I just wrote (sql_hints_diff.sql) to take one set of outline hints and do a MINUS on the other set. The script produces easier to read output but I’ll show the output of both techniques here.

11.2.0.3> !diff 11_hints.dat 10_hints.dat
5c5
< OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
---
> OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
64a65
> USE_HASH(@"SEL$1" "V"@"SEL$1")
72d72
< FULL(@"SEL$2F35337B" "R"@"SEL$9")
74,75c74,75
< LEADING(@"SEL$2F35337B" "S"@"SEL$9" "R"@"SEL$9" "GV$_LOCK"@"SEL$10")
< USE_MERGE_CARTESIAN(@"SEL$2F35337B" "R"@"SEL$9")
---
> FULL(@"SEL$2F35337B" "R"@"SEL$9")
> LEADING(@"SEL$2F35337B" "S"@"SEL$9" "GV$_LOCK"@"SEL$10" "R"@"SEL$9")
76a77
> USE_NL(@"SEL$2F35337B" "R"@"SEL$9")
78d78
< FULL(@"SEL$23" "W"@"SEL$23")
80,81c80,81
< LEADING(@"SEL$23" "S"@"SEL$23" "W"@"SEL$23" "E"@"SEL$23")
< USE_NL(@"SEL$23" "W"@"SEL$23")
---
> FULL(@"SEL$23" "W"@"SEL$23")
> LEADING(@"SEL$23" "S"@"SEL$23" "E"@"SEL$23" "W"@"SEL$23")
82a83
> USE_NL(@"SEL$23" "W"@"SEL$23")
103d103
< FULL(@"SEL$68B588A0" "W"@"SEL$7")
105,106c105,106
< LEADING(@"SEL$68B588A0" "S"@"SEL$7" "W"@"SEL$7" "E"@"SEL$7")
< USE_NL(@"SEL$68B588A0" "W"@"SEL$7")
---
> FULL(@"SEL$68B588A0" "W"@"SEL$7")
> LEADING(@"SEL$68B588A0" "S"@"SEL$7" "E"@"SEL$7" "W"@"SEL$7")
107a108
> USE_NL(@"SEL$68B588A0" "W"@"SEL$7")
 
11.2.0.3> @sql_hints_diff.sql
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for child_no: 0
Enter value for sql_id_2: 9b2hgfwj6bnu1
Enter value for child_no_2: 1
 
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LEADING(@"SEL$23" "S"@"SEL$23" "W"@"SEL$23" "E"@"SEL$23")
LEADING(@"SEL$2F35337B" "S"@"SEL$9" "R"@"SEL$9" "GV$_LOCK"@"SEL$10")
LEADING(@"SEL$68B588A0" "S"@"SEL$7" "W"@"SEL$7" "E"@"SEL$7")
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
USE_MERGE_CARTESIAN(@"SEL$2F35337B" "R"@"SEL$9")
 
5 rows selected.
 
Elapsed: 00:00:01.18
11.2.0.3> /
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for child_no: 1
Enter value for sql_id_2: 9b2hgfwj6bnu1
Enter value for child_no_2: 0
 
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LEADING(@"SEL$23" "S"@"SEL$23" "E"@"SEL$23" "W"@"SEL$23")
LEADING(@"SEL$2F35337B" "S"@"SEL$9" "GV$_LOCK"@"SEL$10" "R"@"SEL$9")
LEADING(@"SEL$68B588A0" "S"@"SEL$7" "E"@"SEL$7" "W"@"SEL$7")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
USE_NL(@"SEL$2F35337B" "R"@"SEL$9")
 
5 rows selected.
 
Elapsed: 00:00:00.96

In the script output it’s pretty easy to see that the ordering of the tables was slightly different (the LEADING hints) but the major difference is that the 10g version chose a Nested Loop, USE_NL(@”SEL$2F35337B” “R”@”SEL$9″), on R which is X$KSQRS (note you can use XPLAN with the aliases option to determine what R is). While the 11g version did the Merge Join Cartesian, USE_MERGE_CARTESIAN(@”SEL$2F35337B” “R”@”SEL$9″). So you should be able to “fix” the query by using the USE_NL hint either directly embedded in the statement or via a SQL Profile or SQL Patch. I’ve tried all three approaches and they work, but I will leave that as an exercise for the reader since this post is getting so long.

But I’m still not satisfied because I haven’t solved the underlying problem. I’d like for Oracle to get it right without my intervention if possible. So why was the cardinality estimate off in the first place? Bad stats? All the objects in the query are V$ views which sit on X$ tables. Are there bad stats on the X$ tables? The answer is no, actually there are no stats on the X$ tables. This situation is common and can be fixed by gathering fixed object stats. If you’re not familiar with them you may want to check out Maria Colgan’s article about gathering fixed object stats.

11.2.0.3> @check_fixed_object_stats
Enter value for name: 
 
no rows selected
 
11.2.0.3> !cat gather_fixed_obj_stats.sql
-- EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
 
11.2.0.3> @gather_fixed_obj_stats.sql
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:02:50.47
11.2.0.3> @check_fixed_object_stats
Enter value for name: X$KS%
 
NAME                              OBJECT_ID ANALYZETI  LOGICALREAD       ROWCNT       BLKCNT       EMPCNT       AVGSPC       CHNCNT       AVGRLN   AVGSPC_FLB       FLBCNT   SAMPLESIZE        FLAGS
------------------------------ ------------ --------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
X$KSDAF                          4294952775 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSDAFT                         4294952776 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSLLTR                         4294950993 14-JAN-13                       553            0            0            0            0          138            0            0          553            0
X$KSLHOT                         4294952169 14-JAN-13                        10            0            0            0            0           19            0            0           10            0
X$KSLLCLASS                      4294951813 14-JAN-13                         8            0            0            0            0           47            0            0            8            0
X$KSLECLASS                      4294951830 14-JAN-13                        97            0            0            0            0           25            0            0           97            0
X$KSLEMAP                        4294951831 14-JAN-13                      1152            0            0            0            0           20            0            0         1152            0
X$KSLLD                          4294950994 14-JAN-13                       553            0            0            0            0           58            0            0          553            0
X$KSLED                          4294951094 14-JAN-13                      1152            0            0            0            0           79            0            0         1152            0
X$KSLCS                          4294952078 14-JAN-13                     20384            0            0            0            0           46            0            0        20384            0
X$KSLSCS                         4294952079 14-JAN-13                        13            0            0            0            0           61            0            0           13            0
X$KSLES                          4294951095 14-JAN-13                    641312            0            0            0            0           33            0            0       641312            0
X$KSLSESHIST                     4294951973 14-JAN-13                      1419            0            0            0            0           42            0            0         1419            0
X$KSLEI                          4294951102 14-JAN-13                      1152            0            0            0            0           41            0            0         1152            0
X$KSLLW                          4294951183 14-JAN-13                      5540            0            0            0            0           36            0            0         5540            0
X$KSLPO                          4294951184 14-JAN-13                       389            0            0            0            0           40            0            0          389            0
X$KSLWSC                         4294951185 14-JAN-13                      5540            0            0            0            0           45            0            0         5540            0
X$KSLWH                          4294952866 14-JAN-13                       550            0            0            0            0          113            0            0          550            0
X$KSLWT                          4294952867 14-JAN-13                        56            0            0            0            0          108            0            0           56            0
X$KSQEQTYP                       4294951983 14-JAN-13                       206            0            0            0            0           96            0            0          206            0
X$KSQRS                          4294950999 14-JAN-13                      8928            0            0            0            0           27            0            0         8928            0
X$KSQDN                          4294951001 14-JAN-13                         1            0            0            0            0           33            0            0            1            0
X$KSQST                          4294951085 14-JAN-13                       305            0            0            0            0          101            0            0          305            0
X$KSUINSTSTAT                    4294953015 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSUSE                          4294951004 14-JAN-13                      1568            0            0            0            0          218            0            0         1568            0
X$KSUSEX                         4294951428 14-JAN-13                      1568            0            0            0            0           28            0            0         1568            0
X$KSUPR                          4294951005 14-JAN-13                      1024            0            0            0            0          162            0            0         1024            0
X$KSUPRLAT                       4294951006 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSURLMT                        4294951396 14-JAN-13                        27            0            0            0            0           50            0            0           27            0
X$KSUSD                          4294951007 14-JAN-13                       638            0            0            0            0           55            0            0          638            0
X$KSUSGSTA                       4294951008 14-JAN-13                       638            0            0            0            0           62            0            0          638            0
X$KSUTM                          4294951067 14-JAN-13                         1            0            0            0            0           21            0            0            1            0
X$KSUSGIF                        4294951930 14-JAN-13                         1            0            0            0            0           18            0            0            1            0
X$KSUSESTA                       4294951009 14-JAN-13                   1019200            0            0            0            0           32            0            0      1019200            0
X$KSUMYSTA                       4294951106 14-JAN-13                       638            0            0            0            0           37            0            0          638            0
X$KSUSIO                         4294951079 14-JAN-13                      1568            0            0            0            0           37            0            0         1568            0
X$KSUSECST                       4294951096 14-JAN-13                      1568            0            0            0            0           52            0            0         1568            0
X$KSULOP                         4294951244 14-JAN-13                       121            0            0            0            0          294            0            0          121            0
X$KSUPGP                         4294951577 14-JAN-13                        89            0            0            0            0           26            0            0           89            0
X$KSUPGS                         4294951578 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSURU                          4294951010 14-JAN-13                     15680            0            0            0            0           23            0            0        15680            0
X$KSUPL                          4294951011 14-JAN-13                        10            0            0            0            0           24            0            0           10            0
X$KSUCF                          4294951012 14-JAN-13                        10            0            0            0            0           19            0            0           10            0
X$KSULL                          4294951082 14-JAN-13                         1            0            0            0            0           44            0            0            1            0
X$KSUCPUSTAT                     4294952112 14-JAN-13                        11            0            0            0            0           81            0            0           11            0
X$KSUVMSTAT                      4294952113 14-JAN-13                         3            0            0            0            0           83            0            0            3            0
X$KSUNETSTAT                     4294953033 14-JAN-13                         8            0            0            0            0           79            0            0            8            0
X$KSUSM                          4294952182 14-JAN-13                      1568            0            0            0            0           67            0            0         1568            0
X$KSIRESTYP                      4294951982 14-JAN-13                       206            0            0            0            0          104            0            0          206            0
X$KSIMSI                         4294951178 14-JAN-13                         2            0            0            0            0           47            0            0            2            0
X$KSIMAV                         4294951179 14-JAN-13                         6            0            0            0            0           38            0            0            6            0
X$KSIMAT                         4294951180 14-JAN-13                         5            0            0            0            0           39            0            0            5            0
X$KSMSD                          4294950995 14-JAN-13                         4            0            0            0            0           36            0            0            4            0
X$KSMSS                          4294950997 14-JAN-13                      1782            0            0            0            0           41            0            0         1782            0
X$KSMNS                          4294951493 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMNIM                         4294951494 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMLS                          4294951383 14-JAN-13                        18            0            0            0            0           36            0            0           18            0
X$KSMFS                          4294951384 14-JAN-13                         4            0            0            0            0           33            0            0            4            0
X$KSMJS                          4294951595 14-JAN-13                        10            0            0            0            0           33            0            0           10            0
X$KSMSTRS                        4294952000 14-JAN-13                         4            0            0            0            0           33            0            0            4            0
X$KSMFSV                         4294951146 14-JAN-13                     13340            0            0            0            0           45            0            0        13340            0
X$KSMLRU                         4294951099 14-JAN-13                        40            0            0            0            0           44            0            0           40            0
X$KSMSPR                         4294951170 14-JAN-13                       149            0            0            0            0           55            0            0          149            0
X$KSMPP                          4294951191 14-JAN-13                      6556            0            0            0            0           50            0            0         6556            0
X$KSMUP                          4294951192 14-JAN-13                      2501            0            0            0            0           54            0            0         2501            0
X$KSMHP                          4294951193 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMJCH                         4294951609 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMSST                         4294952688 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMSP_DSNEW                    4294951768 14-JAN-13                         1            0            0            0            0           26            0            0            1            0
X$KSMSP_NWEX                     4294951769 14-JAN-13                        65            0            0            0            0           63            0            0           65            0
X$KSMSGMEM                       4294952475 14-JAN-13                        11            0            0            0            0           45            0            0           11            0
X$KSMPGST                        4294952646 14-JAN-13                      6144            0            0            0            0           41            0            0         6144            0
X$KSMPGDST                       4294952673 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMPGDP                        4294952674 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSPPI                          4294950998 14-JAN-13                      2752            0            0            0            0           98            0            0         2752            0
X$KSPPCV                         4294951198 14-JAN-13                      2752            0            0            0            0           32            0            0         2752            0
X$KSPPSV                         4294951199 14-JAN-13                      2752            0            0            0            0           32            0            0         2752            0
X$KSPPCV2                        4294951587 14-JAN-13                      2756            0            0            0            0           45            0            0         2756            0
X$KSPPSV2                        4294951588 14-JAN-13                      2756            0            0            0            0           50            0            0         2756            0
X$KSPSPFILE                      4294951746 14-JAN-13                      2766            0            0            0            0           60            0            0         2766            0
X$KSPSPFH                        4294951933 14-JAN-13                         1            0            0            0            0           36            0            0            1            0
X$KSPVLD_VALUES                  4294952694 14-JAN-13                       597            0            0            0            0           58            0            0          597            0
X$KSPPO                          4294951486 14-JAN-13                       131            0            0            0            0           42            0            0          131            0
X$KSBDP                          4294950990 14-JAN-13                       296            0            0            0            0           35            0            0          296            0
X$KSBDD                          4294950991 14-JAN-13                       296            0            0            0            0           45            0            0          296            0
X$KSBFT                          4294952690 14-JAN-13                        74            0            0            0            0           32            0            0           74            0
X$KSBSRVDT                       4294953196 14-JAN-13                        38            0            0            0            0           54            0            0           38            0
X$KSBTABACT                      4294952582 14-JAN-13                      2119            0            0            0            0           52            0            0         2119            0
X$KSQEQ                          4294951000 14-JAN-13                     20016            0            0            0            0           44            0            0        20016            0
X$KSTEX                          4294951145 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSULV                          4294951130 14-JAN-13                       499            0            0            0            0           39            0            0          499            0
X$KSUXSINST                      4294951046 14-JAN-13                         1            0            0            0            0           95            0            0            1            0
X$KSUSECON                       4294951158 14-JAN-13                      1618            0            0            0            0           45            0            0         1618            0
X$KSRPCIOS                       4294952737 14-JAN-13                         2            0            0            0            0           33            0            0            2            0
X$KSFDFTYP                       4294952493 14-JAN-13                        29            0            0            0            0           29            0            0           29            0
X$KSFDSTCG                       4294952842 14-JAN-13                        96            0            0            0            0           69            0            0           96            0
X$KSFDSTCMP                      4294952843 14-JAN-13                      1386            0            0            0            0           65            0            0         1386            0
X$KSFDSTFILE                     4294952844 14-JAN-13                        45            0            0            0            0           68            0            0           45            0
X$KSFDSTBLK                      4294953234 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFDSTHIST                     4294952845 14-JAN-13                        11            0            0            0            0           26            0            0           11            0
X$KSFDSTTHIST                    4294952846 14-JAN-13                         1            0            0            0            0           25            0            0            1            0
X$KSFQP                          4294951563 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFQDVNT                       4294951380 14-JAN-13                         1            0            0            0            0           23            0            0            1            0
X$KSFVQST                        4294951441 14-JAN-13                       192            0            0            0            0           26            0            0          192            0
X$KSFVSTA                        4294951427 14-JAN-13                        32            0            0            0            0           25            0            0           32            0
X$KSFVSL                         4294951426 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSKPLW                         4294952188 14-JAN-13                        13            0            0            0            0           70            0            0           13            0
X$KSRCDES                        4294951579 14-JAN-13                       240            0            0            0            0           44            0            0          240            0
X$KSRCCTX                        4294951580 14-JAN-13                       240            0            0            0            0           38            0            0          240            0
X$KSRMSGDES                      4294951581 14-JAN-13                        80            0            0            0            0           55            0            0           80            0
X$KSRMPCTX                       4294951582 14-JAN-13                        80            0            0            0            0           25            0            0           80            0
X$KSRCHDL                        4294951583 14-JAN-13                        94            0            0            0            0           72            0            0           94            0
X$KSRMSGO                        4294951584 14-JAN-13                        10            0            0            0            0           57            0            0           10            0
X$KSXPPING                       4294952378 14-JAN-13                         2            0            0            0            0           77            0            0            2            0
X$KSXPCLIENT                     4294952380 14-JAN-13                        11            0            0            0            0           31            0            0           11            0
X$KSXPIF                         4294952379 14-JAN-13                         8            0            0            0            0          101            0            0            8            0
X$KSXPIA                         4294952165 14-JAN-13                         1            0            0            0            0           38            0            0            1            0
X$KSMDD                          4294951622 14-JAN-13                       407            0            0            0            0           74            0            0          407            0
X$KSMDUT1                        4294952607 14-JAN-13                       320            0            0            0            0           22            0            0          320            0
X$KSMGE                          4294951733 14-JAN-13                       505            0            0            0            0           64            0            0          505            0
X$KSXRMSG                        4294951728 14-JAN-13                       100            0            0            0            0           74            0            0          100            0
X$KSXRREPQ                       4294951729 14-JAN-13                       200            0            0            0            0           69            0            0          200            0
X$KSXRCONQ                       4294951730 14-JAN-13                       112            0            0            0            0           43            0            0          112            0
X$KSXRCH                         4294951731 14-JAN-13                        54            0            0            0            0           37            0            0           54            0
X$KSXRSG                         4294951732 14-JAN-13                         1            0            0            0            0           74            0            0            1            0
X$KSFMLIB                        4294951910 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMIOST                       4294951817 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMFILE                       4294951818 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMFILEEXT                    4294951819 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMSUBELEM                    4294951820 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMELEM                       4294951821 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMEXTELEM                    4294951822 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMCOMPL                      4294951823 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSOLSSTAT                      4294951842 14-JAN-13                        21            0            0            0            0           38            0            0           21            0
X$KSOLSFTS                       4294951841 14-JAN-13                     75516            0            0            0            0           65            0            0        75516            0
X$KSWSCLSTAB                     4294952155 14-JAN-13                        52            0            0            0            0           58            0            0           52            0
X$KSWSEVTAB                      4294952154 14-JAN-13                      1636            0            0            0            0           47            0            0         1636            0
X$KSWSASTAB                      4294952091 14-JAN-13                         4            0            0            0            0           76            0            0            4            0
X$KSWSAFTAB                      4294953052 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSIRGD                         4294952708 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSDHNG_CHAINS                  4294952927 14-JAN-13                        12            0            0            0            0          145            0            0           12            0
X$KSDHNG_SESSION_BLOCKERS        4294953211 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSDHNG_CACHE_HISTORY           4294953212 14-JAN-13                        20            0            0            0            0           43            0            0           20            0
X$KSXM_DFT                       4294953215 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSXAFA                         4294951204 14-JAN-13                        12            0            0            0            0           35            0            0           12            0
 
145 rows selected.
 
Elapsed: 00:00:00.01
11.2.0.3> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_c7q8y75rh36sc_3723858078  DEFAULT         ENABLED  select /* test */ avg(pk_col) from kso.skew where col1 = 23489         NO
PROF_gm0w4as5hhr4m_3867171677  DEFAULT         ENABLED  select count(*) from kso.skew                                          NO
PROFILE_399m90n8jzpu6_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROFILE_93356j5juvwpz_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew a                                     NO
PROFILE_7s0b9ygcrj77u_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
coe_gs35v5t21d9yf_2684249835   DEFAULT         ENABLED                                                                         NO
coe_9b2hgfwj6bnu1_2160357371   DEFAULT         ENABLED                                                                         NO
 
7 rows selected.
 
Elapsed: 00:00:00.00
11.2.0.3> @disable_sql_profile
Enter value for profile_name: coe_9b2hgfwj6bnu1_2160357371
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
11.2.0.3> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_c7q8y75rh36sc_3723858078  DEFAULT         ENABLED  select /* test */ avg(pk_col) from kso.skew where col1 = 23489         NO
PROF_gm0w4as5hhr4m_3867171677  DEFAULT         ENABLED  select count(*) from kso.skew                                          NO
PROFILE_399m90n8jzpu6_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROFILE_93356j5juvwpz_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew a                                     NO
PROFILE_7s0b9ygcrj77u_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
coe_gs35v5t21d9yf_2684249835   DEFAULT         ENABLED                                                                         NO
coe_9b2hgfwj6bnu1_2160357371   DEFAULT         DISABLED                                                                        NO
 
7 rows selected.
 
Elapsed: 00:00:00.01
11.2.0.3> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66
 
 
32 rows selected.
 
Elapsed: 00:00:00.09
11.2.0.3> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 0
-------------------------------------
 select distinct v.inst_id              , x.server_name       ,
x.status as x_status       , x.pid as x_pid       , x.sid as x_sid
 , w2.sid as p_sid       , v.osuser       , v.schemaname       ,
w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      ,
v$session_wait w1      , v$session_wait w2  where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)  and   x.sid =
w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)
     and substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 1445225033
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |    14 (100)|          |        |      |            |
|   1 |  SORT UNIQUE                       |                 |     1 |   110 |    13 (100)| 00:00:01 |        |      |            |
|   2 |   HASH JOIN OUTER                  |                 |     1 |   110 |    12 (100)| 00:00:01 |        |      |            |
|   3 |    FILTER                          |                 |       |       |            |          |        |      |            |
|   4 |     HASH JOIN OUTER                |                 |     1 |    80 |    12 (100)| 00:00:01 |        |      |            |
|   5 |      HASH JOIN OUTER               |                 |     1 |    60 |     2 (100)| 00:00:01 |        |      |            |
|   6 |       HASH JOIN OUTER              |                 |     1 |    30 |     2 (100)| 00:00:01 |        |      |            |
|   7 |        VIEW                        | V$PX_PROCESS    |     1 |    19 |     1 (100)| 00:00:01 |        |      |            |
|   8 |         HASH JOIN OUTER            |                 |     1 |    56 |     1 (100)| 00:00:01 |        |      |            |
|   9 |          HASH JOIN                 |                 |     1 |    29 |     1 (100)| 00:00:01 |        |      |            |
|  10 |           FIXED TABLE FULL         | X$KXFPDP        |     1 |    17 |     0   (0)|          |        |      |            |
|  11 |           FIXED TABLE FULL         | X$KSUPR         |     9 |   108 |     0   (0)|          |        |      |            |
|  12 |          VIEW                      | V$SESSION       |    57 |  1539 |     0   (0)|          |        |      |            |
|  13 |           NESTED LOOPS             |                 |    57 |  1539 |     0   (0)|          |        |      |            |
|  14 |            NESTED LOOPS            |                 |    57 |   684 |     0   (0)|          |        |      |            |
|  15 |             FIXED TABLE FULL       | X$KSLWT         |    57 |   456 |     0   (0)|          |        |      |            |
|  16 |             FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|          |        |      |            |
|  17 |            FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) |     1 |    15 |     0   (0)|          |        |      |            |
|  18 |        PX COORDINATOR              |                 |    57 |   627 |     0   (0)|          |        |      |            |
|  19 |         PX SEND QC (RANDOM)        | :TQ10000        |    57 |  1653 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|  20 |          VIEW                      | GV$SESSION      |       |       |            |          |  Q1,00 | PCWP |            |
|  21 |           NESTED LOOPS             |                 |    57 |  1653 |     0   (0)|          |  Q1,00 | PCWP |            |
|  22 |            NESTED LOOPS            |                 |    57 |   684 |     0   (0)|          |  Q1,00 | PCWP |            |
|  23 |             FIXED TABLE FULL       | X$KSLWT         |    57 |   456 |     0   (0)|          |  Q1,00 | PCWP |            |
|  24 |             FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|          |  Q1,00 | PCWP |            |
|  25 |            FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) |     1 |    17 |     0   (0)|          |  Q1,00 | PCWP |            |
|  26 |       VIEW                         | V$SESSION_WAIT  |    57 |  1710 |     0   (0)|          |        |      |            |
|  27 |        NESTED LOOPS                |                 |    57 |  2337 |     0   (0)|          |        |      |            |
|  28 |         FIXED TABLE FULL           | X$KSLWT         |    57 |   627 |     0   (0)|          |        |      |            |
|  29 |         FIXED TABLE FIXED INDEX    | X$KSLED (ind:2) |     1 |    30 |     0   (0)|          |        |      |            |
|  30 |      VIEW                          | V$LOCK          |  4018 | 80360 |     9 (100)| 00:00:01 |        |      |            |
|  31 |       HASH JOIN                    |                 |  4018 |   180K|     9 (100)| 00:00:01 |        |      |            |
|  32 |        HASH JOIN                   |                 |    45 |  1260 |     7 (100)| 00:00:01 |        |      |            |
|  33 |         VIEW                       | GV$_LOCK        |    45 |   540 |     6 (100)| 00:00:01 |        |      |            |
|  34 |          UNION-ALL                 |                 |       |       |            |          |        |      |            |
|  35 |           FILTER                   |                 |       |       |            |          |        |      |            |
|  36 |            VIEW                    | GV$_LOCK1       |    37 |   444 |     4 (100)| 00:00:01 |        |      |            |
|  37 |             UNION-ALL              |                 |       |       |            |          |        |      |            |
|  38 |              FIXED TABLE FULL      | X$KDNSSF        |     1 |    16 |     0   (0)|          |        |      |            |
|  39 |              FIXED TABLE FULL      | X$KSQEQ         |    36 |   612 |     4 (100)| 00:00:01 |        |      |            |
|  40 |           FIXED TABLE FULL         | X$KTADM         |     1 |    18 |     1 (100)| 00:00:01 |        |      |            |
|  41 |           FIXED TABLE FULL         | X$KTATRFIL      |     1 |    14 |     0   (0)|          |        |      |            |
|  42 |           FIXED TABLE FULL         | X$KTATRFSL      |     1 |    14 |     0   (0)|          |        |      |            |
|  43 |           FIXED TABLE FULL         | X$KTATL         |     1 |    14 |     0   (0)|          |        |      |            |
|  44 |           FIXED TABLE FULL         | X$KTSTUSC       |     1 |    14 |     0   (0)|          |        |      |            |
|  45 |           FIXED TABLE FULL         | X$KTSTUSS       |     1 |    16 |     0   (0)|          |        |      |            |
|  46 |           FIXED TABLE FULL         | X$KTSTUSG       |     1 |    14 |     0   (0)|          |        |      |            |
|  47 |           FIXED TABLE FULL         | X$KTCXB         |     1 |    16 |     0   (0)|          |        |      |            |
|  48 |         FIXED TABLE FULL           | X$KSUSE         |  1568 | 25088 |     0   (0)|          |        |      |            |
|  49 |        FIXED TABLE FULL            | X$KSQRS         |  8928 |   156K|     2 (100)| 00:00:01 |        |      |            |
|  50 |    VIEW                            | V$SESSION_WAIT  |    57 |  1710 |     0   (0)|          |        |      |            |
|  51 |     NESTED LOOPS                   |                 |    57 |  2337 |     0   (0)|          |        |      |            |
|  52 |      FIXED TABLE FULL              | X$KSLWT         |    57 |   627 |     0   (0)|          |        |      |            |
|  53 |      FIXED TABLE FIXED INDEX       | X$KSLED (ind:2) |     1 |    30 |     0   (0)|          |        |      |            |
-----------------------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - statement not queuable: gv$ statement
 
 
78 rows selected.
 
Elapsed: 00:00:00.04

So gathering the fixed object stats enabled the optimizer to get the right cardinality on the X$KSUSE and the X$KSQRS objects and come up with a good plan. It’s not the same as the one from 10g by the way, but performs well and doesn’t require any intervention. This is the correct way to resolve the root problem.

It’s import to note that the original 11g system I was working on was a production system that I was not comfortable gathering fixed object stats on without discussion and testing, while the bandaid of applying a SQL Profile or adding a hint to my query was a quick work around that allowed me to continue my work with little risk to the rest of the system. So try to use these techniques for what they are good for. Remember that bandaids only help with specific issues and do not resolve underlying problems, but they can provide very quick relief and provide you with a chance to resolve the underlying problem under less stressful conditions.

12 Comments

  1. This is a very nice article which proves again that when the optimizer can make good estimations (even on x$ tables), provided it has up-to-date statistics reflecting the data scattering as close as possible to the reality, it will produce the best execution plan.

    Best Regards

    Mohamed

  2. Hi Kerry,

    thanks for the attribution, but I’m definitely not the author of the script you’ve mentioned. Not sure where you got it from…

    Randolf

  3. Horia Berca says:

    Thanks for the nice post!

    A nice explanation is also given in GATHER_FIXED_OBJECTS_STATS Considerations [ID 798257.1]

    Also, the metalink note How to Gather Statistics on SYS Objects and ‘Fixed’ Objects? [ID 457926.1] mentions that “statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity”. “Gather fixed objects stats if the load is heavy and if the system is busy”.
    So in the end we will have to profile and “read” the symptoms while the instance is alive.

    Regards,
    Horia

  4. Definitely a good exercise, and very useful!

  5. olivier bernhard says:

    Hi Kerry,
    Once again you excel in teaching “by example” tuning sql statements methodology.
    Many times i have faced issues with fixed objects stats. One trivial example is when you query views releated to tables/partitions/subpartitions on databases using a massive amount of partitions.
    Gathering fixed objects stats fixes this.
    I’ve allways wondered why Oracle allways cares about gathering statistics on objects that do not belong to sys (Automatic Jobs and tasks which are created as part of a new database creation), and in the mean time, there’s still no automated task (afaik) to gather fixed objects and data dictionnary stats.
    Thanks again for sharing this stuff.

  6. Kerry,

    The paramon script looked vaguely familiar – so I tracked it down. It was written by Andy Brooker for the “Co-operative Oracle FAQ” in 2001, for Oracle 8.0.4 : http://www.jlcomp.demon.co.uk/faq/pq_proc.html … an oldie, but a goodie.

    It’s worth noting that you can protect a production system from some of the nasties in gathering fixed object stats by gathering stats for just the objects causing problems; gather_table_stats() can gather stats on individual x$ tables ! (See: http://timurakhmadeev.wordpress.com/2013/02/25/fixed-stats/ )

  7. osborne says:

    Hi Olivier,

    The upcoming 12c release of database should make you happy.

    Kerry

  8. osborne says:

    Hi Jonathan,

    Wow – nice job finding the original script and author. Kudos to Andy on that one. Also a nice point on gathering stats on specific X$ tables.

    Kerry

  9. [...] post a little while back about an issue with a single query that I resolved quickly with a profile (Tuning paramon.sql) . The point of the post was that an expedient and low risk solution to a problem is not always the [...]

  10. sudhakar says:

    Will it be possible to get the “rsm” and other scripts used in here?

  11. osborne says:

    Hi sudhakar,

    Most of the scripts have been referenced in other blog posts. Just use the search box to see if you can locate them. There a couple of exceptions. For example, x.sql is Tanel’s little script for running xplan on the last statement executed in you session and sm.sql is a simple query against v$sql_monitor. I have modified rsm.sql a bit since I posted this. Here’s a link to the updated version:

    rsm.sql

    There are some notes on it’s usage inside the script. Let me know if you have trouble finding any of the specific scripts you’re interested in.

    Kerry

  12. Kerry,

    btw, there is a little more convenient view since Oracle 8.1 – gv$px_session, example: http://github.com/xtender/xt_scripts/blob/master/px.sql

    Regards,
    Sayan Malakshinov

Leave a Reply