OraFAQ Forum: Server Administration » Query to find count of executions hourly for the last 30 days (merged)

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to find count of executions hourly for the last 30 days (merged) [message #677605]

Mon, 30 September 2019 12:55

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

Report message to a moderator

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

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

Report message to a moderator

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

BlackSwan
Messages: 26766
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?

Report message to a moderator

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

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!

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

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!

Report message to a moderator

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

JPBoileau
Messages: 88
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.

Code: [Select all] [Show/ hide]

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;

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 14 07:10:08 CDT 2025