Re: Re: cardinality in query plans?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Mar 2004 16:07:10 -0000
There are a couple of articles of mine on
www.dbazine.com/oracle.shtml which may
explain why Oracle chooses the tablescan
for "billy%" Note, though, that based on the
"billy" value, Oracle is reporting the right
cardinality, and doing the right thing. Oracle
is doing the slow path when it gets the right
answer for the cardinality.
Before you move to hinting, have you examined
system statistics. If you can calibrate the system
so that it has a realistic single-block read time compared
to multiblock read time, the paths may correct themselves
automatically. You also need to get the apparent CPU
speed into the system, so that Oracle can check the
CPU cost of scanning all those rows.
However - before you get to that - are you sure that
the sample query is correct. Oracle defaults to using
a RANGE scan on TAB1_PK according to the plan,
but there appear to be no columns it could be using
to avoid a FULL scan.
Is it possible that there is some other predicate on
TAB1 that is causing the problem (it seems to be
returning only 2250 rows out of 16M in the default
plan) or are the statistics on the TAB1 index totally
misleading ?
(I have no comment to make on TAB2 and the other
table at the moment).
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>
Sent: Monday, March 15, 2004 3:36 PM
Subject: Re: Re: cardinality in query plans?
: 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.
:
----------------------------------------------------------------
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:
