Slow database....

From: Clinton Naude <clintonn_at_meb.co.za>
Date: Wed, 20 Sep 2000 14:41:51 +0200
Message-Id: <10625.117433@fatcity.com>

Hi all...

I am stumped with this one and its driving me mad!!!

One of our developers is running a script that is slowing the database down drastically. It actually even hangs it.
I have tried to change database parameters etc and with no luck.

Here are my database parameters and the script he is running (its java), what am I doing wrong?

The archive logs also seem to be switching over every 15 seconds when this scripts is running...

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;

public class Tatanka {

  public static void main(String args[])   {

    Connection con=null;
    PreparedStatement st=null;
    long tollerance = (long)1000;
    int number = 20000;
    try
    {

      Class.forName("oracle.jdbc.driver.OracleDriver");
      con =

DriverManager.getConnection("jdbc:oracle:thin:@10.6.5.211:1521:online","test user","sunos01");

      ArrayList drop = new ArrayList();
      ArrayList create = new ArrayList();
      ArrayList alter = new ArrayList();
      ArrayList insert = new ArrayList();
      ArrayList delete = new ArrayList();

      create.add(new String("create table csemployee" +
                          " (username varchar2(40)," +
                          "  password varchar2(40)," + 
                          "  firstname varchar2(40)," + 
                          "  surname varchar2(40)," +
                          "  id varchar2(13))"));
      alter.add(new String("alter table csemployee " +
                         " add constraint csemployee_pk primary key" +
                         "  (username)"));


      drop.add(new String("drop table csemployee"));

      for (int i =0 ; i < number ;i += 1)


      {

        
        String todo = new String("insert into csemployee " +
                            "values("
                            + "'" + new String("pietieGets" + i)+ "'" + "," 
                            + "'" + new String(new StringBuffer("pietieGets"
+ i).reverse().toString())+ "'"+","
                            + "'" + new String("Piet" + i)+ "'"+ ","
                            + "'" + new String("Pompies"+ i)+ "'"+ ","
                            + "'" + i+ "'"
                            + ")");
         insert.add(todo);
         delete.add(new String("pietieGets" + i));
         if ((i % 1000) == 0 )
          System.out.println("Created number: " + i + " ---- " +  todo);
 
       }

      System.out.println("dropping table... ");
      // drop the tables
      for (int i = 0 ; i < drop.size() ; i += 1 )


      {

        try
        {  
          st = con.prepareStatement((String)drop.get(i));
          st.executeUpdate();

          System.out.println((String)drop.get(i) + " succeeded!");
        } 
        catch (Exception e)
        {
          System.out.println("Could not drop table: "+e);
        }

      }   
      System.out.println("droped table: ");
      
      // create the tables
      for (int i = 0 ; i < create.size() ; i += 1 )


      {

        try
        {  
          st = con.prepareStatement((String)create.get(i));
          st.executeUpdate();

          System.out.println((String)create.get(i) + " succeeded!");
        } 
        catch (Exception e)
        {
          System.out.println("Could not create table: "+e);
        }

      }  
      System.out.println("created table: ");

      // alter the tables
      for (int i = 0 ; i < alter.size() ; i += 1 )


      {

        try
        {  
          st = con.prepareStatement((String)alter.get(i));
          st.executeUpdate();

          System.out.println((String)alter.get(i) + " succeeded!");
        } 
        catch (Exception e)
        {
          System.out.println("Could not alter table: "+e);
        }

      }       
      System.out.println("altered table: ");
      

      // insert into the tables
      System.out.println("Going to do the insert...");
      for (int i = 0 ; i < insert.size() ; i += 1 )


      {

        Date date_start;
        Date date_end;
        
        try
        {  
          st = con.prepareStatement((String)insert.get(i));
          date_start = new Date();
          st.executeUpdate();            
          st.close();
          date_end = new Date();
          if ((i % 1000) == 0)
          {
            System.out.println("Insert nr:" + i);
            //con.commit();
          }
          double diff =
(double)date_end.getTime()-(double)date_start.getTime();
            System.out.println("time pause (insert): time ms - " + diff +
"--- item - " +i);
        } 
        catch (Exception e)
        {
          System.out.println("Could not insert into table: "+e);
        }
      }
      System.out.println("insert complete.");
      
      System.out.println("Going to do the delete...");

      // delete from the tables
      for (int i = 0 ; i < delete.size() ; i += 1 )


      {

        Date date_start;
        Date date_end;

        try
        {  
          st = con.prepareStatement("delete from csemployee where username =
'" +(String)delete.get(i) + "'");
          date_start = new Date();
          st.executeUpdate();
          st.close();
          date_end = new Date();
          if ((i % 1000) == 0)
            System.out.println("delete nr:" + i);
          long diff = date_end.getTime()-date_start.getTime();
          if ( (diff) > (double)tollerance)
            System.out.println("time pause (delete): time ms - " + diff +
"--- item - " +i);
        } 
        catch (Exception e)
        {
          System.out.println("Could not delete from table: "+e);
        }      
      }
      System.out.println("Delete complete.");

    }
    catch (Exception e)
    {
     e.printStackTrace();
    }
    finally

    {        
      try


      {

        st.close();
        con.close();
      }
      catch (Exception e)


      {

        e.printStackTrace();
      }

    }
  }
}

Here are my parameters:

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

