- 论坛徽章:
- 0
|
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ora1 633409748 ora1 1 9.2.0.6.0 NO sun1
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 4 06-1月 -05 09:59:04 18 18.2
End Snap: 5 06-1月 -05 10:48:00 18 17.3
Elapsed: 48.93 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,504M Std Block Size: 8K
Shared Pool Size: 672M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 355,818.07 505.25
Logical reads: 38,180.38 54.21
Block changes: 2,825.17 4.01
Physical reads: 262.65 0.37
Physical writes: 154.88 0.22
User calls: 3,594.81 5.10
Parses: 1,797.99 2.55
Hard parses: 0.00 0.00
Sorts: 0.25 0.00
Logons: 0.00 0.00
Executes: 15,010.99 21.32
Transactions: 704.24
% Blocks changed per Read: 7.40 Recursive Call %: 81.42
Rollback per transaction %: 0.00 Rows per Sort: 12.89
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.31 In-memory Sort %: 100.00
Library Hit %: 100.03 Soft Parse %: 100.00
Execute to Parse %: 88.02 Latch Hit %: 97.83
Parse CPU to Parse Elapsd %: 95.76 % Non-Parse CPU: 95.83
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 9.02 9.02
% SQL with executions>;1: 77.88 79.29
% Memory for SQL w/exec>;1: 71.70 73.82
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 9,324 58.65
log file sync 2,067,581 2,318 14.58
db file sequential read 770,191 2,111 13.28
log file parallel write 3,395,234 1,663 10.46
db file parallel write 9,697 384 2.42
-------------------------------------------------------------
Wait Events for DB: ora1 Instance: ora1 Snaps: 4 -5
->; s - second
->; cs - centisecond - 100th of a second
->; ms - millisecond - 1000th of a second
->; us - microsecond - 1000000th of a second
->; ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 2,067,581 0 2,318 1 1.0
db file sequential read 770,191 0 2,111 3 0.4
log file parallel write 3,395,234 0 1,663 0 1.6
db file parallel write 9,697 0 384 40 0.0
latch free 27,579 26,700 95 3 0.0
control file parallel write 1,155 0 2 1 0.0
LGWR wait for redo copy 35,078 0 1 0 0.0
log file switch completion 131 0 1 8 0.0
buffer busy waits 27 0 0 7 0.0
direct path write 150 0 0 0 0.0
log file single write 30 0 0 1 0.0
db file scattered read 72 0 0 0 0.0
control file sequential read 732 0 0 0 0.0
direct path read 150 0 0 0 0.0
enqueue 2 0 0 0 0.0
log file sequential read 30 0 0 0 0.0
SQL*Net message from client 10,554,179 0 11,658 1 5.1
virtual circuit status 98 98 2,855 29132 0.0
wakeup time manager 95 95 2,774 29195 0.0
SQL*Net message to client 10,554,175 0 14 0 5.1
-------------------------------------------------------------
Background Wait Events for DB: ora1 Instance: ora1 Snaps: 4 -5
->; ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 3,395,242 0 1,663 0 1.6
db file parallel write 9,697 0 384 40 0.0
control file parallel write 1,155 0 2 1 0.0
LGWR wait for redo copy 35,078 0 1 0 0.0
direct path write 150 0 0 0 0.0
log file single write 30 0 0 1 0.0
db file scattered read 71 0 0 0 0.0
control file sequential read 688 0 0 0 0.0
direct path read 150 0 0 0 0.0
db file sequential read 36 0 0 0 0.0
latch free 10 1 0 0 0.0
log file sequential read 30 0 0 0 0.0
rdbms ipc message 1,213,463 1,853 7,237 6 0.6
pmon timer 976 976 2,867 2938 0.0
smon timer 10 10 2,789 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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
--------------- ------------ -------------- ------ -------- --------- ----------
112,000,003 5,276,794 21.2 99.9 8092.25 10260.50 1853932780
Module: FanganDB.exe
begin sel_crbt_user_current_voc(:1,:2,:3); end;
21,095,876 5,276,853 4.0 18.8 496.54 737.15 3279089460
Module: FanganDB.exe
SELECT CRBT_RING_USER_SCHEME_DEF.VOC_ID, CRBT_RING_USER_SCHEME_D
EF.FILE_NAME, CRBT_RING_USER_SCHEME_DEF.COLLECT_NUM, CRBT_RING_U
SER_SCHEME_DEF.LAST_COLLECT_ID, CRBT_RING_USER_SCHEME_DEF.DISK_L
IB_ID, CRBT_RING_USER_SCHEME_DEF.CRBT_STATUS, CRBT_RING_USER_SCH
EME_DEF.PLAY_TYPE_CODE, CRBT_RING_USER_SCHEME_DEF.FRIEND_NO_NUM,
16,020,122 4,007,341 4.0 14.3 393.52 2173.00 739240863
Module: FanganDB.exe
SELECT CRBT_USER_VOC_COLLECT_DEF.VOC_ID, CRBT_USER_VOC_COLLECT_D
EF.DISK_LIB_ID, CRBT_USER_VOC_COLLECT_DEF.FILE_NAME FROM CRBT_US
ER_VOC_COLLECT_DEF WHERE ( CRBT_USER_VOC_COLLECT_DEF.USER_TEL_NO
= :B3 ) AND ( CRBT_USER_VOC_COLLECT_DEF.SERVICE_SCHEME_CODE = :
B2 ) AND ( CRBT_USER_VOC_COLLECT_DEF.COLLECT_ID = :B1 )
15,829,127 5,277,015 3.0 14.1 478.65 478.69 1117863367
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'hh24:mi:ss') FROM DUAL
15,828,913 5,276,567 3.0 14.1 674.28 721.97 3640767565
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM DUAL
10,524,652 5,262,438 2.0 9.4 316.93 299.04 2834424588
Module: FanganDB.exe
SELECT DISK_LIB_PATH FROM CRBT_RING_DISK_LIB_DEF WHERE DISK_LIB_
ID=:B1
9,001,999 3,000,936 3.0 8.0 599.75 609.26 3549638847
Module: FanganDB.exe
SELECT TRUNC(DBMS_RANDOM.VALUE(1,:B1 +1)) FROM DUAL
5,305,956 1,057,398 5.0 4.7 241.16 241.66 456957723
Module: FanganDB.exe
UPDATE CRBT_RING_USER_SCHEME_DEF SET GROUP_COLLECT_ID=:B3 WHERE
( CRBT_RING_USER_SCHEME_DEF.USER_TEL_NO = :B2 ) AND ( CRBT_RING_
USER_SCHEME_DEF.SERVICE_SCHEME_CODE = :B1 )
5,068,996 1,010,276 5.0 4.5 235.27 235.14 720701201
Module: FanganDB.exe
UPDATE CRBT_RING_USER_SCHEME_DEF SET LAST_COLLECT_ID=:B3 WHERE (
CRBT_RING_USER_SCHEME_DEF.USER_TEL_NO = :B2 ) AND ( CRBT_RING_U
SER_SCHEME_DEF.SERVICE_SCHEME_CODE = :B1 )
4,185,096 4,186,208 1.0 3.7 315.84 303.11 3822720426
SQL ordered by Gets for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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
--------------- ------------ -------------- ------ -------- --------- ----------
Module: FanganDB.exe
SELECT COUNT(MUSIC_BOX_ID) FROM CRBT_RING_SP_MUSIC_BOX_DEF WHERE
MUSIC_BOX_ID=:B1
3,584,320 1,194,888 3.0 3.2 85.64 81.09 3695612157
Module: FanganDB.exe
SELECT DBMS_GROUP_USER_INFO_DEF.PLAY_TYPE_CODE, DBMS_GROUP_USER_
INFO_DEF.VOC_COLLECT_NUM, DBMS_GROUP_USER_INFO_DEF.TIME_SCHEME,
DBMS_GROUP_USER_INFO_DEF.START_DATE, DBMS_GROUP_USER_INFO_DEF.EN
D_DATE, DBMS_GROUP_USER_INFO_DEF.DISK_LIB_ID, DBMS_GROUP_USER_IN
FO_DEF.FILE_NAME FROM DBMS_GROUP_USER_INFO_DEF WHERE DBMS_GROUP_
3,181,650 1,060,678 3.0 2.8 75.24 70.87 41046511
Module: FanganDB.exe
SELECT CRBT_GROUP_USER_COLLECT_DEF.DISK_LIB_ID, CRBT_GROUP_USER_
COLLECT_DEF.FILE_NAME FROM CRBT_GROUP_USER_COLLECT_DEF WHERE ( C
RBT_GROUP_USER_COLLECT_DEF.GROUP_USER_ID = :B3 ) AND ( CRBT_GROU
P_USER_COLLECT_DEF.SERVICE_SCHEME_CODE = :B2 ) AND ( CRBT_GROUP_
USER_COLLECT_DEF.GROUP_COLLECT_ID = :B1 )
2,067,673 2,067,685 1.0 1.8 253.14 252.14 1053795750
Module: FanganDB.exe
COMMIT
287,160 95,721 3.0 0.3 7.56 6.91 2005482446
Module: FanganDB.exe
SELECT CRBT_RING_FRIEND_TEL_DEF.VOC_ID, CRBT_RING_FRIEND_TEL_DEF
.FILE_NAME, CRBT_RING_FRIEND_TEL_DEF.CRBT_STATUS, CRBT_RING_FRIE
ND_TEL_DEF.FILE_IS_CHECK, CRBT_RING_FRIEND_TEL_DEF.DISK_LIB_ID,
CRBT_RING_FRIEND_TEL_DEF.TIME_SCHEME, CRBT_RING_FRIEND_TEL_DEF.S
TART_TIME, CRBT_RING_FRIEND_TEL_DEF.END_TIME, CRBT_RING_FRIEND_T
36,678 12,226 3.0 0.0 1.22 1.34 253718770
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL
36,370 95 382.8 0.0 1.02 1.70 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
13,357 1,900 7.0 0.0 0.38 0.44 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
3,144 1,048 3.0 0.0 0.27 0.21 1771587059
SQL ordered by Gets for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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
--------------- ------------ -------------- ------ -------- --------- ----------
Module: FanganDB.exe
SELECT TRUNC(DBMS_RANDOM.VALUE(1,:B1 )) FROM DUAL
2,526 1,263 2.0 0.0 0.11 0.09 3550383609
Module: FanganDB.exe
SELECT DISK_LIB_ID,FILE_NAME FROM CRBT_RING_SP_MUSIC_BOX_DEF WHE
RE MUSIC_BOX_ID=:B2 AND SEQ_ID=:B1
1,172 586 2.0 0.0 0.00 0.17 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
586 586 1.0 0.0 0.00 0.05 1693927332
select count(*) from sys.job$ where (next_date >; sysdate) and (n
ext_date < (sysdate+5/86400))
515 1 515.0 0.0 0.04 0.04 4115672663
select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
1048576) and o.obj#=t.obj# and o.owner# = u.user#
342 114 3.0 0.0 0.04 0.03 1254950678
select file# from file$ where ts#=:1
150 10 15.0 0.0 0.00 0.01 2095543314
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
-------------------------------------------------------------
SQL ordered by Reads for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
769,706 5,276,794 0.1 99.8 8092.25 10260.50 1853932780
Module: FanganDB.exe
begin sel_crbt_user_current_voc(:1,:2,:3); end;
665,489 4,007,341 0.2 86.3 393.52 2173.00 739240863
Module: FanganDB.exe
SELECT CRBT_USER_VOC_COLLECT_DEF.VOC_ID, CRBT_USER_VOC_COLLECT_D
EF.DISK_LIB_ID, CRBT_USER_VOC_COLLECT_DEF.FILE_NAME FROM CRBT_US
ER_VOC_COLLECT_DEF WHERE ( CRBT_USER_VOC_COLLECT_DEF.USER_TEL_NO
= :B3 ) AND ( CRBT_USER_VOC_COLLECT_DEF.SERVICE_SCHEME_CODE = :
B2 ) AND ( CRBT_USER_VOC_COLLECT_DEF.COLLECT_ID = :B1 )
103,607 5,276,853 0.0 13.4 496.54 737.15 3279089460
Module: FanganDB.exe
SELECT CRBT_RING_USER_SCHEME_DEF.VOC_ID, CRBT_RING_USER_SCHEME_D
EF.FILE_NAME, CRBT_RING_USER_SCHEME_DEF.COLLECT_NUM, CRBT_RING_U
SER_SCHEME_DEF.LAST_COLLECT_ID, CRBT_RING_USER_SCHEME_DEF.DISK_L
IB_ID, CRBT_RING_USER_SCHEME_DEF.CRBT_STATUS, CRBT_RING_USER_SCH
EME_DEF.PLAY_TYPE_CODE, CRBT_RING_USER_SCHEME_DEF.FRIEND_NO_NUM,
512 1 512.0 0.1 0.04 0.04 4115672663
select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
1048576) and o.obj#=t.obj# and o.owner# = u.user#
123 10 12.3 0.0 0.00 0.01 2095543314
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
35 95,721 0.0 0.0 7.56 6.91 2005482446
Module: FanganDB.exe
SELECT CRBT_RING_FRIEND_TEL_DEF.VOC_ID, CRBT_RING_FRIEND_TEL_DEF
.FILE_NAME, CRBT_RING_FRIEND_TEL_DEF.CRBT_STATUS, CRBT_RING_FRIE
ND_TEL_DEF.FILE_IS_CHECK, CRBT_RING_FRIEND_TEL_DEF.DISK_LIB_ID,
CRBT_RING_FRIEND_TEL_DEF.TIME_SCHEME, CRBT_RING_FRIEND_TEL_DEF.S
TART_TIME, CRBT_RING_FRIEND_TEL_DEF.END_TIME, CRBT_RING_FRIEND_T
24 95 0.3 0.0 1.02 1.70 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
12 5 2.4 0.0 0.00 0.00 2201872251
SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
:1
12 3,000,936 0.0 0.0 599.75 609.26 3549638847
Module: FanganDB.exe
SELECT TRUNC(DBMS_RANDOM.VALUE(1,:B1 +1)) FROM DUAL
11 5,276,567 0.0 0.0 674.28 721.97 3640767565
Module: FanganDB.exe
SQL ordered by Reads for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM DUAL
4 5,277,015 0.0 0.0 478.65 478.69 1117863367
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'hh24:mi:ss') FROM DUAL
0 175 0.0 0.0 0.04 0.04 16450395
Module: FanganDB.exe
begin test_db_connect(:1,:2); end;
0 1,060,678 0.0 0.0 75.24 70.87 41046511
Module: FanganDB.exe
SELECT CRBT_GROUP_USER_COLLECT_DEF.DISK_LIB_ID, CRBT_GROUP_USER_
COLLECT_DEF.FILE_NAME FROM CRBT_GROUP_USER_COLLECT_DEF WHERE ( C
RBT_GROUP_USER_COLLECT_DEF.GROUP_USER_ID = :B3 ) AND ( CRBT_GROU
P_USER_COLLECT_DEF.SERVICE_SCHEME_CODE = :B2 ) AND ( CRBT_GROUP_
USER_COLLECT_DEF.GROUP_COLLECT_ID = :B1 )
0 9 0.0 0.0 0.00 0.00 130926350
select count(*) from sys.job$ where next_date < :1 and (field1 =
:2 or (field1 = 0 and 'Y' = :3))
0 12,226 0.0 0.0 1.22 1.34 253718770
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL
0 1,057,398 0.0 0.0 241.16 241.66 456957723
Module: FanganDB.exe
UPDATE CRBT_RING_USER_SCHEME_DEF SET GROUP_COLLECT_ID=:B3 WHERE
( CRBT_RING_USER_SCHEME_DEF.USER_TEL_NO = :B2 ) AND ( CRBT_RING_
USER_SCHEME_DEF.SERVICE_SCHEME_CODE = :B1 )
0 5 0.0 0.0 0.00 0.00 615142939
INSERT INTO SMON_SCN_TIME (THREAD, TIME_MP, TIME_DP, SCN_WRP, SC
N_BAS) VALUES (:1, :2, :3, :4, :5)
0 95 0.0 0.0 0.00 0.02 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
0 1,010,276 0.0 0.0 235.27 235.14 720701201
Module: FanganDB.exe
UPDATE CRBT_RING_USER_SCHEME_DEF SET LAST_COLLECT_ID=:B3 WHERE (
CRBT_RING_USER_SCHEME_DEF.USER_TEL_NO = :B2 ) AND ( CRBT_RING_U
SER_SCHEME_DEF.SERVICE_SCHEME_CODE = :B1 )
0 95 0.0 0.0 0.00 0.02 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
SQL ordered by Reads for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_
0 2,067,685 0.0 0.0 253.14 252.14 1053795750
Module: FanganDB.exe
COMMIT
0 114 0.0 0.0 0.04 0.03 1254950678
select file# from file$ where ts#=:1
0 586 0.0 0.0 0.00 0.17 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
-------------------------------------------------------------
SQL ordered by Executions for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
5,277,015 5,276,391 1.0 0.00 0.00 1117863367
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'hh24:mi:ss') FROM DUAL
5,276,853 5,274,275 1.0 0.00 0.00 3279089460
Module: FanganDB.exe
SELECT CRBT_RING_USER_SCHEME_DEF.VOC_ID, CRBT_RING_USER_SCHEME_D
EF.FILE_NAME, CRBT_RING_USER_SCHEME_DEF.COLLECT_NUM, CRBT_RING_U
SER_SCHEME_DEF.LAST_COLLECT_ID, CRBT_RING_USER_SCHEME_DEF.DISK_L
IB_ID, CRBT_RING_USER_SCHEME_DEF.CRBT_STATUS, CRBT_RING_USER_SCH
EME_DEF.PLAY_TYPE_CODE, CRBT_RING_USER_SCHEME_DEF.FRIEND_NO_NUM,
5,276,794 5,276,307 1.0 0.00 0.00 1853932780
Module: FanganDB.exe
begin sel_crbt_user_current_voc(:1,:2,:3); end;
5,276,567 5,276,319 1.0 0.00 0.00 3640767565
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM DUAL
5,262,438 5,262,447 1.0 0.00 0.00 2834424588
Module: FanganDB.exe
SELECT DISK_LIB_PATH FROM CRBT_RING_DISK_LIB_DEF WHERE DISK_LIB_
ID=:B1
4,186,208 4,185,184 1.0 0.00 0.00 3822720426
Module: FanganDB.exe
SELECT COUNT(MUSIC_BOX_ID) FROM CRBT_RING_SP_MUSIC_BOX_DEF WHERE
MUSIC_BOX_ID=:B1
4,007,341 4,005,330 1.0 0.00 0.00 739240863
Module: FanganDB.exe
SELECT CRBT_USER_VOC_COLLECT_DEF.VOC_ID, CRBT_USER_VOC_COLLECT_D
EF.DISK_LIB_ID, CRBT_USER_VOC_COLLECT_DEF.FILE_NAME FROM CRBT_US
ER_VOC_COLLECT_DEF WHERE ( CRBT_USER_VOC_COLLECT_DEF.USER_TEL_NO
= :B3 ) AND ( CRBT_USER_VOC_COLLECT_DEF.SERVICE_SCHEME_CODE = :
B2 ) AND ( CRBT_USER_VOC_COLLECT_DEF.COLLECT_ID = :B1 )
3,000,936 3,000,672 1.0 0.00 0.00 3549638847
Module: FanganDB.exe
SELECT TRUNC(DBMS_RANDOM.VALUE(1,:B1 +1)) FROM DUAL
2,067,685 0 0.0 0.00 0.00 1053795750
Module: FanganDB.exe
COMMIT
1,194,888 1,194,762 1.0 0.00 0.00 3695612157
Module: FanganDB.exe
SELECT DBMS_GROUP_USER_INFO_DEF.PLAY_TYPE_CODE, DBMS_GROUP_USER_
INFO_DEF.VOC_COLLECT_NUM, DBMS_GROUP_USER_INFO_DEF.TIME_SCHEME,
DBMS_GROUP_USER_INFO_DEF.START_DATE, DBMS_GROUP_USER_INFO_DEF.EN
D_DATE, DBMS_GROUP_USER_INFO_DEF.DISK_LIB_ID, DBMS_GROUP_USER_IN
FO_DEF.FILE_NAME FROM DBMS_GROUP_USER_INFO_DEF WHERE DBMS_GROUP_
SQL ordered by Executions for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
1,060,678 1,060,564 1.0 0.00 0.00 41046511
Module: FanganDB.exe
SELECT CRBT_GROUP_USER_COLLECT_DEF.DISK_LIB_ID, CRBT_GROUP_USER_
COLLECT_DEF.FILE_NAME FROM CRBT_GROUP_USER_COLLECT_DEF WHERE ( C
RBT_GROUP_USER_COLLECT_DEF.GROUP_USER_ID = :B3 ) AND ( CRBT_GROU
P_USER_COLLECT_DEF.SERVICE_SCHEME_CODE = :B2 ) AND ( CRBT_GROUP_
USER_COLLECT_DEF.GROUP_COLLECT_ID = :B1 )
1,057,398 1,057,356 1.0 0.00 0.00 456957723
Module: FanganDB.exe
UPDATE CRBT_RING_USER_SCHEME_DEF SET GROUP_COLLECT_ID=:B3 WHERE
( CRBT_RING_USER_SCHEME_DEF.USER_TEL_NO = :B2 ) AND ( CRBT_RING_
USER_SCHEME_DEF.SERVICE_SCHEME_CODE = :B1 )
1,010,276 1,010,237 1.0 0.00 0.00 720701201
Module: FanganDB.exe
UPDATE CRBT_RING_USER_SCHEME_DEF SET LAST_COLLECT_ID=:B3 WHERE (
CRBT_RING_USER_SCHEME_DEF.USER_TEL_NO = :B2 ) AND ( CRBT_RING_U
SER_SCHEME_DEF.SERVICE_SCHEME_CODE = :B1 )
95,721 0 0.0 0.00 0.00 2005482446
Module: FanganDB.exe
SELECT CRBT_RING_FRIEND_TEL_DEF.VOC_ID, CRBT_RING_FRIEND_TEL_DEF
.FILE_NAME, CRBT_RING_FRIEND_TEL_DEF.CRBT_STATUS, CRBT_RING_FRIE
ND_TEL_DEF.FILE_IS_CHECK, CRBT_RING_FRIEND_TEL_DEF.DISK_LIB_ID,
CRBT_RING_FRIEND_TEL_DEF.TIME_SCHEME, CRBT_RING_FRIEND_TEL_DEF.S
TART_TIME, CRBT_RING_FRIEND_TEL_DEF.END_TIME, CRBT_RING_FRIEND_T
12,226 12,226 1.0 0.00 0.00 253718770
Module: FanganDB.exe
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL
1,900 1,900 1.0 0.00 0.00 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
1,263 1,263 1.0 0.00 0.00 3550383609
Module: FanganDB.exe
SELECT DISK_LIB_ID,FILE_NAME FROM CRBT_RING_SP_MUSIC_BOX_DEF WHE
RE MUSIC_BOX_ID=:B2 AND SEQ_ID=:B1
1,048 1,048 1.0 0.00 0.00 1771587059
Module: FanganDB.exe
SELECT TRUNC(DBMS_RANDOM.VALUE(1,:B1 )) FROM DUAL
586 0 0.0 0.00 0.00 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
586 586 1.0 0.00 0.00 1693927332
SQL ordered by Executions for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
select count(*) from sys.job$ where (next_date >; sysdate) and (n
ext_date < (sysdate+5/86400))
175 175 1.0 0.00 0.00 16450395
Module: FanganDB.exe
begin test_db_connect(:1,:2); end;
114 114 1.0 0.00 0.00 1254950678
select file# from file$ where ts#=:1
95 0 0.0 0.00 0.00 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
95 0 0.0 0.00 0.00 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
5,277,020 5,276,794 99.96 1853932780
Module: FanganDB.exe
begin sel_crbt_user_current_voc(:1,:2,:3); end;
175 175 0.00 16450395
Module: FanganDB.exe
begin test_db_connect(:1,:2); end;
114 114 0.00 1254950678
select file# from file$ where ts#=:1
95 0 0.00 163922476
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS.QS_ORDERS_PR_MQTAB where msgid = :1
95 0 0.00 378803933
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_ES.QS_ES_ORDERS_MQTAB where msgid = :1
95 0 0.00 613578973
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_ES.QS_ES_ORDERS_PR_MQTAB where msgid = :1
95 95 0.00 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
95 95 0.00 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_
95 95 0.00 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1
95 0 0.00 1937207903
SQL ordered by Parse Calls for DB: ora1 Instance: ora1 Snaps: 4 -5
->; End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_OS.QS_OS_ORDERS_PR_MQTAB where msgid = :1
95 0 0.00 2540375272
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_OS.QS_OS_ORDERS_MQTAB where msgid = :1
95 95 0.00 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
95 0 0.00 2833257892
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_MQTAB where msgid = :1
95 0 0.00 3137402284
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_WS.QS_WS_ORDERS_PR_MQTAB where msgid = :1
95 1,900 0.00 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
95 0 0.00 3551876895
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
-------------------------------------------------------------
Instance Activity Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 932,399 317.6 0.5
CPU used when call started 932,399 317.6 0.5
CR blocks created 159 0.1 0.0
DBWR buffers scanned 638,131 217.4 0.3
DBWR checkpoint buffers written 392,705 133.8 0.2
DBWR checkpoints 15 0.0 0.0
DBWR free buffers found 588,928 200.6 0.3
DBWR lru scans 4,813 1.6 0.0
DBWR make free requests 8,922 3.0 0.0
DBWR summed scan depth 638,131 217.4 0.3
DBWR transaction table writes 160 0.1 0.0
DBWR undo block writes 36,174 12.3 0.0
SQL*Net roundtrips to/from client 10,554,384 3,594.8 5.1
active txn count during cleanout 55 0.0 0.0
background checkpoints completed 15 0.0 0.0
background checkpoints started 15 0.0 0.0
background timeouts 3,489 1.2 0.0
buffer is not pinned count 47,194,431 16,074.4 22.8
buffer is pinned count 3,610 1.2 0.0
bytes received via SQL*Net from c 1,000,900,014 340,906.0 484.1
bytes sent via SQL*Net to client 575,350,263 195,964.0 278.3
calls to get snapshot scn: kcmgss 68,012,756 23,165.1 32.9
calls to kcmgas 2,073,924 706.4 1.0
calls to kcmgcs 63 0.0 0.0
change write time 11,778 4.0 0.0
cleanout - number of ktugct calls 69 0.0 0.0
cleanouts and rollbacks - consist 38 0.0 0.0
cleanouts only - consistent read 7 0.0 0.0
cluster key scan block gets 10 0.0 0.0
cluster key scans 5 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 1 0.0 0.0
commit cleanout failures: cannot 9 0.0 0.0
commit cleanouts 2,073,450 706.2 1.0
commit cleanouts successfully com 2,073,440 706.2 1.0
commit txn count during cleanout 24 0.0 0.0
consistent changes 4,529 1.5 0.0
consistent gets 105,841,290 36,049.5 51.2
consistent gets - examination 60,938,552 20,755.6 29.5
cursor authentications 24 0.0 0.0
data blocks consistent reads - un 4,527 1.5 0.0
db block changes 8,294,690 2,825.2 4.0
db block gets 6,256,305 2,130.9 3.0
deferred (CURRENT) block cleanout 1,053,880 359.0 0.5
dirty buffers inspected 12,713 4.3 0.0
enqueue conversions 25 0.0 0.0
enqueue releases 4,150,337 1,413.6 2.0
enqueue requests 4,150,335 1,413.6 2.0
enqueue waits 2 0.0 0.0
execute count 44,072,266 15,011.0 21.3
free buffer inspected 12,788 4.4 0.0
free buffer requested 809,083 275.6 0.4
hot buffers moved to head of LRU 1,961,260 668.0 1.0
immediate (CR) block cleanout app 45 0.0 0.0
immediate (CURRENT) block cleanou 21 0.0 0.0
index fetch by key 18,975,020 6,462.9 9.2
Instance Activity Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index scans kdiixs1 4,190,061 1,427.1 2.0
leaf node 90-10 splits 0 0.0 0.0
leaf node splits 10 0.0 0.0
logons cumulative 1 0.0 0.0
messages received 1,710,142 582.5 0.8
messages sent 1,710,142 582.5 0.8
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 13,571,442 4,622.4 6.6
number of auto extends on undo ta 0 0.0 0.0
opened cursors cumulative 1,715 0.6 0.0
parse count (failures) 1 0.0 0.0
parse count (hard) 1 0.0 0.0
parse count (total) 5,278,895 1,798.0 2.6
parse time cpu 38,913 13.3 0.0
parse time elapsed 40,634 13.8 0.0
physical reads 771,128 262.7 0.4
physical reads direct 150 0.1 0.0
physical writes 454,742 154.9 0.2
physical writes direct 150 0.1 0.0
physical writes non checkpoint 119,324 40.6 0.1
pinned buffers inspected 3 0.0 0.0
prefetched blocks 642 0.2 0.0
process last non-idle time 3,060 1.0 0.0
recursive calls 46,239,542 15,749.2 22.4
recursive cpu usage 579,989 197.5 0.3
redo blocks written 2,992,313 1,019.2 1.5
redo buffer allocation retries 98 0.0 0.0
redo entries 4,157,257 1,416.0 2.0
redo log space requests 131 0.0 0.0
redo log space wait time 99 0.0 0.0
redo size 1,044,681,860 355,818.1 505.3
redo synch time 239,298 81.5 0.1
redo synch writes 2,067,658 704.2 1.0
redo wastage 439,492,676 149,691.0 212.6
redo write time 171,986 58.6 0.1
redo writer latching time 105 0.0 0.0
redo writes 1,697,647 578.2 0.8
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 499 0.2 0.0
rows fetched via callback 16,811,405 5,726.0 8.1
session connect time 222 0.1 0.0
session logical reads 112,097,599 38,180.4 54.2
session uga memory 143,920 49.0 0.1
session uga memory max 274,712 93.6 0.1
shared hash latch upgrades - no w 4,945,171 1,684.3 2.4
shared hash latch upgrades - wait 15,104 5.1 0.0
sorts (memory) 738 0.3 0.0
sorts (rows) 9,515 3.2 0.0
summed dirty queue length 67,648 23.0 0.0
switch current to new buffer 1,901 0.7 0.0
table fetch by rowid 16,811,520 5,726.0 8.1
table fetch continued row 0 0.0 0.0
table scan blocks gotten 13,570,987 4,622.3 6.6
table scan rows gotten 13,597,441 4,631.3 6.6
table scans (short tables) 13,570,282 4,622.0 6.6
user calls 10,554,374 3,594.8 5.1
Instance Activity Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
user commits 2,067,659 704.2 1.0
workarea executions - optimal 81 0.0 0.0
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 274 0.1 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
->;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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
USERS
769,415 262 2.8 1.0 418,058 142 8 25.0
UNDOTBS1
15 0 0.0 1.0 36,375 12 3 0.0
SYSTEM
181 0 0.1 4.5 53 0 16 0.0
TOOLS
30 0 0.0 1.1 166 0 0 0.0
CWMLITE
15 0 0.0 1.0 15 0 0 0.0
DRSYS
15 0 0.0 1.0 15 0 0 0.0
EXAMPLE
15 0 0.0 1.0 15 0 0 0.0
INDX
15 0 0.0 1.0 15 0 0 0.0
ODM
15 0 0.0 1.0 15 0 0 0.0
XDB
15 0 0.0 1.0 15 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
->;ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
CWMLITE /san/oracle/oradata/ora1/cwmlite01.dbf
15 0 0.0 1.0 15 0 0
DRSYS /san/oracle/oradata/ora1/drsys01.dbf
15 0 0.0 1.0 15 0 0
EXAMPLE /san/oracle/oradata/ora1/example01.dbf
15 0 0.0 1.0 15 0 0
INDX /san/oracle/oradata/ora1/indx01.dbf
15 0 0.0 1.0 15 0 0
ODM /san/oracle/oradata/ora1/odm01.dbf
15 0 0.0 1.0 15 0 0
SYSTEM /san/oracle/oradata/ora1/system01.dbf
181 0 0.1 4.5 53 0 16 0.0
TOOLS /san/oracle/oradata/ora1/tools01.dbf
30 0 0.0 1.1 166 0 0
UNDOTBS1 /san/oracle/oradata/ora1/undotbs01.dbf
15 0 0.0 1.0 36,375 12 3 0.0
USERS /san/oracle/oradata/ora1/users01.dbf
769,415 262 2.8 1.0 418,058 142 8 25.0
XDB /san/oracle/oradata/ora1/xdb01.dbf
15 0 0.0 1.0 15 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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 186,590 99.3 111,936,850 770,847 454,592 0 0 27
-------------------------------------------------------------
Instance Recovery Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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 121 30 24235 188239 184320 184320 351214
E 121 32 27377 187796 184320 184320 498492
-------------------------------------------------------------
Buffer Pool Advisory for DB: ora1 Instance: ora1 End Snap: 5
->; 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 144 .1 17,865 13.67 116,022,226
D 288 .2 35,730 10.08 85,587,573
D 432 .3 53,595 7.97 67,644,637
D 576 .4 71,460 6.45 54,735,740
D 720 .5 89,325 5.25 44,527,794
D 864 .6 107,190 4.25 36,047,939
D 1,008 .7 125,055 3.39 28,797,711
D 1,152 .8 142,920 2.63 22,290,678
D 1,296 .9 160,785 1.93 16,382,542
D 1,440 1.0 178,650 1.27 10,757,634
D 1,504 1.0 186,590 1.00 8,487,093
D 1,584 1.1 196,515 0.69 5,890,656
D 1,728 1.1 214,380 0.26 2,242,592
D 1,872 1.2 232,245 0.07 574,953
D 2,016 1.3 250,110 0.05 426,344
D 2,160 1.4 267,975 0.05 416,618
D 2,304 1.5 285,840 0.05 414,888
D 2,448 1.6 303,705 0.05 397,954
D 2,592 1.7 321,570 0.05 397,180
D 2,736 1.8 339,435 0.05 397,165
D 2,880 1.9 357,300 0.05 397,165
-------------------------------------------------------------
Buffer wait Statistics for DB: ora1 Instance: ora1 Snaps: 4 -5
->; ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 24 0 8
undo header 3 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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
--------------- ---------------- -------------------------
100.0 1 0
%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 477 420 19.2 0.0 .0 .0 .0 24,414
E 477 420 19.3 0.0 .0 .0 .0 24,414
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: ora1 Instance: ora1 Snaps: 4 -5
->; Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 70 70 0 0
16K 32K 8 8 0 0
32K 64K 2 2 0 0
64K 128K 2 2 0 0
256K 512K 1 1 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: ora1 Instance: ora1 End Snap: 5
->; 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
---------- ------- ---------------- ---------------- -------- ----------
60 0.1 26.5 0.0 100.0 0
119 0.3 26.5 0.0 100.0 0
238 0.5 26.5 0.0 100.0 0
358 0.8 26.5 0.0 100.0 0
477 1.0 26.5 0.0 100.0 0
572 1.2 26.5 0.0 100.0 0
668 1.4 26.5 0.0 100.0 0
763 1.6 26.5 0.0 100.0 0
858 1.8 26.5 0.0 100.0 0
954 2.0 26.5 0.0 100.0 0
1,431 3.0 26.5 0.0 100.0 0
1,907 4.0 26.5 0.0 100.0 0
2,861 6.0 26.5 0.0 100.0 0
3,815 8.0 26.5 0.0 100.0 0
-------------------------------------------------------------
Enqueue activity for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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)
-- ------------ ------------ ----------- ----------- ------------- ------------
HW 119 119 0 2 .50 0
-------------------------------------------------------------
Rollback Segment Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
->;A high value for " ct 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 11.0 0.00 0 0 0 0
1 745,443.0 0.00 49,943,128 10 0 2
2 387,303.0 0.00 25,946,630 16 0 8
3 377,384.0 0.00 25,281,042 16 0 14
4 377,962.0 0.00 25,322,618 19 0 18
5 376,618.0 0.00 25,355,264 20 0 11
6 376,288.0 0.00 25,207,986 18 0 12
7 376,374.0 0.00 25,212,988 16 0 15
8 374,000.0 0.00 25,053,792 19 0 16
9 378,219.0 0.00 25,338,098 18 0 8
10 375,690.0 0.00 25,168,300 16 0 10
-------------------------------------------------------------
Rollback Segment Storage for DB: ora1 Instance: ora1 Snaps: 4 -5
->;Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 103,931,904 5,082,803 130,080,768
2 51,568,640 1,549,783 105,701,376
3 55,762,944 1,243,259 111,337,472
4 53,665,792 1,731,685 106,881,024
5 54,779,904 1,206,047 104,259,584
6 55,697,408 1,529,712 110,747,648
7 54,845,440 1,740,119 104,587,264
8 61,988,864 1,097,748 108,060,672
9 51,568,640 1,776,257 105,439,232
10 53,796,864 1,808,230 104,587,264
-------------------------------------------------------------
Undo Segment Summary for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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 36,199 ########## 1 1 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: ora1 Instance: ora1 Snaps: 4 -5
->; 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-1月 10:4 7,345 ######## 1 1 0 0 0/0/0/0/0/0
5
06-1月 10:3 7,208 ######## 1 1 0 0 0/0/0/0/0/0
5
06-1月 10:2 6,877 ######## 1 1 0 0 0/0/0/0/0/0
5
06-1月 10:1 7,674 ######## 1 1 0 0 0/0/0/0/0/0
5
06-1月 10:0 7,095 ######## 1 1 0 0 0/0/0/0/0/0
5
-------------------------------------------------------------
Latch Activity for DB: ora1 Instance: ora1 Snaps: 4 -5
->;"Get Requests", "ct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->;"NoWait Requests", "ct NoWait Miss" are for no-wait latch get requests
->;"ct 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 1,697,836 0.0 0.0 0 0
FIB s.o chain latch 60 0.0 0 0
FOB s.o list latch 79 0.0 0 0
SQL memory manager latch 1 0.0 0 975 0.0
SQL memory manager worka 65,402 0.0 0 0
active checkpoint queue 13,489 0.0 0 0
archive control 1 0.0 0 0
cache buffer handles 304 0.0 0 0
cache buffers chains 178,636,274 2.0 0.0 0 820,198 0.0
cache buffers lru chain 533,162 0.0 0.0 0 808,893 0.1
channel handle pool latc 2 0.0 0 0
channel operations paren 1,978 0.0 0 0
checkpoint queue latch 2,897,892 0.0 0.0 0 456,151 0.0
child cursor hash table 7 0.0 0 0
dml lock allocation 4,152,764 0.4 0.0 0 0
dummy allocation 2 0.0 0 0
enqueue hash chains 8,300,707 0.2 0.0 0 0
enqueues 7,349,762 0.0 0.0 0 0
event group latch 1 0.0 0 0
hash table column usage 18 0.0 0 0
job_queue_processes para 49 0.0 0 0
ktm global data 10 0.0 0 0
kwqit: protect wakeup ti 95 0.0 0 0
lgwr LWN SCN 1,698,543 0.0 0.0 0 0
library cache 185,238,270 4.2 0.0 0 0
library cache pin 152,935,213 1.5 0.0 0 0
library cache pin alloca 21,123,481 1.0 0.0 0 0
list of block allocation 8 0.0 0 0
loader state object free 180 0.0 0 0
messages 5,071,795 0.1 0.0 0 0
mostly latch-free SCN 1,725,239 0.5 0.0 0 0
multiblock read objects 144 0.0 0 0
ncodef allocation latch 47 0.0 0 0
post/wait queue 3,942,824 0.0 0.0 0 2,065,209 0.1
process allocation 1 0.0 0 1 0.0
process group creation 2 0.0 0 0
redo allocation 7,552,893 0.4 0.0 0 0
redo copy 0 0 4,182,327 0.8
redo writing 5,108,673 0.0 0.0 0 0
row cache enqueue latch 6,579 0.0 0 0
row cache objects 6,589 0.0 0 0
sequence cache 6 0.0 0 0
session allocation 14,697,572 0.7 0.0 0 0
session idle bit 28,453,570 0.0 0.0 0 0
session switching 47 0.0 0 0
session timer 976 0.0 0 0
shared pool 50,346,834 2.0 0.0 0 0
sim partition latch 0 0 710 0.0
simulator hash latch 1,998,169 0.0 0.0 0 0
simulator lru latch 48,229 0.1 0.0 0 52,682 0.0
Latch Activity for DB: ora1 Instance: ora1 Snaps: 4 -5
->;"Get Requests", "ct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->;"NoWait Requests", "ct NoWait Miss" are for no-wait latch get requests
->;"ct Misses" for both should be very close to 0.0
Pct Avg Wait |
|
免费注册
发表于 2005-01-10 17:03
发表于 2005-01-11 11:42
