Tag: awr report

Oracle Exadata Statistics in AWR report – Part 1 (Basic Info)

~ rajeevramdas@aol.com ~ Leave a comment

Starting with Exadata storage server 12.1.2.1.0 , used in combination with Oracle Database release 12.1.0.2, there are new sections which have been added to the Oracle AWR (Automatic Worload repository) report, that displays statistics at the Exadata storage level.

This is a really valuable enhancement, which helps with drilling down from database level statistics to cell level statistics, to identify and analyze the workload profile.
You can click on the URL’s in the section “Exadata Configuration and Statistics” to access this part of the report.

There are a few AWR history tables that store this information.

DBA_HIST_CELL_CONFIG
DBA_HIST_CELL_CONFIG_DETAIL
DBA_HIST_CELL_DB
DBA_HIST_CELL_DISKTYPE
DBA_HIST_CELL_DISK_NAME
DBA_HIST_CELL_DISK_SUMMARY
DBA_HIST_CELL_GLOBAL
DBA_HIST_CELL_GLOBAL_SUMMARY
DBA_HIST_CELL_IOREASON
DBA_HIST_CELL_IOREASON_NAME
DBA_HIST_CELL_METRIC_DESC
DBA_HIST_CELL_NAME
DBA_HIST_CELL_OPEN_ALERTS

The description of these views can be found in the Exadata Storage Server Users Guide.

The section starts off by showing the cell configuration information. Then it displays the Kernel  and the Cell Image version’s.

awex1-1

This information comes from the awr view DBA_HIST_CELL_DISK_SUMMARY.

The next section titled “Exadata Storage Information” storage information shows the number of disks and flash cards in each cell and the entire rack.

awex1-2
The first row of the output shows the amount of flash cache in each cell, The size of the smart flash log, Number of hard disks in a cell, Number of flash cards in each cell, and the number of Grid Disks in each cell.
The second row shows the above columns aggregated for all cells in the rack.

The next section titled “Exadata Griddisks” shows the grid disk names, Number of Grid disks in each cell, the Grid Disk size and The type of Drive

awex1-3
The next section titled “Exadata Cell Disks” shows the Disk type, Size of the cell disk, Number of disks .

awex2-1
The next section “ASM disksgroups” shows the diskgroups used by this database.

awex2-2
It shows the diskgroup name,Total size of the diskgroup,Used space, Number of disks in the diskgroup and the redundancy type.

This is followed by a section “Exadata Server Health Report”, which has 3 sub sections Exadata Alerts Summary,Exadata Alerts Detail,Exadata Non-Online Disks which displays information regarding alerts on the cells and any offline disks.
The remaining sections of Exadata performance statistics in the AWR report, display a great deal of Exadata cell performance numbers.

Before we venture much into those sections, it is important to understand some cell level concepts and how they are captured in Awr.

At the cell level if you list the following attributes (On a x5-2 cell with HD drives)

list cell attributes maxpdiops,maxpdmbps,maxfdiops,maxfdmbps you get the following values

167 111 8929 343

These values are collected and stored in the confval column in DBA_HIST_CELL_CONFIG_DETAIL in an XML format.

These base values are used to calculate the maximum capacities of the cells and disks in the sections that follow.

Shell script to generate awr reports for a range of snapshots

~ rajeevramdas@aol.com ~ 2 Comments

We have been doing some performance testing in the recent days. We were running some workload using an application, capturing awr snapshots every 15 minutes on a 8 node rac cluster. I needed to generate the awr reports between each of the snapshots for each of the nodes. Running awrrpt.sql for each 15 minute intervals for each one of the 8 nodes would have been a tedious task. So i wrote a bash shell script that generates those awr reports.

It takes the database id, instance number, start snapshot id and end snapshot id as arguments (you can query dba_hist_snapshot to find out the start and end snapshot id’s you want to use). The directory in which it generates the reports is hardcoded in the script. The script has to be run as the oracle user  and it logs in as sys to generate the reports (You can modify as you need if you want it to be run as a different user). I have tested it only on Linux.

You can run it as ./genawrs.sh dbid  instancenumber beginsnap endsnap, to generate the reports.  This means that you have to run it once for each instance you need the report for. The script can be found below.

#!/bin/bash

if [ $# != 4 ]
then
echo “Syntax  genawrs.sh dbid instanceId startsnapid endsnapid”
exit 1
fi

l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1

# For all snapshot id’s
# Set the next snapshot id as current snapshot id + 1
# Spool a log file
# Log into sqlplus and call dbms_workload_repository.awr_report_text
# To generate the awr

for i in `seq $l_start_snapid $l_end_snapid`
do
let l_next_snapid=$i+1;
l_awr_log_file=”/awrs/out/awrrpt_${2}_${i}_${l_next_snapid}.log”

sqlplus -s / as sysdba << EOC
set head off
set pages 0
set lines 132
set echo off
set feedback off
spool $l_awr_log_file
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_text
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOC
done