        9 processes
3                

250

FALSE FALSE FALSE FALSE FALSE user processes  

       10 sessions
3                

280

TRUE FALSE FALSE FALSE FALSE user and system sessions  

       11 timed_statistics
1                

FALSE TRUE TRUE IMMEDIATE FALSE FALSE maintain internal timing statistics  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       12 timed_os_statistics
3                

0

TRUE FALSE IMMEDIATE FALSE FALSE internal os statistic gathering interval in seconds  

       13 resource_limit
1                

FALSE TRUE FALSE IMMEDIATE FALSE FALSE master switch for resource limit  

       14 license_max_sessions
3                

0

TRUE FALSE IMMEDIATE FALSE FALSE maximum number of non-system user sessions allowed  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       15 license_sessions_warning
3                

0

TRUE FALSE IMMEDIATE FALSE FALSE warning level for number of non-system user sessions  

       18 cpu_count
3                

2

TRUE FALSE FALSE FALSE FALSE number of cpu's for this instance  

       20 instance_groups
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

list of instance group names  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       21 event
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

debug event control - default null string  

       22 shared_pool_size
2                

61440000

FALSE FALSE FALSE FALSE FALSE size in bytes of shared pool  

       25 shared_pool_reserved_size
2                

3072000

TRUE FALSE FALSE FALSE FALSE size in bytes of reserved area of shared pool  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       27 large_pool_size
2                

81920000

FALSE FALSE FALSE FALSE FALSE size in bytes of the large allocation pool  

       29 java_pool_size
2                

28672000

FALSE FALSE FALSE FALSE FALSE size in bytes of the Java pool  

       30 java_soft_sessionspace_limit
3                

0

TRUE FALSE FALSE FALSE FALSE warning limit on size in bytes of a Java sessionspace  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       31 java_max_sessionspace_size
3                

0

TRUE FALSE FALSE FALSE FALSE max allowed size in bytes of a Java sessionspace  

       32 pre_page_sga
1                

FALSE TRUE FALSE FALSE FALSE FALSE pre-page sga for process  

       33 shared_memory_address
3                

0

TRUE FALSE FALSE FALSE FALSE SGA starting address (low order 32-bits on 64-bit platforms)  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       34 hi_shared_memory_address
3                

0

TRUE FALSE FALSE FALSE FALSE SGA starting address (high order 32-bits on 64-bit platforms)  

       35 use_indirect_data_buffers
1                

FALSE TRUE FALSE FALSE FALSE FALSE Enable indirect data buffers (very large SGA on 32-bit platforms  

       37 lock_sga
1                

FALSE TRUE FALSE FALSE FALSE FALSE Lock entire SGA in physical memory  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       44 lock_name_space
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

lock name space used for generating lock names for standby/clone  

       46 enqueue_resources
3                

1452

TRUE FALSE FALSE FALSE FALSE resources for enqueues  

       59 nls_language
2                

AMERICAN TRUE TRUE FALSE FALSE FALSE NLS language name  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       60 nls_territory
2                

AMERICA TRUE TRUE FALSE FALSE FALSE NLS territory name  

       61 nls_sort
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS linguistic definition name  

       62 nls_date_language
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS date language name  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       63 nls_date_format
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS Oracle date format  

       64 nls_currency
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS local currency symbol  

       65 nls_numeric_characters
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS numeric characters  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       66 nls_iso_currency
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS ISO currency territory name  

       67 nls_calendar
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS calendar system name  

       68 nls_time_format
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

time format  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       69 nls_timestamp_format
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

time stamp format  

       70 nls_time_tz_format
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

time with timezone format  

       71 nls_timestamp_tz_format
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

timestampe with timezone format  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       72 nls_dual_currency
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

Dual currency symbol  

       73 nls_comp
2                
 

TRUE      TRUE  FALSE     FALSE      FALSE

NLS comparison  

       74 disk_asynch_io
1                

TRUE TRUE FALSE FALSE FALSE FALSE Use asynch I/O for random access devices  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       75 tape_asynch_io
1                

TRUE TRUE FALSE FALSE FALSE FALSE Use asynch I/O requests for tape devices  

       77 dbwr_io_slaves
3                

0

TRUE FALSE FALSE FALSE FALSE DBWR I/O slaves  

       81 backup_tape_io_slaves
1                

FALSE TRUE FALSE DEFERRED FALSE FALSE BACKUP Tape I/O slaves  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       85 db_file_direct_io_count
3                

64

TRUE FALSE DEFERRED FALSE FALSE Sequential I/O block count  

       90 resource_manager_plan
2                
 

TRUE      FALSE IMMEDIATE FALSE      FALSE

resource mgr top plan  

       98 lm_procs
3                

127

TRUE FALSE FALSE FALSE FALSE number of client processes configured for the lock manager  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

       99 lm_ress
3                

6000

TRUE FALSE FALSE FALSE FALSE number of resources configured for the lock manager  

      100 lm_locks
3                

12000

TRUE FALSE FALSE FALSE FALSE number of locks configured for the lock manager  

      113 control_files
2                

/oracle/oracle8.1.5/database/product/oradata/online/control01.ctl, /oracle/oracle8.1.5/database/prod
uct/oradata/online/control02.ctl

FALSE FALSE FALSE FALSE FALSE control file names list

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      115 db_file_name_convert
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

datafile name convert pattern and string for standby/clone datab  

      116 log_file_name_convert
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

logfile name convert pattern and string for standby/clone databa  

      119 db_block_buffers
3                

8192

FALSE FALSE FALSE FALSE FALSE Number of database blocks cached in memory

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      120 buffer_pool_keep
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

Number of database blocks/latches in keep buffer pool  

      121 buffer_pool_recycle
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

Number of database blocks/latches in recycle buffer pool  

      131 db_block_checksum
1                

FALSE TRUE FALSE IMMEDIATE FALSE FALSE store checksum in db blocks and check during reads

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      134 db_block_size
3                

4096

FALSE FALSE FALSE FALSE FALSE Size of database block in bytes  

      145 db_block_lru_latches
3                

1

TRUE FALSE FALSE FALSE FALSE number of lru latches  

      146 db_writer_processes
3                

1

TRUE FALSE FALSE FALSE FALSE number of background database writer processes to start

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      158 db_block_max_dirty_target
3                

8192

TRUE FALSE IMMEDIATE FALSE FALSE Upper bound on modified buffers/recovery reads  

      159 max_commit_propagation_delay
3                

700

TRUE FALSE FALSE FALSE FALSE Max age of new snapshot in .01 seconds  

      161 compatible
2                

8.1.0

FALSE FALSE FALSE FALSE FALSE Database will be completely compatible with this software versio

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      164 log_archive_start
1                

TRUE FALSE FALSE FALSE FALSE FALSE start archival process on SGA initialization  

      167 log_archive_dest
2                
 

TRUE      FALSE IMMEDIATE FALSE      FALSE

archival destination text string  

      168 log_archive_duplex_dest
2                
 

TRUE      FALSE IMMEDIATE FALSE      FALSE

duplex archival destination text string

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      169 log_archive_dest_1
2                

location=/usr4/oracle/online/archive_logs

FALSE TRUE IMMEDIATE FALSE FALSE archival destination #1 text string  

      170 log_archive_dest_2
2                
 

TRUE      TRUE  IMMEDIATE FALSE      FALSE

archival destination #2 text string  

      171 log_archive_dest_3
2                
 

TRUE      TRUE  IMMEDIATE FALSE      FALSE

archival destination #3 text string

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      172 log_archive_dest_4
2                
 

TRUE      TRUE  IMMEDIATE FALSE      FALSE

archival destination #4 text string  

      173 log_archive_dest_5
2                
 

TRUE      TRUE  IMMEDIATE FALSE      FALSE

archival destination #5 text string  

      174 log_archive_dest_state_1
2                

enable

TRUE TRUE IMMEDIATE FALSE FALSE archival destination #1 state text string

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      175 log_archive_dest_state_2
2                

enable

TRUE TRUE IMMEDIATE FALSE FALSE archival destination #2 state text string  

      176 log_archive_dest_state_3
2                

enable

TRUE TRUE IMMEDIATE FALSE FALSE archival destination #3 state text string  

      177 log_archive_dest_state_4
2                

enable

TRUE TRUE IMMEDIATE FALSE FALSE archival destination #4 state text string

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      178 log_archive_dest_state_5
2                

enable

TRUE TRUE IMMEDIATE FALSE FALSE archival destination #5 state text string  

      179 log_archive_max_processes
3                

1

TRUE FALSE IMMEDIATE FALSE FALSE maximum number of active ARCH processes  

      180 log_archive_min_succeed_dest
3                

1

TRUE TRUE IMMEDIATE FALSE FALSE minimum number of archive destinations that must succeed

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      181 standby_archive_dest
2                

?/dbs/arch

TRUE FALSE IMMEDIATE FALSE FALSE standby database archivelog destination text string  

      182 log_archive_format
2                

%t_%s.dbf

FALSE FALSE FALSE FALSE FALSE archival destination format  

      184 log_buffer
3                

327680

FALSE FALSE FALSE FALSE FALSE redo circular buffer size

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      186 log_checkpoint_interval
3                

20000

FALSE FALSE IMMEDIATE FALSE FALSE # redo blocks checkpoint threshold  

      187 log_checkpoint_timeout
3                

2500

FALSE FALSE IMMEDIATE FALSE FALSE Maximum time interval between checkpoints in seconds  

      193 db_files
3                

200

TRUE FALSE FALSE FALSE FALSE max allowable # db files

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      194 db_file_multiblock_read_count
3                

8

TRUE TRUE IMMEDIATE FALSE FALSE db block to be read each IO  

      197 read_only_open_delayed
1                

FALSE TRUE FALSE FALSE FALSE FALSE if TRUE delay opening of read only files until first access  

      198 parallel_server
1                

FALSE TRUE FALSE FALSE FALSE FALSE if TRUE startup in parallel server mode

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      199 parallel_server_instances
3                

1

TRUE FALSE FALSE FALSE FALSE number of instances to use for sizing OPS SGA structures  

      202 gc_releasable_locks
3                

0

TRUE FALSE FALSE FALSE FALSE number of releasable locks (DFS)  

      203 gc_rollback_locks
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

locks for the rollback segments (DFS)

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      204 gc_files_to_locks
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

mapping between file numbers and lock buckets (DFS)  

      205 gc_defer_time
3                

10

TRUE FALSE IMMEDIATE FALSE FALSE how long to defer down converts for hot buffers (DFS)  

      217 thread
3                

0

TRUE FALSE FALSE FALSE FALSE Redo thread to mount

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      220 fast_start_io_target
3                

8192

TRUE TRUE IMMEDIATE FALSE FALSE Upper bound on recovery reads  

      225 log_checkpoints_to_alert
1                

FALSE TRUE FALSE FALSE FALSE FALSE log checkpoint begin/end to alert file  

      226 recovery_parallelism
3                

0

TRUE FALSE FALSE FALSE FALSE number of server processes to use for parallel recovery

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      228 control_file_record_keep_time
3                

7

TRUE FALSE IMMEDIATE FALSE FALSE control file record keep time in days  

      229 dml_locks
3                

1232

TRUE FALSE FALSE FALSE FALSE dml locks - one for each table modified in a transaction  

      230 row_locking
2                

always

TRUE FALSE FALSE FALSE FALSE row-locking

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      231 serializable
1                

FALSE TRUE FALSE FALSE FALSE FALSE serializable  

      232 replication_dependency_tracking
1                

TRUE TRUE FALSE FALSE FALSE FALSE tracking dependency for Replication parallel propagation  

      233 instance_number
3                

0

TRUE FALSE FALSE FALSE FALSE instance number

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      237 transactions
3                

308

TRUE FALSE FALSE FALSE FALSE max. number of concurrent active transactions  

      238 transactions_per_rollback_segment
3                

5

TRUE FALSE FALSE FALSE FALSE number of active transactions per rollback segment  

      239 max_rollback_segments
3                

61

TRUE FALSE FALSE FALSE FALSE max. number of rollback segments in SGA cache

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      240 rollback_segments
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

undo segment list  

      245 transaction_auditing
1                

TRUE TRUE FALSE DEFERRED FALSE FALSE transaction auditing records generated in the redo log  

      250 fast_start_parallel_rollback
2                

LOW TRUE FALSE IMMEDIATE FALSE FALSE max number of parallel recovery slaves that may be used

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      252 db_block_checking
1                

FALSE TRUE TRUE DEFERRED FALSE FALSE data and index block checking  

      265 ent_domain_name
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

Enterprise Domain Name  

      266 os_roles
1                

FALSE TRUE FALSE FALSE FALSE FALSE retrieve roles from the operating system

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      267 rdbms_server_dn
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

RDBMS's Distinguished Name  

      268 max_enabled_roles
3                

20

TRUE FALSE FALSE FALSE FALSE max number of roles a user can have enabled  

      269 remote_os_authent
1                

FALSE TRUE FALSE FALSE FALSE FALSE allow non-secure remote clients to use auto-logon accounts

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      270 remote_os_roles
1                

FALSE TRUE FALSE FALSE FALSE FALSE allow non-secure remote clients to use os roles  

      271 O7_DICTIONARY_ACCESSIBILITY
1                

TRUE TRUE FALSE FALSE FALSE FALSE Version 7 Dictionary Accessibility Support  

      273 remote_login_passwordfile
2                

EXCLUSIVE FALSE FALSE FALSE FALSE FALSE password file usage parameter

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      274 dblink_encrypt_login
1                

FALSE TRUE FALSE FALSE FALSE FALSE enforce password for distributed login always be encrypted  

      275 license_max_users
3                

0

TRUE FALSE IMMEDIATE FALSE FALSE maximum number of named users that can be created in the databas  

      276 db_domain
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

directory part of global database name stored with CREATE DATABA

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      277 global_names
1                

FALSE TRUE TRUE IMMEDIATE FALSE FALSE enforce that database links have same name as remote database  

      279 distributed_transactions
3                

10

FALSE FALSE FALSE FALSE FALSE max. number of concurrent distributed transactions  

      282 commit_point_strength
3                

1

TRUE FALSE FALSE FALSE FALSE Bias this node has toward not preparing in a two-phase commit

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

 

      283 service_names
2                

online

FALSE FALSE FALSE FALSE FALSE service names supported by the instance  

      284 instance_name
2                

online

FALSE FALSE FALSE FALSE FALSE instance name supported by the instance  

      285 mts_dispatchers
2                

(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))(DIS=1)(SES=254)(CON=254)(TIC=15)(POO=N O)(MUL=NO)(LIS=(ADDRESS_
LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0. 0.1)(PORT=1521))(ADDRESS
=(PROTOCOL=IPC)(KEY=online))))(PRE=oracle.aurora.server.SGiopServer), (ADDRESS=(PARTIAL=YES)(PROTOCO

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

L=TCP))(DIS=1)(SES=254)(CON=254)(TIC=15)(POO=NO)(MUL=NO)(LIS=(ADDRESS_LIST=( ADDRESS=(PROTOCOL=IPC)(K
EY=PNPKEY))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(ADDRESS=(PROT OCOL=IPC)(KEY=online))))
FALSE FALSE IMMEDIATE FALSE FALSE specifications of dispatchers  

      286 mts_servers
