Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle/SQL Question

Re: Oracle/SQL Question

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 14 Apr 1998 15:13:04 GMT
Message-ID: <6gvue0$jqr2@hendrix.csufresno.edu>


In article <lorrie-ya02408000R1304981948290001_at_enews.newsguy.com>, Lorrie <lorrie_at_macconnect.com> 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 ?

For developing and tuning SQL statements, I find that using SQL Plus and my favorite editor, PFE, in a Windows environment is quite efficient and all I need. I also have an "Explain Plan" script that I run with the command @Explain which gives me all the information I need. (I'll pass along the script if anyone wants it.)

To get an explain plan, all I do is type: EXPLAIN PLAN FOR on a separate line, then swap over to the editor and copy the select I want to try, swap back to SQL Plus, and paste (with Shift-Insert) the text of the select. Follow that with @EXPLAIN, and it's done.

If you have a system that can "take forever" when you use the wrong select or index, you should develop a smaller test database to try your work on first. Also, do an explain plan on anything you are unsure about before you really execute it on your large database. As for killing the application, your orphan session should go away after a while automatically.

Regards,
Steve Cosner
http://members.aol.com/stevec5088 Received on Tue Apr 14 1998 - 10:13:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US