| 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: Re: cardinality in query plans?
We updated to 9.2.0.3 from 8.1.7.3 and kept the defaults. I didn't do it. I have not been budgeted
time to test the queries against better settings so I have to stick with the old settings.
Our parameters are
optimizer_index_caching=0
optimizer_index_cost_adj=50
I did an
alter session set optimizer_index_cashing=90
alter session optimizer_index_cost_adj=10
Re-ran the query. It chose an index, but the wrong index. Instead of TAB2 using an index of the form: name,col1,col2,col3
where the col1,col2,col3 are the join columns and name is the column with the like it chooses an
index of the form
col1,col2,col3,name
This query has 565,000 LIOs as opposed to a worst case of 13,000 and best case of 4,700. I try with bind variables and Oracle flips the join order which gets me down to 17,000 LIOs, but still chooses the wrong index.
I'm at a loss.
Below is the new plan without bindvariables.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=456 Card=1 Bytes=152
)
1 0 VIEW (Cost=456 Card=1 Bytes=152)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=456 Card=1 Bytes=139)
4 3 WINDOW (SORT) (Cost=456 Card=1 Bytes=106)
5 4 WINDOW (SORT) (Cost=456 Card=1 Bytes=106)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2' (Cost=2 Card=1 Bytes=90)
7 6 NESTED LOOPS (Cost=452 Card=1 Bytes=106)
8 7 INDEX (RANGE SCAN) OF 'TAB1_pk
' (UNIQUE) (Cost=11 Card=2250 Bytes=36000)
9 7 INDEX (RANGE SCAN) OF 'TAB2_IND2
D' (NON-UNIQUE) (Cost=1 Card=1)
attached mail follows:
yes billy in this case uses a large fraction of the results. 262,000 records out of 870,000. I really would prefer not to hint. I do not have production data yet, so big and small in production may not be the same. however, we are currently in acceptance testing so when the client sees the query we must not have a problem.
is there anyway to provide oracle with better information? The odd thing was that in the past on a different data set, I had to remove bind variables to make this same query work. This is troubling.
I'll attempt to analyze the 10053 trace and I have a TAR open.
Why would Oracle cost a full table scan lower than an index search even in spite of the large number of records? I can clearly tell with runstats_pkg that the index scan is better.
Here is another oddity. If I use hard coded values. I then ONLY hint the index, oracle uses the index, but the incorrect join order. This is happening in a join between TAB2 and another table also.
I am fearful of hints since I cannot predict what the data will look like in production. I may not see the production data at all due to security reasons(not government, its private sector), yet I have to guarantee performance. Per our SLA this has to support 30,000 concurrent users. Now trimming off 9,000 LIOs on a frequently used query is very important to scaling this application.
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Date: 2004/03/15 Mon AM 10:18:11 EST
> To: <oracle-l_at_freelists.org>
> Subject: Re: Re: cardinality in query plans?
>
>
> 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_at_cox.net>
> To: <oracle-l_at_freelists.org>; <oracle-l_at_freelists.org>
> 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_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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 13:24:19 CST
 |
 |