Re: Oracle Performance -- Possible Disk Bottleneck

On May 24, 5:56 pm, beth.sto..._at_gmail.com wrote:
>We've been having performance problems with our Client/Server
>application for months. Users contantly complain of slow response
>times to their queries.

>Here's the environment:
>Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
>Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
>CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
>stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
>backend. Users connect using FastEthernet. XP clients. All disks on
>the SAN are fibre channel.

>CPU utilization is fine. RAM utilization is fine. Throughput on the
>NIC is fine -- maxes out at 50 Mbps for a short while when users first
>log in in the morning. Averages are 20 Mbps.

I still do not like sifting through Statspack reports (ever since reading Cary Millsap's book a couple years ago, I prefer other methods). I will pull out a couple items from the report that look interesting:

• TNS for 32-bit Windows: Version 9.2.0.7.0 -

32 bit Windows with 8GB of memory - it is likely better to stick with 4GB, or jump to 16GB.

• db_cache_size 209,715,200
• db_writer_processes 4
• hash_join_enabled FALSE
• large_pool_size 100,663,296
• log_buffer 12,288,000
• log_checkpoint_interval 10000
• optimizer_index_caching 90
• optimizer_index_cost_adj 20
• optimizer_mode RULE
• pga_aggregate_target 50,000,000
• sga_max_size 1,665,379,432
• shared_pool_size 209,715,200
• sort_area_size 5,120,000
  • Multiple DB writer processes are recommended for systems with multiple CPUs (at least one DB writer for every 8 CPUs)
  • HASH_JOIN_ENABLED controls whether the CBO is permitted to use a Hash Join when multiple row sets are joined.
  • LARGE_POOL_SIZE specifies (in bytes) the size of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes (RMAN) for disk I/O buffers. Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.
  • LOG_BUFFER - On most systems, sizing the log buffer larger than 1MB does not provide any performance benefit. LGWR begins to write entries from the redo log buffer to the online redo log if any of the following are true: the log buffer becomes 1/3 full, LGWR is posted by a server process performing a COMMIT or ROLLBACK, or if DBWR posts LGWR to do so.
  • LOG_CHECKPOINT_INTERVAL parameter specifies the maximum number of operating system redo blocks the incremental checkpoint target should lag the current log tail. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another.
  • OPTIMIZER_INDEX_CACHING - may be important if OPTIMIZER_MODE were not RULE, as DB_CACHE_SIZE may not be large enough to cache 90% of the index blocks.
  • OPTIMIZER_MODE - RULE uses indexes, even if those are not the best access method, why RULE?
  • PGA_AGGREGATE_TARGET defines the total PGA memory allocation for the instance - this is less than ten times the value of SORT_AREA_SIZE, which is per sorting operation.
  • SGA_MAX_SIZE looks very big for a 32 bit platform, especially considering the size of PGA_AGGREGATE_TARGET and DB_CACHE_SIZE.
  • SHARED_POOL_SIZE might be fine.

The largest wait event of interest:

Event Name                               Count    Total Time
-------------------------------- ------------- -------------
db file sequential read                3742459      2164.09s

A sequential read is a single-block read, where a user process is reading a buffer into the SGA buffer cache and waiting for a physical I/O, usually caused by an index access.

                             Total     Per Tranaction
physical reads             4873242     10047.92
physical reads direct         5328        10.99
recursive calls            1443176      2975.62

Look over the information that appears above. Are there any changes that you can make to the database instance to improve performance?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jun 07 2007 - 22:09:15 CDT