Conference eps::oracle

1504.0. "TCR & OPS hangs !!!" by NETRIX::"[email protected]" (Yves) Thu Jan 30 1997 12:45

Hi, 

We are setting up a TCR on a customer site. Everything is good with
installation of TCR and 
Oracle Parallel Server.
We try to put some load on the system, and as soon as we start an instance in
parallel mode, 
performances are catastrophic.
Catastrophic doesn't mean we are making too much I/Os, or CPUs are bound (
2x400 Mhz ) or 
Memory Channel traffic is too high. 
The machine doesnt do anything. 0 I/O, CPU Idle 98 %, Wait I/O 0%, BSC & BSS =
0 !!!!!

What about versions ??

DUnix 3.2g 
Oracle 7.3.2.3 ( 7.3.2.2 including patch 408237, dixit Oracle Support ). 
TruCluster 1.00


We tried many versions of initXXXX.ora, varying gc_* parameters. Nothing
worked.
We have run a clu_ivp and everything is reported as good.
drd_ivp is also successfull.
Failover tests work well. It really seems the cluster is in perfect state.

The cluster nodes are two 4100 bi-pro, 1Gb memory, Double MC, Double SCSI bus
with 1 HSZ40 
on each bus.
Following are some of the parameters we have set on the machines, or for the
Oracle 
Instances. 

---------------------------------------------------------------------------
//////////////////// syconfigtab on node 1 & node 2
---------------------------------------------------------------------------
io:
	basic-dma-window-size=512

rm:
	rm-no-inheritance=1

ipc:
	shm-max=838860800

vm:
	gh-chunks=100
	ubc-maxpercent=30

---------------------------------------------------------------------------
//////////////////// Oracle Private Instance parameters ( on one node )
---------------------------------------------------------------------------
db_name = DBSGS
compatible = 7.3.2.3
global_names = TRUE

rollback_segments = (rbs1,rbs2,rbs3)   

control_files=( /dev/rdrd/drd10, /dev/rdrd/drd11, /dev/rdrd/drd5 )

# Parallel Query
parallel_min_servers = 8
parallel_max_servers = 30
parallel_server_idle_time = 10
recovery_parallelism = 1

# Replication
job_queue_interval = 60
job_queue_processes = 1
job_queue_keep_connections = TRUE

# Data cache
db_block_size = 8192
db_block_buffers = 32768                    
cache_size_threshold = 32768

# Optimizer
optimizer_mode = CHOOSE
partition_view_enabled = TRUE
optimizer_percent_parallel = 0

# DBWR
db_file_multiblock_read_count = 32
_db_block_write_batch = 16
db_block_checkpoint_batch = 16

# SQL cache
shared_pool_size = 33554432           
open_cursors = 200
cursor_space_for_time = TRUE
row_cache_cursors = 64

# LGWR
log_checkpoint_interval = 10000
log_checkpoint_timeout = 3600
log_checkpoints_to_alert = YES
# log_buffer = 262144            
# log_simultaneous_copies = 0
# log_small_entry_max_size = 32
# _log_entry_prebuild_threshold = 2048
log_buffer = 1048576
log_simultaneous_copies = 4
log_small_entry_max_size = 800
_log_entry_prebuild_threshold = 2048

# User latches
processes = 100     
dml_locks = 100     
db_block_lru_latches = 2
enqueue_resources = 512

sequence_cache_entries = 10                                           # SMALL 

sequence_cache_hash_buckets = 10                                      # SMALL 


sort_direct_writes = TRUE
sort_write_buffers = 8
sort_write_buffer_size = 131072
sort_area_size = 3145728
sort_area_retained_size = 262144

# Bitmap
b_tree_bitmap_plans = TRUE
bitmap_merge_area_size = 1048576
create_bitmap_area_size = 8388608

# Hash joins
hash_join_enabled = TRUE
hash_area_size = 15728640
hash_multiblock_io_count = 8

# Trace
# audit_trail = true            # if you want auditing
timed_statistics = true       # if you want timed statistics
max_dump_file_size = 10240      # limit trace file size to 5 Meg each
# sql_trace = true
user_dump_dest=/oracle/app/oracle/admin/DBSGS/udump
background_dump_dest=/oracle/app/oracle/admin/DBSGS/bdump
core_dump_dest=/oracle/app/oracle/admin/DBSGS/cdump

# The last chance parameters ?????
use_readv = TRUE
ASYNC_WRITE = 1
SPIN_COUNT = 256
instance_number = 1
thread = 1
ifile = /oracle/app/oracle/admin/DBSGS/pfile/initps.ora

---------------------------------------------------------------------------
//////////////////// Oracle Common Instance parameters
---------------------------------------------------------------------------
gc_lck_procs = 2                     

gc_rollback_locks      = 0
gc_save_rollback_locks = 0
gc_segments            = 0
gc_tablespaces         = 0
gc_files_to_locks = 
"1=1000:2=512!257:3=2000:4=1600!32:5=1000!16:6-9=1!89472EACH:10=100!16:11-13=1
51072EACH:14=
200!257:15=50!16:16=2000"
---------------------------------------------------------------------------
//////////////////// IPCS - AM output 
---------------------------------------------------------------------------
Shared Memory:
T      ID     KEY      MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH    
SEGSZ  CPID  
LPID   ATIME    DTIME    CTIME 
m       0     5732 --rw-------      root   system     root   system      2   
524288   912  
3756 13:27:54 13:27:54 12:27:45
m       1 844348508 --rw-rw-rw-      root   system     root   system      2   
   648  2039  
2049 12:28:10 12:28:10 12:28:10
m     642 108832757 --rw-r-----    oracle      dba   oracle      dba      1   
   784  4919  
4919 15:06:13 no-entry 15:06:13
m     643 175941621 --rw-r-----    oracle      dba   oracle      dba      1
327155712  4919  
4919 15:06:13 no-entry 15:06:13


Fine grain locking was tried but didn't worked at all. We did not succeed in
our attempt to 
start the instance. An ORA-600 error occured. Oracle support told us there was
a patch on 
7.3.2.3 to solve the problem. 
Let me know if you need any other information. HSZ, LSM, DRD Services
configuration...


Any idea is welcome. This is the first production site in France !!
Regards, Yves



[Posted by WWW Notes gateway]

Yves,

	Could you be more specific? At which phase your system hangs
	(or whatever)? During nomount, mount, or open database phase?
	Do you see all background processes? Since the shared memory is
	allocated, you are through with nomount phase. Are you able to 
	start exclusive instance?
/KK/

The hang problem has been solved by applying two Oracle Patches : 

# 420001 and # 385481. 

Remember the Oracle version was 7.3.2.3 and OS version was 3.2.g.
Also keep in mind that on Digital Unix 4.0a patch #424307 is mandatory. 
It becomes a little bit complicated to install Oracle on Digital Unix. 
According to Oracle people her ein France, DEC unix is a patch record breaking
platform.
Hope it will be definitely integrated in release 7.3.3.x ??

Bye
[Posted by WWW Notes gateway]

Yves,

A quick question, have you managed to get OPS running with drd's based
on LSM rather than wraw disk partitions?  Is this what one of the
patches fixes, if so, which one?

Thanks,

Helen.