You have to ru explain plan for that statement.
You first have to create the PLAN_TABLE, you can do that running the script
$ORACLE_HOME/rdbms/admin/utlxplan.sql
Then, login to sql and run th following statement:
SQL> explain plan for your-sql ;
then, run the following statement to obtan the execution plan:
select lpad (' ',2*(level-1))
||operation||' '||options||' '||
object_name||' '||decode
(id,0,'cost= '||cost||' Optimizer= '||optimizer||' Bytes= '||bytes) "QUERY
PLAN"
from plan_table
start with id=0
connect by prior id=parent_id
It will tell you the indexes being used.
Best luck
----- Original Message -----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, April 19, 2001 4:31 PM
> Hi All,
> Recently we moved the database(Oracle 8.0.5) from NT to Sun machine. Now
> this database is going online very soon. When we were testing we
> observed
> that, the application (select statement) is running very slow. (I don't
> know
> how fast it was earlier). I have a feeling that it is not using indexes
> properly. I have created 6 indexes on that table on each columns. also
> created a composite indexes. How do I know that the sql is using which
> index
> for select statement.? Is there any other ways to improve the
> performance?
>
> Thanks
> Uday
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Claudio Roca
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