3                

1

FALSE FALSE IMMEDIATE FALSE FALSE number of servers to start up  

      287 mts_max_servers
3                

20

FALSE FALSE FALSE FALSE FALSE max number of servers  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      288 mts_max_dispatchers
3                

5

FALSE FALSE FALSE FALSE FALSE max number of dispatchers  

      289 local_listener
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

local listener  

      293 mts_service
2                

online

TRUE FALSE FALSE FALSE FALSE service supported by dispatchers  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      294 mts_listener_address
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

address(es) of network listener  

      295 mts_multiple_listeners
1                

FALSE TRUE FALSE FALSE FALSE FALSE Are multiple listeners enabled?  

      296 open_links
3                

4

FALSE FALSE FALSE FALSE FALSE max # open links per session  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      297 open_links_per_instance
3                

4

TRUE FALSE FALSE FALSE FALSE max # open links per instance  

      301 optimizer_features_enable
2                

8.1.5

TRUE FALSE FALSE FALSE FALSE optimizer plan compatibility parameter  

      302 fixed_date
2                
 

TRUE      FALSE IMMEDIATE FALSE      FALSE

fixed SYSDATE value  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      303 audit_trail
2                

NONE TRUE FALSE FALSE FALSE FALSE enable system auditing  

      304 sort_area_size
3                

65536

TRUE TRUE DEFERRED FALSE FALSE size of in-memory sort work area  

      305 sort_area_retained_size
3                

0

TRUE TRUE DEFERRED FALSE FALSE size of in-memory sort work area retained between fetch calls  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      306 sort_multiblock_read_count
3                

2

TRUE TRUE DEFERRED FALSE FALSE multi-block read count for sort  

      308 db_name
2                

online

FALSE FALSE FALSE FALSE FALSE database name specified in CREATE DATABASE  

      309 open_cursors
3                

50

TRUE FALSE FALSE FALSE FALSE max # cursors per process  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      310 ifile
4                
 

TRUE      FALSE FALSE     FALSE      FALSE

include file in init.ora  

      311 sql_trace
1                

FALSE TRUE FALSE FALSE FALSE FALSE enable SQL trace  

      313 os_authent_prefix
2                
 

FALSE     FALSE FALSE     FALSE      FALSE

prefix for auto-logon accounts  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      315 optimizer_mode
2                

CHOOSE TRUE TRUE FALSE FALSE FALSE optimizer mode  

      317 sql92_security
1                

FALSE TRUE FALSE FALSE FALSE FALSE require select privilege for searched update/delete  

      319 blank_trimming
1                

FALSE TRUE FALSE FALSE FALSE FALSE blank trimming semantics parameter  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      320 always_anti_join
2                

NESTED_LOOPS TRUE FALSE FALSE FALSE FALSE always use this anti-join when possible  

      321 partition_view_enabled
1                

FALSE TRUE TRUE FALSE FALSE FALSE enable/disable partitioned views  

      324 star_transformation_enabled
2                

FALSE TRUE TRUE FALSE FALSE FALSE enable the use of star transformation  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      333 parallel_broadcast_enabled
1                

FALSE TRUE TRUE FALSE FALSE FALSE enable broadcasting of small inputs to hash and sort merge joins  

      341 parallel_adaptive_multi_user
1                

FALSE TRUE FALSE IMMEDIATE FALSE FALSE enable adaptive setting of degree for multiple user streams  

      342 parallel_threads_per_cpu
3                

2

TRUE FALSE IMMEDIATE FALSE FALSE number of parallel execution threads per CPU  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      343 parallel_automatic_tuning
1                

FALSE TRUE FALSE FALSE FALSE FALSE enable intelligent defaults for parallel execution parameters  

      357 always_semi_join
2                

standard

TRUE FALSE FALSE FALSE FALSE always use this semi-join when possible  

      362 optimizer_max_permutations
3                

80000

TRUE TRUE FALSE FALSE FALSE optimizer maximum join permutations per query block  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      363 optimizer_index_cost_adj
3                

100

