RE: Win2k/8.1.7.3/Optimizer Weirdness

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Tue, 19 Nov 2002 12:03:46 -0800
Message-ID: <F001.0050714A.20021119120346@fatcity.com>

Here are the different explain plans. This is on an 8.1.7.0 db on Solaris. One idea is the problem could be on the merge-join. I've heard of some problems with this operation.  

SQL> select file_name from dba_temp_files;  

FILE_NAME

---

---

/db02/oradata/MDX817/temp_01.dbf  

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=82 Bytes=26486)    1 0 NESTED LOOPS (Cost=21 Card=82 Bytes=26486)

   2    1     NESTED LOOPS (Cost=20 Card=1 Bytes=310)
   3    2       FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=10 Card=1 Bytes=284)
   4    2       FIXED TABLE (FIXED INDEX #1) OF 'X$KTFTHC' (Cost=10 Card=100
Bytes=2600)
   5    1     TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=1 Card=8168
Bytes=106184)
   6    5       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
 
 
 
 
 

SQL> select /*+ RULE */ file_name from dba_temp_files;  

no rows selected  

Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 MERGE JOIN

   2    1     SORT (JOIN)
   3    2       NESTED LOOPS
   4    3         FIXED TABLE (FULL) OF 'X$KTFTHC'
   5    3         TABLE ACCESS (CLUSTER) OF 'TS$'
   6    5           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
   7    1     SORT (JOIN)
   8    7       FIXED TABLE (FULL) OF 'X$KCCFN'

-----Original Message-----
Sent: Tuesday, November 19, 2002 12:39 PM To: Multiple recipients of list ORACLE-L

9.2 on AIX ... same problem ... Wait they must have fixed it in 10i

Raj

---

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
<mailto:peter.gram_at_miracleas.dk> ]
Sent: Tuesday, November 19, 2002 2:15 PM To: Multiple recipients of list ORACLE-L

Same thing in 9.2.0.2 on linux

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Nov 19 2002 - 14:03:46 CST