Re: Oracle/SQL Question

EXPLAIN PLAN i s the answer.
You need to run UTLXPLAN.SQL in your database (this will create PLAN_TABLE). This SQL will be normally located under ORACLE_HOME/rdbms/admin. The exact path is platform dependant.

Then issue
EXPLAIN PLAN
SET statement_id = 'MY STMT'
FOR
Select * FROM my_table
Where col1 = something;

This command will evaluate the execution plan, without actually running the query. The PLAN_TABLE will contain the output of EXPLAIN PLAN command. Now you can query on the PLAN_TABLE as follows:

SELECT
LPAD(' ',2*LEVEL)||operation||' '||' '||options||object_name MY_PLAN FROM plan_table
WHERE statement_id = 'MT STMT'
CONNECT BY prior id = parent_id and statement_id = 'MY STMT' START WITH id=1;

This query will list the execution plan in a readable format. You can see if there is a table scan, or if the indexes are used etc. If you do not understand the output, your DBA should be able to tell you if the query is good or not.

Good luck,
CM

In article <lorrie-ya02408000R1304981946460001_at_enews.newsguy.com>,   lorrie_at_macconnect.com (Lorrie) wrote:
>
> I am an Oracle developer. When I am developing SQL statements and tuning
> them, I need to run them over and over again, to see the performance effect
> of changes to the statement, and optimization mode. I also need to see how
> long it takes each statement to run. Also, sometimes when I drastically
> change
> the statement, and it uses the wrong index, the statement goes on for ever.
> Then I have to kill the application, and then ask my DBA to kill the orphan
> client session. Are there any tools out there that could help me with any
> of these problems/requirements?
>
> TIA,
> Lorrie
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Apr 14 1998 - 11:09:37 CDT