Here is an example when reading "COST" alone can be wrong.
SQL>
SQL> REM ===========================================================================
SQL> REM Discover the table TEST_TABLE
SQL> REM This is a copy of DBA_OBJECTS, inserted twice
SQL>
SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL> column index_name format a30
SQL> column column_name format a30
SQL> column column_position format 9999 hea 'Posn'
SQL>
SQL> REM Identify Indexes present
SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name = 'TEST_TABLE'
4 order by index_name, column_position
5 /
INDEX_NAME COLUMN_NAME Posn
------------------------------ ------------------------------ -----
TEST_TABLE_NDX_1 CREATED 1
Elapsed: 00:00:00.39
SQL>
SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:42.51
SQL> select count(*) from test_table;
COUNT(*)
----------
103114
Elapsed: 00:00:00.65
SQL> select blocks, num_rows from user_tables where table_name = 'TEST_TABLE';
BLOCKS NUM_ROWS
---------- ----------
1460 103114
Elapsed: 00:00:00.14
SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'TEST_TABLE_NDX_1';
LEAF_BLOCKS NUM_ROWS
----------- ----------
273 103114
Elapsed: 00:00:00.17
SQL>
SQL> REM ===========================================================================
SQL> REM Get the query execution plan for MIN queries
SQL> set autotrace on
SQL>
SQL> select min(created) from test_table
2 /
MIN(CREAT
---------
30-AUG-05
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 108964483
--------------------------------------------------------------------------------
---------------
Id Operation Name Rows Bytes Cost (%C
PU) Time
--------------------------------------------------------------------------------
---------------
0 SELECT STATEMENT 1 8 328
(3) 00:00:04
1 SORT AGGREGATE 1 8
2 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
---------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 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> REM --> that was only 2 Block Gets
SQL>
SQL> REM ===========================================================================
SQL> REM Get the query execution plan for MAX queries
SQL> set autotrace on
SQL>
SQL> select max(created) from test_table
2 /
MAX(CREAT
---------
12-AUG-07
Elapsed: 00:00:00.17
Execution Plan
----------------------------------------------------------
Plan hash value: 108964483
--------------------------------------------------------------------------------
---------------
Id Operation Name Rows Bytes Cost (%C
PU) Time
--------------------------------------------------------------------------------
---------------
0 SELECT STATEMENT 1 8 328
(3) 00:00:04
1 SORT AGGREGATE 1 8
2 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
---------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 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> REM --> Again, only 2 Block Gets
SQL> REM ===========================================================================
SQL> REM Get Min and Max together
SQL> set autotrace on
SQL>
SQL> select min(created), max(created) from test_table
2 /
MIN(CREAT MAX(CREAT
--------- ---------
30-AUG-05 12-AUG-07
Elapsed: 00:00:02.28
Execution Plan
----------------------------------------------------------
Plan hash value: 711311523
--------------------------------------------------------------------------------
-
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
-
0 SELECT STATEMENT 1 8 328 (3) 00:00:04
1 SORT AGGREGATE 1 8
2 TABLE ACCESS FULL TEST_TABLE 103K 805K 328 (3) 00:00:04
--------------------------------------------------------------------------------
-
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1445 consistent gets
1440 physical reads
0 redo size
490 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> REM --> That took 1,445 Block Gets -- at the same "COST" of 328 !
SQL>
SQL>
SQL> REM ===========================================================================
SQL> REM Try using a UNION
SQL> set autotrace on
SQL>
SQL> select min(created) Min_date, NULL Max_date from test_table
2 union
3 select NULL Min_date, max(created) Max_date from test_table
4 /
MIN_DATE MAX_DATE
--------- ---------
30-AUG-05
12-AUG-07
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 1754591915
--------------------------------------------------------------------------------
-------------------------
Id Operation Name Rows Bytes TempSpc
Cost (%CPU) Time
--------------------------------------------------------------------------------
-------------------------
0 SELECT STATEMENT 2 16
1426 (52) 00:00:18
1 SORT UNIQUE 2 16 6513K
1426 (52) 00:00:18
2 UNION-ALL
3 SORT AGGREGATE 1 8
713 (4) 00:00:09
4 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
5 SORT AGGREGATE 1 8
713 (4) 00:00:09
6 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
-------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> REM --> Only 4 Block Gets, although the "COST" is 1,426
SQL>
SQL> REM ===========================================================================
SQL> REM Try using Inline Views to get the two values together in single row
SQL> set autotrace on
SQL>
SQL> select a.Min_Date, b.Max_date from
2 (select min(created) Min_date from test_table) a,
3 (select max(created) Max_date from test_table) b
4 /
MIN_DATE MAX_DATE
--------- ---------
30-AUG-05 12-AUG-07
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1527082337
--------------------------------------------------------------------------------
-----------------
Id Operation Name Rows Bytes Cost (
%CPU) Time
--------------------------------------------------------------------------------
-----------------
0 SELECT STATEMENT 1 18 655
(2) 00:00:08
1 NESTED LOOPS 1 18 655
(2) 00:00:08
2 VIEW 1 9 328
(3) 00:00:04
3 SORT AGGREGATE 1 8
4 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
5 VIEW 1 9 328
(3) 00:00:04
6 SORT AGGREGATE 1 8
7 INDEX FULL SCAN (MIN/MAX) TEST_TABLE_NDX_1 103K 805K
--------------------------------------------------------------------------------
-----------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
482 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> REM --> Only 4 Block Gets, although the "COST" is 655
SQL>
SQL>
SQL> spool off
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
28 August, 2007
24 August, 2007
NULLs are not Indexed, Right ? NOT !
Update 09-Feb-08 :
See MetaLink Note#551754.1 "Query with ROWNUM Condition On a Table Which Has Index on Column "Constant" Returns No Rows" for a caveat. (Bug#6737251). The recommendation there is to NOT use a Constant in an Index.
We all know very well that NULLs are not indexed and a query for NULL values in a column cannot use the index on that column ? However, there is a way to index a column with NULL values.
I read about this method a few times on different blogs in the past week. So I decided to create my own test case.
What I do is to add a CONSTANT to the column being indexed. Since the Constant is NOT NULL but is the second column in the index, the NULLs of our actual column have to get included in the index.
This method fails on 10.2.0.1 if the constant is a "1". See the references in my example below :
SQL>
SQL> REM ===========================================================================
SQL> REM Discover the table TEST_TABLE and how many NULLs does the STATUS column have
SQL> REM -- FYI, this is a prebuilt copy of DBA_OBJECTS
SQL> REM where I have updated STATUS to NULL for 29 rows
SQL>
SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL> column index_name format a30
SQL> column column_name format a30
SQL> column column_position format 9999 hea 'Posn'
SQL>
SQL> REM The index that is currently present is not usable in our test queries later
SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name = 'TEST_TABLE'
4 order by index_name, column_position
5 /
INDEX_NAME COLUMN_NAME Posn
------------------------------ ------------------------------ -----
TEST_TBL_NDX LAST_DDL_TIME 1
Elapsed: 00:00:00.21
SQL>
SQL> select decode(status,'VALID','VALID',NULL,'Is Null'),
2 count(*)
3 from test_table
4 group by decode(status,'VALID','VALID',NULL,'Is Null')
5 /
DECODE( COUNT(*)
------- ----------
VALID 51528
Is Null 29
Elapsed: 00:00:00.17
SQL>
SQL> REM ===========================================================================
SQL> REM Get the query execution plan for queries where status is NULL
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:04.43
SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from test_table where status is NULL
2 /
COUNT(*)
----------
29
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 711311523
--------------------------------------------------------------------------------
-
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
-
0 SELECT STATEMENT 1 6 165 (3) 00:00:02
1 SORT AGGREGATE 1 6
* 2 TABLE ACCESS FULL TEST_TABLE 16 96 165 (3) 00:00:02
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
714 consistent gets
405 physical reads
0 redo size
411 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> REM We saw above that it was doing an FTS and 714 block gets
SQL> REM ===========================================================================
SQL>
SQL> REM ===========================================================================
SQL> REM Create an Index on status and try again
SQL> set autotrace off
SQL>
SQL> create index test_table_status_ndx1 on test_table(status)
2 /
Elapsed: 00:00:00.50
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:03.71
SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from test_table where status is NULL
2 /
COUNT(*)
----------
29
Elapsed: 00:00:00.29
Execution Plan
----------------------------------------------------------
Plan hash value: 711311523
--------------------------------------------------------------------------------
-
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
-
0 SELECT STATEMENT 1 6 165 (3) 00:00:02
1 SORT AGGREGATE 1 6
* 2 TABLE ACCESS FULL TEST_TABLE 43 258 165 (3) 00:00:02
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
714 consistent gets
709 physical reads
0 redo size
411 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> REM Still doing an FTS and 714 block gets because index test_table_status_ndx1 does
SQL> REM not capture NULLs !
SQL> REM ===========================================================================
SQL>
SQL> REM ===========================================================================
SQL> REM Create a "concatenated" index on status and try again
SQL> REM -- my concatenated index, as if an FBI, uses a constant "A"
SQL> REM why did I use "A" instead of 1 ?
SQL> REM my first few tests with "1" kept reporting ORA-600 [qkssao2s1] errors on dbms_stats
SQL> REM -- although the Execution Plan WAS working
SQL> REM If I used "2" as the constant, I got worse errors and disconnections
SQL> REM So I decided to use an Alpha character, as STATUS is VARCHAR2
SQL> REM For more information on qkssao2s1 and constant "1" see Note#5005939.8
SQL>
SQL> create index test_table_status_ndx2 on test_table(status,'A')
2 /
Elapsed: 00:00:00.48
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:05.39
SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from test_table where status is NULL
2 /
COUNT(*)
----------
29
Elapsed: 00:00:00.64
Execution Plan
----------------------------------------------------------
Plan hash value: 180495830
--------------------------------------------------------------------------------
------------
Id Operation Name Rows Bytes Cost (%CPU)
Time
--------------------------------------------------------------------------------
------------
0 SELECT STATEMENT 1 6 2 (0)
00:00:01
1 SORT AGGREGATE 1 6
* 2 INDEX RANGE SCAN TEST_TABLE_STATUS_NDX2 45 270 2 (0)
00:00:01
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
411 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> REM Aah ! Now we see the Index test_table_status_ndx2 being used to get NULLs
SQL> REM on status. NULLs *are* in this index because status is only the leading column
SQL> REM of the index and the succeeding column is a not null
SQL> REM The performance gain comes from having to do 2 block gets instead of 714
SQL> REM ===========================================================================
SQL> REM ===========================================================================
SQL> set autotrace off
SQL> drop index test_table_status_ndx1;
Elapsed: 00:00:00.59
SQL> drop index test_table_status_ndx2;
Elapsed: 00:00:00.31
SQL> spool off
See MetaLink Note#551754.1 "Query with ROWNUM Condition On a Table Which Has Index on Column "Constant" Returns No Rows" for a caveat. (Bug#6737251). The recommendation there is to NOT use a Constant in an Index.
We all know very well that NULLs are not indexed and a query for NULL values in a column cannot use the index on that column ? However, there is a way to index a column with NULL values.
I read about this method a few times on different blogs in the past week. So I decided to create my own test case.
What I do is to add a CONSTANT to the column being indexed. Since the Constant is NOT NULL but is the second column in the index, the NULLs of our actual column have to get included in the index.
This method fails on 10.2.0.1 if the constant is a "1". See the references in my example below :
SQL>
SQL> REM ===========================================================================
SQL> REM Discover the table TEST_TABLE and how many NULLs does the STATUS column have
SQL> REM -- FYI, this is a prebuilt copy of DBA_OBJECTS
SQL> REM where I have updated STATUS to NULL for 29 rows
SQL>
SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL> column index_name format a30
SQL> column column_name format a30
SQL> column column_position format 9999 hea 'Posn'
SQL>
SQL> REM The index that is currently present is not usable in our test queries later
SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name = 'TEST_TABLE'
4 order by index_name, column_position
5 /
INDEX_NAME COLUMN_NAME Posn
------------------------------ ------------------------------ -----
TEST_TBL_NDX LAST_DDL_TIME 1
Elapsed: 00:00:00.21
SQL>
SQL> select decode(status,'VALID','VALID',NULL,'Is Null'),
2 count(*)
3 from test_table
4 group by decode(status,'VALID','VALID',NULL,'Is Null')
5 /
DECODE( COUNT(*)
------- ----------
VALID 51528
Is Null 29
Elapsed: 00:00:00.17
SQL>
SQL> REM ===========================================================================
SQL> REM Get the query execution plan for queries where status is NULL
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:04.43
SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from test_table where status is NULL
2 /
COUNT(*)
----------
29
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 711311523
--------------------------------------------------------------------------------
-
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
-
0 SELECT STATEMENT 1 6 165 (3) 00:00:02
1 SORT AGGREGATE 1 6
* 2 TABLE ACCESS FULL TEST_TABLE 16 96 165 (3) 00:00:02
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
714 consistent gets
405 physical reads
0 redo size
411 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> REM We saw above that it was doing an FTS and 714 block gets
SQL> REM ===========================================================================
SQL>
SQL> REM ===========================================================================
SQL> REM Create an Index on status and try again
SQL> set autotrace off
SQL>
SQL> create index test_table_status_ndx1 on test_table(status)
2 /
Elapsed: 00:00:00.50
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:03.71
SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from test_table where status is NULL
2 /
COUNT(*)
----------
29
Elapsed: 00:00:00.29
Execution Plan
----------------------------------------------------------
Plan hash value: 711311523
--------------------------------------------------------------------------------
-
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
-
0 SELECT STATEMENT 1 6 165 (3) 00:00:02
1 SORT AGGREGATE 1 6
* 2 TABLE ACCESS FULL TEST_TABLE 43 258 165 (3) 00:00:02
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
714 consistent gets
709 physical reads
0 redo size
411 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> REM Still doing an FTS and 714 block gets because index test_table_status_ndx1 does
SQL> REM not capture NULLs !
SQL> REM ===========================================================================
SQL>
SQL> REM ===========================================================================
SQL> REM Create a "concatenated" index on status and try again
SQL> REM -- my concatenated index, as if an FBI, uses a constant "A"
SQL> REM why did I use "A" instead of 1 ?
SQL> REM my first few tests with "1" kept reporting ORA-600 [qkssao2s1] errors on dbms_stats
SQL> REM -- although the Execution Plan WAS working
SQL> REM If I used "2" as the constant, I got worse errors and disconnections
SQL> REM So I decided to use an Alpha character, as STATUS is VARCHAR2
SQL> REM For more information on qkssao2s1 and constant "1" see Note#5005939.8
SQL>
SQL> create index test_table_status_ndx2 on test_table(status,'A')
2 /
Elapsed: 00:00:00.48
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'HEMANT',tabname=>'TEST_TABLE',cascade=>TRUE);
Elapsed: 00:00:05.39
SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from test_table where status is NULL
2 /
COUNT(*)
----------
29
Elapsed: 00:00:00.64
Execution Plan
----------------------------------------------------------
Plan hash value: 180495830
--------------------------------------------------------------------------------
------------
Id Operation Name Rows Bytes Cost (%CPU)
Time
--------------------------------------------------------------------------------
------------
0 SELECT STATEMENT 1 6 2 (0)
00:00:01
1 SORT AGGREGATE 1 6
* 2 INDEX RANGE SCAN TEST_TABLE_STATUS_NDX2 45 270 2 (0)
00:00:01
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
411 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> REM Aah ! Now we see the Index test_table_status_ndx2 being used to get NULLs
SQL> REM on status. NULLs *are* in this index because status is only the leading column
SQL> REM of the index and the succeeding column is a not null
SQL> REM The performance gain comes from having to do 2 block gets instead of 714
SQL> REM ===========================================================================
SQL> REM ===========================================================================
SQL> set autotrace off
SQL> drop index test_table_status_ndx1;
Elapsed: 00:00:00.59
SQL> drop index test_table_status_ndx2;
Elapsed: 00:00:00.31
SQL> spool off
18 August, 2007
How does the CBO select an Index ?
Don Seiler has posted his experience with a particular troublesome issue with the CBO.
Why was it selecting "FOO_IDX" over "FOO_PK" ? See
die Seilerwerks: Dr. Statslove or: How I Learned to Stop Guessing and Love the 10053 Trace
Why was it selecting "FOO_IDX" over "FOO_PK" ? See
die Seilerwerks: Dr. Statslove or: How I Learned to Stop Guessing and Love the 10053 Trace
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>
=================================================================
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>
=================================================================
Shared Nothing or Shared Disks (RAC) ?
Kevin Closson has written a (what some call "long") post on Shared Nothing (EnterpriseDB, DB2) v Shared Disks (Oracle RAC). Read it if you work with RAC or DB2 or both. Having read that, also read modred's response (on the bonglonglong blog) All In the Assumptions.
08 August, 2007
LGWR and 'log file sync waits'
Kevin Closson has posted an insightful note on LGWR -- why sometimes the wait event 'log file sync' does not mean Redo Log File Write (ie I/O) waits. LGWR spends time in waiting on writes that it has posted but also in returning to post the sessions waiting on *it* to the effect that the writes have completed. It is the second part of the LGWR time that is the "processing" time which may not be getting enough CPU !
Subscribe to:
Posts (Atom)