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:
Post a Comment