To what Mladen said about the optimizer_blah parameters, there is another thing which
can be added. Most third-party software is over-indexed (as you have noticed) because
they optimistically want to cover *all* possible cases and quite often a column which
is highly discreminant at one site contains a single value elsewhere and so on. What I
mean is that it is common to have totally skewed distributions, and AFAIK collecting
column statistics for the indexed columns isn't the default. Try it, the optimizer may
then have more information, and pick intelligently the index which it picks randomly
in rule mode. Especially if you follow Mladen's advice about cost adjustment.
HTH,
SF
>----- ------- Original Message ------- -----
>From: Mladen Gogala <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Tue, 06 Jan 2004 22:09:24
>
>You can find out by employing the event 10053, lev
>8. Looking from afar, however, it seems more
>likely that you haven't configured your CBO
>properly. Here is something you can try:
>
>Execute the following commands:
>
>alter session set optimizer_index_caching=40;
>alter session set optimizer_index_cost_adj=25;
>
>
>
>After that, retry the query. If I'm correct,
>optimizer will now know that index I/O is much
>cheaper
>then the table one and will be much more likely to
>select full index scan over the full table scan.
>When you're really, really bored, you can read
>Practical Oracle 8i - Building Efficient Databases,
>
>it has a few pages about the parameters above.
>Read the Gospel of Jonathan and enjoy.
>
>
>
>On 2004.01.07 00:29, 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: Stephane Faroult
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
