title: How to find the cost of a stored procedure in Oracle and optimize it
body: <p>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.<br>
  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.<br>
  In addition to that I would like suggestions to optimize my stored procedure especially the query given below.<br>
  Logic of the procedure:<br>
  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.<br>
  My objective is to find out the cost of the proc as well as optimize the sp. </p>
  
  <pre><code>SELECT DISTINCT acct_no    
  FROM raw
  WHERE 1=1
  AND code = ''' || code ||
  ''' AND qty       &lt; 0  
  AND acct_no 
  IN (SELECT acct_no FROM ' || table_name || ' WHERE counter = 
  (SELECT MAX(counter) FROM ' || table_name || '))
  </code></pre>