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
Messages: 35
Registered: January 2008
Location: Dubai
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.
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
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:


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

Re: Performance analysis of package [message #299239 is a reply to message #299232] Mon, 11 February 2008 00:18 Go to previous message
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: Mon Oct 24 02:52:09 CDT 2016

Total time taken to generate the page: 0.05377 seconds