Sunday, 5 July 2020

DB Performance issue due to DST_UPGRADE_STATE=DATAPUMP(1)

SQL> select name, value$ from sys.props$ where name like '%DST%';
NAME                                     VALUE$
---------------------------     -----------------------
DST_SECONDARY_TT_VERSION                 14
DST_PRIMARY_TT_VERSION                   34
DST_UPGRADE_STATE                  DATAPUMP(1)

Database performance issue due to db is in timezone upgrade mode
DST_UPGRADE_STATE should set to NONE for better performance 

as per oracle document below are the steps to downgrade it 

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

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

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

Session altered.

SQL> exec dbms_dst.unload_secondary;

PL/SQL procedure successfully completed.

SQL>  select name, value$ from sys.props$ where name like '%DST%';
NAME                                     VALUE$
---------------------------     ---------------------------
DST_SECONDARY_TT_VERSION                   0
DST_PRIMARY_TT_VERSION                     34
DST_UPGRADE_STATE                          NONE



No comments:

Post a Comment