| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Oracle performance tuning question
Hi,
I'm trying to identify the bottleneck of our Orcle8.1.7 server running on HP-UX 11i.
The server has 20CPU and 16GB DRAM, HP XP1024 disk array is attached as storage.
Currently, we have install 4 fiber links between server and disk array. There are three DBs using the same disk array, but each of them runs on different HP server. I want to improve the Kernel Application DB server at first.
The oracle server is running with Async I/O.
Sometimes, we noticed the server performance is not enough for our application, because we found there is request waiting in queue for about 10 second before being processed.
with sar, It shows there seems to be more time wasted in waiting than serving.
# sar -d 5 1000
22:52:39 c0t6d0 0.40 0.50 1 3 0.00 8.75
c4t6d0 0.40 0.50 0 2 0.00 8.67
c11t12d1 17.56 2.78 81 648 13.38 10.17
c11t12d7 20.96 2.02 79 632 11.73 11.63
c11t13d5 19.56 2.48 79 634 14.41 11.33
c11t14d3 23.15 3.25 91 731 17.04 11.02
c11t15d1 22.16 1.64 87 699 9.18 10.69
c11t15d7 23.35 1.24 80 644 5.87 10.66
c12t0d5 21.36 1.84 88 701 8.69 9.17
c12t1d3 21.76 1.38 84 671 7.37 9.93
c9t2d1 20.76 1.57 86 688 7.99 9.65
c9t2d6 18.56 1.24 80 637 4.97 9.15
c9t2d7 18.56 1.16 73 586 7.16 10.53
c9t3d5 18.96 1.69 86 688 9.05 9.91
c9t4d3 19.56 2.21 86 690 11.87 9.67
c9t5d1 21.96 2.39 87 693 13.24 11.15
c9t5d7 17.76 2.14 86 688 10.96 9.46
c9t6d5 23.75 1.71 89 712 9.35 9.28
c11t12d2 10.98 0.79 25 201 2.47 15.97
c11t13d0 12.97 1.35 29 230 5.86 17.16
c11t13d6 11.18 1.18 25 198 5.05 14.92
c11t14d4 9.98 0.84 24 188 2.76 16.92
c11t15d2 11.98 1.73 26 209 8.46 17.44
c12t0d0 11.98 1.77 30 240 8.23 17.59
c12t0d6 12.38 1.20 32 254 4.82 16.70
c12t1d4 11.18 1.15 28 222 5.07 15.00
c9t2d2 11.38 0.78 25 196 3.27 16.59
c9t3d0 11.18 1.37 25 201 6.42 16.09
c9t3d4 8.78 0.69 23 187 1.80 13.22
c9t3d6 9.78 0.74 20 160 2.92 18.41
c9t4d4 10.38 0.59 19 153 4.98 17.86
c9t5d2 10.78 0.71 24 192 1.60 14.02
c9t6d0 11.98 1.64 28 222 10.24 17.91
c9t6d6 11.78 2.29 26 209 14.98 20.36
c19t0d4 37.72 17.65 135 1079 74.90 18.51
c19t0d5 36.33 16.35 130 1043 69.18 18.60
c19t0d6 36.93 14.75 128 1022 62.47 18.61
c19t0d7 36.53 18.73 135 1079 78.17 18.38
c19t1d0 36.13 18.05 122 974 74.41 18.97
c19t1d1 36.73 13.42 128 1024 60.04 18.74
c19t1d2 36.53 16.89 121 969 72.21 19.56
c19t1d3 34.93 12.86 122 977 57.89 19.20
c20t1d4 36.53 15.47 128 1022 67.87 18.25
c20t1d5 37.13 17.89 125 996 74.77 19.05
c20t1d6 36.13 14.25 125 998 61.85 18.45
c20t1d7 36.53 24.00 126 1008 95.29 19.32
c20t2d0 36.93 23.47 132 1054 95.52 18.62
c20t2d1 35.53 20.33 130 1043 79.09 18.13
c20t2d2 37.13 14.98 124 995 63.62 19.20
c20t2d3 35.53 17.68 124 993 73.76 18.63
c18t2d1 27.94 3.88 109 870 8.02 7.42
c18t2d2 30.14 3.66 116 928 7.53 7.14
c18t2d3 26.35 4.33 113 901 9.12 6.87
c18t2d4 31.34 3.13 112 896 6.34 7.23
c21t2d5 30.74 2.84 108 861 6.51 7.84
c21t2d6 28.14 5.07 109 870 13.59 8.48
c21t2d7 29.54 4.18 104 835 11.20 8.51
c21t3d0 29.74 5.04 104 830 12.69 8.44
c21t3d1 31.54 4.22 119 949 10.43 8.17
c21t3d2 21.36 0.50 220 4106 0.00 1.00
c18t0d7 12.38 26.97 91 746 48.75 9.57
c21t1d0 14.37 22.90 83 666 56.06 11.46
Should I enable LOCK_SGA ? and what should be the start-point of
optimization?
Should I reconstruct DB files to larger size and fewer number ?
Regards
Joe
The following is db comfiguration:
db_name = "codb"
instance_name = codb
service_names = co65db
control_files = ("/oracle/oradata/codb/control/rlvcontrol01", "/oracle/ oradata/codb/control/rlvcontrol02", "/oracle/oradata/pi n65db/control/rlvcontrol03")
open_cursors = 500
max_enabled_roles = 30
shared_pool_size = 800000000
large_pool_size = 30M
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
#processes = 600
processes = 1200
log_buffer = 5242880 log_archive_start = true log_archive_dest_1 = "location=/arch/codb" log_archive_format = arch_%t_%s.arc
#DBCA uses the default database value (30) for max_rollback_segments
#100 rollback segments (or more) may be required in the future
#Uncomment the following entry when additional rollback segments are
created and made online
#max_rollback_segments = 101
# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
rollback_segments =
(COROLL00,COROLL01,COROLL02,COROLL03,COROLL04,COROLL05,COROLL06,COROLL07,COROLL08,COROLL09,COROLL10,P
INROLL11,COROLL12,COROLL13,COROLL14,COROLL15,COROLL16,COROLL17,COROLL18,COROLL19,COROLL20,COROLL21,COROLL22,COROLL23,PIN
ROLL24,COROLL25,COROLL26,COROLL27,COROLL28,COROLL29,COROLL30,COROLL31,COROLL32,COROLL33,COROLL34,COROLL35,COROLL36,PINRO
LL37,COROLL38,COROLL39,COROLL40,COROLL41,COROLL42,COROLL43,COROLL44,COROLL45,COROLL46,COROLL47,COROLL48,COROLL49,COROLL
50,COROLL51,COROLL52,COROLL53,COROLL54,COROLL55,COROLL56,COROLL57,COROLL58,COROLL59,COROLL60,COROLLBIG,COROLL61,COROLL6
2,COROLL63,COROLL64,COROLL65,COROLL66,COROLL67,COROLL68,COROLL69,COROLL70,COROLL71,COROLL72,COROLL73,COROLL74,COROLL75,
COROLL76,COROLL77,COROLL78,COROLL79,COROLL80,COROLL81,COROLL82,COROLL83,COROLL84,COROLL85,COROLL86,COROLL87,COROLL88,PI
NROLL89,COROLL90,COROLL91,COROLL92,COROLL93,COROLL94,COROLL95,COROLL96,COROLL97,COROLL98,COROLL99)
# Global Naming -- enforce that a dblink has same name as the db it
connects to
global_names = true
# Uncomment the following line if you wish to enable the Oracle Trace
product
# to trace server activity. This enables scheduling of server
collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as
enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true
# define directories to store trace and alert files
background_dump_dest = /oracle/app/oracle/admin/codb/bdump
core_dump_dest = /oracle/app/oracle/admin/codb/cdump
user_dump_dest = /oracle/app/oracle/admin/codb/udump
db_block_size = 4096
remote_login_passwordfile = exclusive
os_authent_prefix = ""
compatible = "8.1.0"
sort_area_size = 65536
sort_area_retained_size = 65536
disk_asynch_io = TRUE
db_files=1024
#lock_sga=true
#
db_block_lru_latches=12
audit_trail = false
transaction_auditing = false
optimizer_mode = rule
db_file_multiblock_read_count = 16
db_writer_processes=4
parallel_execution_message_size=8192
sort_multiblock_read_count = 8
UTL_FILE_DIR=/oracle/migrate
#
HPUX_SCHED_NOAGE = 178
Received on Sun Jul 29 2007 - 10:07:45 CDT
 |
 |