Solved

number of times sql execution

Posted on 2007-11-16
7
909 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 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now