Re: Problem with understanding Optimization methods.

Eva,

Any chance this system is CPU-bound? I have seen similar problems when cpu-intensive operations are performed. Another thought is to check your temporary tablespace settings. When in doubt, <okay list, repeat after me...> "Do a 10046, level 8 trace"

Execution plan 1 does a sort which will use the temp tablespace and be cpu intensive.
Plan 2 is probably reading the data in sorted order (without seeing the exact table/index info this is a guess), which does not require a sort.

IIRC, an index will be considered when the ORDER BY clause is invoked to remove the need for a separate sort operation.

Daniel Fink

Denham Eva wrote:

> Hello Listers,
>
> A normal sql query from a data warehouse tool called Sagent.
> SELECT COL1, COL2, COL3
> FROM TABLE
> ORDER BY 3;
>
> The table has approximately 2 mil records.
> table has 22 indexes.
>
> The database is set up optimizer CHOOSE.
> I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
> OS is Win2k
> ORACLE 81741
>
> OK, when doing a explain plan on the above sql, I get the following...
> SELECT STATEMENT Optimizer Mode=CHOOSE
> SORT ORDER BY
> TABLE ACCESS FULL TABLENAME -- Very slow and takes
> hours!
>
> When adding the hint /*+RULE*/ for example I get
> SELECT STATEMENT Optimizer Mode=Hint:RULE
> TABLE ACCESS BY INDEX ROWID TABLENAME
> INDEX FULL SCAN TABLE_INDEX --
> Much faster!!!
>
> Have I given enough info that anyone can explain why the CHOOSE mode insists
> on doing a TABLE ACCESS FULL?
> Is there anything I can do to improve performance? Please remember that this
> query comes from a Data Warehouse tool and hence does not appear to accept
> hints.
>
> Any help will be much appreciated!
> Denham
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Denham Eva
> INET: EVAD_at_TFMC.co.za
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).