create db & init.ora optimization

From: Jan-Erik Rosinowski <spamfilter_at_rosinowski.de>
Date: Wed, 08 Dec 1999 10:16:00 GMT
Message-ID: <384e2c57.3985320@News.CIS.DFN.DE>

hi,

i'm trying to optimize an ora 7.3.4 database for about 10 concurrent users, which deals with blobs. nt-server w/ 128mb ram. typical userload consists of permanent recreation of stored procs (yup) and inserts/updates of hundreds of rows in one table.

most query's plans look ok, so i know try to optimize the db-settings. any comments?

################################# init.ora

audit_trail                          = false
background_dump_dest                 = %dbpath%trc%oracle_sid%
compatible                           = 7.3.0.0.0
control_files                        =
(%dbpath%ctl1%oracle_sid%.ora,%dbpath%ctl2%oracle_sid%.ora) # ...
db_block_buffers                     = 5000
db_block_size                        = 8192
db_file_multiblock_read_count        = 8
db_files                             = 100
db_name                              = %oracle_sid%
dml_locks                            = 200
log_archive_dest                     = %dbpath%lga%oracle_sid%
log_archive_start                    = true
log_buffer                           = 1048576
log_checkpoint_interval              = 20000
max_dump_file_size                   = 10240
open_cursors                         = 200
optimizer_mode                       = choose
processes                            = 100
remote_login_passwordfile            = shared
rollback_segments                    =
(rb0,rb1,rb2,rb3,rb4,rb5,rb6,rb7,rb8,rb9)
row_cache_cursors                    = 100
sequence_cache_entries               = 10
shared_pool_size                     = 15000000
snapshot_refresh_processes           = 1
sort_area_size                       = 1000000
text_enable                          = true
timed_statistics                     = true
user_dump_dest                       = %dbpath%trc%oracle_sid%

################################# build_db



-----------------------------------------------------------------------------


-----------------------------------------------------------------------------


-----------------------------------------------------------------------------

startup nomount pfile=%dbpath%init%oracle_sid%.ora

create database %oracle_sid%
  controlfile reuse
  logfile
    group 1

      ('%dbpath%lg1a%oracle_sid%.dbf',
       '%dbpath%lg1b%oracle_sid%.dbf') size 10m reuse,
    group 2
      ('%dbpath%lg2a%oracle_sid%.dbf',
       '%dbpath%lg2b%oracle_sid%.dbf') size 10m reuse

  maxlogfiles 50
  maxdatafiles 50
  archivelog
  datafile
    '%dbpath%sys0%oracle_sid%.dbf'
    size 10m
    reuse
    autoextend on next 10m maxsize unlimited   character set
    we8iso8859p1;

tablespaces

create tablespace user_data
  datafile '%dbpath%usr0%oracle_sid%.dbf'   size 20m
  reuse
  autoextend on next 10m maxsize unlimited;

create tablespace rollback_data
  datafile '%dbpath%rbs0%oracle_sid%.dbf'   size 10m
  reuse
  autoextend on next 5m maxsize unlimited;

create tablespace temporary_data
  datafile '%dbpath%tmp0%oracle_sid%.dbf'   size 5m
  reuse
  autoextend on next 5m maxsize unlimited;

create 10 rollback segments. allows 10 concurrent users with open
transactions updating the database. this should be enough.

create public rollback segment rbdummy storage(initial 1m next 1m) tablespace system;
alter rollback segment rbdummy online;

create public rollback segment rb0 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb1 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb2 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb3 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb4 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb5 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb6 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb7 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb8 storage(initial 1m next 1m) tablespace rollback_data;
create public rollback segment rb9 storage(initial 1m next 1m) tablespace rollback_data;

alter rollback segment rb0 online;
alter rollback segment rb1 online;
alter rollback segment rb2 online;
alter rollback segment rb3 online;
alter rollback segment rb4 online;
alter rollback segment rb5 online;
alter rollback segment rb6 online;
alter rollback segment rb7 online;
alter rollback segment rb8 online;
alter rollback segment rb9 online;

alter rollback segment rbdummy offline;

drop public rollback segment rbdummy;

alter user sys identified by "ledersessel"; alter user system identified by "ledersessel"; alter user system temporary tablespace temporary_data; alter user system default tablespace user_data;

create user rsp identified by "tiger"
  default tablespace user_data
  temporary tablespace temporary_data
  profile default
  quota unlimited on user_data;

grant connect,dba,exp_full_database,imp_full_database to rsp with admin option;
alter user rsp default role all;

ciao, jan

http://www.rsp.de - Software zur effizienten Erstellung und Verwaltung von Gutachten, Analysen, Pruefprotokollen etc. Received on Wed Dec 08 1999 - 04:16:00 CST