Thanks for the question, KQ.
Asked: May 09, 2011 - 3:29 pm UTC
Last updated: September 18, 2020 - 4:29 pm UTC
Version: 11.1.0.7
Viewed 10K+ times! This question is 
You Asked
Tom, your site has been very usefull, and usually I can get an answer by looking at past responses. However, this time I need to ask you a question.
An application analyst wants to track the total number of SQL executed per day in Prod database, and look at long term trends. I created the below query to get this info from V$SQL. After running for a few days, I found that one day the total executions went down. I was expecting a steady increase.
(Q1) Why would sum(executions) decrease if the database has not been restarted ?
(q2) Am I using the correct method to determine the total number of SQL executions since database was started ?
Ran below query for several days.
select i.instance_name "Database",
to_char(i.startup_time,'YYYY-MON-DD HH24:MI') "Started",
to_char(sysdate,'YYYY-MON-DD HH24:MI:SS') "Current Time",
sum(s.executions) "Tot Executions"
from v$instance@lk_prod1 i, v$sql@lk_prod1 s
group by i.instance_name, i.startup_time ;
Database Started Current Time Tot Executions
--------- ----------------- -------------------- ----------------
prod1 2011-FEB-15 16:15 2011-MAY-06 14:50:30 8,155,573,002
prod1 2011-FEB-15 16:15 2011-MAY-06 23:59:30 8,264,711,464
prod1 2011-FEB-15 16:15 2011-MAY-07 23:59:30 8,310,760,904
prod1 2011-FEB-15 16:15 2011-MAY-08 23:59:30 8,287,844,674 ??
prod1 2011-FEB-15 16:15 2011-MAY-09 14:21:18 8,391,105,271
On May 8, 2011 23:59, the total executions went down !. Did not expect this.
Appreciate your help
and Tom said...
v$sql is a v$ table, it is an in memory data structure of finite size.
All this means is that some sql aged out or was invalidated in your shared pool. Your shared pool - which is what v$sql shows you - is a data structure that grows and shrinks over time.
You would have to use AWR/ASH or statspack repositories for this type of analysis.
And it is a rather silly, meaningless metric. I question the validity of using it for "anything". What insight does this analyst hope to get from this? Knowing that the number of executions went up or down would only lead to more questions, it would provide no answers.
If the analyst really wants to "trend", you would get them hooked on Enterprise Manager and the AWR/ASH repositories - or if you do not have that, statspack at the very least.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment