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