I have a 9i system that I maintain. Some issues that have come up are
out of my league.
Below is the output of a db_tune.sql script.
Anything seem unusual. Biggest problem I am having is when I restart
oracle, thins are okay for about 10 minutes, then eventually all the
oracle pids stay in run mode and have high cpu usage. The java apps
become unresponsive. I can go in with sqlplus and restart, but the
problem just comes right back. Platform is Solaris 8.
If anyone thinks they can help, reply back. I am willing to pay
someone for a few hours of work to try and resolve this.
Here is db_tune.sql outout:
=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2007.03.07 16:10:38
=~=~=~=~=~=~=~=~=~=~=~=
@/usr/local/oracle/OraHome/admin/factory/scripts/db_tune.sql
Hit Ratio Section
BUFFER HIT RATIO
(should be > 70, else increase db_block_buffers in init.ora)
Buffer Hit Ratio
82
round(100 * ((a.value+b.value)-c.value) /
*
ERROR at line 4:
ORA-01476: divisor is equal to zero
DATA DICT HIT RATIO
(should be higher than 90 else increase shared_pool_size in init.ora)
Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
--------------- ----------------------- -------------------------
193,392 1,799 99
=========================
LIBRARY CACHE MISS RATIO
(If > .1, i.e., more than 1% of the pins resulted in reloads, then
increase the shared_pool_size in init.ora)
executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------ ---------------------------- ------------------------
96,958 16 .0002
=========================
Library Cache Section
hit ratio should be > 70, and pin ratio > 70 ...
NAMESPACE Hit ratio pin hit ratio reloads
--------------- ---------- ------------- ------------
SQL AREA 73 81 16
TABLE/PROCEDURE 96 96 0
BODY 98 97 0
TRIGGER 95 95 0
INDEX 0 0 0
CLUSTER 95 96 0
OBJECT 100 100 0
PIPE 100 100 0
JAVA SOURCE 100 100 0
JAVA RESOURCE 50 50 0
JAVA DATA 0 66 0
=========================
REDO LOG BUFFER
redo log space requests 0
NAME BYTES
-------------------------- ----------------
free memory 98,704,616
free memory 25,092,096
****************************************************
SQL Summary Section
Tot SQL run since startup SQL executing now
------------------------- -----------------
22,881 8
=========================
ROLLBACK SEGMENT CONTENTION
If any ratio is > .01 then more rollback segments are needed
NAME WAITS GETS Ratio
------------------------------ ---------- ---------- ---------
SYSTEM 0 57 .00000
_SYSSMU1$ 0 65 .00000
_SYSSMU2$ 0 69 .00000
_SYSSMU3$ 0 65 .00000
_SYSSMU4$ 0 67 .00000
_SYSSMU5$ 0 67 .00000
_SYSSMU6$ 0 67 .00000
_SYSSMU7$ 0 67 .00000
_SYSSMU8$ 0 68 .00000
_SYSSMU9$ 0 67 .00000
_SYSSMU10$ 0 67 .00000
****************************************************
Session Event Section
if average-wait > 0 then contention exists
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
latch free 1 1
2
latch free 792 787
5
latch free 570 569
5
latch free 647 646
6
latch free 223 223
5
latch free 951 950
5
latch free 789 780
5
latch free 475 472
5
latch free 652 646
5
latch free 7 7
5
latch free 38 38
8
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
latch free 40 40
6
latch free 30 28
8
latch free 493 493
5
latch free 6 6
8
latch free 530 530
5
latch free 477 477
5
pmon timer 639 541
232
rdbms ipc message 730 489
203
rdbms ipc message 494 491
299
rdbms ipc message 8 4
15000
rdbms ipc message 3 2
2100
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
rdbms ipc message 1,196 964
124
async disk IO 4 0
3
async disk IO 184 0
3
control file parallel write 8 0
2
control file parallel write 490 0
2
buffer busy waits 47 0
11
buffer busy waits 32 0
17
buffer busy waits 78 0
10
buffer busy waits 12 0
14
buffer busy waits 46 0
15
buffer busy waits 28 0
21
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
buffer busy waits 21 0
14
buffer busy waits 1 0
6
buffer busy waits 10 0
14
buffer busy waits 64 0
8
buffer busy waits 26 0
17
buffer busy waits 98 0
11
log file single write 8 0
2
log file parallel write 237 237
1
log file sync 12 0
2
log file sync 22 0
3
log file sync 1 0
4
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
log file sync 11 0
2
log file sync 3 0
2
log file sync 1 0
3
log file sync 2 0
1
log file sync 26 0
3
log file sync 17 0
4
log file sync 1 0
2
log file sync 7 0
3
log file sync 23 0
3
db file sequential read 29 0
1
db file parallel write 162 162
5
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
direct path write 51 0
9
direct path read (lob) 1 0
1
direct path read (lob) 3 0
4
smon timer 10 4
13548
row cache lock 1 0
4
library cache pin 2 0
4
library cache pin 2 0
1
library cache pin 2 0
1
library cache pin 2 0
1
library cache pin 2 0
2
library cache pin 2 0
1
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
SQL*Net message from client 1,119 0
79
SQL*Net message from client 4,049 0
17
SQL*Net message from client 230 0
432
SQL*Net message from client 25 0
73
SQL*Net message from client 49 0
2230
SQL*Net message from client 55 0
1804
SQL*Net message from client 522 0
154
SQL*Net message from client 150 0
583
SQL*Net message from client 123 0
814
SQL*Net message from client 13 0
7566
SQL*Net message from client 192 0
496
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
AVERAGE_WAIT
------------------------------ ------------ --------------
SQL*Net message from client 4,283 0
18
SQL*Net message from client 88 0
1126
SQL*Net message from client 4,237 0
16
SQL*Net message from client 477 0
197
SQL*Net message from client 3,645 0
19
SQL*Net message from client 103 0
971
83 rows selected.
Queue Section
average wait for queues should be near zero ...
no rows selected
Multi-threaded Server Section
If the following number is > 1
then increase MTS_MAX_SERVERS parm in init.ora
no rows selected
If the following number increases, consider adding dispatcher
processes
Avg wait per response queue
hundredths of seconds
DISPATCHER USAGE
(If Time Busy > 50, then change
MTS_MAX_DISPATCHERS in init.ora)
Shared Server Processes
0
high-water mark for the multi-threaded server
MAXIMUM_CONNECTIONS MAXIMUM_SESSIONS SERVERS_STARTED
SERVERS_TERMINATED
------------------- ---------------- ---------------
SERVERS_HIGHWATER
0 0 0
0
0
****************************************************
file i/o should be evenly distributed across drives.
# Name STATUS BYTES
PHYRDS PHYWRTS
-- ------------------------------ ------- ---------------- ----------
----------
1 /web2/database/oradata/factory SYSTEM 340,787,200
3007 28
2 /web2/database/oradata/factory ONLINE 1,661,992,960
346 338
3 /web2/database/oradata/factory ONLINE 20,971,520
3 1
4 /web2/database/oradata/factory ONLINE 20,971,520
73 1
5 /web2/database/oradata/factory ONLINE 163,840,000
3 1
6 /web2/database/oradata/factory ONLINE 26,214,400
3 1
7 /web2/database/oradata/factory ONLINE 10,485,760
3 1
8 /web2/database/oradata/factory ONLINE 56,360,960
3 1
9 /web2/database/oradata/factory ONLINE 1,073,741,824
4127 24
10 /web2/database/oradata/factory ONLINE 1,073,741,824
3 1
11 /web2/database/oradata/factory ONLINE 1,073,741,824
3 1
# Name STATUS BYTES
PHYRDS PHYWRTS
-- ------------------------------ ------- ---------------- ----------
----------
12 /web2/database/oradata/factory ONLINE 5,242,880
3 1
13 /web2/database/oradata/factory ONLINE 52,428,800
3 1
14 /web2/database/oradata/factory ONLINE 52,428,800
3 1
15 /web2/database/oradata/factory ONLINE 524,288,000
436399 1
16 /web2/database/oradata/factory ONLINE 524,288,000
19592 3
17 /web2/database/oradata/factory ONLINE 104,857,600
3 1
18 /web2/database/oradata/factory ONLINE 209,715,200
3 1
19 /web2/database/oradata/factory ONLINE 104,857,600
3 1
20 /web2/database/oradata/factory ONLINE 3,221,225,472
3 1
21 /web2/database/oradata/factory ONLINE 209,715,200
3 1
22 /web2/database/oradata/factory ONLINE 524,288,000
384786 5
# Name STATUS BYTES
PHYRDS PHYWRTS
-- ------------------------------ ------- ---------------- ----------
----------
23 /web2/database/oradata/factory ONLINE 104,857,600
3 1
24 /web2/database/oradata/factory ONLINE 314,572,800
3 1
25 /web2/database/oradata/factory ONLINE 524,288,000
3 1
26 /web2/database/oradata/factory ONLINE 524,288,000
472090 19
27 /web2/database/oradata/factory SYSTEM 314,572,800
20 1
28 /web2/database/oradata/factory ONLINE 1,073,741,824
3 1
29 /web2/database/oradata/factory ONLINE 1,073,741,824
3 1
30 /web2/database/oradata/factory ONLINE 1,073,741,824
3 1
31 /web2/database/oradata/factory ONLINE 1,073,741,824
3 1
32 /web2/database/oradata/factory ONLINE 1,073,741,824
103076 66
33 /web2/database/oradata/factory ONLINE 524,288,000
3 1
SYSTEM_STATISTIC
VALUE
CPU used by this session
66,703
CPU used when call started
66,641
CR blocks created
1
Cached Commit SCN referenced
0
Commit SCN cached
0
DBWR buffers scanned
266,487
DBWR checkpoint buffers written
0
DBWR checkpoints
0
DBWR cross instance writes
0
DBWR free buffers found
266,089
DBWR fusion writes
0
SYSTEM_STATISTIC
VALUE
DBWR lru scans
138
DBWR make free requests
138
DBWR revisited being-written buffer
0
DBWR summed scan depth
266,487
DBWR transaction table writes
161
DBWR undo block writes
179
DDL statements parallelized
0
DFO trees parallelized
0
DML statements parallelized
0
OS All other sleep time
0
OS Chars read and written
0
SYSTEM_STATISTIC
VALUE
OS Data page fault sleep time
0
OS Input blocks
0
OS Involuntary context switches
0
OS Kernel page fault sleep time
0
OS Major page faults
0
OS Messages received
0
OS Messages sent
0
OS Minor page faults
0
OS Other system trap CPU time
0
OS Output blocks
0
OS Process heap size
0
SYSTEM_STATISTIC
VALUE
OS Process stack size
0
OS Signals received
0
OS Swaps
0
OS System call CPU time
0
OS System calls
0
OS Text page fault sleep time
0
OS User level CPU time
0
OS User lock wait sleep time
0
OS Voluntary context switches
0
OS Wait-cpu (latency) time
0
PX local messages recv'd
0
SYSTEM_STATISTIC
VALUE
PX local messages sent
0
PX remote messages recv'd
0
PX remote messages sent
0
Parallel operations downgraded 1 to 25 pct
0
Parallel operations downgraded 25 to 50 pct
0
Parallel operations downgraded 50 to 75 pct
0
Parallel operations downgraded 75 to 99 pct
0
Parallel operations downgraded to serial
0
Parallel operations not downgraded
0
SQL*Net roundtrips to/from client
20,080
SQL*Net roundtrips to/from dblink
0
SYSTEM_STATISTIC
VALUE
Unnecesary process cleanup for SCN batching
0
background checkpoints completed
0
background checkpoints started
0
background timeouts
1,489
branch node splits
0
buffer is not pinned count
14,847,235
buffer is pinned count
19,154,623
bytes received via SQL*Net from client
1,853,688
bytes received via SQL*Net from dblink
0
bytes sent via SQL*Net to client
5,109,110
bytes sent via SQL*Net to dblink
0
SYSTEM_STATISTIC
VALUE
calls to get snapshot scn: kcmgss
48,916
calls to kcmgas
286
calls to kcmgcs
5
calls to kcmgrs
0
change write time
22
cleanouts and rollbacks - consistent read gets
0
cleanouts only - consistent read gets
2
cluster key scan block gets
1,869
cluster key scans
1,317
cold recycle reads
0
commit cleanout failures: block lost
0
SYSTEM_STATISTIC
VALUE
commit cleanout failures: buffer being written
0
commit cleanout failures: callback failure
0
commit cleanout failures: cannot pin
0
commit cleanout failures: hot backup in progress
0
commit cleanout failures: write disabled
0
commit cleanouts
471
commit cleanouts successfully completed
471
consistent changes
2
consistent gets
20,638,743
consistent gets - examination
2,893,484
current blocks converted for CR
0
SYSTEM_STATISTIC
VALUE
cursor authentications
1,845
data blocks consistent reads - undo records applied
2
db block changes
1,628
db block gets
2,014
deferred (CURRENT) block cleanout applications
271
dirty buffers inspected
139
enqueue conversions
11
enqueue deadlocks
0
enqueue releases
2,260
enqueue requests
2,298
enqueue timeouts
0
SYSTEM_STATISTIC
VALUE
enqueue waits
0
exchange deadlocks
0
execute count
23,000
free buffer inspected
62,294
free buffer requested
3,604,437
gcs messages sent
0
ges messages sent
0
global cache blocks corrupt
0
global cache blocks lost
0
global cache claim blocks lost
0
global cache convert time
0
SYSTEM_STATISTIC
VALUE
global cache convert timeouts
0
global cache converts
0
global cache cr block build time
0
global cache cr block flush time
0
global cache cr block receive time
0
global cache cr block send time
0
global cache cr blocks received
0
global cache cr blocks served
0
global cache current block flush time
0
global cache current block pin time
0
global cache current block receive time
0
SYSTEM_STATISTIC
VALUE
global cache current block send time
0
global cache current blocks received
0
global cache current blocks served
0
global cache defers
0
global cache freelist waits
0
global cache get time
0
global cache gets
0
global cache prepare failures
0
global cache skip prepare failures
0
global lock async converts
0
global lock async gets
0
SYSTEM_STATISTIC
VALUE
global lock convert time
0
global lock get time
0
global lock releases
0
global lock sync converts
0
global lock sync gets
0
hot buffers moved to head of LRU
146,912
immediate (CR) block cleanout applications
2
immediate (CURRENT) block cleanout applications
1
index fast full scans (direct read)
0
index fast full scans (full)
0
index fast full scans (rowid ranges)
0
SYSTEM_STATISTIC
VALUE
instance recovery database freeze count
0
kcmccs called get current scn
0
kcmgss read scn without going to GES
0
kcmgss waited for batching
0
leaf node splits
0
logons cumulative
135
logons current
38
messages received
564
messages sent
562
native hash arithmetic execute
0
native hash arithmetic fail
0
SYSTEM_STATISTIC
VALUE
next scns gotten without going to GES
0
no buffer to keep pinned count
1
no work - consistent read gets
12,119,901
opened cursors cumulative
13,885
opened cursors current
316
opens of replaced files
0
opens requiring cache replacement
0
parse count (failures)
6
parse count (hard)
4,189
parse count (total)
15,939
parse time cpu
1,709
SYSTEM_STATISTIC
VALUE
parse time elapsed
3,759
physical reads
3,605,354
physical reads direct
930
physical reads direct (lob)
6
physical writes
1,334
physical writes direct
858
physical writes direct (lob)
0
physical writes non checkpoint
1,332
pinned buffers inspected
479
prefetch clients - 16k
0
prefetch clients - 2k
0
SYSTEM_STATISTIC
VALUE
prefetch clients - 32k
0
prefetch clients - 4k
0
prefetch clients - 8k
0
prefetch clients - default
0
prefetch clients - keep
0
prefetch clients - recycle
0
prefetched blocks
2,180,272
prefetched blocks aged out before use
0
process last non-idle time
145,489,335,172
queries parallelized
0
recovery array read time
0
SYSTEM_STATISTIC
VALUE
recovery array reads
0
recovery blocks read
0
recursive calls
128,601
recursive cpu usage
1,643
redo blocks written
664
redo buffer allocation retries
0
redo entries
821
redo log space requests
0
redo log space wait time
0
redo log switch interrupts
0
redo ordering marks
0
SYSTEM_STATISTIC
VALUE
redo size
241,208
redo synch time
577
redo synch writes
217
redo wastage
87,964
redo write time
655
redo writer latching time
0
redo writes
237
remote instance undo block writes
0
remote instance undo header writes
0
rollback changes - undo records applied
0
rollbacks only - consistent read gets
1
SYSTEM_STATISTIC
VALUE
rows fetched via callback
1,015,766
serializable aborts
0
session connect time
145,489,335,172
session cursor cache count
0
session cursor cache hits
0
session logical reads
20,640,749
session pga memory
45,150,840
session pga memory max
45,150,840
session stored procedure space
0
session uga memory
10,980,560
session uga memory max
39,110,520
SYSTEM_STATISTIC
VALUE
shared hash latch upgrades - no wait
5,611,299
shared hash latch upgrades - wait
6
sorts (disk)
3
sorts (memory)
4,170
sorts (rows)
373,535
summed dirty queue length
1
switch current to new buffer
2
table fetch by rowid
9,370,714
table fetch continued row
2,944,412
table lookup prefetch client count
0
table scan blocks gotten
4,954,463
SYSTEM_STATISTIC
VALUE
table scan rows gotten
77,787,745
table scans (cache partitions)
0
table scans (direct read)
0
table scans (long tables)
663
table scans (rowid ranges)
0
table scans (short tables)
2,049
total file opens
0
total number of slots
0
transaction lock background get time
0
transaction lock background gets
0
transaction lock foreground requests
0
SYSTEM_STATISTIC
VALUE
transaction lock foreground wait time
0
transaction rollbacks
0
transaction tables consistent read rollbacks
0
transaction tables consistent reads - undo records appl
0
user calls
28,036
user commits
201
user rollbacks
0
workarea executions - multipass
0
workarea executions - onepass
0
workarea executions - optimal
0
workarea memory allocated
0
SYSTEM_STATISTIC
VALUE
write clones created in background
0
write clones created in foreground
2
SQL>
Received on Wed Mar 07 2007 - 15:17:56 CST
