• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

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.

1 Solution
select executions from v$sql  where sql_text = 'your-sql-statement-here'

that is only approximate.

If your statement runs infrequently your statement will get aged out after awhile.

Also, some statements "should" be the same but aren't.

select * from my_table where my_col = 'A'
select * from my_table where my_col = 'B'

"probably" should be a single statement with a bind varaible for my_col.  Not always, but often.

Conversely, if you use cursor_sharing (I hopefully you don't) then statements that SHOULD be different might not be.  If you have legitmate constants in your statement, cursor_sharing will strip them out and replace them with bind variables, causing all the versions of the query to look the same.

However, querying v$sql is probably your best bet
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.

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
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.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now