By Franck Pachot

.
Want to display a specific section from the latest AWR report? I’ll share the script I use to get something like that:

SQL> @ LASTAWRSEC.sql 'Top 10 Foreground Events by Total Wait Time' ''

AWR_REPORT_TEXT
--------------------------------------------------------------------------------
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class
------------------------------ ----------- ---------- ---------- ------ --------
direct path write temp               7,146        8.1       1.14   67.3 User I/O
DB CPU                                            4.6              38.0
direct path read                     1,102         .3       0.32    2.9 User I/O
enq: KO - fast object checkpoi           1         .2     228.12    1.9 Applicat
db file sequential read                140          0       0.15     .2 User I/O
Disk file operations I/O                 4          0       0.66     .0 User I/O
log file sync                            2          0       0.68     .0 Commit
reliable message                         1          0       0.94     .0 Other
control file sequential read           122          0       0.01     .0 System I
SQL*Net break/reset to client            2          0       0.25     .0 Applicat

The script

The LASTAWRSEC.sql used here script get the latest snapshot from DBA_HIST_SNAPSHOT for the current instance and calls DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT to get the report. Then it adds a column with the section name (I’ve listed the sections available in 12.1.0.2 – you may have to modify that list) and the script accepts two parameters:

  • &1 is a regexp to apply to the section name
  • &2 is a regexp applied to the report line (can be the empty string)

and they are combined with a ‘or’. I use that to get the Top event section and a few instance statistics in addition to that.

So here is the script:

with snap as (
  select * from (
    select dbid,lead(snap_id)over(partition by instance_number order by end_interval_time desc) bid,snap_id eid,row_number() over(order by end_interval_time desc) n
    from dba_hist_snapshot where dbid=(select dbid from v$database)
  ) where n=1
),
awr as (
        select rownum line,output
        from table(
                dbms_workload_repository.awr_report_text(l_dbid=>(select dbid from snap),l_inst_num=>(select instance_number from v$instance),l_bid=>(select bid from snap),l_eid=>(select eid from snap),l_options=>1+4+8)
        )
),
awr_sections as (
        select
         last_value(case when regexp_replace(output,' *DB/Inst.*$') in (''
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top Event P1/P2/P3 Values'
        ,'Top SQL with Top Events'
        ,'Top SQL with Top Row Sources'
        ,'Top Sessions'
        ,'Top Blocking Sessions'
        ,'Top PL/SQL Procedures'
        ,'Top Events'
        ,'Top DB Objects'
        ,'Activity Over Time'
        ,'Wait Event Histogram Detail (64 msec to 2 sec)'
        ,'Wait Event Histogram Detail (4 sec to 2 min)'
        ,'Wait Event Histogram Detail (4 min to 1 hr)'
        ,'SQL ordered by Elapsed Time'
        ,'SQL ordered by CPU Time'
        ,'SQL ordered by User I/O Wait Time'
        ,'SQL ordered by Gets'
        ,'SQL ordered by Reads'
        ,'SQL ordered by Physical Reads (UnOptimized)'
        ,'SQL ordered by Optimized Reads'
        ,'SQL ordered by Executions'
        ,'SQL ordered by Parse Calls'
        ,'SQL ordered by Sharable Memory'
        ,'SQL ordered by Version Count'
        ,'SQL ordered by Cluster Wait Time'
        ,'Key Instance Activity Stats'
        ,'Instance Activity Stats'
        ,'IOStat by Function summary'
        ,'IOStat by Filetype summary'
        ,'IOStat by Function/Filetype summary'
        ,'Tablespace IO Stats'
        ,'File IO Stats'
        ,'Checkpoint Activity'
        ,'MTTR Advisory'
        ,'Segments by Logical Reads'
        ,'Segments by Physical Reads'
        ,'Segments by Direct Physical Reads'
        ,'Segments by Physical Read Requests'
        ,'Segments by UnOptimized Reads'
        ,'Segments by Optimized Reads'
        ,'Segments by Physical Write Requests'
        ,'Segments by Physical Writes'
        ,'Segments by Direct Physical Writes'
        ,'Segments by DB Blocks Changes'
       ,'Segments by Table Scans'
        ,'Segments by Row Lock Waits'
        ,'Segments by ITL Waits'
        ,'Segments by Buffer Busy Waits'
        ,'Segments by Global Cache Buffer Busy'
        ,'Segments by CR Blocks Received'
        ,'Segments by Current Blocks Received'
        ,'In-Memory Segments by Scans'
        ,'In-Memory Segments by DB Block Changes'
        ,'In-Memory Segments by Populate CUs'
        ,'In-Memory Segments by Repopulate CUs'
        ,'Interconnect Device Statistics'
        ,'Dynamic Remastering Stats'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Reader'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Buffered Queue Subscribers'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'Persistent Queue Subscribers'
        ,'Rule Set'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Cluster Interconnect'
        ,'Wait Classes by Total Wait Time'
        ,'Top 10 Foreground Events by Total Wait Time'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'Host Configuration Comparison'
        ,'Top Timed Events'
        ,'Top SQL Comparison by Elapsed Time'
        ,'Top SQL Comparison by I/O Time'
        ,'Top SQL Comparison by CPU Time'
        ,'Top SQL Comparison by Buffer Gets'
        ,'Top SQL Comparison by Physical Reads'
        ,'Top SQL Comparison by UnOptimized Read Requests'
        ,'Top SQL Comparison by Optimized Reads'
        ,'Top SQL Comparison by Executions'
        ,'Top SQL Comparison by Parse Calls'
        ,'Top SQL Comparison by Cluster Wait Time'
        ,'Top SQL Comparison by Sharable Memory'
        ,'Top SQL Comparison by Version Count'
        ,'Top Segments Comparison by Logical Reads'
        ,'Top Segments Comparison by Physical Reads'
        ,'Top Segments Comparison by Direct Physical Reads'
        ,'Top Segments Comparison by Physical Read Requests'
        ,'Top Segments Comparison by Optimized Read Requests'
        ,'Top Segments Comparison by Physical Write Requests'
        ,'Top Segments Comparison by Physical Writes'
        ,'Top Segments Comparison by Table Scans'
        ,'Top Segments Comparison by DB Block Changes'
        ,'Top Segments by Buffer Busy Waits'
        ,'Top Segments by Row Lock Waits'
        ,'Top Segments by ITL Waits'
        ,'Top Segments by CR Blocks Received'
        ,'Top Segments by Current Blocks Received'
        ,'Top Segments by GC Buffer Busy Waits'
        ,'Top In-Memory Segments Comparison by Scans'
        ,'Top In-Memory Segments Comparison by DB Block Changes'
        ,'Top In-Memory Segments Comparison by Populate CUs'
        ,'Top In-Memory Segments Comparison by Repopulate CUs'
        ,'Service Statistics'
        ,'Service Statistics (RAC)'
        ,'Global Messaging Statistics'
        ,'Global CR Served Stats'
        ,'Global CURRENT Served Stats'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'Streams by CPU Time'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Reader'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams by IO Time'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Rule Set by Evaluations'
        ,'Rule Set by Elapsed Time'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'IOStat by Function - Data Rate per Second'
        ,'IOStat by Function - Requests per Second'
        ,'IOStat by File Type - Data Rate per Second'
        ,'IOStat by File Type - Requests per Second'
        ,'Tablespace IO Stats'
        ,'Top File Comparison by IO'
        ,'Top File Comparison by Read Time'
        ,'Top File Comparison by Buffer Waits'
        ,'Key Instance Activity Stats'
        ,'Other Instance Activity Stats'
        ,'Enqueue Activity'
        ,'Buffer Wait Statistics'
        ,'Dynamic Remastering Stats'
        ,'Library Cache Activity'
        ,'Library Cache Activity (RAC)'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Buffered Subscribers'
        ,'Persistent Queue Subscribers'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'OS Statistics By Instance'
        ,'Foreground Wait Classes -  % of Total DB time'
        ,'Foreground Wait Classes'
        ,'Foreground Wait Classes -  % of DB time '
        ,'Time Model'
        ,'Time Model - % of DB time'
        ,'System Statistics'
        ,'System Statistics - Per Second'
        ,'System Statistics - Per Transaction'
        ,'Global Cache Efficiency Percentages'
        ,'Global Cache and Enqueue Workload Characteristics'
        ,'Global Cache and Enqueue Messaging Statistics'
        ,'SysStat and Global Messaging  - RAC'
        ,'SysStat and  Global Messaging (per Sec)- RAC'
        ,'SysStat and Global Messaging (per Tx)- RAC'
        ,'CR Blocks Served Statistics'
        ,'Current Blocks Served Statistics'
        ,'Global Cache Transfer Stats'
        ,'Global Cache Transfer (Immediate)'
        ,'Cluster Interconnect'
        ,'Interconnect Client Statistics'
        ,'Interconnect Client Statistics (per Second)'
        ,'Interconnect Device Statistics'
        ,'Interconnect Device Statistics (per Second)'
        ,'Ping Statistics'
        ,'Top Timed Events'
        ,'Top Timed Foreground Events'
        ,'Top Timed Background Events'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'SQL ordered by Elapsed Time (Global)'
        ,'SQL ordered by CPU Time (Global)'
        ,'SQL ordered by User I/O Time (Global)'
        ,'SQL ordered by Gets (Global)'
        ,'SQL ordered by Reads (Global)'
        ,'SQL ordered by UnOptimized Read Requests (Global)'
        ,'SQL ordered by Optimized Reads (Global)'
        ,'SQL ordered by Cluster Wait Time (Global)'
        ,'SQL ordered by Executions (Global)'
        ,'IOStat by Function (per Second)'
        ,'IOStat by File Type (per Second)'
        ,'Segment Statistics (Global)'
        ,'Library Cache Activity'
        ,'System Statistics (Global)'
        ,'Global Messaging Statistics (Global)'
        ,'System Statistics (Absolute Values)'
        ,'PGA Aggregate Target Statistics'
        ,'Process Memory Summary'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Time Model Statistics'
        ,'Operating System Statistics'
        ,'Host Utilization Percentages'
        ,'Global Cache Load Profile'
        ,'Wait Classes'
        ,'Wait Events'
        ,'Cache Sizes'
        ,'PGA Aggr Target Stats'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Global Cache Transfer Stats'
        ,' Exadata Storage Server Model'
        ,' Exadata Storage Server Version'
        ,' Exadata Storage Information'
        ,' Exadata Griddisks'
        ,' Exadata Celldisks'
        ,' ASM Diskgroups'
        ,' Exadata Non-Online Disks'
        ,' Exadata Alerts Summary'
        ,' Exadata Alerts Detail'
        ,'Exadata Statistics'
) then output end ) ignore nulls over(order by line) section
        ,output
        from awr
)
select output AWR_REPORT_TEXT from awr_sections where regexp_like(section,'&1') or regexp_like(output,'&2')
/

Please is you have any improvement or modification, don’t hesitate to comment.

Post Views: 1,249
Thumbnail [60x60]
by
Oracle Team