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.
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
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
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
Definitely a good exercise, and very useful!
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.
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/ )
Hi Olivier,
The upcoming 12c release of database should make you happy.
Kerry
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
[…] 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 […]
Will it be possible to get the “rsm” and other scripts used in here?
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
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