Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: What server 9.2.0 read?
"Orca777" <member28638_at_dbforums.com> wrote in message news:2914780.1053684502_at_dbforums.com...
>
> Hi, had the same efect on win2k 9i r2, but have no chande to test it
> under Linux;
>
> The CPU goes to 100% and Oracle is working :
>
>
>
> SELECT STATEMENT, GOAL = CHOOSE 77 1 795
> NESTED LOOPS 77 1 795
> MERGE JOIN CARTESIAN 33 1 714
> MERGE JOIN CARTESIAN 22 1 681
> FIXED TABLE FULL SYS X$KSUSE 11 1 651
> BUFFER SORT 11 1 30
> FIXED TABLE FULL SYS X$KSUSE 1 30
> BUFFER SORT 22 1 33
> FIXED TABLE FULL SYS X$KSQRS 1 33
> VIEW SYS GV$_LOCK 1 81
> UNION-ALL
> VIEW SYS GV$_LOCK1 22 2 162
> UNION-ALL
> FIXED TABLE FULL SYS X$KDNSSF 11 1 94
> FIXED TABLE FULL SYS X$KSQEQ 11 1 94
> FIXED TABLE FULL SYS X$KTADM 11 1 94
> FIXED TABLE FULL SYS X$KTCXB 11 1 94
>
On my instance (not under heavy load) there is single MERGE JOIN CARTESIAN for this query, but it manages to kill performance significantly. On 8.1.7 there are no merge joins, everything goes through NLs and works pretty fast. I think this must be a bug (or a new feature?) of the CBO in 9.2. I took 10053 dumps for the query on both 8.1.7.4 and on 9.2.0.3, but I am no good at decoding the trace so I can not figure out where that merge join came from:
Join order[8]: X$KSUSE [ S] X$KSUSE [X$KSUSE] X$KSQRS [ R] GV$_LOCK [GV$_LOCK]
Now joining: X$KSQRS [ R] *******
NL Join
Outer table: cost: 22 cdn: 1 rcz: 681 resp: 22
Inner table: X$KSQRS
Access path: tsc Resc: 11
Join: Resc: 33 Resp: 33
Join cardinality: 0 = outer (0) * inner (100) * sel (1.0000e+000) [flag=0]
Best NL cost: 33 resp: 33
Join result: cost: 33 cdn: 1 rcz: 714
Now joining: GV$_LOCK [GV$_LOCK] *******
NL Join
Outer table: cost: 33 cdn: 1 rcz: 714 resp: 33
Inner table: GV$_LOCK
Access path: tsc Resc: 44
Join: Resc: 77 Resp: 77
Join cardinality: 0 = outer (0) * inner (4) * sel (1.0000e-004) [flag=0]
Best NL cost: 77 resp: 77
Join result: cost: 77 cdn: 1 rcz: 795 Best so far: TABLE#: 1 CST: 11 CDN: 1 BYTES: 30 Best so far: TABLE#: 0 CST: 22 CDN: 1 BYTES: 681 Best so far: TABLE#: 3 CST: 33 CDN: 1 BYTES: 714 Best so far: TABLE#: 2 CST: 77 CDN: 1 BYTES: 795 ***********************
This was the plan that won, but I don't see how that merge join cartesian sneaked in - no sign of it here in the CBO trace. Also note that the trace itself is kinda strange: the CBO starts with joining X$KSQRS [R] to WHAT??? It's the third table in list, this means that X$KSUSE [S] and X$KSUSE [X$KSUSE] were already joined somehow and this costed 22 - indeed, buffer soft and NL join of these two tables costs 22 in the plan, but why it's not in the trace? More than that, the trace shows that R was joined using NL and the cost was 33 - but in the plan it's merge join cartesian with cost 33! Maybe I am interpreting this trace incorrectly? If so, can anyone having more experience with 10053 dumps explain how the trace above becomes
NESTED LOOPS 77 1 795 77
MERGE JOIN CARTESIAN 33 1 714 33
NESTED LOOPS 22 1 681 22 FIXED TABLE FULL SYS X$KSUSE 11 1 30 FIXED TABLE FIXED INDEX SYS X$KSUSE (ind:1) 11 1 651 BUFFER SORT 22 1 33 22 FIXED TABLE FULL SYS X$KSQRS 1 33VIEW SYS GV$_LOCK 1 81 ? I think this is a glitch in the 9.2 CBO, but this may well be some new feature, while the fixed views were not hinted properly to take it into account... Anyone care to comment?
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer.Received on Fri May 23 2003 - 07:19:54 CDT