RAC aware SLOB2 analyze script (Flash DBA)

I’ve been using the very useful scripts from FlashDBA to run SLOB2 on our new system, but unfortunately the analyze one is not RAC aware, so I’ve modified it, in very minor ways, such that it can use an AWR Global report (awrgrpt.sql) as input and still extract the same values that the original does.

I call the script slob2-rac-analyze.sh

Here is an example run – ignore the numbers as they are not representative of anything in particular.


a555.net(jeff.a1):/app/support/SLOB: ./slob2-rac-analyze.sh rac_awr_12jul2013/awr.20.032/awr.20.032.txt > slob.csv
Info : Analyzing file rac_awr_12jul2013/awr.20.032/awr.20.032.txt
Info : Filename = awr.20.032.txt
Info : Update Pct = 20
Info : Workers = 032
Info : Read IOPS = 85.8
Info : Write IOPS = 33.0
Info : Redo IOPS = 15.6
Info : Total IOPS = 134.4
Info : Read Num Waits = 712
Info : Read Wait Time = 0.58
Info : Read Latency us = 814.606
Info : Write Num Waits = 926
Info : Write Wait Time = 0.28
Info : Write Latency us = 302.375
Info : Redo Num Waits = 2043
Info : Redo Wait Time = 0.37
Info : Redo Latency us = 181.106
Info : Num CPUs = 384
Info : Num CPU Cores = 192
Info : Num CPU Sockets = 24
Info : Linux Version = Red Hat Enterprise Linux Server release 6.3 (Santiago)
Info : Kernel Version = 2.6.32-279.2.1.el6.x86_64
Info : Processor Type = Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz
Info : SLOB Run Time = 300
Info : SLOB Work Loop = 0
Info : SLOB Scale = 10000
Info : SLOB Work Unit = 256
Info : SLOB Redo Stress = LIGHT
Info : SLOB Shared Data Mod = 0
Info : No more files found
Info : =============================
Info : AWR Files Found = 1
Info : AWR Files Processed = 1
Info : Errors Experienced = 0
Info : =============================

Jonathan Lewis has a nice article covering the different AWR Reports.

I’ve only tested it on the system at work and it seems to work OK – your mileage may vary and I’d be happy to hear comments to the contrary, in relation to the changes I’ve made for use on RAC, but obviously the script is still 99% unchanged, so please contact FlashDBA if there are any generic issues you want to raise.

I’m not a unix shell script guy, but it seems to work…see what you think.

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…

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.

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:

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.