Home » RDBMS Server » Server Administration » Query to find count of executions hourly for the last 30 days (merged) (Oracle Enterprise Edition, 11.2.0.4, Oracle Linux X86-64, version 5)
Query to find count of executions hourly for the last 30 days (merged) [message #677605] Mon, 30 September 2019 12:55 Go to next message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
Hi,

I am trying to find a particular sql (using sql id) executions hourly and daily counts for the last 30 days. Can anyone has command please?

I have tried with dba_hist_active_sess_history and dba_hist_sqlstat for the last 1 hour to test if it gives correct result. But the output doesn't match with sum of counts in each instance taken from awrsqlrpt.sql

Thanks,
Suresh
Query to find count of executions hourly for the last 30 days [message #677606 is a reply to message #677605] Mon, 30 September 2019 12:55 Go to previous messageGo to next message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
Hi,

I am trying to find a particular sql (using sql id) executions hourly and daily counts for the last 30 days. Can anyone has command please?

I have tried with dba_hist_active_sess_history and dba_hist_sqlstat for the last 1 hour to test if it gives correct result. But the output doesn't match with sum of counts in each instance taken from awrsqlrpt.sql

Thanks,
Suresh
Re: Query to find count of executions hourly for the last 30 days [message #677607 is a reply to message #677606] Mon, 30 September 2019 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
Why do you expect every SQL that was ever executed to be in the HISTORY views?
What does the documentation say regarding the content of these views?
Re: Query to find count of executions hourly for the last 30 days [message #677608 is a reply to message #677607] Mon, 30 September 2019 15:19 Go to previous messageGo to next message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
I am looking for only one particular query (I have sql id of that query) and I see information related to that sql in dba_hist_active_sess_history. Since it is ASH data and counts are not accurate based on number of rows of the query in history tables, I am facing problem in aggregating the sql count hourly and daily. Could you please provide a query to get the counts? Thanks!
Re: Query to find count of executions hourly for the last 30 days [message #677609 is a reply to message #677608] Mon, 30 September 2019 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
What I know is blah, blah blah from you.
Is COPY & PASTE broken for you?
I don't know what you have.
I don't know what you do.
I don't know what you see.
I don't know what you want.
Re: Query to find count of executions hourly for the last 30 days [message #677610 is a reply to message #677609] Mon, 30 September 2019 15:47 Go to previous messageGo to next message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
I am looking for a query which gives output like below.

Example output:

sql_id . time_of_hour num_of_executions
--------- --------- ----------
nh3bg8aq 9/30/2019 00:00 2856
nh3bg8aq 9/30/2019 01:00 1704
nh3bg8aq 9/30/2019 02:00 6729
nh3bg8aq 9/30/2019 03:00 654
nh3bg8aq 9/30/2019 04:00 2723


Hope this helps. Thanks!
Re: Query to find count of executions hourly for the last 30 days [message #677622 is a reply to message #677610] Tue, 01 October 2019 08:28 Go to previous messageGo to next message
JPBoileau
Messages: 82
Registered: September 2017
Member
I reverse-engineered the AWR report code (well ok, ok, I just sql-traced it) and came up with the following.

However, remember that SQL_ID CAN and DO change, especially when you bounce the server or the shared pool is flushed.

select
   S.begin_interval_time,
   s.end_interval_time,
   T.executions_total
from
   AWR_PDB_sqlstat T,
   DBA_HIST_SNAPSHOT S 
where
   T.sql_id = 'fg0kvac1dkzw5'  -- Insert your own SQL_ID here
   and s.snap_id = t.snap_id
order by 1 desc;

JP
Re: Query to find count of executions hourly for the last 30 days [message #677686 is a reply to message #677622] Sun, 06 October 2019 19:54 Go to previous messageGo to next message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
Thanks JP. I found a way to get the details using DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOTS. However, I will try your query and compare if it see any difference in results.
Re: Query to find count of executions hourly for the last 30 days [message #677687 is a reply to message #677686] Mon, 07 October 2019 00:47 Go to previous message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I found a way to get the details using DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOTS.
So please post it.

Previous Topic: how are the bits in ASSM calculated?
Next Topic: DB12.1 upgrades
Goto Forum:
  


Current Time: Fri Dec 13 06:54:06 CST 2019