Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: Charles Hooper <>
Date: Fri, 08 Jun 2007 04:49:31 -0700
Message-ID: <>

On Jun 7, 11:09 pm, Charles Hooper <> wrote:
> On May 24, 5:56 pm, 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 -
> 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.

In another post in the same thread, the OP also mentions this:
> There is anti-virus software on this machine, but I don't think it
> accounts for a significant part of the 2000+ IOPs per second. We use
> the same product on all of our servers. IO is not a problem on any
> other server. On this machine, there are peaks of 10,000 IOPs. This
> seems abnormally high.

It might be a good idea to verify that the virus scanner is not attempting to real-time scan the Oracle data, undo, redo, archived redo, or other files related to Oracle. There are a fairly large number of tablespaces defined - is that intentional? Table data separated from index data?

Reviewing what I wrote last night, I see that SGA_MAX_SIZE is defined, but not SGA_TARGET - is that correct?

If you plan to change settings, make changes slowly and analyze the effect of those changes, possibly starting with the virus scanner. Then, clearly identify specific business critical processes that are slow, and the sessions that are affected by the performance of the specific business critical processes that are slow. Analyze the performance specifically for those sessions, using the Oracle wait event interface, 10046 trace files, and possibly 10053 trace files. This is a more focused approach to performance tuning than scanning through a Statspack report that covers a 30 minute time interval for all sessions, and trying to determine the relevance of the top wait event, "SQL*Net message from client", in that context.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jun 08 2007 - 06:49:31 CDT

Original text of this message