 |
Click
here to order the book online! |
|
|
 |
Oracle9i UNIX
Administration Handbook
Donald K.
Burleson
Administer Oracle9i on all of the major UNIX platforms, including Solaris,
HP-UNIX and IBM-UNIX, and Linux. |
Descriptions of useful Oracle scripts from Oracle9i UNIX Administration
Handbook by Oracle Press.
This is a collection if references to useful scripts that can be found in the
Oracle9i UNIX Administration Handbook, (c) 2002 by Oracle Press.
Trend-based vmstat alert scripts for UNIX
The SQL script vmstat_alert.sql can quickly give a complete exception
report on all of the servers in our Oracle environment. This report will display
times when the CPU and RAM memory exceed your predefined thresholds.
The standard vmstat alert report is used to alert the
Oracle DBA and systems administrator to out-of-bounds conditions on each Oracle
server. These conditions include:
·
CPU waits > 40% (AIX version only) This
may indicate I/O-based contention. The solution is to spread files across more
disks or add buffer memory.
·
Runqueue > xxx – (where xxx is the number of CPUs on the
server, 2 in this example) This
indicates an overloaded CPU. The solution is to add additional processors to the
server.
·
Page_in > 2 Page-in
operations indicate overloaded memory. The solution is to reduce the size of the
Oracle SGA, PGA, or add additional RAM memory to the server.
·
User CPU + System CPU > 90% This
indicates periods where the CPU is highly utilized.
While the SQL here is self-explanatory, let’s look at a
sample report and see how it will help our systems administrator monitor the
server’s behavior:
SQL> @vmstat_alert 7
Wed Dec 20 page
1
run queue > 2
May indicate an overloaded CPU.
When runqueue exceeds the number of CPUs
on the server, tasks are waiting for service.
SERVER_NAME date hour runq
pg_in pg_ot usr sys idl
--------------- -------------------- ---- ----- ----- ---- ---- ----
AD-01 00/12/13 17 3 0 0 87 5 8
Wed Dec 20 page
1
page_in > 1
May indicate overloaded memory.
Whenever Unix performs a page-in, the RAM memory
on the server has been exhausted and swap pages are being used.
SERVER_NAME date hour runq pg_in pg_ot usr sys idl
----------------- -------------------- ---- ----- ----- ---- ---- ----
AD-01 00/12/13 16 0 5 0 1 1 98
AD-01 00/12/14 09 0 5 0 10 2 88
AD-01 00/12/15 16 0 6 0 0 0 100
AD-01 00/12/19 20 0 29 2 1 2 98
PROD1DB 00/12/13 14 0 3 43 4 4 93
PROD1DB 00/12/19 07 0 2 0 1 3 96
One of the jobs of the Oracle tuning expert is to monitor
the database and the server for regular trends. This is not just an exercise in
searching for trends because every database will exhibit regular patterns of CPU
and memory consumption.
Using the stats$vmstat table, it is very easy to write a query that will
aggregate the CPU and memory. Below is a sample SQL script that aggregates
server values:
To get details on this technique,
Click here to order the book online!
|
