Home » RDBMS Server » Server Utilities » Create database, svrmgrl are taking very long time on AIX with 8.1.7
Create database, svrmgrl are taking very long time on AIX with 8.1.7 [message #69562] Thu, 31 January 2002 08:06 Go to next message
Venkata Ramana B
Messages: 3
Registered: January 2002
Junior Member
Hi,

We are facing a strange problem with Oracle 8.1.7 on IBM AIX 4.3.3.
We have worked fine with Oracle 7.3.2 through Oracle 8.1.6 on the same machine.
We installed Oracle 8.1.7 and tried creating a database. It is taking more than 30 minutes to just process "CREATE DATABASE" command. Also all other commands are taking long time. We have a database creation script, which creates a database and some tablespaces and our database schema. This script used to take 30-40 MINUTES on Oracle 7.3.2, 7.3.4 and on 8.1.6.
But now, the same script is taking untolerably 9-10 HOURS.
Ofcourse, database is running fine and it is not giving any error messages. But, each time if we SHUTDOWN and STARTUP is taking a minimum of 40 minutes.
We have a need to create lot of databases per eachday and 10 hours each time for each database is really costly.

DETAILS:
OS: IBM AIX 4.3.3
MAIN MEM: 192MB
SWAP: 380MB

initSID.ora parameters
-------------------------------------------------------
rollback_segments = (r01, r02, r03, r04)
db_files = 20
db_block_size = 8192
db_file_multiblock_read_count = 8
db_block_buffers = 200
shared_pool_size = 3500000
processes = 50
dml_locks = 100
log_buffer = 8192
max_dump_file_size = 10240
nls_language = "american"
nls_territory = "america"
nls_date_language = "american"
nls_date_format = "dd-mon-yyyy hh24:mi:ss"
os_authent_prefix = ""
--------------------------------------------------

#ulimit -a
time(seconds) unlimited
file(blocks) 2097151
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

$ORACLE_HOME/rdbms/log/alert_DBNAME.log
-------------------------------------------------------------------
Thu Jan 31 20:37:38 2002
Shutting down instance (abort)
Thu Jan 31 20:38:25 2002
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 8.1.7.0.0.
System parameters with non-default values:
processes = 50
shared_pool_size = 3500000
control_files = cntrl_SUNEEL1.ora, cntrl_SUNEEL2.ora, cntrl_SUNEEL3
.ora
db_block_buffers = 100
log_buffer = 32768
log_checkpoint_interval = 10000
db_files = 80
db_file_multiblock_read_count= 8
global_names = TRUE
db_name = SUNEEL
parallel_max_servers = 5
max_dump_file_size = 10240
oracle_trace_enable = TRUE
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Thu Jan 31 20:44:14 2002
alter database mount
Thu Jan 31 20:49:01 2002
Successful mount of redo thread 1, with mount id 2501647927.
Thu Jan 31 20:49:01 2002
Database mounted in Exclusive Mode.
Completed: alter database mount
Thu Jan 31 20:49:26 2002
alter database open
Thu Jan 31 20:49:30 2002
Beginning crash recovery of 1 threads
Thu Jan 31 20:49:32 2002
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0 errs 0: /hcl1/oracle817/dbs/redo02SUNEEL.dbf
Thu Jan 31 20:49:33 2002
Thread recovery: finish rolling forward thread 1
Thread recovery: 0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Thu Jan 31 20:49:38 2002
Thread 1 advanced to log sequence 3
Thread 1 opened at log sequence 3
Current log# 3 seq# 3 mem# 0: /hcl1/oracle817/dbs/redo03SUNEEL.dbf
Successful open of redo thread 1.
Thu Jan 31 20:53:48 2002
SMON: enabling cache recovery
Thu Jan 31 20:58:55 2002
SMON: enabling tx recovery
Thu Jan 31 20:58:56 2002
Completed: alter database open
---------------------------------------------------------------------
Observe the time for database STARTUP, MOUNT and OPEN commands.

Trace File Output
----------------------------------------------------------------
Dump file /hcl1/oracle817/rdbms/log/ora_21022_suneel.trc
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
ORACLE_HOME = /hcl1/oracle817
System name: AIX
Node name: freedom
Release: 3
Version: 4
Machine: 000089825800
Instance name: SUNEEL
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
21022

*** 2002-01-31 20:43:55.922
----- Dump of Process Statistics -----
User time used = 5
System time used = 13
Maximum resident set size = 13216
Integral shared text size = 59360
Integral unshared data size = 5900
Integral unshared stack size = 0
Page reclaims = 399
Page faults = 0
Swaps = 0
Block input operations = 0
Block output operations = 0
Socket messages sent = 0
Socket messages received = 0
Signals received = 0
Voluntary context switches = 13
Involuntary context switches = 19
----------------------------------------------------------------

I appreciate your kind help in this regard.

Thanks in Advance,

Best Regards,
Venkata Ramana B.
Member Technical Staff,
bvramana@ctd.hcltech.com
Re: Create database, svrmgrl are taking very long time on AIX with 8.1.7 [message #69564 is a reply to message #69562] Thu, 31 January 2002 10:36 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
Off hand I would say your SGA is way too small. A good rule of thumb is to have SGA set to:
DB_BLOCK_BUFFERS=50% of total size
SHARED_POOL_SIZE=40% of total size
SHARED_POOL_RESERVED_SIZE=10% of total size

Your SGA is less than 5M. Too small. Give it 50M.

DB_BLOCK_BUFFERS=(50000000/2)/8192=3051
SHARED_POOL_SIZE=50000000 * .40 = 20000000
SHARED_POOL_RESERVED_SIZE=50000000 * .10 = 5000000
Previous Topic: HELP
Next Topic: SqlPLus batch file
Goto Forum:
  


Current Time: Thu Mar 28 18:03:18 CDT 2024