Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Obsolete Database Parameters

by Jeff Hunter, Sr. Database Administrator

With each new release of the database, Oracle will always add and remove certain database parameters found in the init.ora file. This month, I will show several views provided by Oracle that are useful for detecting obsolete parameters.

Oracle provides the view V$OBSOLETE_PARAMETER to display all obsolete and underscored parameters. This view contains the name of the parameter and a flag ISSPECIFIED indicating if the parameter has actually been set in the init.ora file. Keep in mind that Oracle has not really retired all of the parameters contained in V$OBSOLETE_PARAMETER, some of them have been underscored. Underscored parameters are ones that Oracle wants to keep around, but does not want you to use them unless absolutely necessary. These underscored parameters are better know as hidden parameters. They are not documented and they all start with an _, like _lgwr_io_slaves in Oracle8i.

Use the following query to see the list of obsolete parameter names and if they have been specified in your init.ora:

   SQL> SELECT name, isspecified
      2 FROM v$obsolete_parameter;

To check if the parameter name (found in V$OBSOLETE_PARAMETER) was removed or underscored, you can query the X$KSPPO view. The value in the KSPPOFLAG column identifies if the parameter has been removed or underscored. Removed values are identified by a "1" and underscored values are identified by "2".

   SQL> SELECT kspponm, 
      2 DECODE(ksppoflg, 1,'Obsolete', 2, 'Underscored')
      3 FROM x$ksppo
      4 ORDER BY kspponm;

   KSPPONM                                                          DECODE(KSPP
   ---------------------------------------------------------------- -----------
   allow_partial_sn_results                                         Obsolete
   arch_io_slaves                                                   Underscored
   b_tree_bitmap_plans                                              Underscored
   backup_disk_io_slaves                                            Underscored
   cache_size_threshold                                             Obsolete
   cleanup_rollback_entries                                         Underscored
   close_cached_open_cursors                                        Underscored
   compatible_no_recovery                                           Underscored
   complex_view_merging                                             Underscored
   db_block_checkpoint_batch                                        Obsolete
   db_block_lru_extended_statistics                                 Obsolete
   db_block_lru_statistics                                          Obsolete
   db_file_simultaneous_writes                                      Obsolete
   delayed_logging_block_cleanouts                                  Obsolete
   discrete_transactions_enabled                                    Underscored
   distributed_lock_timeout                                         Underscored
   distributed_recovery_connection_hold_time                        Underscored
   fast_full_scan_enabled                                           Underscored
   freeze_DB_for_fast_instance_recovery                             Underscored
   gc_latches                                                       Underscored
   gc_lck_procs                                                     Underscored
   job_queue_keep_connections                                       Obsolete
   large_pool_min_alloc                                             Underscored
   lgwr_io_slaves                                                   Underscored
   lock_sga_areas                                                   Underscored
   log_archive_buffer_size                                          Underscored
   log_archive_buffers                                              Underscored
   log_block_checksum                                               Obsolete
   log_files                                                        Obsolete
   log_simultaneous_copies                                          Underscored
   log_small_entry_max_size                                         Obsolete
   max_transaction_branches                                         Underscored
   ogms_home                                                        Underscored
   ops_admin_group                                                  Obsolete
   optimizer_search_limit                                           Underscored
   parallel_default_max_instances                                   Underscored
   parallel_min_message_pool                                        Underscored
   parallel_server_idle_time                                        Underscored
   parallel_transaction_resource_timeout                            Obsolete
   push_join_predicate                                              Underscored
   row_cache_cursors                                                Underscored
   sequence_cache_entries                                           Obsolete
   sequence_cache_hash_buckets                                      Obsolete
   shared_pool_reserved_min_alloc                                   Underscored
   snapshot_refresh_interval                                        Obsolete
   snapshot_refresh_keep_connections                                Obsolete
   snapshot_refresh_processes                                       Obsolete
   sort_direct_writes                                               Obsolete
   sort_read_fac                                                    Obsolete
   sort_spacemap_size                                               Obsolete
   sort_write_buffer_size                                           Obsolete
   sort_write_buffers                                               Obsolete
   spin_count                                                       Underscored
   temporary_table_locks                                            Obsolete
   use_ism                                                          Underscored
   55 rows selected.

HINT: Whenever I install a new version of Oracle, one of the first tasks I perform is to generate a standard init.ora file for that version of the database. The init.ora file should be setup with all of the possible parameters in alphabetical order. Parameters that are not specifically used (i.e. that are not being explicitly set) may be commented out. Here is a query that can be run against a new database to build a default init.ora file that contains all possible parameters.

   SET pagesize 9000
   SET head OFF
   SET term OFF

   SPOOL initParameters.ora

   SELECT 
     DECODE(isdefault, 'TRUE', '# ') ||
     DECODE(isdefault, 'TRUE', RPAD(name,43), RPAD(name,45)) ||
     ' = ' || value
   FROM v$parameter
   ORDER BY name;

   SPOOL off

NOTE: Hidden parameters are not listed by the above SQL command. One of the more popular hidden parameters is "_trace_files_public = true". All hidden parameters should be at the top of the init.ora file.