Document Type | Troubleshooting
Category | Tuning
Applicable Product Versions | 6FS06, 6FS07
Document Number | TTUTS004
Issue
NLS parameters affect the plan, causing the Plan Hash Value to be set differently even for the same plan.
If the following NLS parameters are set differently in JDBC for the same plan, different plan hash values occur.
NLS_CALENDAR, NLS_COMP, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_LENGTH_SEMANTICS, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT
-- Example showing how the Plan hash value for the same query changes according to NLS_TIMESTAMP_TZ_FORMAT modification SQL> select * from dba_datafiles order by 2; SQL ID: g6tt6d49311kw Child number: 1290 Plan hash value: 1966304868 Execution Plan 1 ORDER BY (SORT) (Cost:741454, %%CPU:6, Rows:22322851) 2 HASH JOIN (Cost:504, %%CPU:66, Rows:22322851) 3 DPV: _VT_DATAFILE_BM (Cost:41, %%CPU:0, Rows:7268) 4 HASH JOIN (Cost:142, %%CPU:10, Rows:1535694) 5 DPV: _VT_DBFILE (Cost:41, %%CPU:0, Rows:7268) 6 HASH JOIN (Cost:86, %%CPU:1, Rows:105647) 7 HASH JOIN (Cost:44, %%CPU:0, Rows:7268) 8 TABLE ACCESS (ROWID): _DD_TS (Cost:3, %%CPU:0, Rows:5) 9 INDEX (FULL): _DD_TS_IDX1 (Cost:2, %%CPU:0, Rows:5) 10 DPV: _VT_DATAFILE (Cost:41, %%CPU:0, Rows:7268) 11 DPV: _VT_DATAFILE_HEADER (Cost:41, %%CPU:0, Rows:7268) SQL>alter session set NLS_TIMESTAMP_TZ_FORMAT="YYYY MM DD HH24.MI.SSXFF TZR"; Session altered. SQL> select * from dba_datafiles order by 2; SQL ID: g6tt6d49311kw Child number: 1509 Plan hash value: 1223193576 Execution Plan 1 ORDER BY (SORT) (Cost:741454, %%CPU:6, Rows:22322851) 2 HASH JOIN (Cost:504, %%CPU:66, Rows:22322851) 3 DPV: _VT_DATAFILE_BM (Cost:41, %%CPU:0, Rows:7268) 4 HASH JOIN (Cost:142, %%CPU:10, Rows:1535694) 5 DPV: _VT_DBFILE (Cost:41, %%CPU:0, Rows:7268) 6 HASH JOIN (Cost:86, %%CPU:1, Rows:105647) 7 HASH JOIN (Cost:44, %%CPU:0, Rows:7268) 8 TABLE ACCESS (ROWID): _DD_TS (Cost:3, %%CPU:0, Rows:5) 9 INDEX (FULL): _DD_TS_IDX1 (Cost:2, %%CPU:0, Rows:5) 10 DPV: _VT_DATAFILE (Cost:41, %%CPU:0, Rows:7268) 11 DPV: _VT_DATAFILE_HEADER (Cost:41, %%CPU:0, Rows:7268)
Cause
The plan hash value is configured to be influenced by NLS parameters, so the same plan can have different hash values depending on the NLS parameters.
Solutions
Apply the patch to resolve the issue. (Applied patch: 167822i)
CautionApply the patch through technical support provided by Tmax Tibero.