Re: Oracle performance tuning question

On Sun, 29 Jul 2007 08:07:45 -0700, "jshen.cad_at_gmail.com" <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
Setting lock_sga to true would be yet another rash measure. You would need to closely verify your current parameters. To mention a few things with the worst first: your db_block_size is 4k, while as far as I remember HP always reads 8k blocks. So, even if you do a single block read and ask for 4k, HP will still read 8k, and simply transfer only 4k to Oracle. Changing db_block_size requires rebuilding the database, so beware.

db_file_multiblock_read_count seems too small, as HP can read 1 Mb ahead.

shared_pool_size is probably way too big. Did you ever query v$sgastat to find out how much shared pool is free?

Large_pool is set to 30M and you don't use it, as you are not using MTS
sort_area_size is 64k, should be 1 M.

But then the biggest problem of them all: you didn't set db_cache_size or db_block_buffers. The default is only 200 blocks.
You should verify this parameter at once! You are not using any cache!
I'm not surprised you are waiting for disk big time.

I would also follow up Daniels advice and look to migrate to 10g, at the same time dumping the Rule Based Optimizer.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA