Batch mode AWR report generation

Sometimes it is necessary to generate the awr report for the whole day or couple of interval (I generally do it for trend analysis). Doing it manually for every interval could be a tedious job. I have written a small script which you can run with starting and ending snap_id and it generates reports in below format:


instance_name_startsnapid_endsnapid_date_starttime_endtime.lst

Eg:
instancename_8837_8838_08_25_2008_15_00_16_00.lst
instancename_8838_8839_08_25_2008_16_00_17_00.lst
instancename_8839_8840_08_25_2008_17_00_18_00.lst
instancename_8840_8841_08_25_2008_18_00_19_00.lst
instancename_8841_8842_08_25_2008_19_00_20_00.lst
instancename_8842_8843_08_25_2008_20_00_21_00.lst
instancename_8843_8844_08_25_2008_21_00_22_00.lst
instancename_8844_8845_08_25_2008_22_00_23_00.lst
instancename_8845_8846_08_25_2008_23_00_00_00.lst


Script:

AWR_report_generation.sh

#!/bin/ksh

# AWR_report_generation.shV1.2 03-Aug-09
#
# Author - Kapil Goyal
# Batch mode AWR report generation
# This script needs minimum 2 arguments. First - Starting snap_id, Second - Ending Snap_id
# You need to change username/password. whomsoever has Grant execute on DBMS_WORKLOAD_REPOSITORY
# If you encounter any issue while running it contact me at kapil_goyal@yahoo.com
# It can also take third argument for incremental reports. Eg. if you take snapshots every 20 minutes but want a report
# for every hour then you can pass third argument - 3.

s_snap_id=$1
e_snap_id=$2

if [ "$3" = "" ]; then
step=1
else
step=$3
fi

while [ $s_snap_id -lt $e_snap_id ]
do
incr=`expr $s_snap_id + $step`

sqlplus /nolog << !
connect username/password

define num_days = 0;
define report_type='text';
define begin_snap='$s_snap_id'
define end_snap ='$incr'

column i_name new_value i_name
col dt new_value dt

select instance_name i_name from v\$instance;

select a.snap_id||'_'||b.snap_id||'_'||
to_char(a.end_interval_time,'MM_DD_YYYY_HH24_MI')||
'_'|| to_char(b.end_interval_time,'HH24_MI') dt
from dba_hist_snapshot A, dba_hist_snapshot B
where a.instance_number = (select instance_number from v\$instance) and
a.dbid = (select dbid from v\$database)
and a.snap_id=&begin_snap and b.snap_id=$incr;

define report_name = &i_name\_&dt

@?/rdbms/admin/awrrpt
!

s_snap_id=`expr $incr`;

done

Leveraging Result Cache to enhance the SQL performance

Everyone uses Cache for performance as accessing data from disk is always slower compared to memory (currently). Storages like EMC have cache, so that read/write can happen in cache instead of disk. Oracle Database has buffer cache to avoid physical reads.

The SQL query result cache enables explicit caching of query result sets and query fragments in database memory. A dedicated memory buffer stored in shared pool can be used for storing and retrieving the cached result. Good candidate statements for result cache are the statements which access many rows and returns less rows though it can be used for any statement.

Create a TEST User and Table:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user TEST identified by test default tablespace USERS temporary tablespace TEMP;

User created.

SQL> grant connect,resource to TEST;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table test_cache ( c1 varchar2(4000));

Table created.

SQL> insert into test_cache values('This is a test table to demonstrate Result Cache Feature of Oracle 11g');

1 row created.

Insert approx 2 millions rows:

SQL> insert into test_cache select * from test_cache;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> /

262144 rows created.

SQL> /

524288 rows created.

SQL> /

1048576 rows created.

SQL> insert into test_cache values('ONE');

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> grant dba to TEST;

Grant succeeded.

SQL>

Check whether anything is cached or not:

SQL> conn test/test
Connected.
SQL> select TYPE,STATUS,NAME,CREATION_TIMESTAMP,ROW_COUNT,ROW_SIZE_MAX,OBJECT_NO,ROW_SIZE_AVG,INVALIDATIONS from v$result_cache_objects;

no rows selected


Run a Complex Self-Join query with AUTOTRACE ON

SQL> alter system flush buffer_cache;

System altered.

SQL> set autotrace on
SQL> SELECT count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t6
Where t1.c1='ONE' and
T2.c1='ONE' and
T3.c1='ONE' and
T4.c1='ONE' and
T5.c1='ONE' and
T6.c1='ONE';

COUNT(*)
----------
1

Elapsed: 00:00:04.77

Execution Plan
----------------------------------------------------------
Plan hash value: 156414347

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12012 | 6513T (2)|999:59:59 |
| 1 | SORT AGGREGATE | | 1 | 12012 | | |
| 2 | MERGE JOIN CARTESIAN | | 260T| 2780P| 6513T (2)|999:59:59 |
| 3 | MERGE JOIN CARTESIAN | | 1031G| 9389T| 25T (2)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 4080M| 29T| 101G (2)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 16M| 90G| 403M (2)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN| | 63879 | 243M| 1597K (2)| 05:19:33 |
|* 7 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6292 (2)| 00:01:16 |
| 8 | BUFFER SORT | | 253 | 494K| 1591K (2)| 05:18:18 |
|* 9 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 10 | BUFFER SORT | | 253 | 494K| 403M (2)|999:59:59 |
|* 11 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 12 | BUFFER SORT | | 253 | 494K| 101G (2)|999:59:59 |
|* 13 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 14 | BUFFER SORT | | 253 | 494K| 25T (2)|999:59:59 |
|* 15 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 16 | BUFFER SORT | | 253 | 494K| 6513T (2)|999:59:59 |
|* 17 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("T6"."C1"='ONE')
9 - filter("T5"."C1"='ONE')
11 - filter("T4"."C1"='ONE')
13 - filter("T3"."C1"='ONE')
15 - filter("T2"."C1"='ONE')
17 - filter("T1"."C1"='ONE')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
133314 consistent gets
133254 physical reads
0 redo size
522 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

Ran the above sql statement couple of times, even after database blocks are cached, it was consistently taking 4.77 seconds and ~133k consistent gets and physical reads.


Execute the query with the result_cache hint

Alter system flush buffer_cache;

First time:

SQL> SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t6
Where t1.c1='ONE' and
T2.c1='ONE' and
T3.c1='ONE' and
T4.c1='ONE' and
T5.c1='ONE' and
T6.c1='ONE'
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15

COUNT(*)
----------
1

Elapsed: 00:00:04.64

Execution Plan
----------------------------------------------------------
Plan hash value: 156414347

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12012 | 6513T (2)|999:59:59 |
| 1 | RESULT CACHE | ag7yp31pfqyv00m3vhbfwxjjz1 | | | | |
| 2 | SORT AGGREGATE | | 1 | 12012 | | |
| 3 | MERGE JOIN CARTESIAN | | 260T| 2780P| 6513T (2)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 1031G| 9389T| 25T (2)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 4080M| 29T| 101G (2)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN | | 16M| 90G| 403M (2)|999:59:59 |
| 7 | MERGE JOIN CARTESIAN| | 63879 | 243M| 1597K (2)| 05:19:33 |
|* 8 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6292 (2)| 00:01:16 |
| 9 | BUFFER SORT | | 253 | 494K| 1591K (2)| 05:18:18 |
|* 10 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 11 | BUFFER SORT | | 253 | 494K| 403M (2)|999:59:59 |
|* 12 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 13 | BUFFER SORT | | 253 | 494K| 101G (2)|999:59:59 |
|* 14 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 15 | BUFFER SORT | | 253 | 494K| 25T (2)|999:59:59 |
|* 16 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 17 | BUFFER SORT | | 253 | 494K| 6513T (2)|999:59:59 |
|* 18 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("T6"."C1"='ONE')
10 - filter("T5"."C1"='ONE')
12 - filter("T4"."C1"='ONE')
14 - filter("T3"."C1"='ONE')
16 - filter("T2"."C1"='ONE')
18 - filter("T1"."C1"='ONE')

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(TEST.TEST_CACHE); attributes=(single-row); parameters=(nls); name="SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t"


Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
133314 consistent gets
133261 physical reads
0 redo size
522 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SQL>

First time, it took the same time as previous query took.

Second time

SQL> /

COUNT(*)
----------
1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 156414347

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12012 | 6513T (2)|999:59:59 |
| 1 | RESULT CACHE | ag7yp31pfqyv00m3vhbfwxjjz1 | | | | |
| 2 | SORT AGGREGATE | | 1 | 12012 | | |
| 3 | MERGE JOIN CARTESIAN | | 260T| 2780P| 6513T (2)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 1031G| 9389T| 25T (2)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 4080M| 29T| 101G (2)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN | | 16M| 90G| 403M (2)|999:59:59 |
| 7 | MERGE JOIN CARTESIAN| | 63879 | 243M| 1597K (2)| 05:19:33 |
|* 8 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6292 (2)| 00:01:16 |
| 9 | BUFFER SORT | | 253 | 494K| 1591K (2)| 05:18:18 |
|* 10 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 11 | BUFFER SORT | | 253 | 494K| 403M (2)|999:59:59 |
|* 12 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 13 | BUFFER SORT | | 253 | 494K| 101G (2)|999:59:59 |
|* 14 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 15 | BUFFER SORT | | 253 | 494K| 25T (2)|999:59:59 |
|* 16 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 17 | BUFFER SORT | | 253 | 494K| 6513T (2)|999:59:59 |
|* 18 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("T6"."C1"='ONE')
10 - filter("T5"."C1"='ONE')
12 - filter("T4"."C1"='ONE')
14 - filter("T3"."C1"='ONE')
16 - filter("T2"."C1"='ONE')
18 - filter("T1"."C1"='ONE')

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(TEST.TEST_CACHE); attributes=(single-row); parameters=(nls); name="SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t"


Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Note – Second time onwards it only took .01 second and 0 consistent gets and physical reads.

