Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Database Configuration:

The database was setup using a standard set of initialization parameters as shown below, which is a capture of the Oracle show parameter command with no options specified. As with the envshown in the section on server configuration, the parameter listing for an Oracle database is several pages long. However, the point here was to show that no special tricks and no undocumented parameters were used to make the database behave in anyway other than a normal Oracle database.

NAME                                 TYPE        VALUE  
------------------------------------ ----------- ------------------- 

aq_tm_processes                      integer     1
archive_lag_target                   integer     0

audit_file_dest                      string      ?/rdbms/audit
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
background_core_dump                 string      partial
background_dump_dest                 string     
backup_tape_io_slaves                boolean     FALSE
bitmap_merge_area_size               integer     1048576
blank_trimming                       boolean     FALSE
circuits                             integer     170
commit_point_strength                integer     1
compatible                           string
control_file_record_keep_time        integer     7
control_files                        string     

core_dump_dest                       string     
cpu_count                            integer     2
create_bitmap_area_size              integer     8388608
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE                          db_block_checking                    boolean     FALSE                      db_block_checksum                    boolean     TRUE                           db_block_size                        integer     16384                          db_cache_advice                      string      ON                             db_cache_size                        big integer 1073741824                      db_file_multiblock_read_count        integer     16                             db_files                             integer     200                            db_name                              string      dss                            db_writer_processes                  integer     1                              dblink_encrypt_login                 boolean     FALSE                          dg_broker_config_file1               string      ?/dbs/dr1@.dat                 dg_broker_config_file2               string      ?/dbs/dr2@.dat                 dg_broker_start                      boolean     FALSE                          disk_asynch_io                       boolean     TRUE                           dispatchers                          string      (PROTOCOL=TCP) (SERVICE=dssXDB) distributed_lock_timeout             integer     60                             dml_locks                            integer     748                            
drs_start                            boolean     FALSE                          enqueue_resources                    integer     968                            fast_start_io_target                 integer     0                     fast_start_mttr_target               integer     300                            fast_start_parallel_rollback         string      LOW                            file_mapping                         boolean     FALSE                          filesystemio_options                 string      none                           global_names                         boolean     FALSE                          hash_area_size                       integer     1048576                        hash_join_enabled                    boolean     TRUE                           hi_shared_memory_address             integer     0                              hs_autoregister                      boolean     TRUE                           instance_name                        string      dss                            java_max_sessionspace_size           integer     0                              java_pool_size                       big integer 33554432                       java_soft_sessionspace_limit         integer     0                              job_queue_processes                  integer     10                             large_pool_size                      big integer 16777216                       lock_sga                             boolean     FALSE                          
log_archive_start                    boolean     FALSE                          log_buffer                           integer     524288                         log_checkpoint_interval              integer     0                  log_checkpoint_timeout               integer     1800                           log_checkpoints_to_alert             boolean     FALSE                          log_parallelism                      integer     1                              logmnr_max_persistent_sessions       integer     1                              max_commit_propagation_delay         integer     700                            max_dispatchers                      integer     5                              max_dump_file_size                   string      UNLIMITED                      max_enabled_roles                    integer     30                             max_rollback_segments                integer     37                             max_shared_servers                   integer     20                             mts_circuits                         integer     170                            mts_dispatchers                      string      (PROTOCOL=TCP) (SERVICE=dssXDB) mts_max_dispatchers                  integer     5                              mts_max_servers                      integer     20                             mts_multiple_listeners               boolean     FALSE                          mts_servers                          integer     1                              
mts_service                          string      dss                            mts_sessions                         integer     165                            nls_language                         string      AMERICAN                       
nls_length_semantics                 string      BYTE                           nls_nchar_conv_excp                  string      FALSE                          nls_territory                        string      AMERICA                     object_cache_max_size_percent        integer     10                             object_cache_optimal_size            integer     102400                         olap_page_pool_size                  integer     33554432                       open_cursors                         integer     300                            open_links                           integer     4                              open_links_per_instance              integer     4                              optimizer_dynamic_sampling           integer     1                              optimizer_features_enable            string      9.2.0                          optimizer_index_caching
              integer     0                              optimizer_index_cost_adj integer     100                            optimizer_max_permutations           integer     2000                           optimizer_mode string      CHOOSE                         oracle_trace_collection_path         string      ?/otrace/admin/cdf             oracle_trace_collection_size         integer     5242880                        oracle_trace_enable                  boolean     FALSE                          oracle_trace_facility_name           string      oracled                        oracle_trace_facility_path           string      ?/otrace/admin/fdf             os_authent_prefix                    string      ops$              os_roles                             boolean     FALSE                          parallel_adaptive_multi_user         boolean     FALSE                          parallel_automatic_tuning            boolean     FALSE                          parallel_execution_message_size      integer     2148                           parallel_max_servers                 integer     5                              parallel_min_percent                 integer     0                              parallel_min_servers                 integer     0                              parallel_server                      boolean     FALSE                          parallel_server_instances            integer     1                              parallel_threads_per_cpu             integer     2                              partition_view_enabled               boolean     FALSE                          pga_aggregate_target                 big integer 2147483648                     plsql_compiler_flags                 string      INTERPRETED                    plsql_native_library_subdir_count    integer     0                              plsql_v2_compatibility               boolean     FALSE                          pre_page_sga                         boolean     FALSE                          processes                            integer     150                            query_rewrite_enabled                string      FALSE                          query_rewrite_integrity              string      enforced       read_only_open_delayed               boolean     FALSE                          recovery_parallelism                 integer     0                              remote_archive_enable                string      true                        remote_dependencies_mode             string      TIMESTAMP                      remote_login_passwordfile            string      EXCLUSIVE                      remote_os_authent                    boolean     FALSE                          remote_os_roles                      boolean     FALSE                          replication_dependency_tracking      boolean     TRUE                           resource_limit                       boolean     FALSE                          row_locking                          string      always                        
