4 of 4
10K
[reformatted in plain MS DOS text]
Paper 143: Oracle8 on NT - Tips and Techniques
Bruce McCartney and Steve Recsky
DBCORP Information Systems Inc.
...
[begin 4 of 4]
Performance Tuning
Performance tuning for Oracle on NT is the same as tuning Oracle on
Unix. You can use any of the 'standard' statistic gathering
methodologies such as the V$ tables and Oracle Enterprise Manager. In
addition, you can use Oracle Performance Manager or other 3rd party
products such as Precise/SQL to help with the task of analysing
performance.
***** It is important to make sure you are collecting data
on a regular basis to ensure a base line of performance.
There are several general systems setting on an NT server which you
can ensure that NT is configured as well as possible for database
server performance. To make Windows NT virtual memory perform as well
as possible, it is best to have a large page file. The file
pagefile.sys is used for managing virtual memory via the operating
system.
***** You may need to configure the page file to be as
big as largest datafile to use OCOPY, as OCOPY.EXE
uses memory to copy the datafile.
To change the page file right click on My Computer and select
Properties and go to the Performance Tab. Also, using
Control Panel | Network Server | Configuration
select optimize for Network Applications instead of File sharing,
and remove unused protocols. Also set the Performance Boost Foreground
Applications to NONE.
A couple of Oracle settings via initSID.ORA are pre_page_sga to load
the entire SGA into memory (we'll see why in a minute) and
timed_statistics to get accurate timings in various Oracle performance
reporting utilities.
One unique performance monitoring tools for Windows NT is called the
Performance Monitor or Perfmon for short. Using Perfmon, you can
interactively monitor charts containing counters of various performance
metrics such as CPU use and DISK activity.
Oracle8 has integrated its own objects into the Perfmon tool - creating
a program entry called Oracle8 Performance Monitor. In order gather
statistics from Oracle, the performance manager needs to connect to
Oracle. This is done via the registry entries mentioned previously in
this paper - and creates a security exposure that you can avoid by
creating a PERFMON user that simply has CREATE SESSION and
SELECT ANY TABLE privileges.
***** If you have connection problems with Perfmon
(i.e. no Oracle Objects are shown) check the file
%ORACLE_HOME%\dbs\PERF80.LOG file for error messages.
In order to use Perfmon, it is useful to measure the statistics over a
period of time to get an overview of the situation. You can configure
Perfmon to save its statistics to a LOG file for later review, and
export to an analysis tools such as Excel. This approach is
recommended for developing a base line. We will now look at the main
tuning aspects of MEMORY, CPU, I/O and Oracle Resources.
Memory
Tuning memory on NT is the single most important area to look at first.
Since NT is a 32 bit operating system, it can address 4GB of memory,
2GB of which are reserved for the operating system. Thus there is a
maximum of 2GB available to applications (including Oracle). Oracle's
memory consumption is primarily a function of the Shared Global Area or
SGA. We will discuss how to change the SGA later, but to manage memory
use, you need to ensure the SGA must fit into physical memory, and you
have a large enough page file. If your system is consuming more memory
that is physically available, you will observe a phenomenon know as
paging. You can isolate paging activity by placing the page file on a
separate volume. Using Perfmon, you can watch Memory: page faults/sec,
pages input/sec and pages read/sec. If you are seeing more than 5 page
faults per second over time, you have a paging problem. Another easy
indication of paging can be found on the Performance Tab of the task
manager, which shows Physical and Virtual memory use at a point in time.
***** To address paging problems you must either reduce
consumption (shrink SGA, remove unnecessary
services/protocols etc.) and/or add more memory.
Do not allow your system to continuously page fault!
CPU
CPU bottlenecks are easily observed in Perfmon (or Task Manager). If
you CPU is consistently over 90% busy during normal processing, you
have a CPU problem. You can use Perfmon to see where CPU is spent; it
is best that the CPU is spent in USER rather than SYSTEM time. Excess
SYSTEM time may indicate unnecessary overhead in the I/O or Oracle
subsystems.
***** To address CPU bottlenecks application or SQL tuning
may be required. You can also upgrade the speed or
number of CPUs to take advantage of multiprocessing.
In addition, Oracle8 provides for many operations to
be executed in parallel.
I/O
The overall I/O tuning objective is to spend as little time doing I/O as possible.
This is accomplished by providing
the system with enough memory and making sure that the I/O that occurs is as fast as
possible. To ensure fast I/O,
you must avoid having critical files on busy devices.
***** Place REDO logs on separate fast devices, separate
DATA and INDEX datafiles and avoid RAID 5 for Oracle
files.
In order to monitor disk activity, it is recommended that you use the
INIT.ORA parameter timed_statistics and the Perfmon tool. In order to
view disk activity in Perfmon, you must enable statistic calculations
by executing the command DISKPERF -YE. Use DISKPERF -N to disable
statistic gathering.
Within Oracle, the view V$Filestat will provide disk statistics such
as number of read/writes and associated timings at the datafile level.
This can be used to identify unbalanced I/O across the database.
Use Perform to determine if disk activity is too high for your hardware.
Assuming the system is not paging: you must look at the PhysicalDisk
Disk Queue Length; Disk Transfer/Sec and %Disk Time to see if you are
attempting to exceed your disk's maximum transfer rate.
Oracle Specific counters
Oracle has provided a set of Perfmon objects for monitoring what is
going on in Oracle. Following is a brief explanation of these counters.
Object Counter Description
------------------------ --------------- -----------------------------
Oracle8 Buffer Cache %physreads/gets The objective is to have the
cache miss ratio < 10%, if it
is too high increase
DB_BLOCK_BUFFERS in the
INIT.ORA file.
------------------------ --------------- -----------------------------
Oracle8 Dictionary Cache %getmisses/gets The objective is to have miss
ratio < 10%
------------------------ --------------- -----------------------------
Oracle8 Data Files phyrds/sec, Can add a chart entry for each
phywrts/sec datafile, showing the rate at
which read/writes are being done.
------------------------ --------------- -----------------------------
Oracle8 DBWR stats1 Buffer Indication of Database writer
scanned/sec; activity.
LRU scans/sec
------------------------ --------------- -----------------------------
Oracle8 DBWR stats 2 Checkpoints/sec, Indication of how much time
timeouts/sec Oracle is spending processing
checkpoints - may need to
increase LOG file size to reduce.
------------------------ --------------- -----------------------------
Oracle8 Dynamic Space Recursive Sustained high rate indicates
Mgmt calls/sec dynamic space management, you
should review space allocation
parameters.
------------------------ --------------- -----------------------------
Oracle8 Free List %freelist waits Contention on freelist means
/ requests concurrent update activity is
slowing - add freelist to
updated objects.
------------------------ --------------- -----------------------------
Oracle8 Library Cache %reloads/pins The ratio should be <10% or
you should increase the
INIT.ORA parameter
SHARED_POOL_SIZE
------------------------ --------------- -----------------------------
Oracle8 redo Log Buffer Redo log space Space request should be near
requests zero, otherwise
increase LOG_BUFFER and
investigate LOG I/O.
------------------------ --------------- -----------------------------
Oracle8 Sorts Memory/ Sorts to DISK should be minimized.
disk sorts /sec Increase INIT.ORA SORT_AREA_SIZE
parameter to get more sorts into
memory.
------------------------ --------------- -----------------------------
*** [end table] ***
You see the SQL running to create the counter by looking at the file
%ORACLE_HOME%\DBS\PERF80.ORA.
Conclusion
With proper configuration, Oracle8 on NT can be managed in a similar
fashion to Oracle on Unix. Windows NT provides opportunities for
additional systems management tools and integrating into an NT
Enterprise.
###
Paper #143 / Page 12
Paper #143/ Page 11
[end 4 of 4]
[end]
