CODEWITHSUNDEEP
performanceoracle-databasestored-proceduresplsqlquery-optimization
ramesh
ramesh

Reputation: 300

How to find the cost of a stored procedure in Oracle and optimize it

Can anybody let me know if there is any way to find out cost of a stored procedure in Oracle? If no direct way is there, I would like to know any substitutes.
The way I found the cost is doing an auto trace of all the queries used in the stored procedure and then estimate the proc cost according to the frequency of the queries execution.
In addition to that I would like suggestions to optimize my stored procedure especially the query given below.
Logic of the procedure:
Below is the dynamic sql query used as a cursor in my stored procedure. This cursor is opened and fetched inside a loop. I fetch the info and put them in a varray, count the data and then insert it to a table.
My objective is to find out the cost of the proc as well as optimize the sp. 

SELECT DISTINCT acct_no    
FROM raw
WHERE 1=1
AND code = ''' || code ||
''' AND qty       < 0  
AND acct_no 
IN (SELECT acct_no FROM ' || table_name || ' WHERE counter = 
(SELECT MAX(counter) FROM ' || table_name || '))

Upvotes: 1

Views: 18480

Answers (4)

yogesh anand
yogesh anand

Reputation: 1

Basically we can find the cost of the execution query by checking the explanation plan/execution plan, where the cost of the query will.be mentioned , This would be the basis and first step to find the cost of the query,

To find the cost of the program ,we can also use the profiler with utility_get_time;

Where we can able to find program execution time .

Upvotes: 0

APC
APC

Reputation: 146209

The way to find out the cost (in execution of time) for a stored procedure is to employ a profiler. 11g introduced the Hierarchical Profiler which is highly neat. Find out more.

Prior to 11g there was only the DBMS_PROFILER, which is good enough, especially if your stored procedure doesn't use objects in other schemas. Find out more.

Trace is good for identifying poorly performing SQL. Profilers are good for identifying the cost of the PL/SQL elements of a stored proc. If your proc has some expensive computation elements which don't read or write to tables then that won't show up in SQL trace.

Likewise if you have a well-tuned SQL statement but use it badly ia profiler run is likely to be more help than trace. An example of what I mean is repeatedly executing the same SELECT statement inside a Cursor loop: I know that's not quite what you're doing but it's close enough.

Apparently the hierarchical profiler DBMS_HPROF is installed by default in 11g but a DBA has to grant some privileges to developers who want to use it. Find out more.

To install the DBMS_PROFILER in 10g (or earlier) a DBA has to run this script:

$ORACLE_HOME/rdbms/admin/proftab.sql

Be sure to get the reporting infrastructure as well: 

$ORACLE_HOME/plsql/demo/profsum.sql

(The name or location of this script may vary in earlier versions).

Upvotes: 4

haki
haki

Reputation: 9759

The easy way is to execute the procedure and then query v$sql. if you want a little tip to make your life easier (not just for packages) add a blank comment to the query inside the procedure, something like 

select /* BIG DADDY */ * from dual;

and then query v$sql as follows 

select * from v$sql where sql_text like '%BIG DADDY%';

the best way is definitely the way @Vincent Malgrat suggested.

good luck.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

One of the best tool in analyzing SQL and PLSQL performance is the native SQL trace.

  1. enable tracing in your session:

    SQL> alter session set SQL_TRACE=TRUE;

Session altered

  2. Run your procedure

  3. Exit your session

  4. Navigate to your server udump directory and find your trace file (usually the latest)

  5. Run tkprof

This will produce a file containing a list of all statements with lots of information, including the number of times each was executed, its query plan and statistics. This is more detailed and precise than manually running the plan for each select.

If you want to optimize performance on a procedure, you would usually sort the trace file by the time taken to execute (with sort=EXEELA) or fetch SQL and try to optimize the queries that make the most work.

You can also make the trace file log wait events by using the following command at step 1:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Upvotes: 6

Related Questions