Tuesday, February 26, 2008
Tracking TEMP usage throughout the day
I really wanted to do this via AWR, but I've not been able to find out if this kind of information is stored and, if it is, how I'd get access to it...maybe someone else knows...hint hint?!
I have various queries I run whilst I'm online and processes are running, but what I really wanted, was to know the usage profile throughout the day...so, given that I couldn't find an AWR way of tracking our use of TEMP on a database, I figured I'd use a more klunky method...
I can now run queries against the TEMP_USE_HISTORY table to show how much TEMP has been used, when, by whom and for what use, e.g.
From the above, I can see that, during the last twenty four hours on the database in question, there was reasonably heavy use of the TEMP area between 4pm and 6pm yesterday, and that the load peaked at approximately 267Gb.
I have various queries I run whilst I'm online and processes are running, but what I really wanted, was to know the usage profile throughout the day...so, given that I couldn't find an AWR way of tracking our use of TEMP on a database, I figured I'd use a more klunky method...
DROP TABLE mgmt_t_temp_use_history PURGE
/
CREATE TABLE temp_use_history(snap_date DATE NOT NULL
,sid NUMBER NOT NULL
,segtype VARCHAR2(9) NOT NULL
,qcsid NUMBER NULL
,username VARCHAR2(30) NULL
,osuser VARCHAR2(30) NULL
,contents VARCHAR2(9) NULL
,sqlhash NUMBER NULL
,sql_id VARCHAR2(13) NULL
,blocks NUMBER NULL
)
PCTFREE 0
COMPRESS
NOLOGGING
/
CREATE UNIQUE INDEX tuh_pk ON
temp_use_history(snap_date,sid,segtype)
PCTFREE 0
COMPRESS
NOLOGGING
/
ALTER TABLE temp_use_history ADD CONSTRAINT tuh_pk PRIMARY
KEY(snap_date,sid,segtype)
USING INDEX
/
DECLARE
l_program_action VARCHAR2(2000);
l_27477 EXCEPTION;
PRAGMA EXCEPTION_INIT(l_27477,-27477); BEGIN
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'MINUTELY_5M'
,start_date => SYSDATE
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'
,comments =>'Daily schedule to run a job every five minutes.'
);
EXCEPTION
WHEN l_27477 THEN
NULL; -- Ignore if the schedule exists
END;
l_program_action := 'DECLARE';
l_program_action := l_program_action||' l_date DATE := SYSDATE; ';
l_program_action := l_program_action||'BEGIN';
l_program_action := l_program_action||' INSERT /*+ APPEND */ INTO temp_use_history(snap_date,sid,qcsid,username,osuser,contents,seg
type,sqlhash,sql_id,blocks)';
l_program_action := l_program_action||' SELECT l_date,s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqlh
ash,su.sql_id,sum(su.blocks)';
l_program_action := l_program_action||' FROM v$sort_usage su';
l_program_action := l_program_action||' , v$session s';
l_program_action := l_program_action||' , v$px_session ps';
l_program_action := l_program_action||' WHERE s.sid=ps.sid(+)';
l_program_action := l_program_action||' AND s.saddr =
su.session_addr';
l_program_action := l_program_action||' AND s.serial# =
su.session_num';
l_program_action := l_program_action||' GROUP BY s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqladdr,su.
sqlhash,su.sql_id;';
l_program_action := l_program_action||' COMMIT; ';
l_program_action := l_program_action||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'SNAP_TEMP_USAGE'
,program_type => 'PLSQL_BLOCK'
,program_action => l_program_action
,enabled => TRUE
,comments => 'Program to snap the temp usage into TEMP_USE_HISTORY'
);
EXCEPTION
WHEN l_27477 THEN
NULL; -- Ignore if the program exists
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_SNAP_TEMP_USAGE'
,program_name => 'SNAP_TEMP_USAGE'
,schedule_name => 'MINUTELY_5M'
,enabled => TRUE
,auto_drop => FALSE
,comments => 'Job to snap the temp usage into TEMP_USE_HISTORY'
);
EXCEPTION
WHEN l_27477 THEN
NULL; -- Ignore if the job exists
END;
END;
/
I can now run queries against the TEMP_USE_HISTORY table to show how much TEMP has been used, when, by whom and for what use, e.g.
SQL> ed
Wrote file afiedt.buf
1 select snap_date,round(sum(blocks)*32/1024/1024) gb
2 from temp_use_history
3 where snap_date > sysdate-1
4 group by snap_date
5 having round(sum(blocks)*32/1024/1024) > 50
6* order by 1
SQL> /
SNAP_DATE GB
-------------------- ----------
26-FEB-2008 16:12:25 57
26-FEB-2008 16:17:25 65
26-FEB-2008 16:22:25 74
26-FEB-2008 16:27:25 86
26-FEB-2008 16:32:25 95
26-FEB-2008 16:37:25 107
26-FEB-2008 16:42:25 121
26-FEB-2008 16:47:25 127
26-FEB-2008 16:52:25 147
26-FEB-2008 16:57:25 160
26-FEB-2008 17:02:25 162
26-FEB-2008 17:07:25 179
26-FEB-2008 17:12:25 196
26-FEB-2008 17:17:25 208
26-FEB-2008 17:22:25 217
26-FEB-2008 17:27:25 233
26-FEB-2008 17:32:25 241
26-FEB-2008 17:37:25 251
26-FEB-2008 17:42:25 257
26-FEB-2008 17:47:25 262
26-FEB-2008 17:52:25 264
26-FEB-2008 17:57:25 267
26-FEB-2008 18:02:25 201
27-FEB-2008 00:27:25 59
27-FEB-2008 00:32:25 60
27-FEB-2008 00:37:25 69
27-FEB-2008 01:12:25 57
27-FEB-2008 02:22:25 53
27-FEB-2008 09:57:25 51
29 rows selected.
From the above, I can see that, during the last twenty four hours on the database in question, there was reasonably heavy use of the TEMP area between 4pm and 6pm yesterday, and that the load peaked at approximately 267Gb.
Thursday, February 08, 2007
Using AWR to summarise SQL operations activity
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:
...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:
It was just an idea...if anyone has anything to add/improve it feel free to comment.
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
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.
Subscribe to Posts [Atom]