Hi,

I have a really bad performance on the database as more users got into the system (> 50 users) or after the bulk load or bulk import. I'm not a pure DBA, I'd need help on tuning the server (at least with buffer cache, shared pool size, and pga).
Below is my AWR Report

Thanks in Advance

Buffer Cache: 1,768M Std Block Size: 8K
Shared Pool Size: 352M Log Buffer: 5,120K

Load Profile

Per Second Per Transaction
Redo size: 1,131.67 3,025.06
Logical reads: 169.48 453.03
Block changes: 6.54 17.49
Physical reads: 0.18 0.49
Physical writes: 0.39 1.04
User calls: 9.65 25.79
Parses: 3.22 8.61
Hard parses: 0.17 0.45
Sorts: 1.01 2.71
Logons: 0.05 0.13
Executes: 6.09 16.28
Transactions: 0.37


% Blocks changed per Read: 3.86 Recursive Call %: 60.19
Rollback per transaction %: 18.47 Rows per Sort: 40.95

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.07 In-memory Sort %: 100.00
Library Hit %: 97.80 Soft Parse %: 94.82
Execute to Parse %: 47.09 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 95.10 % Non-Parse CPU: 91.53

Shared Pool Statistics

Begin End
Memory Usage %: 75.62 79.10
% SQL with executions>1: 95.39 93.05
% Memory for SQL w/exec>1: 93.56 91.78

Top 5 Timed Events

Event Waits Time(s) Percent Total DB Time Wait Class
class slave wait 122 624 3,490.44 Other
CPU time 11 64.05
control file parallel write 1,180 3 19.57 System I/O
control file sequential read 544 2 13.80 System I/O
log file parallel write 1,358 2 10.52 System I/O

SGA Memory Summary
SGA regions Size in Bytes
Database Buffers 1,853,882,368
Fixed Size 792,964
Redo Buffers 5,242,880
Variable Size 556,000,892

init.ora Parameters
Parameter Name Begin value End value (if different)
__db_cache_size 1853882368
__java_pool_size 16777216
__large_pool_size 58720256
__shared_pool_size 369098752
_shared_pool_reserved_pct 6
background_dump_dest C:\ORACLE\PRODUCT\10.1.0\ADMIN\V5
compatible 10.1.0.2.0
control_files C:\ORACLE\PRODUCT\10.1.0\V5\V5\CO
core_dump_dest C:\ORACLE\PRODUCT\10.1.0\ADMIN\V5
db_block_size 8192
db_cache_size 16777216
db_domain
db_file_multiblock_read_count 16
db_name V5
db_recovery_file_dest C:\oracle\product\10.1.0\flash_re
db_recovery_file_dest_size 21474836480
dispatchers (protocol=TCP)
fast_start_mttr_target 26
java_pool_size 16777216
job_queue_processes 20
large_pool_size 58720256
log_buffer 5242880
open_cursors 2000
pga_aggregate_target 896147200
processes 900
remote_login_passwordfile SHARED
resource_limit FALSE
session_cached_cursors 100
sessions 995
sga_max_size 2415919104
sga_target 2306867200
shared_pool_size 369098752
shared_servers 25
sort_area_size 65536
transactions 900
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest C:\ORACLE\PRODUCT\10.1.0\ADMIN\V5