Re: Re: cardinality in query plans?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Mar 2004 15:18:11 -0000
The execution plan suggests that your query is
just a little more complicated than a simple
join - but I don't think the outer edges are
important (looks like a couple of analytics
and an inline view to get a page of results).
Would you care to check your second plan -
I think the TAB2_IND should read TAB1_PK,
and the TAB2_PK should read TAB2_IND.
: 7 6 INDEX (RANGE SCAN) OF 'TAB2_IND'(UNIQUE) (Cost=11 Card=2250
Bytes=36000)
: 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2' (Cost=337 Card=43539
Bytes=3178347)
: 9 8 INDEX (RANGE SCAN) OF 'TAB2_PK' (NON-UNIQUE) (Cost=44 Card=1)
: 7 6 INDEX (RANGE SCAN) OF 'TAB1_PK'(UNIQUE) (Cost=11 Card=2250
Bytes=36000)
: 8 6 TABLE ACCESS (FULL) OF 'TAB2'(Cost=337 Card=252624
Bytes=18441552)
It is a little odd that the TAB2 cost on the access by ROWID is
identical to the cost for the tablescan. It's possible, but a bit of a
coincidence. This may be a case where you can't trust explain plan
(autotrace). Do the STAT lines from an sql_trace agree with the
autotrace ? (The CARD = 1 on the PK line is also suspect, but
Oracle does have a number of little reporting errors).
My guess at this point is that it's the bind variable thing -
Oracle is not peeking - it is using a coded constant.
so
search_col like upper('billy%')
does not produce the same cardinality as
search_col like upper(:bindvar)
The literal allows the optimizer to compare the
BILLY range with the low and high for the column,
and it's deciding on a large fraction of the table.
Does a large fraction of the data start with the
first few characters you are supplying ?
It looks like the bind variable option is just taking
the standard 5% that is normally used for an
unbounded range with bind variable. (Technically,
I think Oracle should consider it a bounded range,
but the upper() may be causing extra confusion).
I've just been sent another example of a similar oddity
in 10.1.0.2, where a change from a literal to a bind,
coincidentally including the upper() function also produced
an unexpected change of plan. In the 10.1 case, though,
it was easy to see a bug in the 10053 trace.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: <ryan.gaffuri@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 15, 2004 2:34 PM
Subject: Re: Re: cardinality in query plans?
: ok, was confused on the cardinality. I thought I was onto something with
that. No its version 9.2.0.3
:
: I need to reformat the tables so I can change column and table names.
:
: tab1 has 16m rows
: tab2 has 870,000 rows.
:
: Here it is. Its very hard to format query plans over email. So please bare
with me.
:
: I'm at a loss. I'm forced to hint in 9.2.0.3 and I do not know why. I have
a simple two table joined. I have analyzed both tables as follows
:
: exec dbms_stats(cascade=>true). I know the hint is better because of your
runstats output. Query two has the hints.
:
: SQL> exec runstats_pkg.rs_stop(500);
: Run1 ran in 1456 hsecs
: Run2 ran in 1288 hsecs
: run 1 ran in 113.04% of the time
:
: Name Run1 Run2 Diff
: LATCH.simulator hash latch 898 257 -641
: STAT...redo size 72,380 73,300 920
: STAT...table fetch by rowid 1 6,293 6,292
: STAT...buffer is pinned count 0 7,942 7,942
: STAT...session logical reads 14,490 5,535 -8,955
: STAT...consistent gets 13,945 4,982 -8,963
: STAT...no work - consistent re 13,897 4,927 -8,970
: STAT...buffer is not pinned co 13,858 4,812 -9,046
: STAT...table scan blocks gotte 13,856 168 -13,688
: LATCH.cache buffers chains 30,587 12,668 -17,919
: STAT...session uga memory max 0 27,592 27,592
: STAT...table scan rows gotten 870,888 130 -870,758
:
: Run1 latches total versus runs -- difference and pct
: Run1 Run2 Diff Pct
: 33,803 14,975 -18,828 225.73%
:
: PL/SQL procedure successfully completed.
:
: The interesting thing is that if I use bind variables Oracle chooses the
correct plan, if I don't oracle does not.
:
: I'm forced to change table and column names. So please bare with me.
:
: Here is the query and plan without hints or bind variables.
: I'm attempting to format this by hand so its readable. Hope it helps.
:
: Here is the query:
:
: select /*+ ordered index(a TAB2_IND) */ COL3, COL4, COL5
: from TAB2 a, TAB1 b
: WHERE a.col1 = b.col1
: AND a.col2 = b.col2
: AND a.col3 = b.col3
: and col6 = 'hello'
: and a.searchCol like upper('billy%')
: order by a.hh_nm, a.hh_id, a.acct_no
:
:
: Execution Plan
: ----------------------------------------------------------
: 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=353 Card=1 Bytes=140)
: 1 0 VIEW (Cost=353 Card=1 Bytes=140)
: 2 1 COUNT (STOPKEY)
: 3 2 VIEW (Cost=353 Card=1 Bytes=127)
: 4 3 WINDOW (SORT) (Cost=353 Card=1 Bytes=89)
: 5 4 WINDOW (SORT) (Cost=353 Card=1 Bytes=89)
: 6 5 HASH JOIN (Cost=351 Card=1 Bytes=89)
: 7 6 INDEX (RANGE SCAN) OF 'TAB1_PK'(UNIQUE) (Cost=11 Card=2250
Bytes=36000)
: 8 6 TABLE ACCESS (FULL) OF 'TAB2'(Cost=337 Card=252624
Bytes=18441552)
: Statistics
: ----------------------------------------------------------
: 0 recursive calls
: 0 db block gets
: 13745 consistent gets
: 0 physical reads
: 0 redo size
: 3379 bytes sent via SQL*Net to client
: 662 bytes received via SQL*Net from client
: 3 SQL*Net roundtrips to/from client
: 2 sorts (memory)
: 0 sorts (disk)
: 25 rows processed
:
: CASE 2 Now with hints or bind variables:
:
: Execution Plan
: ----------------------------------------------------------
: 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=346 Card=1 Bytes=140)
: 1 0 VIEW (Cost=346 Card=1 Bytes=140)
: 2 1 COUNT (STOPKEY)
: 3 2 VIEW (Cost=346 Card=1 Bytes=127)
: 4 3 WINDOW (SORT) (Cost=346 Card=1 Bytes=89)
: 5 4 WINDOW (SORT) (Cost=346 Card=1 Bytes=89)
: 6 5 HASH JOIN (Cost=344 Card=1 Bytes=89)
: 7 6 INDEX (RANGE SCAN) OF 'TAB2_IND'(UNIQUE) (Cost=11 Card=2250
Bytes=36000)
: 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2' (Cost=337 Card=43539
Bytes=3178347)
: 9 8 INDEX (RANGE SCAN) OF 'TAB2_PK' (NON-UNIQUE) (Cost=44 Card=1)
: Statistics
: ----------------------------------------------------------
: 0 recursive calls
: 0 db block gets
: 4764 consistent gets
: 0 physical reads
: 0 redo size
: 3379 bytes sent via SQL*Net to client
: 662 bytes received via SQL*Net from client
: 3 SQL*Net roundtrips to/from client
: 2 sorts (memory)
: 0 sorts (disk)
: 25 rows processed
: Now its choosing the correct plan.
:
: The indexes are as follows
:
: TAB1_PK (col1,col2,col3,searchcol)
: TAB2_IND (searchCol,col1,col2,col3)
:
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
