Exadata: RDS Performance Degrades when Database is in Timezone Upgrade Mode

Asset ID:	1-72-1583297.1
Update Date:	2013-09-26
Keywords:

Solution Type Problem Resolution Sure

Solution 1583297.1 : Exadata: RDS Performance Degrades when Database is in Timezone Upgrade Mode

Applies to:

Oracle Exadata Storage Server Software - Version 11.2.2.3.0 to 11.2.3.2.1 [Release 11.2]
Oracle Exadata Hardware - Version 11.2.0.1 and later
Exadata Database Machine V2 - Version All Versions and later
Exadata Database Machine X2-2 Hardware - Version All Versions and later
Exadata Database Machine X2-8 - Version All Versions and later
Information in this document applies to any platform.

Symptoms

RDS (and database) performance suffers when database is in timezone upgrade mode.

To determine if the database is in timezone upgrade mode you might see something like:

select name, value$ from sys.props$ where name like '%DST%';

DST_UPGRADE_STATE DATAPUMP(1)
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 11

select property_value from sys.database_properties where property_name = 'DST_UPGRADE_STATE';

PROPERTY_VALUE
------------------------------------------------------------------------------
DATAPUMP(1)

In an AWR or Statspack report the 'cell num smart IO sessions using passthru mode due to timezone' statistic is seen to be increasing.

And in v$cell_state, you would see:

<noopt_reasons group="Predicate Noopt Reasons" name="Smart scan">
<stat name="Smart IO uses passthru as timezone file is unavailable">390</stat>

Changes

Timezone was recently changed (usually to and from daylight savings time).

Cause

When the DB timezone is in upgrade mode or inconsistent mode, I/Os issued from DB nodes to cell nodes will not go through smart scan and hence we do block I/O instead. This results in cell nodes shipping all blocks rather than blocks of interest (filtered) to the database. If the I/O size is huge the IO might saturate the RDS traffic and impact the RDA service times along with database performance.

Solution

1. Enable event 30090:

SQL> ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';

2. Execute the following:

SQL> exec dbms_dst.unload_secondary;

3. Validate that the database is not in DST upgrade mode

SQL> select property_value from sys.database_properties where property_name = 'DST_UPGRADE_STATE';

PROPERTY_VALUE
----------------------------------
NONE

SQL> select name, value$ from sys.props$ where name like '%DST%';

NAME
------------------------------
VALUE$

------------------------------------------------------------------------------
DST_UPGRADE_STATE
NONE

DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
0

Attachments

This solution has no attachment