Re: Help with query...

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 03 May 2004 12:33:48 -0700
Message-ID: <1083612826.295511@yasure>

Comments in-line

Andrew Metcalfe wrote:

>>Are statistics current?

>
>
> No. I figured this out last night. This is problematic.
>
> I've tried a "analyze table compute statistics", but this took an
> unreasonable amount of time... I eneded up killing the session so I
> could try something else.

Next time read the documentation. Use statistical sampling.

> The table has 7 million records. Whats a reasonable amount of time to
> wait?
>

>>Were they created with DBMS_STATS?

>
> No.

Oracle no longer recommends using analyze table. Go to http://tahiti.oracle.com. Learn how to use DBMS_STATS with statistical sampling.

>>On tables only or on tables and indexes?
>>What is the setting for optimizer_index_cost_adj?

>
>
> Default is 100, I believe. My init.ora doesn't reference this
> setting.

Try changing it to 50 and tune from there.

>>What is the stting for optimizer_index_caching?

>
>
> Default is 0, I believe. My init.ora doesn't reference this setting.

Try changing it to 50 and tune from there.

>>What percentage of the rows in the table will this retrieve?

>
> 30,000 of 7 million - 0.0043%
>
> _Am

Then an index seems to be appropriate. My guess is lack of statistics and improper settings of the init parameters.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon May 03 2004 - 14:33:48 CDT