Re: I am confused with this CBO issuse

Evan Cao wrote in message <362C0C04.5DB5BBD6_at_ti.com>...

>There is an unique index on table xxx on column say index_0 on
>xxx(c1,c2);
>select * from xxx where c1=:a and c2=:b;
>
>Noticed by an SAP monitor, ORACLE is doing a full table read, taking
>long time, which is understandable.

Never used the SAP monitor myself - are you sure that it's reporting what you think it is reporting? Any idea what SQL it uses to determine if a session is doing a FTS (full table scan)?

>what
>I am confused is that when I explain this stmt. I see ORACLE using the
>index index_0

If the EXPLAIN PLAN says Oracle is using an index then it will be using that index. No questions there.

>What's wrong here??? What is the logical explaination here?

SAP monitor either sucks by giving you the wrong information, or you are looking or interpreting the data from the SAP monitor incorrectly IMHO.

>BTW. how can we find out ORACLE is doing a full table scan without using
>sql trace?

Rule of thumb. If the session is doing sequential db file reads it's usually a full table scan as it's processing the table's datafiles sequentially. If the session is doing a db file scattered read, it's likely using an index (i.e. Oracle processes the index sequentially but does a "random" access to the table's datafiles). Look at V$SESSION_EVENT.

You can also look at the V$SESSION_WAIT while that session is busy. P1 and P2 contains the file number and block id for the db i/o events. You can look up the block's owner and type of data stored there (i.e. index of table data) in DBA_EXTENTS.

Hopes this help.

regards,
Billy Received on Tue Oct 20 1998 - 00:57:07 CDT