| Performance of Package and Procedure [message #137606] |
Thu, 15 September 2005 12:04  |
azeem87
Messages: 116
Registered: September 2005
Location: dallas
|
Senior Member |
|
|
Hi all Gurus
I joined a new project and i am told to do performance of existing package and procedure there are atleast 5-8 packages and more than 40 procedures,What is the best strategy,steps and tools i should adopt for tuning of these code.
Thanks
|
|
|
|
|
|
| Re: Performance of Package and Procedure [message #137614 is a reply to message #137610] |
Thu, 15 September 2005 12:33  |
azeem87
Messages: 116
Registered: September 2005
Location: dallas
|
Senior Member |
|
|
Thanks Martin
Let's say 1 package having 10 procedure and 10 functions in it, currently it is running end users say it is slow performance is affectd,there fore my question is how to come up where it is going slow ,which area to be concentrated,best tool and steps to tune PL/SQL code
Thanks
[Updated on: Thu, 15 September 2005 12:36] Report message to a moderator |
|
|
|
| Re: Performance of Package and Procedure [message #137619 is a reply to message #137606] |
Thu, 15 September 2005 13:41 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
First, read the sticky in this forum and do everything it says.
Second, time those 10 functions and see which are the slowest. Start there as they will give you the biggest return on your investment.
You may also look into built-in packages like dbms_profiler and dbms_trace.
|
|
|
|
| Re: Performance of Package and Procedure [message #144054 is a reply to message #137606] |
Mon, 24 October 2005 17:52 |
SQLTraceViewer
Messages: 5
Registered: October 2005
|
Junior Member |
|
|
There is a tool to help you identify the most time-consuming SQLs in the processes. You still need to do sql trace and use TKPROF to format data. Then you can use the tool named SQLTraceViewer:
SQLTraceViewer is developed to help Oracle DBAs or Developers with their task of performance tuning. It is a Java-based tool to view Oracle SQL trace data formatted by TKPROF. The Viewer can filter and sort data, so it can help to find the most time-consuming SQL statements with great ease.
Consider the following scenario: A business application built with Oracle has many complicated processes. One of the processes is taking a long time to run and the Oracle developer decides that the cause may be in one or many of the SQL statements in that process. The developer enables SQL trace and obtains trace data, then formats the data with TKPROF. After that, the developer screens through the trace data statement by statement to find the problematic SQL. Due to large number of statements in the formatted SQL trace file, this screening process is taking a long time, and sometimes the developers fails to find the most time-consuming SQL on the first attempt. With the help of SQLTraceViewer, the screening process is much more efficient and accurate. The developer opens the file with SQLTraceViewer and, after filtering and sorting data, the developer finds the time-consuming SQL right away!
It's freely downloadable from:
http://sqltraceviewer.jarlib.com/
And with little money contributed to make it better, you can get support and future new features.
SQLTraceViewer 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
