Re: Puzzling execution plan

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 12 Jun 2004 03:11:37 -0400
Message-ID: <20040612071137.GA11958@medo.noip.com>

On 06/12/2004 04:17:26 PM, Don Granaman wrote:
> (Oracle 9.2.0.4) This doesn't make sense to me...
> How does this make sense? What might make an optimizer with fresh
> statistics so badly munge the execution plan for Q1 & Q2? This was in a
> database with no tweaks to OIC/etc. A hint "fixes" Q1 and Q2, but it seems
> one should not be necessary.
>
> -Don Granaman
> puzzled OraSaurus

At the first glance, it looks like a bug, because in the first two cases, you're comparing to result of a function, while the 3rd example looks like a constant. Try setting event 10053, level 10 and see the trace file. Trace file should reveal each step optimizer takes and you'll see what does it do differently in queries 1 and 2 as opposed to 3. This is what the command looks like:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> alter session set events='10053 trace name context forever, level 10';

Session altered.

SQL> This is the trace it produces in BACKGROUND_DUMP_DEST directory:

Column Usage Monitoring is ON: tracking level = 1

QUERY BLOCK SIGNATURE

qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=0 objn=44109 hint_alias="EMP"@"SEL$1"

BASE STATISTICAL INFORMATION

Table stats Table: EMP Alias: EMP
  TOTAL :: CDN: 14 NBLKS: 5 AVG_ROW_LEN: 37 Index stats
  Index: EMP_DEPTNO_I COL#: 8
    TOTAL :: LVLS: 0 #LB: 1 #DK: 3 LB/K: 1 DB/K: 1 CLUF: 1   Index: PK_EMP COL#: 1
    TOTAL :: LVLS: 0 #LB: 1 #DK: 14 LB/K: 1 DB/K: 1 CLUF: 1 _OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH
COLUMN: ENAME(VARCHAR2) Col#: 2 Table: EMP Alias: EMP Size: 6 NDV: 14 Nulls: 0 Density: 3.5714e-02 Frequency Histogram: #BKT: 14

        (14 uncompressed buckets and 14 endpoint values)
  TABLE: EMP  Alias: EMP

Original Card: 14 Rounded Card: 1 Computed Card: 0.50 Access Path: table-scan Resc: 3 Resp: 3 BEST_CST: 3.01 PATH: 2 Degree: 1

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: EMP[EMP]#0
Best so far: TABLE#: 0 CST: 3 CDN: 1 BYTES: 37 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 Final - All Rows Plan:
JOIN ORDER: 1
CST: 3 CDN: 1 RSC: 3 RSP: 3 BYTES: 37 IO-RSC: 3 IO-RSP: 3 CPU-RSC: 38807 CPU-RSP: 38807 QUERY
select * from emp where ename='KING'
QUERY
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; My version is not the same as your version, so our optimizers are likely to be very different. I cannot make any reasonable conclusions on my database.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Jun 12 2004 - 02:08:27 CDT