Home » RDBMS Server » Performance Tuning » statspack report (Oracle 9i)
| statspack report [message #337645] |
Thu, 31 July 2008 08:40  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear sir
Could you please check the statspack report and advise me what I should do to increase the performance of the Oracle Database
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
cis 936888161 cis 1 9.2.0.4.0 NO cis12
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 141 31-Jul-08 12:05:42 14 18.5
End Snap: 143 31-Jul-08 15:13:02 20 8.1
Elapsed: 187.33 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 8K
Shared Pool Size: 152M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,128.70 15,536.75
Logical reads: 3,020.53 22,045.94
Block changes: 13.56 99.00
Physical reads: 2,744.76 20,033.21
Physical writes: 38.11 278.18
User calls: 3.96 28.88
Parses: 2.04 14.86
Hard parses: 0.18 1.33
Sorts: 2.86 20.88
Logons: 0.01 0.04
Executes: 2.54 18.56
Transactions: 0.14
% Blocks changed per Read: 0.45 Recursive Call %: 62.13
Rollback per transaction %: 85.06 Rows per Sort: 1466.05
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 10.43 In-memory Sort %: 99.25
Library Hit %: 96.13 Soft Parse %: 91.05
Execute to Parse %: 19.97 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 85.88 % Non-Parse CPU: 99.56
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.08 92.91
% SQL with executions>1: 67.11 38.29
% Memory for SQL w/exec>1: 64.72 43.13
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read 2,587,518 2,022 55.87
CPU time 1,390 38.41
db file sequential read 966,114 176 4.86
direct path read 38,989 23 .63
control file parallel write 3,651 3 .09
-------------------------------------------------------------
Wait Events for DB: cis Instance: cis Snaps: 141 -143
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 2,587,518 0 2,022 1 1,680.2
db file sequential read 966,114 0 176 0 627.3
direct path read 38,989 0 23 1 25.3
control file parallel write 3,651 0 3 1 2.4
control file sequential read 1,233 0 2 1 0.8
log file parallel write 13,408 10,378 2 0 8.7
db file parallel write 546 0 1 2 0.4
SQL*Net break/reset to clien 46 0 0 6 0.0
buffer busy waits 460 0 0 0 0.3
log file sync 232 0 0 1 0.2
direct path write 960 0 0 0 0.6
SQL*Net more data to client 1,033 0 0 0 0.7
latch free 28 19 0 2 0.0
LGWR wait for redo copy 226 0 0 0 0.1
SQL*Net message from client 45,549 0 19,990 439 29.6
virtual circuit status 375 375 13,969 37252 0.2
wakeup time manager 354 354 12,244 34588 0.2
SQL*Net more data from clien 52 0 0 5 0.0
SQL*Net message to client 45,555 0 0 0 29.6
-------------------------------------------------------------
Background Wait Events for DB: cis Instance: cis Snaps: 141 -143
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write 3,651 0 3 1 2.4
log file parallel write 13,408 10,378 2 0 8.7
control file sequential read 1,098 0 2 2 0.7
db file scattered read 418 0 1 3 0.3
db file parallel write 546 0 1 2 0.4
db file sequential read 131 0 0 3 0.1
LGWR wait for redo copy 226 0 0 0 0.1
latch free 1 0 0 0 0.0
rdbms ipc message 35,194 21,651 76,207 2165 22.9
pmon timer 3,786 3,786 14,057 3713 2.5
smon timer 37 37 13,504 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: cis Instance: cis Snaps: 141 -143
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
2,439,170 4 609,792.5 7.2 96.22 207.60 1793571320
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5832 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5832 and user_id=22 and mdlid =4 an
2,436,900 4 609,225.0 7.2 92.36 194.52 1829253582
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5688 and user_id=1674 and coai
d =4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subject
ive_prm_trans where borrid=5688 and user_id=1674 and mdlid =
2,414,944 4 603,736.0 7.1 105.19 211.56 4118714993
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=3153 and user_id=22 and coaid
=1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=3153 and user_id=22 and mdlid =1 an
1,830,996 3 610,332.0 5.4 74.20 193.04 1481346004
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5835 and user_id=210 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=5835 and user_id=210 and mdlid =4
1,471,177 5 294,235.4 4.3 32.89 97.98 2513593745
Module: w3wp.exe
select rownum as sr_no,t.* from ( select pfc.base_entity_id
as borr_id, pfc.pw_id, pfc.propfacname, pfc.id as fac_id, pf
c.parent_facility_id as parent_facility_id, pfc.security_name,
pfc.attached_value, pfc.proposed_sanction_limit as proposed_sa
nction_limit from rvw_cam_report_info pfc where pfc.base_enti
1,343,681 9 149,297.9 4.0 117.11 280.70 3364412532
Module: w3wp.exe
begin sp_riskscoregen_ybl_sme(:V00001,:V00002,:V00003,:V00004,:V
00005,:V00006,:V00007); end;
1,255,281 5 251,056.2 3.7 53.59 151.23 2592635488
Module: w3wp.exe
begin RiskScoreGen_YBL_LC(:V00001,:V00002,:V00003,:V00004,:V0000
5,:V00006,:V00007); end;
SQL ordered by Gets for DB: cis Instance: cis Snaps: 141 -143
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
1,220,630 2 610,315.0 3.6 45.61 97.52 306547636
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5688 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5688 and user_id=24 and mdlid =4 an
1,220,630 2 610,315.0 3.6 47.08 97.59 742978199
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5835 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5835 and user_id=24 and mdlid =4 an
1,220,630 2 610,315.0 3.6 47.47 97.51 4035011226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5835 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5835 and user_id=22 and mdlid =4 an
1,219,540 2 609,770.0 3.6 40.70 90.90 781694832
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5688 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5688 and user_id=22 and mdlid =4 an
1,208,436 2 604,218.0 3.6 45.14 109.06 3173269226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=3317 and user_id=132 and coaid
=1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=3317 and user_id=132 and mdlid =1
1,208,356 2 604,178.0 3.6 43.13 92.92 2962969062
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5834 and user_id=1760 and coai
d =1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subject
ive_prm_trans where borrid=5834 and user_id=1760 and mdlid =
1,208,356 2 604,178.0 3.6 44.56 94.42 3618587409
Module: w3wp.exe
SQL ordered by Gets for DB: cis Instance: cis Snaps: 141 -143
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=2579 and user_id=132 and coaid
=2 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=2579 and user_id=132 and mdlid =2
-------------------------------------------------------------
SQL ordered by Reads for DB: cis Instance: cis Snaps: 141 -143
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
2,376,891 4 594,222.8 7.7 96.22 207.60 1793571320
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5832 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5832 and user_id=22 and mdlid =4 an
2,348,948 4 587,237.0 7.6 105.19 211.56 4118714993
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=3153 and user_id=22 and coaid
=1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=3153 and user_id=22 and mdlid =1 an
2,342,030 4 585,507.5 7.6 92.36 194.52 1829253582
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5688 and user_id=1674 and coai
d =4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subject
ive_prm_trans where borrid=5688 and user_id=1674 and mdlid =
1,783,950 3 594,650.0 5.8 74.20 193.04 1481346004
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5835 and user_id=210 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=5835 and user_id=210 and mdlid =4
1,494,362 9 166,040.2 4.8 117.11 280.70 3364412532
Module: w3wp.exe
begin sp_riskscoregen_ybl_sme(:V00001,:V00002,:V00003,:V00004,:V
00005,:V00006,:V00007); end;
1,212,004 5 242,400.8 3.9 53.59 151.23 2592635488
Module: w3wp.exe
begin RiskScoreGen_YBL_LC(:V00001,:V00002,:V00003,:V00004,:V0000
5,:V00006,:V00007); end;
1,203,629 2 601,814.5 3.9 47.08 97.59 742978199
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5835 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5835 and user_id=24 and mdlid =4 an
1,203,321 2 601,660.5 3.9 40.70 90.90 781694832
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5688 and user_id=22 and coaid
SQL ordered by Reads for DB: cis Instance: cis Snaps: 141 -143
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5688 and user_id=22 and mdlid =4 an
1,203,317 2 601,658.5 3.9 47.47 97.51 4035011226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5835 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5835 and user_id=22 and mdlid =4 an
1,203,241 2 601,620.5 3.9 45.61 97.52 306547636
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5688 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5688 and user_id=24 and mdlid =4 an
1,183,315 2 591,657.5 3.8 45.14 109.06 3173269226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=3317 and user_id=132 and coaid
=1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=3317 and user_id=132 and mdlid =1
1,182,708 2 591,354.0 3.8 44.56 94.42 3618587409
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=2579 and user_id=132 and coaid
=2 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=2579 and user_id=132 and mdlid =2
1,181,987 2 590,993.5 3.8 43.13 92.92 2962969062
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5834 and user_id=1760 and coai
d =1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subject
ive_prm_trans where borrid=5834 and user_id=1760 and mdlid =
1,095,808 2 547,904.0 3.6 46.94 114.33 1420449757
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=3153 and user_id=359 and coaid
=1 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjecti
ve_prm_trans where borrid=3153 and user_id=359 and mdlid =1
-------------------------------------------------------------
SQL ordered by Executions for DB: cis Instance: cis Snaps: 141 -143
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
7,788 7,788 1.0 0.00 0.00 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
2,193 0 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
2,193 2,193 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
1,299 0 0.0 0.00 0.00 1064601204
Module: w3wp.exe
alter session set isolation_level = read committed
965 965 1.0 0.00 0.00 3594945689
Module: w3wp.exe
SELECT count(id) from ulogins where id = :b1
965 965 1.0 0.00 0.00 3656262341
Module: w3wp.exe
begin sp_loginIsValid(:V00001,:V00002); end;
424 491 1.2 0.00 0.00 1254950678
select file# from file$ where ts#=:1
354 0 0.0 0.00 0.00 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
354 7,788 22.0 0.00 0.01 815501214
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft
354 0 0.0 0.00 0.00 870116171
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYSTEM.DEF$_AQERROR where tim
e_manager_info <= :1 and state != :2 for update skip locked
354 0 0.0 0.00 0.00 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
SQL ordered by Executions for DB: cis Instance: cis Snaps: 141 -143
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_
354 0 0.0 0.00 0.00 1153132087
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYSTEM.DEF$_AQCALL where time
_manager_info <= :1 and state != :2 for update skip locked
354 0 0.0 0.00 0.00 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1
354 0 0.0 0.00 0.00 3637162254
select q_name, state, delay, expiration, rowid, msgid, dequeue_
msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr
iority, exception_qschema, exception_queue, retry_count, corrid,
time_manager_info, sender_name, sender_address, sender_protoco
l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st
354 0 0.0 0.00 0.00 3920324236
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1
239 239 1.0 0.00 0.00 3716207873
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1
182 182 1.0 0.00 0.00 3468666020
select text from view$ where rowid=:1
150 0 0.0 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
136 75 0.6 0.00 0.00 2703824309
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: cis Instance: cis Snaps: 141 -143
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,930 965 8.44 3656262341
Module: w3wp.exe
begin sp_loginIsValid(:V00001,:V00002); end;
1,299 1,299 5.68 1064601204
Module: w3wp.exe
alter session set isolation_level = read committed
424 424 1.85 1254950678
select file# from file$ where ts#=:1
354 0 1.55 163922476
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS.QS_ORDERS_PR_MQTAB where msgid = :1
354 0 1.55 378803933
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_ES.QS_ES_ORDERS_MQTAB where msgid = :1
354 0 1.55 613578973
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_ES.QS_ES_ORDERS_PR_MQTAB where msgid = :1
354 354 1.55 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
354 354 1.55 815501214
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft
354 354 1.55 870116171
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYSTEM.DEF$_AQERROR where tim
e_manager_info <= :1 and state != :2 for update skip locked
354 354 1.55 994140048
SQL ordered by Parse Calls for DB: cis Instance: cis Snaps: 141 -143
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_
354 354 1.55 1153132087
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYSTEM.DEF$_AQCALL where time
_manager_info <= :1 and state != :2 for update skip locked
354 354 1.55 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1
354 0 1.55 1937207903
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_OS.QS_OS_ORDERS_PR_MQTAB where msgid = :1
354 0 1.55 2540375272
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_OS.QS_OS_ORDERS_MQTAB where msgid = :1
354 0 1.55 2833257892
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_MQTAB where msgid = :1
354 0 1.55 3137402284
select q_name, state, delay, expiration, rowid, msgid, dequeue
-------------------------------------------------------------
Instance Activity Stats for DB: cis Instance: cis Snaps: 141 -143
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 138,986 12.4 90.3
CPU used when call started 138,986 12.4 90.3
CR blocks created 735 0.1 0.5
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DBWR buffers scanned 18,165 1.6 11.8
DBWR checkpoint buffers written 1,247 0.1 0.8
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 16,257 1.5 10.6
DBWR lru scans 53 0.0 0.0
DBWR make free requests 58 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 18,165 1.6 11.8
DBWR transaction table writes 60 0.0 0.0
DBWR undo block writes 1,134 0.1 0.7
SQL*Net roundtrips to/from client 45,382 4.0 29.5
active txn count during cleanout 552 0.1 0.4
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 13,380 1.2 8.7
branch node splits 0 0.0 0.0
buffer is not pinned count 33,274,338 2,960.4 21,606.7
buffer is pinned count 369,925 32.9 240.2
bytes received via SQL*Net from c 4,749,266 422.5 3,083.9
bytes sent via SQL*Net to client 8,711,636 775.1 5,656.9
calls to get snapshot scn: kcmgss 418,195 37.2 271.6
calls to kcmgas 33,294 3.0 21.6
calls to kcmgcs 453 0.0 0.3
change write time 22 0.0 0.0
cleanout - number of ktugct calls 986 0.1 0.6
cleanouts and rollbacks - consist 19 0.0 0.0
cleanouts only - consistent read 392 0.0 0.3
cluster key scan block gets 51,256 4.6 33.3
cluster key scans 39,894 3.6 25.9
commit cleanout failures: block l 92 0.0 0.1
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 2 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 26,028 2.3 16.9
commit cleanouts successfully com 25,934 2.3 16.8
commit txn count during cleanout 604 0.1 0.4
consistent changes 18,988 1.7 12.3
consistent gets 33,841,234 3,010.8 21,974.8
consistent gets - examination 248,330 22.1 161.3
current blocks converted for CR 0 0.0 0.0
cursor authentications 1,720 0.2 1.1
data blocks consistent reads - un 18,988 1.7 12.3
db block changes 152,464 13.6 99.0
db block gets 109,507 9.7 71.1
deferred (CURRENT) block cleanout 16,091 1.4 10.5
dirty buffers inspected 200 0.0 0.1
enqueue conversions 3,705 0.3 2.4
enqueue deadlocks 0 0.0 0.0
enqueue releases 60,963 5.4 39.6
enqueue requests 60,963 5.4 39.6
enqueue timeouts 0 0.0 0.0
Instance Activity Stats for DB: cis Instance: cis Snaps: 141 -143
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
enqueue waits 0 0.0 0.0
execute count 28,585 2.5 18.6
free buffer inspected 1,051 0.1 0.7
free buffer requested 30,420,802 2,706.5 19,753.8
hot buffers moved to head of LRU 2,669 0.2 1.7
immediate (CR) block cleanout app 411 0.0 0.3
immediate (CURRENT) block cleanou 376 0.0 0.2
index fast full scans (full) 2 0.0 0.0
index fetch by key 182,667 16.3 118.6
index scans kdiixs1 102,262 9.1 66.4
leaf node 90-10 splits 19 0.0 0.0
leaf node splits 158 0.0 0.1
logons cumulative 58 0.0 0.0
messages received 13,937 1.2 9.1
messages sent 13,937 1.2 9.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 33,169,444 2,951.0 21,538.6
opened cursors cumulative 17,267 1.5 11.2
parse count (failures) 4 0.0 0.0
parse count (hard) 2,048 0.2 1.3
parse count (total) 22,878 2.0 14.9
parse time cpu 608 0.1 0.4
parse time elapsed 708 0.1 0.5
physical reads 30,851,138 2,744.8 20,033.2
physical reads direct 441,629 39.3 286.8
physical reads direct (lob) 0 0.0 0.0
physical writes 428,393 38.1 278.2
physical writes direct 425,046 37.8 276.0
physical writes non checkpoint 428,003 38.1 277.9
pinned buffers inspected 674 0.1 0.4
prefetched blocks 26,855,964 2,389.3 17,438.9
prefetched blocks aged out before 1 0.0 0.0
process last non-idle time 69,397,054,344 6,174,115.2 ############
recovery array read time 0 0.0 0.0
recovery array reads 0 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 72,976 6.5 47.4
recursive cpu usage 27,233 2.4 17.7
redo blocks written 52,072 4.6 33.8
redo buffer allocation retries 0 0.0 0.0
redo entries 77,168 6.9 50.1
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 1 0.0 0.0
redo size 23,926,592 2,128.7 15,536.8
redo synch time 6 0.0 0.0
redo synch writes 238 0.0 0.2
redo wastage 1,943,008 172.9 1,261.7
redo write time 48 0.0 0.0
redo writer latching time 3 0.0 0.0
redo writes 13,408 1.2 8.7
rollback changes - undo records a 9 0.0 0.0
rollbacks only - consistent read 2,132 0.2 1.4
rows fetched via callback 64,373 5.7 41.8
session connect time 69,397,054,344 6,174,115.2 ############
session logical reads 33,950,741 3,020.5 22,045.9
Instance Activity Stats for DB: cis Instance: cis Snaps: 141 -143
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session pga memory 1,406,784 125.2 913.5
session pga memory max 9,524,520 847.4 6,184.8
session uga memory 838,664 74.6 544.6
session uga memory max 68,307,752 6,077.2 44,355.7
shared hash latch upgrades - no w 103,097 9.2 67.0
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 240 0.0 0.2
sorts (memory) 31,912 2.8 20.7
sorts (rows) 47,136,330 4,193.6 30,608.0
summed dirty queue length 8 0.0 0.0
switch current to new buffer 8,734 0.8 5.7
table fetch by rowid 157,886 14.1 102.5
table fetch continued row 5 0.0 0.0
table scan blocks gotten 33,016,263 2,937.4 21,439.1
table scan rows gotten 2,725,313,677 242,465.6 1,769,684.2
table scans (direct read) 0 0.0 0.0
table scans (long tables) 63,323 5.6 41.1
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 38,654 3.4 25.1
transaction rollbacks 3 0.0 0.0
user calls 44,473 4.0 28.9
user commits 230 0.0 0.2
user rollbacks 1,310 0.1 0.9
workarea executions - multipass 154 0.0 0.1
workarea executions - onepass 318 0.0 0.2
workarea executions - optimal 40,190 3.6 26.1
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: cis Instance: cis Snaps: 141 -143
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
IRS2007_YBL_USER
3,540,495 315 0.6 8.6 1,016 0 460 0.3
TEMP
82,618 7 1.0 5.3 68,022 6 0 0.0
SYSTEM
7,713 1 1.8 4.2 52 0 0 0.0
PERFSTAT
1,527 0 5.4 1.1 1,083 0 0 0.0
XDB
1,482 0 5.0 1.0 0 0 0 0.0
UNDOTBS1
4 0 0.0 1.0 1,196 0 0 0.0
IRS2007_YBL_R_USER
838 0 1.5 1.0 0 0 0 0.0
EXAMPLE
750 0 2.9 1.0 0 0 0 0.0
DRSYS
310 0 2.8 1.0 0 0 0 0.0
CWMLITE
300 0 2.0 1.0 0 0 0 0.0
TOOLS
194 0 3.2 1.0 0 0 0 0.0
ODM
190 0 2.5 1.0 0 0 0 0.0
cis_YBL_USER
136 0 3.3 1.0 0 0 0 0.0
INTCDR_YBL_USER
20 0 4.5 1.0 0 0 0 0.0
cis_YBL_TEMP
2 0 0.0 1.0 0 0 0 0.0
INDX
2 0 5.0 1.0 0 0 0 0.0
INTCDR_YBL_TEMP
2 0 10.0 1.0 0 0 0 0.0
IRS2007_YBL_R_TEMP
2 0 5.0 1.0 0 0 0 0.0
IRS2007_YBL_TEMP
2 0 5.0 1.0 0 0 0 0.0
USERS
2 0 5.0 1.0 0 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: cis Instance: cis Snaps: 141 -143
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
CWMLITE C:\ORACLE\ORADATA\cis\CWMLITE01.DBF
300 0 2.0 1.0 0 0 0
DRSYS C:\ORACLE\ORADATA\cis\DRSYS01.DBF
310 0 2.8 1.0 0 0 0
EXAMPLE C:\ORACLE\ORADATA\cis\EXAMPLE01.DBF
750 0 2.9 1.0 0 0 0
cis_YBL_TEMP C:\ORACLE\ORADATA\TABLESPACE\cis_YBL_TEMP.ORA
2 0 0.0 1.0 0 0 0
cis_YBL_USER C:\ORACLE\ORADATA\TABLESPACE\cis_YBL_USER.ORA
136 0 3.3 1.0 0 0 0
INDX C:\ORACLE\ORADATA\cis\INDX01.DBF
2 0 5.0 1.0 0 0 0
INTCDR_YBL_TEMP C:\ORACLE\ORADATA\TABLESPACE\INTCDR_YBL_TEMP.ORA
2 0 10.0 1.0 0 0 0
INTCDR_YBL_USER C:\ORACLE\ORADATA\TABLESPACE\INTCDR_YBL_USER.ORA
18 0 3.9 1.0 0 0 0
D:\ORACLE\DATA\INTCDR_YBL_USER1.DBF
2 0 10.0 1.0 0 0 0
IRS2007_YBL_R_TEMP C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_R_TEMP.ORA
2 0 5.0 1.0 0 0 0
IRS2007_YBL_R_USER C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_R_USER.ORA
838 0 1.5 1.0 0 0 0
IRS2007_YBL_TEMP C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_TEMP.ORA
2 0 5.0 1.0 0 0 0
IRS2007_YBL_USER C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_USER.ORA
1,724,891 153 0.5 7.0 370 0 199 0.4
D:\ORACLE\DATA\IRS2007_YBL_USER1.DBF
1,815,604 162 0.7 10.1 646 0 261 0.3
ODM C:\ORACLE\ORADATA\cis\ODM01.DBF
190 0 2.5 1.0 0 0 0
PERFSTAT C:\ORACLE\ORADATA\TABLESPACE\PERFSTAT01.DBF
1,527 0 5.4 1.1 1,083 0 0
SYSTEM C:\ORACLE\ORADATA\cis\SYSTEM01.DBF
7,675 1 1.8 4.2 52 0 0
C:\ORACLE\ORADATA\cis\SYSTEM02.DBF
38 0 3.2 2.2 0 0 0
TEMP C:\ORACLE\ORADATA\cis\TEMP01.DBF
File IO Stats for DB: cis Instance: cis Snaps: 141 -143
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
82,618 7 1.0 5.3 68,022 6 0
TOOLS C:\ORACLE\ORADATA\cis\TOOLS01.DBF
194 0 3.2 1.0 0 0 0
UNDOTBS1 C:\ORACLE\ORADATA\cis\UNDOTBS01.DBF
4 0 0.0 1.0 1,196 0 0
USERS C:\ORACLE\ORADATA\cis\USERS01.DBF
2 0 5.0 1.0 0 0 0
XDB C:\ORACLE\ORADATA\cis\XDB01.DBF
1,482 0 5.0 1.0 0 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: cis Instance: cis Snaps: 141 -143
-> 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 3,003 10.4 33,949,304 30,414,220 3,347 0 0 460
-------------------------------------------------------------
Instance Recovery Stats for DB: cis Instance: cis Snaps: 141 -143
-> 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 14395 13436 184320 13436
E 0 0 10714 10215 184320 10215
-------------------------------------------------------------
Buffer Pool Advisory for DB: cis Instance: cis End Snap: 143
-> 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
--- ------------ ----- ---------------- ------------- ------------------
D 8 .3 1,001 1.33 5,030,344,285
D 16 .7 2,002 1.09 4,111,401,303
D 24 1.0 3,003 1.00 3,785,412,620
D 32 1.3 4,004 0.88 3,325,874,690
D 40 1.7 5,005 0.73 2,778,756,798
D 48 2.0 6,006 0.59 2,224,515,464
D 56 2.3 7,007 0.41 1,540,741,333
D 64 2.7 8,008 0.26 995,586,778
D 72 3.0 9,009 0.19 709,557,460
D 80 3.3 10,010 0.16 591,536,046
D 88 3.7 11,011 0.12 448,525,720
D 96 4.0 12,012 0.11 399,329,837
D 104 4.3 13,013 0.10 364,511,029
D 112 4.7 14,014 0.08 312,321,922
D 120 5.0 15,015 0.07 260,147,348
D 128 5.3 16,016 0.06 211,367,285
D 136 5.7 17,017 0.05 175,634,866
D 144 6.0 18,018 0.04 142,382,812
D 152 6.3 19,019 0.03 104,428,938
D 160 6.7 20,020 0.02 61,150,579
-------------------------------------------------------------
Buffer wait Statistics for DB: cis Instance: cis Snaps: 141 -143
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 460 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: cis Instance: cis Snaps: 141 -143
-> B: Begin snap E: End snap (rows dentified 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
--------------- ---------------- -------------------------
31.1 5,001 11,098
%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 24 7 23.1 0.0 .0 100.0 .0 1,228
E 24 7 24.6 0.0 .0 .0 .0 1,228
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: cis Instance: cis Snaps: 141 -143
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 26,389 26,389 0 0
16K 32K 4,932 4,932 0 0
32K 64K 5,148 5,148 0 0
64K 128K 1,960 1,960 0 0
128K 256K 732 732 0 0
256K 512K 411 411 0 0
512K 1024K 479 479 0 0
1M 2M 121 59 62 0
2M 4M 188 0 188 0
4M 8M 74 0 68 6
8M 16M 112 0 0 112
16M 32M 18 0 0 18
32M 64M 18 0 0 18
-------------------------------------------------------------
PGA Memory Advisory for DB: cis Instance: cis End Snap: 143
-> 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
---------- ------- ---------------- ---------------- -------- ----------
12 0.5 1,482,904.6 1,221,885.5 55.0 37,135
18 0.8 1,482,904.6 1,221,885.5 55.0 37,134
24 1.0 1,482,904.6 1,146,032.4 56.0 334
29 1.2 1,482,904.6 1,045,266.0 59.0 15
34 1.4 1,482,904.6 979,328.1 60.0 14
38 1.6 1,482,904.6 918,905.4 62.0 14
43 1.8 1,482,904.6 844,923.3 64.0 7
48 2.0 1,482,904.6 750,170.4 66.0 7
72 3.0 1,482,904.6 294,527.4 83.0 0
96 4.0 1,482,904.6 285,455.9 84.0 0
144 6.0 1,482,904.6 274,908.8 84.0 0
192 8.0 1,482,904.6 269,613.6 85.0 0
-------------------------------------------------------------
Rollback Segment Stats for DB: cis Instance: cis Snaps: 141 -143
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- -------- -------- --------
0 40.0 0.00 0 0 0 0
1 4,706.0 0.00 428,516 0 0 0
2 3,988.0 0.00 1,338,990 4 0 1
3 4,320.0 0.00 652,016 1 0 0
4 4,095.0 0.00 373,810 3 0 1
5 3,912.0 0.00 664,334 1 0 0
6 4,432.0 0.00 306,288 0 0 0
7 3,804.0 0.00 480,016 0 0 0
8 4,270.0 0.00 311,800 2 0 1
9 4,070.0 0.00 303,198 0 0 0
10 4,296.0 0.00 2,189,274 5 0 2
-------------------------------------------------------------
Rollback Segment Storage for DB: cis Instance: cis Snaps: 141 -143
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 6,553 385,024
1 2,220,032 403,125 3,268,608
2 2,220,032 488,447 4,317,184
3 2,220,032 496,665 3,268,608
4 2,220,032 399,239 4,317,184
5 2,220,032 496,112 4,317,184
6 2,220,032 409,036 4,317,184
7 2,220,032 362,586 3,268,608
8 2,220,032 415,089 3,268,608
9 2,220,032 421,983 4,317,184
10 4,317,184 1,314,611 4,317,184
-------------------------------------------------------------
Undo Segment Summary for DB: cis Instance: cis Snaps: 141 -143
-> Undo segment block stats:
-> uS - unexpired Stolen, your - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 979 ########## 105 1 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: cis Instance: cis Snaps: 141 -143
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
31-Jul 15:08 19 ######## 19 1 0 0 0/0/0/0/0/0
31-Jul 14:58 17 ######## 47 1 0 0 0/0/0/0/0/0
31-Jul 14:48 24 ######## 49 1 0 0 0/0/0/0/0/0
31-Jul 14:38 18 ######## 43 1 0 0 0/0/0/0/0/0
31-Jul 14:28 17 ######## 3 1 0 0 0/0/0/0/0/0
31-Jul 14:18 17 ######## 3 1 0 0 0/0/0/0/0/0
31-Jul 14:08 18 ######## 3 1 0 0 0/0/0/0/0/0
31-Jul 13:58 16 ######## 3 1 0 0 0/0/0/0/0/0
31-Jul 13:48 18 ######## 4 1 0 0 0/0/0/0/0/0
31-Jul 13:38 19 ######## 49 1 0 0 0/0/0/0/0/0
31-Jul 13:28 33 ######## 60 1 0 0 0/0/0/0/0/0
31-Jul 13:18 36 ######## 74 1 0 0 0/0/0/0/0/0
31-Jul 13:08 34 ######## 64 1 0 0 0/0/0/0/0/0
31-Jul 12:58 20 ######## 6 1 0 0 0/0/0/0/0/0
31-Jul 12:48 18 ######## 49 1 0 0 0/0/0/0/0/0
31-Jul 12:38 34 ######## 49 1 0 0 0/0/0/0/0/0
31-Jul 12:28 48 ######## 58 1 0 0 0/0/0/0/0/0
31-Jul 12:18 438 ######## 65 1 0 0 0/0/0/0/0/0
31-Jul 12:08 135 ######## 105 1 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: cis Instance: cis Snaps: 141 -143
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 13,409 0.0 0 0
FIB s.o chain latch 2 0.0 0 0
FOB s.o list latch 296 0.0 0 0
SQL memory manager latch 2 0.0 0 3,651 0.0
SQL memory manager worka 248,881 0.0 0 0
active checkpoint queue 4,359 0.0 0 0
archive control 371 0.0 0 0
archive process latch 219 0.0 0 0
cache buffer handles 1,504 0.0 0 0
cache buffers chains 98,632,793 0.0 0.0 0 58,719,289 0.0
cache buffers lru chain 14,637 0.4 0.0 0 63,016,916 0.1
channel handle pool latc 110 0.0 0 0
channel operations paren 7,507 0.0 0 0
checkpoint queue latch 411,236 0.0 0 3,346 0.0
child cursor hash table 16,999 0.0 0 0
dml lock allocation 70,204 0.0 0 0
dummy allocation 110 0.0 0 0
enqueue hash chains 125,629 0.0 0 0
enqueues 24,000 0.0 0 0
event group latch 58 0.0 0 0
hash table column usage 42 0.0 0 1,488 0.0
job_queue_processes para 183 0.0 0 0
kmcptab latch 1 0.0 0 0
kmcpvec latch 0 0 1 0.0
ktm global data 37 0.0 0 0
kwqit: protect wakeup ti 354 0.0 0 0
lgwr LWN SCN 16,290 0.0 0 0
library cache 563,175 0.0 0.0 0 7,907 0.3
library cache load lock 896 0.0 0 0
library cache pin 231,505 0.0 0 0
library cache pin alloca 147,689 0.0 0 0
list of block allocation 221 0.0 0 0
loader state object free 1,416 0.0 0 0
longop free list parent 8 0.0 0 8 0.0
messages 90,400 0.1 0.0 0 0
mostly latch-free SCN 16,343 0.1 0.0 0 0
multiblock read objects 6,832,318 0.0 0.0 0 0
ncodef allocation latch 180 0.0 0 0
object stats modificatio 3,409 0.0 0 0
post/wait queue 336 0.0 0 232 0.0
process allocation 58 0.0 0 58 0.0
process group creation 110 0.0 0 0
redo allocation 106,867 0.1 0.0 0 0
redo copy 0 0 77,396 0.3
redo writing 51,890 0.0 0 0
row cache enqueue latch 63,940 0.0 0 0
row cache objects 68,997 0.0 0 2,096 0.0
sequence cache 35,854 0.0 0 0
session allocation 44,086 0.0 0 0
session idle bit 94,703 0.0 0 0
Latch Activity for DB: cis Instance: cis Snaps: 141 -143
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
session switching 180 0.0 0 0
session timer 3,786 0.0 0 0
shared pool 285,945 0.0 0.3 0 0
simulator hash latch 4,103,915 0.0 0 0
simulator lru latch 1,983,566 0.0 0.0 0 9,730 0.1
sort extent pool 3,770 0.0 0 0
spilled msgs queues list 354 0.0 0 0
transaction allocation 149 0.0 0 0
transaction branch alloc 180 0.0 0 0
undo global data 69,325 0.0 0 0
user lock 216 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: cis Instance: cis Snaps: 141 -143
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
multiblock read objects 6,832,318 1,758 1 1757/1/0/0/0
cache buffers chains 98,632,793 918 19 0/0/0/0/0
redo allocation 106,867 71 1 70/1/0/0/0
library cache 563,175 34 1 33/1/0/0/0
shared pool 285,945 18 6 12/6/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: cis Instance: cis Snaps: 141 -143
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbrls: kslbegin 0 7 3
cache buffers chains kcbgtcr: kslbegin excl 0 6 6
cache buffers chains kcbzib: multi-block read: 0 4 0
cache buffers chains kcbzgb: scan from tail. no 0 1 0
cache buffers chains kcbzib: finish free bufs 0 1 5
multiblock read objects kcbzib: normal mbr free 0 1 1
redo allocation kcrfwr 0 1 0
shared pool kghfrunp: alloc: wait 0 5 0
shared pool kghalo 0 1 0
shared pool kghfrunp: clatch: wait 0 1 0
-------------------------------------------------------------
Top 5 Logical Reads per Segment for DB: cis Instance: cis Snaps: 141 -143
-> End Segment Logical Reads Threshold: 10000
Subobject Obj. Logical
Owner Tablespace Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
IRS2007_YB IRS2007_YB SUBJECTIVE_PRM_TRANS TABLE 24,481,184 72.17
IRS2007_YB IRS2007_YB TRN_PROP_FACILITY TABLE 3,475,856 10.25
IRS2007_YB IRS2007_YB TRN_SANC_FACILITY TABLE 1,885,696 5.56
IRS2007_YB IRS2007_YB COABALANCES TABLE 1,207,200 3.56
IRS2007_YB IRS2007_YB RAT_CALC_RISKSCORE TABLE 640,528 1.89
-------------------------------------------------------------
Top 5 Physical Reads per Segment for DB: cis Instance: cis Snaps: 141 -143
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical
Owner Tablespace Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
IRS2007_YB IRS2007_YB SUBJECTIVE_PRM_TRANS TABLE 23,951,135 78.89
IRS2007_YB IRS2007_YB TRN_PROP_FACILITY TABLE 1,847,436 6.08
IRS2007_YB IRS2007_YB TRN_SANC_FACILITY TABLE 1,541,800 5.08
IRS2007_YB IRS2007_YB COABALANCES TABLE 1,190,053 3.92
IRS2007_YB IRS2007_YB RAT_CALC_RISKSCORE TABLE 622,136 2.05
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: cis Instance: cis Snaps: 141 -14
-> End Segment Buffer Busy Waits Threshold: 100
Buffer
Subobject Obj. Busy
Owner Tablespace Object Name Name Type Waits %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
IRS2007_YB IRS2007_YB SUBJECTIVE_PRM_TRANS TABLE 458 99.57
IRS2007_YB IRS2007_YB COARATIOS TABLE 1 .22
IRS2007_YB IRS2007_YB STATEMENT TABLE 1 .22
-------------------------------------------------------------
Dictionary Cache Stats for DB: cis Instance: cis Snaps: 141 -143
->"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_files 22 0.0 0 0 22
dc_histogram_defs 684 21.9 0 0 276
dc_object_ids 1,768 0.8 0 0 674
dc_objects 8,478 2.5 0 0 2,525
dc_profiles 57 0.0 0 0 2
dc_rollback_segments 840 0.0 0 0 22
dc_segments 12,157 0.1 0 0 1,552
dc_sequences 239 1.7 0 239 38
dc_tablespace_quotas 1 0.0 0 1 1
dc_tablespaces 2,519 0.0 0 0 22
dc_user_grants 3,484 0.0 0 0 22
dc_usernames 795 0.1 0 0 38
dc_users 4,807 0.0 0 0 50
-------------------------------------------------------------
Library Cache Activity for DB: cis Instance: cis Snaps: 141 -143
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 43 0.0 43 0.0 0 0
CLUSTER 241 0.0 130 0.0 0 0
INDEX 2,478 0.0 2,478 0.0 0 0
SQL AREA 21,051 8.8 75,088 5.5 183 2
TABLE/PROCEDURE 22,980 1.1 43,000 1.4 146 0
TRIGGER 224 0.0 224 0.0 0 0
-------------------------------------------------------------
Shared Pool Advisory for DB: cis Instance: cis End Snap: 143
-> 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
----------- ----- ---------- ------------ ------------ ------- ---------------
88 .6 84 12,568 1,873,053 1.0 142,916,788
104 .7 99 14,863 1,873,904 1.0 143,162,723
120 .8 124 19,129 1,874,436 1.0 143,364,105
136 .9 139 23,439 1,875,000 1.0 143,641,721
152 1.0 154 27,411 1,875,719 1.0 144,001,698
168 1.1 169 29,826 1,876,309 1.0 144,297,774
184 1.2 184 32,090 1,876,764 1.0 144,549,145
200 1.3 199 34,104 1,877,083 1.0 144,720,515
216 1.4 214 36,322 1,877,255 1.0 144,818,301
232 1.5 229 37,879 1,877,387 1.0 144,882,268
248 1.6 244 40,237 1,877,416 1.0 144,901,654
264 1.7 259 42,547 1,877,440 1.0 144,919,319
280 1.8 274 46,396 1,877,463 1.0 144,933,934
296 1.9 289 48,599 1,877,477 1.0 144,944,634
312 2.1 327 53,355 1,877,497 1.0 144,952,808
-------------------------------------------------------------
SGA Memory Summary for DB: cis Instance: cis Snaps: 141 -143
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 25,165,824
Fixed Size 455,472
Redo Buffers 667,648
Variable Size 662,700,032
----------------
sum 688,988,976
-------------------------------------------------------------
SGA breakdown difference for DB: cis Instance: cis Snaps: 141 -143
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 155,848,704 155,848,704 0.00
java memory in use 3,534,848 3,534,848 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 564,608 564,608 0.00
shared FileOpenBlock 695,504 695,504 0.00
shared KGK heap 3,756 3,756 0.00
shared KGLS heap 17,513,296 3,963,748 -77.37
shared KQR M PO 2,633,304 2,654,808 0.82
shared KQR S PO 654,916 654,916 0.00
shared KQR S SO 9,740 9,740 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 1,217,812 1,043,520 -14.31
shared PL/SQL MPCODE 2,048,336 2,042,224 -0.30
shared PLS non-lib hp 2,068 2,068 0.00
shared character set object 374,860 374,860 0.00
shared dictionary cache 1,614,976 1,614,976 0.00
shared errors 49,216 49,216 0.00
shared event statistics per sess 1,899,240 1,899,240 0.00
shared fixed allocation callback 284 284 0.00
shared free memory 17,479,904 12,494,564 -28.52
shared joxlod: in ehe 193,380 193,380 0.00
shared joxs heap init 4,220 4,220 0.00
shared library cache 32,626,100 34,080,588 4.46
shared message pool freequeue 834,752 834,752 0.00
shared miscellaneous 14,476,544 14,648,852 1.19
shared parameters 123,108 104,552 -15.07
shared sessions 410,720 410,720 0.00
shared sim memory hea 42,344 42,344 0.00
shared sql area 65,943,184 93,793,648 42.23
shared table definiti 3,336 4,812 44.24
shared trigger defini 748 748 0.00
shared trigger inform 1,528 1,528 0.00
shared trigger source 380 380 0.00
buffer_cache 25,165,824 25,165,824 0.00
fixed_sga 455,472 455,472 0.00
log_buffer 656,384 656,384 0.00
-------------------------------------------------------------
init.ora Parameters for DB: cis Instance: cis Snaps: 141 -143
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
audit_trail TRUE
background_dump_dest C:\oracle\admin\cis\bdump
compatible 9.2.0.0.0
control_files C:\oracle\oradata\cis\CONTROL01
core_dump_dest C:\oracle\admin\cis\cdump
db_block_size 8192
db_cache_size 25165824
db_domain
db_file_multiblock_read_count 16
db_name cis
dispatchers (PROTOCOL=TCP) (SERVICE=cisXDB)
fast_start_mttr_target 0
hash_join_enabled TRUE
instance_name cis
java_pool_size 159383552
job_queue_processes 10
large_pool_size 8388608
log_archive_dest D:\oracle\oradata\cis\Archive\
log_archive_format cis_%t_%s.arc
log_archive_start TRUE
open_cursors 300
pga_aggregate_target 25165824
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
sga_max_size 688988976
shared_pool_size 159383552
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest C:\oracle\admin\cis\udump
-------------------------------------------------------------
End of Report
Regards
|
|
|
|
|
|
|
|
| Re: statspack report [message #337749 is a reply to message #337657] |
Fri, 01 August 2008 00:45  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 2,587,518 0 2,022 1 1,680.2
Need to tune this query
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5832 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5832 and user_id=22 and mdlid =4 an
I/O tune  all the blocks are scattered in database buffer
Set proper value for DB_FILE_MULTIBLOCK_READ_COUNT
|
|
|
|
|
|
| Re: statspack report [message #337767 is a reply to message #337751] |
Fri, 01 August 2008 01:47 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Well according to me Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter for db_FILE_MULTIBLOCK_READ_COUNT.
As far as tuning of the query is concerned we will apply indexes on these queries so that would avoid the full table scans.
Also I would suggests the value for db_file_multiblock_read_count as 12
Since our application is not at all an OLTP NOR its batch environments.If you think its correct I would suggest this value
also I would recommend cursor_sharing parameter to set as force.
This is what my observation is all about
Please suggest I should do?
Regards
|
|
|
|
|
|
| Re: statspack report [message #338050 is a reply to message #337781] |
Sat, 02 August 2008 15:12 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
What do u mean by db file db file scattered read ?
It is a wait event which occurs when Oracle performs multiblock reads from disk into non-contiguous ('scattered') buffers in the Buffer Cache.
Seeing your statspack report.I came to know about this wait which is degrading your performance
Secondly most of the query doing select as per the statspack report.
Multiblock reads are issued for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time.
I/O is a function of the operating system and so there are operating system specific limits imposed on the setting of this parameter.
It is possible to tune the size of multiblock I/Os issued by Oracle by setting DB_FILE_MULTIBLOCK_READ_COUNT as
DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
In 10g DB_FILE_MULTIBLOCK_READ_COUNT is automatically tuned to use a default value when this parameter is not set explicitly.
If the value of db_file_multiblock_read_count is set too high then the server will consume additional memory
For OLTP oracle says having high value of DB_FILE_MULTIBLOCK_READ_COUNT most of time degrade the performance.
Note:-Db_file_multiblock_read_count cannot exceed:
(db_block_buffers/4)
Default value corresponds to the maximum I/O size that can be performed efficiently is 1MB for most platforms.
db_file_multiblock_read_count decide how many I/O calls will
be required to complete a table scan.
Manoj as your are suggesting db_file_multiblock_read_count value as 12 then
8X12= 96k I don't think it will improve performance.
Note :- As per statspack Block size is 8
Can you try after setting db_file_multiblock_read_count value as 64 ?
8 X 64 = 512K. 
[Updated on: Sat, 02 August 2008 15:14] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 22:33:22 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
