Skip to main content

Search

Welcome to Tibero GTS!

Difference in Hash Values of the Same Plan According to NLS Parameter Values

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)

Caution
Apply the patch through technical support provided by Tmax Tibero.
  • If the FS07_229885f_2 patch is applied, it reverses the effects of patch 167822i, so apply patches after FS07_229885g.
  • This issue was discovered due to different initial NLS settings caused by JDBC driver version differences, so ensure that NLS parameters are not set differently.
 

 

 

 

Related to