CLUSTERING_FACTOR

I had written a post about CLUSTERING_FACTOR more than a year ago.

I am re-visiting the issue here with a Test Case. I've written the SQL script with REM comments inline. Therefore, the output below is self-explanatory :

SQL> 
SQL> set arraysize 5000
SQL> set pages600
SQL> set linesize132
SQL> 
SQL> set feedback off
SQL> 
SQL> set SQLPrompt ''
set SQLContinue ''

REM  I wil be creating my test table from this table of 1.368million rows
REM  This is a copy of DBA_OBJECTS multiplied many times
select num_rows, sample_size, blocks from user_tables where table_name = 'SOURCE_TABLE';

  NUM_ROWS SAMPLE_SIZE     BLOCKS                                                                                                   
---------- ----------- ----------                                                                                                   
   1368063     1368063      18967                                                                                                   


REM  This is my Test Table to review CLUSTERING_FACTOR
drop table MY_TEST_TABLE purge ;
create table MY_TEST_TABLE  as select * from SOURCE_TABLE where 1=2;
alter table MY_TEST_TABLE modify (OWNER not null, OBJECT_NAME not null, OBJECT_ID not null);
alter table MY_TEST_TABLE nologging;

create index MY_TEST_TABLE_NDX_1 on MY_TEST_TABLE (OWNER, OBJECT_NAME) ;


REM REM REM ################### Setup Table with first dataset

insert into MY_TEST_TABLE select * from SOURCE_TABLE ;
commit;
-- rem need to do a FullTableScan to effect delayed block cleanout
select count(*) from MY_TEST_TABLE dc;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   1368063                                                                                                                          

exec dbms_stats.gather_table_stats(user,'MY_TEST_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER, OBJECT_NAME SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

select table_name, num_rows, blocks from user_tables order by 1;

TABLE_NAME                       NUM_ROWS     BLOCKS                                                                                
------------------------------ ---------- ----------                                                                                
MY_TEST_TABLE                     1368063      19277                                                                                
SOURCE_TABLE                      1368063      18967                                                                                
select index_name, num_rows, distinct_keys, leaf_blocks, clustering_factor from user_indexes order by 1;

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR                                               
------------------------------ ---------- ------------- ----------- -----------------                                               
MY_TEST_TABLE_NDX_1               1368063         49232        9778           1343029                                               
REM  Notice the very high CLUSTERING_FACTOR -- almost as high as the number of rows !

select  count(*) from source_table where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
     12258                                                                                                                          
select  count(*) from my_test_table where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
     12258                                                                                                                          
REM  The number of rows with "owner='SYSTEM'"  is actually less than 1% of the total row count !
REM  ie, 12,258 out of 1,368,063


REM  Let's see the Execution Plan that Oracle generates for the query
set autotrace traceonly
select owner, object_name, object_type, created from MY_TEST_TABLE where owner = 'SYSTEM';

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 965484217                                                                                                          
                                                                                                                                    
-----------------------------------------------------------------------------------                                                 
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                                 
-----------------------------------------------------------------------------------                                                 
|   0 | SELECT STATEMENT  |               | 12258 |   610K|  3527   (2)| 00:00:43 |                                                 
|*  1 |  TABLE ACCESS FULL| MY_TEST_TABLE | 12258 |   610K|  3527   (2)| 00:00:43 |                                                 
-----------------------------------------------------------------------------------                                                 
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OWNER"='SYSTEM')                                                                                                     


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
      18817  consistent gets                                                                                                        
       5373  physical reads                                                                                                         
          0  redo size                                                                                                              
     362774  bytes sent via SQL*Net to client                                                                                       
        514  bytes received via SQL*Net from client                                                                                 
          4  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
      12258  rows processed                                                                                                         

set autotrace off

REM That was 18,817 consistent gets for 12,258 rows, using a FullTableScan
REM  some of the consistent gets were for the parse
REM   (and some of the 'consistent gets' were re-reads of the same blocks because of the
REM    arraysize of 5,000 being less than the number of rows, resulting in 3 additional SQL*Net round trips)
REM Surprised that Oracle prefers a FullTableScan ?
REM That is preferred because of the high CLUSTERING_FACTOR
REM which indicates to Oracle that it will have to keep skipping to a different Table Block
REM for each next ROWID fetched from the index.
REM This means that Oracle estimates it will have to make very many SingleBlockReadCalls
REM to read the rows from the Table

REM  Let's see the results if I force the Index
set autotrace traceonly
select /*+ INDEX (M MY_TEST_TABLE_NDX_1) */ owner, object_name, object_type, created from MY_TEST_TABLE M where owner = 'SYSTEM';

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 519844067                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------------                                 
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                 
---------------------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT            |                     | 12258 |   610K| 12138   (1)| 00:02:26 |                                 
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TEST_TABLE       | 12258 |   610K| 12138   (1)| 00:02:26 |                                 
|*  2 |   INDEX RANGE SCAN          | MY_TEST_TABLE_NDX_1 | 12258 |       |    90   (0)| 00:00:02 |                                 
---------------------------------------------------------------------------------------------------                                 
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("OWNER"='SYSTEM')                                                                                                     


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
      10638  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
     109228  bytes sent via SQL*Net to client                                                                                       
        514  bytes received via SQL*Net from client                                                                                 
          4  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
      12258  rows processed                                                                                                         

set autotrace off

REM That was 10,638 consistent gets for 12,258 rows, using an IndexRangeScan
REM Oracle had preferred the FullTableScan (18,817 consistent gets) because
REM that would be achieved using MultiBlockReadCalls
REM While the retrieval using the Index would require SingleBlockReadCalls


REM REM REM ############### DELETE approximately 20% (282,528) of the rows in the table

connect hemant/hemant
Connected.
delete MY_TEST_TABLE where object_id < 11000;
commit;
-- rem need to do a FullTableScan to effect delayed block cleanout
select /*+ FULL (dc) */ count(*) from MY_TEST_TABLE dc;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   1085535                                                                                                                          

exec dbms_stats.gather_table_stats(user,'MY_TEST_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER, OBJECT_NAME SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

select table_name, num_rows, blocks from user_tables order by 1;

TABLE_NAME                       NUM_ROWS     BLOCKS                                                                                
------------------------------ ---------- ----------                                                                                
MY_TEST_TABLE                     1085535      19277                                                                                
SOURCE_TABLE                      1368063      18967                                                                                
select index_name, num_rows, distinct_keys, leaf_blocks, clustering_factor from user_indexes order by 1;

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR                                               
------------------------------ ---------- ------------- ----------- -----------------                                               
MY_TEST_TABLE_NDX_1               1085535         39460        8372           1072489                                               
REM  I still have a very high CLUSTERING_FACTOR

select  count(*) from source_table where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
     12258                                                                                                                          
select  count(*) from my_test_table where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
       135                                                                                                                          
REM  The number of rows with "owner='SYSTEM'"  is much lesser now !
REM  ie, 135 out of 1,085,535

REM  Let's see the Execution Plan that Oracle generates for the query
set autotrace traceonly
select owner, object_name, object_type, created from MY_TEST_TABLE where owner = 'SYSTEM';

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 519844067                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------------                                 
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                 
---------------------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT            |                     |   135 |  7020 |   138   (0)| 00:00:02 |                                 
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TEST_TABLE       |   135 |  7020 |   138   (0)| 00:00:02 |                                 
|*  2 |   INDEX RANGE SCAN          | MY_TEST_TABLE_NDX_1 |   135 |       |     4   (0)| 00:00:01 |                                 
---------------------------------------------------------------------------------------------------                                 
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("OWNER"='SYSTEM')                                                                                                     


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

set autotrace off

REM That was 214 consistent gets for 135 rows, using an IndexRangeScan
REM Aah !  Now, Oracle prefers the IndexRangeScan because it estimates
REM much lesser SingleBlockReadCalls to read the only 135 rows from the table.
REM This inspite of the high CLUSTERING_FACTOR
REM Oracle accounts for both expected Cardinality and Clustering when selecting an execution plan.


REM REM REM ############### TRUNCATE and Re-INSERT rows

connect hemant/hemant
Connected.
truncate table MY_TEST_TABLE reuse storage;
insert into MY_TEST_TABLE select * from SOURCE_TABLE ;
commit;
-- rem need to do a FullTableScan to effect delayed block cleanout
select /*+ FULL (dc) */ count(*) from MY_TEST_TABLE dc;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   1368063                                                                                                                          

exec dbms_stats.gather_table_stats(user,'MY_TEST_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER, OBJECT_NAME SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

select table_name, num_rows, blocks from user_tables order by 1;

TABLE_NAME                       NUM_ROWS     BLOCKS                                                                                
------------------------------ ---------- ----------                                                                                
MY_TEST_TABLE                     1368063      18765                                                                                
SOURCE_TABLE                      1368063      18967                                                                                
select index_name, num_rows, distinct_keys, leaf_blocks, clustering_factor from user_indexes order by 1;

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR                                               
------------------------------ ---------- ------------- ----------- -----------------                                               
MY_TEST_TABLE_NDX_1               1368063         49232        9778           1343029                                               

select  count(*) from source_table where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
     12258                                                                                                                          

set autotrace traceonly
select owner, object_name, object_type, created from MY_TEST_TABLE where owner = 'SYSTEM';

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 965484217                                                                                                          
                                                                                                                                    
-----------------------------------------------------------------------------------                                                 
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                                 
-----------------------------------------------------------------------------------                                                 
|   0 | SELECT STATEMENT  |               | 12258 |   610K|  3434   (2)| 00:00:42 |                                                 
|*  1 |  TABLE ACCESS FULL| MY_TEST_TABLE | 12258 |   610K|  3434   (2)| 00:00:42 |                                                 
-----------------------------------------------------------------------------------                                                 
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OWNER"='SYSTEM')                                                                                                     


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
      18779  consistent gets                                                                                                        
       3797  physical reads                                                                                                         
          0  redo size                                                                                                              
     362796  bytes sent via SQL*Net to client                                                                                       
        514  bytes received via SQL*Net from client                                                                                 
          4  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
      12258  rows processed                                                                                                         

set autotrace off


REM REM REM ##########################
REM What can I do to improve the CLUSTERING_FACTOR ??
REM REM REM ############### Reorder the rows !!!

connect hemant/hemant
Connected.
drop table TEMP_TBL;
drop table TEMP_TBL
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


create table TEMP_TBL  as select * from MY_TEST_TABLE where 1=2;
alter table TEMP_TBL nologging;
insert /*+ APPEND */ into TEMP_TBL select * from MY_TEST_TABLE  order by OWNER, OBJECT_NAME;
commit;
truncate table MY_TEST_TABLE;
insert into MY_TEST_TABLE select * from TEMP_TBL  order by OWNER, OBJECT_NAME;
commit;
-- rem need to do a FullTableScan to effect delayed block cleanout
select /*+ FULL (dc) */ count(*) from MY_TEST_TABLE dc;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   1368063                                                                                                                          

exec dbms_stats.gather_table_stats(user,'MY_TEST_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER, OBJECT_NAME SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

select table_name, num_rows, blocks from user_tables order by 1;

TABLE_NAME                       NUM_ROWS     BLOCKS                                                                                
------------------------------ ---------- ----------                                                                                
MY_TEST_TABLE                     1368063      19277                                                                                
SOURCE_TABLE                      1368063      18967                                                                                
TEMP_TBL                                                                                                                            
select index_name, num_rows, distinct_keys, leaf_blocks, clustering_factor from user_indexes order by 1;

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR                                               
------------------------------ ---------- ------------- ----------- -----------------                                               
MY_TEST_TABLE_NDX_1               1368063         49232        7186             22498                                               
REM Note how the CLUSTERING_FACTOR is now down to 22,498 from the 1,343,029 on the first run !

select  count(*) from source_table where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
     12258                                                                                                                          
select  count(*) from MY_TEST_TABLE where owner = 'SYSTEM';

  COUNT(*)                                                                                                                          
----------                                                                                                                          
     12258                                                                                                                          
REM  The number of rows with "owner='SYSTEM'"  is the same in as in the first test.
REM  ie, 12,258 out of 1,368,063

set autotrace traceonly
select owner, object_name, object_type, created from MY_TEST_TABLE where owner = 'SYSTEM';

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 519844067                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------------                                 
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                 
---------------------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT            |                     | 12258 |   610K|   270   (1)| 00:00:04 |                                 
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TEST_TABLE       | 12258 |   610K|   270   (1)| 00:00:04 |                                 
|*  2 |   INDEX RANGE SCAN          | MY_TEST_TABLE_NDX_1 | 12258 |       |    67   (0)| 00:00:01 |                                 
---------------------------------------------------------------------------------------------------                                 
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("OWNER"='SYSTEM')                                                                                                     


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        273  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
      99453  bytes sent via SQL*Net to client                                                                                       
        514  bytes received via SQL*Net from client                                                                                 
          4  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
      12258  rows processed                                                                                                         

set autotrace off

REM That was only 273 consistent gets for 12,235 rows, using an IndexRangeScan
REM With the table's rows *physically re-ordered*
REM Oracle now had to do only 273 block gets
REM when the forced IndexRangeScan in the badly clustered table resulted in 10,638 block gets !

REM The *same* data, after being re-ordered
REM is down from 106,38 block gets to 273 block gets
REM with the *same* Execution Plan !!

drop table TEMP_TBL;

spool off 

RDBMS Software, Database and Instance

This was a quick note I wrote for a friend and colleague a few weeks ago :
==========================================================================

Many a times we hear or read statements like "I installed the Oracle Database" OR "I started (or shutdown) the Database".

I'd like to explain the 3 terms "Software" , "Database" and "Instance" and a few additional terms.


1. Software :
1a. What is *installed* is the Oracle RDBMS Software (which may also be called the Oracle Database Software). Alternatively it is called "Oracle Binaries" or "Oracle Executables". This is the software (binary files, library files, make files, default configuration/parameter files) that are installed using the Oracle Universal Installer. The OUI extracts or copies files from the Installation Media (CD/DVD/DownloadedImage) and may relink them before placing them under the ORACLE_HOME.

1b. Here, I will also refer to ORACLE_HOME . This is an important concept. The ORACLE_HOME is where the Software is Installed. Database Instance Parameter files may also be placed here, although they are generally placed under their own admin/ directory under ORACLE_BASE. The OFA "standard" (where "OFA" is variously called "Oracle Flexible Architecture" or , as in "Oracle OFA" "Optimal Flexible Architecture") in earlier versions has never been mandatory but a good guideline. ORACLE_BASE is the "parent" directory which can hold 1 or more ORACLE_HOMEs. Each ORACLE_HOME holds a different Version/Release/PatchSetLevel of the Oracle Database Software. For each database, the admin directories would be under ORACLE_BASE, not under ORACLE_HOME.
However, the OFA has been documented differently in the 10gR2 Installation Guides. See, for example, the appendix on OFA in the Installation Guide for Windows or the appendix on OFA in the Installation Guide for Linux
This provides for multiple installations of the Release (e.g. 10.2.0) under one directory but as separate "db_n" subdirectories. Thus, the ORACLE_BASE is now what used to be the ORACLE_HOME in earlier versions, and the ORACLE_HOME is a sub-directory of that. Personally, I prefer the older "architecture".

1c. Parameter Files. An installation of the Oracle Software also includes default parameter files that can be used as templates for the init.ora and the listener.ora and tnsnames.ora and other configuration files. If, in the process of installing the Software, you also choose to run the NetConfigurationAssistant, it will configure the listener.ora and tnsnames.ora files with default parameters (or the parameters you specify when running the assistant). If you had also chosen to "Create the Database", the DatabaseConfigurationAssistant (called "dbca"), this tool would create the instance parameter file (init.ora and/or spfile.ora).

When someone says "I installed the Oracle Database" ask him if he
a. Installed the Software
b. and also Created a Database (configuring the Database Instance Parameter files through the "dbca" at the same time)
c. and also Configured the Network

He may have actually done only "a" (which is what I would recommend as a standalone exercise) OR also done "b" and "c" with "a", in which he case did more than "install". He also did "create" and "configure".


2. Database :
The Database is the set of files which actually hold the data. This consists of the Control Files, the Database Files (corresponding to the Tablespaces) and the Redo Log Files (and, optionally, ArchiveLogs of the Redo Logs). A Database is *created*, not installed.

When someone says "Oracle was already installed, so I just installed the database", he should actually be saying "Using the existing installed Software, I created the Database" (and he may, or may not have, started the Database Instance yet !).
When someone says "I shutdown the database", he should actually be saying "I shutdown the Database Instance".


3. Instance (or Database Instance) :
The Database Instance is the set of *processes* (a single process, multi-threaded, in Windows) that are "running" on the Operating System. These include the Background Processes and the Server Processes. The processes share a common "SGA" ("System Global Area") and communicate with each other through IPC. Thus, Oracle on Unix and Linux is a Multi-Processing architecture (while on Windows it is Mulit-Threaded). Each client that connects to the Database Instance gets a Server Process forked for it (unless the Database Instance is configured to run Shared Servers and the Client requests a Shared Server connection [Shared Servers were also known as "MultiThreaded Servers" in earlier vresions].

When someone says "I am unable to connect to the database" , he should actually be saying "I [or 'My Client program [SQLPlus ?]'] is unable to connect to the Database Instance".

When the DBA says "the Database has crashed", he should actually be saying "The Database Instance has crashed [or terminated with error]".

When the IT Manager says "Startup the Database on the second node of the RAC Cluster", he should actually be saying "Startup the [second] Database Instance, to run on the second node of the Oracle RAC [Cluster]".


4. Multiple Database Instances, Multiple Databases :
On a particular storage set and on a particular server, you can also have more than 1 Database. You can have 2 or 3 or 4 Databases, whereby Instances are started from the same ORACLE_HOME or from separate ORACLE_HOMEs (which separate ORACLE_HOMEs may or may not be the same Version, Release and Patchset of the Oracle [RDBMS] Software.


Oracle OPS (Oracle Parallel Server) and RAC (Real Application Cluster) consists of two or more Database Instances on two more nodes [although there are ways to have RAC Instances running on the same node] accessing the same Database [I prefer "the same database" instead of "a common database" or a "shared database", although I would be comfortable with "on shared disks" ]. Similarly, the shared storage can host more than 1 Database, of which none or some or all are RAC Databases.

In an OS Cluster , you can have one more Databases configured to run in
a. Failover :- the surviving node takes over (owns) the IP address of a failed node and starts the Database Instance
b. RAC : both (or all nodes) run Database Instances accessing the same Database(s).


5. Connectivity :
Client Software not installed on the same Host (server) as the Database Server software can connect to the Database Instance by first sending a connection request to the Listener. This configuration definition is in the tnsnames.ora file on the client which identifies the Host, the Listener Port and the Database Instance SID or Service Name, and in the listener.ora file on the server which identifies the ORACLE_HOME, the Instance SID or Service Name. The Listener does no Authentication (other than verifying the requested SID or Service Name) and "forks" a Server Process and hands over the Client connection to the Server Process. It is the Server process that then does the Database Authentication. After that, the Listener has no hand in the communication between the Server and Client. The Listener can be shutdown without affecting existing connections (although, of course, this will prevent new connection requests from succeeding). In a Shared Server architecture, the Listener does the handover to the Dispatcher which then redirects the Client to an available Shared Server process.
Note that it should be clear that clients (e.g. 'sqlplus') running from the same ORACLE_HOME installation can connect to a running Database Instance even when the Listener has been shut down.


6. Backup and Recovery :
Transactions are captured in the Online Redo Logs (which are archived as ArchiveLogs). Thus, Backup and Recovery consists of the Database and the ArchiveLogs (and, additional, configuration files). In an RAC environment, each Instance has it's own Redo Thread. Backup and Recovery must capture and restore *all* the Redo threads of the Databases, else the database is not recoverable.

EXPLAIN PLAN on DDLs

Randolf Geist has posted the results of his experimentation with EXPLAIN PLAN on some DDLs : Oracle related stuff: EXPLAIN PLAN on DDLs

Restore or Create Controlfile

This forum thread is a discussion about the scenario where ALL Controlfiles are lost, but the rest of the database is intact. Since the Datafiles and Online Redo Log files are intact, Complete Recovery is possible. The question is "should you restore the Controlfiles from a Binary Backup OR should you recreate the Controlfiles ?". The next question would be "If you don't have a Trace Backup of your Controlfile, what would you do ?".

We discuss the issues and options in the thread, with pointers to other discussions and the documentation as well.

Full Table Scan , Arraysize etc

Having received some questions on my last blog post about array processing, :

Can FTS(retriving more than 40%data of out some 20milion )  be optimized in the with all the three things applying  at one time ?

1.increase the array size. ( I fell only for static sqls , not for the peoplesoft application engine programs)

2.put the optimum (increase) value for DB_FILE_MULTIPLE_READ_COUNT .

3. put  parallel degree at  Query level.

4. Defragment the table if there is any fragmentation.



I can see from documentation  below information.

When Oracle performs a full table scan, the blocks are read sequentially.

The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

Using multiblock reads means a full table scan can be performed very efficiently.

Each block is read only once.(Not able to make out this).

I hope in single block read also each block will be reading only once . 

this was my response :

1. ARRAYSIZE is a client-side specification.
Thus, "SET ARRAYSIZE" is specified by SQLPlus and determines how many rows will SQLPlus ask the Oracle Server process for at each round-trip. If your application is not SQLPlus but some other client, you have to check the ARRAYSIZE specification for that client. (eg TOAD, I think, in the current version, uses 500 as a default ?).

However, the number of SQLNet round trips would also be impacted by PacketSize. Default EtherNet packets (not using JumboFrames) are 1500 bytes. SQLNet SDU and TDU sizes are 2K. If you fetch more than 2K (say 3K at each array fetch) then SQLNet breaks it up into 2 SQLNet Packets. However, EtherNet would break it up into 3 Packets (the first 2000 as two packets of 1500 and 500 and the next 1000 as one packet). Of course, you have to factor in packet header overheads in both SQLNet and Ethernet. SDU and TDU are configurable but need to be set in both the tnsnames.ora and listener.ora

One issue with ARRAYSIZE is the the client also has a limited BufferSize. If the RowSize is very large then a large ARRAYSIZE results in BufferOverflow.
SQLPlus clients used to have a 32K buffer (probably still have on 32bit SQLPlus ?). Therefore SQLPlus queries would sometimes fail with a large arraysize because RowSize X ArraySize would exceed the buffer.
Again, this depends on what client software your application uses.

Also, see the other blog posting where I also show the impact of PAGESIZE and LINESIZE in SQLPlus.

Another dimension is the number of columns. Ask if you really need to SELECT * or you need SELECT CO1, COL2. The latter reduces the fetched row size significantly and, therefore, the number of rows that can fit into 1 packet is much larger , allowing a larger ARRAYSIZE.

Finally, if your operation is INSERT INTO table_1 SELECT * from table_2 then ARRAYSIZE does not matter at all. As I said, ARRAYSIZE is a client side parameter . If you are running an INSERT, then everything happens in the server. As you are talking of 40% of 20million rows, I presume that your operation is an INSERT operation. ARRAYSIZE doesn't matter there. (Also, if you are inserting across a DBLink, Oracle ignores ARRAYSIZE [I think I have blog posting on that as well]).


2. Yes, DB_FILE_MULTIBLOCK_READ_COUNT will help. The "autotuned" D_F_MB_R_C (when it is NOT specified in the system or session level parameters) works well only in 10.2.0.4.
Note that extents which are smaller than 1MB will break up your multiblock reads into smaller reads anyway. Also, for blocks present in the db_cache, the read from the extents containing those blocks would also be "broken" up.

3. Yes, Parallel as a Hint is always better than Parallel at the Table level. Better control !

4. "Fragmentation" is difficult to define. However, if your table has undergone a large purge recently it may make sense to rebuild the table.


What is the documentation (URL !!!) that says "Each block is read only once" ?

A block could contain 10 to 1000 rows. If you are doing a FullTableScan and Oracle needs all the rows, Oracle needs to "read" the block only once to get all the 1000 rows.
If you are doing an IndexRangeScan and the table is badly clustered, the IndexRangeScan may "visit" the block 1000 times , effectively "reading" it 1000 times.
For example, suppose the block contains the rows with the key values "A", "H", "F", "L", "Z". An IndexRange Scan that is doing an ordered read for all the values between say "A" and "M" would visit the block for "A", then go to another block for "B" and somewhere else for "C" …. and return to this same block again for "F", another block for "G", back here for "H" ….. You get the picture now ? In this case, a FullTableScan *may* be better , depending on how many Blocks it has to read upto the HWM versus how many key values and how many Index Blocks and how many Table Block "visits" occur. If this block contained values "A", "B", "C", "D", "E", then it has a good Clustering Factor and Oracle has to visit this block only once to read all 5 rows (if the Index has now key values between "A" and "B" and betweeen "B" and "C" etc).

Array Processing, SQL*Net RoundTrips and consistent gets

When fetching large datasets (even if the SQLPlus Client is on the *same* server as the Database), it is important to use the right ARRAYSIZE.

Here I demonstrate the impact of ARRAYSIZE on
a. Total volume of bytes transferred from the Server to the Client
b. Number of SQL*Net round-trips
c. Number of 'consistent gets' (and, even, a slightly higher CPU usage for the higher logical reads, but which isn't presented by SET AUTOTRACE )

I shall be querying a table with 1.6million rows. (This has the same structure and multiple copies of the contents of DBA_OBJECTS). I avoid using the index -- I am fetching ALL rows and ALL columns.

ora10204>sqlplus hemant/hemant

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 23:26:25 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> REM Start with the default arrasize
SQL> show arraysize
arraysize 15
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> select /*+ FULL (st) */ * from source_table st;

1620512 rows selected.


Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
128823  consistent gets
11211  physical reads
 116  redo size
83292501  bytes sent via SQL*Net to client
1188866  bytes received via SQL*Net from client
108036  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
1620512  rows processed

SQL> set timing on
SQL> select /*+ FULL (st) */ * from source_table st;

1620512 rows selected.

Elapsed: 00:00:42.22

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
128822  consistent gets
11436  physical reads
   0  redo size
83292501  bytes sent via SQL*Net to client
1188866  bytes received via SQL*Net from client
108036  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
1620512  rows processed

SQL>   

This was 83.292million bytes sent by the server to my SQLPlus client via SQLNet in 108,036 round-trips. The query did 128,822 'consistent gets'.

Now, I switch to an ARRAYSIZE of 100. Thus, the SQLPlus client will "ask" Oracle for 100 rows at each fetch.

SQL> REM Now I set a larger arraysize
SQL> SET ARRAYSIZE 100
SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:00:16.73

Statistics
----------------------------------------------------------
    0  recursive calls
    0  db block gets
38238  consistent gets
11434  physical reads
    0  redo size
66487794  bytes sent via SQL*Net to client
178747  bytes received via SQL*Net from client
16207  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
1620512  rows processed

SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:00:16.68

Statistics
----------------------------------------------------------
    0  recursive calls
    0  db block gets
38238  consistent gets
11414  physical reads
    0  redo size
66487794  bytes sent via SQL*Net to client
178747  bytes received via SQL*Net from client
16207  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
1620512  rows processed

SQL>  

Now, the server has to send 66.488million bytes to me in 16,207 round-trips. Since my ARRAYSIZE has increased 7 fold, the number of round-trips has dropped to almost 1/7th. More rows are being sent by the DB server in each SQL*Net packet. Even 'consistent gets' has dropped from 128,822 to 38,238. This is because fewer Fetch calls (which we would have seen in a tkprof of trace file with SQL_TRACE enabled) have had to be executed, and the number of blocks being re-read has declined. Execution time has also improved.

What about the other extreme ? An ARRAYSIZE of 2 ?

SQL> REM Now I set a very small arraysize
SQL> SET ARRAYSIZE 2
SQL> select /*+ FULL (st) */ * from source_table st
2  /

1620512 rows selected.

Elapsed: 00:03:10.08

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
821428  consistent gets
 11721  physical reads
     0  redo size
211798944  bytes sent via SQL*Net to client
8913297  bytes received via SQL*Net from client
810257  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
1620512  rows processed

SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:03:46.49

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
821428  consistent gets
 11466  physical reads
     0  redo size
211798944  bytes sent via SQL*Net to client
8913297  bytes received via SQL*Net from client
810257  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
1620512  rows processed


SQL> 

With a very small ARRAYSIZE, the server sent 211.799MB in 810,257 round-trips ! The number of 'consistent gets' has jumped to 821,428 !

So we have :

ARRAYSIZE     MB sent     RoundTrips     consistent    query
                                       gets          time
2            211.799     810,257        821,428       190sec
15            83.292     108,036        128,822        42sec
100           66.488      16,207        38,238         17sec         

For some other tests with PAGESIZE and LINESIZE, see my previous post "Tuning Very Large SELECTs in SQLPlus"

MIN/MAX Queries, Execution Plans and COST

Note : I had earlier posted this note with a test case where the Indexed columns were not defined as
NOT NULL (ie, were NULLable) inspite of not really having any NULL values.  That resulted in a
different execution plan when querying for both MIN and MAX.  I withdrew that post when I realised
my mistake but, being busy through the day, didn't have time to republish it with the corrected test
case where the columns are NOT NULL.
I am including both NULLable and NOT NULL scenarios in this test case.

This test shows how the performance of queries that attempt to retrieve both MIN and MAX values together can be much poorer than two seperate queries for MIN and MAX. The test then goes on to demonstrate 3 methods to handle the performance issue : a) UNION of the two queries, b) Inline Views and c) a Materialized View.
I also demonstrate the difference between the Indexed column being NULLable and NOT NULL.

SQL> 
SQL> REM  ===========================================================================
SQL> REM  Create the table TEST_MINMAX
SQL> REM  This is built from SOURCE_TABLE, which is a multiplied copy of DBA_OBJECTS
SQL> REM  I will be loading the table with 1.6million rows
SQL> REM  ===========================================================================
SQL> REM  ===========================================================================
SQL> 
SQL> desc source_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> select count(*) from source_table;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   1620512                                                                                                                          
SQL> 
SQL> drop table test_minmax ;
SQL> create table test_minmax as select * from source_table where 1=2;
SQL> alter table test_minmax nologging;
SQL> insert /*+ APPEND */ into test_minmax select * from source_table where object_id is not null;
SQL> 
SQL> create index test_minmax_objectid_ndx on test_minmax(object_id) nologging;
SQL> create index test_minmax_created_ndx on test_minmax(created) nologging;
SQL> 
SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_MINMAX',estimate_percent=>NULL,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
SQL> 
SQL> select count(*) from test_minmax;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   1620512                                                                                                                          
SQL> select blocks, num_rows from user_tables where table_name = 'TEST_MINMAX';

    BLOCKS   NUM_ROWS                                                                                                               
---------- ----------                                                                                                               
     22448    1620512                                                                                                               
SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'TEST_MINMAX_OBJECTID_NDX';

LEAF_BLOCKS   NUM_ROWS                                                                                                              
----------- ----------                                                                                                              
       3572    1620512                                                                                                              
SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'TEST_MINMAX_CREATED_NDX';

LEAF_BLOCKS   NUM_ROWS                                                                                                              
----------- ----------                                                                                                              
       4299    1620512                                                                                                              
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  Get the query execution plan for MIN queries
SQL> REM  ###########################################################################
SQL> 
SQL> select min(object_id) from test_minmax
  2  /

MIN(OBJECT_ID)                                                                                                                      
--------------                                                                                                                      
             2                                                                                                                      
SQL> select min(created) from test_minmax
  2  /

MIN(CREAT                                                                                                                           
---------                                                                                                                           
12-MAR-08                                                                                                                           
SQL> set autotrace on
SQL> select min(object_id) from test_minmax
  2  /

MIN(OBJECT_ID)                                                                                                                      
--------------                                                                                                                      
             2                                                                                                                      

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1603217912                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------------------                             
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |                             
-------------------------------------------------------------------------------------------------------                             
|   0 | SELECT STATEMENT           |                          |     1 |     5 |  4109   (2)| 00:00:50 |                             
|   1 |  SORT AGGREGATE            |                          |     1 |     5 |            |          |                             
|   2 |   INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|            |          |                             
-------------------------------------------------------------------------------------------------------                             


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

SQL> select min(created) from test_minmax
  2  /

MIN(CREAT                                                                                                                           
---------                                                                                                                           
12-MAR-08                                                                                                                           

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 201212520                                                                                                          
                                                                                                                                    
------------------------------------------------------------------------------------------------------                              
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |                              
------------------------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT           |                         |     1 |     8 |  4123   (3)| 00:00:50 |                              
|   1 |  SORT AGGREGATE            |                         |     1 |     8 |            |          |                              
|   2 |   INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX |  1620K|    12M|            |          |                              
------------------------------------------------------------------------------------------------------                              


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

SQL> set autotrace off
SQL> 
SQL> REM   --> that was only 3 Block Gets for both queries.
SQL> REM   --> note that the COST was 4,109 and 4,123 !!
SQL> REM   -->   -- although Oracle really had to read only 3 Blocks
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  Get the query execution plan for MAX queries
SQL> REM  ###########################################################################
SQL> 
SQL> select max(object_id) from test_minmax
  2  /

MAX(OBJECT_ID)                                                                                                                      
--------------                                                                                                                      
         59122                                                                                                                      
SQL> select max(created) from test_minmax
  2  /

MAX(CREAT                                                                                                                           
---------                                                                                                                           
02-NOV-08                                                                                                                           
SQL> set autotrace on
SQL> select max(object_id) from test_minmax
  2  /

MAX(OBJECT_ID)                                                                                                                      
--------------                                                                                                                      
         59122                                                                                                                      

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1603217912                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------------------                             
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |                             
-------------------------------------------------------------------------------------------------------                             
|   0 | SELECT STATEMENT           |                          |     1 |     5 |  4109   (2)| 00:00:50 |                             
|   1 |  SORT AGGREGATE            |                          |     1 |     5 |            |          |                             
|   2 |   INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|            |          |                             
-------------------------------------------------------------------------------------------------------                             


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

SQL> select max(created) from test_minmax
  2  /

MAX(CREAT                                                                                                                           
---------                                                                                                                           
02-NOV-08                                                                                                                           

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 201212520                                                                                                          
                                                                                                                                    
------------------------------------------------------------------------------------------------------                              
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |                              
------------------------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT           |                         |     1 |     8 |  4123   (3)| 00:00:50 |                              
|   1 |  SORT AGGREGATE            |                         |     1 |     8 |            |          |                              
|   2 |   INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX |  1620K|    12M|            |          |                              
------------------------------------------------------------------------------------------------------                              


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

SQL> set autotrace off
SQL> 
SQL> REM   --> Again, only 3 Block Gets for both queries
SQL> REM   --> Also, the COST is very high, but the real effort is very low
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  Get Min and Max together.  NOW we will be surprised !
SQL> REM  ###########################################################################
SQL> 
SQL> select min(object_id), max(object_id) from test_minmax
  2  /

MIN(OBJECT_ID) MAX(OBJECT_ID)                                                                                                       
-------------- --------------                                                                                                       
             2          59122                                                                                                       
SQL> select min(created), max(created) from test_minmax
  2  /

MIN(CREAT MAX(CREAT                                                                                                                 
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 
SQL> set autotrace on
SQL> select min(object_id), max(object_id) from test_minmax
  2  /

MIN(OBJECT_ID) MAX(OBJECT_ID)                                                                                                       
-------------- --------------                                                                                                       
             2          59122                                                                                                       

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1671342682                                                                                                         
                                                                                                                                    
----------------------------------------------------------------------------------                                                  
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                  
----------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT   |             |     1 |     5 |  4109   (2)| 00:00:50 |                                                  
|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |                                                  
|   2 |   TABLE ACCESS FULL| TEST_MINMAX |  1620K|  7912K|  4109   (2)| 00:00:50 |                                                  
----------------------------------------------------------------------------------                                                  


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

SQL> select min(created), max(created) from test_minmax
  2  /

MIN(CREAT MAX(CREAT  
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1671342682                                                                                                         
                                                                                                                                    
----------------------------------------------------------------------------------                                                  
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                  
----------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT   |             |     1 |     8 |  4123   (3)| 00:00:50 |                                                  
|   1 |  SORT AGGREGATE    |             |     1 |     8 |            |          |                                                  
|   2 |   TABLE ACCESS FULL| TEST_MINMAX |  1620K|    12M|  4123   (3)| 00:00:50 |                                                  
----------------------------------------------------------------------------------                                                  


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

SQL> set autotrace off
SQL> 
SQL> REM  --> That took  22,269  Block Gets  -- at the same "COST" of 4,109 or 4,123 !
SQL> REM  -->     For my 1.6million rows, Oracle prefers a FULL TABLE SCAN.
SQL> REM  --> BUT WAIT ! There's more.  See the AFTERNOTE section at the end of this round of testing
SQL> 
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  Try using a UNION
SQL> REM  ###########################################################################
SQL> 
SQL> select min(object_id) Min_object_id, NULL Max_object_id from test_minmax
  2  union
  3  select NULL Min_object_id, max(object_id) Max_object_id from test_minmax
  4  /

MIN_OBJECT_ID MAX_OBJECT_ID                                                                                                         
------------- -------------                                                                                                         
            2                                                                                                                       
                      59122                                                                                                         
SQL> select min(created) Min_date, NULL Max_date from test_minmax
  2  union
  3  select NULL Min_date, max(created) Max_date from test_minmax
  4  /

MIN_DATE  MAX_DATE                                                                                                                  
--------- ---------                                                                                                                 
12-MAR-08                                                                                                                           
          02-NOV-08                                                                                                                 
SQL> set autotrace on
SQL> select min(object_id) Min_object_id, NULL Max_object_id from test_minmax
  2  union
  3  select NULL Min_object_id, max(object_id) Max_object_id from test_minmax
  4  /

MIN_OBJECT_ID MAX_OBJECT_ID                                                                                                         
------------- -------------                                                                                                         
            2                                                                                                                       
                      59122                                                                                                         

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1629869728                                                                                                         
                                                                                                                                    
-----------------------------------------------------------------------------------------------------------------                   
| Id  | Operation                    | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                   
-----------------------------------------------------------------------------------------------------------------                   
|   0 | SELECT STATEMENT             |                          |     2 |    10 |       | 18482  (52)| 00:03:42 |                   
|   1 |  SORT UNIQUE                 |                          |     2 |    10 |    74M| 18482  (52)| 00:03:42 |                   
|   2 |   UNION-ALL                  |                          |       |       |       |            |          |                   
|   3 |    SORT AGGREGATE            |                          |     1 |     5 |       |  9241   (4)| 00:01:51 |                   
|   4 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|       |            |          |                   
|   5 |    SORT AGGREGATE            |                          |     1 |     5 |       |  9241   (4)| 00:01:51 |                   
|   6 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|       |            |          |                   
-----------------------------------------------------------------------------------------------------------------                   


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

SQL> select min(created) Min_date, NULL Max_date from test_minmax
  2  union
  3  select NULL Min_date, max(created) Max_date from test_minmax
  4  /

MIN_DATE  MAX_DATE                                                                                                                  
--------- ---------                                                                                                                 
12-MAR-08                                                                                                                           
          02-NOV-08                                                                                                                 

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 662419327                                                                                                          
                                                                                                                                    
----------------------------------------------------------------------------------------------------------------                    
| Id  | Operation                    | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                    
----------------------------------------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT             |                         |     2 |    16 |       | 20349  (52)| 00:04:05 |                    
|   1 |  SORT UNIQUE                 |                         |     2 |    16 |    99M| 20349  (52)| 00:04:05 |                    
|   2 |   UNION-ALL                  |                         |       |       |       |            |          |                    
|   3 |    SORT AGGREGATE            |                         |     1 |     8 |       | 10174   (4)| 00:02:03 |                    
|   4 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX |  1620K|    12M|       |            |          |                    
|   5 |    SORT AGGREGATE            |                         |     1 |     8 |       | 10174   (4)| 00:02:03 |                    
|   6 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX |  1620K|    12M|       |            |          |                    
----------------------------------------------------------------------------------------------------------------                    


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

SQL> set autotrace off
SQL> 
SQL> REM  --> Only 6 Block Gets, at *much higher* COSTS of 18,482 and 20,349 !
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  Try using Inline Views to get the two values together in single row
SQL> REM  ###########################################################################
SQL> 
SQL> select a.Min_object_id, b.Max_object_id from
  2  (select min(object_id) Min_object_id from test_minmax) a,
  3  (select max(object_id) Max_object_id from test_minmax) b
  4  /

MIN_OBJECT_ID MAX_OBJECT_ID                                                                                                         
------------- -------------                                                                                                         
            2         59122                                                                                                         
SQL> select a.Min_date, b.Max_date from
  2  (select min(created) Min_date from test_minmax) a,
  3  (select max(created) Max_date from test_minmax) b
  4  /

MIN_DATE  MAX_DATE                                                                                                                  
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 
SQL> set autotrace on
SQL> select a.Min_object_id, b.Max_object_id from
  2  (select min(object_id) Min_object_id from test_minmax) a,
  3  (select max(object_id) Max_object_id from test_minmax) b
  4  /

MIN_OBJECT_ID MAX_OBJECT_ID                                                                                                         
------------- -------------                                                                                                         
            2         59122                                                                                                         

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 796082343                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------------------                           
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |                           
---------------------------------------------------------------------------------------------------------                           
|   0 | SELECT STATEMENT             |                          |     1 |    26 |  8218   (2)| 00:01:39 |                           
|   1 |  NESTED LOOPS                |                          |     1 |    26 |  8218   (2)| 00:01:39 |                           
|   2 |   VIEW                       |                          |     1 |    13 |  4109   (2)| 00:00:50 |                           
|   3 |    SORT AGGREGATE            |                          |     1 |     5 |            |          |                           
|   4 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|            |          |                           
|   5 |   VIEW                       |                          |     1 |    13 |  4109   (2)| 00:00:50 |                           
|   6 |    SORT AGGREGATE            |                          |     1 |     5 |            |          |                           
|   7 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|            |          |                           
---------------------------------------------------------------------------------------------------------                           


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

SQL> select a.Min_date, b.Max_date from
  2  (select min(created) Min_date from test_minmax) a,
  3  (select max(created) Max_date from test_minmax) b
  4  /

MIN_DATE  MAX_DATE                                                                                                                  
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2568336276                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------------------------------------                            
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |                            
--------------------------------------------------------------------------------------------------------                            
|   0 | SELECT STATEMENT             |                         |     1 |    18 |  8246   (3)| 00:01:39 |                            
|   1 |  NESTED LOOPS                |                         |     1 |    18 |  8246   (3)| 00:01:39 |                            
|   2 |   VIEW                       |                         |     1 |     9 |  4123   (3)| 00:00:50 |                            
|   3 |    SORT AGGREGATE            |                         |     1 |     8 |            |          |                            
|   4 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX |  1620K|    12M|            |          |                            
|   5 |   VIEW                       |                         |     1 |     9 |  4123   (3)| 00:00:50 |                            
|   6 |    SORT AGGREGATE            |                         |     1 |     8 |            |          |                            
|   7 |     INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX |  1620K|    12M|            |          |                            
--------------------------------------------------------------------------------------------------------                            


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

SQL> set autotrace off
SQL> 
SQL> REM  --> Only 6 Block Gets, although the "COST" is still high.
SQL> REM  -->   By now, you would have gotten my hint. IGNORE the "COST" !
SQL> 
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  Using a Materialized View
SQL> REM  ###########################################################################
SQL> set autotrace off
SQL> drop materialized view test_minmax_values_mv;
SQL> create materialized view test_minmax_values_mv
  2  refresh on commit
  3  as
  4  select min(object_id) Min_Object_ID, max(object_id) Max_Object_ID, min(created) Min_Created, max(created) Max_Created
  5  from test_minmax
  6  /
SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_MINMAX_VALUES_MV',estimate_percent=>NULL,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
SQL> 
SQL> select Min_Object_ID, Max_Object_ID from test_minmax_values_mv
  2  /

MIN_OBJECT_ID MAX_OBJECT_ID                                                                                                         
------------- -------------                                                                                                         
            2         59122                                                                                                         
SQL> select Min_Created, Max_Created from test_minmax_values_mv
  2  /

MIN_CREAT MAX_CREAT                                                                                                                 
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 
SQL> set autotrace on
SQL> select Min_Object_ID, Max_Object_ID from test_minmax_values_mv
  2  /

MIN_OBJECT_ID MAX_OBJECT_ID                                                                                                         
------------- -------------                                                                                                         
            2         59122                                                                                                         

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1925756672                                                                                                         
                                                                                                                                    
----------------------------------------------------------------------------------------------                                      
| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |                                      
----------------------------------------------------------------------------------------------                                      
|   0 | SELECT STATEMENT     |                       |     1 |     8 |     2   (0)| 00:00:01 |                                      
|   1 |  MAT_VIEW ACCESS FULL| TEST_MINMAX_VALUES_MV |     1 |     8 |     2   (0)| 00:00:01 |  
----------------------------------------------------------------------------------------------                                      


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

SQL> select Min_Created, Max_Created from test_minmax_values_mv
  2  /

MIN_CREAT MAX_CREAT                                                                                                                 
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1925756672                                                                                                         
                                                                                                                                    
----------------------------------------------------------------------------------------------                                      
| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |                                      
----------------------------------------------------------------------------------------------                                      
|   0 | SELECT STATEMENT     |                       |     1 |    16 |     2   (0)| 00:00:01 |                                      
|   1 |  MAT_VIEW ACCESS FULL| TEST_MINMAX_VALUES_MV |     1 |    16 |     2   (0)| 00:00:01 |                                      
----------------------------------------------------------------------------------------------                                      


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

SQL> set autotrace off
SQL> 
SQL> REM  --> Only 3 Block Gets !
SQL> REM  --> Yeah. The FASTEST  MIN/MAX Query !
SQL> 
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM  AFTERNOTE : Re-visiting the case of FullTableScan on querying both MIN and MAX together
SQL> REM  ###########################################################################
SQL> 
SQL> REM  --> What happens if the columns were actually NOT NULLs
SQL> alter table test_minmax modify (object_id not null);
SQL> alter table test_minmax modify (created not null);
SQL> 
SQL> select min(object_id), max(object_id) from test_minmax
  2  /

MIN(OBJECT_ID) MAX(OBJECT_ID)                                                                                                       
-------------- --------------                                                                                                       
             2          59122                                                                                                       
SQL> select min(created), max(created) from test_minmax
  2  /

MIN(CREAT MAX(CREAT                                                                                                                 
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 
SQL> set autotrace on
SQL> select min(object_id), max(object_id) from test_minmax
  2  /

MIN(OBJECT_ID) MAX(OBJECT_ID)                                                                                                       
-------------- --------------                                                                                                       
             2          59122                                                                                                       

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2667923095                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------------------------------                                  
| Id  | Operation             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |                                  
--------------------------------------------------------------------------------------------------                                  
|   0 | SELECT STATEMENT      |                          |     1 |     5 |   676   (5)| 00:00:09 |                                  
|   1 |  SORT AGGREGATE       |                          |     1 |     5 |            |          |                                  
|   2 |   INDEX FAST FULL SCAN| TEST_MINMAX_OBJECTID_NDX |  1620K|  7912K|   676   (5)| 00:00:09 |                                  
--------------------------------------------------------------------------------------------------                                  


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

SQL> select min(created), max(created) from test_minmax
  2  /

MIN(CREAT MAX(CREAT                                                                                                                 
--------- ---------                                                                                                                 
12-MAR-08 02-NOV-08                                                                                                                 

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2239184443                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------------                                   
| Id  | Operation             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |                                   
-------------------------------------------------------------------------------------------------                                   
|   0 | SELECT STATEMENT      |                         |     1 |     8 |   807   (5)| 00:00:10 |                                   
|   1 |  SORT AGGREGATE       |                         |     1 |     8 |            |          |                                   
|   2 |   INDEX FAST FULL SCAN| TEST_MINMAX_CREATED_NDX |  1620K|    12M|   807   (5)| 00:00:10 |                                   
-------------------------------------------------------------------------------------------------                                   


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

SQL> set autotrace off
SQL> 
SQL> REM  --> When the column is NOT NULL, Oracle knows that it can do an Index Fast Full Scan
SQL> REM  --> Yet, this requires 3,592 and 4,324 block gets
SQL> REM  --> That is still much higher than the 3 and 6 block gets with the alternate methods
SQL> 
SQL> REM  ===========================================================================
SQL> REM  ***************************************************************************
SQL> REM REM REM REM
SQL> REM  ###########################################################################
SQL> REM What have we learnt ?
SQL> REM   1.  Min/Max Queries CAN be improved using UNIONs, Inline Views or Materialized Views
SQL> REM   2.  COST isn't always a reliable indicator
SQL> REM   3.  Whether a column is NULLable or NOT NULL makes a difference to the Optimizer
SQL> 
SQL>