STATSPACK REPORT (suggestıon)
582889Mar 22 2008 — edited Apr 7 2008hi
The database is oracle 10gXE on windows xp.An application is runnıng on the database but it is very slow.
I have generated statspack report.Any suggestions?
```
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2520550172 xe 1 17-Mar-08 11:42 10.2.0.1.0 NO
Host Name: ORCL Num CPUs: 2 Phys Memory (MB): 990
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 21-Mar-08 11:00:04 27 2.9
End Snap: 59 21-Mar-08 23:01:56 24 3.2
Elapsed: 721.87 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 132M Std Block Size: 8K
Shared Pool Size: 96M Log Buffer: 2,792K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 2,413.79 4,566.13
Logical reads: 1,981.86 3,749.06
Block changes: 11.38 21.52
Physical reads: 546.40 1,033.61
Physical writes: 1.44 2.73
User calls: 16.33 30.89
Parses: 6.79 12.85
Hard parses: 0.41 0.77
Sorts: 4.42 8.36
Logons: 0.01 0.02
Executes: 11.20 21.18
Transactions: 0.53
% Blocks changed per Read: 0.57 Recursive Call %: 79.44
Rollback per transaction %: 0.01 Rows per Sort: 49.78
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.94 Redo NoWait %: 99.96
Buffer Hit %: 72.44 In-memory Sort %: 100.00
Library Hit %: 93.29 Soft Parse %: 93.97
Execute to Parse %: 39.35 Latch Hit %: 99.92
Parse CPU to Parse Elapsd %: 34.66 % Non-Parse CPU: 95.78
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 72.44 80.75
% SQL with executions>1: 69.45 82.36
% Memory for SQL w/exec>1: 86.09 87.87
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file scattered read 1,324,791 10,488 8 49.1
db file sequential read 913,916 7,789 9 36.4
CPU time 1,902 8.9
read by other session 49,612 430 9 2.0
control file sequential read 24,739 160 6 .7
-------------------------------------------------------------
Host CPU (CPUs: 2)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
4.18 3.75 92.07
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 2.28
% of busy CPU for Instance: 28.79
%DB time waiting for CPU - Resource Mgr: 0.00
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 990.4 990.4
SGA use (MB): 240.0 240.0
PGA use (MB): 48.8 46.2
% Host Mem used for SGA+PGA: 29.2 28.9
-------------------------------------------------------------
Time Model System Stats DB/Inst: XE/xe Snaps: 1-59
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 20,404.8 ########
DB CPU 1,885.0 ########
parse time elapsed 305.3 ########
hard parse elapsed time 272.7 ########
PL/SQL execution elapsed time 6.9 ########
hard parse (sharing criteria) elaps 6.0 ########
PL/SQL compilation elapsed time 4.8 ########
connection management call elapsed 3.1 ########
hard parse (bind mismatch) elapsed 2.5 ########
failed parse elapsed time 1.0 ########
sequence load elapsed time 0.8 ########
repeated bind elapsed time 0.4 ########
DB time 20,580.8
background elapsed time 991.5
background cpu time 91.9
-------------------------------------------------------------
Wait Events DB/Inst: XE/xe Snaps: 1-59
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file scattered read 1,324,791 0 10,488 8 57.9
db file sequential read 913,916 0 7,789 9 39.9
read by other session 49,612 0 430 9 2.2
control file sequential read 24,739 0 160 6 1.1
db file parallel read 2,351 0 136 58 0.1
log file parallel write 30,287 0 124 4 1.3
db file parallel write 24,872 0 102 4 1.1
log file sync 22,839 0 98 4 1.0
control file parallel write 14,574 0 62 4 0.6
resmgr:cpu quantum 2,840 0 36 13 0.1
log file sequential read 144 0 32 222 0.0
direct path read 3,602 0 7 2 0.2
Log archive I/O 174 0 5 29 0.0
direct path read temp 1,425 0 5 3 0.1
SQL*Net more data to client 45,754 0 3 0 2.0
os thread startup 51 0 1 20 0.0
log file switch completion 8 0 1 85 0.0
direct path write temp 3,830 0 1 0 0.2
SQL*Net break/reset to client 2,158 0 0 0 0.1
latch: shared pool 55 0 0 6 0.0
latch free 76 0 0 3 0.0
latch: library cache 26 0 0 7 0.0
rdbms ipc reply 164 0 0 1 0.0
direct path write 792 0 0 0 0.0
log file single write 12 0 0 3 0.0
latch: cache buffers chains 147 0 0 0 0.0
latch: undo global data 1 0 0 23 0.0
LGWR wait for redo copy 119 0 0 0 0.0
latch: cache buffers lru chain 57 0 0 0 0.0
buffer busy waits 4 0 0 3 0.0
SQL*Net message from client 499,278 0 431,618 864 21.8
virtual circuit status 5,776 100 172,470 29860 0.3
Streams AQ: qmn slave idle wait 1,543 0 43,140 27958 0.1
Streams AQ: qmn coordinator idle 3,115 51 43,140 13849 0.1
Streams AQ: waiting for time mana 10 100 18,589 ###### 0.0
jobq slave wait 488 99 1,460 2991 0.0
class slave wait 25 100 125 4998 0.0
SQL*Net message to client 499,276 0 2 0 21.8
SQL*Net more data from client 15,990 0 1 0 0.7
-------------------------------------------------------------
Background Wait Events DB/Inst: XE/xe Snaps: 1-59
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file sequential read 10,711 0 172 16 0.5
log file parallel write 30,288 0 124 4 1.3
db file parallel write 24,872 0 102 4 1.1
control file sequential read 9,180 0 92 10 0.4
control file parallel write 14,574 0 62 4 0.6
log file sequential read 144 0 32 222 0.0
db file scattered read 518 0 9 17 0.0
Log archive I/O 174 0 5 29 0.0
os thread startup 51 0 1 20 0.0
events in waitclass Other 303 0 0 0 0.0
direct path write 24 0 0 3 0.0
log file switch completion 1 0 0 70 0.0
log file single write 12 0 0 3 0.0
latch: shared pool 2 0 0 16 0.0
latch: library cache 1 0 0 24 0.0
buffer busy waits 2 0 0 1 0.0
rdbms ipc message 168,419 83 455,399 2704 7.4
pmon timer 15,077 100 43,278 2870 0.7
Streams AQ: qmn slave idle wait 1,543 0 43,140 27958 0.1
Streams AQ: qmn coordinator idle 3,115 51 43,140 13849 0.1
smon timer 165 78 40,962 ###### 0.0
Streams AQ: waiting for time mana 10 100 18,589 ###### 0.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: XE/xe Snaps: 1-59
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
buffer busy waits 4 50.0 25.0 25.0
control file parallel writ 14K 74.4 2.7 3.9 5.9 6.1 4.6 2.3
control file sequential re 24K 70.8 1.5 1.0 6.0 5.9 8.5 6.3
db file parallel read 2351 18.8 6.0 2.9 3.5 8.0 11.5 49.3
db file parallel write 24K 65.8 3.1 4.5 9.1 11.0 5.2 1.3
db file scattered read 1324K 39.9 8.7 11.8 13.2 10.6 9.4 6.4 .0
db file sequential read 913K 51.9 3.2 3.0 7.6 15.4 12.4 6.4
direct path read 3602 91.7 .5 .4 1.4 3.0 .9 2.0
direct path read temp 1425 83.2 1.5 .8 2.0 4.5 5.5 2.5
direct path write 792 99.6 .3 .1
direct path write temp 3830 97.3 .5 1.1 .3 .7 .1
latch: cache buffers chain 122 96.7 2.5 .8
latch: cache buffers lru c 57 98.2 1.8
latch free 74 78.4 4.1 2.7 8.1 6.8
latch: library cache 26 19.2 7.7 19.2 26.9 19.2 3.8 3.8
latch: row cache objects 2 100.0
latch: shared pool 53 49.1 5.7 11.3 7.5 17.0 7.5 1.9
latch: undo global data 1 100.0
LGWR wait for redo copy 119 95.8 4.2
Log archive I/O 174 31.0 1.7 7.5 59.8
log file parallel write 30K 76.2 2.2 2.3 3.2 7.5 6.1 2.5
log file sequential read 144 7.6 1.4 .7 6.9 24.3 53.5 5.6
log file single write 12 83.3 16.7
log file switch completion 8 12.5 87.5
log file sync 22K 74.4 2.5 2.6 3.3 8.4 6.4 2.4
os thread startup 51 5.9 60.8 19.6 13.7
rdbms ipc reply 164 98.2 .6 .6 .6
read by other session 49K 57.3 6.9 3.6 4.3 6.4 12.5 9.0
resmgr:cpu quantum 2840 66.5 7.4 9.3 3.8 3.8 1.7 7.5
SQL*Net break/reset to cli 2158 99.8 .1 .1
SQL*Net more data to clien 45K 100.0 .0 .0
undo segment extension 4 100.0
class slave wait 25 100.0
dispatcher timer 722 .1 99.9
jobq slave wait 488 100.0
pmon timer 15K 4.2 .0 .0 95.7
rdbms ipc message 168K 2.5 .4 1.1 .8 1.2 1.8 34.9 57.3
smon timer 165 .6 99.4
SQL*Net message from clien 499K 58.0 11.8 5.2 10.6 2.2 1.4 4.1 6.6
SQL*Net message to client 499K 100.0 .0 .0
SQL*Net more data from cli 15K 99.9 .0 .0
Streams AQ: qmn coordinato 3115 48.8 .0 .0 .0 .0 51.1
Streams AQ: qmn slave idle 1543 100.0
Streams AQ: waiting for ti 10 100.0
virtual circuit status 5776 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: XE/xe Snaps: 1-59
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 1,885
-> Captured SQL accounts for 44.5% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
705.98 6,279 0.11 37.5 6855.58 28,849,551 3248049967
Module: aspnet_wp.exe
SELECT SHIPMENT.waybillid, SHIPMENT.APPLICATIONID, SHIPMENT.
APPLICATION_VERSION, SHIPMENT.AUDITCREATEDBY,
SHIPMENT.AUDITCREATEUNITID, SHIPMENT.AUDITMODIFIEDBY, SHIP
MENT.AUDITMODIFYUNITID, SHIPMENT.AUDIT_CREATE_DATE,
SHIPMENT.AUDIT_DELETED, SHIPMENT.AUDIT_MODIFY_DATE,
36.17 2,200 0.02 1.9 42.00 7,794,780 2473420379
Module: Replication.Windows.exe
select ac.constraint_name key_name, acc.column_name key_col,1 fr
om all_cons_columns acc, all_constraints ac where acc.owner = ac
.owner and acc.constraint_name = ac.constraint_name and acc.tabl
e_name = ac.table_name and ac.constraint_type = 'P' and ac.owner
= user and ac.table_name = :TableName order by acc.constraint_n
30.59 293 0.10 1.6 204.30 1,348,848 1027974040
Module: aspnet_wp.exe
UPDATE SHIPMENT SET FIRST_PIECE_ARRIVAL_DATE = :firstDate, LAST_PIECE_ARRIVAL_DATE = :lastDate,AUDIT_MODIFY_DATE=CURRENT_
TIMESTAMP, LOVSHIPMENTSTATUSID = :shipmentStatus,
RESPONSIBL
EUNITID = :unitId WHERE (SHIPMENT_CODE = :shipmentCode)
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: XE/xe Snaps: 1-59
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 20,581
-> Captured SQL accounts for 42.5% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
6855.58 6,279 1.09 33.3 705.98 16,792,312 3248049967
Module: aspnet_wp.exe
SELECT SHIPMENT.waybillid, SHIPMENT.APPLICATIONID, SHIPMENT.
APPLICATION_VERSION, SHIPMENT.AUDITCREATEDBY,
SHIPMENT.AUDITCREATEUNITID, SHIPMENT.AUDITMODIFIEDBY, SHIP
MENT.AUDITMODIFYUNITID, SHIPMENT.AUDIT_CREATE_DATE,
SHIPMENT.AUDIT_DELETED, SHIPMENT.AUDIT_MODIFY_DATE,
936.18 12 78.01 4.5 4.54 50,374 1764973207
Module: Replication.Windows.exe
Select * from ACCOUNTADDRESS where AUDITMODIFYUNITID = 8 AND (TR
ANSFER_STATUS = '0' OR TRANSFER_STATUS='1' OR TRANSFER_STATUS IS
NULL) AND AUDIT_MODIFY_DATE > TO_DATE('20.02.2008','dd.mm.yyyy'
) AND rownum <= 50 order by AUDIT_MODIFY_DATE
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: XE/xe Snaps: 1-59
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 85,838,510
-> Captured SQL accounts for 45.8% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
28,849,551 6,279 4,594.6 33.6 705.98 6855.58 3248049967
Module: aspnet_wp.exe
SELECT SHIPMENT.waybillid, SHIPMENT.APPLICATIONID, SHIPMENT.
APPLICATION_VERSION, SHIPMENT.AUDITCREATEDBY,
SHIPMENT.AUDITCREATEUNITID, SHIPMENT.AUDITMODIFIEDBY, SHIP
MENT.AUDITMODIFYUNITID, SHIPMENT.AUDIT_CREATE_DATE,
SHIPMENT.AUDIT_DELETED, SHIPMENT.AUDIT_MODIFY_DATE,
7,794,780 2,200 3,543.1 9.1 36.17 42.00 2473420379
Module: Replication.Windows.exe
select ac.constraint_name key_name, acc.column_name key_col,1 fr
om all_cons_columns acc, all_constraints ac where acc.owner = ac
.owner and acc.constraint_name = ac.constraint_name and acc.tabl
e_name = ac.table_name and ac.constraint_type = 'P' and ac.owner
= user and ac.table_name = :TableName order by acc.constraint_n
1,348,848 293 4,603.6 1.6 30.59 204.30 1027974040
Module: aspnet_wp.exe
UPDATE SHIPMENT SET FIRST_PIECE_ARRIVAL_DATE = :firstDate, LAST_PIECE_ARRIVAL_DATE = :lastDate,AUDIT_MODIFY_DATE=CURRENT_
TIMESTAMP, LOVSHIPMENTSTATUSID = :shipmentStatus,
RESPONSIBL
EUNITID = :unitId WHERE (SHIPMENT_CODE = :shipmentCode)
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: XE/xe Snaps: 1-59
-> End Disk Reads Threshold: 1000 Total Disk Reads: 23,665,483
-> Captured SQL accounts for 73.4% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
16,792,312 6,279 2,674.4 71.0 705.98 6855.58 3248049967
Module: aspnet_wp.exe
SELECT SHIPMENT.waybillid, SHIPMENT.APPLICATIONID, SHIPMENT.
APPLICATION_VERSION, SHIPMENT.AUDITCREATEDBY,
SHIPMENT.AUDITCREATEUNITID, SHIPMENT.AUDITMODIFIEDBY, SHIP
MENT.AUDITMODIFYUNITID, SHIPMENT.AUDIT_CREATE_DATE,
SHIPMENT.AUDIT_DELETED, SHIPMENT.AUDIT_MODIFY_DATE,
458,891 293 1,566.2 1.9 30.59 204.30 1027974040
Module: aspnet_wp.exe
UPDATE SHIPMENT SET FIRST_PIECE_ARRIVAL_DATE = :firstDate, LAST_PIECE_ARRIVAL_DATE = :lastDate,AUDIT_MODIFY_DATE=CURRENT_
TIMESTAMP, LOVSHIPMENTSTATUSID = :shipmentStatus,
RESPONSIBL
EUNITID = :unitId WHERE (SHIPMENT_CODE = :shipmentCode)
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: XE/xe Snaps: 1-59
-> End Executions Threshold: 100 Total Executions: 484,910
-> Captured SQL accounts for 37.2% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
37,113 22,595 0.6 0.00 0.00 4274598960
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
16,799 22,943 1.4 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
15,881 255,229 16.1 0.00 0.00 199663413
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
10,126 10,126 1.0 0.00 0.00 1348827743
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2
and block#=:3
9,915 16,112 1.6 0.00 0.00 2889900621
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1
8,660 24 0.0 0.00 0.00 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
8,636 8,636 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
6,279 355 0.1 0.11 1.09 3248049967
Module: aspnet_wp.exe
SELECT SHIPMENT.waybillid, SHIPMENT.APPLICATIONID, SHIPMENT.
APPLICATION_VERSION, SHIPMENT.AUDITCREATEDBY,
SHIPMENT.AUDITCREATEUNITID, SHIPMENT.AUDITMODIFIEDBY, SHIP
MENT.AUDITMODIFYUNITID, SHIPMENT.AUDIT_CREATE_DATE,
SHIPMENT.AUDIT_DELETED, SHIPMENT.AUDIT_MODIFY_DATE,
5,628 5,626 1.0 0.00 0.00 431456802
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
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: XE/xe Snaps: 1-59
-> End Parse Calls Threshold: 1000 Total Parse Calls: 294,114
-> Captured SQL accounts for 24.1% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
10,126 10,126 3.44 1348827743
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2
and block#=:3
6,279 6,279 2.13 3248049967
Module: aspnet_wp.exe
SELECT SHIPMENT.waybillid, SHIPMENT.APPLICATIONID, SHIPMENT.
APPLICATION_VERSION, SHIPMENT.AUDITCREATEDBY,
SHIPMENT.AUDITCREATEUNITID, SHIPMENT.AUDITMODIFIEDBY, SHIP
MENT.AUDITMODIFYUNITID, SHIPMENT.AUDIT_CREATE_DATE,
SHIPMENT.AUDIT_DELETED, SHIPMENT.AUDIT_MODIFY_DATE,
4,380 83 1.49 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)
-------------------------------------------------------------
Instance Activity Stats DB/Inst: XE/xe Snaps: 1-59
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
active txn count during cleanout 1,918 0.0 0.1
application wait time 33 0.0 0.0
background checkpoints completed 6 0.0 0.0
background checkpoints started 6 0.0 0.0
background timeouts 139,419 3.2 6.1
branch node splits 0 0.0 0.0
buffer is not pinned count 41,949,891 968.6 1,832.2
buffer is pinned count 118,856,670 2,744.2 5,191.2
bytes received via SQL*Net from c 198,167,600 4,575.4 8,655.1
bytes sent via SQL*Net to client 318,403,898 7,351.4 13,906.5
Cached Commit SCN referenced 33,834,455 781.2 1,477.8
calls to get snapshot scn: kcmgss 813,809 18.8 35.5
calls to kcmgas 35,515 0.8 1.6
calls to kcmgcs 1,428 0.0 0.1
change write time 429 0.0 0.0
cleanout - number of ktugct calls 2,858 0.1 0.1
cleanouts and rollbacks - consist 260 0.0 0.0
cleanouts only - consistent read 363 0.0 0.0
cluster key scan block gets 125,349 2.9 5.5
cluster key scans 47,845 1.1 2.1
commit batch/immediate performed 665 0.0 0.0
commit batch/immediate requested 665 0.0 0.0
commit cleanout failures: block l 1 0.0 0.0
commit cleanout failures: buffer 1 0.0 0.0
commit cleanout failures: callbac 230 0.0 0.0
commit cleanout failures: cannot 6 0.0 0.0
commit cleanouts 151,855 3.5 6.6
commit cleanouts successfully com 151,617 3.5 6.6
commit immediate performed 665 0.0 0.0
commit immediate requested 665 0.0 0.0
Commit SCN cached 332 0.0 0.0
commit txn count during cleanout 1,679 0.0 0.1
concurrency wait time 155 0.0 0.0
consistent changes 1,846 0.0 0.1
consistent gets 85,266,119 1,968.7 3,724.1
consistent gets - examination 30,225,229 697.9 1,320.1
consistent gets direct 6,412 0.2 0.3
consistent gets from cache 85,259,707 1,968.5 3,723.8
CPU used by this session 190,215 4.4 8.3
CPU used when call started 185,619 4.3 8.1
CR blocks created 567 0.0 0.0
current blocks converted for CR 0 0.0 0.0
cursor authentications 4,294 0.1 0.2
data blocks consistent reads - un 797 0.0 0.0
db block changes 492,744 11.4 21.5
db block gets 572,391 13.2 25.0
db block gets direct 1,676 0.0 0.1
db block gets from cache 570,715 13.2 24.9
DB time 2,193,432 50.6 95.8
DBWR checkpoint buffers written 5,283 0.1 0.2
DBWR checkpoints 6 0.0 0.0
DBWR tablespace checkpoint buffer 0 0.0 0.0
DBWR thread checkpoint buffers wr 5,283 0.1 0.2
DBWR transaction table writes 226 0.0 0.0
DBWR undo block writes 6,279 0.1 0.3
deferred (CURRENT) block cleanout 116,628 2.7 5.1
Instance Activity Stats DB/Inst: XE/xe Snaps: 1-59
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
dirty buffers inspected 44,112 1.0 1.9
drop segment calls in space press 0 0.0 0.0
enqueue conversions 9,816 0.2 0.4
enqueue releases 445,619 10.3 19.5
enqueue requests 445,619 10.3 19.5
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 484,910 11.2 21.2
free buffer inspected 23,943,362 552.8 1,045.7
free buffer requested 23,667,208 546.4 1,033.7
global undo segment hints helped 0 0.0 0.0
heap block compress 1,723 0.0 0.1
hot buffers moved to head of LRU 392,343 9.1 17.1
IMU commits 18,039 0.4 0.8
IMU contention 4 0.0 0.0
IMU CR rollbacks 156 0.0 0.0
IMU- failed to get a private stra 4,711 0.1 0.2
IMU Flushes 2,003 0.1 0.1
IMU ktichg flush 0 0.0 0.0
IMU pool not allocated 4,711 0.1 0.2
IMU recursive-transaction flush 12 0.0 0.0
IMU Redo allocation size 9,098,580 210.1 397.4
IMU undo allocation size 43,536,696 1,005.2 1,901.5
immediate (CR) block cleanout app 623 0.0 0.0
immediate (CURRENT) block cleanou 9,523 0.2 0.4
index fast full scans (full) 4,540 0.1 0.2
index fetch by key 18,295,574 422.4 799.1
index scans kdiixs1 4,595,032 106.1 200.7
leaf node splits 1,078 0.0 0.1
leaf node 90-10 splits 170 0.0 0.0
lob reads 7,302 0.2 0.3
lob writes 2,463 0.1 0.1
lob writes unaligned 2,394 0.1 0.1
local undo segment hints helped 0 0.0 0.0
logons cumulative 437 0.0 0.0
messages received 53,595 1.2 2.3
messages sent 53,595 1.2 2.3
Misses for writing mapping 1 0.0 0.0
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 50,452,090 1,164.9 2,203.5
opened cursors cumulative 394,724 9.1 17.2
parse count (failures) 68 0.0 0.0
parse count (hard) 17,725 0.4 0.8
parse count (total) 294,114 6.8 12.9
parse time cpu 8,022 0.2 0.4
parse time elapsed 23,144 0.5 1.0
physical read bytes 193,867,636,736 4,476,072.1 8,467,314.7
physical read IO requests 2,266,445 52.3 99.0
physical read total bytes 194,394,011,648 4,488,225.2 8,490,304.5
physical read total IO requests 2,291,379 52.9 100.1
physical read total multi block r 1,324,991 30.6 57.9
physical reads 23,665,483 546.4 1,033.6
physical reads cache 23,657,001 546.2 1,033.2
physical reads cache prefetch 21,417,493 494.5 935.4
physical reads direct 8,482 0.2 0.4
physical reads direct (lob) 4,868 0.1 0.2
Instance Activity Stats DB/Inst: XE/xe Snaps: 1-59
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical reads direct temporary t 3,389 0.1 0.2
physical reads prefetch warmup 0 0.0 0.0
physical write bytes 511,287,296 11,804.8 22,330.9
physical write IO requests 45,357 1.1 2.0
physical write total bytes 986,972,160 22,787.5 43,106.8
physical write total IO requests 90,390 2.1 4.0
physical write total multi block 34,132 0.8 1.5
physical writes 62,413 1.4 2.7
physical writes direct 6,291 0.2 0.3
physical writes direct (lob) 356 0.0 0.0
physical writes direct temporary 5,881 0.1 0.3
physical writes from cache 56,122 1.3 2.5
physical writes non checkpoint 59,514 1.4 2.6
pinned buffers inspected 2,173 0.1 0.1
prefetch warmup blocks aged out b 0 0.0 0.0
prefetched blocks aged out before 50 0.0 0.0
process last non-idle time 43,315 1.0 1.9
recovery blocks read 0 0.0 0.0
recursive calls 2,733,215 63.1 119.4
recursive cpu usage 17,951 0.4 0.8
redo blocks read for recovery 0 0.0 0.0
redo blocks written 227,104 5.2 9.9
redo buffer allocation retries 76 0.0 0.0
redo entries 185,258 4.3 8.1
redo log space requests 76 0.0 0.0
redo log space wait time 66 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 104,546,020 2,413.8 4,566.1
redo synch time 10,055 0.2 0.4
redo synch writes 32,517 0.8 1.4
redo wastage 8,073,948 186.4 352.6
redo write time 12,612 0.3 0.6
redo writer latching time 5 0.0 0.0
redo writes 30,288 0.7 1.3
rollback changes - undo records a 706 0.0 0.0
rollbacks only - consistent read 310 0.0 0.0
rows fetched via callback 14,626,888 337.7 638.8
session connect time 0 0.0 0.0
session cursor cache hits 341,568 7.9 14.9
session logical reads 85,838,510 1,981.9 3,749.1
session pga memory 39,516,768 912.4 1,725.9
session pga memory max 83,950,176 1,938.3 3,666.6
session uga memory 103,089,491,456 2,380,160.0 4,502,511.0
session uga memory max 664,645,440 15,345.5 29,028.9
shared hash latch upgrades - no w 4,329,941 100.0 189.1
shared hash latch upgrades - wait 0 0.0 0.0
SMON posted for undo segment shri 7 0.0 0.0
sorts (disk) 1 0.0 0.0
sorts (memory) 191,391 4.4 8.4
sorts (rows) 9,526,930 220.0 416.1
space was found by tune down 0 0.0 0.0
sql area purged 319 0.0 0.0
SQL*Net roundtrips to/from client 498,187 11.5 21.8
steps of tune down ret. in space 0 0.0 0.0
summed dirty queue length 495,242 11.4 21.6
switch current to new buffer 1,308 0.0 0.1
Instance Activity Stats DB/Inst: XE/xe Snaps: 1-59
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table fetch by rowid 61,803,098 1,426.9 2,699.3
table fetch continued row 2,256 0.1 0.1
table scan blocks gotten 41,572,955 959.9 1,815.7
table scan rows gotten 1,047,913,442 24,194.5 45,768.4
table scans (long tables) 8,187 0.2 0.4
table scans (short tables) 74,638 1.7 3.3
total number of times SMON posted 37 0.0 0.0
transaction rollbacks 665 0.0 0.0
tune down retentions in space pre 0 0.0 0.0
undo change vector size 34,291,232 791.7 1,497.7
user calls 707,309 16.3 30.9
user commits 22,894 0.5 1.0
user I/O wait time 1,885,355 43.5 82.3
user rollbacks 2 0.0 0.0
workarea executions - onepass 1 0.0 0.0
workarea executions - optimal 133,412 3.1 5.8
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 6 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: XE/xe Snaps: 1-59
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 27 24
opened cursors current 79 76
session cursor cache count 34,430 40,732
-------------------------------------------------------------
Instance Activity Stats DB/Inst: XE/xe Snaps: 1-59
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 6 .50
-------------------------------------------------------------
OS Statistics DB/Inst: XE/xe Snaps: 1-59
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 686,560
IDLE_TIME 7,974,671
SYS_TIME 324,849
USER_TIME 361,711
RSRC_MGR_CPU_WAIT_TIME 3,520
VM_IN_BYTES 1,365,131,264
VM_OUT_BYTES 702,283,776
PHYSICAL_MEMORY_BYTES 1,038,462,976
NUM_CPUS 2
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: XE/xe Snaps: 1-59
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ESASBRANCH
15,506,280 358 8.1 9.9 214,794 5 488,082 7.9
SYSTEM
144,186 3 19.6 1.5 7,396 0 150 9.3
SYSAUX
112,668 3 20.4 1.4 33,532 1 1 0.0
UNDO
1,566 0 229.1 1.0 10,334 0 17 1.2
TEMP
2,605 0 4.4 1.8 1,378 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: XE/xe Snaps: 1-59
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
ESASBRANCH C:\ESAS\ORACLE\ORADATA\XE\ESASBRANCH.DBF
15,506,280 358 8.1 ### 9.9 214,794 5 488,082 7.9
SYSAUX C:\ESAS\ORACLE\ORADATA\XE\SYSAUX.DBF
112,668 3 20.4 ### 1.4 33,532 1 1 0.0
SYSTEM C:\ESAS\ORACLE\ORADATA\XE\SYSTEM.DBF
144,186 3 19.6 ### 1.5 7,396 0 150 9.3
TEMP C:\ESAS\ORACLE\ORADATA\XE\TEMP.DBF
2,605 0 4.4 ### 1.8 1,378 0 0
UNDO C:\ESAS\ORACLE\ORADATA\XE\UNDO.DBF
1,566 0 ##### ### 1.0 10,334 0 17 1.2
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: XE/xe Snaps: 1-59
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSAUX C:\ESAS\ORACLE\ORADATA\XE\SYSAUX.DBF
6,083 915 2,998 2,675 2,845 1,613
SYSTEM C:\ESAS\ORACLE\ORADATA\XE\SYSTEM.DBF
7,291 1,920 4,776 4,623 4,969 4,054
UNDO C:\ESAS\ORACLE\ORADATA\XE\UNDO.DBF
5 1 14 75 45 18
ESASBRANCH C:\ESAS\ORACLE\ORADATA\XE\ESASBRANCH.DBF
489,175 24,713 61,892 133,075 105,841 53,191
TEMP C:\ESAS\ORACLE\ORADATA\XE\TEMP.DBF
971 16 24 79 58 51
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: XE/xe Snaps: 1-59
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 16K 72 85,830,629 23,656,981 56,122 0 0 49,616
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: XE/xe Snaps: 1-59
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 0 685 27711 27399 73710 27399
E 0 0 2918 16554 15958 73710 15958
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: XE/xe End Snap: 59
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 12 .1 1 1.1 162,891 209,991 151.5
D 24 .2 3 1.0 160,225 186,978 134.9
D 36 .3 4 1.0 158,005 167,812 121.1
D 48 .4 6 1.0 156,406 154,006 111.1
D 60 .5 7 1.0 155,520 146,357 105.6
D 72 .5 9 1.0 155,087 142,616 102.9
D 84 .6 10 1.0 154,802 140,156 101.1
D 96 .7 12 1.0 154,004 133,265 96.2
D 108 .8 13 1.0 153,554 129,385 93.4
D 120 .9 15 1.0 153,365 127,753 92.2
D 132 1.0 16 1.0 153,200 126,326 91.1
D 144 1.1 18 1.0 153,067 125,175 90.3
D 156 1.2 19 1.0 152,949 124,157 89.6
D 168 1.3 21 1.0 152,842 123,233 88.9
D 180 1.4 22 1.0 152,754 122,471 88.4
D 192 1.5 24 1.0 152,668 121,736 87.8
D 204 1.5 25 1.0 152,566 120,854 87.2
D 216 1.6 27 1.0 152,288 118,454 85.5
D 228 1.7 28 1.0 151,564 112,199 81.0
D 240 1.8 30 0.9 141,409 79,753 57.5
-------------------------------------------------------------
Buffer wait Statistics DB/Inst: XE/xe Snaps: 1-59
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
---------------------- ----------- ------------------- -------------
data block 49,613 445 9
undo header 3 0 7
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: XE/xe Snaps: 1-59
-> B: Begin snap E: End snap (rows identified with B or E contain data
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
--------------- ---------------- -------------------------
99.8 19,679 40
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 80 50 48.8 0.0 .0 .0 .0 16,384
E 80 52 46.2 0.0 .0 .0 .0 16,384
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: XE/xe Snaps: 1-59
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 108,752 108,752 0 0
64K 128K 141 141 0 0
128K 256K 36 36 0 0
256K 512K 18 18 0 0
512K 1024K 23,042 23,042 0 0
1M 2M 1,384 1,384 0 0
32M 64M 1 0 1 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: XE/xe End Snap: 59
-> 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
---------- ------- ---------------- ---------------- -------- ----------
10 0.1 130,408.9 31,667.7 80.0 3,599
20 0.3 130,408.9 31,667.7 80.0 3,599
40 0.5 130,408.9 195.4 100.0 24
60 0.8 130,408.9 116.2 100.0 12
80 1.0 130,408.9 40.4 100.0 2
96 1.2 130,408.9 40.4 100.0 0
112 1.4 130,408.9 40.4 100.0 0
128 1.6 130,408.9 40.4 100.0 0
144 1.8 130,408.9 40.4 100.0 0
160 2.0 130,408.9 40.4 100.0 0
240 3.0 130,408.9 0.0 100.0 0
320 4.0 130,408.9 0.0 100.0 0
480 6.0 130,408.9 0.0 100.0 0
640 8.0 130,408.9 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: XE/xe Snaps: 1-59
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 48.7 24.4 .0 1.5 2.5 9 16 32
Other 47.6 1.5 2.5 9 9 32
SQL .9 .4 .0 .0 0 15 17
PL/SQL .2 .1 .0 .0 0 0 27
E -------- 46.4 23.1 .0 1.6 2.6 9 20 29
Other 45.6 1.6 2.6 9 9 29
SQL .8 .3 .0 .0 0 18 14
PL/SQL .1 .0 .0 .0 0 0 24
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: XE/xe Snaps: 1-59
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 19 ARC0 -------- 9.1 4.2 .0 9.1 9.1
Other 9.1 9.1 9.1
PL/SQL .0 .0 .0 .0
20 ARC1 -------- 9.1 4.2 .0 9.1 9.1
Other 9.1 9.1 9.1
PL/SQL .0 .0 .0 .0
6 LGWR -------- 8.8 4.2 .0 8.8 9.5
Other 8.8 8.8 9.5
PL/SQL .0 .0 .0 .0
25 J000 -------- 2.2 1.1 .0 2.2 4.1
Other 2.0 2.0 2.0
SQL .2 .1 .2 2.3
PL/SQL .0 .0 .0 .0
11 MMON -------- 1.7 1.2 .0 1.7 3.0
Other 1.6 1.6 2.0
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .1
5 DBW0 -------- 1.6 .3 .0 1.6 1.6
Other 1.6 1.6 1.6
PL/SQL .0 .0 .0 .0
7 CKPT -------- 1.4 .3 .0 1.4 1.4
Other 1.4 1.4 1.4
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
21 SHAD -------- 1.2 .6 .0 1.2 2.7
Other 1.1 1.1 1.5
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
8 SMON -------- 1.1 .5 .0 1.1 1.9
Other 1.0 1.0 1.0
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
30 SHAD -------- 1.1 .5 .0 1.1 5.3
Other 1.0 1.0 1.0
SQL .1 .0 .1 4.8
PL/SQL .0 .0 .0 .0
E 19 ARC0 -------- 9.1 4.2 .0 9.1 9.1
Other 9.1 9.1 9.1
```