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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: cardinality in query plans?

Re: Re: cardinality in query plans?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 15 Mar 2004 15:18:11 -0000
Message-ID: <022201c40aa0$c07191d0$7002a8c0@Primary> 






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







: 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_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 Mon Mar 15 2004 - 09:14:52 CST












Original text of this message













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