serial_reuse                         string      DISABLE                        serializable                         boolean     FALSE                          service_names                        string      dss                            session_cached_cursors               integer     0                              session_max_open_files               integer     10                             sessions                             integer     170                            sga_max_size                         big integer 1310862512                      shadow_core_dump                     string      partial                        shared_memory_address                integer     0                              
shared_pool_reserved_size            big integer 2516582                        shared_pool_size                     big integer 50331648                       shared_server_sessions               integer     165                     shared_servers                       integer     1                              sort_area_retained_size              integer     0                              sort_area_size                       integer     524288                         spfile                               string      ?/dbs/spfile@.ora              sql92_security                       boolean     FALSE                          sql_trace                            boolean     FALSE                          sql_version                          string      NATIVE                         standby_archive_dest                 string      ?/dbs/arch                     standby_file_management              string      MANUAL                         star_transformation_enabled          string      FALSE                          statistics_level                     string      TYPICAL                        tape_asynch_io                       boolean     TRUE                           thread                               integer     0                              timed_os_statistics                  integer     0                              timed_statistics                     boolean     TRUE                           trace_enabled                        boolean     TRUE                           
transaction_auditing                 boolean     TRUE                           transactions                         integer     187                            transactions_per_rollback_segment    integer     5                    undo_management                      string      AUTO                           undo_retention                       integer     10800                          undo_suppress_errors                 boolean     FALSE                          undo_tablespace                      string      UNDOTBS                        use_indirect_data_buffers            boolean     FALSE                          user_dump_dest                       string     
workarea_size_policy                 string      AUTO

Following the initial load of the base product, Oracle patch set 3095277 was applied to bring the database software to release The Oracle Universal Installer was used to perform the initial installation and patching.

Once the database was installed and patched to the proper release level, the Oracle supplied database creation assistant (dbca) utility was used to generate a set of database creation scripts. The decision was made to utilize scripts because the same exact set of scripts could then be used and documented to create all test databases.

Using the dbcacreated scripts, the database was created in noarchivelog mode and the tables were created using a standard table create script. nologgingwas specified for the initial data load, index builds and series of tests.

At this point in the process, there is a standard Oracle database, patched appropriately to release and configured as a normal database would be configured in any Oracle shop in the world. The required tablespaces, user tables, and base tables for the test have been built. Data loading is next.

The above book excerpt is from:

Oracle Solid State Disk Tuning

High Performance Oracle tuning with RAM disk

ISBN 0-9744486-5-6  

Donald K. Burleson & Mike Ault


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter