Link to home
Start Free TrialLog in
Avatar of dgupta70
dgupta70

asked on

number of times sql execution

I am DBA , one developer gave me an sql and asked me to find out how many times this sql gets executed in the DB. Is there a way to find out how many tiems sql got executed?

Please let me know.

Thanks in advance.

Deepak
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To be precise, you cannot find that one exactly.

But as updated you can find that one approximately ( meaning if shared sql area contains your sql statement then you can find the available count of the execution times which might not be the correct one or may be the correct one ... provided it is not cleared out of the shared area by oracle which depends on lot of other things like .... )

But there is no guarantee that one has to agree to your results. Let us say, if you tell me your sql got executed 10 times, then if i am developer then i will not take it for granted that 10 is number of times it got executed instead it is an estimate/approximate which we got when DBA looked into the V$SQL for this statement.

Thanks
After you've found your SQL in V$SQL, you can always use DBMS_SHARED_POOL.KEEP to ensure it will never be flushed out of the library cache -after that, EXECUTIONS will be reliable ;-)

Oh, and if your developer does not use bind variables, go to the nearest police station and open a case against him: he's using more memory than needed, making code more prone to SQL injection and causing hard parses which kill CPU and raise wait times through latching.
simple. either trace the session and do a TKPROF which will tell you exactly the number of times it was executed
OR
use statspack at regular intervals. then when you generate the statspack report using a begin time and an end time, the SQL (if its high in number of executions) will get reported in the statspack report.
Avatar of Sean Stuber
Sean Stuber

I already answered this question, but I think I need to update it.

I think a more appropriate answer than querying v$sql or tracing is to put it back on the developer "how many times is it executing".  The developer wrote the code, right?  Have them instrument it.

Then you know not just how many times, but when, who ran it, with what parameters, etc.

Everything else is just an approximation.  Even if you force the statement to stick around with the "KEEP" command suggested above, that can still be skewed by cursor_sharing.  Because that will cause the modified version to be kept.  If you don't have binds and don't use cursor sharing then you have the problem of which one do you keep?

And, if the statement is called from a package called many times from many places, then which session do you trace?  To be totally accurate and encompassing, the application code must be properly instrumented.