Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Database Generates so many trace files.

599229Mar 3 2008 — edited Sep 26 2008
Hello Friends,

currenctly i am working on oracle 10g RAC with 2 nodes installed on AIX 5.3. since some time my database generates so many trace file with name "<nodename>_j000_1138844.trc" in one of the node only. here i post the output of that file below. the entry of this file is not enter in alert log file. it generates 1000-1500 trace files in daily activity.

can you please help me why this file generate?

Trace file contains,

/home/oracle/admin/mucb/bdump/mucb2_j000_1138844.trc
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
With the Real Application Clusters option
ORACLE_HOME = /home/oracle/product/10g
System name: AIX
Node name: mucb1
Release: 3
Version: 5
Machine: 0000B0C1D600
Instance name: mucb2
Redo thread mounted by this instance: 2
Oracle process number: 19
Unix process pid: 1138844, image: oracle@mucb1 (J000)

*** SERVICE NAME:(SYS$USERS) 2008-03-01 20:24:50.499
*** SESSION ID:(1019.29381) 2008-03-01 20:24:50.499
---------------------- Dumping Query Warning -------------------
SQL Text (sql_id=1gc7umngbqyc1):
SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_NOTIFY_Q' AND A.QID = B.QID


Dumping stream from 0 to 551
----------------------------------

0000: 143 7 0 0 1 93 179 144 32 137 20 1 0 103 57 8 224 110 209 234 .....].. ....g9..n..
0020: 6 1 128 171 1 0 0 0 0 147 7 3 5 11 8 106 108 109 224 152 ...............jlm..
0040: 228 161 113 111 2 111 1 46 3 129 204 0 110 2 110 4 46 5 129 204 ..qo.o......n.n.....
0060: 0 110 3 110 6 110 5 46 7 129 204 0 110 8 110 7 6 1 8 12 .n.n.n......n.n.....
0080: 5 2 9 10 9 11 34 6 1 8 15 3 1 13 14 34 6 1 8 17 ......"........"....
0100: 2 0 18 34 2 1 7 19 1 1 3 20 40 1 5 8 12 8 15 8 ..."........(.......
0120: 17 7 19 3 20 19 1 8 15 19 1 8 17 18 1 7 19 18 1 3 ....................
0140: 20 2 2 2 21 0 0 143 7 0 0 1 93 179 145 232 4 2 4 0 ............].......
0160: 7 224 58 120 209 6 1 128 171 0 143 7 0 0 1 93 179 152 152 4 ..:x...........]....
0180: 3 4 1 7 218 205 111 6 1 128 145 0 143 7 0 0 1 89 166 68 ......o..........Y.D
0200: 80 4 4 2 0 4 192 123 88 4 1 121 0 143 7 0 0 1 89 166 P......{X..y......Y.
0220: 77 248 4 5 2 0 3 192 90 194 3 1 94 0 143 7 0 0 1 86 M.......Z...^......V
0240: 160 131 216 134 124 6 38 40 2 186 5 2 1 73 1 22 9 145 230 8 ....|.&(.....I......
0260: 12 14 143 7 0 0 1 86 160 137 184 134 124 7 23 12 1 156 153 1 .......V....|.......
0280: 1 0 1 22 23 145 234 8 12 1 143 7 0 0 1 89 166 79 152 134 ...............Y.O..
0300: 252 6 2 38 40 1 160 189 1 1 21 1 22 13 145 228 8 15 14 143 ...&(...............
0320: 7 0 0 1 86 160 127 168 134 124 7 23 13 0 132 26 0 1 0 1 ....V....|..........
0340: 22 24 145 229 8 15 1 143 7 0 0 1 89 166 70 120 134 252 5 2 ............Y.Fx....
0360: 38 42 1 160 149 1 1 27 1 25 16 22 8 17 3 143 7 0 0 1 &*..................
0380: 89 166 75 184 134 124 6 23 13 0 132 26 0 1 0 1 25 18 11 8 Y.K..|..............
0400: 17 17 143 7 0 0 1 93 179 156 208 134 252 4 2 38 24 2 162 157 .......].......&....
0420: 2 6 128 144 1 25 19 130 220 7 19 14 143 7 0 0 1 93 173 201 .................]..
0440: 120 134 148 3 2 89 0 0 197 87 48 0 100 138 40 2 3 20 0 143 x....Y...W0.d.(.....
0460: 7 0 0 1 93 179 148 224 193 32 100 4 88 89 0 197 87 48 0 100 ....].... d.XY..W0.d
0480: 138 40 131 232 4 25 26 0 0 143 7 0 0 1 93 179 149 144 193 32 .(............]....
0500: 103 5 53 0 0 0 131 232 2 25 20 240 255 255 194 11 22 143 7 0 g.5.................
0520: 0 1 93 179 150 96 193 38 116 6 21 24 0 197 87 48 0 100 138 40 ..]..`.&t.....W0.d.(
0540: 131 232 2 2 25 21 0 2 21 9 142 ...........

============
Plan Table
============
--------------------------------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
--------------------------------------------------------------------+-----------------------------------+-------------------------+
| 0 | SELECT STATEMENT | | | | 8 | | | | |
| 1 | HASH GROUP BY | | 1 | 171 | 8 | 00:00:01 | | | |
| 2 | HASH JOIN | | 1 | 171 | 7 | 00:00:01 | | | |
| 3 | HASH JOIN OUTER | | 1 | 145 | 7 | 00:00:01 | | | |
| 4 | NESTED LOOPS | | 1 | 121 | 4 | 00:00:01 | | | |
| 5 | NESTED LOOPS | | 1 | 94 | 3 | 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUES | 1 | 73 | 2 | 00:00:01 | | | |
| 7 | INDEX RANGE SCAN | AQ$_QUEUES_CHECK | 1 | | 1 | 00:00:01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUE_TABLES | 1 | 21 | 1 | 00:00:01 | | | |
| 9 | INDEX UNIQUE SCAN | AQ$_QUEUE_TABLES_PRIMARY| 1 | | 0 | | | | |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 27 | 1 | 00:00:01 | | | |
| 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | | | | |
| 12 | TABLE ACCESS FULL | SERVICE$ | 6 | 144 | 2 | 00:00:01 | | | |
| 13 | PX COORDINATOR | | 100 | 2600 | 0 | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2600 | 0 | |:Q1000| P->S |QC (RANDOM)|
| 15 | VIEW | GV$AQ1 | | | | |:Q1000| PCWP | |
| 16 | FIXED TABLE FULL | X$KWQSI | 100 | 2600 | 0 | |:Q1000| PCWP | |
--------------------------------------------------------------------+-----------------------------------+-------------------------+
Predicate Information:
----------------------
2 - access("QID"="Q"."EVENTID")
3 - access("Q"."SERVICE_NAME"="NAME")
7 - access("Q"."NAME"='MGMT_NOTIFY_Q')
9 - access("Q"."TABLE_OBJNO"="T"."OBJNO")
10 - filter("U"."NAME"="T"."SCHEMA")
11 - access("U"."USER#"=USERENV('SCHEMAID'))
12 - filter("DELETION_DATE" IS NULL)

Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SYSMAN
plan_hash : 576655312
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$674740F7")
MERGE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
INDEX(@"SEL$674740F7" "Q"@"SEL$2" ("AQ$_QUEUES"."NAME" "AQ$_QUEUES"."TABLE_OBJNO"))
INDEX(@"SEL$674740F7" "T"@"SEL$2" ("AQ$_QUEUE_TABLES"."OBJNO"))
INDEX(@"SEL$674740F7" "U"@"SEL$2" "I_USER#")
FULL(@"SEL$674740F7" "SERVICE$"@"SEL$3")
NO_ACCESS(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
LEADING(@"SEL$674740F7" "Q"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "SERVICE$"@"SEL$3" "GV$AQ1"@"SEL$4")
USE_NL(@"SEL$674740F7" "T"@"SEL$2")
USE_NL(@"SEL$674740F7" "U"@"SEL$2")
USE_HASH(@"SEL$674740F7" "SERVICE$"@"SEL$3")
USE_HASH(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
FULL(@"SEL$5" "X$KWQSI"@"SEL$5")
END_OUTLINE_DATA
*/

dumping QC plan signature
plansig->qkn_ctxpxsig = a1da7df3
ctx_ctxpxsig = 0
rwo_ctxpxsig = 7e7ab312
px_ctxpxsig = 4
dfo_ctxpxsig = 76e072fa
gd_ctxpxsig = 5
sm_ctxpxsig = 6
objs_ctxpxsig = 7
pgatgt_ctxpxsig = 1
WATSON_PRAGMA BEGINAPPEND file=pq.wat
com='soft_assert' sql_id=1gc7umngbqyc1 fi='#FILE' li=#LINE mesg=kxfxcValidateJoin: QC got no slave-join due to sql problems
WATSON_PRAGMA ENDAPPEND
----------------------------------------------------------------
kxfpgsg
some slaves didn't join after cb
kxfpgsg
Error 12850 encountered
---------------------- Dumping Query Warning -------------------
SQL Text (sql_id=1gc7umngbqyc1):
SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_NOTIFY_Q' AND A.QID = B.QID


Dumping stream from 0 to 551
----------------------------------

0000: 143 7 0 0 1 93 179 144 32 137 20 1 0 103 57 8 224 110 209 234 .....].. ....g9..n..
0020: 6 1 128 171 1 0 0 0 0 147 7 3 5 11 8 106 108 109 224 152 ...............jlm..
0040: 228 161 113 111 2 111 1 46 3 129 204 0 110 2 110 4 46 5 129 204 ..qo.o......n.n.....
0060: 0 110 3 110 6 110 5 46 7 129 204 0 110 8 110 7 6 1 8 12 .n.n.n......n.n.....
0080: 5 2 9 10 9 11 34 6 1 8 15 3 1 13 14 34 6 1 8 17 ......"........"....
0100: 2 0 18 34 2 1 7 19 1 1 3 20 40 1 5 8 12 8 15 8 ..."........(.......
0120: 17 7 19 3 20 19 1 8 15 19 1 8 17 18 1 7 19 18 1 3 ....................
0140: 20 2 2 2 21 0 0 143 7 0 0 1 93 179 145 232 4 2 4 0 ............].......
0160: 7 224 58 120 209 6 1 128 171 0 143 7 0 0 1 93 179 152 152 4 ..:x...........]....
0180: 3 4 1 7 218 205 111 6 1 128 145 0 143 7 0 0 1 89 166 68 ......o..........Y.D
0200: 80 4 4 2 0 4 192 123 88 4 1 121 0 143 7 0 0 1 89 166 P......{X..y......Y.
0220: 77 248 4 5 2 0 3 192 90 194 3 1 94 0 143 7 0 0 1 86 M.......Z...^......V
0240: 160 131 216 134 124 6 38 40 2 186 5 2 1 73 1 22 9 145 230 8 ....|.&(.....I......
0260: 12 14 143 7 0 0 1 86 160 137 184 134 124 7 23 12 1 156 153 1 .......V....|.......
0280: 1 0 1 22 23 145 234 8 12 1 143 7 0 0 1 89 166 79 152 134 ...............Y.O..
0300: 252 6 2 38 40 1 160 189 1 1 21 1 22 13 145 228 8 15 14 143 ...&(...............
0320: 7 0 0 1 86 160 127 168 134 124 7 23 13 0 132 26 0 1 0 1 ....V....|..........
0340: 22 24 145 229 8 15 1 143 7 0 0 1 89 166 70 120 134 252 5 2 ............Y.Fx....
0360: 38 42 1 160 149 1 1 27 1 25 16 22 8 17 3 143 7 0 0 1 &*..................
0380: 89 166 75 184 134 124 6 23 13 0 132 26 0 1 0 1 25 18 11 8 Y.K..|..............
0400: 17 17 143 7 0 0 1 93 179 156 208 134 252 4 2 38 24 2 162 157 .......].......&....
0420: 2 6 128 144 1 25 19 130 220 7 19 14 143 7 0 0 1 93 173 201 .................]..
0440: 120 134 148 3 2 89 0 0 197 87 48 0 100 138 40 2 3 20 0 143 x....Y...W0.d.(.....
0460: 7 0 0 1 93 179 148 224 193 32 100 4 88 89 0 197 87 48 0 100 ....].... d.XY..W0.d
0480: 138 40 131 232 4 25 26 0 0 143 7 0 0 1 93 179 149 144 193 32 .(............]....
0500: 103 5 53 0 0 0 131 232 2 25 20 240 255 255 194 11 22 143 7 0 g.5.................
0520: 0 1 93 179 150 96 193 38 116 6 21 24 0 197 87 48 0 100 138 40 ..]..`.&t.....W0.d.(
0540: 131 232 2 2 25 21 0 2 21 9 142 ...........

============
Plan Table
============
--------------------------------------------------------------------------------------------------------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------+
| 0 | SELECT STATEMENT | | | | 8 | | | | |
| 1 | HASH GROUP BY | | 1 | 171 | 8 | 00:00:01 | | | |
| 2 | HASH JOIN | | 1 | 171 | 7 | 00:00:01 | | | |
| 3 | HASH JOIN OUTER | | 1 | 145 | 7 | 00:00:01 | | | |
| 4 | NESTED LOOPS | | 1 | 121 | 4 | 00:00:01 | | | |
| 5 | NESTED LOOPS | | 1 | 94 | 3 | 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUES | 1 | 73 | 2 | 00:00:01 | | | |
| 7 | INDEX RANGE SCAN | AQ$_QUEUES_CHECK | 1 | | 1 | 00:00:01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUE_TABLES | 1 | 21 | 1 | 00:00:01 | | | |
| 9 | INDEX UNIQUE SCAN | AQ$_QUEUE_TABLES_PRIMARY| 1 | | 0 | | | | |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 27 | 1 | 00:00:01 | | | |
| 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | | | | |
| 12 | TABLE ACCESS FULL | SERVICE$ | 6 | 144 | 2 | 00:00:01 | | | |
| 13 | PX COORDINATOR | | 100 | 2600 | 0 | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2600 | 0 | |:Q1000| P->S |QC (RANDOM)|
| 15 | VIEW | GV$AQ1 | | | | |:Q1000| PCWP | |
| 16 | FIXED TABLE FULL | X$KWQSI | 100 | 2600 | 0 | |:Q1000| PCWP | |
--------------------------------------------------------------------------------------------------------------------------------+
Predicate Information:
----------------------
2 - access("QID"="Q"."EVENTID")
3 - access("Q"."SERVICE_NAME"="NAME")
7 - access("Q"."NAME"='MGMT_NOTIFY_Q')
9 - access("Q"."TABLE_OBJNO"="T"."OBJNO")
10 - filter("U"."NAME"="T"."SCHEMA")
11 - access("U"."USER#"=USERENV('SCHEMAID'))
12 - filter("DELETION_DATE" IS NULL)

Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SYSMAN
plan_hash : 576655312
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$674740F7")
MERGE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
INDEX(@"SEL$674740F7" "Q"@"SEL$2" ("AQ$_QUEUES"."NAME" "AQ$_QUEUES"."TABLE_OBJNO"))
INDEX(@"SEL$674740F7" "T"@"SEL$2" ("AQ$_QUEUE_TABLES"."OBJNO"))
INDEX(@"SEL$674740F7" "U"@"SEL$2" "I_USER#")
FULL(@"SEL$674740F7" "SERVICE$"@"SEL$3")
NO_ACCESS(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
LEADING(@"SEL$674740F7" "Q"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "SERVICE$"@"SEL$3" "GV$AQ1"@"SEL$4")
USE_NL(@"SEL$674740F7" "T"@"SEL$2")
USE_NL(@"SEL$674740F7" "U"@"SEL$2")
USE_HASH(@"SEL$674740F7" "SERVICE$"@"SEL$3")
USE_HASH(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
FULL(@"SEL$5" "X$KWQSI"@"SEL$5")
END_OUTLINE_DATA
*/

dumping QC plan signature
plansig->qkn_ctxpxsig = a1da7df3
ctx_ctxpxsig = 0
rwo_ctxpxsig = 7e7ab312
px_ctxpxsig = 4
dfo_ctxpxsig = 76e072fa
gd_ctxpxsig = 5
sm_ctxpxsig = 6
objs_ctxpxsig = 7
pgatgt_ctxpxsig = 1
WATSON_PRAGMA BEGINAPPEND file=pq.wat
com='soft_assert' sql_id=1gc7umngbqyc1 fi='#FILE' li=#LINE mesg=kxfxcValidateJoin: QC got no slave-join due to sql problems
WATSON_PRAGMA ENDAPPEND
----------------------------------------------------------------
kxfpgsg
some slaves didn't join after cb
kxfpgsg
Error 12850 encountered
---------------------- Dumping Query Warning -------------------
SQL Text (sql_id=1gc7umngbqyc1):
SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_NOTIFY_Q' AND A.QID = B.QID


Dumping stream from 0 to 551
----------------------------------

0000: 143 7 0 0 1 93 179 144 32 137 20 1 0 103 57 8 224 110 209 234 .....].. ....g9..n..
0020: 6 1 128 171 1 0 0 0 0 147 7 3 5 11 8 106 108 109 224 152 ...............jlm..
0040: 228 161 113 111 2 111 1 46 3 129 204 0 110 2 110 4 46 5 129 204 ..qo.o......n.n.....
0060: 0 110 3 110 6 110 5 46 7 129 204 0 110 8 110 7 6 1 8 12 .n.n.n......n.n.....
0080: 5 2 9 10 9 11 34 6 1 8 15 3 1 13 14 34 6 1 8 17 ......"........"....
0100: 2 0 18 34 2 1 7 19 1 1 3 20 40 1 5 8 12 8 15 8 ..."........(.......
0120: 17 7 19 3 20 19 1 8 15 19 1 8 17 18 1 7 19 18 1 3 ....................
0140: 20 2 2 2 21 0 0 143 7 0 0 1 93 179 145 232 4 2 4 0 ............].......
0160: 7 224 58 120 209 6 1 128 171 0 143 7 0 0 1 93 179 152 152 4 ..:x...........]....
0180: 3 4 1 7 218 205 111 6 1 128 145 0 143 7 0 0 1 89 166 68 ......o..........Y.D
0200: 80 4 4 2 0 4 192 123 88 4 1 121 0 143 7 0 0 1 89 166 P......{X..y......Y.
0220: 77 248 4 5 2 0 3 192 90 194 3 1 94 0 143 7 0 0 1 86 M.......Z...^......V
0240: 160 131 216 134 124 6 38 40 2 186 5 2 1 73 1 22 9 145 230 8 ....|.&(.....I......
0260: 12 14 143 7 0 0 1 86 160 137 184 134 124 7 23 12 1 156 153 1 .......V....|.......
0280: 1 0 1 22 23 145 234 8 12 1 143 7 0 0 1 89 166 79 152 134 ...............Y.O..
0300: 252 6 2 38 40 1 160 189 1 1 21 1 22 13 145 228 8 15 14 143 ...&(...............
0320: 7 0 0 1 86 160 127 168 134 124 7 23 13 0 132 26 0 1 0 1 ....V....|..........
0340: 22 24 145 229 8 15 1 143 7 0 0 1 89 166 70 120 134 252 5 2 ............Y.Fx....
0360: 38 42 1 160 149 1 1 27 1 25 16 22 8 17 3 143 7 0 0 1 &*..................
0380: 89 166 75 184 134 124 6 23 13 0 132 26 0 1 0 1 25 18 11 8 Y.K..|..............
0400: 17 17 143 7 0 0 1 93 179 156 208 134 252 4 2 38 24 2 162 157 .......].......&....
0420: 2 6 128 144 1 25 19 130 220 7 19 14 143 7 0 0 1 93 173 201 .................]..
0440: 120 134 148 3 2 89 0 0 197 87 48 0 100 138 40 2 3 20 0 143 x....Y...W0.d.(.....
0460: 7 0 0 1 93 179 148 224 193 32 100 4 88 89 0 197 87 48 0 100 ....].... d.XY..W0.d
0480: 138 40 131 232 4 25 26 0 0 143 7 0 0 1 93 179 149 144 193 32 .(............]....
0500: 103 5 53 0 0 0 131 232 2 25 20 240 255 255 194 11 22 143 7 0 g.5.................
0520: 0 1 93 179 150 96 193 38 116 6 21 24 0 197 87 48 0 100 138 40 ..]..`.&t.....W0.d.(
0540: 131 232 2 2 25 21 0 2 21 9 142 ...........

============
Plan Table
============
--------------------------------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
--------------------------------------------------------------------+-----------------------------------+-------------------------+
| 0 | SELECT STATEMENT | | | | 8 | | | | |
| 1 | HASH GROUP BY | | 1 | 171 | 8 | 00:00:01 | | | |
| 2 | HASH JOIN | | 1 | 171 | 7 | 00:00:01 | | | |
| 3 | HASH JOIN OUTER | | 1 | 145 | 7 | 00:00:01 | | | |
| 4 | NESTED LOOPS | | 1 | 121 | 4 | 00:00:01 | | | |
| 5 | NESTED LOOPS | | 1 | 94 | 3 | 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUES | 1 | 73 | 2 | 00:00:01 | | | |
| 7 | INDEX RANGE SCAN | AQ$_QUEUES_CHECK | 1 | | 1 | 00:00:01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUE_TABLES | 1 | 21 | 1 | 00:00:01 | | | |
| 9 | INDEX UNIQUE SCAN | AQ$_QUEUE_TABLES_PRIMARY| 1 | | 0 | | | | |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 27 | 1 | 00:00:01 | | | |
| 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | | | | |
| 12 | TABLE ACCESS FULL | SERVICE$ | 6 | 144 | 2 | 00:00:01 | | | |
| 13 | PX COORDINATOR | | 100 | 2600 | 0 | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2600 | 0 | |:Q1000| P->S |QC (RANDOM)|
| 15 | VIEW | GV$AQ1 | | | | |:Q1000| PCWP | |
| 16 | FIXED TABLE FULL | X$KWQSI | 100 | 2600 | 0 | |:Q1000| PCWP | |
--------------------------------------------------------------------+-----------------------------------+-------------------------+
Predicate Information:
----------------------
2 - access("QID"="Q"."EVENTID")
3 - access("Q"."SERVICE_NAME"="NAME")
7 - access("Q"."NAME"='MGMT_NOTIFY_Q')
9 - access("Q"."TABLE_OBJNO"="T"."OBJNO")
10 - filter("U"."NAME"="T"."SCHEMA")
11 - access("U"."USER#"=USERENV('SCHEMAID'))
12 - filter("DELETION_DATE" IS NULL)

Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SYSMAN
plan_hash : 576655312
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$674740F7")
MERGE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
INDEX(@"SEL$674740F7" "Q"@"SEL$2" ("AQ$_QUEUES"."NAME" "AQ$_QUEUES"."TABLE_OBJNO"))
INDEX(@"SEL$674740F7" "T"@"SEL$2" ("AQ$_QUEUE_TABLES"."OBJNO"))
INDEX(@"SEL$674740F7" "U"@"SEL$2" "I_USER#")
FULL(@"SEL$674740F7" "SERVICE$"@"SEL$3")
NO_ACCESS(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
LEADING(@"SEL$674740F7" "Q"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "SERVICE$"@"SEL$3" "GV$AQ1"@"SEL$4")
USE_NL(@"SEL$674740F7" "T"@"SEL$2")
USE_NL(@"SEL$674740F7" "U"@"SEL$2")
USE_HASH(@"SEL$674740F7" "SERVICE$"@"SEL$3")
USE_HASH(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
FULL(@"SEL$5" "X$KWQSI"@"SEL$5")
END_OUTLINE_DATA
*/

dumping QC plan signature
plansig->qkn_ctxpxsig = a1da7df3
ctx_ctxpxsig = 0
rwo_ctxpxsig = 7e7ab312
px_ctxpxsig = 4
dfo_ctxpxsig = 76e072fa
gd_ctxpxsig = 5
sm_ctxpxsig = 6
objs_ctxpxsig = 7
pgatgt_ctxpxsig = 1
WATSON_PRAGMA BEGINAPPEND file=pq.wat
com='soft_assert' sql_id=1gc7umngbqyc1 fi='#FILE' li=#LINE mesg=kxfxcValidateJoin: QC got no slave-join due to sql problems
WATSON_PRAGMA ENDAPPEND
----------------------------------------------------------------
kxfpgsg
some slaves didn't join after cb
kxfpgsg
Error 12850 encountered
---------------------- Dumping Query Warning -------------------
SQL Text (sql_id=1gc7umngbqyc1):
SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_NOTIFY_Q' AND A.QID = B.QID


Dumping stream from 0 to 551
----------------------------------

0000: 143 7 0 0 1 93 179 144 32 137 20 1 0 103 57 8 224 110 209 234 .....].. ....g9..n..
0020: 6 1 128 171 1 0 0 0 0 147 7 3 5 11 8 106 108 109 224 152 ...............jlm..
0040: 228 161 113 111 2 111 1 46 3 129 204 0 110 2 110 4 46 5 129 204 ..qo.o......n.n.....
0060: 0 110 3 110 6 110 5 46 7 129 204 0 110 8 110 7 6 1 8 12 .n.n.n......n.n.....
0080: 5 2 9 10 9 11 34 6 1 8 15 3 1 13 14 34 6 1 8 17 ......"........"....
0100: 2 0 18 34 2 1 7 19 1 1 3 20 40 1 5 8 12 8 15 8 ..."........(.......
0120: 17 7 19 3 20 19 1 8 15 19 1 8 17 18 1 7 19 18 1 3 ....................
0140: 20 2 2 2 21 0 0 143 7 0 0 1 93 179 145 232 4 2 4 0 ............].......
0160: 7 224 58 120 209 6 1 128 171 0 143 7 0 0 1 93 179 152 152 4 ..:x...........]....
0180: 3 4 1 7 218 205 111 6 1 128 145 0 143 7 0 0 1 89 166 68 ......o..........Y.D
0200: 80 4 4 2 0 4 192 123 88 4 1 121 0 143 7 0 0 1 89 166 P......{X..y......Y.
0220: 77 248 4 5 2 0 3 192 90 194 3 1 94 0 143 7 0 0 1 86 M.......Z...^......V
0240: 160 131 216 134 124 6 38 40 2 186 5 2 1 73 1 22 9 145 230 8 ....|.&(.....I......
0260: 12 14 143 7 0 0 1 86 160 137 184 134 124 7 23 12 1 156 153 1 .......V....|.......
0280: 1 0 1 22 23 145 234 8 12 1 143 7 0 0 1 89 166 79 152 134 ...............Y.O..
0300: 252 6 2 38 40 1 160 189 1 1 21 1 22 13 145 228 8 15 14 143 ...&(...............
0320: 7 0 0 1 86 160 127 168 134 124 7 23 13 0 132 26 0 1 0 1 ....V....|..........
0340: 22 24 145 229 8 15 1 143 7 0 0 1 89 166 70 120 134 252 5 2 ............Y.Fx....
0360: 38 42 1 160 149 1 1 27 1 25 16 22 8 17 3 143 7 0 0 1 &*..................
0380: 89 166 75 184 134 124 6 23 13 0 132 26 0 1 0 1 25 18 11 8 Y.K..|..............
0400: 17 17 143 7 0 0 1 93 179 156 208 134 252 4 2 38 24 2 162 157 .......].......&....
0420: 2 6 128 144 1 25 19 130 220 7 19 14 143 7 0 0 1 93 173 201 .................]..
0440: 120 134 148 3 2 89 0 0 197 87 48 0 100 138 40 2 3 20 0 143 x....Y...W0.d.(.....
0460: 7 0 0 1 93 179 148 224 193 32 100 4 88 89 0 197 87 48 0 100 ....].... d.XY..W0.d
0480: 138 40 131 232 4 25 26 0 0 143 7 0 0 1 93 179 149 144 193 32 .(............]....
0500: 103 5 53 0 0 0 131 232 2 25 20 240 255 255 194 11 22 143 7 0 g.5.................
0520: 0 1 93 179 150 96 193 38 116 6 21 24 0 197 87 48 0 100 138 40 ..]..`.&t.....W0.d.(
0540: 131 232 2 2 25 21 0 2 21 9 142 ...........

============
Plan Table
============
--------------------------------------------------------------------------------------------------------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------+
| 0 | SELECT STATEMENT | | | | 8 | | | | |
| 1 | HASH GROUP BY | | 1 | 171 | 8 | 00:00:01 | | | |
| 2 | HASH JOIN | | 1 | 171 | 7 | 00:00:01 | | | |
| 3 | HASH JOIN OUTER | | 1 | 145 | 7 | 00:00:01 | | | |
| 4 | NESTED LOOPS | | 1 | 121 | 4 | 00:00:01 | | | |
| 5 | NESTED LOOPS | | 1 | 94 | 3 | 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUES | 1 | 73 | 2 | 00:00:01 | | | |
| 7 | INDEX RANGE SCAN | AQ$_QUEUES_CHECK | 1 | | 1 | 00:00:01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | AQ$_QUEUE_TABLES | 1 | 21 | 1 | 00:00:01 | | | |
| 9 | INDEX UNIQUE SCAN | AQ$_QUEUE_TABLES_PRIMARY| 1 | | 0 | | | | |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 27 | 1 | 00:00:01 | | | |
| 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | | | | |
| 12 | TABLE ACCESS FULL | SERVICE$ | 6 | 144 | 2 | 00:00:01 | | | |
| 13 | PX COORDINATOR | | 100 | 2600 | 0 | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2600 | 0 | |:Q1000| P->S |QC (RANDOM)|
| 15 | VIEW | GV$AQ1 | | | | |:Q1000| PCWP | |
| 16 | FIXED TABLE FULL | X$KWQSI | 100 | 2600 | 0 | |:Q1000| PCWP | |
--------------------------------------------------------------------------------------------------------------------------------+
Predicate Information:
----------------------
2 - access("QID"="Q"."EVENTID")
3 - access("Q"."SERVICE_NAME"="NAME")
7 - access("Q"."NAME"='MGMT_NOTIFY_Q')
9 - access("Q"."TABLE_OBJNO"="T"."OBJNO")
10 - filter("U"."NAME"="T"."SCHEMA")
11 - access("U"."USER#"=USERENV('SCHEMAID'))
12 - filter("DELETION_DATE" IS NULL)

Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SYSMAN
plan_hash : 576655312
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$674740F7")
MERGE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
INDEX(@"SEL$674740F7" "Q"@"SEL$2" ("AQ$_QUEUES"."NAME" "AQ$_QUEUES"."TABLE_OBJNO"))
INDEX(@"SEL$674740F7" "T"@"SEL$2" ("AQ$_QUEUE_TABLES"."OBJNO"))
INDEX(@"SEL$674740F7" "U"@"SEL$2" "I_USER#")
FULL(@"SEL$674740F7" "SERVICE$"@"SEL$3")
NO_ACCESS(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
LEADING(@"SEL$674740F7" "Q"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "SERVICE$"@"SEL$3" "GV$AQ1"@"SEL$4")
USE_NL(@"SEL$674740F7" "T"@"SEL$2")
USE_NL(@"SEL$674740F7" "U"@"SEL$2")
USE_HASH(@"SEL$674740F7" "SERVICE$"@"SEL$3")
USE_HASH(@"SEL$674740F7" "GV$AQ1"@"SEL$4")
FULL(@"SEL$5" "X$KWQSI"@"SEL$5")
END_OUTLINE_DATA
*/

dumping QC plan signature
plansig->qkn_ctxpxsig = a1da7df3
ctx_ctxpxsig = 0
rwo_ctxpxsig = 7e7ab312
px_ctxpxsig = 4
dfo_ctxpxsig = 76e072fa
gd_ctxpxsig = 5
sm_ctxpxsig = 6
objs_ctxpxsig = 7
pgatgt_ctxpxsig = 1
WATSON_PRAGMA BEGINAPPEND file=pq.wat
com='soft_assert' sql_id=1gc7umngbqyc1 fi='#FILE' li=#LINE mesg=kxfxcValidateJoin: QC got no slave-join due to sql problems
WATSON_PRAGMA ENDAPPEND
----------------------------------------------------------------
kxfpgsg
some slaves didn't join after cb
kxfpgsg
Error 12850 encountered
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2008
Added on Mar 3 2008
#general-database-discussions
5 comments
1,674 views