Home » RDBMS Server » Performance Tuning » How to tuning our oracle product server?
| How to tuning our oracle product server? [message #222888] |
Tue, 06 March 2007 20:18  |
Tom_webex
Messages: 13
Registered: July 2006
Location: China
|
Junior Member |
|
|
We have a product server,it can work normal daily(speed can ok),but when we deal with large mount of data(insert,update,delete),the deal speed is very slow,I have the statspack report,pls tell me how to tuning?
THANKS!
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ACC 1484458362 acc1 1 9.2.0.7.0 YES ACCOUNT1
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 662 06-Mar-07 12:40:21 434 #########
End Snap: 663 06-Mar-07 13:28:56 432 #########
Elapsed: 48.58 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 11,456M Std Block Size: 8K
Shared Pool Size: 3,920M Log Buffer: 8,000K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 119,665.82 1,784.27
Logical reads: 77,481.13 1,155.28
Block changes: 876.34 13.07
Physical reads: 783.24 11.68
Physical writes: 163.28 2.43
User calls: 2,474.29 36.89
Parses: 451.53 6.73
Hard parses: 28.30 0.42
Sorts: 385.74 5.75
Logons: 14.09 0.21
Executes: 1,999.53 29.81
Transactions: 67.07
% Blocks changed per Read: 1.13 Recursive Call %: 18.08
Rollback per transaction %: 3.61 Rows per Sort: 20.97
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.06 In-memory Sort %: 100.00
Library Hit %: 99.17 Soft Parse %: 93.73
Execute to Parse %: 77.42 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 13.64 % Non-Parse CPU: 96.34
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.34 93.65
% SQL with executions>1: 21.40 21.34
% Memory for SQL w/exec>1: 24.44 24.28
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 2,600 30.97
db file sequential read 336,849 2,163 25.76
SQL*Net message from dblink 1,499,147 1,476 17.59
log file sync 376,731 1,159 13.80
db file scattered read 125,330 228 2.72
-------------------------------------------------------------
Cluster Statistics for DB: ACC Instance: acc1 Snaps: 662 -663
Global Cache Service - Workload Characteristics
-----------------------------------------------
Ave global cache get time (ms): 0.2
Ave global cache convert time (ms): 2.2
Ave build time for CR block (ms): 0.0
Ave flush time for CR block (ms): 0.2
Ave send time for CR block (ms): 0.0
Ave time to process CR block request (ms): 0.2
Ave receive time for CR block (ms): 0.5
Ave pin time for current block (ms): 0.3
Ave flush time for current block (ms): 0.0
Ave send time for current block (ms): 0.0
Ave time to process current block request (ms): 0.3
Ave receive time for current block (ms): 1.6
Global cache hit ratio: 1.1
Ratio of current block defers: 0.0
% of messages sent for buffer gets: 1.1
% of remote buffer gets: 0.1
Ratio of I/O for coherence: 1.0
Ratio of local vs remote work: 13.6
Ratio of fusion vs physical writes: 0.0
Global Enqueue Service Statistics
---------------------------------
Ave global lock get time (ms): 0.0
Ave global lock convert time (ms): 0.0
Ratio of global lock gets vs global lock releases: 1.0
GCS and GES Messaging statistics
--------------------------------
Ave message sent queue time (ms): 0.1
Ave message sent queue time on ksxp (ms): 0.7
Ave message received queue time (ms): 0.0
Ave GCS message process time (ms): 0.0
Ave GES message process time (ms): 0.0
% of direct sent messages: 24.8
% of indirect sent messages: 41.6
% of flow controlled messages: 33.6
-------------------------------------------------------------
GES Statistics for DB: ACC Instance: acc1 Snaps: 662 -663
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
dynamically allocated gcs resourc 0 0.0 0.0
dynamically allocated gcs shadows 0 0.0 0.0
flow control messages received 104 0.0 0.0
flow control messages sent 7 0.0 0.0
gcs ast xid 1 0.0 0.0
gcs blocked converts 35,370 12.1 0.2
gcs blocked cr converts 85,798 29.4 0.4
gcs compatible basts 8 0.0 0.0
gcs compatible cr basts (global) 5,964 2.0 0.0
gcs compatible cr basts (local) 248,634 85.3 1.3
gcs cr basts to PIs 0 0.0 0.0
gcs cr serve without current lock 0 0.0 0.0
gcs error msgs 0 0.0 0.0
gcs flush pi msgs 7,058 2.4 0.0
gcs forward cr to pinged instance 0 0.0 0.0
gcs immediate (compatible) conver 11,807 4.1 0.1
gcs immediate (null) converts 12,763 4.4 0.1
gcs immediate cr (compatible) con 46,903 16.1 0.2
gcs immediate cr (null) converts 1,781,323 611.1 9.1
gcs msgs process time(ms) 34,048 11.7 0.2
gcs msgs received 2,203,588 755.9 11.3
gcs out-of-order msgs 0 0.0 0.0
gcs pings refused 231 0.1 0.0
gcs queued converts 3 0.0 0.0
gcs recovery claim msgs 0 0.0 0.0
gcs refuse xid 0 0.0 0.0
gcs retry convert request 0 0.0 0.0
gcs side channel msgs actual 12,287 4.2 0.1
gcs side channel msgs logical 1,228,800 421.5 6.3
gcs write notification msgs 583 0.2 0.0
gcs write request msgs 8,769 3.0 0.0
gcs writes refused 44 0.0 0.0
ges msgs process time(ms) 2,851 1.0 0.0
ges msgs received 359,205 123.2 1.8
global posts dropped 0 0.0 0.0
global posts queue time 0 0.0 0.0
global posts queued 0 0.0 0.0
global posts requested 0 0.0 0.0
global posts sent 0 0.0 0.0
implicit batch messages received 195,793 67.2 1.0
implicit batch messages sent 184,619 63.3 0.9
lmd msg send time(ms) 1,680 0.6 0.0
lms(s) msg send time(ms) 5 0.0 0.0
messages flow controlled 918,454 315.1 4.7
messages received actual 1,663,338 570.6 8.5
messages received logical 2,560,938 878.5 13.1
messages sent directly 678,307 232.7 3.5
messages sent indirectly 1,135,959 389.7 5.8
msgs causing lmd to send msgs 190,760 65.4 1.0
msgs causing lms(s) to send msgs 29,610 10.2 0.2
msgs received queue time (ms) 50,236 17.2 0.3
msgs received queued 2,558,847 877.8 13.1
msgs sent queue time (ms) 75,569 25.9 0.4
msgs sent queue time on ksxp (ms) 924,601 317.2 4.7
msgs sent queued 1,135,832 389.7 5.8
msgs sent queued on ksxp 1,379,927 473.4 7.1
GES Statistics for DB: ACC Instance: acc1 Snaps: 662 -663
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
process batch messages received 133,160 45.7 0.7
process batch messages sent 345,964 118.7 1.8
-------------------------------------------------------------
Wait Events for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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 sequential read 336,849 0 2,163 6 1.7
SQL*Net message from dblink 1,499,147 0 1,476 1 7.7
log file sync 376,731 0 1,159 3 1.9
db file scattered read 125,330 0 228 2 0.6
single-task message 5,045 0 155 31 0.0
global cache cr request 443,341 30,426 143 0 2.3
db file parallel write 7,115 0 125 18 0.0
global cache null to x 19,182 963 101 5 0.1
direct path write 15,027 0 49 3 0.1
SQL*Net break/reset to clien 161,596 0 23 0 0.8
enqueue 157,268 4 20 0 0.8
log file parallel write 361,026 0 20 0 1.8
log file sequential read 517 0 19 37 0.0
db file parallel read 4,226 0 13 3 0.0
direct path read 7,790 0 11 1 0.0
async disk IO 1,015 0 11 10 0.0
global cache open x 9,897 571 10 1 0.1
buffer busy global cache 186 3 10 56 0.0
library cache lock 66,110 0 8 0 0.3
DFS lock handle 37,015 0 7 0 0.2
global cache s to x 28,147 825 7 0 0.1
buffer busy global CR 1,005 0 6 6 0.0
control file parallel write 1,052 0 6 5 0.0
SQL*Net more data to client 531,804 0 6 0 2.7
buffer busy waits 517 0 4 7 0.0
PX Deq: Execute Reply 182 10 3 14 0.0
control file sequential read 4,974 0 2 0 0.0
row cache lock 4,115 0 2 0 0.0
SQL*Net more data from dblin 45,591 0 2 0 0.2
name-service call wait 40 0 1 37 0.0
latch free 1,258 463 1 1 0.0
process startup 10 0 1 96 0.0
LGWR wait for redo copy 1,519 20 1 1 0.0
SQL*Net message to dblink 1,499,147 0 1 0 7.7
CGS wait for IPC msg 29,688 28,897 0 0 0.2
log file switch completion 6 0 0 50 0.0
global cache null to s 735 48 0 0 0.0
global cache open s 771 81 0 0 0.0
PX Deq Credit: send blkd 60 9 0 2 0.0
ksxr poll remote instances 12,366 7,147 0 0 0.1
PX Deq: reap credit 4,619 4,105 0 0 0.0
global cache busy 8 0 0 12 0.0
library cache pin 358 0 0 0 0.0
PX Deq Credit: need buffer 408 62 0 0 0.0
PX Deq: Parse Reply 14 1 0 2 0.0
PX Deq: Join ACK 11 5 0 1 0.0
KJC: Wait for msg sends to c 76 0 0 0 0.0
log file single write 4 0 0 2 0.0
PX Deq: Msg Fragment 12 0 0 0 0.0
Wait Events for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
global cache quiesce wait 1 0 0 3 0.0
PX Deq: Signal ACK 8 4 0 0 0.0
IPC send completion sync 4 3 0 0 0.0
SQL*Net more data to dblink 3 0 0 0 0.0
undo segment extension 82 82 0 0 0.0
buffer deadlock 15 15 0 0 0.0
lock deadlock retry 1 1 0 0 0.0
cr request retry 1 1 0 0 0.0
lock escalate retry 1 1 0 0 0.0
SQL*Net message from client 7,069,530 0 800,947 113 36.2
gcs remote message 1,329,412 51,717 17,011 13 6.8
PX Idle Wait 2,097 2,088 5,096 2430 0.0
ges remote message 373,155 57,028 2,839 8 1.9
SQL*Net message to client 7,069,585 0 4 0 36.2
PX Deq: Execution Msg 210 26 3 13 0.0
PX Deq: Table Q Normal 34 0 0 11 0.0
SQL*Net more data from clien 730 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: ACC Instance: acc1 Snaps: 662 -663
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 7,115 0 125 18 0.0
log file parallel write 360,986 0 20 0 1.8
log file sequential read 517 0 19 37 0.0
enqueue 156,836 2 18 0 0.8
async disk IO 1,015 0 11 10 0.0
db file scattered read 1,100 0 7 6 0.0
control file parallel write 1,052 0 6 5 0.0
DFS lock handle 22,980 0 3 0 0.1
control file sequential read 4,758 0 2 0 0.0
db file sequential read 268 0 1 4 0.0
LGWR wait for redo copy 1,519 20 1 1 0.0
db file parallel read 43 0 1 13 0.0
CGS wait for IPC msg 29,687 28,896 0 0 0.2
direct path read 1,167 0 0 0 0.0
global cache cr request 1,299 0 0 0 0.0
ksxr poll remote instances 12,342 7,137 0 0 0.1
direct path write 279 0 0 0 0.0
global cache open x 266 0 0 0 0.0
latch free 179 8 0 0 0.0
global cache null to x 107 0 0 0 0.0
buffer busy waits 8 0 0 1 0.0
log file single write 4 0 0 2 0.0
library cache lock 10 0 0 0 0.0
global cache open s 2 0 0 0 0.0
row cache lock 2 0 0 0 0.0
global cache s to x 2 0 0 0 0.0
rdbms ipc message 390,151 34,064 26,813 69 2.0
gcs remote message 1,329,415 51,712 17,010 13 6.8
pmon timer 1,179 1,179 2,846 2414 0.0
ges remote message 373,018 57,030 2,839 8 1.9
smon timer 217 0 2,822 13003 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
16,141,356 128,087 126.0 7.1 93.58 461.67 4256219959
Module: RkfReckonQry@CRM_MIRROR (TNS V1-V3)
select to_number(item_code) into :b0 from sfeecodeqry where (qu
ery_code='1521' and fee_code=:b1)
15,027,250 5 3,005,450.0 6.7 47.80 47.59 31319754
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
9,305,060 168,893 55.1 4.1 1.21 45.43 1931538663
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select count(*) into :b0 from dConMsg a ,dConUserMsg b where (
((a.contract_no=b.contract_no and b.id_no=:b1) and b.serial_no=0
) and a.pay_code='4')
9,017,879 3 3,005,959.7 4.0 68.15 83.90 325431805
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
9,016,350 3 3,005,450.0 4.0 29.58 29.29 3086137835
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
8,139,204 4,553 1,787.7 3.6 414.02 430.81 954844454
Module: /account1/bin/stop/BcpNuser@BILLING1 (TNS V1-V3)
insert into pCustStatChg (phone_no,id_no,sm_code,op_code,run_cod
e,run_time)select :b0 ,0 ,'gn' ,'NUSR' ,'AA' ,sysdate from dual
where ( not exists (select 1 from dCustMsg where phone_no=:b0)
and not exists (select 1 from dCustNoOwnerMsg where (phone_no
=:b0 and op_time>add_months(sysdate,(-1)))))
8,138,776 4,553 1,787.6 3.6 413.50 429.88 40229392
Module: /account1/bin/stop/BcpNuser@BILLING1 (TNS V1-V3)
insert into dCustNoOwnerMsg (phone_no,run_code,op_time)select :b
0 ,'AA' ,sysdate from dual where ( not exists (select 1 from d
CustMsg where phone_no=:b0) and not exists (select 1 from dCus
tNoOwnerMsg where (phone_no=:b0 and op_time>add_months(sysdate,(
-1)))))
SQL ordered by Gets for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
6,011,659 2 3,005,829.5 2.7 29.39 37.93 903666932
Module: RPubWebRptGen@CRM_MIRROR (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
6,010,876 2 3,005,438.0 2.7 23.71 30.69 1638073794
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
6,010,876 2 3,005,438.0 2.7 24.37 30.71 3479178902
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
6,010,876 2 3,005,438.0 2.7 24.32 30.25 3549551739
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
where a.total_date>=to_number(:in_begin_day)
3,374,936 31,916 105.7 1.5 1.87 77.66 375256146
Module: R1300Itf@CRM_MIRROR (TNS V1-V3)
select MIN_YM into :b0 from DCONMSG where CONTRACT_NO=(select d
istinct contract_no from dConUserMsg where ((id_no=:b1 and seri
al_no=0) and bill_order=(select min(bill_order) from dConUserMs
g where (id_no=:b1 and serial_no=0))))
3,059,595 9,713 315.0 1.4 9.72 42.40 892712084
Module: R1300Query@OFFON1 (TNS V1-V3)
select TO_NUMBER(SUBSTR(item_code,3,2)) ,trim(item_code) ,trim(i
tem_name) into :b0,:b1,:b2 from sfeecodeqry where (query_code='
1522' and fee_code=:b3)
3,012,211 1 3,012,211.0 1.3 24.49 24.17 309366679
Module: RPubWebRptGen@CRM_MIRROR (TNS V1-V3)
begin PRC_1702_PS002('smcfg','02','**','**','**','**','**','2007
0201','20070228','**' ,20000448236182); end;
SQL ordered by Gets for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
3,008,825 1 3,008,825.0 1.3 22.94 31.07 3998956009
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
begin PRC_1702_PS002('smcfg','01','**','**','**','001','**','200
70201','20070228','**' ,20000448040038); end;
3,008,422 1 3,008,422.0 1.3 22.92 27.45 3376040608
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
begin PRC_1702_PS002('smcfg','14','**','**','**','001','**','200
70201','20070228','**' ,20000447990357); end;
3,008,420 1 3,008,420.0 1.3 22.84 26.20 1967695607
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
begin PRC_1702_PS002('smcfg','14','**','**','**','001','**','200
-------------------------------------------------------------
SQL ordered by Reads for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
455,096 16 28,443.5 19.9 22.03 63.35 3970772345
Module: R1557@CRM_MIRROR (TNS V1-V3)
select a.id_no ,a.contract_no ,a.id_iccid ,a.login_no ,a.total_d
ate ,substr(a.belong_code,1,2) ,a.save_code ,a.save_flag ,a.tota
l_fee ,a.prepay_fee ,b.pay_name ,a.month_fee ,a.favour_fee ,a.ca
rd_fee ,a.other_fee ,a.month_number ,to_char(a.begin_time,'YYYYM
MDD') ,to_char(a.end_time,'YYYYMMDD') ,to_char(op_time,'YYYYMMDD
183,683 1 183,683.0 8.0 8.69 56.35 1666839351
Module: oracle@CRM1 (TNS V1-V3)
SELECT "ID_NO","CUST_ID","CONTRACT_NO","LIMIT_OWE","CREDIT_CODE"
,"CREDIT_VALUE","RUN_CODE","RUN_TIME" FROM "DBACCADM"."DCUSTMSGD
EAD" "A"
183,149 1 183,149.0 8.0 7.87 41.68 1566328450
Module: oracle@CRM1 (TNS V1-V3)
SELECT "ID_NO","SM_CODE","OPEN_TIME","CREDIT_VALUE","RUN_CODE","
RUN_TIME","GROUP_ID" FROM "DBACCADM"."DCUSTMSGDEAD" "A"
150,786 1 150,786.0 6.6 35.21 343.24 2657302539
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
begin PRC_1702_PS002('smcfg','14','**','**','**','001','**','200
70101','20070131','**' ,20000448010058); end;
150,780 1 150,780.0 6.6 35.02 342.97 1057408891
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200701 a
where a.total_date>=to_number(:in_begin_day)
84,527 3 28,175.7 3.7 4.16 13.47 3148540390
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13335637695'
62,717 1 62,717.0 2.7 2.72 15.28 4218245477
Module: oracle@CRM1 (TNS V1-V3)
SELECT "ID_NO","UNBILL_FEE","PREPAY_FEE","OWE_FEE","OP_TIME" FRO
M "DBACCADM"."DCUSTCREDITBALANCE" "A"
62,409 1 62,409.0 2.7 2.56 11.44 2145308681
Module: oracle@CRM1 (TNS V1-V3)
SELECT "ID_NO","LIMIT_OWE","UNBILL_FEE","PREPAY_FEE","OWE_FEE","
OP_TIME" FROM "DBACCADM"."DCUSTCREDITBALANCE" "A"
60,361 3 20,120.3 2.6 68.15 83.90 325431805
Module: RPubWebRptGen@OFFON1 (TNS V1-V3)
insert into wpaytemp select a.login_no,a.op_code
,a.pay_type,a.back_flag,count(a.login_accept),sum(a.pay_money),s
um(a.payed_owe+a.dead_fee+a.bad_fee),sum(a.delay_fee)
from wpay200702 a
SQL ordered by Reads for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
where a.total_date>=to_number(:in_begin_day)
39,363 25,723 1.5 1.7 13.26 443.53 4107065061
Module: R1300Print@OFFON1 (TNS V1-V3)
begin EXECUTE IMMEDIATE :TmpSqlStr into :should_pay , :favour_fe
e , :payed_prepay , :payed_later using :contract_no , :id_no ; E
ND ;
29,329 1 29,329.0 1.3 1.33 3.07 1603243170
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13195521775'
29,327 1 29,327.0 1.3 1.27 2.85 1026460270
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13365633257'
29,326 1 29,326.0 1.3 1.32 2.84 501136882
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13170138628'
29,326 1 29,326.0 1.3 1.26 2.85 3207990484
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13205675580'
29,325 1 29,325.0 1.3 1.35 2.93 1008551857
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13170179901'
29,319 1 29,319.0 1.3 1.29 4.91 2766439327
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13014015388'
29,313 1 29,313.0 1.3 1.44 6.64 3219157484
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13083026042'
29,313 1 29,313.0 1.3 1.16 4.07 3733913812
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
SQL ordered by Reads for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
"A1" WHERE "A1"."PHONE_NO"='13014047872'
29,295 1 29,295.0 1.3 1.37 7.01 1334391698
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13195341761'
29,269 1 29,269.0 1.3 1.35 3.65 916840149
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13365551072'
29,259 1 29,259.0 1.3 1.37 3.02 3518447985
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
-------------------------------------------------------------
SQL ordered by Executions for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
212,784 212,784 1.0 0.00 0.00 2460221484
Module: oracle@CRM1 (TNS V1-V3)
SELECT "CONTRACT_NO","PAY_CODE" FROM "DBACCADM"."DCONMSG" "B" WH
ERE "CONTRACT_NO"=:1
199,270 199,134 1.0 0.00 0.00 2559662561
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select count(*) into :b0 from dConMsg a ,sAccountType b where
(((contract_no=:b1 and a.pay_code<>'4') and a.account_type=b.acc
ount_type) and b.flag='0')
198,382 198,349 1.0 0.00 0.00 1975747683
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select phone_no ,ids ,substr(belong_code,1,2) ,sm_code into :b0,
:b1,:b2,:b3 from dCustMsg where (id_no=:b4 and substr(run_code,
2,1)<'a')
176,939 176,830 1.0 0.00 0.00 3017632330
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
begin EXECUTE IMMEDIATE :v_tmp_buf into :v_owe_fee using :v_cont
ract_no ; END ;
172,314 172,262 1.0 0.00 0.00 2670561399
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select cust_id ,contract_no ,ids ,phone_no ,sm_code ,attr_code ,
belong_code ,limit_owe ,credit_code ,credit_value ,run_code ,to_
char(run_time,'YYYYMMDD HH24:MI:SS') ,cmd_right ,nvl(stop_attr,'
AAA') ,nvl(group_id,'UnKnown') ,bak_field into :b0,:b1,:b2,:b3,:
b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 from dCust
172,230 175,115 1.0 0.00 0.00 1333119256
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select a.operate_type ,b.type_flag from sStopOprTypeOrder a ,sS
topOprTypeCode b where ((op_type=:b0 and a.operate_type=b.operat
e_type) and op_code=:b1) order by serial_no
172,039 0 0.0 0.00 0.00 2921912018
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select nvl(limit_owe,0) into :b0 from sCardStopCode where (regi
on_code=substr(:b1,1,2) and card_code=substr(:b2,3,2))
171,947 171,960 1.0 0.00 0.00 4042630244
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select attr_code ,belong_code into :b0,:b1 from dCustMsg where
(id_no=:b2 and substr(run_code,2,1)<'a')
169,043 0 0.0 0.00 0.00 87929808
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select awake_fee ,awake_times ,calling_times ,to_number(begin_hm
) ,to_number(end_hm) ,time_flag ,forbid_flag ,limit_call_fee int
o :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 from dCustAwake where id_no=:
b8
169,029 168,991 1.0 0.00 0.00 859277994
SQL ordered by Executions for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select count(*) into :b0 from sCustGradeCode where ((region_co
de=substr(:b1,1,2) and owner_code=substr(:b2,1,2)) and vip_flag=
'1')
169,000 0 0.0 0.00 0.00 232557060
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select nvl(calling_fee,0) into :b0 from sStopDateCalling where
(region_code=substr(:b1,1,2) and stop_date=to_number(to_char(sys
date,'DD')))
168,966 169,048 1.0 0.00 0.00 1641017865
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select awake_fee ,awake_times ,calling_fee ,limit_flag ,calling_
times ,calling_cyc ,nvl(limit_call_fee,0.00) into :b0,:b1,:b2,:b
3,:b4,:b5,:b6 from sAwakeCallingCode where (((region_code=subst
r(:b7,1,2) and op_code=:b8) and operate_type=:b9) and operate_co
de=:b10)
168,965 168,937 1.0 0.00 0.00 908745005
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
insert into dcustcreditbalance (id_no,limit_owe,unbill_fee,prepa
y_fee,owe_fee,op_time) values (:b0,:b1,:b2,:b3,:b4,sysdate)
168,893 168,952 1.0 0.00 0.00 1931538663
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select count(*) into :b0 from dConMsg a ,dConUserMsg b where (
((a.contract_no=b.contract_no and b.id_no=:b1) and b.serial_no=0
) and a.pay_code='4')
168,859 168,954 1.0 0.00 0.00 2734308029
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select pay_code into :b0 from dConMsg where contract_no=:b1
168,441 168,441 1.0 0.00 0.00 4120737253
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select org_code ,org_id ,to_char(sysdate,'YYYYMMDD HH24:MI:SS')
,sMaxSysAccept.nextval into :b0,:b1,:b2,:b3 from dLoginMsg whe
re login_no=:b4
168,394 168,303 1.0 0.00 0.00 477416225
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select login_region into :b0 from sRegionCode where region_code
=trim(:b1)
168,155 168,144 1.0 0.00 0.00 2410741627
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select AWAKE_FLAG ,STOP_FLAG ,LIMIT_FLAG ,PREPAY_FLAG into :b0,:
b1,:b2,:b3 from dAwakeCtr where id_no=:b4
168,089 168,116 1.0 0.00 0.00 883270137
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select count(*) into :b0 from dAwakeCtr where id_no=:b1
SQL ordered by Executions for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
167,952 168,003 1.0 0.00 0.00 163094052
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select count(*) into :b0 from dconmsgpre a ,sPayType b where (
((((contract_no=:b1 and a.pay_type=b.pay_type) and b.special_fla
g='1') and a.prepay_fee>0) and begin_dt<=:b2) and end_dt>:b2)
167,932 167,985 1.0 0.00 0.00 2009294770
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select nvl(sum(prepay_fee),0) into :b0 from dConMsgPre a ,sPayT
ype b where ((((contract_no=:b1 and a.pay_type=b.pay_type) and b
.special_flag<>'1') and begin_dt<=:b2) and end_dt>:b2)
167,909 167,929 1.0 0.00 0.00 1969363370
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select to_char(prepay_time,'YYYYMMDD HH24:MI:SS') ,min_ym into :
b0,:b1 from dConMsg where contract_no=:b2
167,819 171,862 1.0 0.00 0.00 2208570141
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
select distinct b.id_no ,trim(b.phone_no) ,b.limit_owe from dCo
nUserMsg a ,dCustMsg b where (a.contract_no=:b0 and a.id_no=b.id
_no)
128,087 128,106 1.0 0.00 0.00 4256219959
Module: RkfReckonQry@CRM_MIRROR (TNS V1-V3)
select to_number(item_code) into :b0 from sfeecodeqry where (qu
ery_code='1521' and fee_code=:b1)
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
43,507 45,666 3.31 2056067577
Module: R1300Init@CRM_MIRROR (TNS V1-V3)
select IP_ADDR ,COMM_PORT ,SERVER_NAME ,to_char(sysdate,'YYYYMMD
D HH24:MI:SS') into :b0,:b1,:b2,:b3 from sPhoneComm where ((PHO
NE_HEAD=:b4 and query_type=:b5) and default_flag=0)
41,028 41,028 3.12 1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
39,780 39,774 3.02 1172831400
Module: R1300Init@CRM_MIRROR (TNS V1-V3)
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERI
CA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CH
ARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYYM
MDD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY
' TIME_ZONE= '+08:00' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT =
38,590 39,096 2.93 1765419266
Module: RkfReckonQry@CRM_MIRROR (TNS V1-V3)
select ip_addr ,to_char(comm_port) into :b0,:b1 from sPhoneComm
where (phone_head=:b2 and query_type=:b3)
38,577 39,084 2.93 1039056369
Module: RkfReckonQry@CRM_MIRROR (TNS V1-V3)
select substrb(phone_no,1,7) ,phone_no into :b0,:b1 from dCustM
sg where id_no=:b2
34,625 34,651 2.63 3949959653
Module: R1300Print@CRM_MIRROR (TNS V1-V3)
select prepay_fee ,owe_fee into :b0,:b1 from dConMsg where cont
ract_no=:b2
34,314 34,284 2.61 1203816046
Module: R1300Itf@CRM_MIRROR (TNS V1-V3)
select contract_no ,user_passwd ,credit_value ,id_no into :b0,:b
1,:b2,:b3 from dCustMsg where phone_no=:b4
32,071 32,069 2.44 1552793035
Module: R1300Itf@CRM_MIRROR (TNS V1-V3)
select distinct contract_no from dConUserMsg where id_no=:v1 and
serial_no=0
32,063 32,091 2.44 2695030252
Module: R1300Itf@CRM_MIRROR (TNS V1-V3)
select (to_char(sysdate,'yyyymm')||'01000000') ,(to_char(sysdate
,'yyyymmdd')||'235959') into :b0,:b1 from dual
32,045 31,916 2.43 375256146
Module: R1300Itf@CRM_MIRROR (TNS V1-V3)
select MIN_YM into :b0 from DCONMSG where CONTRACT_NO=(select d
istinct contract_no from dConUserMsg where ((id_no=:b1 and seri
al_no=0) and bill_order=(select min(bill_order) from dConUserMs
SQL ordered by Parse Calls for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
g where (id_no=:b1 and serial_no=0))))
23,105 0 1.76 2615455129
Module: oracle@CRM1 (TNS V1-V3)
SELECT * FROM "DBACCADM"."DCUSTMSG"
18,972 24,127 1.44 2740825592
Module: RkfReckonQry@CRM_MIRROR (TNS V1-V3)
select 1 into :b0 from dual
17,062 17,062 1.30 1992987695
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007035 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,981 16,981 1.29 3382397401
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007038 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,973 16,973 1.29 4287875706
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007032 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,917 16,916 1.29 16212981
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007034 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,893 16,892 1.28 2597340965
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007030 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,854 16,852 1.28 3785536722
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007036 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,684 16,684 1.27 1991825597
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007033 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,666 16,665 1.27 1725079535
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SQL ordered by Parse Calls for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007031 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,541 16,541 1.26 1954420858
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007039 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
16,526 16,525 1.26 2067455573
Module: /account1/bin/stop/rtStopMain@BILLING1 (TNS V1-V
SELECT nvl(sum(should_pay-favour_fee-payed_prepay-payed_later),0
) FROM dCustOweTotal2007037 WHERE contract_no=:v1 AND payed_stat
us in ('0','9')
14,513 27,805 1.10 1377486047
Module: R1300Init@CRM_MIRROR (TNS V1-V3)
select work_type into :b0 from sPayWorkNoType where :b1 like lo
gin_format
9,675 9,693 0.74 697919914
Module: R1300Pub@CRM_MIRROR (TNS V1-V3)
select cust_name into :b0 from dCustDoc where cust_id=:b1
9,490 10,205 0.72 3793337549
Module: R1300Init@CRM_MIRROR (TNS V1-V3)
select to_char(sysdate,'YYYYMMDD') into :b0 from dual
9,101 9,288 0.69 3184154178
Module: R1300Init@CRM_MIRROR (TNS V1-V3)
select nvl(sum(prepay_fee),0) into :b0 from dConMsgPre where ((
-------------------------------------------------------------
SQL ordered by Version Count for DB: ACC Instance: acc1 Snaps: 662 -663
-> End Version Count Threshold: 20
Version
Count Executions Hash Value
-------- ------------ ------------
33 408 2250267358
Module: /account1/bin/stop/BcpNuser@BILLING1 (TNS V1-V3)
insert into wNuserMsg (phone_no,nuser_fee,nuser_type,nuser_time,
cmd_flag)select :b0 ,:b1 ,:b2 ,to_date(:b3,'yyyymmdd hh24:mi:ss'
) ,'0' from dual
-------------------------------------------------------------
Instance Activity Stats for DB: ACC Instance: acc1 Snaps: 662 -663
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 260,003 89.2 1.3
CPU used when call started 260,042 89.2 1.3
CR blocks created 15,358 5.3 0.1
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DBWR buffers scanned 40,619 13.9 0.2
DBWR checkpoint buffers written 73,923 25.4 0.4
DBWR checkpoints 1 0.0 0.0
DBWR cross instance writes 0 0.0 0.0
DBWR free buffers found 40,051 13.7 0.2
DBWR fusion writes 9,056 3.1 0.1
DBWR lru scans 256 0.1 0.0
DBWR make free requests 267 0.1 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 40,619 13.9 0.2
DBWR transaction table writes 275 0.1 0.0
DBWR undo block writes 24,705 8.5 0.1
DDL statements parallelized 0 0.0 0.0
DFO trees parallelized 2 0.0 0.0
PX local messages recv'd 464 0.2 0.0
PX local messages sent 464 0.2 0.0
PX remote messages recv'd 103 0.0 0.0
PX remote messages sent 1,732 0.6 0.0
Parallel operations downgraded 75 2 0.0 0.0
Parallel operations downgraded to 0 0.0 0.0
Parallel operations not downgrade 0 0.0 0.0
RowCR - row contention 0 0.0 0.0
RowCR attempts 44 0.0 0.0
RowCR hits 44 0.0 0.0
SQL*Net roundtrips to/from client 6,943,597 2,382.0 35.5
SQL*Net roundtrips to/from dblink 1,498,533 514.1 7.7
active txn count during cleanout 22,525 7.7 0.1
background checkpoints completed 1 0.0 0.0
background checkpoints started 1 0.0 0.0
background timeouts 6,451 2.2 0.0
branch node splits 0 0.0 0.0
buffer is not pinned count 172,363,928 59,130.0 881.7
buffer is pinned count 239,593,147 82,193.2 1,225.5
bytes received via SQL*Net from c 941,746,422 323,069.1 4,817.1
bytes received via SQL*Net from d 410,407,353 140,791.5 2,099.3
bytes sent via SQL*Net to client 2,730,849,069 936,826.4 13,968.5
bytes sent via SQL*Net to dblink 330,916,048 113,521.8 1,692.7
calls to get snapshot scn: kcmgss 6,942,070 2,381.5 35.5
calls to kcmgas 359,086 123.2 1.8
calls to kcmgcs 5,721 2.0 0.0
change write time 1,315 0.5 0.0
cleanout - number of ktugct calls 22,340 7.7 0.1
cluster key scan block gets 6,734 2.3 0.0
cluster key scans 1,719 0.6 0.0
commit cleanout failures: block l 1,766 0.6 0.0
commit cleanout failures: buffer 2 0.0 0.0
commit cleanout failures: callbac 272 0.1 0.0
commit cleanout failures: cannot 37 0.0 0.0
commit cleanouts 421,429 144.6 2.2
commit cleanouts successfully com 419,352 143.9 2.2
commit txn count during cleanout 7,654 2.6 0.0
Instance Activity Stats for DB: ACC Instance: acc1 Snaps: 662 -663
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
consistent changes 54,858 18.8 0.3
consistent gets 222,435,523 76,307.2 1,137.8
consistent gets - examination 21,928,064 7,522.5 112.2
current blocks converted for CR 93 0.0 0.0
cursor authentications 73 0.0 0.0
data blocks consistent reads - un 19,902 6.8 0.1
db block changes 2,554,518 876.3 13.1
db block gets 3,422,484 1,174.1 17.5
deferred (CURRENT) block cleanout 122,351 42.0 0.6
dirty buffers inspected 241 0.1 0.0
enqueue conversions 11,676,478 4,005.7 59.7
enqueue releases 6,897,846 2,366.3 35.3
enqueue requests 6,897,859 2,366.3 35.3
enqueue timeouts 22 0.0 0.0
enqueue waits 78,599 27.0 0.4
exchange deadlocks 16 0.0 0.0
execute count 5,828,638 1,999.5 29.8
free buffer inspected 1,124 0.4 0.0
free buffer requested 2,335,035 801.0 11.9
gcs messages sent 2,373,087 814.1 12.1
ges messages sent 360,177 123.6 1.8
global cache blocks lost 5 0.0 0.0
global cache convert time 10,756 3.7 0.1
global cache converts 48,263 16.6 0.3
global cache cr block build time 86 0.0 0.0
global cache cr block flush time 1,607 0.6 0.0
global cache cr block receive tim 4,755 1.6 0.0
global cache cr block send time 201 0.1 0.0
global cache cr blocks received 86,556 29.7 0.4
global cache cr blocks served 82,649 28.4 0.4
global cache current block flush 80 0.0 0.0
global cache current block pin ti 7,891 2.7 0.0
global cache current block receiv 12,829 4.4 0.1
global cache current block send t 738 0.3 0.0
global cache current blocks recei 78,208 26.8 0.4
global cache current blocks serve 278,428 95.5 1.4
global cache defers 1,533 0.5 0.0
global cache get time 47,129 16.2 0.2
global cache gets 2,188,418 750.7 11.2
global cache prepare failures 0 0.0 0.0
global cache skip prepare failure 31,317 10.7 0.2
global lock async converts 2,481 0.9 0.0
global lock async gets 2,453 0.8 0.0
global lock convert time 5 0.0 0.0
global lock get time 5,601 1.9 0.0
global lock releases 2,201,968 755.4 11.3
global lock sync converts 3,081 1.1 0.0
global lock sync gets 2,212,087 758.9 11.3
hot buffers moved to head of LRU 464,309 159.3 2.4
immediate (CR) block cleanout app 16,318 5.6 0.1
immediate (CURRENT) block cleanou 15,517 5.3 0.1
index fast full scans (full) 128,542 44.1 0.7
index fast full scans (rowid rang 0 0.0 0.0
index fetch by key 2,845,557 976.2 14.6
index scans kdiixs1 12,399,513 4,253.7 63.4
leaf node 90-10 splits 16 0.0 0.0
Instance Activity Stats for DB: ACC Instance: acc1 Snaps: 662 -663
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
leaf node splits 397 0.1 0.0
logons cumulative 41,080 14.1 0.2
messages received 378,392 129.8 1.9
messages sent 378,392 129.8 1.9
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 187,176,161 64,211.4 957.4
opened cursors cumulative 915,885 314.2 4.7
parse count (failures) 64,653 22.2 0.3
parse count (hard) 82,492 28.3 0.4
parse count (total) 1,316,208 451.5 6.7
parse time cpu 9,513 3.3 0.1
parse time elapsed 69,762 23.9 0.4
physical reads 2,283,139 783.2 11.7
physical reads direct 152,496 52.3 0.8
physical writes 475,950 163.3 2.4
physical writes direct 394,107 135.2 2.0
physical writes non checkpoint 439,185 150.7 2.3
pinned buffers inspected 830 0.3 0.0
prefetch clients - default 0 0.0 0.0
prefetched blocks 1,680,607 576.5 8.6
prefetched blocks aged out before 66 0.0 0.0
process last non-idle time 0 0.0 0.0
queries parallelized 2 0.0 0.0
recursive calls 1,591,413 545.9 8.1
recursive cpu usage 61,753 21.2 0.3
redo blocks written 925,759 317.6 4.7
redo buffer allocation retries 6 0.0 0.0
redo entries 1,502,815 515.6 7.7
redo log space requests 6 0.0 0.0
redo log space wait time 32 0.0 0.0
redo ordering marks 10,951 3.8 0.1
redo size 348,825,876 119,665.8 1,784.3
redo synch time 118,728 40.7 0.6
redo synch writes 380,353 130.5 2.0
redo wastage 110,266,576 37,827.3 564.0
redo write time 82,667 28.4 0.4
redo writer latching time 84 0.0 0.0
redo writes 360,791 123.8 1.9
remote instance undo block writes 0 0.0 0.0
rollback changes - undo records a 200 0.1 0.0
rows fetched via callback 2,789,265 956.9 14.3
session connect time 0 0.0 0.0
session logical reads 225,857,507 77,481.1 1,155.3
session pga memory 6,023,200 2,066.3 30.8
session pga memory max 61,693,208 21,164.1 315.6
session uga memory 577,565,960 198,135.8 2,954.3
session uga memory max 6,460,931,272 2,216,443.0 33,048.1
shared hash latch upgrades - no w 12,412,736 4,258.2 63.5
shared hash latch upgrades - wait 40,499 13.9 0.2
sorts (disk) 0 0.0 0.0
sorts (memory) 1,124,433 385.7 5.8
sorts (rows) 23,581,581 8,089.7 120.6
summed dirty queue length 824 0.3 0.0
switch current to new buffer 6,080 2.1 0.0
table fetch by rowid 184,451,569 63,276.7 943.5
table fetch continued row 121 0.0 0.0
Instance Activity Stats for DB: ACC Instance: acc1 Snaps: 662 -663
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scan blocks gotten 33,110,649 11,358.7 169.4
table scan rows gotten 4,214,881,843 1,445,928.6 21,559.4
table scans (direct read) 0 0.0 0.0
table scans (long tables) 194 0.1 0.0
table scans (rowid ranges) 144 0.1 0.0
table scans (short tables) 306,599 105.2 1.6
transaction lock background gets 0 0.0 0.0
transaction lock foreground reque 0 0.0 0.0
transaction lock foreground wait 0 0.0 0.0
transaction rollbacks 3,669 1.3 0.0
transaction tables consistent rea 1 0.0 0.0
transaction tables consistent rea 10 0.0 0.0
user calls 7,212,559 2,474.3 36.9
user commits 188,447 64.7 1.0
user rollbacks 7,054 2.4 0.0
workarea executions - multipass 10 0.0 0.0
workarea executions - onepass 16 0.0 0.0
workarea executions - optimal 1,089,741 373.8 5.6
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 12 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCMSG02
172,724 59 2.3 10.0 18,714 6 157 9.8
ACCHIS01
95,034 33 2.3 1.0 3 0 1 30.0
ACCMSG01
44,207 15 6.3 1.2 10,769 4 0 0.0
ACCHIS01_IDX
38,318 13 4.4 1.0 3 0 2 20.0
UNDOTBS1
27 0 19.6 1.0 24,950 9 28 0.7
ACCOUNT03_IDX
16,955 6 4.5 1.0 6,804 2 129 32.3
TEMP
6,623 2 2.1 22.8 14,748 5 0 0.0
ACCOUNT02
15,358 5 11.4 1.0 1,259 0 1 10.0
ACCMSG01_IDX
10,948 4 10.0 1.2 4,255 1 1,199 9.6
ACCMSG02_IDX
6,471 2 11.6 1.0 7,939 3 89 3.8
ACCOUNT02_IDX
11,645 4 11.3 1.0 4 0 1 20.0
SYSTEM
10,041 3 3.4 2.5 319 0 2 0.0
PERSTAT
4,267 1 4.4 1.2 3,487 1 0 0.0
ACCHIS12
6,733 2 12.0 1.0 479 0 2 15.0
ACCHIS12_IDX
5,496 2 10.8 1.0 674 0 2 10.0
ACCOUNT01
5,541 2 11.2 1.0 178 0 1 10.0
ACCHIS11
5,221 2 11.3 1.0 16 0 3 10.0
ACCOUNT03
3,307 1 11.9 1.0 1,710 1 0 0.0
ACCHIS10
4,704 2 11.3 1.0 9 0 1 10.0
ACCHIS11_IDX
4,617 2 11.0 1.0 3 0 2 10.0
ACCHIS09
4,209 1 11.3 1.0 8 0 1 10.0
ACCHIS10_IDX
4,190 1 10.6 1.0 3 0 1 30.0
ACCHIS09_IDX
3,817 1 11.0 1.0 3 0 1 10.0
RPT01
3,711 1 4.5 4.0 6 0 0 0.0
ACCHIS08
3,613 1 11.1 1.0 9 0 0 0.0
ACCHIS08_IDX
3,307 1 11.0 1.0 3 0 0 0.0
Tablespace IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCHIS07
3,003 1 11.3 1.0 9 0 0 0.0
ACCHIS07_IDX
2,784 1 11.0 1.0 3 0 0 0.0
ACCHIS06
2,335 1 11.4 1.0 8 0 0 0.0
ACCHIS06_IDX
2,227 1 10.8 1.0 3 0 0 0.0
ACCHIS05
2,100 1 10.9 1.0 6 0 0 0.0
ACCHIS05_IDX
2,042 1 11.2 1.0 3 0 0 0.0
ACCHIS04
1,914 1 11.5 1.0 5 0 0 0.0
ACCHIS04_IDX
1,876 1 11.2 1.0 3 0 0 0.0
ACCHIS03
1,755 1 11.4 1.0 4 0 0 0.0
ACCHIS03_IDX
1,720 1 11.5 1.0 3 0 0 0.0
ACCHIS02
1,571 1 12.0 1.0 5 0 0 0.0
ACCHIS02_IDX
1,548 1 11.2 1.0 3 0 0 0.0
RPT02
1,504 1 6.3 14.8 6 0 0 0.0
ACCTDATA01
1,098 0 12.8 1.0 100 0 0 0.0
ACCMSGCFG
838 0 9.5 1.1 5 0 9 17.8
transt_data_01
205 0 36.5 1.0 40 0 0 0.0
ACCMSGCFG_IDX
81 0 10.5 1.0 1 0 4 5.0
ACCOUNT04_IDX
35 0 12.6 1.0 3 0 0 0.0
ACCOUNT09_IDX
27 0 16.7 1.0 3 0 0 0.0
ACCOUNT09
23 0 40.0 1.0 5 0 0 0.0
ACCOUNT06_IDX
24 0 11.3 1.0 3 0 0 0.0
ACCOUNT01_IDX
22 0 20.9 1.0 3 0 0 0.0
ACCOUNT10
20 0 56.0 1.0 5 0 0 0.0
UNDOTBS2
18 0 22.8 1.0 7 0 70 41.7
ACCOUNT07_IDX
21 0 11.9 1.0 3 0 0 0.0
ACCOUNT08_IDX
21 0 11.4 1.0 3 0 0 0.0
Tablespace IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCOUNT10_IDX
21 0 18.1 1.0 3 0 0 0.0
RPT_IDX01
18 0 16.7 1.0 6 0 0 0.0
ACCOUNT06
17 0 51.2 1.0 5 0 0 0.0
ACCOUNT08
17 0 50.6 1.0 5 0 0 0.0
ACCOUNT05_IDX
18 0 11.7 1.0 3 0 0 0.0
ACCOUNT07
16 0 53.8 1.0 5 0 0 0.0
ACCTDATA03
12 0 25.0 1.0 6 0 0 0.0
ACCOUNT04
11 0 54.5 1.0 5 0 0 0.0
ACCOUNT05
10 0 60.0 1.0 5 0 0 0.0
ACCTDATA02
6 0 50.0 1.0 6 0 0 0.0
ACCTDATA04
8 0 25.0 1.0 4 0 0 0.0
USERTEMP
6 0 11.7 1.0 1 0 0 0.0
ACCOUNT11
2 0 25.0 1.0 1 0 0 0.0
ACCOUNT11_IDX
2 0 25.0 1.0 1 0 0 0.0
ACCOUNT12
2 0 25.0 1.0 1 0 0 0.0
ACCOUNT12_IDX
2 0 25.0 1.0 1 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCHIS01 /dev/racchis01_01lv
31,604 11 2.4 1.0 1 0 1 30.0
/dev/racchis01_02lv
31,682 11 2.3 1.0 1 0 0
/dev/racchis01_03lv
31,748 11 2.4 1.0 1 0 0
ACCHIS01_IDX /dev/racchis01_04lv
12,603 4 4.5 1.0 1 0 1 10.0
/dev/racchis01_05lv
12,956 4 4.1 1.0 1 0 1 30.0
/dev/racchis01_06lv
12,759 4 4.5 1.0 1 0 0
ACCHIS02 /dev/racchis02_01lv
590 0 12.5 1.0 3 0 0
/dev/racchis02_02lv
523 0 11.6 1.0 1 0 0
/dev/racchis02_03lv
458 0 11.7 1.0 1 0 0
ACCHIS02_IDX /dev/racchis02_04lv
530 0 11.0 1.0 1 0 0
/dev/racchis02_05lv
533 0 10.5 1.0 1 0 0
/dev/racchis02_06lv
485 0 12.2 1.0 1 0 0
ACCHIS03 /dev/racchis03_01lv
688 0 11.8 1.0 1 0 0
/dev/racchis03_02lv
507 0 11.8 1.0 1 0 0
/dev/racchis03_03lv
560 0 10.5 1.0 2 0 0
ACCHIS03_IDX /dev/racchis03_04lv
559 0 11.0 1.0 1 0 0
/dev/racchis03_05lv
626 0 11.8 1.0 1 0 0
/dev/racchis03_06lv
535 0 11.8 1.0 1 0 0
ACCHIS04 /dev/racchis04_01lv
778 0 11.8 1.0 2 0 0
/dev/racchis04_02lv
524 0 11.5 1.0 1 0 0
/dev/racchis04_03lv
612 0 11.0 1.0 2 0 0
ACCHIS04_IDX /dev/racchis04_04lv
602 0 11.9 1.0 1 0 0
/dev/racchis04_05lv
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
686 0 10.5 1.0 1 0 0
ACCHIS04_IDX /dev/racchis04_06lv
588 0 11.3 1.0 1 0 0
ACCHIS05 /dev/racchis05_01lv
830 0 11.4 1.0 1 0 0
/dev/racchis05_02lv
599 0 11.5 1.0 2 0 0
/dev/racchis05_03lv
671 0 9.8 1.0 3 0 0
ACCHIS05_IDX /dev/racchis05_04lv
693 0 11.6 1.0 1 0 0
/dev/racchis05_05lv
782 0 10.8 1.0 1 0 0
/dev/racchis05_06lv
567 0 11.2 1.0 1 0 0
ACCHIS06 /dev/racchis06_01lv
864 0 11.9 1.0 5 0 0
/dev/racchis06_02lv
698 0 11.8 1.0 2 0 0
/dev/racchis06_03lv
773 0 10.5 1.0 1 0 0
ACCHIS06_IDX /dev/racchis06_04lv
650 0 10.6 1.0 1 0 0
/dev/racchis06_05lv
887 0 10.8 1.0 1 0 0
/dev/racchis06_06lv
690 0 10.9 1.0 1 0 0
ACCHIS07 /dev/racchis07_01lv
1,121 0 11.5 1.0 4 0 0
/dev/racchis07_02lv
889 0 11.3 1.0 2 0 0
/dev/racchis07_03lv
993 0 11.0 1.0 3 0 0
ACCHIS07_IDX /dev/racchis07_04lv
1,031 0 10.8 1.0 1 0 0
/dev/racchis07_05lv
769 0 11.1 1.0 1 0 0
/dev/racchis07_06lv
984 0 11.0 1.0 1 0 0
ACCHIS08 /dev/racchis08_01lv
1,309 0 11.6 1.0 5 0 0
/dev/racchis08_02lv
1,055 0 11.2 1.0 1 0 0
/dev/racchis08_03lv
1,249 0 10.5 1.0 3 0 0
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCHIS08_IDX /dev/racchis08_04lv
1,107 0 10.9 1.0 1 0 0
/dev/racchis08_05lv
959 0 11.0 1.0 1 0 0
/dev/racchis08_06lv
1,241 0 11.0 1.0 1 0 0
ACCHIS09 /dev/racchis09_01lv
1,504 1 11.7 1.0 2 0 1 10.0
/dev/racchis09_02lv
1,292 0 11.4 1.0 1 0 0
/dev/racchis09_03lv
1,413 0 10.8 1.0 5 0 0
ACCHIS09_IDX /dev/racchis09_04lv
1,177 0 11.0 1.0 1 0 0
/dev/racchis09_05lv
1,304 0 10.8 1.0 1 0 1 10.0
/dev/racchis09_06lv
1,336 0 11.2 1.0 1 0 0
ACCHIS10 /dev/racchis10_01lv
1,648 1 12.2 1.0 3 0 0
/dev/racchis10_02lv
1,485 1 11.1 1.0 5 0 1 10.0
/dev/racchis10_03lv
1,571 1 10.6 1.0 1 0 0
ACCHIS10_IDX /dev/racchis10_04lv
1,280 0 10.4 1.0 1 0 1 30.0
/dev/racchis10_05lv
1,552 1 10.7 1.0 1 0 0
/dev/racchis10_06lv
1,358 0 10.7 1.0 1 0 0
ACCHIS11 /dev/racchis11_01lv
1,910 1 11.7 1.0 7 0 1 10.0
/dev/racchis11_02lv
1,713 1 11.4 1.0 7 0 1 10.0
/dev/racchis11_03lv
1,598 1 10.5 1.0 2 0 1 10.0
ACCHIS11_IDX /dev/racchis11_04lv
1,710 1 10.3 1.0 1 0 1 10.0
/dev/racchis11_05lv
1,511 1 11.6 1.0 1 0 1 10.0
/dev/racchis11_06lv
1,396 0 11.1 1.0 1 0 0
ACCHIS12 /dev/racchis12_01lv
2,082 1 13.5 1.0 234 0 0
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCHIS12 /dev/racchis12_02lv
2,037 1 11.6 1.0 136 0 1 10.0
/dev/racchis12_03lv
1,725 1 10.6 1.0 7 0 0
/dev/racchis12_07lv
889 0 12.3 1.0 102 0 1 20.0
ACCHIS12_IDX /dev/racchis12_04lv
2,103 1 10.6 1.0 211 0 1 0.0
/dev/racchis12_05lv
1,690 1 10.9 1.0 220 0 1 20.0
/dev/racchis12_06lv
1,703 1 11.1 1.0 243 0 0
ACCMSG01 /dev/raccmsg01_01lv
4,300 1 7.4 1.5 1,095 0 0
/dev/raccmsg01_02lv
9,758 3 6.2 1.1 2,198 1 0
/dev/raccmsg01_03lv
9,254 3 5.8 1.1 2,524 1 0
/dev/raccmsg01_04lv
8,038 3 6.2 1.1 1,945 1 0
/dev/raccmsg01_05lv
6,205 2 6.7 1.3 1,542 1 0
/dev/raccmsg01_06lv
6,652 2 6.5 1.2 1,465 1 0
ACCMSG01_IDX /dev/rag01_IDX_01lv
2,719 1 10.2 1.3 1,016 0 215 6.5
/dev/rag01_IDX_02lv
4,633 2 10.2 1.1 1,951 1 396 7.9
/dev/rag01_IDX_03lv
3,596 1 9.5 1.3 1,288 0 588 11.9
ACCMSG02 /dev/raccmsg02_01lv
27,974 10 2.6 10.6 2,806 1 0
/dev/raccmsg02_02lv
32,022 11 2.1 9.1 3,076 1 0
/dev/raccmsg02_03lv
24,747 8 2.1 10.2 3,387 1 1 0.0
/dev/raccmsg02_04lv
30,651 11 2.7 9.7 3,925 1 144 10.4
/dev/raccmsg02_05lv
28,610 10 2.3 10.4 3,355 1 12 3.3
/dev/raccmsg02_06lv
28,720 10 2.3 10.3 2,165 1 0
ACCMSG02_IDX /dev/rag02_IDX_01lv
2,159 1 11.4 1.0 2,621 1 0
/dev/rag02_IDX_02lv
2,162 1 11.4 1.0 2,652 1 0
/dev/rag02_IDX_03lv
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
2,150 1 12.0 1.0 2,666 1 89 3.8
ACCMSGCFG /dev/racc_mcfg_01lv
838 0 9.5 1.1 5 0 9 17.8
ACCMSGCFG_IDX /dev/racc_midx_01lv
81 0 10.5 1.0 1 0 4 5.0
ACCOUNT01 /dev/raccount01_02lv
1,131 0 11.1 1.0 44 0 0
/dev/raccount01_03lv
1,122 0 10.4 1.0 29 0 0
/dev/raccount01_04lv
869 0 11.3 1.0 31 0 0
/dev/raccount01_05lv
933 0 11.7 1.0 38 0 0
/dev/raccount01_06lv
1,486 1 11.6 1.0 36 0 1 10.0
ACCOUNT01_IDX /dev/rat01_IDX_01lv
7 0 21.4 1.0 1 0 0
/dev/rat01_IDX_02lv
8 0 20.0 1.0 1 0 0
/dev/rat01_IDX_03lv
7 0 21.4 1.0 1 0 0
ACCOUNT02 /dev/raccount02_02lv
3,241 1 11.6 1.0 276 0 0
/dev/raccount02_03lv
3,057 1 11.6 1.0 244 0 1 10.0
/dev/raccount02_04lv
3,157 1 11.3 1.0 225 0 0
/dev/raccount02_05lv
2,879 1 11.0 1.0 235 0 0
/dev/raccount02_06lv
3,024 1 11.5 1.0 279 0 0
ACCOUNT02_IDX /dev/raccount06_01lv
2,693 1 11.3 1.0 1 0 0
/dev/rat02_IDX_01lv
2,915 1 11.1 1.0 1 0 0
/dev/rat02_IDX_02lv
3,067 1 11.2 1.0 1 0 0
/dev/rat02_IDX_03lv
2,970 1 11.7 1.0 1 0 1 20.0
ACCOUNT03 /dev/raccount03_02lv
637 0 11.7 1.0 219 0 0
/dev/raccount03_03lv
705 0 11.9 1.0 420 0 0
/dev/raccount03_04lv
722 0 11.6 1.0 219 0 0
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCOUNT03 /dev/raccount03_05lv
515 0 12.1 1.0 434 0 0
/dev/raccount03_06lv
728 0 12.2 1.0 418 0 0
ACCOUNT03_IDX /dev/rat03_IDX_01lv
5,434 2 4.5 1.0 2,067 1 1 10.0
/dev/rat03_IDX_02lv
5,881 2 4.5 1.0 2,676 1 128 32.5
/dev/rat03_IDX_03lv
5,640 2 4.6 1.0 2,061 1 0
ACCOUNT04 /dev/raccount04_02lv
2 0 60.0 1.0 1 0 0
/dev/raccount04_03lv
3 0 40.0 1.0 1 0 0
/dev/raccount04_04lv
2 0 60.0 1.0 1 0 0
/dev/raccount04_05lv
2 0 60.0 1.0 1 0 0
/dev/raccount04_06lv
2 0 60.0 1.0 1 0 0
ACCOUNT04_IDX /dev/rat04_IDX_01lv
9 0 17.8 1.0 1 0 0
/dev/rat04_IDX_02lv
19 0 10.5 1.0 1 0 0
/dev/rat04_IDX_03lv
7 0 11.4 1.0 1 0 0
ACCOUNT05 /dev/raccount05_02lv
2 0 60.0 1.0 1 0 0
/dev/raccount05_03lv
2 0 60.0 1.0 1 0 0
/dev/raccount05_04lv
2 0 60.0 1.0 1 0 0
/dev/raccount05_05lv
2 0 60.0 1.0 1 0 0
/dev/raccount05_06lv
2 0 60.0 1.0 1 0 0
ACCOUNT05_IDX /dev/rat05_IDX_01lv
6 0 11.7 1.0 1 0 0
/dev/rat05_IDX_02lv
6 0 11.7 1.0 1 0 0
/dev/rat05_IDX_03lv
6 0 11.7 1.0 1 0 0
ACCOUNT06 /dev/raccount06_02lv
3 0 56.7 1.0 1 0 0
/dev/raccount06_03lv
3 0 56.7 1.0 1 0 0
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCOUNT06 /dev/raccount06_04lv
4 0 45.0 1.0 1 0 0
/dev/raccount06_05lv
4 0 45.0 1.0 1 0 0
/dev/raccount06_06lv
3 0 56.7 1.0 1 0 0
ACCOUNT06_IDX /dev/rat06_IDX_01lv
9 0 11.1 1.0 1 0 0
/dev/rat06_IDX_02lv
8 0 11.3 1.0 1 0 0
/dev/rat06_IDX_03lv
7 0 11.4 1.0 1 0 0
ACCOUNT07 /dev/raccount07_02lv
3 0 56.7 1.0 1 0 0
/dev/raccount07_03lv
3 0 56.7 1.0 1 0 0
/dev/raccount07_04lv
3 0 56.7 1.0 1 0 0
/dev/raccount07_05lv
3 0 56.7 1.0 1 0 0
/dev/raccount07_06lv
4 0 45.0 1.0 1 0 0
ACCOUNT07_IDX /dev/rat07_IDX_01lv
8 0 12.5 1.0 1 0 0
/dev/rat07_IDX_02lv
7 0 11.4 1.0 1 0 0
/dev/rat07_IDX_03lv
6 0 11.7 1.0 1 0 0
ACCOUNT08 /dev/raccount08_02lv
3 0 56.7 1.0 1 0 0
/dev/raccount08_03lv
4 0 42.5 1.0 1 0 0
/dev/raccount08_04lv
4 0 45.0 1.0 1 0 0
/dev/raccount08_05lv
3 0 56.7 1.0 1 0 0
/dev/raccount08_06lv
3 0 56.7 1.0 1 0 0
ACCOUNT08_IDX /dev/rat08_IDX_01lv
7 0 10.0 1.0 1 0 0
/dev/rat08_IDX_02lv
7 0 11.4 1.0 1 0 0
/dev/rat08_IDX_03lv
7 0 12.9 1.0 1 0 0
ACCOUNT09 /dev/raccount09_02lv
4 0 45.0 1.0 1 0 0
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCOUNT09 /dev/raccount09_03lv
3 0 56.7 1.0 1 0 0
/dev/raccount09_04lv
3 0 56.7 1.0 1 0 0
/dev/raccount09_05lv
10 0 23.0 1.0 1 0 0
/dev/raccount09_06lv
3 0 56.7 1.0 1 0 0
ACCOUNT09_IDX /dev/rat09_IDX_01lv
10 0 16.0 1.0 1 0 0
/dev/rat09_IDX_02lv
8 0 17.5 1.0 1 0 0
/dev/rat09_IDX_03lv
9 0 16.7 1.0 1 0 0
ACCOUNT10 /dev/raccount10_02lv
4 0 57.5 1.0 1 0 0
/dev/raccount10_03lv
4 0 57.5 1.0 1 0 0
/dev/raccount10_04lv
4 0 55.0 1.0 1 0 0
/dev/raccount10_05lv
4 0 55.0 1.0 1 0 0
/dev/raccount10_06lv
4 0 55.0 1.0 1 0 0
ACCOUNT10_IDX /dev/rat10_IDX_01lv
7 0 18.6 1.0 1 0 0
/dev/rat10_IDX_02lv
7 0 18.6 1.0 1 0 0
/dev/rat10_IDX_03lv
7 0 17.1 1.0 1 0 0
ACCOUNT11 /dev/raccount11_07lv
2 0 25.0 1.0 1 0 0
ACCOUNT11_IDX /dev/rat11_IDX_04lv
2 0 25.0 1.0 1 0 0
ACCOUNT12 /dev/raccount12_07lv
2 0 25.0 1.0 1 0 0
ACCOUNT12_IDX /dev/rat12_IDX_04lv
2 0 25.0 1.0 1 0 0
ACCTDATA01 /dev/racc_dt01_01lv
77 0 14.3 1.0 3 0 0
/dev/racc_dt01_02lv
109 0 13.0 1.0 9 0 0
/dev/racc_dt01_03lv
115 0 11.6 1.0 12 0 0
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCTDATA01 /dev/racc_dt01_04lv
90 0 11.7 1.0 4 0 0
/dev/racc_dt01_05lv
92 0 11.4 1.0 13 0 0
/dev/racc_dt01_06lv
45 0 11.3 1.0 1 0 0
/dev/racc_dt01_07lv
23 0 11.7 1.0 2 0 0
/dev/racc_dt01_08lv
86 0 12.1 1.0 10 0 0
/dev/racc_dt01_09lv
148 0 13.5 1.0 16 0 0
/dev/racc_dt01_10lv
92 0 12.5 1.0 13 0 0
/dev/racc_dt01_11lv
104 0 14.8 1.0 11 0 0
/dev/racc_dt01_12lv
117 0 13.3 1.0 6 0 0
ACCTDATA02 /dev/racc_dt02_01lv
1 0 50.0 1.0 1 0 0
/dev/racc_dt02_02lv
1 0 50.0 1.0 1 0 0
/dev/racc_dt02_03lv
1 0 50.0 1.0 1 0 0
/dev/racc_dt02_04lv
1 0 50.0 1.0 1 0 0
/dev/racc_dt02_05lv
1 0 50.0 1.0 1 0 0
/dev/racc_dt02_06lv
1 0 50.0 1.0 1 0 0
ACCTDATA03 /dev/racc_dt03_01lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt03_02lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt03_03lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt03_04lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt03_05lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt03_06lv
2 0 25.0 1.0 1 0 0
ACCTDATA04 /dev/racc_dt04_01lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt04_02lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt04_03lv
2 0 25.0 1.0 1 0 0
/dev/racc_dt04_04lv
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
2 0 25.0 1.0 1 0 0
PERSTAT /dev/rPERSTAT_lv
4,267 1 4.4 1.2 3,487 1 0
RPT01 /dev/rrpt01_01lv
716 0 4.6 3.6 1 0 0
/dev/rrpt01_02lv
631 0 4.4 3.8 1 0 0
/dev/rrpt01_03lv
333 0 3.8 6.2 1 0 0
/dev/rrpt01_04lv
312 0 4.4 6.8 1 0 0
/dev/rrpt01_05lv
934 0 4.6 3.3 1 0 0
/dev/rrpt01_06lv
785 0 4.7 3.5 1 0 0
RPT02 /dev/rrpt02_01lv
203 0 5.9 14.9 1 0 0
/dev/rrpt02_02lv
275 0 7.5 14.7 1 0 0
/dev/rrpt02_03lv
274 0 6.6 14.7 1 0 0
/dev/rrpt02_04lv
272 0 7.2 14.9 1 0 0
/dev/rrpt02_05lv
273 0 5.1 14.8 1 0 0
/dev/rrpt02_06lv
207 0 5.2 14.7 1 0 0
RPT_IDX01 /dev/rrpt_idx01_01lv
3 0 16.7 1.0 1 0 0
/dev/rrpt_idx01_02lv
3 0 16.7 1.0 1 0 0
/dev/rrpt_idx01_03lv
3 0 16.7 1.0 1 0 0
/dev/rrpt_idx01_04lv
3 0 16.7 1.0 1 0 0
/dev/rrpt_idx01_05lv
3 0 16.7 1.0 1 0 0
/dev/rrpt_idx01_06lv
3 0 16.7 1.0 1 0 0
SYSTEM /dev/racc_syslv
10,041 3 3.4 2.5 319 0 2 0.0
TEMP /dev/racc_temp01lv
3,242 1 2.6 22.7 5,902 2 0
/dev/racc_temp02lv
1,043 0 2.0 31.0 2,942 1 0
/dev/racc_temp03lv
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
2,338 1 1.3 19.5 5,904 2 0
UNDOTBS1 /dev/racc_undo01lv
1 0 50.0 1.0 5,258 2 8 2.5
/dev/racc_undo02lv
7 0 15.7 1.0 4,042 1 6 0.0
/dev/racc_undo03lv
7 0 17.1 1.0 6,813 2 3 0.0
/dev/racc_undo04lv
6 0 18.3 1.0 5,051 2 10 0.0
/dev/raccount07_01lv
6 0 23.3 1.0 3,786 1 1 0.0
UNDOTBS2 /dev/racc_undo05lv
1 0 50.0 1.0 1 0 8 5.0
/dev/racc_undo06lv
6 0 18.3 1.0 1 0 6 0.0
/dev/racc_undo07lv
1 0 50.0 1.0 1 0 2 5.0
/dev/racc_undo08lv
1 0 50.0 1.0 1 0 54 53.1
/dev/racc_undo09lv
3 0 16.7 1.0 1 0 0
/dev/racc_undo10lv
3 0 16.7 1.0 1 0 0
/dev/racc_undo11lv
3 0 16.7 1.0 1 0 0
USERTEMP /dev/rusertemp_01lv
6 0 11.7 1.0 1 0 0
transt_data_01 /dev/raccount01_01lv
7 0 25.7 1.0 1 0 0
/dev/raccount02_01lv
7 0 25.7 1.0 1 0 0
/dev/raccount03_01lv
7 0 25.7 1.0 1 0 0
/dev/raccount04_01lv
7 0 24.3 1.0 1 0 0
/dev/raccount05_01lv
6 0 23.3 1.0 1 0 0
/dev/raccount11_02lv
4 0 55.0 1.0 1 0 0
/dev/raccount11_03lv
4 0 55.0 1.0 1 0 0
/dev/raccount11_04lv
4 0 55.0 1.0 1 0 0
/dev/raccount11_05lv
4 0 55.0 1.0 1 0 0
/dev/raccount11_06lv
4 0 55.0 1.0 1 0 0
/dev/raccount12_02lv
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
4 0 55.0 1.0 1 0 0
transt_data_01 /dev/raccount12_03lv
4 0 55.0 1.0 1 0 0
/dev/raccount12_04lv
4 0 55.0 1.0 1 0 0
/dev/raccount12_05lv
4 0 55.0 1.0 1 0 0
/dev/raccount12_06lv
4 0 55.0 1.0 1 0 0
/dev/rat11_IDX_01lv
6 0 18.3 1.0 1 0 0
/dev/rat11_IDX_02lv
6 0 18.3 1.0 1 0 0
/dev/rat11_IDX_03lv
6 0 18.3 1.0 1 0 0
/dev/rat12_IDX_01lv
6 0 18.3 1.0 1 0 0
/dev/rat12_IDX_02lv
6 0 18.3 1.0 1 0 0
/dev/rat12_IDX_03lv
6 0 18.3 1.0 1 0 0
/dev/rcp01_IDX_02lv
5 0 50.0 1.0 1 0 0
/dev/rcp01_IDX_03lv
5 0 50.0 1.0 1 0 0
/dev/rcp02_IDX_01lv
6 0 30.0 1.0 1 0 0
/dev/rcp02_IDX_02lv
7 0 27.1 1.0 1 0 0
/dev/rcp02_IDX_03lv
7 0 25.7 1.0 1 0 0
/dev/rcrmsnap1_02lv
4 0 55.0 1.0 1 0 0
/dev/rcrmsnap1_03lv
4 0 55.0 1.0 1 0 0
/dev/rcrmsnap1_04lv
4 0 55.0 1.0 1 0 0
/dev/rcrmsnap1_05lv
4 0 55.0 1.0 1 0 0
/dev/rcrmsnap1_06lv
4 0 55.0 1.0 1 0 0
/dev/rcrmsnap2_02lv
5 0 50.0 1.0 1 0 0
/dev/rcrmsnap2_03lv
5 0 50.0 1.0 1 0 0
/dev/rcrmsnap2_04lv
5 0 50.0 1.0 1 0 0
/dev/rcrmsnap2_05lv
5 0 50.0 1.0 1 0 0
/dev/rcrmsnap2_06lv
5 0 50.0 1.0 1 0 0
/dev/rtrans_data_01lv
File IO Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
6 0 18.3 1.0 1 0 0
transt_data_01 /dev/rtrans_data_02lv
6 0 18.3 1.0 1 0 0
/dev/rtrans_data_03lv
6 0 18.3 1.0 1 0 0
/dev/rtrans_data_04lv
2 0 25.0 1.0 1 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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 1,369,708 99.1 225,571,585 2,130,627 81,667 0 0 517
-------------------------------------------------------------
Instance Recovery Stats for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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 300 59 30198 356086 599160 941868 599160
E 300 61 34974 354022 573472 941868 573472
-------------------------------------------------------------
Buffer Pool Advisory for DB: ACC Instance: acc1 End Snap: 663
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate (default block size first)
Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 1,152 .1 137,736 24.81 7,508,791,398
D 2,304 .2 275,472 3.79 1,147,861,623
D 3,456 .3 413,208 2.21 669,465,396
D 4,608 .4 550,944 1.71 518,038,013
D 5,760 .5 688,680 1.47 443,833,037
D 6,912 .6 826,416 1.31 397,515,742
D 8,064 .7 964,152 1.20 364,167,719
D 9,216 .8 1,101,888 1.12 339,357,329
D 10,368 .9 1,239,624 1.05 319,144,538
D 11,456 1.0 1,369,708 1.00 302,599,589
D 11,520 1.0 1,377,360 1.00 301,611,835
D 12,672 1.1 1,515,096 0.94 283,430,617
D 13,824 1.2 1,652,832 0.89 269,032,661
D 14,976 1.3 1,790,568 0.86 258,737,646
D 16,128 1.4 1,928,304 0.83 251,540,362
D 17,280 1.5 2,066,040 0.81 244,794,246
D 18,432 1.6 2,203,776 0.79 239,379,123
D 19,584 1.7 2,341,512 0.77 234,151,246
D 20,736 1.8 2,479,248 0.75 228,385,698
D 21,888 1.9 2,616,984 0.74 223,000,404
D 23,040 2.0 2,754,720 0.72 217,054,219
-------------------------------------------------------------
Buffer wait Statistics for DB: ACC Instance: acc1 Snaps: 662 -663
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 1,607 18 11
undo header 84 3 32
undo block 14 0 0
2nd level bmb 3 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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
--------------- ---------------- -------------------------
54.0 20,003 17,050
%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 3,906 3,276 391.6 1.2 .3 100.0 .0 102,400
E 3,906 3,273 388.5 0.0 .0 100.0 .0 102,400
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: ACC Instance: acc1 Snaps: 662 -663
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 1,087,873 1,087,873 0 0
16K 32K 421 421 0 0
32K 64K 381 381 0 0
64K 128K 347 347 0 0
128K 256K 20 20 0 0
256K 512K 6 6 0 0
512K 1024K 44 44 0 0
1M 2M 38 38 0 0
2M 4M 154 154 0 0
4M 8M 1 1 0 0
8M 16M 10 10 0 0
16M 32M 34 34 0 0
32M 64M 3 3 0 0
256M 512M 26 0 16 10
-------------------------------------------------------------
PGA Memory Advisory for DB: ACC Instance: acc1 End Snap: 663
-> 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
---------- ------- ---------------- ---------------- -------- ----------
488 0.1 1,662,626.4 199,581.2 89.0 0
977 0.3 1,662,626.4 152,127.1 92.0 0
1,953 0.5 1,662,626.4 124,828.5 93.0 0
2,930 0.8 1,662,626.4 122,866.3 93.0 0
3,906 1.0 1,662,626.4 115,811.8 93.0 0
4,688 1.2 1,662,626.4 87,762.2 95.0 0
5,469 1.4 1,662,626.4 87,762.2 95.0 0
6,250 1.6 1,662,626.4 87,762.2 95.0 0
7,031 1.8 1,662,626.4 87,762.2 95.0 0
7,813 2.0 1,662,626.4 87,762.2 95.0 0
11,719 3.0 1,662,626.4 87,762.2 95.0 0
15,625 4.0 1,662,626.4 87,762.2 95.0 0
23,438 6.0 1,662,626.4 87,762.2 95.0 0
31,250 8.0 1,662,626.4 87,762.2 95.0 0
-------------------------------------------------------------
Enqueue activity for DB: ACC Instance: acc1 Snaps: 662 -663
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
US 90,955 90,955 0 78,127 .25 20
TX 198,355 198,375 1 118 21.69 3
SQ 2,920 2,920 0 14 3.93 0
TA 217 217 0 180 .24 0
PS 105 85 20 40 .38 0
FB 65 65 0 52 .27 0
CF 4,906 4,906 0 23 .52 0
TM 361,765 361,765 0 8 .88 0
HW 942 942 0 15 .20 0
TD 9 9 0 9 .11 0
DV 6 6 0 3 .33 0
PI 20 20 0 5 .00 0
DR 2 2 0 2 .00 0
CU 83,636 83,636 0 1 .00 0
WL 2 1 1 1 .00 0
TT 3,667 3,667 0 1 .00 0
-------------------------------------------------------------
Rollback Segment Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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 219.0 0.00 0 0 0 0
1 17,023.0 0.00 933,686 3 1 0
2 11,802.0 0.00 664,422 3 0 0
3 19,299.0 0.00 1,082,398 4 1 0
4 28,373.0 0.00 1,598,206 4 0 0
5 43,935.0 0.00 2,493,972 5 0 1
6 28,003.0 0.00 1,570,420 4 0 0
7 6,236.0 0.00 354,342 0 0 0
8 21,108.0 0.00 1,182,058 4 0 0
9 19,602.0 0.00 1,173,306 4 1 0
10 15,694.0 0.00 901,012 1 0 0
42 23,809.0 0.01 1,341,238 4 1 0
43 284.0 0.00 0 0 0 0
44 20,311.0 0.00 1,166,572 3 0 0
45 17,541.0 0.01 985,840 3 1 0
46 24,077.0 0.00 1,367,788 2 0 0
47 32,653.0 0.00 1,851,280 5 1 0
48 63,607.0 0.00 0 0 0 0
49 16,791.0 0.00 935,740 1 0 0
50 35,946.0 0.00 2,072,188 5 0 0
51 39,748.0 0.00 2,311,912 5 0 0
52 219.0 0.00 0 0 0 0
53 28,570.0 0.00 1,580,434 4 0 0
54 24,097.0 0.00 2,176,682 4 0 1
55 13,207.0 0.00 741,000 2 0 0
56 18,424.0 0.00 1,024,594 3 0 0
57 13,974.0 0.00 772,106 1 0 0
58 21,543.0 0.00 1,257,498 1 0 0
59 27,374.0 0.00 1,517,934 4 0 1
60 220.0 0.00 0 0 0 0
61 22,810.0 0.00 1,263,274 4 0 0
62 21,925.0 0.00 1,211,334 4 1 0
63 17,543.0 0.00 971,962 4 1 0
64 395.0 0.00 0 0 0 0
65 14,901.0 0.00 863,884 3 0 0
66 219.0 0.00 0 0 0 0
67 219.0 0.00 0 0 0 0
68 33,397.0 0.00 1,865,440 2 0 0
69 19,628.0 0.00 1,091,666 3 1 0
70 21,110.0 0.00 1,176,720 0 0 0
71 11,703.0 0.00 652,028 1 0 0
72 17,701.0 0.00 1,023,610 3 0 0
73 219.0 0.00 0 0 0 0
74 23,275.0 0.00 1,325,586 2 0 0
75 21,351.0 0.01 1,363,074 2 1 0
76 18,799.0 0.00 1,051,098 4 1 0
85 7,191.0 0.00 417,248 3 0 0
86 22,563.0 0.00 1,296,076 2 0 1
87 17,768.0 0.00 976,822 1 0 0
88 23,778.0 0.00 1,389,746 4 1 0
89 21,181.0 0.00 1,213,856 1 0 0
90 24,495.0 0.00 1,371,296 3 0 0
Rollback Segment Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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
------ -------------- ------- --------------- -------- -------- --------
92 22,097.0 0.00 1,226,216 4 0 0
93 13,157.0 0.00 711,788 1 0 0
94 17,320.0 0.00 946,748 2 0 0
95 22,092.0 0.00 1,276,992 3 0 0
100 19,146.0 0.00 1,073,024 3 0 0
114 34,797.0 0.00 1,991,296 4 1 0
115 28,175.0 0.00 1,582,948 4 1 0
116 22,908.0 0.00 1,263,718 1 0 0
117 21,558.0 0.00 1,232,118 4 1 0
118 17,510.0 0.00 962,316 4 0 0
273 9,209.0 0.00 531,006 0 0 0
274 23,927.0 0.00 1,351,076 4 0 1
275 20,168.0 0.00 1,103,442 3 0 0
276 25,031.0 0.00 1,390,008 4 0 0
277 34,835.0 0.00 2,008,192 5 1 0
278 21,967.0 0.00 1,242,104 4 1 0
279 21,618.0 0.00 1,165,416 4 2 1
280 17,887.0 0.00 993,588 3 1 0
281 20,499.0 0.00 1,132,682 2 1 0
282 19,323.0 0.00 1,100,046 4 0 0
283 19,045.0 0.00 1,069,506 2 0 0
284 3,336.0 0.00 179,474 3 1 0
285 11,835.0 0.00 647,784 1 0 0
286 18,859.0 0.00 1,057,332 3 1 0
287 15,477.0 0.00 876,710 0 0 0
288 17,234.0 0.00 1,014,950 1 0 0
289 18,046.0 0.00 1,052,234 4 0 0
290 23,627.0 0.00 1,331,022 0 0 0
291 22,857.0 0.00 1,291,310 0 0 0
292 17,653.0 0.00 993,270 4 1 0
293 19,934.0 0.00 1,104,388 3 0 0
294 26,129.0 0.00 1,463,444 4 1 0
295 19,190.0 0.00 1,086,536 0 0 0
296 23,762.0 0.00 1,369,764 0 0 0
297 46,410.0 0.00 2,614,648 5 1 2
298 7,233.0 0.00 401,776 0 0 0
299 39,262.0 0.00 2,245,194 5 0 0
300 43,001.0 0.00 2,413,880 6 0 0
301 28,266.0 0.00 1,600,022 5 0 1
302 23,733.0 0.00 1,290,494 0 0 0
303 23,214.0 0.00 1,368,316 3 1 0
304 18,722.0 0.00 1,057,692 3 0 0
305 24,626.0 0.00 1,428,744 4 1 0
306 21,163.0 0.00 1,159,306 4 0 0
307 7,227.0 0.00 397,948 0 0 0
308 40,438.0 0.00 2,321,688 6 0 1
309 21,789.0 0.00 1,307,002 3 0 0
310 18,758.0 0.00 1,026,570 4 0 0
311 8,217.0 0.00 442,548 0 0 0
312 20,050.0 0.00 1,107,876 4 0 0
313 19,296.0 0.00 1,071,740 3 0 0
314 28,421.0 0.00 1,618,614 5 0 1
Rollback Segment Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->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
------ -------------- ------- --------------- -------- -------- --------
315 28,323.0 0.00 1,599,664 2 0 0
316 345.0 0.00 0 0 0 0
317 20,432.0 0.00 1,147,134 1 0 0
318 19,217.0 0.00 1,116,488 3 0 0
319 18,312.0 0.00 3,608,940 8 1 5
320 17,533.0 0.00 1,002,732 3 0 0
321 24,032.0 0.00 1,340,734 2 0 1
322 24,762.0 0.00 1,406,962 4 0 1
323 19,521.0 0.00 1,129,704 3 0 0
324 18,964.0 0.00 1,084,800 3 0 0
325 26,993.0 0.00 1,651,252 2 0 0
326 32,035.0 0.00 1,794,432 2 0 0
327 29,266.0 0.00 1,716,136 6 1 3
328 43,180.0 0.00 2,463,858 6 1 0
329 39,391.0 0.00 2,238,316 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: ACC Instance: acc1 Snaps: 662 -663
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 17,022 385,024
1 2,220,032 617,901 1,277,288,448
2 2,220,032 602,773 1,447,157,760
3 2,220,032 633,365 1,148,313,600
4 3,268,608 661,163 964,812,800
5 4,317,184 709,893 901,898,240
6 3,268,608 611,040 721,608,704
7 2,220,032 685,096 541,188,096
8 2,220,032 606,513 218,226,688
9 2,220,032 1,662,959 562,159,616
10 2,220,032 2,323,125 370,270,208
42 2,220,032 606,476 117,628,928
43 2,220,032 626,765 184,672,256
44 2,220,032 636,393 117,563,392
45 2,220,032 667,768 100,851,712
46 3,268,608 743,812 143,777,792
47 3,268,608 721,046 150,069,248
48 1,171,456 0 13,754,368
49 4,317,184 699,974 167,895,040
50 3,268,608 631,270 1,676,795,904
51 3,268,608 612,871 294,772,736
52 5,365,760 690,174 24,240,128
53 3,268,608 625,016 209,838,080
54 3,268,608 597,562 184,672,256
55 3,268,608 725,016 302,112,768
56 2,220,032 577,405 193,060,864
57 3,268,608 675,480 248,635,392
58 2,220,032 669,477 335,667,200
59 3,268,608 637,298 251,781,120
60 8,511,488 4,147,647 223,469,568
61 2,220,032 561,638 237,101,056
62 3,268,608 699,493 184,672,256
63 2,220,032 688,516 361,881,600
64 3,268,608 620,577 503,504,896
65 2,220,032 580,441 511,893,504
66 3,268,608 752,682 411,164,672
67 4,317,184 836,857 369,221,632
68 3,268,608 653,943 175,235,072
69 2,220,032 609,249 132,243,456
70 9,560,064 40,227,349 239,198,208
71 2,220,032 686,563 320,987,136
72 2,220,032 600,748 144,826,368
73 2,220,032 618,498 51,503,104
74 3,268,608 714,615 48,357,376
75 2,220,032 605,670 9,560,064
76 2,220,032 630,711 7,462,912
85 3,268,608 548,608 7,462,912
86 3,268,608 600,180 4,317,184
87 2,220,032 487,808 3,268,608
88 2,220,032 532,892 5,365,760
89 2,220,032 551,484 6,414,336
90 2,220,032 476,652 4,317,184
92 2,220,032 573,158 6,414,336
93 3,268,608 677,780 7,462,912
94 4,317,184 656,904 10,608,640
Rollback Segment Storage for DB: ACC Instance: acc1 Snaps: 662 -663
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
95 2,220,032 480,837 5,365,760
100 2,220,032 398,849 4,317,184
114 3,268,608 737,950 9,560,064
115 2,220,032 562,998 6,414,336
116 3,268,608 595,283 7,462,912
117 2,220,032 522,719 4,317,184
118 2,220,032 434,773 3,268,608
273 2,220,032 469,057 3,268,608
274 3,268,608 554,219 5,365,760
275 2,220,032 447,612 3,268,608
276 2,220,032 462,820 5,431,296
277 3,268,608 702,732 8,511,488
278 2,220,032 613,867 6,414,336
279 2,220,032 749,111 12,705,792
280 2,220,032 488,957 14,802,944
281 2,220,032 505,419 5,365,760
282 2,220,032 480,837 5,365,760
283 3,268,608 668,276 4,317,184
284 2,220,032 497,184 4,317,184
285 2,220,032 552,323 5,365,760
286 2,220,032 446,569 3,268,608
287 9,560,064 726,928 9,560,064
288 9,560,064 734,614 9,560,064
289 2,220,032 507,708 3,268,608
290 9,560,064 734,614 9,560,064
291 9,560,064 104,857 9,560,064
292 2,220,032 381,536 3,268,608
293 2,220,032 286,652 2,220,032
294 2,220,032 424,506 13,754,368
295 8,511,488 11,714 8,511,488
296 8,511,488 11,714 8,511,488
297 4,317,184 570,427 5,365,760
298 3,268,608 339,430 3,268,608
299 3,268,608 505,419 10,608,640
300 3,268,608 511,304 11,657,216
301 3,268,608 384,717 3,268,608
302 8,511,488 11,714 8,511,488
303 2,220,032 344,093 3,268,608
304 2,220,032 236,500 3,268,608
305 2,220,032 414,541 10,608,640
306 2,220,032 286,652 10,608,640
307 2,220,032 236,500 3,268,608
308 3,268,608 481,432 11,657,216
309 2,220,032 291,695 3,268,608
310 3,268,608 373,917 11,657,216
311 2,220,032 180,774 3,268,608
312 2,220,032 286,652 3,268,608
313 2,220,032 242,103 3,268,608
314 3,268,608 384,717 3,268,608
315 3,268,608 436,726 3,334,144
316 3,268,608 199,228 3,268,608
317 2,220,032 267,554 3,268,608
318 2,220,032 242,103 3,268,608
319 2,220,032 873,450 9,560,064
320 2,220,032 236,500 2,220,032
Rollback Segment Storage for DB: ACC Instance: acc1 Snaps: 662 -663
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
321 3,268,608 339,430 3,268,608
322 3,268,608 322,750 9,560,064
323 2,220,032 286,652 2,220,032
324 2,220,032 286,652 2,220,032
325 3,268,608 390,793 3,268,608
326 3,268,608 477,944 4,317,184
327 3,334,144 281,416 3,334,144
328 3,268,608 542,858 4,317,184
329 8,511,488 0 8,511,488
-------------------------------------------------------------
Undo Segment Summary for DB: ACC Instance: acc1 Snaps: 662 -663
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - 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 26,381 ########## 462 38 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: ACC Instance: acc1 Snaps: 662 -663
-> 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
------------ ------------ -------- ------- -------- ------- ------ -------------
06-Mar 13:23 4,721 ######## 11 29 0 0 0/0/0/0/0/0
06-Mar 13:13 5,882 ######## 193 31 0 0 0/0/0/0/0/0
06-Mar 13:03 4,583 ######## 462 30 0 0 0/0/0/0/0/0
06-Mar 12:53 4,774 ######## 128 38 0 0 0/0/0/0/0/0
06-Mar 12:43 6,421 ######## 24 32 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: ACC Instance: acc1 Snaps: 662 -663
->"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 360,768 0.0 0 0
FAL request queue 59 0.0 0 0
FIB s.o chain latch 14 0.0 0 0
FOB s.o list latch 204,159 0.1 0.0 0 0
KCL bast context freelis 47,604 0.0 0.0 0 0
KCL freelist parent latc 5,799,132 0.1 0.0 0 0
KCL gc element parent la 10,865,080 0.0 0.0 0 1,327,953 0.0
KCL name table parent la 2,308,442 0.0 0.0 0 1,315,382 0.0
KJC message pool free li 1,254,751 0.1 0.0 0 277,553 0.0
KJCT flow control latch 2,730,192 0.0 0.0 0 261,373 0.0
SQL memory manager latch 1 0.0 0 969 0.0
SQL memory manager worka 284,256 0.0 0.0 0 0
active checkpoint queue 12,494 0.3 0.0 0 0
alert log latch 7 0.0 0 0
archive control 101 0.0 0 0
archive process latch 83 0.0 0 0
cache buffer handles 63,660 1.3 0.0 0 0
cache buffers chains 438,012,502 0.0 0.0 1 6,574,381 0.0
cache buffers lru chain 96,377 0.0 0.0 0 4,330,348 0.0
channel handle pool latc 82,749 0.6 0.0 0 0
channel operations paren 193,242 0.3 0.0 0 0
checkpoint queue latch 2,135,208 0.0 0.0 0 110,051 0.0
child cursor hash table 392,117 0.0 0.0 0 0
commit callback allocati 90 0.0 0 0
dml lock allocation 723,542 0.1 0.0 0 0
dummy allocation 82,186 0.0 0.0 0 0
enqueue hash chains 26,784,668 0.0 0.0 0 0
enqueues 13,100,898 0.7 0.0 0 0
error message lists 42 2.4 0.0 0 0
event group latch 41,076 0.0 0 0
gcs opaque info freelist 567,210 1.6 0.0 0 0
gcs resource freelist 3,583,975 0.9 0.0 0 0
gcs resource hash 7,244,767 0.1 0.0 0 294 0.0
gcs resource scan list 588 0.0 0 0
gcs shadows freelist 2,043,992 1.0 0.0 0 0
ges caches resource list 1,497,443 0.2 0.0 0 358,633 0.3
ges deadlock list 222,739 0.0 0.0 0 12 0.0
ges domain table 1,313,443 0.0 0.0 0 0
ges enqueue table freeli 4,501,896 0.0 0.0 0 0
ges group parent 1,313,399 0.1 0.0 0 0
ges group table 1,313,637 0.0 0.0 0 0
ges process hash list 202,315 0.0 0 0
ges process parent latch 14,208,756 0.0 0.0 0 0
ges process table freeli 82,161 0.3 0.0 0 0
ges resource hash list 7,191,279 0.1 0.0 0 302,531 0.0
ges resource scan list 264 0.0 0 0
ges resource table freel 2,046,532 0.0 0.0 0 0
ges timeout list 3,216 0.0 0 815 0.0
global tx hash mapping 6,402,212 0.0 0.0 0 0
hash table column usage 1,578 0.0 0 62,007 0.0
Latch Activity for DB: ACC Instance: acc1 Snaps: 662 -663
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
job_queue_processes para 48 0.0 0 0
ksxp tid allocation 1,229,345 0.0 0.0 0 0
ktm global data 281 0.0 0 0
lgwr LWN SCN 361,079 0.0 0.0 0 0
library cache 39,824,360 0.5 0.0 0 53,228 2.9
library cache load lock 2,808 0.0 0 0
library cache pin 22,265,196 0.0 0.0 0 0
library cache pin alloca 9,268,665 0.0 0.0 0 0
list of block allocation 15,462 0.0 0 0
loader state object free 422 0.0 0 0
longop free list parent 97 0.0 0 34 0.0
message pool operations 846 0.0 0 0
messages 1,318,209 0.1 0.0 0 0
mostly latch-free SCN 362,233 0.1 0.0 0 0
multiblock read objects 342,646 0.0 0.0 0 0
name-service memory obje 46,716 0.0 0 0
name-service namespace b 40,058 0.0 0 0
name-service namespace o 5 0.0 0 0
name-service pending que 24,206 0.0 0 0
name-service request 80 0.0 0 0
name-service request que 69,665 0.0 0.0 0 0
ncodef allocation latch 56 0.0 0 0
object stats modificatio 119 0.0 0 0
parallel query alloc buf 1,078 9.2 0.1 0 0
parallel query stats 16 31.3 0.0 0 0
parallel txn reco latch 90,923 0.0 0 0
post/wait queue 736,710 0.0 0.0 0 384,027 0.1
process allocation 98,799 0.5 0.0 0 40,919 0.4
process group creation 82,161 0.4 0.0 0 0
process queue 89 0.0 0 0
process queue reference 30,933 0.0 0.0 0 2,350 8.4
query server freelists 191 3.1 0.0 0 0
query server process 10 0.0 0 15 0.0
redo allocation 2,224,359 0.2 0.0 0 0
redo copy 0 0 1,503,872 0.1
redo writing 1,096,825 0.0 0.0 0 0
row cache enqueue latch 21,367,787 0.2 0.0 0 0
row cache objects 26,149,512 0.6 0.0 0 191 0.0
sequence cache 192,533 0.0 0.0 0 0
session allocation 946,237 0.8 0.0 0 0
session idle bit 14,815,495 0.0 0.0 0 0
session switching 56 0.0 0 0
session timer 1,179 0.0 0 0
shared pool 19,357,778 0.1 0.0 0 0
sim partition latch 0 0 513 0.0
simulator hash latch 14,256,922 0.0 0.0 0 0
simulator lru latch 40,332 0.0 0.0 0 243,201 0.1
sort extent pool 6,014 0.0 0 0
temporary table state ob 344 0.0 0 0
transaction allocation 82,416 0.0 0 0
Latch Activity for DB: ACC Instance: acc1 Snaps: 662 -663
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
transaction branch alloc 5,999,526 0.1 0.0 0 0
undo global data 2,120,047 0.0 0.0 0 0
user lock 164,418 1.3 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: ACC Instance: acc1 Snaps: 662 -663
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
library cache 39,824,360 180,210 85 180126/83/1/
0/0
cache buffers chains 438,012,502 172,123 475 171751/281/9
7/0/0
row cache objects 26,149,512 154,316 1 154315/1/0/0
/0
enqueues 13,100,898 87,797 26 87771/26/0/0
/0
row cache enqueue latch 21,367,787 39,527 3 39524/3/0/0/
0
shared pool 19,357,778 21,327 465 20864/461/2/
0/0
gcs shadows freelist 2,043,992 20,568 2 20566/2/0/0/
0
enqueue hash chains 26,784,668 12,252 2 12250/2/0/0/
0
ges resource hash list 7,191,279 9,408 177 9231/177/0/0
/0
gcs opaque info freelist 567,210 9,195 4 9191/4/0/0/0
session allocation 946,237 7,377 2 7375/2/0/0/0
gcs resource hash 7,244,767 5,474 2 5473/0/1/0/0
KCL freelist parent latch 5,799,132 3,491 1 3490/1/0/0/0
user lock 164,418 2,060 1 2059/1/0/0/0
KJC message pool free list 1,254,751 910 1 909/1/0/0/0
process allocation 98,799 462 2 460/2/0/0/0
session idle bit 14,815,495 243 2 242/0/1/0/0
parallel query alloc buffe 1,078 99 8 91/8/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: ACC Instance: acc1 Snaps: 662 -663
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
KCL freelist parent latc kclfget 0 1 0
KJC message pool free li kjcspfmbq: free vector of 0 1 0
cache buffers chains kcbgtcr: kslbegin excl 0 205 234
cache buffers chains kcbgtcr: fast path 0 113 42
cache buffers chains kcbrls: kslbegin 0 78 132
cache buffers chains kcbzwb 0 43 28
cache buffers chains kcbchg: kslbegin: bufs not 0 22 16
cache buffers chains kcbzgb: scan from tail. no 0 6 0
cache buffers chains kcbbxsv 0 2 0
cache buffers chains kcbgtcr 0 2 0
cache buffers chains kcbzib: multi-block read: 0 2 0
cache buffers chains kcbgcur: kslbegin 0 1 2
cache buffers chains kclbla 0 1 0
cost function kzulrl 0 1 0
enqueue hash chains ksqcnl 0 1 0
enqueue hash chains ksqrcl 0 1 0
enqueues ksqgel: create enqueue 0 13 21
enqueues ksqdel 0 11 4
enqueues ksqies 0 2 1
gcs opaque info freelist kjbrcvt save opaque info 0 2 4
gcs opaque info freelist kjbrgrant free opaque info 0 2 0
gcs resource hash kjbcropen 0 2 0
gcs shadows freelist kjblalc add lock 0 2 2
ges resource hash list kjlmfnd: search for lockp 0 133 0
ges resource hash list kjakcai: search for resp b 0 43 0
ges resource hash list kjrmas1: lookup master nod 0 1 0
library cache kglupc: child 0 22 13
library cache kgllkdl: child: cleanup 0 11 1
library cache kglpnc: child 0 8 43
library cache kgllkdl: child: multiinsta 0 4 0
library cache kglpndl: child: after proc 0 3 0
library cache kglpndl: child: before pro 0 3 1
library cache kgldti: 2child 0 2 1
library cache kglobpn: child: 0 2 2
library cache kglget: child: KGLDSBYD 0 1 10
library cache kglhdgc: child: 0 1 0
library cache kglhdgn: child: 0 1 8
library cache kglpin: child: heap proces 0 1 0
library cache kglic 0 1 0
parallel query alloc buf kxfpbalo 0 7 7
parallel query alloc buf kxfpnfy 0 1 1
process allocation ksudlp 45 2 2
row cache enqueue latch kqreqd 0 2 0
row cache enqueue latch kqreqa 0 1 3
row cache objects kqrpfl: not dirty 0 1 0
session allocation ksuxds: KSUSFCLC not set 0 2 0
session idle bit ksupuc: set busy 0 2 2
shared pool kghalo 0 148 27
shared pool kghalp 0 124 0
shared pool kghfrunp: alloc: wait 0 120 0
shared pool kghupr1 0 72 430
shared pool kghfrunp: clatch: wait 0 23 3
shared pool kghfrunp: clatch: nowait 0 2 0
Latch Miss Sources for DB: ACC Instance: acc1 Snaps: 662 -663
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
shared pool kghfen: not perm alloc cla 0 1 0
-------------------------------------------------------------
Dictionary Cache Stats for DB: ACC Instance: acc1 Snaps: 662 -663
->"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_database_links 3,661,667 0.0 0 0 9
dc_histogram_defs 1,203,348 0.1 0 0 58,388
dc_object_ids 230,038 0.0 0 0 16,595
dc_objects 410,054 0.1 0 0 45,225
dc_profiles 41,070 0.0 0 0 1
dc_rollback_segments 190,760 0.0 0 0 421
dc_segments 88,639 0.2 0 5 13,804
dc_sequences 2,907 22.6 0 2,907 4
dc_tablespace_quotas 17 17.6 0 5 11
dc_tablespaces 11,781 0.0 0 0 103
dc_user_grants 4,524,539 0.0 0 0 22
dc_usernames 168,098 0.0 0 0 23
dc_users 4,884,017 0.0 0 0 37
-------------------------------------------------------------
GES GES GES
Cache Requests Conflicts Releases
------------------------- ------------ ------------ ------------
dc_database_links 0 0 0
dc_histogram_defs 634 0 314
dc_object_ids 98 0 0
dc_objects 354 0 14
dc_profiles 0 0 0
dc_rollback_segments 2 2 0
dc_segments 190 1 12
dc_sequences 5,814 656 0
dc_tablespace_quotas 12 0 0
dc_tablespaces 0 0 0
dc_user_grants 0 0 0
dc_usernames 0 0 1
dc_users 0 0 0
-------------------------------------------------------------
Library Cache Activity for DB: ACC Instance: acc1 Snaps: 662 -663
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 428 0.0 428 0.0 0 0
CLUSTER 23 0.0 23 0.0 0 0
INDEX 39,605 0.0 39,605 0.0 0 0
SQL AREA 1,380,354 1.2 9,233,327 1.1 65,717 0
TABLE/PROCEDURE 454,104 0.1 2,870,585 0.1 721 0
TRIGGER 82,146 0.0 82,146 0.0 0 0
-------------------------------------------------------------
GES Lock GES Pin GES Pin GES Inval GES Invali-
Namespace Requests Requests Releases Requests dations
--------------- ------------ ------------ ------------ ----------- -----------
BODY 0 0 0 0 0
CLUSTER 14 0 1 0 0
INDEX 39,605 0 0 0 0
SQL AREA 0 0 0 0 0
TABLE/PROCEDURE 1,490,854 372 100 208 0
TRIGGER 0 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory for DB: ACC Instance: acc1 End Snap: 663
-> 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
----------- ----- ---------- ------------ ------------ ------- ---------------
2,336 .6 2,025 241,181 3,222,396 1.0 564,606,961
2,736 .7 2,424 277,105 3,222,637 1.0 564,695,249
3,136 .8 2,823 312,263 3,222,805 1.0 564,772,480
3,536 .9 3,222 348,038 3,222,962 1.0 564,869,694
3,936 1.0 3,621 382,952 3,223,074 1.0 564,928,243
4,336 1.1 4,020 418,426 3,223,176 1.0 564,978,305
4,736 1.2 4,419 454,452 3,223,255 1.0 565,024,625
5,136 1.3 4,818 486,663 3,223,336 1.0 565,077,703
5,536 1.4 5,217 524,756 3,223,383 1.0 565,107,251
5,936 1.5 5,616 564,614 3,223,423 1.0 565,132,805
6,336 1.6 6,015 605,311 3,223,465 1.0 565,170,834
6,736 1.7 6,414 652,099 3,223,499 1.0 565,213,925
7,136 1.8 6,813 716,677 3,223,520 1.0 565,227,590
7,536 1.9 7,212 780,029 3,223,543 1.0 565,252,363
7,936 2.0 7,642 848,804 3,223,564 1.0 565,263,975
-------------------------------------------------------------
SGA Memory Summary for DB: ACC Instance: acc1 Snaps: 662 -663
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 12,012,486,656
Fixed Size 770,488
Redo Buffers 8,466,432
Variable Size 8,002,732,032
----------------
sum 20,024,455,608
-------------------------------------------------------------
SGA breakdown difference for DB: ACC Instance: acc1 Snaps: 662 -663
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 218,103,808 218,103,808 0.00
large free memory 117,440,512 117,440,512 0.00
shared 1M buffer 528,384 528,384 0.00
shared Checkpoint queue 31,472,640 31,472,640 0.00
shared DML lock 3,055,520 3,055,520 0.00
shared FileIdentificatonBlock 1,153,752 1,153,752 0.00
shared FileOpenBlock 16,104,056 16,104,056 0.00
shared KCL bast context freelist 672,000 672,000 0.00
shared KCL extra lock elements 2,592,000 2,592,000 0.00
shared KCL lock context 728,400 728,400 0.00
shared KCL name table 8,218,776 8,218,776 0.00
shared KGK heap 33,368 33,368 0.00
shared KGLS heap 90,510,816 90,775,760 0.29
shared KQR L PO 62,980,456 63,332,400 0.56
shared KQR M PO 37,377,112 37,633,144 0.68
shared KQR M SO 6,656 6,656 0.00
shared KQR S SO 5,632 5,632 0.00
shared KQR X PO 28,360 28,360 0.00
shared KSXR large reply queue 167,624 167,624 0.00
shared KSXR pending messages que 853,952 853,952 0.00
shared KSXR receive buffers 1,034,000 1,034,000 0.00
shared MTTR advisory 991,656 991,656 0.00
shared PL/SQL DIANA 1,534,640 1,492,480 -2.75
shared PL/SQL MPCODE 1,729,080 1,941,152 12.27
shared PL/SQL PPCODE 12,648 12,648 0.00
shared PLS non-lib hp 3,512 3,512 0.00
shared PX subheap 56,160 56,160 0.00
shared Temporary Tables State Ob 775,488 775,488 0.00
shared UNDO INFO HASH 116,320 116,320 0.00
shared UNDO INFO SEGMENTED ARRAY 649,856 649,856 0.00
shared branch 1,180,120 1,180,120 0.00
shared channel handle 780,672 780,672 0.00
shared character set object 124,152 124,152 0.00
shared constraints 824,960 824,960 0.00
shared db_block_hash_buckets 48,680,368 48,680,368 0.00
shared db_files 218,504 218,504 0.00
shared db_handles 3,480,000 3,480,000 0.00
shared dictionary cache 4,274,432 4,274,432 0.00
shared enqueue 6,180,360 6,180,360 0.00
shared enqueue resources 2,027,280 2,027,280 0.00
shared enqueue_hash 318,960 318,960 0.00
shared errors 57,424 57,424 0.00
shared event statistics per sess 37,491,920 37,491,920 0.00
shared fixed allocation callback 1,880 1,880 0.00
shared free memory 319,621,704 304,589,344 -4.70
shared gcs resource hash table 2,097,152 2,097,152 0.00
shared gcs resources 220,003,128 220,003,128 0.00
shared gcs shadows 146,161,624 146,161,624 0.00
shared ges big msg buffers 13,058,088 13,058,088 0.00
shared ges enqueues 200,977,944 200,977,944 0.00
shared ges process array 3,841,280 3,841,280 0.00
shared ges regular msg buffers 3,040,248 3,040,248 0.00
shared ges reserved msg buffers 2,096,008 2,096,008 0.00
shared ges resource hash table 4,325,376 4,325,376 0.00
SGA breakdown difference for DB: ACC Instance: acc1 Snaps: 662 -663
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
shared ges resources 134,617,144 134,617,144 0.00
shared ges shared global area 199,168 199,168 0.00
shared joxs heap init 888 888 0.00
shared kglsim hash table bkts 1,396,736 1,396,736 0.00
shared kglsim heap 11,573,120 11,573,120 0.00
shared kglsim sga 137,544 137,544 0.00
shared krvxrr 253,056 253,056 0.00
shared ksm_file2sga region 370,496 370,496 0.00
shared ksuloi: long op statistic 124,000 124,000 0.00
shared ktlbk state objects 1,948,360 1,948,360 0.00
shared library cache 798,094,696 802,762,272 0.58
shared log_buffer 264,224 264,224 0.00
shared miscellaneous 235,749,336 235,913,016 0.07
shared parameters 108,432 135,688 25.14
shared partitioning d 239,936 239,936 0.00
shared processes 3,936,000 3,936,000 0.00
shared qmps connections 1,454,200 1,454,200 0.00
shared replication session stats 1,004,720 1,004,720 0.00
shared session param values 6,276,096 6,165,504 -1.76
shared sessions 8,936,720 8,936,720 0.00
shared sim memory hea 11,511,408 11,511,408 0.00
shared sql area 2,288,285,384 2,297,526,512 0.40
shared table definiti 117,096 117,104 0.01
shared temporary tabl 2,144 2,616 22.01
shared trace buffer 1,327,104 1,327,104 0.00
shared transaction 6,096,584 6,096,584 0.00
shared trigger defini 880 880 0.00
shared trigger inform 1,448 1,448 0.00
shared trigger source 408 408 0.00
buffer_cache 12,012,486,656 12,012,486,656 0.00
fixed_sga 770,488 770,488 0.00
log_buffer 8,455,168 8,455,168 0.00
-------------------------------------------------------------
Resource Limit Stats for DB: ACC Instance: acc1 End Snap: 663
-> only rows with Current or Maximum Utilization > 80% of Limit are shown
-> ordered by resource name
Current Maximum Initial
Resource Name Utilization Utilization Allocation Limit
------------------------------ ------------ ------------ ---------- ----------
gcs_resources 1,094,029 1,303,225 1506678 1506678
parallel_max_servers 0 6 6 6
-------------------------------------------------------------
init.ora Parameters for DB: ACC Instance: acc1 Snaps: 662 -663
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_sys_operations TRUE
audit_trail DB
background_dump_dest /oracle9/app/admin/acc/bdump
cluster_database TRUE
cluster_database_instances 2
cluster_interconnects 100.100.100.21
compatible 9.2.0.0.0
control_files /dev/racc_ctl01lv, /dev/racc_ctl0
core_dump_dest /oracle9/app/admin/acc/cdump
db_block_size 8192
db_cache_size 12012486656
db_domain
db_file_multiblock_read_count 16
db_files 1024
db_name acc
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name acc1
instance_number 1
java_pool_size 218103808
job_queue_processes 10
large_pool_size 117440512
log_archive_dest_1 LOCATION=/arch01
log_archive_dest_2
log_archive_format %t_%s.dbf
log_archive_start TRUE
log_buffer 8192000
open_cursors 1000
open_links 16
pga_aggregate_target 4096000000
processes 3000
query_rewrite_enabled TRUE
query_rewrite_integrity trusted
remote_login_passwordfile EXCLUSIVE
sga_max_size 20024455608
shared_pool_size 4110417920
sort_area_size 524288
spfile /dev/racc_spfilelv
star_transformation_enabled FALSE
thread 1
timed_statistics TRUE
undo_management AUTO
undo_retention 1500
undo_tablespace UNDOTBS1
user_dump_dest /oracle9/app/admin/acc/udump
-------------------------------------------------------------
End of Report
|
|
|
|
|
|
| Re: How to tuning our oracle product server? [message #222952 is a reply to message #222888] |
Wed, 07 March 2007 01:06  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
IMHO you need a good performance expert for 2-3 weeks.
As I see it almost all application SQL statements need tuning and the application needs re-design.
For example:
84,527 3 28,175.7 3.7 4.16 13.47 3148540390
Module: oracle@CRM1 (TNS V1-V3)
SELECT "A1"."SAVE_CODE","A1"."LOGIN_ACCEPT","A1"."CONTRACT_NO","
A1"."TOTAL_DATE","A1"."TOTAL_FEE" FROM "DBACCADM"."DSAVEFAVMSG"
"A1" WHERE "A1"."PHONE_NO"='13335637695'
1. The statement does NOT use bind variables, so hard parse is performed for each different phone_no.
2. The number of executions is 3,but 84,527 physical IO's are performed ( 28,175.7 IO's per execution). Do you have an index defined for DBACCADM.DSAVEFAVMSG table on PHONE_NO column?
HTH.
Michael
|
|
|
|
|
|
| Re: How to tuning our oracle product server? [message #224287 is a reply to message #223811] |
Tue, 13 March 2007 14:01 |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
Another SQL which stands out...
select to_number(item_code) into :b0 from sfeecodeqry where (qu
ery_code='1521' and fee_code=:b1)
16,141,356 buffer gets for 128,087 executions or 126 gets/exec.
126 gets is very high for a statement that should be returning one value from one table (of course sfeecodeqry could be some hideously complex view but I'm assuming it is not). Make sure there is an index on column fee_code, or maybe a composite index on fee_code and query_code. If the combination of fee_code and query_code is unique, then instead of the index you could try a unique constraint which helps the optimizer choose the most efficient plan, especially if your stats are out of date.
JR
|
|
|
|
| Re: How to tuning our oracle product server? [message #224302 is a reply to message #222888] |
Tue, 13 March 2007 16:15 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi, JR.
As I already wrote:
1. IMHO almost all SQL statements need tuning.
2. The application does NOT use bind variables, performing hard parses and burning CPU.
3. It looks like OLTP system, but parallel queries are used.
I don't like it in OLTP (my personal opinion).
And it's just for starters.
Michael
|
|
|
|
Goto Forum:
Current Time: Thu May 16 18:34:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
