Search My Oracle Blog

Custom Search

13 August, 2007

NLS_DATE_FORMAT

NLS_DATE_FORMAT

Friday afternoon, I was working with an application team to "tune" the most expensive SQL on the system -- an hourly MV refresh query.
The query was re-written (excluding data that was not needed) and ran much faster. Next, I then ran the refresh from the database server just prior to submitting a job for the hourly refresh. And I got my shock. The query ran for 39 minutes from my Unix telnet session although I had just seen it complete in less than 3 minutes on the desktop. We tested the same thing twice as "DBMS_MVIEW.REFRESH" calls.

I went back to the developer's desktop. He was using a non-Oracle client and I suspected that it was passing some directives ("alter session" commands). I couldn't find any such directives in the setup. I then looked at parameters with a SHOW PARAMETER. There was no difference in the listing from the desktop and the Unix telnet session. I went back to my PC, fired up a SQLPlus client and tried the DBMS_MVIEW.REFRESH. Sure enough, it was running in less than 2minutes.

I traced the executions from the server and the client. Rather than looking at the EXPLAIN PLAN, I looked at the ROW SOURCE OPERATIONS. I found that there was a difference in the actual execution. (If I were to tkprof with the explain option, the server-side Explain would still use the server-side NLS_DATE_FORMAT on both trace files, I suspect).

I knew that I should suspect NLS_DATE_FORMAT because the server-side init.ora had a date format DD-MON-YYYY HH24:MI:SS. But I saw no Registry settings on both desktops that would have set NLS_DATE_FORMAT to DD-MON-RR. Logically, the server-side format should apply (and that is what SHOW PARAMETER also seemed to show -- I saw the same value from both the client and the telnet session).

We then tested DBMS_JOB.SUBMIT and DBMS_JOB.RUN. Again (and not too surprisingly now !), the execution of the job submitted from the client was faster than the same job definition (but a different job id) from the server. NLS_ENV in DBA_JOBS showed that NLS_DATE_FORMAT for the job submitted from the client was DD-MON-RR.

So that meant :
a. The Windows Clients (whether the non-Oracle tool or my desktop's SQLPlus) was using DD-MON-RR and overrriding the server-side NLS_DATE_FORMAT. Since I could not find a client-side registry entry that explicitly sets this, it would mean that the client *defaults* to DD-MON-RR
b. SHOW PARAMETER doesn’t always show the current values being used by the session

And, quite obviously, different NLS_DATE_FORMAT’s meant that the results of the query could very well vary ! That is quite important to know.


Back at home during the weekend, I decided to test all of this again.

First, I create a “Test” Table.

Here I create a table with data from DBA_OBJECTS and modify the “LAST_DDL_TIME” values so that I can have a date colum with very many different date values.
=================================================================
SQL>
SQL> drop table hemant.test_table ;

Table dropped.

SQL>
SQL> create table hemant.test_table nologging as
2 select * from dba_objects
3 union
4 select * from dba_objects
5 /

Table created.

SQL>
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL>
SQL> update hemant.test_table
2 set last_ddl_time = (last_ddl_time-365)+rownum/100;

51557 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index hemant.test_tbl_ndx on hemant.test_table(last_ddl_time) nologging;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select to_char(min(last_ddl_time),'DD-MON-YYYY') from test_table ;

TO_CHAR(MIN
-----------
29-OCT-2001

SQL> select to_char(max(last_ddl_time),'DD-MON-YYYY') from test_table ;

TO_CHAR(MAX
-----------
17-OCT-2007

SQL>
SQL>

=================================================================

Next, I run my test script.

I find that the
a) even though the server-side NLS_DATE_FORMAT is DD-MON-YYYY …, the DBMS_JOB call actually inserts the setting of DD-MON-RR when I submit my first MV Refresh Job.
I am quite sure that I have not set this in my Windows Registry.
b) when I query by a two-digit year, Oracle does a Full Table Scan and retrieves all
the records – thus it is NOT behaving with “RR” ??
c) If I explicitly change my NLS_DATE_FORMAT to DD-MON-RR, the same query
converts to an Index Range Scan and finds only the specific rows that match the
specified date range.

