Mohamed Houri’s Oracle Notes

December 9, 2019

AWR bulk collect

Filed under: Oracle — hourim @ 5:08 pm

There are many posts out there explaining how to collect a bunch of AWR reports which I could have just referenced and leave it there, but as much for my own documentation as anything else, I decided to write this simple note as an answer to my client request of generating and analyzing 49 one-hour AWR reports.

First, the SQL script to generate a bulk of AWR reports between two dates:

-- +----------------------------------------------------------------------------+
-- | Author : Mohamed Houri                                                     |
-- |----------------------------------------------------------------------------|
-- | DATABASE : 12cR1                                                           |
-- | Name     : AwrBulkcollect.sql                                              |
-- | PURPOSE  : Dynamically create a SQL script to generate a list of AWR       |
-- |            reports between two snapshots dates.                            |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- | Remarks : CHR(10) adds a new line                                          |
-- |           SET termout off so that AWR content will not be displayed        |
-- |           For RAC configuration consider the instance number               |       
-- |          Enter input date in this format :'mmddyyyy hh24:mi:ss'            |
-- |                                                                            |
-- ------------------------------------------------------------------------------
set termout off
set head off
set define off
spool collectAWRs.sql

SELECT
    'spool awr_XYZ_inst_1_'
    || t.si
    || '_'
    || t.se
    || '.text '
    || CHR(10)
    || 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_text('
    || t.dbid
    || ','
    || t.instance_number
    || ','
    || t.si
    || ','
    || t.se
    || '));'
    || CHR(10)
    || ' spool off;'
FROM
    (SELECT
            dbid,
            snap_id si,
            snap_id + 1 se,
            instance_number
     FROM
            dba_hist_snapshot
     WHERE
            begin_interval_time >   TO_DATE('27102019 18:00:00', 'ddmmyyyy hh24:mi:ss')
      AND end_interval_time   <=  TO_DATE('27102019 19:30:00', 'ddmmyyyy hh24:mi:ss')
      AND  instance_number = 1
      ) t;
	
spool off;
-- +-------------------------------------+	
  prompt running collectAWRs.sql script |
-- +-------------------------------------+
@collectAWRs.sql

Depending on the number of snapshots found between the two entered dates the output of the script might look like the following:

spool awr_XYZ_inst_1_11923_11924.text                                                                                   
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(4209020461,1,11923,11924));                                
spool off;                                                                                                             
                                                                                                                        
spool awr_XYZ_inst_1_11922_11923.text                                                                                   
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(4209020461,1,11922,11923));                                
spool off;     

etc.…

When the script ends you can see such a list of AWR files

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/28/2019   2:45 PM         451751 awr_XYZ_inst_1_11946_11947.text
-a----       10/28/2019   2:46 PM         464089 awr_XYZ_inst_1_11952_11953.text
-a----       10/28/2019   2:47 PM         446377 awr_XYZ_inst_1_11945_11946.text
-a----       10/28/2019   2:48 PM         494261 awr_XYZ_inst_1_11949_11950.text
-a----       10/28/2019   2:49 PM         447109 awr_XYZ_inst_1_11954_11955.text
-a----       10/28/2019   2:50 PM         440269 awr_XYZ_inst_1_11956_11957.text
-a----       10/28/2019   2:51 PM         448697 awr_XYZ_inst_1_11955_11956.text
-a----       10/28/2019   2:52 PM         441979 awr_XYZ_inst_1_11942_11943.text
-a----       10/28/2019   2:53 PM         444789 awr_XYZ_inst_1_11943_11944.text
-a----       10/28/2019   2:54 PM         424755 awr_XYZ_inst_1_11947_11948.text
-a----       10/28/2019   2:55 PM         441857 awr_XYZ_inst_1_11951_11952.text
-a----       10/28/2019   2:55 PM         445155 awr_XYZ_inst_1_11944_11945.text
-a----       10/28/2019   2:56 PM         468731 awr_XYZ_inst_1_11948_11949.text
-a----       10/28/2019   2:57 PM         449185 awr_XYZ_inst_1_11950_11951.text
-a----       10/28/2019   2:58 PM         428541 awr_XYZ_inst_1_11953_11954.text
-a----       10/28/2019   2:59 PM         442711 awr_XYZ_inst_1_11957_11958.text

Top 10 Foreground Events

This list of AWR reports has been requested by the Oracle support so that tuning suggestions can be given to my client. One of the suggestions given by Oracle was that reducing the number of log file sync wait event will help improving the performance of the application. I was curious to know what in the AWR reports list makes Oracle support suggesting this log file sync issue. So, I decided to examine the 45 Top 10 Foreground Events to see how much the log file sync wait event participate to the % of the total DB time.

For that, I used the following simple PowerShell script to generate a single file containing only the Top 10 Foreground Events of the totality of the 45 AWR reports:

$SearchString = 'Top 10 Foreground Events by Total Wait Time'
$AllAwrs=Get-ChildItem -Recurse -Include *.text	
for ($i=0; $i -lt $AllAwrs.Count; $i++)
 {
	$result = Select-String $SearchString $AllAwrs[$i].FullName -Context 0, 14 | % {$_.Context.PostContext} 
    $result |  Out-File Top10ForegroundWaitevents.txt   -Append	
 }     	


And, finally, all what I have to do was to analyze the Top10ForegroundWaitevents.txt file

 Event                                 Waits Time Avg(ms)   time Wait Class 
------------------------------ ------------ ---- ------- ------ ---------- 
DB CPU                                      1820           55.2            
db file scattered read               44,406 1032      23   31.3 User I/O   
db file sequential read           2,346,969 735.       0   22.3 User I/O   
direct path write temp                4,085 50.1      12    1.5 User I/O   
log file sync                         3,688 18.3       5     .6 Commit     --> only 0.6%
direct path read temp                13,173 14.2       1     .4 User I/O   
enq: TX - row lock contention           133    1       7     .0 Applicatio 
control file sequential read         11,846   .6       0     .0 System I/O 
db file parallel read                    13   .6      43     .0 User I/O   
direct path read                      2,556   .5       0     .0 User I/O   
                                                                           
                                            Tota    Wait   % DB            
Event                                 Waits Time Avg(ms)   time Wait Class 
------------------------------ ------------ ---- ------- ------ ---------- 
db file scattered read              212,869 3439      16   94.1 User I/O   
DB CPU                                      422.           11.5            
control file sequential read         11,846   .5       0     .0 System I/O 
OJVM: Generic                           139   .5       3     .0 Other      
db file sequential read               5,270   .3       0     .0 User I/O   
log file sync                            67   .3       4     .0 Commit     --> almost 0.0% 
Disk file operations I/O                373    0       0     .0 User I/O   
direct path read                        108    0       0     .0 User I/O   
SQL*Net message to client             1,706    0       0     .0 Network    
direct path sync                          1    0       2     .0 User I/O   
                                                                           
                                            Tota    Wait   % DB            
Event                                 Waits Time Avg(ms)   time Wait Class 
------------------------------ ------------ ---- ------- ------ ---------- 
DB CPU                                      3918           91.8            
db file scattered read               11,553  235      20    5.5 User I/O   
db file sequential read              28,353 87.4       3    2.0 User I/O   
log file sync                         9,537   78       8    1.8 Commit     --> only 1.8% 
resmgr:become active                      4   10    2512     .2 Scheduler  
enq: TX - row lock contention           344  4.3      13     .1 Applicatio 
direct path read temp                 1,241    1       1     .0 User I/O   
direct path read                      6,324    1       0     .0 User I/O   
direct path write temp                  130   .8       6     .0 User I/O   
control file sequential read         11,871   .7       0     .0 System I/O

Etc.….

Bottom-line

In this very simple note I gave a SQL script to generate multiple AWR reports between two dates. I have also examined, via a PowerShell script, how to loop over the content of those AWR reports and extract only an AWR section into a single file for a better and clean analysis.

5 Comments »

  1. Hi Mohamed Houri,

    Thanks for the wonderful script.

    In order to generate AWR global report using ALL option, what modification should be done for this script?

    Thanks,
    Rakesh

    Comment by Rakesh RA — December 15, 2019 @ 12:36 pm | Reply

  2. Hi Mohamed,

    I think I was able to generate the above requirement by slightly modifying your script… Below is what i did…

    set termout off
    set head off
    set define off
    spool collectAWRs.sql

    SELECT ‘spool awr_XYZ_RAC_’ || t.si || ‘_’ || t.se || ‘.txt ‘ || CHR(10) ||’set termout off ‘|| chr(10)|| ‘SELECT * FROM TABLE(dbms_workload_repository.awr_global_report_text(‘ || t.dbid || ‘,’ || chr(39) || chr(39)|| ‘,’ || t.si || ‘,’ || t.se || ‘));’ || CHR(10) || ‘ spool off;’
    FROM (
    SELECT dbid
    ,snap_id si
    ,snap_id + 1 se
    ,’ ‘
    FROM dba_hist_snapshot
    WHERE begin_interval_time > TO_DATE(‘22112019 00:00:00’, ‘ddmmyyyy hh24:mi:ss’)
    AND end_interval_time <= TO_DATE('09122019 19:30:00', 'ddmmyyyy hh24:mi:ss')
    ) t;

    spool off;

    Comment by Rakesh RA — December 15, 2019 @ 1:19 pm | Reply

    • Thanks Rakash for your comment and sorry i have missed it when you have posted it.

      Comment by hourim — August 26, 2020 @ 6:36 pm | Reply

  3. Hi ,

    Could you please share dba script which are you using.

    Comment by Mehul — August 26, 2020 @ 1:06 pm | Reply

    • I will put these scripts in github very soon.

      Comment by hourim — August 26, 2020 @ 6:35 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: