Category Archives: Checklist

Exadata System Statistics

Since August 2012, the DBMS_STATS.GATHER_SYSTEM_STATS procedure has offered an ‘EXADATA‘ option to allow Exadata-specific system statistics to be gathered.   The following versions / patchsets of the database include this option:

  • 11.2.0.2.18
  • 11.2.0.3.8
  • 11.2.0.4
  • Any version of 12c

Gathering Exadata-specific system statistics ensures the optimizer is aware of the Exadata performance features and takes them into account when determining the execution plan – often resulting in SmartScans (full-table scans) instead of indexes.

To determine the last time that system statistics were gathered on the database:

COL statistic FORMAT a55
COL value FORMAT a20
SELECT pname AS statistic,
pval2 AS value
FROM aux_stats$
WHERE pname IN (‘STATUS’,’DSTART’,’DSTOP’)
ORDER BY pname;


DSTART                                                          03-28-2011 13:59
DSTOP                                                            03-28-2011 13:59
STATUS                                                          COMPLETED

This indicates that the system statistics were not run AFTER the ability to compile Exadata-specific statistics was made available (August 2012).  Also, they have not run since this particular database migrated from a V2 machine to an X3-2 machine earlier in the year, thus they are unlikely to be accurate.

This was the likely cause of a performance problem we encountered recently, which eventually required a FULL hint to be made to the optimizer to allow the query to complete in an acceptable timeframe.

To determine the values of the system statistics (I used DECODE to format them nicely):

SELECT DECODE(pname,
‘CPUSPEED’,’CPUSPEED: (Workload) CPU speed in millions of cycles/second’,
‘CPUSPEEDNW’,’CPUSPEEDNW: (No Workload) CPU speed in millions of cycles/second’,
‘IOSEEKTIM’,’IOSEEKTIM: Seek time + latency time + operating system overhead time in milliseconds’,
‘IOTFRSPEED’,’IOTFRSPEED: Rate of a single read request in bytes/millisecond’,
‘MAXTHR’,’MAXTHR: Maximum throughput that the I/O subsystem can deliver in bytes/second’,
‘MBRC’,’MBRC: Average multiblock read count sequentially in blocks’,
‘MREADTIM’,’MREADTIM: Average time for a multi-block read request in milliseconds’,
‘SLAVETHR’,’SLAVETHR: Average parallel slave I/O throughput in bytes/second’,
‘SREADTIM’,’SREADTIM: Average time for a single-block read request in milliseconds’
) AS statistic,
pval1 AS value
FROM aux_stats$
WHERE pname IN (‘CPUSPEEDNW’,
‘IOSEEKTIM’,’IOTFRSPEED’,
‘SREADTIM’,’MREADTIM’,
‘CPUSPEED’,’MBRC’,
‘MAXTHR’,’SLAVETHR’)
AND sname = ‘SYSSTATS_MAIN’
ORDER BY pname;

CPUSPEED: (Workload) CPU speed in millions of cycles/second
CPUSPEEDNW: (No Workload) CPU speed in millions of cycles/second                            2351.43
IOSEEKTIM: Seek time + latency time + operating system overhead time in milliseconds 10
IOTFRSPEED: Rate of a single read request in bytes/millisecond                                      4096
MAXTHR: Maximum throughput that the I/O subsystem can deliver in bytes/second
MBRC: Average multiblock read count sequentially in blocks
MREADTIM: Average time for a multi-block read request in milliseconds
SLAVETHR: Average parallel slave I/O throughput in bytes/second
SREADTIM: Average time for a single-block read request in milliseconds

If the value for the MBRC system statistic is NULL, the optimizer uses the value for db_file_multiblock_read_count which, on this database, is 64.

Exadata benefits from higher values for MBRC as this increases the likelihood that the optimizer will choose to perform full table scans over an index.  Gathering Exadata system statistics will set the MBRC at 128 and will likely set a significantly higher value for the IOTFRSPEED.   These statistics are set based on your machine, not gathered, because:

  • the database won’t take the storage cells into account when calculating multi-block reads
  • direct path reads are not counted as multi-block reads for the MBRC system statistic

We should NOT gather system statistics with a workload on Exadata as the database will attempt to calculate the MBRC rate itself, likely resulting in a significantly lower (and inaccurate) MBRC value.

Gathering Exadata system statistics is pretty simple and should be done if the statistics are older than August 2012 or when you migrate a database to an Exadata machine. We can either back up the stats beforehand or keep a note of their values which we can use to manually set if required, then issue the following command:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’);

Advertisements
Tagged , , , ,

DBA 3.0 – How to Become a Real-World Exadata DBA – IOUG Collaborate 2015

According to a Book of Lists survey, 41% of people’s biggest fear is “public speaking”.  To put that into perspective, “death” is the biggest fear for 19%, “flying” for 18% and “clowns” don’t even register (which does make me seriously doubt the survey’s credibility).

I gave my first public presentation at IOUG Collaborate 2015 last week in Las Vegas and I didn’t die.

Why did do make your presentation debut at the second largest Oracle event on the calendar?  Excellent question.

Continue reading

Tagged , , , ,

My Collaborate IOUG 2015 Abstract

I will be presenting DBA 3.0 or “How to Become a Real-World Exadata DBA” at Collaborate 2015 – IOUG’s annual user conference – from April 12th to 16th at the Mandalay Bay Resort and Casino in Las Vegas. I submitted this as my abstract:

“DBA resources are more scarce than ever before and it can be very difficult to allocate time on anything but keeping the lights on – even when an organization has made a (substantial) hardware investment in Exadata.

However, if Exadata is treated like any other Oracle database, the promised “extreme performance” will likely be very underwhelming to developers, users and managers and can become unwieldy for DBAs to support.

On the other hand, when an organization configures and supports Exadata properly, they can realize exponential performance improvements in key IT infrastructure, can facilitate better business decisions and may actually reduce infrastructure costs.

The customer has bought a sports car – but might not realize that they haven’t taken it out of second gear (yet).

I will talk about the evolution of Exadata and then get into the “nuts and bolts” of how to support a high-performance Exadata environment as a Production DBA.

I will discuss how to get performance improvements of up to 20x, what NOT to do as an Exadata DBA and how Exadata can become the foundation of your organization’s high-performance enterprise infrastructure.”

I hope to see you in Las Vegas!

Tagged , , ,

My Exadata “Check List”

I’ve been maintaining a configuration “check list” for Exadata machines in my EverNote for months and finally decided it was time to put it into a document.

This is meant to be used in the event of a new Exadata machine arriving at our data center or before some major upcoming change. Do you have anything else to add that you specifically check for?  Continue reading

Tagged , , ,
Advertisements
%d bloggers like this: