Home » SQL & PL/SQL » SQL & PL/SQL » Performance analysis of package (Oracle 8.0, Pl/SQL)
Performance analysis of package [message #299226] Sun, 10 February 2008 23:19 Go to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Guruji,

I have modified a package. Changed the approaches of the sql queries. It performs well.

1. Earlier the package was taking approximately 1 hr and 30 minutes. Now it is taking only 20 mts.

2. Earlier it was comparing more than 10 million records. After the 'WHERE' clause changes, it is now comparing only 5000 records. (I checked it by using count(*)).

Now, I need to take a comparitive statistics on both earlier package and modified package. Like time taken, query hitting records/rows, performance,etc. What to do and how to do? Is the 'Statistics' in the Pl/Sql Developer tool, useful one?


Thanx in advance.
-Lenin.
Re: Performance analysis of package [message #299232 is a reply to message #299226] Sun, 10 February 2008 23:56 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
You might need to consider using "EXPLAIN PLAN". I hope it gives you the performance information you are looking for.

Here is a useful link that can get you started:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm

Let us know if this is what you are looking for.

Regards,
Baz
Re: Performance analysis of package [message #299239 is a reply to message #299232] Mon, 11 February 2008 00:18 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can use SQL Trace and TKProf to trace each session and compare the statistics generated.
Previous Topic: simple insert statement using select and values
Next Topic: date formate problem?
Goto Forum:
  


Current Time: Fri Dec 09 19:47:33 CST 2016

Total time taken to generate the page: 0.08790 seconds