I was asked recently how much “use” a database had – a non too specific question but basically the person asking it wanted to know how many queries, DMLs, DDLs etc.. were run against the database on a daily basis…kind of like the queries per hour metrics that are sometimes quoted on TPC benchmarks I guess.
I didn’t have an answer as to where to get this information so I figured it warranted a ramble through the AWR tables to try and come up with something…
This isn’t going to be a description of how the AWR works – there are manuals for that. Instead, I’ll just give you the salient points from my exploration.
The AWR data, including it’s history, is accessible by looking at a collection of DBA Views named DBA_HIST%.
Part of the AWR processing captures Active Session History (ASH) data which is then accessible via the DBA_HIST_ACTIVE_SESS_HISTORY view. From this view we can obtain, for any given snap point in time, the sessions which were active, together with details of what they were doing and the statistics relating to the performance of that activity.
On this view is a column called SQL_OPCODE which tells us the type of SQL operation being performed by the session, with various codes indicating different things. I didn’t manage to find a definitive list of these SQL_OPCODES in a table/view anywhere so, by trial and error I worked them out as follows:
SQL_OPCODE SQL Operation 1 DDL 2 INSERT 3 Query 6 UPDATE 7 DELETE 47 PL/SQL package call 50 Explain Plan 189 MERGE
…I’m sure this isn’t definitive…someone really smart out there will probably know where this list is actually stored and will, perhaps, put it on a comment to this post 😉
OK, now to code up something that goes back over this AWR data for the last, say seven days, and group it up by SQL operation so we can see what our activity has been – I used a ROW_NUMBER analytic (cos they rock) to make sure we only get the latest snapshot row from the AWR table – if a SQL operation is running for a long time then it may appear in the AWR view more than once…and we don’t want to double count it as far as executions go.
I’ve grouped up those sessions which don’t have an active SQL statement associated with them (i.e. SQL_ID is NULL) into groups based on their Object Type and I’ve then used ROLLUP to get the results for each SQL operation by day and also the summed up totals by operation across the seven days and a grand total.
I’m not actually sure why some rows in DBA_HIST_ACTIVE_SESS_HISTORY don’t have a SQL_ID against them – if they were not actually running a statement in the session then that sounds fair enough…but some of them had values in PLSQL_ENTRY_OBJECT_ID indicating (I think) that they were running a specific package (DBMS_STATS or DBMS_SPACE in many cases) so the fact they didn’t have a SQL_ID was confusing to me – perhaps it means they’re in that package but not actually running a query at the time of the snap – either way, they’re grouped up separately.
Here is a SQL*Plus spool of the code and results from a test system:
x_j4134[543/9757]@AED52> l
1 WITH ash AS
2 (
3 SELECT TRUNC(ash.sample_time) sample_day
4 , (CASE WHEN ash.sql_opcode = 47
5 THEN ‘PL/SQL’
6 WHEN ash.sql_opcode IN(1)
7 THEN ‘DDL’
8 WHEN ash.sql_opcode IN(2,6,7,189)
9 THEN ‘DML’
10 WHEN ash.sql_opcode IN(50)
11 THEN ‘Explain Plan’
12 WHEN ash.sql_opcode IN(3)
13 THEN ‘Query’
14 ELSE ‘No Statement ID; In object type: ‘NVL(o.object_type,’Not Specified’)
15 END) statement_type
16 , ROW_NUMBER() OVER(PARTITION BY ash.sql_id,ash.sql_child_number ORDER BY ash.sample_time DESC) rn
17 FROM dba_hist_snapshot s
18 , dba_hist_active_sess_history ash
19 , dba_objects o
20 WHERE s.snap_id = ash.snap_id(+)
21 AND s.dbid = ash.dbid(+)
22 AND s.instance_number = ash.instance_number(+)
23 AND ash.plsql_entry_object_id = o.object_id(+)
24 AND TRUNC(ash.sample_time) BETWEEN TRUNC(SYSDATE-6) AND TRUNC(SYSDATE+1) — all within last 7 days
25 )
26 SELECT sample_day
27 , statement_type
28 , COUNT(1)
29 FROM ash
30 WHERE rn = 1
31 GROUP BY ROLLUP(sample_day)
32 , ROLLUP(statement_type)
33 ORDER BY sample_day
34* , statement_type
x_j4134[543/9757]@AED52> /
SAMPLE_DAY STATEMENT_TYPE COUNT(1)
——————– —————————————————- ———-
02-FEB-2007 00:00:00 DDL 112
02-FEB-2007 00:00:00 DML 49
02-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 292
Now there is good news is that http://appalachianmagazine.com/category/news-headlines/page/6/ cialis 40 mg impotency caused by cycling is chiefly temporary. This is why these capsules are stated wholesale cialis price as safe diabetes supplements. This medicine starts working within generic cialis viagra half an hour to exhibit its consequences; if the man is sexually elicited. And, tadalafil 5mg no prescription as I have already commented, Voice Broadcasting is one sure fire way of bringing a voice and a face to the relationship. 02-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 7
02-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 131
02-FEB-2007 00:00:00 PL/SQL 301
02-FEB-2007 00:00:00 Query 181
02-FEB-2007 00:00:00 1073
03-FEB-2007 00:00:00 DDL 20
03-FEB-2007 00:00:00 DML 26
03-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 91
03-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 2
03-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 84
03-FEB-2007 00:00:00 PL/SQL 166
03-FEB-2007 00:00:00 Query 12
03-FEB-2007 00:00:00 401
04-FEB-2007 00:00:00 DDL 127
04-FEB-2007 00:00:00 DML 14
04-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 410
04-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 305
04-FEB-2007 00:00:00 PL/SQL 306
04-FEB-2007 00:00:00 Query 14
04-FEB-2007 00:00:00 1176
05-FEB-2007 00:00:00 DDL 115
05-FEB-2007 00:00:00 DML 81
05-FEB-2007 00:00:00 Explain Plan 1
05-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 261
05-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 16
05-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 161
05-FEB-2007 00:00:00 PL/SQL 315
05-FEB-2007 00:00:00 Query 360
05-FEB-2007 00:00:00 1310
06-FEB-2007 00:00:00 DDL 98
06-FEB-2007 00:00:00 DML 86
06-FEB-2007 00:00:00 Explain Plan 2
06-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 212
06-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 16
06-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 108
06-FEB-2007 00:00:00 PL/SQL 299
06-FEB-2007 00:00:00 Query 439
06-FEB-2007 00:00:00 1260
07-FEB-2007 00:00:00 DDL 98
07-FEB-2007 00:00:00 DML 162
07-FEB-2007 00:00:00 Explain Plan 1
07-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 210
07-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 24
07-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 96
07-FEB-2007 00:00:00 PL/SQL 337
07-FEB-2007 00:00:00 Query 348
07-FEB-2007 00:00:00 1276
08-FEB-2007 00:00:00 DDL 112
08-FEB-2007 00:00:00 DML 420
08-FEB-2007 00:00:00 Explain Plan 1
08-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 311
08-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 25
08-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 119
08-FEB-2007 00:00:00 PL/SQL 572
08-FEB-2007 00:00:00 Query 493
08-FEB-2007 00:00:00 2053
DDL 682
DML 838
Explain Plan 5
No Statement ID; In object type: Not Specified 1787
No Statement ID; In object type: PACKAGE 90
No Statement ID; In object type: PROCEDURE 1004
PL/SQL 2296
Query 1847
8549
68 rows selected.
Elapsed: 00:09:49.44
It was just an idea…if anyone has anything to add/improve it feel free to comment.
It doesn’t include the MERGE statement that you identified but there’s an almost complete list here.
http://tinyurl.com/2jz8pw
I know you were probably asking for a table_name, so how about AUDIT_ACTIONS?
Good to see that “COUNT(1)” for improved performance 😉
If you don’t want to pay license fees for the AWR option you could simply use the *_tab_modifications views:
select table_name, inserts,updates,deletes from USER_TAB_MODIFICATIONS;
TABLE_NAME INSERTS UPDATES DELETES PROTECTION_FEE_BY_DAY
142171 0 0
CONTACT_DIM
271 24 0
You could use snapshots to find out the daily number of each type of dml operations or use the timestamp column to calculate the average.
D’ya know…I nearly put behind the words “Someone really smart”, a HTML hyperlink to you Doug!
😉
Aldo, I wanted to find more than just DML activity…but that’s a useful thing to know anyhow – thanks.
William, I can’t tell if that’s sarcasm or not…I should obviously have said that I was running on 10.2.0.2 right? 😉
a HTML hyperlink to you Doug!
Only if Jonathan Lewis, Tom Kyte, Kevin Closson …. happened to be in the bathroom at the time 😉
Not together, I might add!
I should really think some of these comments through a little more carefully 😉
…I didn’t read anything into your first response…I just figured you had more inside information now that you’re an Oakie!
😉
Oh, yeah, source code and everything!
😉
> William, I can’t tell if that’s sarcasm or not.
Apologies. There is a long tradition on Oracle forums of making fun of people who suggest that COUNT(1) is faster than COUNT(*). Someone will usually propose COUNT(Chocula), COUNT(your_blessings) etc. I always enjoy it anyway.
Sorry William – I get it now…that’s why I mentioned that I should have said I was running on 10.2.0.2 so that the issue of COUNT(1) v COUNT(*) v COUNT(Indexed column) was moot since the CBO deals with them all the same (most optimal) way in this release.
I remember in days gone by that it wasn’t necessarily the case but given I’m usually working on a DW then I’m usually on the latest version so I can leave the CBO to it 😉
Eddie has a few links to that topic here
> I remember in days gone by that it wasn’t necessarily the case
Actually it always was. Hence the long tradition 😉
> given I’m usually working on a DW then I’m usually on the latest version
Is that noormally the rule? At my last job (a major investment bank) the warehouse ran on 8i and they only migrated to 10g last November, around the time I left.
Last month I turned down a contract (billing system though, not DW) because it was 8i. I’m starting to regret it now…
That’s interesting…I responded to your last COUNT(1) v COUNT(*) comment but it’s not here now…that’s nice of blogger!
Anyway, what I had wanted to say was that on the Tom Kyte link on Eddie’s link I put up earlier on the thread, he talks about how in Oracle 7.x it did make a difference as to which method you used…that’s what I meant about days gone by (like, really gone by!).
Most people I’ve spoken to re DW seem to be on bleeding edge systems, myself included…but I wouldn’t be so bold as to call that a “rule” 😉 I don’t think I’d go for a contract on 8i…unless there were significant other benefits.
Do you mean this one, where (apparently) COUNT(1) was slower? I distinctly remember hearing the “count PK columns” tip back in Oracle 6 days and (perhaps unusually at that time) testing it.
Of course I fell for the “explicit cursor saves one fetch” tip later on, though to be fair it was a Forms 3 project and it may have had some truth in that specific environment.
Blimey, now we really are going back in time!
Yes, that was the Tom Kyte link I meant.
Ah, the good old days with Forms 3 and Oracle 5 / 6…before all this java/xml malarky!
So to summarise:
COUNT(1) has never been faster than COUNT(*), a lot of people seem to think it is, at one time it may have actually been slower, and this has provided a rich source of merriment on nerdy forums and indeed blogs.
I’m aware this a very old post, and more thank likely this has already been solved elsewhere…. I played with it a bit, as I was looking for a similar answer earlier this morning and ran into it.
— rpt_sqlops_from_ash.sql
— Aug 2015
— Using AWR to summarize sql operations
— Found on https://www.oramoss.com/blog/using-awr-to-summarise-sql-operations-activity/
— Modified it to use instance,session and serial# in the grouping, got rid of the CASE for the opcode
— Added a wrapper SELECT statement to determine percentages withing the window queried using ratio_to_report
SELECT sample_day, statement_type,executions
,ROUND(RATIO_TO_REPORT (executions) OVER() *100 ,2) Pct
FROM (
WITH ash AS
(
SELECT TRUNC(ash.sample_time) sample_day
, sql_opname statement_type
, ROW_NUMBER() OVER(PARTITION BY ash.instance_number,ash.session_id,ash.session_serial#,ash.sql_id,ash.sql_child_number,sql_exec_start ORDER BY ash.sample_time DESC) rn
FROM dba_hist_snapshot s
, dba_hist_active_sess_history ash
–, dba_objects o
WHERE s.snap_id = ash.snap_id(+)
AND s.dbid = ash.dbid(+)
AND s.instance_number = ash.instance_number(+)
–AND ash.plsql_entry_object_id = o.object_id(+)
AND TRUNC(ash.sample_time) BETWEEN TRUNC(SYSDATE-6) AND TRUNC(SYSDATE+1) — all within last 7 days
–AND ash.snap_id between 1583 and 1588
)
SELECT sample_day
, statement_type
, COUNT(1) Executions
FROM ash
WHERE rn = 1
and statement_type is not null
–GROUP BY ROLLUP(sample_day), ROLLUP(statement_type)
GROUP BY sample_day, statement_type
ORDER BY sample_day,statement_type)
ORDER BY 1,4;