Check v$result_cache_objects:

SQL> set autot off
SQL> l
1* select TYPE,STATUS,NAME,CREATION_TIMESTAMP,ROW_COUNT,ROW_SIZE_MAX,OBJECT_NO,ROW_SIZE_AVG,INVALIDATIONS from v$result_cache_objects
SQL> /

TYPE STATUS NAME
---------- --------- --------------------------------------------------------------------------------------------------------------------------------
CREATION_ ROW_COUNT ROW_SIZE_MAX OBJECT_NO ROW_SIZE_AVG INVALIDATIONS
--------- ---------- ------------ ---------- ------------ -------------
Dependency Published TEST.TEST_CACHE
26-AUG-08 0 0 70304 0 0

Result Published SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t
26-AUG-08 1 5 0 5 0


As it is clear the result of the query is cached and published which means Result is available to use.

Check how much memory is being used by Result Cache

SQL> set serveroutput on
SQL> exec dbms_result_cache.memory_report(detailed=>TRUE)
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 7872K bytes (7872 blocks)
Maximum Result Size = 393K bytes (393 blocks)
[Memory]
Total Memory = 147280 bytes [0.029% of the Shared Pool]
... Fixed Memory = 2400 bytes [0.000% of the Shared Pool]
....... Cache Mgr = 152 bytes
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = bytes
... Dynamic Memory = 144880 bytes [0.029% of the Shared Pool]
....... Overhead = 112112 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 9712 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

Flush the Result Cache

Following supplied package can be used to flush the result cache:

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> execute dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 7872K bytes (7872 blocks)
Maximum Result Size = 393K bytes (393 blocks)
[Memory]
Total Memory = 2400 bytes [0.000% of the Shared Pool]
... Fixed Memory = 2400 bytes [0.000% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

How to dump Database block and how to read it

I did this small test on my 11g instance. I created a small table EMP with 3 rows and then I dumped the block.

Create table, Insert 3 rows and commit:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create table emp(empno number(5), ename varchar2(30)) tablespace users;

Table created.

SQL> insert into emp values(7369,'SCOTT');

1 row created.

SQL> insert into emp values(7812,'SMITH');

1 row created.

SQL> insert into emp values(9241,'TOM');

1 row created.

SQL> commit;

Commit complete.

Get the block-number for rows:

select rowid,empno,ename,
dbms_rowid.rowid_relative_fno(rowid) fileno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno, rownum
from emp;


ROWID EMPNO ENAME FILENO BLOCKNO ROWNO ROWNUM
------------------ ---------- ------------------------------ ---------- ---------- ---------- ----------
AAARJGAAEAAAABEAAA 7369 SCOTT 4 68 0 1
AAARJGAAEAAAABEAAB 7812 SMITH 4 68 1 2
AAARJGAAEAAAABEAAC 9241 TOM 4 68 2 3


Dump the block:

SQL> alter system dump datafile 4 block 68;

System altered.

Trace file can be found in User Dump Dest.

SQL> show parameter user

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
redo_transport_user string
user_dump_dest string /dmecsoid/oracle/diag/rdbms/de
v11g/dev11g/trace
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@dmecsoid->cd /dmecsoid/oracle/diag/rdbms/dev11g/dev11g/trace
oracle@dmecsoid->

oracle@dmecsoid->ls -lrt dev11g_ora_10038.trc
-rw-r----- 1 oracle dba 37429 Aug 25 23:26 dev11g_ora_10038.trc
oracle@dmecsoid->

How to read Block Data:

block_row_dump:
tab 0, row 0, @0x1f8b
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 4a 46 --- 4a (hex to decimal)=74-1=73, 46(hex to decimal)=70-1=69-> 73 69(empno)
col 1: [ 5] 53 43 4f 54 54--- 53=S, 43=C, 4f=O, 54=T, 54=T -> S C O T T (ename)
tab 0, row 1, @0x1f7e
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 4f 0d --- 4f (hex to decimal)=79-1=78, 0d(hex to decimal)=13-1=12 -> 78 12(empno)
col 1: [ 5] 53 4d 49 54 48--- 53=S, 4d=M, 49=I, 54=T, 48=H -> S M I T H (ename)
tab 0, row 2, @0x1f73
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 5d 2a --- 5d (hex to decimal)=93-1=92, 2a(hex to decimal)=42-1=41 -> 92 41 (empno)
col 1: [ 3] 54 4f 4d --- 54=T, 4f=O, 4d=M -> T O M (ename)
end_of_block_dump