What is the Moral Of The Story ?
Be Careful with NLS_DATE_FORMATs. Pay particular attention to see if your client is defaulting to DD-MON-RR when your server is set to some other value. There are application scenarios where you would *want* different NLS_DATE_FORMATs (eg in a global database with clients using different formats). Remember that data entry and translation are handled by NLS_% parameters. Verify that you are really seeing the data that you want to see.
=================================================================

SQL>
SQL> set pages600
SQL> set linesize 110
SQL>
SQL> col owner format a15
SQL> col object_name format a30 trunc
SQL> col name format a18
SQL> col value format a22
SQL>
SQL> select name, value from v$parameter where name = 'nls_date_format';

NAME VALUE
------------------ ----------------------
nls_date_format DD-MON-YYYY HH24:MI:SS

SQL>
SQL> show parameter nls_date_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-YYYY HH24:MI:SS
SQL>
SQL> select to_char(min(last_ddl_time),'DD-MON-YYYY') from test_table ;

TO_CHAR(MIN
-----------
29-OCT-2001

SQL> select to_char(max(last_ddl_time),'DD-MON-YYYY') from test_table ;

TO_CHAR(MAX
-----------
17-OCT-2007

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select owner, count(*)
2 from test_table
3 where last_ddl_time
4 > sysdate-365
5 group by owner
6 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467

---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 26 364 172 (7) 00:00:03
1 HASH GROUP BY 26 364 172 (7) 00:00:03
* 2 TABLE ACCESS FULL TEST_TABLE 19370 264K 169 (5) 00:00:03
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("LAST_DDL_TIME">SYSDATE@!-365)

SQL>
SQL> select owner, count(*)
2 from test_table
3 where last_ddl_time
4 > '01-OCT-07'
5 group by owner
6 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467

---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 26 364 174 (8) 00:00:03
1 HASH GROUP BY 26 364 174 (8) 00:00:03
* 2 TABLE ACCESS FULL TEST_TABLE 51556 704K 167 (4) 00:00:03
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("LAST_DDL_TIME">'01-OCT-07')

SQL>
SQL>
SQL> set autotrace off
SQL> drop materialized view test_mv;

Materialized view dropped.

SQL> create materialized view test_mv
2 build deferred
3 REFRESH WITH ROWID
4 as
5 select * from test_table
6 where last_ddl_time
7 > '01-OCT-07'
8 /

Materialized view created.

SQL>
SQL> variable jno number;
SQL> exec dbms_job.submit(:jno,'DBMS_MVIEW.REFRESH(''TEST_MV'',''C'');',sysdate+1,'sysdate+1/24');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from user_jobs order by job;

JOB LOG_USER PRIV_USER SCHEMA_USER
---------- ------------------------------ ------------------------------ ------------------------------
LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
--------- -------- --------- -------- --------- -------- ---------- -
INTERVAL
--------------------------------------------------------------------------------------------------------------
FAILURES
----------
WHAT
--------------------------------------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------
MISC_ENV INSTANCE
---------------------------------------------------------------- ----------
131 HEMANT HEMANT HEMANT
14-AUG-07 22:28:37 0 N
sysdate+1/24

DBMS_MVIEW.REFRESH('TEST_MV','C');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARAC
TERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000 0


SQL>
SQL>
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> show parameter nls_date_format;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-YYYY HH24:MI:SS
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select owner, count(*)
2 from test_table
3 where last_ddl_time
4 > '01-OCT-07'
5 group by owner
6 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467

---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 26 364 174 (8) 00:00:03
1 HASH GROUP BY 26 364 174 (8) 00:00:03
* 2 TABLE ACCESS FULL TEST_TABLE 51556 704K 167 (4) 00:00:03
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("LAST_DDL_TIME">'01-OCT-07')

SQL>
SQL>
SQL> set autotrace off
SQL> drop materialized view test_mv_2;

Materialized view dropped.

SQL> create materialized view test_mv_2
2 build deferred
3 REFRESH WITH ROWID
4 as
5 select * from test_table
6 where last_ddl_time
7 > '01-OCT-07'
8 /

Materialized view created.

SQL>
SQL> variable jno number;
SQL> exec dbms_job.submit(:jno,'DBMS_MVIEW.REFRESH(''TEST_MV_2'',''C'');',sysdate+1,'sysdate+1/24');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from user_jobs order by job;

JOB LOG_USER PRIV_USER SCHEMA_USER
---------- ------------------------------ ------------------------------ ------------------------------
LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-------------------- -------- -------------------- -------- -------------------- -------- ---------- -
INTERVAL
--------------------------------------------------------------------------------------------------------------
FAILURES
----------
WHAT
--------------------------------------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------
MISC_ENV INSTANCE
---------------------------------------------------------------- ----------
131 HEMANT HEMANT HEMANT
14-AUG-2007 22:28:37 22:28:37 0 N
sysdate+1/24

DBMS_MVIEW.REFRESH('TEST_MV','C');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARAC
TERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000 0

132 HEMANT HEMANT HEMANT
14-AUG-2007 22:28:41 22:28:41 0 N
sysdate+1/24

DBMS_MVIEW.REFRESH('TEST_MV_2','C');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARAC
TERS='.,' NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000 0


SQL>
SQL> show parameter nls_date_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-YYYY HH24:MI:SS
SQL>
SQL> set autotrace on
SQL>
SQL> select owner, count(*)
2 from test_table
3 where last_ddl_time
4 > '01-OCT-07'
5 group by owner
6 /

OWNER COUNT(*)
--------------- ----------
MDSYS 885
DMSYS 189
TSMSYS 3
CTXSYS 339
FLOWS_FILES 12
HR 34
OLAPSYS 720
OUTLN 8
PUBLIC 20073
EXFSYS 281
HEMANT 25
SCOTT 6
SYSTEM 454
DBSNMP 46
OE 127
ORDPLUGINS 10
ORDSYS 1669
PM 26
SH 306
SYSMAN 1321
BI 8
IX 53
XDB 682
FLOWS_010600 1111
SI_INFORMTN_SCH 8
EMA

SYS 22918
WMSYS 242

27 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467

---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 26 364 174 (8) 00:00:03
1 HASH GROUP BY 26 364 174 (8) 00:00:03
* 2 TABLE ACCESS FULL TEST_TABLE 51556 704K 167 (4) 00:00:03
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("LAST_DDL_TIME">'01-OCT-07')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
714 consistent gets
704 physical reads
0 redo size
990 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
27 rows processed

SQL>
SQL>
SQL> alter session set nls_date_format='DD-MON-RR';

Session altered.

SQL>
SQL> show parameter nls_date_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-YYYY HH24:MI:SS
SQL>
SQL> select owner, count(*)
2 from test_table
3 where last_ddl_time
4 > '01-OCT-07'
5 group by owner
6 /

OWNER COUNT(*)
--------------- ----------
SYS 55


Execution Plan
----------------------------------------------------------
Plan hash value: 3358789471

---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 26 364 57 (2) 00:00:01
1 HASH GROUP BY 26 364 57 (2) 00:00:01
2 TABLE ACCESS BY INDEX ROWID TEST_TABLE 729 10206 56 (0) 00:00:01
* 3 INDEX RANGE SCAN TEST_TBL_NDX 729 3 (0) 00:00:01
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LAST_DDL_TIME">'01-OCT-07')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> alter session set "_GBY_HASH_AGGREGATION_ENABLED"=FALSE ;

Session altered.

SQL>
SQL> select owner, count(*)
2 from test_table
3 where last_ddl_time
4 > '01-OCT-07'
5 group by owner
6 /

OWNER COUNT(*)
--------------- ----------
SYS 55


Execution Plan
----------------------------------------------------------
Plan hash value: 2247708158

---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 26 364 57 (2) 00:00:01
1 SORT GROUP BY 26 364 57 (2) 00:00:01
2 TABLE ACCESS BY INDEX ROWID TEST_TABLE 729 10206 56 (0) 00:00:01
* 3 INDEX RANGE SCAN TEST_TBL_NDX 729 3 (0) 00:00:01
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LAST_DDL_TIME">'01-OCT-07')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

=================================================================

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016