| 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 -> Performance issue and statspack
I have a strong suspect that something is wrong with the relation
between Oracle and the operating system i'm using... There must be
something wrong with the OS.
Oracle 9iR2 on Tru64 5.1A.
The database seems to be quite slow on writing data. I used this very stupid script to test the performance between installations on different machines, obviously it's not a common behavior of our applications against the db, bit i think it's useful for testing between different instances.
set timing on
begin
for i in 1..100000 loop
INSERT into t values ('xxxxxxxxxxxxxxxx');
end loop;
On an installation with tha same db version and w2k it took about 13
secs to run.
On the Tru64 instance it took from 55 to 65 secs.
In this moment the w2k instance has a bigger sized SGA, but the other instance gave us the same results before even with a similar sized SGA. Sorry for the big post, here are the statspack reports of the two instances for just the time of running that script. Don't look at the users tbs that looks terrible at a first glance, it's just for testing (i tried with different sized tbs and with a table with initial extent quite big etc etc: nothing changed). I'm impressed by the top five wait events: it seems that the big difference between the two reports is just the CPU time (used in what?), the alphaserver with Tru64 has a 600Mhz cpu, the w2k a 2800Mhz P4, but i don't think here it makes the difference... (I tried the script even on a notebook and it went faster than the alphaserver... imagine).
/***************************************/
_TO MAKE THE POINT_: Do you think could be useful to reinstall all the operating system and then Oracle on the machine? (I know it sounds horrible, but...). Could it be a problem of the installation? Or there is a magical parameter that i don't know specific for Tru64? (I tried any combination of disk_asynch_io and filesystemio_options: nothing new).
/***************************************/
The Tru64 report:
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
ALEF 454647123 alef 1 9.2.0.1.0 NOalpha
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 3 21-May-03 13:46:34 8 6.5
End Snap: 4 21-May-03 13:47:45 8 7.9
Elapsed: 1.18 (mins)
Cache Sizes (end)
Buffer Cache: 40M Std Block Size: 32K
Shared Pool Size: 60M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 375,690.93
13,337,028.00
Logical reads: 1,455.85
51,682.50
Block changes: 2,832.31
100,547.00
Physical reads: 0.87
31.00
Physical writes: 9.87
350.50
User calls: 0.04
1.50
Parses: 5.34
189.50
Hard parses: 0.41
14.50
Sorts: 4.08
145.00
Logons: 0.00
0.00
Executes: 1,416.73
50,294.00
Transactions: 0.03
% Blocks changed per Read: 194.55 Recursive Call %: 100.00
Rollback per transaction %: 0.00 Rows per Sort: 17.77
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.94 In-memory Sort %: 100.00
Library Hit %: 99.61 Soft Parse %: 92.35
Execute to Parse %: 99.62 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 77.36 % Non-Parse CPU: 99.32
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 33.62 35.60
% SQL with executions>1: 55.95 71.20
% Memory for SQL w/exec>1: 38.96 72.13
Top 5 Timed Events
Event Waits Time (s)Ela Time
CPU time 60
94.42
log file parallel write 115 3
4.68
control file parallel write 23 1
.81
log file sync 2 0
.06
LGWR wait for redo copy 12 0
.03
-------------------------------------------------------------
Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
57.5
control file parallel write 23 0 1 23
11.5
log file sync 2 0 0 20
1.0
LGWR wait for redo copy 12 0 0 2
6.0
db file sequential read 60 0 0 0
30.0
control file sequential read 54 0 0 0
27.0
db file parallel write 45 0 0 0
22.5
latch free 1 0 0 0
0.5
SQL*Net message from client 2 0 189 94685
1.0
SQL*Net message to client 2 0 0 0
1.0
-------------------------------------------------------------
Background Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
57.5
control file parallel write 23 0 1 23
11.5
LGWR wait for redo copy 12 0 0 2
6.0
log file sync 1 0 0 10
0.5
db file parallel write 45 0 0 0
22.5
control file sequential read 12 0 0 0
6.0
db file sequential read 10 0 0 0
5.0
latch free 1 0 0 0
0.5
rdbms ipc message 179 63 207 1156
89.5
SQL*Net message from client 1 0 118 ######
0.5
SQL*Net message to client 1 0 0 0
0.5
-------------------------------------------------------------
SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
-> End Buffer Gets Threshold: 10000
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
101,890 1 101,890.0 98.6 58.63 62.15
2617268171
Module: SQL*Plus
BEGIN test; END;
101,828 100,000 1.0 98.5 50.43 52.60
3860503990
Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
156 38 4.1 0.2 0.00 0.01
2085632044
36 13 2.8 0.0 0.00 0.00
2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
or subname is null and :6 is null)
15 5 3.0 0.0 0.00 0.00 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
12 4 3.0 0.0 0.00 0.00
189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
12 4 3.0 0.0 0.00 0.00
4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
8 1 8.0 0.0 0.00 0.00
1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
10 1 10.0 16.1 58.63 62.15
2617268171
Module: SQL*Plus
BEGIN test; END;
9 100,000 0.0 14.5 50.43 52.60
3860503990
Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
1 5 0.2 1.6 0.00 0.00
2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
0 4 0.0 0.0 0.00 0.00
189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
0 1 0.0 0.0 0.00 0.00
1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1
0 38 0.0 0.0 0.00 0.01
2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
0 13 0.0 0.0 0.00 0.00
2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
or subname is null and :6 is null)
0 4 0.0 0.0 0.00 0.00
4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
100,000 100,000 1.0 0.00 0.00
3860503990
Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
38 40 1.1 0.00 0.00
2085632044
13 10 0.8 0.00 0.00
2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
or subname is null and :6 is null)
5 24 4.8 0.00 0.00 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
4 4 1.0 0.00 0.00
189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
4 4 1.0 0.00 0.00
4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
1 3 3.0 0.00 0.00
1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1
1 1 1.0 58.63 62.15
2617268171
Module: SQL*Plus
BEGIN test; END;
% Total
4 4 1.06 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user# ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
3 5 0.79 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
3 13 0.79 2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6 or subname is null and :6 is null)
2 4 0.53 189272129 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
2 38 0.53 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
1 1 0.26 1930240031 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj# =:1
1 1 0.26 2617268171
Module: SQL*Plus
BEGIN test; END;
1 100,000 0.26 3860503990 Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
-------------------------------------------------------------
Instance Activity Stats for DB: ALEF Instance: alef Snaps: 3 -4
Statistic Total per Secondper Trans
CPU used by this session 6,038 85.0
3,019.0
CPU used when call started 174 2.5
87.0
CR blocks created 18 0.3
9.0
DBWR checkpoint buffers written 701 9.9
350.5
DBWR transaction table writes 23 0.3
11.5
DBWR undo block writes 405 5.7
202.5
SQL*Net roundtrips to/from client 2 0.0
1.0
active txn count during cleanout 80 1.1
40.0
background timeouts 68 1.0
34.0
buffer is not pinned count 947 13.3
473.5
buffer is pinned count 729 10.3
364.5
bytes received via SQL*Net from c 481 6.8
240.5
bytes sent via SQL*Net to client 322 4.5
161.0
calls to get snapshot scn: kcmgss 100,628 1,417.3
50,314.0
calls to kcmgas 358 5.0
179.0
calls to kcmgcs 74 1.0
37.0
change write time 4,044 57.0
2,022.0
cleanout - number of ktugct calls 80 1.1
40.0
cluster key scan block gets 253 3.6
126.5
cluster key scans 177 2.5
88.5
commit cleanout failures: block l 4 0.1
2.0
commit cleanouts 144 2.0
72.0
commit cleanouts successfully com 140 2.0
70.0
consistent changes 18 0.3
9.0
consistent gets 1,834 25.8
917.0
consistent gets - examination 912 12.9
456.0
cursor authentications 9 0.1
4.5
data blocks consistent reads - un 18 0.3
9.0
db block changes 201,094 2,832.3
100,547.0
db block gets 101,531 1,430.0
50,765.5
deferred (CURRENT) block cleanout 28 0.4
14.0
dirty buffers inspected 2 0.0
1.0
enqueue conversions 27 0.4
13.5
enqueue releases 202 2.9
101.0
enqueue requests 202 2.9
101.0
execute count 100,588 1,416.7
50,294.0
free buffer inspected 2 0.0
1.0
free buffer requested 604 8.5
302.0
hot buffers moved to head of LRU 206 2.9
103.0
immediate (CURRENT) block cleanou 3 0.0
1.5
index fetch by key 281 4.0
140.5
index scans kdiixs1 939 13.2
469.5
logons cumulative 0 0.0
0.0
messages received 161 2.3
80.5
messages sent 161 2.3
80.5
no buffer to keep pinned count 0 0.0
0.0
no work - consistent read gets 574 8.1
287.0
opened cursors cumulative 357 5.0
178.5
parse count (hard) 29 0.4
14.5
parse count (total) 379 5.3
189.5
parse time cpu 41 0.6
20.5
parse time elapsed 53 0.8
26.5
physical reads 62 0.9
31.0
physical reads direct 0 0.0
0.0
physical writes 701 9.9
350.5
physical writes direct 0 0.0
0.0
Statistic Total per Secondper Trans
physical writes non checkpoint 573 8.1
286.5
prefetched blocks 0 0.0
0.0
process last non-idle time 0 0.0
0.0
recursive calls 107,163 1,509.3
53,581.5
recursive cpu usage 5,627 79.3
2,813.5
redo blocks written 26,557 374.0
13,278.5
redo entries 100,514 1,415.7
50,257.0
redo ordering marks 298 4.2
149.0
redo size 26,674,056 375,690.9
############
redo synch time 4 0.1
2.0
redo synch writes 2 0.0
1.0
redo wastage 89,880 1,265.9
44,940.0
redo write time 1 0.0
0.5
redo writer latching time 2 0.0
1.0
redo writes 115 1.6
57.5
rollbacks only - consistent read 18 0.3
9.0
rows fetched via callback 67 0.9
33.5
session connect time 0 0.0
0.0
session logical reads 103,365 1,455.9
51,682.5
session pga memory 24,992 352.0
12,496.0
session pga memory max 0 0.0
0.0
session uga memory 65,408 921.2
32,704.0
session uga memory max 0 0.0
0.0
shared hash latch upgrades - no w 292 4.1
146.0
sorts (memory) 290 4.1
145.0
sorts (rows) 5,154 72.6
2,577.0
switch current to new buffer 0 0.0
0.0
table fetch by rowid 337 4.8
168.5
table scan blocks gotten 1 0.0
0.5
table scan rows gotten 1 0.0
0.5
table scans (long tables) 0 0.0
0.0
table scans (short tables) 2 0.0
1.0
user calls 3 0.0
1.5
user commits 2 0.0
1.0
workarea executions - optimal 211 3.0
105.5
write clones created in backgroun 1 0.0
0.5
write clones created in foregroun 33 0.5
16.5
-------------------------------------------------------------
Session Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
Session Id: 7 Serial#: 5
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
log file sync 1 0 0 30
0.5
db file sequential read 65 0 0 0
32.5
control file sequential read 42 0 0 0
21.0
SQL*Net message from client 1 0 71 71380
0.5
SQL*Net message to client 1 0 0 0
0.5
-------------------------------------------------------------
Session Statistics for DB: ALEF Instance: alef Snaps: 3 -4
Session Id: 7 Serial#: 5
Statistic Total per Second
per Trans
active txn count during cleanout 80 1.1 buffer is not pinned count 322 4.5 buffer is pinned count 5 0.1 bytes received via sql*net from c 214 3.0 bytes sent via sql*net to client 161 2.3 calls to get snapshot scn: kcmgss 100,174 1,410.9 calls to kcmgas 356 5.0 calls to kcmgcs 74 1.0 change write time 4,044 57.0 cleanout - number of ktugct calls 80 1.1 cluster key scan block gets 114 1.6 cluster key scans 49 0.7 commit cleanout failures: block l 4 0.1 commit cleanouts 88 1.2 commit cleanouts successfully com 84 1.2 consistent changes 18 0.3 consistent gets 589 8.3 consistent gets - examination 252 3.6 cpu used by this session 5,864 82.6 cr blocks created 18 0.3 cursor authentications 8 0.1 data blocks consistent reads - un 18 0.3 db block changes 200,909 2,829.7 db block gets 101,301 1,426.8 deferred (current) block cleanout 15 0.2 enqueue releases 104 1.5 enqueue requests 104 1.5 execute count 100,141 1,410.4 free buffer requested 536 7.6 hot buffers moved to head of lru 201 2.8 index fetch by key 60 0.9 index scans kdiixs1 82 1.2 messages sent 91 1.3 no work - consistent read gets 220 3.1 opened cursors cumulative 82 1.2 opened cursors current parse count (hard) 11 0.2 parse count (total) 89 1.3 parse time cpu 5 0.1 parse time elapsed 9 0.1 physical reads 10 0.1 recursive calls 101,586 1,430.8 recursive cpu usage 5,550 78.2 redo entries 100,362 1,413.6 redo ordering marks 298 4.2 redo size 26,468,032 372,789.2 redo synch time 1 0.0 redo synch writes 1 0.0 rollbacks only - consistent read 18 0.3 rows fetched via callback 4 0.1 session logical reads 101,890 1,435.1 session pga memory 213,000 3,000.0 session uga memory 65,408 921.2 shared hash latch upgrades - no w 83 1.2 sorts (memory) 17 0.2 Session Statistics for DB: ALEF Instance: alef Snaps: 3 -4 Session Id: 7 Serial#: 5 Statistic Total per Secondper Trans
sorts (rows) 13 0.2
sql*net roundtrips to/from client 1 0.0
table fetch by rowid 82 1.2
user calls 1 0.0
user commits 1 0.0
workarea executions - optimal 8 0.1
write clones created in foregroun 33 0.5
-------------------------------------------------------------
Tablespace IO Stats for DB: ALEF Instance: alef Snaps: 3 -4
->ordered by IOs (Reads + Writes) desc
Tablespace
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
Tablespace Filename
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
SYSTEM /oradata/alef/sys/system.dbf
11 0 0.0 1.0 160 2 0
UNDOTBS /oradata/alef/sys/undotbs.dbf
3 0 0.0 1.0 452 6 0
USERS /oradata/alef/users/users.dbf
38 1 0.0 1.0 89 1 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: ALEF Instance: alef Snaps: 3 -4
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
D 630 99.9 103,973 55 701 0
0 0
8k 2,475 0 0 0 0
0 0
-------------------------------------------------------------
Instance Recovery Stats for DB: ALEF Instance: alef Snaps: 3 -4 -> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size TimeoutInterval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ----------
B 0 7 87 2 1 7371
1
E 0 7 52 90 1 7371
1
-------------------------------------------------------------
Buffer Pool Advisory for DB: ALEF Instance: alef End Snap: 4 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------PGA Aggr Target Stats for DB: ALEF Instance: alef Snaps: 3 -4 -> B: Begin snap E: End snap (rows dentified with B or E contain data
------------------
D 4 .2 126 1.76 1,604 D 8 .4 252 1.40 1,277 D 12 .6 378 1.05 961 D 16 .8 504 1.02 931 D 20 1.0 630 1.00 911 D 24 1.2 756 0.97 881 D 28 1.4 882 0.97 881 D 32 1.6 1,008 0.97 881 D 36 1.8 1,134 0.97 881 D 40 2.0 1,260 0.97 881 D 44 2.2 1,386 0.97 881 D 48 2.4 1,512 0.97 881 D 52 2.6 1,638 0.97 881 D 56 2.8 1,764 0.97 881 D 60 3.0 1,890 0.97 881 D 64 3.2 2,016 0.97 881 D 68 3.4 2,142 0.97 881 D 72 3.6 2,268 0.97 881 D 76 3.8 2,394 0.97 881 D 80 4.0 2,520 0.97 881 -------------------------------------------------------------
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only
in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual +
auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto
Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual
control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 5 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A
Global Mem
PGA Aggr Target Histogram for DB: ALEF Instance: alef Snaps: 3 -4 -> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
32K 64K 130 130 0 0
64K 128K 1 1 0 0
128K 256K 3 3 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: ALEF Instance: alef End Snap: 4 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd
PGA
PGA Target Size W/A MB W/A MB Read/ Cache
Overalloc
Est (MB) Factr Processed Written to Disk Hit %
Count
---------- ------- ---------------- ---------------- --------
----------
11 0.8 34.4 0.0 100.0
0
14 1.0 34.4 0.0 100.0
0
17 1.2 34.4 0.0 100.0
0
20 1.4 34.4 0.0 100.0
0
23 1.6 34.4 0.0 100.0
0
26 1.8 34.4 0.0 100.0
0
29 2.0 34.4 0.0 100.0
0
43 3.0 34.4 0.0 100.0
0
57 4.0 34.4 0.0 100.0
0
86 6.0 34.4 0.0 100.0
0
114 8.0 34.4 0.0 100.0
0
-------------------------------------------------------------
Rollback Segment Stats for DB: ALEF Instance: alef Snaps: 3 -4
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
0 1.0 0.00 0 0 0 0
3 1.0 0.00 0 0 0 0
4 1.0 0.00 0 0 0 0
5 1.0 0.00 0 0 0 0
6 2.0 0.00 0 0 0 0
7 1.0 0.00 0 0 0 0
8 2.0 0.00 0 0 0 0
9 4.0 0.00 49,432 1 0 0
10 1.0 0.00 0 0 0 0
11 473.0 0.00 7,405,076 15 0 12
12 3.0 0.00 2,476 0 0 0
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 753,664 0 753,664
3 360,448 0 360,448
4 27,230,208 0 27,230,208
5 557,056 0 557,056
6 13,598,720 0 13,598,720
7 14,647,296 0 14,647,296
8 28,475,392 0 28,475,392
9 360,448 6,553 360,448
10 425,984 0 425,984
11 39,813,120 7,975,047 39,813,120
12 26,181,632 0 26,181,632
-------------------------------------------------------------
Latch Activity for DB: ALEF Instance: alef Snaps: 3 -4
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics
for
Pct Avg Wait
Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
redo allocation 100,735 1 1
0/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: ALEF Instance: alef Snaps: 3 -4
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
redo allocation kcrfwr 0 1
0
-------------------------------------------------------------
Dictionary Cache Stats for DB: ALEF Instance: alef Snaps: 3 -4
->"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
Cache Requests Miss Reqs Miss Reqs
Usage
------------------------- ------------ ------ ------- ----- --------
dc_free_extents 12 50.0 6 0.0 6
0
dc_object_ids 96 24.0 0 0
228
dc_objects 203 47.3 0 0
392
dc_segments 93 4.3 0 6
158
dc_tablespace_quotas 6 16.7 0 6
1
dc_tablespaces 54 0.0 0 0
25
dc_used_extents 6 100.0 0 6
6
dc_user_grants 12 8.3 0 0
19
dc_usernames 48 0.0 0 0
4
dc_users 56 0.0 0 0
21
-------------------------------------------------------------
Library Cache Activity for DB: ALEF Instance: alef Snaps: 3 -4 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
CLUSTER 16 0.0 24 0.0 0
0
SQL AREA 516 2.1 102,349 0.1 0
0
TABLE/PROCEDURE 401 28.4 528 62.1 0
0
-------------------------------------------------------------
Shared Pool Advisory for DB: ALEF Instance: alef End Snap: 4
-> Note there is often a 1:Many correlation between a single logical
object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd
Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
Obj Hits
----------- ----- ---------- ------------ ------------ -------
36 .6 6 1,391 12 1.0 2,669
44 .7 6 1,391 12 1.0 2,669
52 .9 6 1,391 12 1.0 2,669
60 1.0 6 1,391 12 1.0 2,669
68 1.1 6 1,391 12 1.0 2,669
76 1.3 6 1,391 12 1.0 2,669
84 1.4 6 1,391 12 1.0 2,669
92 1.5 6 1,391 12 1.0 2,669
100 1.7 6 1,391 12 1.0 2,669
108 1.8 6 1,391 12 1.0 2,669
116 1.9 6 1,391 12 1.0 2,669
124 2.1 6 1,391 12 1.0 2,669
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 41,943,040 Fixed Size 732,904 Redo Buffers 811,008 Variable Size 100,663,296 ---------------- sum 144,150,248 -------------------------------------------------------------
SGA breakdown difference for DB: ALEF Instance: alef Snaps: 3 -4
Pool Name Begin value Endvalue % Diff
------ ------------------------------ ----------------init.ora Parameters for DB: ALEF Instance: alef Snaps: 3 -4
---------------- -------
java free memory 12,582,912 12,582,912 0.00 large free memory 4,194,304 4,194,304 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 513,280 513,280 0.00 shared FileIdentificatonBlock 349,824 349,824 0.00 shared FileOpenBlock 1,191,104 1,191,104 0.00 shared KGK heap 7,000 7,000 0.00 shared KGLS heap 1,094,672 1,252,320 14.40 shared KQR L PO 531,480 634,904 19.46 shared KQR M PO 132,640 138,784 4.63 shared KQR S SO 4,608 4,864 5.56 shared KSXR pending messages que 853,952 853,952 0.00 shared KSXR receive buffers 1,034,000 1,034,000 0.00 shared PL/SQL DIANA 625,360 638,696 2.13 shared PL/SQL MPCODE 441,576 450,864 2.10 shared PLS non-lib hp 2,088 2,088 0.00 shared character set object 323,752 323,752 0.00 shared dictionary cache 3,221,760 3,221,760 0.00 shared enqueue 309,528 309,528 0.00 shared errors 22,960 22,960 0.00 shared event statistics per sess 1,728,560 1,728,560 0.00 shared fixed allocation callback 408 408 0.00 shared free memory 55,685,760 54,026,720 -2.98 shared joxs heap init 4,240 4,240 0.00 shared kgl simulator 1,038,616 1,038,616 0.00 shared krvxrr 253,056 253,056 0.00 shared library cache 3,986,512 4,381,328 9.90 shared message pool freequeue 940,944 940,944 0.00 shared miscellaneous 4,672,080 4,693,040 0.45 shared parameters 9,576 10,640 11.11 shared pl/sql source 4,968 4,968 0.00 shared sessions 462,400 462,400 0.00 shared sim memory hea 46,880 46,880 0.00 shared sql area 1,909,584 2,859,336 49.74 shared table definiti 1,792 4,144 131.25 shared transaction 330,704 330,704 0.00 shared trigger defini 1,112 1,112 0.00 shared trigger inform 1,320 1,320 0.00 shared trigger source 1,144 1,144 0.00 shared type object de 48,664 48,664 0.00 buffer_cache 41,943,040 41,943,040 0.00 fixed_sga 732,904 732,904 0.00 log_buffer 788,480 788,480 0.00 -------------------------------------------------------------
End
value
Parameter Name Begin value (if
different)
background_dump_dest /oracle/admin/alef/bdump
compatible 9.2.0.0.0
control_files /oradata/alef/sys/control01.ctl,
core_dump_dest /oracle/admin/alef/cdump
db_8k_cache_size 20971520
db_block_size 32768
db_cache_size 20971520
db_domain
db_file_multiblock_read_count 32
db_name alef
filesystemio_options none
hash_join_enabled TRUE
instance_name alef
java_pool_size 12582912
large_pool_size 4194304
log_checkpoint_interval 1
log_checkpoint_timeout 0
open_cursors 300
pga_aggregate_target 15000000
processes 150
query_rewrite_enabled FALSE
shared_pool_size 62914560
sort_area_retained_size 1000000
sort_area_size 1000000
timed_statistics TRUE
undo_management AUTO
undo_retention 3600
undo_tablespace UNDOTBS
user_dump_dest /oracle/admin/alef/udump
utl_file_dir /usr/users/oracle/utl_file
-------------------------------------------------------------
End of Report
The w2k report:
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
ALEF 470618733 alef 1 9.2.0.1.0 NOJACK
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 3 21-May-03 19:10:22 9 6.0
End Snap: 4 21-May-03 19:10:43 9 8.7
Elapsed: 0.35 (mins)
Cache Sizes (end)
Buffer Cache: 240M Std Block Size: 16K
Shared Pool Size: 240M Log Buffer: 4,883K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 1,162,755.05
12,208,928.00
Logical reads: 6,533.71
68,604.00
Block changes: 9,612.38
100,930.00
Physical reads: 32.57
342.00
Physical writes: 32.57
342.00
User calls: 0.43
4.50
Parses: 7.90
83.00
Hard parses: 0.38
4.00
Sorts: 3.57
37.50
Logons: 0.00
0.00
Executes: 4,777.48
50,163.50
Transactions: 0.10
% Blocks changed per Read: 147.12 Recursive Call %: 99.99
Rollback per transaction %: 0.00 Rows per Sort: 317.07
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 97.33
Library Hit %: 99.98 Soft Parse %: 95.18
Execute to Parse %: 99.83 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 75.00 % Non-Parse CPU: 99.54
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.78 93.78
% SQL with executions>1: 36.98 37.03
% Memory for SQL w/exec>1: 49.29 49.33
Top 5 Timed Events
Event Waits Time (s)Ela Time
CPU time 13
87.83
log file parallel write 26 1
4.12
direct path read 96 1
3.67
control file sequential read 55 0
2.19
log file switch completion 2 0
1.49
-------------------------------------------------------------
Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
log file parallel write 26 25 1 23
13.0
direct path read 96 0 1 6
48.0
control file sequential read 55 0 0 6
27.5
log file switch completion 2 0 0 110
1.0
control file parallel write 14 0 0 3
7.0
log file sync 2 0 0 16
1.0
log file sequential read 2 0 0 6
1.0
direct path write 8 0 0 1
4.0
log file single write 2 0 0 0
1.0
SQL*Net more data to client 11 0 0 0
5.5
LGWR wait for redo copy 6 0 0 0
3.0
SQL*Net message from client 6 0 232 38633
3.0
SQL*Net more data from clien 4 0 0 0
2.0
SQL*Net message to client 6 0 0 0
3.0
-------------------------------------------------------------
Background Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
log file parallel write 26 25 1 23
13.0
control file sequential read 13 0 0 13
6.5
control file parallel write 14 0 0 3
7.0
log file sequential read 2 0 0 6
1.0
log file single write 2 0 0 0
1.0
LGWR wait for redo copy 6 0 0 0
3.0
rdbms ipc message 79 51 61 767
39.5
-------------------------------------------------------------
SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
-> End Buffer Gets Threshold: 10000
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
102,128 1 102,128.0 74.4 11.98 12.39
2617268171
Module: SQL*Plus
BEGIN test; END;
102,127 100,000 1.0 74.4 10.92 10.88
3860503990
Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
34,959 1 34,959.0 25.5 0.94 3.85
2118091657
30,150 1 30,150.0 22.0 0.22 0.22
4059808258
Module: SQL*Plus
INSERT into stats$sqltext ( hash_value
, text_subset , piece
, sql_text , address , comman
d_type , last_snap_id )
select st1.hash_value , ss.text_subset
229 3 76.3 0.2 0.58 2.11
1017492279
INSERT into stats$sql_summary ( snap_id
, dbid , instance_number , text_subset
, sharable_mem , sorts , modu
le , loaded_versions , fetches
, executions , loads , invalidations
130 3 43.3 0.1 0.02 0.01
1354174952
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
76 19 4.0 0.1 0.00 0.00
2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
70 10 7.0 0.1 0.00 0.00
2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
60 6 10.0 0.0 0.02 0.01 448729003
declare p varchar2(32767); begin p := LBAC_R
LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
substr(p,8001,4000); :v4 := substr(p,12001,4000);
:v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
60 20 3.0 0.0 0.00 0.00
1705880752
60 10 6.0 0.0 0.00 0.00
1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
50 10 5.0 0.0 0.00 0.00
3936714171
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
2=:7,priv3=:8 where ts#=:1 and user#=:2
30 10 3.0 0.0 0.00 0.00
904892542
select file#,block#,length from fet$ where length>=:1 and
ts#=:2 and file#=:3
30 5 6.0 0.0 0.00 0.00
1536916657
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1
30 10 3.0 0.0 0.00 0.00
1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
30 10 3.0 0.0 0.00 0.00
3687396716
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
h)values (:1, :2, :3, :4, :5, :6, :7)
30 6 5.0 0.0 0.00 0.00
3816270963
SELECT POL.POL#,POL.COLUMN_NAME,POLT.PREDICATE FROM LBACSYS.LB
AC$POL POL,LBACSYS.LBAC$POLT POLT WHERE BITAND(POL.FLAGS,1) = 1
AND POLT.OWNER = :b1 AND POLT.TBL_NAME = :b2 AND BITAND(POLT
.FLAGS,1) = 1 AND BITAND(POLT.OPTIONS,:b3) = :b3 AND POL.POL#
= POLT.POL#
20 10 2.0 0.0 0.00 0.00
2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
20 10 2.0 0.0 0.00 0.00
3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
18 6 3.0 0.0 0.00 0.00
3696784358
SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
12 1 12.0 0.0 0.20 0.70
2734029811
Module: SQL*Plus
INSERT into stats$sql_statistics ( snap_id
, dbid , instance_number , total_
sql , total_sql_mem , single_use_sql
, single_use_sql_mem ) select :
b3 , :b2 , :b1 , count(1)
6 6 1.0 0.0 0.00 0.00
667543881
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
1,014 3 338.0 148.2 0.58 2.11
1017492279
Module: SQL*Plus
INSERT into stats$sql_summary ( snap_id
, dbid , instance_number , text_subset
, sharable_mem , sorts , modu
le , loaded_versions , fetches
, executions , loads , invalidations
684 1 684.0 100.0 0.94 3.85
2118091657
343 1 343.0 50.1 0.20 0.70
2734029811
Module: SQL*Plus
INSERT into stats$sql_statistics ( snap_id
, dbid , instance_number , total_
sql , total_sql_mem , single_use_sql
, single_use_sql_mem ) select :
b3 , :b2 , :b1 , count(1)
0 5 0.0 0.0 0.00 0.00
114078687
0 6 0.0 0.0 0.02 0.01 448729003
declare p varchar2(32767); begin p := LBAC_R
LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
substr(p,8001,4000); :v4 := substr(p,12001,4000);
:v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
0 6 0.0 0.0 0.00 0.00
667543881
0 109 0.0 0.0 0.00 0.00 787810128
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
0 10 0.0 0.0 0.00 0.00
904892542
select file#,block#,length from fet$ where length>=:1 and
ts#=:2 and file#=:3
0 3 0.0 0.0 0.02 0.01
1354174952
SELECT "ID","CDC_RAT_DTA","TPO_ID_RTN","TPO_MCR_SET","CDC_SCT_RT
N","DEF_RAT","DVSDEFRAT","SCN","MESI","LABEL" FROM "DATALEF"."M
KT_DEF_RAT" WHERE "ID" = :1 AND "CDC_RAT_DTA" = :2 AND "TPO_ID_
RTN" = :3 AND "TPO_MCR_SET" = :4 AND "CDC_SCT_RTN" = :5 AND "SCN
SQL ordered by Reads for DB: ALEF Instance: alef Snaps: 3 -4
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
0 20 0.0 0.0 0.00 0.00
1705880752
select file# from file$ where ts#=:1
0 10 0.0 0.0 0.00 0.00
1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
0 10 0.0 0.0 0.00 0.00
1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
0 7 0.0 0.0 0.00 0.00
1966425544
select text from view$ where rowid=:1
0 19 0.0 0.0 0.00 0.00
2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
0 10 0.0 0.0 0.00 0.00
2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
and file#=:2 and block#=:3
0 1 0.0 0.0 11.98 12.39
2617268171
Module: SQL*Plus
BEGIN test; END;
0 6 0.0 0.0 0.00 0.00
2721705697
SELECT MAX(TAG#) FROM LBAC$LAB
0 10 0.0 0.0 0.00 0.00
2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
0 10 0.0 0.0 0.00 0.00
3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
0 10 0.0 0.0 0.00 0.00
3687396716
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
h)values (:1, :2, :3, :4, :5, :6, :7)
0 6 0.0 0.0 0.00 0.00
3696784358
SELECT POL#,PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1) = 1 O
RDER BY PACKAGE
0 6 0.0 0.0 0.00 0.00
3816270963
SELECT POL.POL#,POL.COLUMN_NAME,POLT.PREDICATE FROM LBACSYS.LB
AC$POL POL,LBACSYS.LBAC$POLT POLT WHERE BITAND(POL.FLAGS,1) = 1
SQL ordered by Reads for DB: ALEF Instance: alef Snaps: 3 -4
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
INSERT into t values ('xxxxxxxxxxxxxxxx')
0 10 0.0 0.0 0.00 0.00
3936714171
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
100,000 100,000 1.0 0.00 0.00
3860503990
Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
109 0 0.0 0.00 0.00
787810128
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
20 20 1.0 0.00 0.00
1705880752
select file# from file$ where ts#=:1
19 19 1.0 0.00 0.00
2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
10 10 1.0 0.00 0.00
904892542
select file#,block#,length from fet$ where length>=:1 and
ts#=:2 and file#=:3
10 0 0.0 0.00 0.00
1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
10 10 1.0 0.00 0.00
1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
10 10 1.0 0.00 0.00
2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
and file#=:2 and block#=:3
10 0 0.0 0.00 0.00
2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
10 10 1.0 0.00 0.00
3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
10 10 1.0 0.00 0.00
3687396716
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
h)values (:1, :2, :3, :4, :5, :6, :7)
10 10 1.0 0.00 0.00
3936714171
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
2=:7,priv3=:8 where ts#=:1 and user#=:2
7 7 1.0 0.00 0.00
1966425544
select text from view$ where rowid=:1
6 6 1.0 0.00 0.00 448729003
declare p varchar2(32767); begin p := LBAC_R
LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
SQL ordered by Executions for DB: ALEF Instance: alef Snaps: 3 -4
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
substr(p,8001,4000); :v4 := substr(p,12001,4000);
:v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
6 0 0.0 0.00 0.00
667543881
6 6 1.0 0.00 0.00
2721705697
SELECT MAX(TAG#) FROM LBAC$LAB
6 6 1.0 0.00 0.00
3696784358
SELECT POL#,PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1) = 1 O
RDER BY PACKAGE
6 6 1.0 0.00 0.00
3816270963
SELECT POL.POL#,POL.COLUMN_NAME,POLT.PREDICATE FROM LBACSYS.LB
AC$POL POL,LBACSYS.LBAC$POLT POLT WHERE BITAND(POL.FLAGS,1) = 1
AND POLT.OWNER = :b1 AND POLT.TBL_NAME = :b2 AND BITAND(POLT
.FLAGS,1) = 1 AND BITAND(POLT.OPTIONS,:b3) = :b3 AND POL.POL#
= POLT.POL#
5 0 0.0 0.00 0.00
114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1
5 19 3.8 0.00 0.00
1536916657
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1
3 748 249.3 0.19 0.70
1017492279
Module: SQL*Plus
INSERT into stats$sql_summary ( snap_id
, dbid , instance_number , text_subset
, sharable_mem , sorts , modu
le , loaded_versions , fetches
, executions , loads , invalidations
3 30 10.0 0.01 0.00
1354174952
SQL ordered by Executions for DB: ALEF Instance: alef Snaps: 3 -4 -> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
1 1 1.0 11.98 12.39
2617268171
Module: SQL*Plus
BEGIN test; END;
1 1 1.0 0.20 0.70
2734029811
Module: SQL*Plus
INSERT into stats$sql_statistics ( snap_id
, dbid , instance_number , total_
sql , total_sql_mem , single_use_sql
, single_use_sql_mem ) select :
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4
-> End Parse Calls Threshold: 1000
% Total
20 20 12.05 1705880752 select file# from file$ where ts#=:1
10 10 6.02 904892542
select file#,block#,length from fet$ where length>=:1 and
ts#=:2 and file#=:3
10 10 6.02 1839874543 select file#,block#,length from uet$ where ts#=:1 and segfile#=: 2 and segblock#=:3 and ext#=:4
10 10 6.02 1877781575 delete from fet$ where file#=:1 and block#=:2 and ts#=:3
10 10 6.02 2249281901 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13 , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2 and block#=:3
10 10 6.02 2913840444 select length from fet$ where file#=:1 and block#=:2 and ts#=:3
10 10 6.02 3230982141 insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
10 10 6.02 3687396716 insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt h)values (:1, :2, :3, :4, :5, :6, :7)
10 10 6.02 3936714171 update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv 2=:7,priv3=:8 where ts#=:1 and user#=:2
7 7 4.22 1966425544 select text from view$ where rowid=:1
6 6 3.61 448729003
declare p varchar2(32767); begin p := LBAC_R
LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
substr(p,8001,4000); :v4 := substr(p,12001,4000);
:v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
6 6 3.61 667543881
SELECT A.pol#, nlabel, CASE WHEN A.pol# IN (0) THEN DECODE(LBAC$
SA.enforce_write(pol_name,ilabel),1,21,2,42,0) END FROM lbac$lab
A, lbac$pol B WHERE B.pol# = A.pol# AND bitand(A.flags,1)=1 AND
bitand(B.flags,1)=1 AND tag# > sys_context('LBAC$LABELS','LBAC$
LASTSEQ') AND (CASE WHEN A.pol# IN (0) AND sys_context('LBAC$'|
4 5 2.41 114078687 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4 -> End Parse Calls Threshold: 1000
% Total
4 109 2.41 787810128
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
4 5 2.41 1536916657 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c def$ where obj#=:1
2 3 1.20 1017492279 Module: SQL*Plus
INSERT into stats$sql_summary ( snap_id
, dbid , instance_number , text_subset
, sharable_mem , sorts , modu
le , loaded_versions , fetches
, executions , loads , invalidations
2 1 1.20 2118091657
Module: SQL*Plus
1 3 0.60 1354174952 SELECT "ID","CDC_RAT_DTA","TPO_ID_RTN","TPO_MCR_SET","CDC_SCT_RT N","DEF_RAT","DVSDEFRAT","SCN","MESI","LABEL" FROM "DATALEF"."M KT_DEF_RAT" WHERE "ID" = :1 AND "CDC_RAT_DTA" = :2 AND "TPO_ID_ RTN" = :3 AND "TPO_MCR_SET" = :4 AND "CDC_SCT_RTN" = :5 AND "SCN " = :6 AND "MESI" = :7 AND "LABEL" = :8 OR "ID" = :9 AND "CDC_RA 1 19 0.60 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
1 1 0.60 2617268171
Module: SQL*Plus
BEGIN test; END;
1 100,000 0.60 3860503990 Module: SQL*Plus
INSERT into t values ('xxxxxxxxxxxxxxxx')
1 1 0.60 4059808258
Module: SQL*Plus
INSERT into stats$sqltext ( hash_value
, text_subset , piece
, sql_text , address , comman
d_type , last_snap_id )
select st1.hash_value , ss.text_subset
0 0 0.00 11393400
SELECT "DATALEF"."DATIAZ"."DATA_RILEV" ,"SUPPORTO"."COD_DIV"."CD
SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4
-> End Parse Calls Threshold: 1000
% Total
C_DVS_ALF" FROM "DATALEF"."DATIAZ","DATALEF"."COD_IDN","SUPPORT
O"."CDC_TPL_DTG","SUPPORTO"."COD_DIV","DATALEF"."COD_PTF" WHERE
(((((("DATALEF"."DATIAZ"."DATA_RILEV" = TO_DATE ('2003-04-14','Y
YYY-MM-DD') ) AND ("DATALEF"."DATIAZ"."COD_ALMAX" = "DATALEF"."C
0 0 0.00 29771263
insert into argument$( obj#,procedure$,procedure#,overload#,posi
tion#,sequence#,level#,argument,type#,default#,in_out,length,pre
Statistic Total per Secondper Trans
CPU used by this session 1,295 61.7
647.5
CPU used when call started 97 4.6
48.5
CR blocks created 30 1.4
15.0
DBWR checkpoint buffers written 0 0.0
0.0
DBWR checkpoints 1 0.1
0.5
DBWR transaction table writes 0 0.0
0.0
DBWR undo block writes 0 0.0
0.0
SQL*Net roundtrips to/from client 6 0.3
3.0
active txn count during cleanout 183 8.7
91.5
background checkpoints started 1 0.1
0.5
background timeouts 20 1.0
10.0
branch node splits 0 0.0
0.0
buffer is not pinned count 4,208 200.4
2,104.0
buffer is pinned count 34,882 1,661.1
17,441.0
bytes received via SQL*Net from c 10,818 515.1
5,409.0
bytes sent via SQL*Net to client 25,777 1,227.5
12,888.5
calls to get snapshot scn: kcmgss 100,407 4,781.3
50,203.5
calls to kcmgas 345 16.4
172.5
calls to kcmgcs 165 7.9
82.5
change write time 905 43.1
452.5
cleanout - number of ktugct calls 184 8.8
92.0
cleanouts and rollbacks - consist 0 0.0
0.0
cleanouts only - consistent read 0 0.0
0.0
cluster key scan block gets 1,357 64.6
678.5
cluster key scans 1,080 51.4
540.0
commit cleanout failures: buffer 0 0.0
0.0
commit cleanout failures: callbac 2 0.1
1.0
commit cleanouts 280 13.3
140.0
commit cleanouts successfully com 278 13.2
139.0
commit txn count during cleanout 9 0.4
4.5
consistent changes 30 1.4
15.0
consistent gets 35,055 1,669.3
17,527.5
consistent gets - examination 2,931 139.6
1,465.5
cursor authentications 15 0.7
7.5
data blocks consistent reads - un 30 1.4
15.0
db block changes 201,860 9,612.4
100,930.0
db block gets 102,153 4,864.4
51,076.5
deferred (CURRENT) block cleanout 72 3.4
36.0
dirty buffers inspected 0 0.0
0.0
enqueue conversions 29 1.4
14.5
enqueue releases 233 11.1
116.5
enqueue requests 233 11.1
116.5
enqueue timeouts 0 0.0
0.0
execute count 100,327 4,777.5
50,163.5
free buffer inspected 0 0.0
0.0
free buffer requested 615 29.3
307.5
hot buffers moved to head of LRU 89 4.2
44.5
immediate (CR) block cleanout app 0 0.0
0.0
immediate (CURRENT) block cleanou 17 0.8
8.5
index fast full scans (full) 0 0.0
0.0
index fetch by key 2,638 125.6
1,319.0
index scans kdiixs1 30,109 1,433.8
15,054.5
leaf node 90-10 splits 2 0.1
1.0
leaf node splits 8 0.4
4.0
logons cumulative 0 0.0
0.0
messages received 26 1.2
13.0
Statistic Total per Secondper Trans
messages sent 26 1.2
13.0
no buffer to keep pinned count 0 0.0
0.0
no work - consistent read gets 5,617 267.5
2,808.5
opened cursors cumulative 166 7.9
83.0
parse count (failures) 0 0.0
0.0
parse count (hard) 8 0.4
4.0
parse count (total) 166 7.9
83.0
parse time cpu 6 0.3
3.0
parse time elapsed 8 0.4
4.0
physical reads 684 32.6
342.0
physical reads direct 684 32.6
342.0
physical writes 684 32.6
342.0
physical writes direct 684 32.6
342.0
physical writes non checkpoint 684 32.6
342.0
pinned buffers inspected 0 0.0
0.0
prefetched blocks 0 0.0
0.0
prefetched blocks aged out before 0 0.0
0.0
process last non-idle time 0 0.0
0.0
recursive calls 103,762 4,941.1
51,881.0
recursive cpu usage 1,238 59.0
619.0
redo blocks written 49,345 2,349.8
24,672.5
redo buffer allocation retries 2 0.1
1.0
redo entries 100,943 4,806.8
50,471.5
redo log space requests 2 0.1
1.0
redo log space wait time 22 1.1
11.0
redo ordering marks 294 14.0
147.0
redo size 24,417,856 1,162,755.1
############
redo synch time 2 0.1
1.0
redo synch writes 2 0.1
1.0
redo wastage 8,436 401.7
4,218.0
redo write time 82 3.9
41.0
redo writer latching time 0 0.0
0.0
redo writes 26 1.2
13.0
rollback changes - undo records a 0 0.0
0.0
rollbacks only - consistent read 30 1.4
15.0
rows fetched via callback 1,006 47.9
503.0
session connect time 0 0.0
0.0
session logical reads 137,208 6,533.7
68,604.0
session pga memory 65,312 3,110.1
32,656.0
session pga memory max 1,324,180 63,056.2
662,090.0
session uga memory 130,928 6,234.7
65,464.0
session uga memory max 65,464 3,117.3
32,732.0
shared hash latch upgrades - no w 29,851 1,421.5
14,925.5
shared hash latch upgrades - wait 0 0.0
0.0
sorts (disk) 2 0.1
1.0
sorts (memory) 73 3.5
36.5
sorts (rows) 23,780 1,132.4
11,890.0
summed dirty queue length 0 0.0
0.0
switch current to new buffer 0 0.0
0.0
table fetch by rowid 2,619 124.7
1,309.5
table fetch continued row 0 0.0
0.0
table scan blocks gotten 12 0.6
6.0
table scan rows gotten 1,308 62.3
654.0
table scans (long tables) 0 0.0
0.0
table scans (short tables) 9 0.4
4.5
transaction rollbacks 0 0.0
0.0
Statistic Total per Secondper Trans
user calls 9 0.4
4.5
user commits 2 0.1
1.0
user rollbacks 0 0.0
0.0
workarea executions - onepass 2 0.1
1.0
workarea executions - optimal 98 4.7
49.0
-------------------------------------------------------------
Session Statistics for DB: ALEF Instance: alef Snaps: 3 -4
Session Id: 10 Serial#: 12631
Statistic Total per Second
per Trans
active txn count during cleanout 170 8.1
buffer is not pinned count 160 7.6
bytes received via sql*net from c 165 7.9
bytes sent via sql*net to client 121 5.8
calls to get snapshot scn: kcmgss 100,154 4,769.2
calls to kcmgas 335 16.0
calls to kcmgcs 150 7.1
change write time 905 43.1
cleanout - number of ktugct calls 170 8.1
cluster key scan block gets 100 4.8
cluster key scans 60 2.9
commit cleanouts 181 8.6
commit cleanouts successfully com 181 8.6
consistent changes 30 1.4
consistent gets 483 23.0
consistent gets - examination 310 14.8
cpu used by this session 1,198 57.1
cr blocks created 30 1.4
data blocks consistent reads - un 30 1.4
db block changes 201,336 9,587.4
db block gets 101,645 4,840.2
deferred (current) block cleanout 30 1.4
enqueue releases 137 6.5
enqueue requests 137 6.5
execute count 100,102 4,766.8
free buffer requested 561 26.7
hot buffers moved to head of lru 70 3.3
index fetch by key 80 3.8
index scans kdiixs1 20 1.0
messages sent 24 1.1
no work - consistent read gets 110 5.2
opened cursors cumulative 103 4.9
opened cursors current
parse count (hard) 1 0.1
parse count (total) 103 4.9
recursive calls 101,084 4,813.5
recursive cpu usage 1,148 54.7
redo buffer allocation retries 2 0.1
redo entries 100,649 4,792.8
redo log space requests 2 0.1
redo log space wait time 22 1.1
redo ordering marks 294 14.0
redo size 23,690,488 1,128,118.5
redo synch time 2 0.1
redo synch writes 1 0.1
rollbacks only - consistent read 30 1.4
session logical reads 102,128 4,863.2
shared hash latch upgrades - no w 20 1.0
sql*net roundtrips to/from client 1 0.1
table fetch by rowid 20 1.0
user calls 2 0.1
user commits 1 0.1
-------------------------------------------------------------
Tablespace IO Stats for DB: ALEF Instance: alef Snaps: 3 -4
->ordered by IOs (Reads + Writes) desc
Tablespace
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
Tablespace Filename
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
TMP C:\ORACLE\ORADATA\ALEF\TMP.DBF
212 10 6.1 3.2 236 11 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: ALEF Instance: alef Snaps: 3 -4
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
D 15,180 100.0 166,312 0 0 0
0 0
-------------------------------------------------------------
Instance Recovery Stats for DB: ALEF Instance: alef Snaps: 3 -4 -> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size TimeoutInterval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ----------
B 0 58 1900 194210 194210 921600 194210
E 0 58 2549 243555 243555 921600 243555
-------------------------------------------------------------
Buffer Pool Advisory for DB: ALEF Instance: alef End Snap: 4 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------PGA Aggr Target Stats for DB: ALEF Instance: alef Snaps: 3 -4 -> B: Begin snap E: End snap (rows dentified with B or E contain data
------------------
D 24 .1 1,518 20.56 49,508,886 D 48 .2 3,036 8.13 19,569,781 D 72 .3 4,554 6.32 15,224,822 D 96 .4 6,072 5.42 13,042,042 D 120 .5 7,590 3.67 8,834,123 D 144 .6 9,108 3.20 7,711,832 D 168 .7 10,626 2.30 5,528,156 D 192 .8 12,144 1.85 4,464,981 D 216 .9 13,662 1.54 3,719,772 D 240 1.0 15,180 1.00 2,408,492 D 264 1.1 16,698 0.95 2,286,679 D 288 1.2 18,216 0.80 1,934,676 D 312 1.3 19,734 0.71 1,709,859 D 336 1.4 21,252 0.65 1,567,446 D 360 1.5 22,770 0.63 1,512,809 D 384 1.6 24,288 0.63 1,509,226 D 408 1.7 25,806 0.63 1,508,330 D 432 1.8 27,324 0.62 1,504,748 D 456 1.9 28,842 0.62 1,504,748 D 480 2.0 30,360 0.62 1,504,748 -------------------------------------------------------------
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only
in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual +
auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto
Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual
control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
60.5 9 6
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A
Global Mem
PGA Aggr Target Histogram for DB: ALEF Instance: alef Snaps: 3 -4 -> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
16K 32K 89 89 0 0
32K 64K 4 4 0 0
64K 128K 1 1 0 0
512K 1024K 2 2 0 0
2M 4M 2 0 2 0
-------------------------------------------------------------
PGA Memory Advisory for DB: ALEF Instance: alef End Snap: 4 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd
PGA
PGA Target Size W/A MB W/A MB Read/ Cache
Overalloc
Est (MB) Factr Processed Written to Disk Hit %
Count
---------- ------- ---------------- ---------------- --------
----------
14 0.5 42,411.1 42.6 100.0
12
21 0.8 42,411.1 14.0 100.0
7
29 1.0 42,411.1 5.5 100.0
6
34 1.2 42,411.1 5.5 100.0
5
40 1.4 42,411.1 5.5 100.0
4
46 1.6 42,411.1 5.5 100.0
3
51 1.8 42,411.1 5.5 100.0
3
57 2.0 42,411.1 2.9 100.0
2
86 3.0 42,411.1 0.0 100.0
0
114 4.0 42,411.1 0.0 100.0
0
172 6.0 42,411.1 0.0 100.0
0
229 8.0 42,411.1 0.0 100.0
0
-------------------------------------------------------------
Rollback Segment Stats for DB: ALEF Instance: alef Snaps: 3 -4
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
0 1.0 0.00 0 0 0 0
1 1.0 0.00 0 0 0 0
2 1.0 0.00 0 0 0 0
3 1.0 0.00 0 0 0 0
4 1.0 0.00 0 0 0 0
5 45.0 0.00 253,802 2 0 0
6 2.0 0.00 0 0 0 0
7 495.0 0.00 6,607,496 19 0 10
8 2.0 0.00 2,436 0 0 0
9 1.0 0.00 0 0 0 0
10 1.0 0.00 0 0 0 0
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 442,368 6,553 442,368
1 507,904 289,784 5,357,568
2 442,368 1,542,769 9,551,872
3 7,454,720 4,842,640 18,989,056
4 507,904 127,533 6,406,144
5 7,323,648 5,596,574 7,323,648
6 1,163,264 2,803,943 7,520,256
7 7,323,648 8,093,488 20,037,632
8 376,832 139,607 17,940,480
9 638,976 2,238,838 8,372,224
10 14,663,680 5,649,321 14,663,680
-------------------------------------------------------------
Latch Activity for DB: ALEF Instance: alef Snaps: 3 -4
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics
for
Pct Avg Wait
Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Subobject Obj.
Logical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----Dictionary Cache Stats for DB: ALEF Instance: alef Snaps: 3 -4
------------ -------
PERFSTAT USERS STATS$SQL_SUMMARY_PK INDEX 29,504 96.14 SYS SYSTEM USER$ TABLE 448 1.46 SYS SYSTEM I_OBJ1 INDEX 336 1.09 SYS SYSTEM I_FILE#_BLOCK# INDEX 64 .21 SYS SYSTEM I_CCOL2 INDEX 64 .21 -------------------------------------------------------------
->"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
Cache Requests Miss Reqs Miss Reqs
Usage
------------------------- ------------ ------ ------- ----- --------
dc_free_extents 30 33.3 10 0.0 30
3
dc_histogram_defs 163 66.9 0 0
278
dc_object_ids 91 0.0 0 0
771
dc_objects 48 0.0 0 0
1,496
dc_segments 55 0.0 0 10
533
dc_tablespace_quotas 10 0.0 0 10
9
dc_tablespaces 82 0.0 0 0
18
dc_used_extents 10 100.0 0 10
97
dc_user_grants 32 0.0 0 0
39
dc_usernames 15 0.0 0 0
29
dc_users 86 0.0 0 0
43
-------------------------------------------------------------
Library Cache Activity for DB: ALEF Instance: alef Snaps: 3 -4 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
CLUSTER 7 0.0 11 0.0 0
0
SQL AREA 143 0.0 100,759 0.0 1
0
TABLE/PROCEDURE 123 0.0 330 2.7 0
0
-------------------------------------------------------------
Shared Pool Advisory for DB: ALEF Instance: alef End Snap: 4
-> Note there is often a 1:Many correlation between a single logical
object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd
Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
Obj Hits
----------- ----- ---------- ------------ ------------ -------
120 .5 119 7,623 30,994 1.0 7,313,661
144 .6 142 9,460 30,995 1.0 7,313,864
168 .7 165 11,320 30,995 1.0 7,314,067
192 .8 188 12,796 30,995 1.0 7,314,195
216 .9 211 14,626 30,995 1.0 7,314,311
240 1.0 234 16,471 31,000 1.0 7,315,055
264 1.1 257 18,341 31,002 1.0 7,315,464
288 1.2 280 20,506 31,006 1.0 7,315,938
312 1.3 303 22,228 31,009 1.0 7,316,207
336 1.4 326 24,203 31,011 1.0 7,316,956
360 1.5 349 25,233 31,015 1.0 7,317,565
384 1.6 372 26,986 31,020 1.0 7,318,547
408 1.7 395 28,894 31,024 1.0 7,319,058
432 1.8 418 30,292 31,024 1.0 7,319,133
456 1.9 441 32,095 31,024 1.0 7,319,148
480 2.0 464 33,992 31,024 1.0 7,319,269
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 251,658,240 Fixed Size 454,156 Redo Buffers 5,140,480 Variable Size 293,601,280 ---------------- sum 550,854,156 -------------------------------------------------------------
SGA breakdown difference for DB: ALEF Instance: alef Snaps: 3 -4
Pool Name Begin value Endvalue % Diff
------ ------------------------------ ----------------init.ora Parameters for DB: ALEF Instance: alef Snaps: 3 -4
---------------- -------
java free memory 16,777,216 16,777,216 0.00 large free memory 8,388,608 8,388,608 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 282,304 282,304 0.00 shared FileIdentificatonBlock 323,292 323,292 0.00 shared FileOpenBlock 695,504 695,504 0.00 shared KGK heap 3,768 3,768 0.00 shared KGLS heap 2,697,968 2,740,792 1.59 shared KQR M PO 1,286,608 1,342,456 4.34 shared KQR S PO 256,936 256,936 0.00 shared KQR S SO 9,984 9,984 0.00 shared KQR X PO 4,864 4,864 0.00 shared KSXR large reply queue 166,104 166,104 0.00 shared KSXR pending messages que 841,036 841,036 0.00 shared KSXR receive buffers 1,033,000 1,033,000 0.00 shared PL/SQL DIANA 3,349,584 3,349,584 0.00 shared PL/SQL MPCODE 780,136 780,136 0.00 shared PLS non-lib hp 2,068 2,068 0.00 shared character set object 392,368 392,368 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared enqueue 171,860 171,860 0.00 shared errors 42,612 42,612 0.00 shared event statistics per sess 1,718,360 1,718,360 0.00 shared fixed allocation callback 180 180 0.00 shared free memory 16,697,172 16,693,304 -0.02 shared joxs heap init 1,244 1,244 0.00 shared kgl simulator 4,648,196 4,648,196 0.00 shared library cache 27,913,852 27,874,604 -0.14 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 4,783,592 4,813,440 0.62 shared parameters 22,596 22,596 0.00 shared pl/sql source 7,752 7,752 0.00 shared sessions 410,720 410,720 0.00 shared sim memory hea 71,996 71,996 0.00 shared sql area 195,185,012 195,099,384 -0.04 shared table definiti 8,296 8,520 2.70 shared trigger defini 23,236 23,236 0.00 shared trigger inform 2,324 2,324 0.00 shared trigger source 3,736 3,736 0.00 shared type object de 53,388 53,388 0.00 buffer_cache 251,658,240 251,658,240 0.00 fixed_sga 454,156 454,156 0.00 log_buffer 5,132,288 5,132,288 0.00 -------------------------------------------------------------
End
value
Parameter Name Begin value (if
different)
background_dump_dest c:\oracle\admin\alef\bdump
compatible 9.2.0.0.0
control_files c:\oracle\oradata\alef\control01.
core_dump_dest c:\oracle\admin\alef\cdump
db_block_size 16384
db_cache_size 251658240
db_domain
db_file_multiblock_read_count 64
db_name alef
hash_join_enabled TRUE
ifile c:\oracle\admin\alef\pfile\init.o
instance_name alef
java_pool_size 16777216
large_pool_size 8388608
log_buffer 5000192
open_cursors 300
pga_aggregate_target 30000000
processes 150
query_rewrite_enabled FALSE
shared_pool_size 251658240
sort_area_retained_size 4000000
sort_area_size 4000000
undo_management AUTO
undo_retention 3600
undo_tablespace UNDOTBS
user_dump_dest c:\oracle\admin\alef\udump
utl_file_dir c:\oracle\utl_file
-------------------------------------------------------------
End of Report
I hope SOMETHING.
Thank you.
Kamal Received on Tue Jun 03 2003 - 05:53:56 CDT
 |
 |