Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: help figuring out why query doing full table scan
I don't think that's the answer, David.
For the hash join there are no conditions
to eliminate rows from USER (how do you
get a table with a reserved word as its name?)
so the reported cardinality will match the number
of rows in the table, and histograms will not
affect that..
I would guess (and I stress guess) that the key is in the numbers, though.
The cardinality of the join (i.e. result set) is 20,492 so Oracle has probably worked out that getting the data through a nested loop will require 20,492 index probes and table access for a cost of 20,492 * 5 plus the 1970 cost of the scan on SUMMARY_BILLING_EVENT A total of 104,430 - much higher than the cost of the hash join.
If you know that Oracle's caching of the relevant index is going to make its I/O cost incorrect, you could adjust the parameter
optimizer_index_cost_adj
Default value = 100 - implying that every index block request
will turn into a physical I/O - set it to (say) 10% to indicate
that a large fraction of index block requests will be met from
the buffer cache.
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley See a first review at: http://www.ixora.com.au/resources/index.htm#practical_8i More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html David Fitzjarrell wrote in message <91tncv$mmf$1_at_nnrp1.deja.com>...Received on Thu Dec 21 2000 - 15:02:20 CST
>In our last gripping episode bad <bad_at_yahoo.com> wrote:
>> Running Oracle 8.0.5 on Solaris 2.6
>>
>> We have the following statement within a stored procedure that has all
>> the sudden
>> decided to do a full table scan on the user table. I don't understand
>> why it all the sudden started doing this, as the statistics are up to
>> date. The USER table has tens of millions of rows, so this just isn't
>> going to work. Any ideas???????
>>
>> SELECT COUNT(DISTINCT USER.USER_ID || '@' || USER.USER_DOMAIN )
FROM
>> USER,SUMMARY.SUMMARY_EVENT SE WHERE USER.USAGE_USED_BE_ID =
>> SE._EVENT_ID AND SE.CUSTOMER_SUMMARY_ID = :b1
>>
>> With the following explain plan output.....
>>
>> SELECT STATEMENT Optimizer=CHOOSE (Cost=84353 Card=1 Bytes=2991832)
>> SORT (GROUP BY)
>> HASH JOIN (Cost=84353 Card=20492 Bytes=2991832)
>> TABLE ACCESS (BY INDEX ROWID) OF SUMMARY_BILLING_EVENT (Cost=1970
>> Card=6002 Bytes=168056)
>> INDEX (RANGE SCAN) OF SBE_CUSTOMER_SUMMARY_ID_IDX (NON-UNIQUE)
(Cost=47
>> Card=6002)
>> PARTITION (CONCATENATED)
>> TABLE ACCESS (FULL) OF USER (Cost=73345 Card=32531311
Bytes=3838694698)
>>
>>0
>
>The key is the Card value for the scan of USER -- the optimiser is
>expecting 32,531,311 rows to be returned. It would appear that your
>indexed column values are not evenly distributed; I would try
>rebuilding the index or indexes on USER and create histograms. This
>will indicate to the optimiser the skew of the data.
>
>--
>David Fitzjarrell
>Oracle Certified DBA
>
>
>Sent via Deja.com
>http://www.deja.com/