Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What server 9.2.0 read?

Re: What server 9.2.0 read?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Fri, 23 May 2003 16:19:54 +0400
Message-ID: <bal475$dl7$1@babylon.agtel.net>


"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 33   
   VIEW 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US