TRUE TRUE FALSE FALSE FALSE optimizer index cost adjustment  

      364 optimizer_index_caching
3                

0

TRUE TRUE FALSE FALSE FALSE optimizer percent index caching  

      370 query_rewrite_enabled
1                

FALSE TRUE TRUE IMMEDIATE FALSE FALSE allow rewrite of queries using materialized views if enabled  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      371 query_rewrite_integrity
2                

enforced

TRUE TRUE IMMEDIATE FALSE FALSE perform rewrite using materialized views with desired integrity  

      385 serial_reuse
2                

DISABLE TRUE FALSE FALSE FALSE FALSE reuse the frame segments  

      386 cursor_space_for_time
1                

FALSE TRUE FALSE FALSE FALSE FALSE use more memory in order to get faster execution  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      387 session_cached_cursors
3                

0

TRUE TRUE FALSE FALSE FALSE number of cursors to save in the session cursor cache  

      388 text_enable
1                

FALSE TRUE TRUE IMMEDIATE FALSE FALSE enable text searching  

      389 remote_dependencies_mode
2                

TIMESTAMP TRUE TRUE IMMEDIATE FALSE FALSE remote-procedure-call dependencies mode parameter  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      390 utl_file_dir
2                
 

TRUE      FALSE FALSE     FALSE      FALSE

utl_file accessible directories list  

      392 plsql_v2_compatibility
1                

FALSE TRUE TRUE IMMEDIATE FALSE FALSE PL/SQL version 2.x compatibility flag  

      393 plsql_load_without_compile
1                

FALSE TRUE TRUE FALSE FALSE FALSE PL/SQL load without compilation flag  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      394 job_queue_processes
3                

2

FALSE FALSE IMMEDIATE FALSE FALSE number of job queue processes to start  

      395 job_queue_interval
3                

60

FALSE FALSE FALSE FALSE FALSE Wakeup interval in seconds for job queue processes  

      396 optimizer_percent_parallel
3                

0

TRUE TRUE FALSE FALSE FALSE optimizer percent parallel  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      397 optimizer_search_limit
3                

5

TRUE TRUE FALSE FALSE FALSE optimizer search limit  

      398 parallel_min_percent
3                

0

TRUE TRUE FALSE FALSE FALSE minimum percent of threads required for parallel query  

      401 create_bitmap_area_size
3                

8388608

TRUE FALSE FALSE FALSE FALSE size of create bitmap buffer for bitmap index  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      402 bitmap_merge_area_size
3                

1048576

TRUE FALSE FALSE FALSE FALSE maximum memory allow for BITMAP MERGE  

      404 parallel_min_servers
3                

0

TRUE FALSE FALSE FALSE FALSE minimum parallel query servers per instance  

      405 parallel_max_servers
3                

5

TRUE FALSE FALSE FALSE FALSE maximum parallel query servers per instance  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      409 parallel_instance_group
2                
 

TRUE      TRUE  IMMEDIATE FALSE      FALSE

instance group to use for all parallel operations  

      412 parallel_execution_message_size
3                

2148

TRUE FALSE FALSE FALSE FALSE message buffer size for parallel execution  

      420 hash_join_enabled
1                

TRUE TRUE TRUE FALSE FALSE FALSE enable/disable hash join  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      421 hash_area_size
3                

131072

TRUE TRUE FALSE FALSE FALSE size of in-memory hash work area  

      422 hash_multiblock_io_count
3                

0

TRUE TRUE IMMEDIATE FALSE FALSE number of blocks hash join will read/write at once  

      424 shadow_core_dump
2                

partial

TRUE FALSE FALSE FALSE FALSE Core Size for Shadow Processes  

      NUM NAME
TYPE                
--------- ----------------------------------------------------------------
---------                

VALUE

---

---

ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ

• ----- --------- ---------- -----

DESCRIPTION

---

      425 background_core_dump
2                

partial

TRUE FALSE FALSE FALSE FALSE Core Size for Background Processes  

      426 background_dump_dest
2                

/oracle/oracle8.1.5/database/product/admin/online/bdump

FALSE FALSE IMMEDIATE FALSE FALSE Received on Wed Sep 20 2000 - 07:41:51 CDT