Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

number of times sql execution

Posted on 2007-11-16
7
Medium Priority
?
924 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:dgupta70
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 20300608
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'
and
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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20302966
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
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 20304150
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.
0
 
LVL 5

Expert Comment

by:sora
ID: 20310195
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20312262
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.

0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question