| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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
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
CASE 2 Now with hints or bind variables:
Execution Plan
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
The indexes are as follows
TAB1_PK (col1,col2,col3,searchcol)
TAB2_IND (searchCol,col1,col2,col3)
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Date: 2004/03/15 Mon AM 09:24:09 EST
> To: <oracle-l_at_freelists.org>
> Subject: Re: cardinality in query plans?
>
>
> How about giving us a clue -
>
> Which version of Oracle ?
>
> Two tables - we could probably cope with
>
> select {small (subset) list of columns}
> from {two table names}
> where {list of join predicates}
> and {list of filter predicates}
>
> And an execution plan on a two-table joins
> could only be a maximum of 8 lines.
>
> I'll take a guess at the bind variable question, though:
> your query is using the bind variables to specify a range.
>
> And if you used the same values for the binds as you
> did for the original query, you must be on Oracle 8,
> otherwise bind variable peeking would have produced
> the same plan.
>
> The cardinality is the number of rows returned from
> that step of the plan - not the number input - so with
> a table of 8M rows, the cardinality could literally be
> any integer between zero and 8M on the scan line.
>
>
> 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_at_cox.net>
> To: <oracle-l_at_freelists.org>
> Sent: Monday, March 15, 2004 1:50 PM
> Subject: cardinality in query plans?
>
>
> : I'm doing a two table join and both tables are analyzed. Oracle is
> incorrectly choosing a full tables scan over an index search(I tested it,
> index search has 1/4 the logical I/Os).
> :
> : When I see the cardinality for the full tablescan I see 262,000. However,
> when I do a count(*) of the table or check num_rows in dba_tables I see
> 870,000 records. I'm assuming this is why Oracle is choosing the full table
> scan.
> :
> : table is analyzed as follows:
> :
> : exec dbms_stats.gather_table_stats(cascade=>true).
> :
> : this interesting part is that when I use bind variables Oracle chooses the
> proper plan. Any ideas?
> :
> :
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 - 08:31:13 CST
 |
 |