Re: SQL Execution Plan

Steve J wrote:

> I'm running Oracle 9.2.0.5 on a Sun box with the optimizer mode set to
> CHOOSE.
>
> I'm running a query against a table which has ~30 million rows in it.
> The table has a date column which is indexed called created and it
> can't be null. The table has about a year and a half of data which is
> pretty much evenly skewed per day.
>
> We're using DBMS_STATS to apply stats:
>
> exec dbms_stats.gather_schema_stats(ownname => 'NGDREP', cascade =>
> true, -
> estimate_percent => dbms_stats.auto_sample_size, method_opt=>'for all
> indexed columns size AUTO', -
> degree => DBMS_STATS.DEFAULT_DEGREE);
>
> The stats are a couple of days old. When we look at the plan for the
> following query it does a full table scan:
>
> SELECT
> created,
> par_evt_id,
> name
> FROM
> activity
> WHERE
> SELECT
> created,
> par_evt_id,
> name
> FROM
> activity
> WHERE
> created between to_date('11/07/2004 00:00:00','mm/dd/yyyy
> hh24:mi:ss') and to_date('11/13/2004 23:59:59','mm/dd/yyyy
> hh24:mi:ss')
> order by created
>
> SELECT STATEMENT **CHOOSE** Cost=66875
> SORT ORDER BY
> TABLE ACCESS FULL SIEBEL.S_EVT_ACT **ANALYZED**
>
> The FTS takes 15-20 minutes to run vs < a minute when the index is
> being used.
>
> We've had similar problems with other more complex queries not using
> indexes on date columns.
>
> Any thoughts?
>
> Thanks...

Get rid of Siebel ... it is a pile of .....

As likely you can't upgrade to 10g and use the ADDM to rewrite their pathetically damaged SQL statements.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)