| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: tunning help needed...(timed_Stats)
Stefan,
here is the statrep taken today with timed_statistics turned on and the database is only 2 days old.
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 17 25-Mar-02 10:30:01 29
End Snap: 18 25-Mar-02 11:30:01 29
Elapsed: 60.00 (mins)
Cache Sizes
db_block_buffers: 45000 log_buffer: 524288
db_block_size: 8192 shared_pool_size: 307200000
Load Profile
~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 23,368.13 2,630.81
Logical reads: 700.47 78.86
Block changes: 159.86 18.00
Physical reads: 71.52 8.05
Physical writes: 9.34 1.05
User calls: 139.39 15.69
Parses: 30.75 3.46
Hard parses: 15.24 1.72
Sorts: 24.57 2.77
Logons: 0.00 0.00
Executes: 43.41 4.89
Transactions: 8.88
% Blocks changed per Read: 22.82 Recursive Call %: 33.38
Rollback per transaction %: 1.19 Rows per Sort: 12.28
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 89.79 In-memory Sort %: 99.98
Library Hit %: 84.35 Soft Parse %: 50.45
Execute to Parse %: 29.16 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 95.61 % Non-Parse CPU: 99.59
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 76.10 67.21
% SQL with executions>1: 57.08 66.06
Top 5 Wait Events
~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs)Wt Time
-------------------------------------------- ------------ ------------
-------
log file sync 41,672 17,671
43.80
db file sequential read 31,706 8,383
20.78
log file parallel write 41,353 7,087
17.57
db file parallel write 1,169 3,790
9.39
db file scattered read 28,357 1,495
3.71
-------------------------------------------------------------
Wait Events for DB: prod001 Instance: prod001 Snaps: 17 -18
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------
------
log file sync 41,672 6 17,671 4
1.3
db file sequential read 31,706 0 8,383 3
1.0
log file parallel write 41,353 0 7,087 2
1.3
db file parallel write 1,169 0 3,790 32
0.0
db file scattered read 28,357 0 1,495 1
0.9
direct path write 799 0 698 9
0.0
latch free 1,514 910 484 3
0.0
direct path read 4,000 0 294 1
0.1
log file sequential read 3,208 0 176 1
0.1
control file parallel write 1,192 0 82 1
0.0
enqueue 3 0 64 213
0.0
SQL*Net more data to client 8,280 0 42 0
0.3
log buffer space 3 0 33 110
0.0
log file switch completion 4 0 18 45
0.0
buffer busy waits 12 0 12 10
0.0
row cache lock 11 0 4 4
0.0
log file single write 8 0 4 5
0.0
file open 97 0 2 0
0.0
LGWR wait for redo copy 21 0 2 1
0.0
control file sequential read 106 0 1 0
0.0
refresh controlfile command 6 0 1 2
0.0
file identify 16 0 0 0
0.0
SQL*Net message from client 470,840 0 7,128,355 151
14.7
virtual circuit status 117 117 359,341 30713
0.0
SQL*Net message to client 470,841 0 76 0
14.7
SQL*Net more data from clien 72 0 1 0
0.0
-------------------------------------------------------------
Background Wait Events for DB: prod001 Instance: prod001 Snaps: 17 -18
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------
------
log file parallel write 41,351 0 7,087 2
1.3
db file parallel write 1,169 0 3,790 32
0.0
log file sequential read 3,208 0 176 1
0.1
control file parallel write 1,192 0 82 1
0.0
db file scattered read 35 0 8 2
0.0
latch free 3 3 6 20
0.0
log file single write 8 0 4 5
0.0
LGWR wait for redo copy 21 0 2 1
0.0
db file sequential read 14 0 2 1
0.0
direct path read 13 0 2 2
0.0
control file sequential read 86 0 1 0
0.0
file open 66 0 1 0
0.0
file identify 16 0 0 0
0.0
direct path write 13 0 0 0
0.0
rdbms ipc message 125,693 3,383 1,810,217 144
3.9
smon timer 12 12 368,452 ######
0.0
pmon timer 1,177 1,175 359,968 3058
0.0
-------------------------------------------------------------
Instance Activity Stats for DB: prod001 Instance: prod001 Snaps: 17 -18
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 32,258 9.0 1.0
CPU used when call started 32,256 9.0 1.0
CR blocks created 71 0.0 0.0
Cached Commit SCN referenced 0 0.0 0.0
DBWR buffers scanned 29,595 8.2 0.9
DBWR checkpoint buffers written 4,616 1.3 0.1
DBWR checkpoints 2 0.0 0.0
DBWR free buffers found 15,765 4.4 0.5
DBWR lru scans 259 0.1 0.0
DBWR make free requests 259 0.1 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 29,595 8.2 0.9
DBWR transaction table writes 32 0.0 0.0
DBWR undo block writes 4,100 1.1 0.1
SQL*Net roundtrips to/from client 470,775 130.8 14.7
background checkpoints completed 1 0.0 0.0
background checkpoints started 2 0.0 0.0
background timeouts 3,543 1.0 0.1
branch node splits 7 0.0 0.0
buffer is not pinned count 1,064,659 295.7 33.3
buffer is pinned count 1,164,188 323.4 36.4
bytes received via SQL*Net from c 31,578,290 8,771.8 987.5
bytes sent via SQL*Net to client 40,661,051 11,294.7 1,271.6
calls to get snapshot scn: kcmgss 239,785 66.6 7.5
calls to kcmgas 42,346 11.8 1.3
calls to kcmgcs 830 0.2 0.0
change write time 863 0.2 0.0
cleanouts and rollbacks - consist 16 0.0 0.0
cleanouts only - consistent read 2,478 0.7 0.1
cluster key scan block gets 742 0.2 0.0
cluster key scans 427 0.1 0.0
commit cleanout failures: block l 57 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 0 0.0 0.0
commit cleanout failures: cannot 17 0.0 0.0
commit cleanouts 119,072 33.1 3.7
commit cleanouts successfully com 118,998 33.1 3.7
consistent changes 1,141 0.3 0.0
consistent gets 1,819,200 505.3 56.9
cursor authentications 8,446 2.4 0.3
data blocks consistent reads - un 1,141 0.3 0.0
db block changes 575,507 159.9 18.0
db block gets 702,502 195.1 22.0
deferred (CURRENT) block cleanout 87,215 24.2 2.7
dirty buffers inspected 3,194 0.9 0.1
enqueue conversions 0 0.0 0.0
enqueue releases 84,889 23.6 2.7
enqueue requests 84,890 23.6 2.7
enqueue timeouts 1 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 156,274 43.4 4.9
free buffer inspected 3,194 0.9 0.1
free buffer requested 250,056 69.5 7.8
hot buffers moved to head of LRU 28,385 7.9 0.9
immediate (CR) block cleanout app 2,494 0.7 0.1
immediate (CURRENT) block cleanou 2,262 0.6 0.1
index fast full scans (full) 153 0.0 0.0
leaf node splits 412 0.1 0.0
logons cumulative 7 0.0 0.0
logons current
messages received 43,138 12.0 1.4
messages sent 43,137 12.0 1.4
no buffer to keep pinned count 291,767 81.1 9.1
no work - consistent read gets 930,050 258.4 29.1
opened cursors cumulative 94,246 26.2 3.0
parse count (hard) 54,853 15.2 1.7
parse count (total) 110,712 30.8 3.5
parse time cpu 13,070 3.6 0.4
parse time elapsed 13,670 3.8 0.4
physical reads 257,462 71.5 8.1
physical reads direct 11,984 3.3 0.4
physical writes 33,624 9.3 1.1
physical writes direct 11,984 3.3 0.4
physical writes non checkpoint 31,752 8.8 1.0
prefetched blocks 185,429 51.5 5.8
prefetched blocks aged out before 0 0.0 0.0
process last non-idle time 7,119,534,184 1,977,648.4 222,645.5
recursive calls 251,418 69.8 7.9
recursive cpu usage 2,269 0.6 0.1
redo blocks written 191,587 53.2 6.0
redo buffer allocation retries 6 0.0 0.0
redo entries 289,021 80.3 9.0
redo log space requests 4 0.0 0.0
redo log space wait time 18 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 84,125,268 23,368.1 2,630.8
redo synch time 17,769 4.9 0.6
redo synch writes 41,636 11.6 1.3
redo wastage 10,845,480 3,012.6 339.2
redo write time 14,851 4.1 0.5
redo writer latching time 2 0.0 0.0
redo writes 41,355 11.5 1.3
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 55 0.0 0.0
rows fetched via callback 80,232 22.3 2.5
session connect time 7,119,534,184 1,977,648.4 222,645.5
session logical reads 2,521,708 700.5 78.9
session pga memory 8,179,268 2,272.0 255.8
session pga memory max 8,402,876 2,334.1 262.8
session uga memory 18,156 5.0 0.6
session uga memory max 4,528,212 1,257.8 141.6
sorts (disk) 15 0.0 0.0
sorts (memory) 88,422 24.6 2.8
sorts (rows) 1,085,628 301.6 34.0
summed dirty queue length 58,329 16.2 1.8
switch current to new buffer
table fetch by rowid 916,442 254.6 28.7
table fetch continued row 17 0.0 0.0
table scan blocks gotten 278,141 77.3 8.7
table scan rows gotten 56,757,219 15,765.9 1,774.9
table scans (direct read) 0 0.0 0.0
table scans (long tables) 14 0.0 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 14,826 4.1 0.5
total file opens 97 0.0 0.0
transaction rollbacks 0 0.0 0.0
user calls 501,811 139.4 15.7
user commits 31,598 8.8 1.0
user rollbacks 379 0.1 0.0
write clones created in foregroun 2 0.0 0.0
-------------------------------------------------------------
Buffer Pool Statistics for DB: prod001 Instance: prod001 Snaps: 17 -18
-> Pools D: default pool, K: keep pool, R: recycle pool
Free Write
Buffer
Buffer Consistent Physical Physical Buffer Complete
Busy
P Gets Gets Reads Writes Waits Waits
Waits
D 250,060 1,063,685 245,489 22,701 0 0
12
-------------------------------------------------------------
Buffer wait Statistics for DB: prod001 Instance: prod001 Snaps: 17 -18 -> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 9 12 1
undo header 3 0 0
-------------------------------------------------------------
Latch Activity for DB: prod001 Instance: prod001 Snaps: 17 -18 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg
Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
----------------------------- -------------- ------ ------ ------------
------
Token Manager 57 0.0 9,688
0.0
active checkpoint queue latch 2,777 0.0 0
archive control 4 0.0 0
archive process latch 4 0.0 0
cache buffer handles 392 0.0 0
cache buffers chains 5,769,101 0.0 0.1 442,799
0.0
cache buffers lru chain 505,455 0.0 0.1 0
channel handle pool latch 14 0.0 0
channel operations parent lat 21 0.0 0
checkpoint queue latch 77,852 0.0 0.0 0
dictionary lookup 17 0.0 0
dml lock allocation 84,144 0.0 0.0 0
enqueue hash chains 169,760 0.0 0.0 0
enqueues 271,874 0.0 0.0 0
event group latch 7 0.0 0
job_queue_processes parameter 56 0.0 0
ktm global data 12 0.0 0
latch wait list 879 0.8 0.0 879
0.0
library cache 2,712,861 0.1 0.2 145,909
0.2
library cache load lock 990 0.0 0
list of block allocation 85,638 0.0 0.0 0
loader state object freelist 88 0.0 0
longop free list 292 0.0 0
messages 308,390 0.0 0.1 0
multiblock read objects 58,774 0.0 0
ncodef allocation latch 56 0.0 0
process allocation 7 0.0 7
0.0
process group creation 14 0.0 0
redo allocation 371,871 0.0 0.0 0
redo writing 212,923 0.3 0.0 0
row cache objects 5,653,303 0.1 0.0 9,677
0.1
sequence cache 20,260 0.0 0.0 0
session allocation 202,726 0.0 0.0 0
session idle bit 1,104,899 0.0 0.0 0
session switching 56 0.0 0
session timer 1,176 0.0 0
shared pool 2,792,797 0.2 0.2 0
sort extent pool 179 0.0 0
transaction allocation 127,087 0.0 0.0 0
transaction branch allocation 56 0.0 0
undo global data 132,438 0.0 0.0 0
user lock 28 0.0 0
virtual circuit queues 176 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: prod001 Instance: prod001 Snaps: 17 -18
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps
1->4
-------------------------- -------------- ----------- -----------
------------
shared pool 2,792,797 4,380 916
3922/96/320/
42/0
row cache objects 5,653,303 3,097 5
3092/5/0/0/0
library cache 2,712,861 2,576 548
2210/210/135
/21/0
redo writing 212,923 710 17
693/17/0/0/0
cache buffers lru chain 505,455 114 13
101/13/0/0/0
messages 308,390 90 6 84/6/0/0/0
cache buffers chains 5,769,101 52 4 48/4/0/0/0
session allocation 202,726 46 1 45/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: prod001 Instance: prod001 Snaps: 17 -18
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
------------------------ -------------------------- ------- ----------
-------
cache buffers chains kcbgtcr: kslbegin 0 4
0
cache buffers lru chain kcbbiop: lru scan 0 13
0
library cache kglic 0 95
3
library cache kgllkdl: child: cleanup 0 75
2
library cache kglupc: child 0 24
94
library cache kglhdgc: child: 0 15
8
library cache kgldti: 2child 0 9
44
library cache kglsca: parent 0 8
0
library cache kglati 0 3
0
library cache kglhdgn: child: 0 3
82
library cache kglpnal: child: before pro 0 2
26
library cache kglpnc: child 0 2
67
library cache kgllkdl: child: free pin 0 1
54
library cache kglobld: child: 0 1
13
library cache kglpnal: child: alloc spac 0 1
41
messages ksarcv: after wait 0 4
0
messages ksaamb: after wakeup 0 2
6
redo writing kcrfsr 0 12
0
redo writing kcrfss 0 5
17
row cache objects kqrpre: find obj 0 3
5
row cache objects kqreqd 0 2
0
session allocation ksuxds: not user session 0 1
1
shared pool kghfrunp: alloc: clatch no 0 882
0
shared pool kghfrunp: clatch: nowait 0 292
0
shared pool kghfrunp: clatch: wait 0 17
68
shared pool kghalo 0 14
511
shared pool kghfrunp: alloc: wait 0 7
1
shared pool kghfen: not perm alloc cla 0 5
22
shared pool kghfnd: min scan 0 4
0
shared pool kghfnd: req scan 0 2
0
shared pool kghfnd: get next extent 0 1
0
shared pool kghfre 0 1
5
-------------------------------------------------------------
Dictionary Cache Stats for DB: prod001 Instance: prod001 Snaps: 17 -18
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod
Final Pct
Cache Requests Miss Requests Miss Req
Usage SGA
---------------------- ------------ ------ -------- ----- --------
------ ----
dc_constraints 0 0 0
0 0
dc_database_links 0 0 0
0 0
dc_files 0 0 0
13 65
dc_free_extents 12 0.0 0 0
158 89
dc_global_oids 0 0 0
0 0
dc_histogram_data 0 0 0
0 0
dc_histogram_data_valu 0 0 0
0 0
dc_histogram_defs 354,671 0.0 0 0
759 100
dc_object_ids 972,250 0.0 0 13
349 100
dc_objects 85,215 0.0 0 15
620 100
dc_outlines 0 0 0
0 0
dc_profiles 7 0.0 0 0
1 50
dc_rollback_segments 522 0.0 0 0
19 76
dc_segments 355,425 0.0 0 0
328 99
dc_sequence_grants 55 0.0 0 0
22 96
dc_sequences 10,175 0.1 0 10,128
56 98
dc_synonyms 1,034 0.0 0 0
48 83
dc_tablespace_quotas 0 0 0
4 17
dc_tablespaces 38 0.0 0 0
11 69
dc_used_extents 0 0 0
23 96
dc_user_grants 40,851 0.0 0 0
13 72
dc_usernames 36,766 0.0 0 0
7 32
dc_users 40,983 0.0 0 0
14 67
ifs_acl_cache_entries 0 0 0
0 0
-------------------------------------------------------------
Library Cache Activity for DB: prod001 Instance: prod001 Snaps: 17 -18 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 8 12.5 8 12.5 0
0
CLUSTER 45 0.0 51 0.0 0
0
INDEX 0 0 0
0
OBJECT 0 0 0
0
PIPE 0 0 0
0
SQL AREA 110,645 47.8 319,180 33.8 1,928
0
TABLE/PROCEDURE 158,573 0.1 372,116 0.1 247
0
TRIGGER 312 0.0 312 0.0 0
0
-------------------------------------------------------------
Stefan wrote:
> Hi Stan, > > this thread is quite interesting. These guys are all talking about problems > without any real evidence. Running statspack without having turned on > timed_statistics is pretty useless. > > Please turn on timed_statistics and rerun your statspack report > > Regards > Stefan > > > > Stan wrote: > > >>i just did a major export/import of one of production database and we >>moved data from hp-ux, oracle 8.1.6 std. edition to sun box with 8.1.7.2 >>enterpirse edt. now i started to see slowness in the application and >>there was no change in the application. infact i have increased more >>db_buffers and shared_poool size in the new environment that the old one. >> >>here is my statspack analysis report. i know i need to increase my >>redolog buffer and sort_area_size, can anyone point out that am >>missing.... >> >>any tuning advise is greatly appreicated. thanks.... >> >>Instance Efficiency Percentages (Target 100%) >>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >> Buffer Nowait %: 100.00 Redo NoWait %: 100.00 >> Buffer Hit %: 77.69 In-memory Sort %: 99.41 >> Library Hit %: 86.63 Soft Parse %: 57.43 >> Execute to Parse %: 56.65 Latch Hit %: 99.99 >>Parse CPU to Parse Elapsd %: % Non-Parse CPU: >> >> Shared Pool Statistics Begin End >> ------ ------ >> Memory Usage %: 73.13 78.14 >> % SQL with executions>1: 15.21 14.83 >> % Memory for SQL w/exec>1: 24.97 23.97 >> >>Top 5 Wait Events >>~~~~~~~~~~~~~~~~ Wait % >>Total >>Event Waits Time (cs) >>Wt Time >>-------------------------------------------- ------------ ------------ >>------- >>db file scattered read 14,880 0 >> .00 >>direct path write 4,713 0 >> .00 >>db file sequential read 4,704 0 >> .00 >>direct path read 3,803 0 >> .00 >>SQL*Net more data to client 2,189 0 >> .00 >> ------------------------------------------------------------- >>Wait Events for DB: prod1 Instance: prod1 Snaps: 011- 012 >> >>-> ordered by wait time desc, waits desc (idle events last) >> >> Avg >> Total Wait wait >> Waits >>Event Waits Timeouts Time (cs) (ms) >> /txn >>---------------------------- ------------ ---------- ----------- ------ >>-------- >>db file scattered read 14,880 0 0 0 >> 15.1 >>direct path write 4,713 0 0 0 >> 4.8 >>db file sequential read 4,704 0 0 0 >> 4.8 >>direct path read 3,803 0 0 0 >> 3.9 >>SQL*Net more data to client 2,189 0 0 0 >> 2.2 >>log file parallel write 1,435 0 0 0 >> 1.5 >>log file sync 1,288 0 0 0 >> 1.3 >>control file parallel write 162 0 0 0 >> 0.2 >>db file parallel write 64 0 0 0 >> 0.1 >>control file sequential read 53 0 0 0 >> 0.1 >>file open 29 0 0 0 >> 0.0 >>latch free 10 9 0 0 >> 0.0 >>refresh controlfile command 8 0 0 0 >> 0.0 >>LGWR wait for redo copy 3 0 0 0 >> 0.0 >>log buffer space 3 0 0 0 >> 0.0 >>SQL*Net break/reset to clien 2 0 0 0 >> 0.0 >>SQL*Net message from client 18,885 0 0 0 >> 19.2 >>SQL*Net message to client 18,885 0 0 0 >> 19.2 >>virtual circuit status 16 16 0 0 >> 0.0 >>SQL*Net more data from clien 5 0 0 0 >> 0.0 >> ------------------------------------------------------------- >>Background Wait Events for DB: prod1 Instance: prod1 Snaps: 011- 012 >>-> ordered by wait time desc, waits desc (idle events last) >> >> Avg >> Total Wait wait >> Waits >>Event Waits Timeouts Time (cs) (ms) >> /txn >>---------------------------- ------------ ---------- ----------- ------ >>-------- >>log file parallel write 1,435 0 0 0 >> 1.5 >>control file parallel write 162 0 0 0 >> 0.2 >>db file parallel write 64 0 0 0 >> 0.1 >>control file sequential read 24 0 0 0 >> 0.0 >>LGWR wait for redo copy 4 0 0 0 >> 0.0 >>rdbms ipc message 4,706 482 0 0 >> 4.8 >>pmon timer 163 163 0 0 >> 0.2 >>smon timer 1 1 0 0 >> 0.0 >> >> >> >>Instance Activity Stats for DB: prod1 Instance: prod1 Snaps: 011- 012 >> >>Statistic Total per Second >>per Trans >>--------------------------------- ---------------- ------------ >>---------------- >>CR blocks created 5 0.0 >> 0.0 >>DBWR buffers scanned 0 0.0 >> 0.0 >>DBWR checkpoint buffers written 489 1.0 >> 0.5 >>DBWR checkpoints 0 0.0 >> 0.0 >>DBWR free buffers found 0 0.0 >> 0.0 >>DBWR lru scans 0 0.0 >> 0.0 >>DBWR make free requests 0 0.0 >> 0.0 >>DBWR summed scan depth 0 0.0 >> 0.0 >>DBWR transaction table writes 0 0.0 >> 0.0 >>DBWR undo block writes 226 0.5 >> 0.2 >>SQL*Net roundtrips to/from client 18,879 37.8 >> 19.2 >>background checkpoints started 0 0.0 >> 0.0 >>background timeouts 488 1.0 >> 0.5 >>branch node splits 0 0.0 >> 0.0 >>buffer is not pinned count 244,099 489.2 >> 248.3 >>buffer is pinned count 72,696 145.7 >> 74.0 >>bytes received via SQL*Net from c 1,601,637 3,209.7 >> 1,629.3 >>bytes sent via SQL*Net to client 6,233,304 12,491.6 >> 6,341.1 >>calls to get snapshot scn: kcmgss 11,868 23.8 >> 12.1 >>calls to kcmgas 1,321 2.7 >> 1.3 >>calls to kcmgcs 41 0.1 >> 0.0 >>cleanouts and rollbacks - consist 0 0.0 >> 0.0 >>cleanouts only - consistent read 10 0.0 >> 0.0 >>cluster key scan block gets 349 0.7 >> 0.4 >>cluster key scans 154 0.3 >> 0.2 >>commit cleanout failures: block l 6 0.0 >> 0.0 >>commit cleanout failures: callbac 1 0.0 >> 0.0 >>commit cleanout failures: cannot 0 0.0 >> 0.0 >>commit cleanouts 5,518 11.1 >> 5.6 >>commit cleanouts successfully com 5,511 11.0 >> 5.6 >>consistent changes 13 0.0 >> 0.0 >>consistent gets 488,751 979.5 >> 497.2 >>cursor authentications 175 0.4 >> 0.2 >>data blocks consistent reads - un 13 0.0 >> 0.0 >>db block changes 51,154 102.5 >> 52.0 >>db block gets 59,916 120.1 >> 61.0 >>deferred (CURRENT) block cleanout 3,482 7.0 >> 3.5 >>dirty buffers inspected 0 0.0 >> 0.0 >>enqueue releases 2,743 5.5 >> 2.8 >>enqueue requests 2,743 5.5 >> 2.8 >>enqueue waits 0 0.0 >> 0.0 >>execute count 9,314 18.7 >> 9.5 >>free buffer inspected 0 0.0 >> 0.0 >>free buffer requested 110,342 221.1 >> 112.3 >>hot buffers moved to head of LRU 43 0.1 >> 0.0 >>immediate (CR) block cleanout app 10 0.0 >> 0.0 >>immediate (CURRENT) block cleanou 94 0.2 >> 0.1 >>index fast full scans (full) 21 0.0 >> 0.0 >>leaf node splits 26 0.1 >> 0.0 >>logons cumulative 3 0.0 >> 0.0 >>logons current >>messages received 1,517 3.0 >> 1.5 >>messages sent 1,517 3.0 >> 1.5 >>no buffer to keep pinned count 140,717 282.0 >> 143.2 >>no work - consistent read gets 199,274 399.4 >> 202.7 >>opened cursors cumulative 3,507 7.0 >> 3.6 >>opened cursors current >>parse count (hard) 1,719 3.4 >> 1.8 >>parse count (total) 4,038 8.1 >> 4.1 >>physical reads 122,431 245.4 >> 124.6 >>physical reads direct 12,529 25.1 >> 12.8 >>physical writes 13,018 26.1 >> 13.2 >>physical writes direct 12,529 25.1 >> 12.8 >>physical writes non checkpoint 13,014 26.1 >> 13.2 >>prefetched blocks 90,319 181.0 >> 91.9 >>prefetched blocks aged out before 0 0.0 >> 0.0 >>recursive calls 10,383 20.8 >> 10.6 >>redo blocks written 14,099 28.3 >> 14.3 >>redo buffer allocation retries 3 0.0 >> 0.0 >>redo entries 25,509 51.1 >> 26.0 >>redo log space requests 0 0.0 >> 0.0 >>redo size 6,529,240 13,084.7 >> 6,642.2 >>redo synch writes 1,287 2.6 >> 1.3 >>redo wastage 456,268 914.4 >> 464.2 >>redo writes 1,434 2.9 >> 1.5 >>rollback changes - undo records a 0 0.0 >> 0.0 >>rollbacks only - consistent read 5 0.0 >> 0.0 >>rows fetched via callback 12,796 25.6 >> 13.0 >>session logical reads 548,667 1,099.5 >> 558.2 >>session pga memory 2,044,132 4,096.5 >> 2,079.5 >>session pga memory max 2,706,572 5,424.0 >> 2,753.4 >>session uga memory 65,824 131.9 >> 67.0 >>session uga memory max 931,680 1,867.1 >> 947.8 >>sorts (disk) 19 0.0 >> 0.0 >>sorts (memory) 3,179 6.4 >> 3.2 >>sorts (rows) 615,835 1,234.1 >> 626.5 >>summed dirty queue length 0 0.0 >> 0.0 >>switch current to new buffer >>table fetch by rowid 76,726 153.8 >> 78.1 >>table fetch continued row 1 0.0 >> 0.0 >>table scan blocks gotten 137,870 276.3 >> 140.3 >>table scan rows gotten 27,222,785 54,554.7 >>27,693.6 >>table scans (long tables) 9 0.0 >> 0.0 >>table scans (short tables) 846 1.7 >> 0.9 >>total file opens 28 0.1 >> 0.0 >>user calls 22,319 44.7 >> 22.7 >>user commits 952 1.9 >> 1.0 >>user rollbacks 31 0.1 >> 0.0 >>write clones created in foregroun 0 0.0 >> 0.0 >> ------------------------------------------------------------- >> >> >> Pct Avg Pct >> Get Get Slps NoWait >>NoWait >>Latch Name Requests Miss /Miss Requests >> Miss >>----------------------------- -------------- ------ ------ ------------ >>------ >>Token Manager 28 0.0 0 >>active checkpoint queue latch 224 0.0 0 >>cache buffer handles 39 0.0 0 >>cache buffers chains 1,051,226 0.0 0.0 205,823 >> 0.0 >>cache buffers lru chain 223,441 0.0 0.0 0 >>channel handle pool latch 5 0.0 0 >>channel operations parent lat 8 0.0 0 >>checkpoint queue latch 3,972 0.0 0.0 0 >>dml lock allocation 2,684 0.0 0 >>enqueue hash chains 5,490 0.0 0 >>enqueues 8,783 0.0 0.0 0 >>event group latch 3 0.0 0 >>job_queue_processes parameter 8 0.0 0 >>ktm global data 1 0.0 0 >>latch wait list 10 0.0 10 >> 0.0 >>library cache 131,738 0.0 0.2 0 >>library cache load lock 140 0.0 0 >>list of block allocation 2,858 0.0 0 >>loader state object freelist 62 0.0 0 >>longop free list 11 0.0 0 >>messages 11,302 0.1 0.0 0 >>multiblock read objects 34,156 0.0 0 >>ncodef allocation latch 8 0.0 0 >>process allocation 3 0.0 3 >> 0.0 >>process group creation 5 0.0 0 >>redo allocation 28,465 0.0 0.0 0 >>redo writing 7,315 0.5 0.0 0 >>row cache objects 178,431 0.0 0.0 0 >>sequence cache 696 0.0 0 >>session allocation 6,269 0.0 0.0 0 >>session idle bit 47,767 0.0 0 >>session switching 8 0.0 0 >>session timer 163 0.0 0 >>shared pool 60,645 0.0 0.0 0 >>sort extent pool 129 0.0 0 >>transaction allocation 3,974 0.0 0 >>transaction branch allocation 8 0.0 0 >>undo global data 4,189 0.0 0 >>user lock 10 0.0 0 >>virtual circuit queues 24 0.0 0 >> ------------------------------------------------------------- >>Latch Sleep breakdown for DB: prod1 Instance: prod1 Snaps: 011- 012 >>-> ordered by misses desc >> >> Get Spin >> & >>Latch Name Requests Misses Sleeps Sleeps >>1->4 >>-------------------------- -------------- ----------- ----------- >>------------ >>library cache 131,738 41 10 >>31/10/0/0/0 >> ------------------------------------------------------------- >>Latch Miss Sources for DB: prod1 Instance: prod1 Snaps: 011- 012 >>-> only latches with sleeps are shown >>-> ordered by name, sleeps desc >> >> NoWait >> Waiter >>Latch Name Where Misses Sleeps >>Sleeps >>------------------------ -------------------------- ------- ---------- >>------- >>library cache kglic 0 9 >> 0 >>library cache kglpnal: child: alloc spac 0 1 >> 1 >> ------------------------------------------------------------- >>Dictionary Cache Stats for DB: prod1 Instance: prod1 Snaps: 011- 012 >>->"Pct Misses" should be very low (< 2% in most cases) >>->"Cache Usage" is the number of cache entries being used >>->"Pct SGA" is the ratio of usage to allocated size for that cache >> >> Get Pct Scan Pct Mod >>Final Pct >>Cache Requests Miss Requests Miss Req >>Usage SGA >>---------------------- ------------ ------ -------- ----- -------- >>------ ---- >>dc_constraints 0 0 0 >>0 0 >>dc_database_links 0 0 0 >>0 0 >>dc_files 33 39.4 0 0 >>13 65 >>dc_free_extents 1 0.0 0 0 >>1 9 >>dc_global_oids 0 0 0 >>0 0 >>dc_histogram_data 0 0 0 >>0 0 >>dc_histogram_data_valu 0 0 0 >>0 0 >>dc_histogram_defs 10,937 0.3 0 0 >>738 100 >>dc_object_ids 29,329 0.0 0 0 >>271 98 >>dc_objects 2,579 1.0 0 0 >>396 99 >>dc_outlines 0 0 0 >>0 0 >>dc_profiles 3 0.0 0 0 >>1 25 >>dc_rollback_segments 81 0.0 0 0 >>19 76 >>dc_segments 10,628 0.0 0 0 >>294 95 >>dc_sequence_grants 22 0.0 0 0 >>22 96 >>dc_sequences 348 0.0 0 342 >>30 79 >>dc_synonyms 26 30.8 0 0 >>36 92 >>dc_tablespace_quotas 0 0 0 >>0 0 >>dc_tablespaces 54 13.0 0 0 >>9 45 >>dc_used_extents 0 0 0 >>158 99 >>dc_user_grants 2,847 0.1 0 0 >>12 67 >>dc_usernames 1,122 0.1 0 0 >>6 29 >>dc_users 2,894 0.0 0 0 >>14 88 >>ifs_acl_cache_entries 0 0 0 >>0 0 >> ------------------------------------------------------------- >> >> >>Library Cache Activity for DB: prod1 Instance: prod1 Snaps: 011- 012 >>->"Pct Misses" should be very low >> >> Get Pct Pin Pct >>Invali- >>Namespace Requests Miss Requests Miss Reloads >>dations >>--------------- ------------ ------ -------------- ------ ---------- >>-------- >>BODY 1 0.0 1 0.0 0 >> 0 >>CLUSTER 13 0.0 2 0.0 0 >> 0 >>INDEX 0 0 0 >> 0 >>OBJECT 0 0 0 >> 0 >>PIPE 0 0 0 >> 0 >>SQL AREA 4,022 42.6 15,064 22.8 7 >> 0 >>TABLE/PROCEDURE 4,781 1.0 11,091 0.6 0 >> 0 >>TRIGGER 21 0.0 21 0.0 0 >> 0 >> ------------------------------------------------------------- >> >Received on Mon Mar 25 2002 - 23:56:22 CST
 |
 |