Tag Archives: DBA

How to Make the Most Out of Collaborate IOUG 2019!

A new video on how to make the most out of Collaborate 2019 is up on my YouTube channel.

Along with some handy tips, I also explain how to score a free Oracle certification exam AND how to get Oracle’s new Autonomous DBA certification!

Tagged , , , , , , ,

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’);

Tagged , , , ,

DBA, DMA, DBMA, other?

Quite some time ago, I read a fascinating article, co-written by easily the best Oracle instructor I ever had the pleasure of being taught by in Joel Goodman, which talked about the skills required to be a “DBA 2.0”.

They even mentioned Exadata needing its own “version”, though they suggested it would be “DBA 2.1”. I’m not sure Exadata had made it out into the wild at this point.

The article was written five or six years ago and was tremendously prescient. With the data industry at such a fascinating crossroads with Big Data, engineered systems and extreme performance, how will the DBA role change to keep up with the demands of the ever-increasing volume and mission-critical exploitation (hopefully the beneficial kind) of enterprise data?

Continue reading

Tagged , , , , , , , ,
%d bloggers like this: