Re: Oracle performance tuning question

jshen.cad_at_gmail.com wrote:
> 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
The best thing you could do to improve performance is upgrade to a version of the Oracle database supported during the current millennium.

Why not invest the efforts into moving to 10g.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org