Oracle performance tuning question

Giganews Newsgroups
Subject: Oracle performance tuning question
Posted by:  jshen.c…@gmail.com (jshen.c…@gmail.com)
Date: Sun, 29 Jul 2007

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

Replies