| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: suddenly high "physical write" from statspack
Hi, yong:
As you noted, there are also some front users having "physical write",I think it was caused by the sort. we can see that it is caused by the "disk sort", as the physical write = physical direct write = physical write non-checkpoint. Both Account and Eachpay user has the "physical direct write", but from the whosort.sql output, it is only a small part of total physical write. So I think it is not the disk sort that caused the so high physical write.
direct insert/load or import can only be done by DBA and it was 23:00 when the disk write increase, there should be no one working on the db. I asked the other dba of the site, he did not do it. Nor did I do. And as the physical write continues to be that high, it should not be because of such Maintenance work.
And the physical write has been so high since that day, now it is 2 times of disk read. From unix iostat log(I have cron run iostat hourly), the physical write is also triple. I also checked the "db file parallel write" wait event from statspack report, the wait time also increased.
I carefully compared the statspack report before and after the high physical write. It was about 23 pm, 18 Feb that physical write suddenly increased. The statistics like redo/second , db_block_changes/second, logical_read/second after 23PM(business begin to drop after 23:00) were all lower than the statistics before 23:00. But only physical write increased.
We saw similar things before. The physical write was very low when the database just started, after it runs for 2-3 days(typical business load), the physical write suddently increased and never drop down, kept on a steady level. If I did a batch job(like table reorg etc, yes, we do table reorg some times) after the database just started during the maintenance time, then the physical write soon get to that steady level , not after a typical run of 2-3 days. I guess it is because of some internal rule of the dbwr write the cache. Or maybe a bug of oracle database? We are on 8.1.7.2 64bit. But this time the database has been running for almost one month.
I will tell you how the disk write is after server reboot, as we will have to reboot all the server because of the maintenance of telecome server room.
I attached the statspack report before and after the high disk write. If you are still interested in the problem, you can have a look. There is still other problems in the database:)
Regards
Zhu Chao.
> Zhu Chao,
>
> I don't know the answer to your mystery. But your query against v$sesstat
shown
> below seems to say "physical writes", "physical writes direct" and
"physical
> writes non checkpoint" for eachpay are all 4224. Am I reading it right? So
my
> old question remains, did anybody do direct mode insert or sql*load or
> import/export during that mystery period? Did this occur again?
>
> Search in page for "Finding users who have dynamically set SORT_AREA_SIZE"
at
> http://www.stormloader.com/yonghuang/computer/OracleQ&A.txt to see whether
> anybody changed his session specific sort_area_size (use level 1 as you
tried).
> Using oradebug is not that straightforward. Each parameter is in a
different
> variable section.
>
> Yong
>
> --- zhuchao <chao_ping_at_vip.163.com> wrote:
> > Hi, yong:
> > We do have hourly statspack snapshot. From statspack report, we see
some
> > tablespaces get more write:
> > when with less write:
> > LTablespace IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps:
7412 -7413
> > ->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)
>
> -------------- ------- ------ ------- ------------ -------- ---------- ---
---Received on Mon Feb 23 2004 - 03:35:46 CST
> > ACCOUNT_TS
> > 121,198 34 9.4 1.0 42,178 12 40
4.8
> > BIDDB_TBL
> > 77,534 22 9.2 1.1 51,255 14 2,357
47.5
> > BIDDB_IDX
> > 40,963 11 9.8 1.0 36,558 10 84
0.5
> > RBS
> > 6 0 0.0 1.0 30,049 8 1,060
0.5
> > EACHPAY_IDX
> > 5,157 1 9.4 1.0 6,669 2 0
0.0
> > TEMP_LOCAL
> > 1,282 0 0.0 24.6 5,855 2 0
0.0
> > EACHPAY_BIG
> > 2,150 1 9.7 1.0 3,363 1 0
0.0
> > MBUS_TS
> > 36 0 0.0 1.0 996 0 2,681
3.2
> > EACHPAY_M
> > 375 0 7.9 1.0 43 0 0
0.0
> > USERS
> > 225 0 5.7 1.0 141 0 0
0.0
> > SYSTEM
> > 202 0 15.6 3.2 40 0 2
5.0
> > TOOLS
> > 36 0 0.8 1.0 24 0 0
0.0
> > DICT_TS
> > 12 0 0.0 1.0 6 0 0
0.0
> >
> >
> > when with high write:
> > Tablespace
> > ------------------------------
> > Av Av Av Av Buffer
Av Buf
> > Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
>
> -------------- ------- ------ ------- ------------ -------- ---------- ---
---
> > BIDDB_TBL
> > 53,885 15 8.5 1.0 121,543 34 3,948
26.8
> > BIDDB_IDX
> > 27,657 8 9.4 1.0 146,401 41 84
0.6
> > RBS
> > 5 0 0.0 1.0 150,667 42 899
0.6
> > ACCOUNT_TS
> > 65,021 18 9.3 1.0 54,236 15 12
13.3
> > MBUS_TS
> > 30 0 0.0 1.0 20,706 6 2,371
3.1
> > EACHPAY_IDX
> > 3,895 1 9.6 1.0 14,934 4 0
0.0
> > EACHPAY_BIG
> > 1,672 0 10.3 1.0 12,120 3 0
0.0
> > TEMP_LOCAL
> > 710 0 0.0 24.7 3,357 1 0
0.0
> > SYSTEM
> > 110 0 12.1 3.7 2,787 1 4
5.0
> > EACHPAY_M
> > 371 0 6.7 1.0 93 0 0
0.0
> > USERS
> > 231 0 5.4 1.0 207 0 0
0.0
> > TOOLS
> > 32 0 2.2 1.0 29 0 0
0.0
> > DICT_TS
> > 10 0 0.0 1.0 5 0 0
0.0
> > -------------------------------------------------------------
> > ^LFile IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415
> > ->ordered by Tablespace, File
> >
> >
> >
> > From the above report, I can conclude that the eachpay user is causing
more
> > write.(as this tablespace is mainly eachpay user's tablespace, though it
also
> > contain other schema's data).
> > Oradebug dump global_area 1 generate a tracefile, but I cannot find
the
> > changed parameter there. This database is still oracle 8.1.7 and there
is no
> > v$segstat table.
> >
> > 1 select /*+rule*/ c.username,c.machine,b.name,sum(a.value)
> > 2 from v$sesstat a, v$statname b,v$session c
> > 3 where a.statistic#=b.statistic#
> > 4 and a.sid=c.sid
> > 5 and b.name like '%physical write%'
> > 6 group by c.username, c.machine,b.name
> > 7* having sum(a.value)>0 order by 4 asc
> > 20:39:52 SQL> /
> >
> > USERNAME MACHINE NAME
> > SUM(A.VALUE)
> > ------------------------------ --------------------
> > ------------------------------ ------------
> > ACCOUNT app3 physical writes
> > 334
> > ACCOUNT app3 physical writes
direct
> > 334
> > ACCOUNT app3 physical writes non
> > checkpoint 334
> > EACHPAY pay5 physical writes
> > 435
> > EACHPAY pay5 physical writes non
> > checkpoint 435
> > EACHPAY pay5 physical writes
direct
> > 435
> > ACCOUNT app2 physical writes
> > 1322
> > ACCOUNT app2 physical writes
direct
> > 1322
> > ACCOUNT app2 physical writes non
> > checkpoint 1322
> > EACHPAY mid2 physical writes
> > 4224
> > EACHPAY mid2 physical writes
direct
> > 4224
> >
> > USERNAME MACHINE NAME
> > SUM(A.VALUE)
> > ------------------------------ --------------------
> > ------------------------------ ------------
> > EACHPAY mid2 physical writes non
> > checkpoint 4224
> > ACCOUNT app4 physical writes
> > 7029
> > ACCOUNT app4 physical writes
direct
> > 7029
> > ACCOUNT app4 physical writes non
> > checkpoint 7029
> > EACHPAY mid1 physical writes
> > 8448
> > EACHPAY mid1 physical writes
direct
> > 8448
> > EACHPAY mid1 physical writes non
> > checkpoint 8448
> > ACCOUNT mid1 physical writes
> > 9074
> > ACCOUNT mid1 physical writes
direct
> > 9074
> > ACCOUNT mid1 physical writes non
> > checkpoint 9074
> > PERFSTAT main-db3 physical writes
> > 10929
> >
> > USERNAME MACHINE NAME
> > SUM(A.VALUE)
> > ------------------------------ --------------------
> > ------------------------------ ------------
> > PERFSTAT main-db3 physical writes
direct
> > 10929
> > PERFSTAT main-db3 physical writes non
> > checkpoint 10929
> > ACCOUNT mid2 physical writes
> > 59433
> > ACCOUNT mid2 physical writes
direct
> > 59433
> > ACCOUNT mid2 physical writes non
> > checkpoint 59433
> > main-db3 physical writes
direct
> > 173681
> > ACCOUNT appg physical writes
> > 1692664
> > ACCOUNT appg physical writes
direct
> > 1692664
> > ACCOUNT appg physical writes non
> > checkpoint 1692664
> > ACCOUNT apph physical writes
> > 1735456
> > ACCOUNT apph physical writes
direct
> > 1735456
> >
> > USERNAME MACHINE NAME
> > SUM(A.VALUE)
> > ------------------------------ --------------------
> > ------------------------------ ------------
> > ACCOUNT apph physical writes non
> > checkpoint 1735456
> > ACCOUNT appi physical writes
> > 1740670
> > ACCOUNT appi physical writes
direct
> > 1740670
> > ACCOUNT appi physical writes non
> > checkpoint 1740670
> > ACCOUNT appj physical writes
> > 1785008
> > ACCOUNT appj physical writes
direct
> > 1785008
> > ACCOUNT appj physical writes non
> > checkpoint 1785008
> > main-db3 physical writes non
> > checkpoint 23826637
> > main-db3 physical writes
> > 62862580
> >
> > I think sort/hashing does not have much to do with this, as sort used
> > direct read/write in 8i version, right?
> > and there is seperate io for them:
> >
> === message truncated ===
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail SpamGuard - Read only the mail you want.
> http://antispam.yahoo.com/tools
>
>
-- Attached file included as plaintext by Ecartis -- -- File: spreport-before-highio-3.txt Using the report name sp_7413_7414 STATSPACK report for DB Name DB Id Instance Inst Num Release OPS Host ------------ ----------- ------------ -------- ----------- --- ------------ EACHDB2 3249187745 eachdb2 1 8.1.7.2.0 NO main-db3 Snap Id Snap Time Sessions ------- ------------------ -------- Begin Snap: 7413 18-Feb-04 22:00:09 543 End Snap: 7414 18-Feb-04 23:00:17 543 Elapsed: 60.13 (mins) Cache Sizes ~~~~~~~~~~~ db_block_buffers: 983040 log_buffer: 256000 db_block_size: 8192 shared_pool_size: 262144000 Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 135,210.57 1,757.46 Logical reads: 77,150.05 1,002.79 Block changes: 954.24 12.40 Physical reads: 66.06 0.86 Physical writes: 70.96 0.92 User calls: 2,313.67 30.07 Parses: 420.86 5.47 Hard parses: 0.00 0.00 Sorts: 99.84 1.30 Logons: 0.11 0.00 Executes: 1,224.73 15.92 Transactions: 76.94 % Blocks changed per Read: 1.24 Recursive Call %: 12.14 Rollback per transaction %: 29.46 Rows per Sort: 106.17 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.91 In-memory Sort %: 99.98 Library Hit %: 100.01 Soft Parse %: 100.00 Execute to Parse %: 65.64 Latch Hit %: 99.78 Parse CPU to Parse Elapsd %: 92.29 % Non-Parse CPU: 99.99 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 68.24 68.24 % SQL with executions>1: 74.15 74.15 % Memory for SQL w/exec>1: 89.77 89.78 Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- enqueue 23,717 611,398 57.49 db file sequential read 211,407 209,723 19.72 log file sync 208,575 111,731 10.51 log file parallel write 202,488 50,123 4.71 db file parallel write 3,789 33,943 3.19 ------------------------------------------------------------- Wait Events for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ enqueue 23,717 59 611,398 258 0.1 db file sequential read 211,407 0 209,723 10 0.8 log file sync 208,575 13 111,731 5 0.8 log file parallel write 202,488 0 50,123 2 0.7 db file parallel write 3,789 0 33,943 90 0.0 buffer busy waits 6,074 11 12,267 20 0.0 latch free 5,496 5,418 11,249 20 0.0 SQL*Net more data to client 863,038 0 9,566 0 3.1 log file sequential read 17,590 0 7,836 4 0.1 direct path write 2,922 0 1,764 6 0.0 db file scattered read 571 0 1,203 21 0.0 direct path read 3,010 0 933 3 0.0 control file sequential read 4,966 0 648 1 0.0 control file parallel write 1,358 0 413 3 0.0 refresh controlfile command 920 0 243 3 0.0 log file switch completion 21 0 179 85 0.0 file open 4,924 0 84 0 0.0 log buffer space 11 0 72 65 0.0 inactive transaction branch 2 0 33 165 0.0 switch logfile command 1 0 20 200 0.0 single-task message 24 0 15 6 0.0 LGWR wait for redo copy 1,435 1 14 0 0.0 log file single write 12 0 10 8 0.0 db file single write 7 0 3 4 0.0 SQL*Net break/reset to clien 70 0 2 0 0.0 file identify 30 0 1 0 0.0 buffer deadlock 23 23 0 0 0.0 SQL*Net message from client 8,366,576 0 ########### 168 30.1 SQL*Net message to client 8,366,559 0 2,390 0 30.1 SQL*Net more data from clien 29,397 0 286 0 0.1 ------------------------------------------------------------- Background Wait Events for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ log file parallel write 202,444 0 50,114 2 0.7 db file parallel write 3,787 0 33,907 90 0.0 log file sequential read 16,011 0 5,894 4 0.1 direct path read 2,275 0 514 2 0.0 control file parallel write 1,354 0 409 3 0.0 control file sequential read 2,233 0 283 1 0.0 file open 4,824 0 73 0 0.0 db file scattered read 10 0 25 25 0.0 latch free 9 8 17 19 0.0 LGWR wait for redo copy 1,437 1 14 0 0.0 db file sequential read 24 0 13 5 0.0 enqueue 2 0 11 55 0.0 log file single write 12 0 10 8 0.0 db file single write 7 0 3 4 0.0 direct path write 840 0 2 0 0.0 file identify 27 0 1 0 0.0 rdbms ipc message 368,383 2,865 2,089,538 57 1.3 smon timer 12 12 368,652 ###### 0.0 pmon timer 1,172 1,171 360,965 3080 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Buffer Gets Threshold: 30000 -> 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 Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 53,229,839 25,261 2,107.2 19.1 594340633 select CRIDIT_ID ,CRIDIT_ACCOUNT ,TRUST_CLASS ,(((((TO_CHAR(TIME _DTM,'YYYY')||'年')||TO_CHAR(TIME_DTM,'MM'))||'月')||TO_CHAR(TIM E_DTM,'DD'))||'日') A_TIME_DTM ,PRODUCT_ID ,TITLE ,PRICE ,CRITIC _REMARK ,JUDGE_STATE ,nvl(CRITIC_COMMENT,' ') A_CRITIC_COMMENT , nvl(MSG_CONTENT,' ') A_MSG_CONTENT ,nvl(MSG_STATE,0) A_MSG_STATE 33,146,437 25,260 1,312.2 11.9 3065721488 select count(*) into :b0 from USERJUDGE A ,USERPRIVATEINFO B w here ((((((A.JUDGE_ID>0 and A.CRITIC_ID=B.USER_ID) and A.JUDGE_S TATE not in (0,88)) and A.ACCEPTER_ID=:b1) and A.CRITIC_IDENTIT Y in (:b2,:b3)) and A.ACCP_VISIBLE=1) and A.CRITIC_REMARK in (:b 4,:b5,:b6)) order by A.CRITIC_TIME_DTM desc 23,521,816 25,286 930.2 8.5 2353964393 select count(*) into :b0 from USERJUDGE where ((CRITIC_ID=:b1 and (:b2 is null or RECORD_TIME_DTM>=TO_DATE(:b2,:b4))) and (:b 5 is null or RECORD_TIME_DTM<=TO_DATE(:b5,:b4))) 23,521,802 25,287 930.2 8.5 69757181 select count(*) into :b0 from USERJUDGE where (((CRITIC_ID=:b1 and JUDGE_STATE not in (:b2,:b3)) and (:b4 is null or RECORD_ TIME_DTM>=TO_DATE(:b4,:b6))) and (:b7 is null or RECORD_TIME_DT M<=TO_DATE(:b7,:b6))) 20,872,056 51,161 408.0 7.5 2203634836 select CRITIC_IDENTITY ,CRITIC_REMARK ,count(*) from UserJudge where (((ACCEPTER_ID=:b0 and JUDGE_STATE=:b1) and (SYSDATE-CRIT IC_TIME_DTM)<=:b2) and ACCP_VISIBLE=1) group by CRITIC_IDENTITY, CRITIC_REMARK 20,418,013 10,354 1,972.0 7.3 152563559 select A_JUDGE_ID ,A_AUCTION_ID ,A_PRODUCT_ID ,A_TITLE ,A_AUCTIO N_TYPE ,A_PRICE ,(((((TO_CHAR(A_AUCTION_DATE,'YYYY')||'年')||TO_ CHAR(A_AUCTION_DATE,'MM'))||'月')||TO_CHAR(A_AUCTION_DATE,'DD')) ||'日') AUCTION_DATE ,A_ACCEPT_ID ,C_ACCEPT_ACCOUNT ,C_TRUST_CLA SS ,B_JUDGE_STATE ,A_CRITIC_IDENTITY ,D_CHARGE_TYPE ,D_CHARGE_ID 10,436,027 25,580 408.0 3.7 2579060820 select CRITIC_IDENTITY ,CRITIC_REMARK ,count(*) from UserJudge where ((ACCEPTER_ID=:b0 and JUDGE_STATE=:b1) and ACCP_VISIBLE=1 ) group by CRITIC_IDENTITY,CRITIC_REMARK 10,411,863 25,265 412.1 3.7 3362851951 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S YSDATE-7)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 10,411,820 25,264 412.1 3.7 2408713440 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S SQL ordered by Gets for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Buffer Gets Threshold: 30000 -> 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 Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ YSDATE-30)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 10,411,723 25,264 412.1 3.7 2534678434 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S YSDATE-180)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 7,746,518 25,579 302.8 2.8 54290869 select A.CRITIC_IDENTITY ,A.CRITIC_REMARK ,count(*) from UserJ udge A ,USERPRIVATEINFO B where ((((A.CRITIC_ID=B.USER_ID and B. USER_TYPE>=:b0) and A.ACCEPTER_ID=:b1) and A.JUDGE_STATE=:b2) an d A.ACCP_VISIBLE=1) group by A.CRITIC_IDENTITY,A.CRITIC_REMARK 7,207,722 15,883 453.8 2.6 2291175285 SELECT /*+ index(a,IDX2_USERJUDGE) */TO_CHAR(A.JUDGE_ID) || C HR(9) || TO_CHAR(A.AUCTION_ID) || CHR(9) || TO_CHAR(A.PRODUCT _ID) || CHR(9) || RTRIM(A.TITLE) || CHR(9) || RTRIM(A.AUCTIO N_TYPE) || CHR(9) || TO_CHAR(A.CRITIC_ID) || CHR(9) || RTRIM (B.USER_ACCOUNT) || CHR(9) || TO_CHAR(B.USER_TRUST_SCORE) || 5,098,649 25,581 199.3 1.8 3786195604 select A.CRITIC_IDENTITY ,A.CRITIC_REMARK ,count(*) from UserJ udge A ,USERPRIVATEINFO B where ((((A.CRITIC_ID=B.USER_ID and B. USER_TYPE between :b0 and :b1) and A.ACCEPTER_ID=:b2) and A.JUDG E_STATE in (:b3,:b4)) and A.ACCP_VISIBLE=1) group by A.CRITIC_ID ENTITY,A.CRITIC_REMARK 4,869,756 27,428 177.5 1.7 1607768868 select count(*) into :b0 from USERJUDGE where ((((JUDGE_ID>0 a nd CRITIC_ID=:b1) and JUDGE_STATE=:b2) and RECORD_TIME_DTM>=(SYS DATE-:b3)) and RECORD_TIME_DTM<(SYSDATE-:b4)) 4,179,852 13,316 313.9 1.5 2641148933 select count(*) into :b0 from USERJUDGE where ((((JUDGE_ID>0 a nd CRITIC_ID=:b1) and JUDGE_STATE=:b2) and CRITIC_IDENTITY=:b3) and RECORD_TIME_DTM>=(SYSDATE-:b4)) 1,506,579 255,492 5.9 0.5 1640050967 select STD.STD_StatementDetailID ,STD.STD_BeginningBalance ,STD. STD_EndingBalance ,FT.FT_FundTypeDesc ,FT.FT_FundTypeID into :b0 ,:b1,:b2,:b3,:b4 from STD_StatementDetail STD ,FT_FundType FT w here ((STD.ST_StatementID=:b5 and STD.FT_FundTypeID=:b4) and FT. FT_FundTypeID=:b4) ------------------------------------------------------------- SQL ordered by Reads for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Disk Reads Threshold: 1000 Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 22,766 25,261 0.9 9.6 594340633 select CRIDIT_ID ,CRIDIT_ACCOUNT ,TRUST_CLASS ,(((((TO_CHAR(TIME _DTM,'YYYY')||'年')||TO_CHAR(TIME_DTM,'MM'))||'月')||TO_CHAR(TIM E_DTM,'DD'))||'日') A_TIME_DTM ,PRODUCT_ID ,TITLE ,PRICE ,CRITIC _REMARK ,JUDGE_STATE ,nvl(CRITIC_COMMENT,' ') A_CRITIC_COMMENT , nvl(MSG_CONTENT,' ') A_MSG_CONTENT ,nvl(MSG_STATE,0) A_MSG_STATE 17,677 131,409 0.1 7.4 4017178300 select JUDGE_STATE into :b0 from USERJUDGE where ((((JUDGE_ID>0 and CRITIC_ID=:b1) and ACCEPTER_ID=:b2) and AUCTION_ID=:b3) and AUCTION_TYPE=:b4) 15,426 24,393 0.6 6.5 479371298 SELECT USER_ID, USER_TYPE, USER_STATUS, USER_EACHPAY, USER_REGIO N_CODE FROM USERPRIVATEINFO WHERE USER_ACCOUNT = LOWER(:user_acc ount) OR USER_EMAIl = LOWER(:user_account) 9,855 46,815 0.2 4.1 593732686 select max(ST_STATEMENTDATEYYYYMM) into :b0 from ST_Statement w here PA_AccountID=:b1 9,691 21,436 0.5 4.1 1507661013 select STATUS into :b0 from USEREVENTCOLLATE where (USER_ID=:b1 and EVENT_ID=:b2) 7,874 21,479 0.4 3.3 1180587233 SELECT NVL(PHONE_MOBILE, :"SYS_B_0"), NVL(TO_NUMBER(TO_CHAR(SYSD ATE, :"SYS_B_1") - TO_CHAR(BIRTHDAY, :"SYS_B_2")), :"SYS_B_3"), NVL(CAREER, :"SYS_B_4"), NVL(INCOME, :"SYS_B_5"), NVL(EDUCATION, :"SYS_B_6"), NVL(SEX, :"SYS_B_7") FROM USERALLINFO WHERE USER_I D = :iuser_id 7,174 487 14.7 3.0 1907026973 SELECT NVL(TX_TRANSACTIONID,0),NVL(TO_CHAR(NVL(TX_TRANSACTIONPOS TDATE, NULL ),'YYYY-MM-DD HH24:MI:SS'),' '),NVL(TO_CHAR(NVL(TX_T RANSACTIONSETTLEMENTDATE, NULL ),'YYYY-MM-DD HH24:MI:SS'),' '),N VL(TXT_TRANSACTIONTYPEID,0),NVL(TX_TRANSACTIONDESC,' '),NVL(TX_S TATUS,0),NVL(TX_TRANSACTIONAMOUNT,0),NVL(TX_TRANSACTIONSETTLEMEN 7,109 57,729 0.1 3.0 940098683 select nvl(A.user_account,' ') ,nvl(A.USER_TYPE,0) ,NVL(A.USER_E MAIL,' ') ,NVL(B.USER_CITY,' ') ,NVL(B.ADDRESS_OFFICE,' ') ,NVL( B.ZIPCODE_OFFICE,' ') ,NVL(B.ADDRESS_HOME,' ') ,NVL(B.ZIPCODE_HO ME,' ') ,NVL(B.PHONE_OFFICE,' ') ,NVL(B.PHONE_HOME,' ') ,NVL(B.P HONE_MOBILE,' ') ,NVL(B.PAGER,' ') ,NVL(B.HOMEPAGE,' ') ,NVL(B.I 6,346 307,108 0.0 2.7 1258347700 select count(*) into :b0 from REALNAMEVERIFYINFO where (USER_I D=:b1 and ITEM_STATUS in (1,7,5)) 5,811 19,249 0.3 2.4 2587615371 insert into TXD_TransactionDetail (TXD_TransactionDetailID,TXD_T ransactionDetailPostDate,TXD_TransactionDetailAmount,TXD_Beginni ngBalance,TXD_EndingBalance,TX_TransactionID,AB_AccountBalanceID ,UL_LoginName) values (:b0,SYSDATE,:b1,:b2,:b3,:b4,:b5,:b6) SQL ordered by Reads for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Disk Reads Threshold: 1000 Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 5,442 27,428 0.2 2.3 1607768868 select count(*) into :b0 from USERJUDGE where ((((JUDGE_ID>0 a nd CRITIC_ID=:b1) and JUDGE_STATE=:b2) and RECORD_TIME_DTM>=(SYS DATE-:b3)) and RECORD_TIME_DTM<(SYSDATE-:b4)) 4,972 25,265 0.2 2.1 3362851951 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S YSDATE-7)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 4,758 6,414 0.7 2.0 3543481043 insert into TX_Transaction (TX_TransactionID,TX_TransactionDate, TX_TransactionPostDate,TX_TransactionSettlementDate,TX_Reference Number1,TX_ReferenceNumber2,TX_ReferenceString1,TX_ReferenceStri ng2,TX_ReferenceString3,TX_ReferenceString4,TX_TransactionDesc,T X_TransactionAmount,TX_TransactionSettlementAmount,TX_InvoiceIss 4,480 255,492 0.0 1.9 1640050967 select STD.STD_StatementDetailID ,STD.STD_BeginningBalance ,STD. STD_EndingBalance ,FT.FT_FundTypeDesc ,FT.FT_FundTypeID into :b0 ,:b1,:b2,:b3,:b4 from STD_StatementDetail STD ,FT_FundType FT w here ((STD.ST_StatementID=:b5 and STD.FT_FundTypeID=:b4) and FT. FT_FundTypeID=:b4) 4,348 7,834 0.6 1.8 2018138957 select UL_STATUS ,LOWER(UL_PASSWORD) ,ULT_USERLOGINTYPEID ,UL_LO GINID into :b0,:b1,:b2,:b3 from UL_UserLogin where UL_LOGINNAME =:b4 3,972 2,310 1.7 1.7 3414358947 insert into TXI_TRANSACTIONINFO (CT_CATEGORYID,TXI_TRANSACTIONIN FOID,TXI_CATEGORYNAME,SR_SERVICEID,TXI_ONSHELLTYPEID,PR_PRODUCTI D,TXI_PRODUCTDESC,TXI_TRANSACTIONAMOUNT,TXI_TRANSACTIONREALAMOUN T,TXI_GMSAMOUNT,PA_ACCOUNTID,TXI_TRANSACTIONDATE,TXI_REFERENCENU MBER1,TXI_REFERENCENUMBER2,TXI_REFERENCENUMBER3,TXI_REFERENCENUM 3,713 307,106 0.0 1.6 1650541524 select DECODE(SUCCESS_TIME,null ,' ',(((((TO_CHAR(SUCCESS_TIME,: b0)||:b1)||TO_CHAR(SUCCESS_TIME,:b2))||:b3)||TO_CHAR(SUCCESS_TIM E,:b4))||:b5)) into :b6 from UserStatisInfo where USER_ID=:b7 2,982 878 3.4 1.3 3521449882 insert into UserConfirmInfo (trace_no,user_id,user_confirm_statu ------------------------------------------------------------- SQL ordered by Executions for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Executions Threshold: 100 Executions Rows Processed Rows per Exec Hash Value ------------ ---------------- ---------------- ------------ 307,115 349,382 1.1 3035567900 select (((((((((((((RTRIM(USER_ACCOUNT)||CHR(9))||TO_CHAR(USER_R EGISTER_DATE,:b0))||:b1)||TO_CHAR(USER_REGISTER_DATE,:b2))||:b3) ||TO_CHAR(USER_REGISTER_DATE,:b4))||:b5)||CHR(9))||TO_CHAR(USER_ TRUST_CLASS))||CHR(9))||TO_CHAR(USER_TRUST_SCORE))||CHR(9))||TO_ CHAR(USER_STATUS)) ,USER_STORE_GRADE ,USER_VERIFY_TYPE ,USER_TYP 307,111 175,710 0.6 1405787545 select (((((((TO_CHAR(CHECK_DATE,:b0)||:b1)||TO_CHAR(CHECK_DATE, :b2))||:b3)||TO_CHAR(CHECK_DATE,:b4))||:b5)||CHR(9))||RTRIM(ITEM _TYPE)) into :b6 from USERVERIFYINFO where USER_ID=:b7 307,108 349,425 1.1 1258347700 select count(*) into :b0 from REALNAMEVERIFYINFO where (USER_I D=:b1 and ITEM_STATUS in (1,7,5)) 307,106 348,909 1.1 1650541524 select DECODE(SUCCESS_TIME,null ,' ',(((((TO_CHAR(SUCCESS_TIME,: b0)||:b1)||TO_CHAR(SUCCESS_TIME,:b2))||:b3)||TO_CHAR(SUCCESS_TIM E,:b4))||:b5)) into :b6 from UserStatisInfo where USER_ID=:b7 255,492 243,470 1.0 1640050967 select STD.STD_StatementDetailID ,STD.STD_BeginningBalance ,STD. STD_EndingBalance ,FT.FT_FundTypeDesc ,FT.FT_FundTypeID into :b0 ,:b1,:b2,:b3,:b4 from STD_StatementDetail STD ,FT_FundType FT w here ((STD.ST_StatementID=:b5 and STD.FT_FundTypeID=:b4) and FT. FT_FundTypeID=:b4) 144,196 156,234 1.1 140681161 select RTRIM(LOWER(:b0)) into :b0 from DUAL 131,409 131,341 1.0 4017178300 select JUDGE_STATE into :b0 from USERJUDGE where ((((JUDGE_ID>0 and CRITIC_ID=:b1) and ACCEPTER_ID=:b2) and AUCTION_ID=:b3) and AUCTION_TYPE=:b4) 66,111 66,115 1.0 1942863705 select AB_Balance into :b0 from AB_AccountBalance where (PA_Acc ountID=:b1 and FT_FundTypeID=:b2) 57,729 58,042 1.0 940098683 select nvl(A.user_account,' ') ,nvl(A.USER_TYPE,0) ,NVL(A.USER_E MAIL,' ') ,NVL(B.USER_CITY,' ') ,NVL(B.ADDRESS_OFFICE,' ') ,NVL( B.ZIPCODE_OFFICE,' ') ,NVL(B.ADDRESS_HOME,' ') ,NVL(B.ZIPCODE_HO ME,' ') ,NVL(B.PHONE_OFFICE,' ') ,NVL(B.PHONE_HOME,' ') ,NVL(B.P HONE_MOBILE,' ') ,NVL(B.PAGER,' ') ,NVL(B.HOMEPAGE,' ') ,NVL(B.I 51,161 118,602 2.3 2203634836 select CRITIC_IDENTITY ,CRITIC_REMARK ,count(*) from UserJudge where (((ACCEPTER_ID=:b0 and JUDGE_STATE=:b1) and (SYSDATE-CRIT IC_TIME_DTM)<=:b2) and ACCP_VISIBLE=1) group by CRITIC_IDENTITY, CRITIC_REMARK 46,815 44,859 1.0 593732686 select max(ST_STATEMENTDATEYYYYMM) into :b0 from ST_Statement w SQL ordered by Executions for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Executions Threshold: 100 Executions Rows Processed Rows per Exec Hash Value ------------ ---------------- ---------------- ------------ here PA_AccountID=:b1 46,815 44,855 1.0 640575081 select ST_STATEMENTID into :b0 from ST_Statement where (ST_STAT EMENTDATEYYYYMM=:b1 and PA_AccountID=:b2) 46,671 44,712 1.0 4107473742 select PA_AccountID into :b0 from UL_UserLogin where UL_LoginNa me=:b1 46,369 40,576 0.9 4222868888 select ST_StatementID ,ST_Status ,NVL(TO_CHAR(NVL(ST_LastEmailDa te,null ),'YYYY-MM-DD HH24:MI:SS'),' ') ,NVL(TO_CHAR(NVL(ST_Last FileDate,null ),'YYYY-MM-DD HH24:MI:SS'),' ') into :b0,:b1,:b2,: b3 from ST_Statement a where (PA_AccountID=:b4 and ST_STATEMENT DATEYYYYMM=:b5) 46,367 44,405 1.0 3681105500 select TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM')) into :b0 from DUAL 46,366 743 0.0 2028936444 update ST_Statement set ST_EndingDateYYYYMMDD=TO_NUMBER(TO_CHAR (SYSDATE,'YYYYMMDD')),ST_Status=:b0 where (ST_STATEMENTID=:b1 an d ST_EndingDateYYYYMMDD<>TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD'))) 45,905 46,207 1.0 3714111846 select count(*) into :b0 from SUSPECTLIST where SUSPECT_ID=:b1 45,900 45,918 1.0 1398313222 update STD_StatementDetail set STD_EndingBalance=:b0 where (ST_ StatementID=:b1 and FT_FundTypeID=:b2) 45,316 41,951 0.9 2187975665 select UL_STATUS ,ULT_USERLOGINTYPEID ,NVL(PA_ACCOUNTID,(-1)) in to :b0,:b1,:b2 from UL_UserLogin where UL_LoginName=:b3 45,241 41,920 0.9 3665067949 select PA_Status ,NVL(PA_OverdueBill,'F') ,NVL(PA_OverCreditLimi t,'F') into :b0,:b1,:b2 from PA_PaymentAccount where PA_Account ID=:b3 44,982 44,982 1.0 2294325282 select STD_EndingBalance into :b0 from STD_StatementDetail wher e (ST_StatementID=:b1 and FT_FundTypeID=:b2) for update 40,313 40,312 1.0 4260979423 select SEQ_TXD.nextval into :b0 from DUAL 40,311 40,312 1.0 2940529150 update AB_AccountBalance set AB_Balance=:b0 where AB_AccountBal anceID=:b1 SQL ordered by Executions for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> End Executions Threshold: 100 Executions Rows Processed Rows per Exec Hash Value ------------ ---------------- ---------------- ------------ ------------------------------------------------------------- Instance Activity Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ CPU used by this session 999,639 277.1 3.6 CPU used when call started 999,637 277.1 3.6 CR blocks created 117,421 32.5 0.4 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DBWR buffers scanned 29,199 8.1 0.1 DBWR checkpoint buffers written 231,138 64.1 0.8 DBWR checkpoints 6 0.0 0.0 DBWR free buffers found 29,099 8.1 0.1 DBWR lru scans 518 0.1 0.0 DBWR make free requests 518 0.1 0.0 DBWR revisited being-written buff 58 0.0 0.0 DBWR summed scan depth 29,199 8.1 0.1 DBWR transaction table writes 6,352 1.8 0.0 DBWR undo block writes 41,565 11.5 0.2 SQL*Net roundtrips to/from client 8,362,581 2,317.8 30.1 background checkpoints completed 7 0.0 0.0 background checkpoints started 6 0.0 0.0 background timeouts 3,620 1.0 0.0 branch node splits 10 0.0 0.0 buffer is not pinned count 216,534,068 60,015.0 780.1 buffer is pinned count 217,968,054 60,412.4 785.2 bytes received via SQL*Net from c 1,277,206,635 353,993.0 4,601.2 bytes sent via SQL*Net to client 2,687,074,479 744,754.6 9,680.3 calls to get snapshot scn: kcmgss 5,482,246 1,519.5 19.8 calls to kcmgas 215,031 59.6 0.8 calls to kcmgcs 26,652 7.4 0.1 change write time 5,728 1.6 0.0 cleanouts and rollbacks - consist 24,937 6.9 0.1 cleanouts only - consistent read 38,686 10.7 0.1 cluster key scan block gets 107,825 29.9 0.4 cluster key scans 64,321 17.8 0.2 commit cleanout failures: block l 26,994 7.5 0.1 commit cleanout failures: buffer 1,272 0.4 0.0 commit cleanout failures: callbac 1,067 0.3 0.0 commit cleanout failures: cannot 2,670 0.7 0.0 commit cleanouts 840,208 232.9 3.0 commit cleanouts successfully com 808,205 224.0 2.9 consistent changes 144,185 40.0 0.5 consistent gets 270,998,973 75,110.6 976.3 current blocks converted for CR cursor authentications 595 0.2 0.0 data blocks consistent reads - un 142,546 39.5 0.5 db block changes 3,442,881 954.2 12.4 db block gets 7,358,276 2,039.4 26.5 deferred (CURRENT) block cleanout 280,182 77.7 1.0 dirty buffers inspected 454 0.1 0.0 enqueue conversions 3,250,374 900.9 11.7 enqueue deadlocks 0 0.0 0.0 enqueue releases 5,725,777 1,587.0 20.6 enqueue requests 5,725,840 1,587.0 20.6 enqueue timeouts 59 0.0 0.0 enqueue waits 59 0.0 0.0 exchange deadlocks 23 0.0 0.0 execute count 4,418,824 1,224.7 15.9 free buffer inspected 787 0.2 0.0 Instance Activity Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ free buffer requested 505,532 140.1 1.8 hot buffers moved to head of LRU 641,409 177.8 2.3 immediate (CR) block cleanout app 63,623 17.6 0.2 immediate (CURRENT) block cleanou 39,774 11.0 0.1 index fast full scans (full) 3 0.0 0.0 leaf node splits 2,210 0.6 0.0 logons cumulative 398 0.1 0.0 logons current messages received 209,529 58.1 0.8 messages sent 209,530 58.1 0.8 no buffer to keep pinned count 6,775,665 1,878.0 24.4 no work - consistent read gets 179,824,427 49,840.5 647.8 opened cursors cumulative 120,662 33.4 0.4 opened cursors current parse count (hard) 0 0.0 0.0 parse count (total) 1,518,456 420.9 5.5 parse time cpu 10,985 3.0 0.0 parse time elapsed 11,903 3.3 0.0 physical reads 238,343 66.1 0.9 physical reads direct 24,444 6.8 0.1 physical writes 256,011 71.0 0.9 physical writes direct 24,345 6.8 0.1 physical writes non checkpoint 139,101 38.6 0.5 pinned buffers inspected 0 0.0 0.0 prefetched blocks 1,973 0.6 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 176,646,824,217 ############ 636,377.1 recursive calls 1,153,526 319.7 4.2 recursive cpu usage 15,488 4.3 0.1 redo blocks written 1,096,012 303.8 4.0 redo buffer allocation retries 32 0.0 0.0 redo entries 1,787,420 495.4 6.4 redo log space requests 21 0.0 0.0 redo log space wait time 179 0.1 0.0 redo size 487,839,732 135,210.6 1,757.5 redo synch time 111,934 31.0 0.4 redo synch writes 208,499 57.8 0.8 redo wastage 55,644,412 15,422.5 200.5 redo write time 81,078 22.5 0.3 redo writer latching time 15 0.0 0.0 redo writes 202,338 56.1 0.7 rollback changes - undo records a 4,053 1.1 0.0 rollbacks only - consistent read 93,942 26.0 0.3 rows fetched via callback 16,850,346 4,670.3 60.7 session connect time 176,646,824,217 ############ 636,377.1 session cursor cache count 40 0.0 0.0 session cursor cache hits 1,440,907 399.4 5.2 session logical reads 278,357,377 77,150.1 1,002.8 session pga memory 1,243,967,040 344,780.2 4,481.4 session pga memory max 1,243,927,896 344,769.4 4,481.3 session uga memory 3,034,544 841.1 10.9 session uga memory max 27,726,736 7,684.8 99.9 sorts (disk) 84 0.0 0.0 sorts (memory) 360,145 99.8 1.3 sorts (rows) 38,244,567 10,599.9 137.8 summed dirty queue length 125 0.0 0.0 Instance Activity Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ switch current to new buffer table fetch by rowid 200,663,209 55,616.2 722.9 table fetch continued row 8,452,213 2,342.6 30.5 table scan blocks gotten 933,238 258.7 3.4 table scan rows gotten 21,983,513 6,093.0 79.2 table scans (long tables) 0 0.0 0.0 table scans (short tables) 363,528 100.8 1.3 total file opens 4,924 1.4 0.0 transaction rollbacks 104 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 user calls 8,347,733 2,313.7 30.1 user commits 195,818 54.3 0.7 user rollbacks 81,764 22.7 0.3 write clones created in backgroun 17 0.0 0.0 write clones created in foregroun 10,296 2.9 0.0 ------------------------------------------------------------- Tablespace IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ ACCOUNT_TS 94,889 26 10.2 1.0 49,246 14 46 14.1 BIDDB_TBL 72,261 20 9.4 1.0 63,781 18 2,293 48.9 BIDDB_IDX 39,071 11 9.8 1.0 53,753 15 90 2.2 RBS 7 0 0.0 1.0 47,421 13 1,088 0.5 EACHPAY_IDX 5,064 1 10.0 1.0 8,612 2 0 0.0 EACHPAY_BIG 2,119 1 9.4 1.0 4,607 1 0 0.0 TEMP_LOCAL 877 0 0.0 25.0 4,026 1 0 0.0 MBUS_TS 42 0 0.0 1.0 3,016 1 2,554 3.2 SYSTEM 148 0 21.0 4.0 300 0 2 5.0 EACHPAY_M 358 0 7.4 1.0 39 0 0 0.0 USERS 240 0 6.2 1.0 137 0 0 0.0 TOOLS 41 0 1.0 1.0 25 0 0 0.0 DICT_TS 14 0 0.0 1.0 7 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ ACCOUNT_TS /oradata/eachdb2/table/account1.dbf 10,159 3 10.2 1.0 4,619 1 3 10.0 /oradata/eachdb2/table/account10.dbf 8,041 2 10.6 1.0 8,333 2 5 16.0 /oradata/eachdb2/table/account11.dbf 3,026 1 11.5 1.0 4,331 1 0 /oradata/eachdb2/table/account2.dbf 9,004 2 10.1 1.0 1,919 1 4 10.0 /oradata/eachdb2/table/account3.dbf 10,259 3 10.1 1.0 4,698 1 6 6.7 /oradata/eachdb2/table/account4.dbf 9,205 3 9.9 1.0 2,385 1 5 28.0 /oradata/eachdb2/table/account5.dbf 7,120 2 9.9 1.0 2,705 1 4 10.0 /oradata/eachdb2/table/account6.dbf 7,859 2 9.8 1.0 3,118 1 1 60.0 /oradata/eachdb2/table/account7.dbf 11,173 3 10.3 1.0 5,001 1 7 20.0 /oradata/eachdb2/table/account8.dbf 10,352 3 10.2 1.0 4,958 1 4 17.5 /oradata/eachdb2/table/account9.dbf 8,691 2 10.6 1.0 7,179 2 7 1.4 BIDDB_IDX /oradata/eachdb2/index/index001.dbf 253 0 9.7 1.0 145 0 0 /oradata/eachdb2/index/index002.dbf 1,767 0 10.1 1.0 14,578 4 7 5.7 /oradata/eachdb2/index/index003.dbf 1,326 0 10.9 1.2 1,598 0 1 0.0 /oradata/eachdb2/index/index004.dbf 573 0 10.4 1.4 97 0 0 /oradata/eachdb2/index/index005.dbf 11 0 2.7 1.0 10 0 0 /oradata/eachdb2/index/index006.dbf 1,114 0 10.2 1.0 1,096 0 0 /oradata/eachdb2/index/index007.dbf 1,322 0 9.9 1.0 3,280 1 76 2.0 /oradata/eachdb2/index/index008.dbf 1,092 0 9.9 1.0 517 0 0 /oradata/eachdb2/index/index009.dbf 1,151 0 10.5 1.0 856 0 0 /oradata/eachdb2/index/index010.dbf 1,065 0 9.9 1.0 529 0 0 /oradata/eachdb2/index02/index011.dbf 1,084 0 10.5 1.0 627 0 1 0.0 /oradata/eachdb2/index02/index012.dbf 1,385 0 9.8 1.0 771 0 0 /oradata/eachdb2/index02/index013.dbf 1,362 0 9.5 1.0 1,033 0 1 0.0 /oradata/eachdb2/index02/index014.dbf 1,489 0 9.5 1.0 527 0 0 /oradata/eachdb2/index02/index015.dbf File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ 1,172 0 9.2 1.0 595 0 1 10.0 BIDDB_IDX /oradata/eachdb2/index02/index016.dbf 1,034 0 9.5 1.0 607 0 0 /oradata/eachdb2/index02/index017.dbf 895 0 9.7 1.0 579 0 0 /oradata/eachdb2/index02/index018.dbf 1,129 0 10.3 1.0 638 0 0 /oradata/eachdb2/index02/index019.dbf 871 0 10.2 1.0 892 0 0 /oradata/eachdb2/index02/index020.dbf 1,003 0 9.8 1.0 689 0 0 /oradata/eachdb2/index02/index021.dbf 786 0 10.0 1.0 1,642 0 0 /oradata/eachdb2/index02/index022.dbf 1,213 0 9.0 1.0 948 0 0 /oradata/eachdb2/index02/index033.dbf 1,027 0 9.4 1.0 966 0 0 /oradata/eachdb2/index02/index034.dbf 818 0 9.0 1.0 1,301 0 1 0.0 /oradata/eachdb2/index02/index035.dbf 991 0 9.6 1.0 1,619 0 0 /oradata/eachdb2/index02/index036.dbf 1,086 0 9.8 1.0 1,164 0 1 0.0 /oradata/eachdb2/index02/index037.dbf 1,301 0 9.4 1.0 1,229 0 0 /oradata/eachdb2/index02/index038.dbf 851 0 9.4 1.0 1,270 0 0 /oradata/eachdb2/index02/index039.dbf 658 0 10.3 1.0 996 0 0 /oradata/eachdb2/index02/index040.dbf 560 0 9.9 1.0 1,194 0 0 /oradata/eachdb2/index02/index041.dbf 580 0 9.7 1.0 879 0 0 /oradata/eachdb2/index02/index042.dbf 613 0 9.3 1.0 1,298 0 0 /oradata/eachdb2/index02/index043.dbf 671 0 8.9 1.0 897 0 1 0.0 /oradata/eachdb2/index02/index23.dbf 571 0 8.8 1.0 505 0 0 /oradata/eachdb2/index02/index24.dbf 943 0 9.7 1.0 1,444 0 0 /oradata/eachdb2/index02/index25.dbf 382 0 7.9 1.0 649 0 0 /oradata/eachdb2/index02/index26.dbf 927 0 9.4 1.0 797 0 0 /oradata/eachdb2/index02/index27.dbf 460 0 10.7 1.0 689 0 0 /oradata/eachdb2/index02/index28.dbf 718 0 11.0 1.0 797 0 0 /oradata/eachdb2/index02/index29.dbf 749 0 9.8 1.0 699 0 0 /oradata/eachdb2/index02/index30.dbf File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ 448 0 9.1 1.0 752 0 0 BIDDB_IDX /oradata/eachdb2/index02/index31.dbf 1,131 0 9.5 1.0 1,042 0 0 /oradata/eachdb2/index02/index32.dbf 489 0 8.9 1.0 1,312 0 0 BIDDB_TBL /oradata/eachdb2/table/table001.dbf 2,505 1 11.3 1.3 4,432 1 43 1.9 /oradata/eachdb2/table/table002.dbf 1,795 0 9.5 1.0 1,549 0 0 /oradata/eachdb2/table/table003.dbf 2,079 1 9.7 1.0 2,163 1 0 /oradata/eachdb2/table/table004.dbf 7 0 0.0 1.0 7 0 0 /oradata/eachdb2/table/table005.dbf 10 0 3.0 1.0 7 0 0 /oradata/eachdb2/table/table006.dbf 450 0 9.6 1.0 776 0 0 /oradata/eachdb2/table/table007.dbf 2,973 1 10.2 1.0 976 0 0 /oradata/eachdb2/table/table008.dbf 63 0 10.3 1.0 274 0 0 /oradata/eachdb2/table/table009.dbf 95 0 8.2 1.0 161 0 0 /oradata/eachdb2/table/table010.dbf 1,202 0 9.3 1.2 1,082 0 0 /oradata/eachdb2/table/table011.dbf 1,972 1 9.2 1.1 1,040 0 0 /oradata/eachdb2/table/table012.dbf 1,464 0 9.5 1.0 976 0 2 5.0 /oradata/eachdb2/table/table013.dbf 1,791 0 9.8 1.0 1,712 0 0 /oradata/eachdb2/table/table014.dbf 2,015 1 9.3 1.0 1,997 1 0 /oradata/eachdb2/table/table015.dbf 3,280 1 9.5 1.0 1,585 0 0 /oradata/eachdb2/table/table016.dbf 1,813 1 9.3 1.0 1,767 0 0 /oradata/eachdb2/table/table017.dbf 1,710 0 9.2 1.0 2,173 1 0 /oradata/eachdb2/table/table018.dbf 1,520 0 9.7 1.0 1,050 0 0 /oradata/eachdb2/table/table019.dbf 1,434 0 10.2 1.0 1,310 0 0 /oradata/eachdb2/table/table020.dbf 1,823 1 10.0 1.0 1,214 0 3 10.0 /oradata/eachdb2/table/table021.dbf 1,402 0 10.1 1.0 714 0 2 5.0 /oradata/eachdb2/table/table022.dbf 2,021 1 9.5 1.0 1,050 0 0 /oradata/eachdb2/table/table023.dbf 2,618 1 10.0 1.0 1,637 0 1 10.0 File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ BIDDB_TBL /oradata/eachdb2/table/table024.dbf 2,038 1 9.2 1.0 1,173 0 1 10.0 /oradata/eachdb2/table/table025.dbf 2,161 1 9.4 1.0 1,269 0 1 10.0 /oradata/eachdb2/table/table026.dbf 2,958 1 8.7 1.0 397 0 0 /oradata/eachdb2/table/table027.dbf 2,569 1 10.1 1.0 2,575 1 0 /oradata/eachdb2/table/table028.dbf 1,059 0 9.6 1.0 936 0 0 /oradata/eachdb2/table/table029.dbf 3,956 1 9.1 1.0 1,897 1 0 /oradata/eachdb2/table/table030.dbf 968 0 9.6 1.0 765 0 2 0.0 /oradata/eachdb2/table/table031.dbf 988 0 9.5 1.0 737 0 0 /oradata/eachdb2/table/table032.dbf 1,365 0 9.8 1.0 1,850 1 0 /oradata/eachdb2/table/table033.dbf 1,048 0 9.4 1.0 726 0 1 10.0 /oradata/eachdb2/table/table034.dbf 1,342 0 9.8 1.0 2,481 1 0 /oradata/eachdb2/table/table035.dbf 1,212 0 10.0 1.0 1,073 0 0 /oradata/eachdb2/table/table036.dbf 2,351 1 9.1 1.0 3,332 1 365 267.1 /oradata/eachdb2/table/table037.dbf 2,738 1 9.4 1.0 5,747 2 222 8.1 /oradata/eachdb2/table/table038.dbf 1,571 0 8.8 1.0 3,147 1 204 8.5 /oradata/eachdb2/table/table039.dbf 1,568 0 8.4 1.0 1,951 1 220 6.9 /oradata/eachdb2/table/table040.dbf 1,558 0 8.3 1.0 1,398 0 232 6.2 /oradata/eachdb2/table/table041.dbf 1,179 0 8.5 1.0 921 0 176 7.2 /oradata/eachdb2/table/table042.dbf 935 0 8.3 1.0 455 0 198 8.7 /oradata/eachdb2/table/table043.dbf 924 0 8.7 1.0 685 0 197 9.4 /oradata/eachdb2/table/table044.dbf 962 0 7.7 1.0 245 0 215 6.9 /oradata/eachdb2/table/table045.dbf 769 0 8.0 1.0 369 0 208 7.5 DICT_TS /oradata/eachdb2/system/dict_ts.dbf 14 0 0.0 1.0 7 0 0 EACHPAY_BIG /oradata/eachdb2/table/eachpay_big1.dbf 232 0 9.1 1.0 454 0 0 /oradata/eachdb2/table/eachpay_big2.dbf 181 0 8.0 1.0 279 0 0 File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ EACHPAY_BIG /oradata/eachdb2/table/eachpay_big3.dbf 156 0 9.6 1.0 265 0 0 /oradata/eachdb2/table/eachpay_big4.dbf 203 0 8.7 1.0 297 0 0 /oradata/eachdb2/table/eachpay_big5.dbf 411 0 9.7 1.0 752 0 0 /oradata/eachdb2/table/eachpay_big6.dbf 525 0 10.4 1.0 1,553 0 0 /oradata/eachdb2/table/eachpay_big7.dbf 375 0 10.0 1.0 1,000 0 0 /oradata/eachdb2/table/eachpay_big8.dbf 36 0 0.3 1.0 7 0 0 EACHPAY_IDX /oradata/eachdb2/index/eachpay_idx01.dbf 1,593 0 9.7 1.0 3,115 1 0 /oradata/eachdb2/index/eachpay_idx02.dbf 1,692 0 10.2 1.0 2,688 1 0 /oradata/eachdb2/index/eachpay_idx03.dbf 1,779 0 10.0 1.0 2,809 1 0 EACHPAY_M /oradata/eachdb2/table/eachpay_m1.dbf 358 0 7.4 1.0 39 0 0 MBUS_TS /oradata/eachdb2/table/mbus01.dbf 14 0 0.0 1.0 1,304 0 1,213 3.1 /oradata/eachdb2/table/mbus02.dbf 28 0 0.0 1.0 1,712 0 1,341 3.3 RBS /oradata/eachdb2/undo/rbs01.dbf 7 0 0.0 1.0 47,421 13 1,088 0.5 SYSTEM /oradata/eachdb2/system/system01.dbf 148 0 21.0 4.0 300 0 2 5.0 TEMP_LOCAL /oradata/eachdb2/temp/temp1.tmp 877 0 0.0 25.0 4,026 1 0 TOOLS /oradata/eachdb2/system/tools01.dbf 8 0 1.3 1.0 8 0 0 /oradata/eachdb2/system/tools02.dbf 33 0 0.9 1.0 17 0 0 USERS /oradata/eachdb2/system/user02.dbf 61 0 4.9 1.0 35 0 0 /oradata/eachdb2/system/user03.dbf 77 0 4.8 1.0 26 0 0 /oradata/eachdb2/system/users01.dbf 102 0 7.9 1.0 76 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> Pools D: default pool, K: keep pool, R: recycle pool Free Write Buffer Buffer Consistent Physical Physical Buffer Complete Busy P Gets Gets Reads Writes Waits Waits Waits - ----------- ------------- ----------- ---------- ------- -------- ---------- D 504,986 184,769,181 213,931 230,587 0 0 6,074 ------------------------------------------------------------- Buffer wait Statistics for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (cs) Time (cs) ------------------ ----------- ---------- --------- data block 4,984 12,217 2 undo block 603 38 0 undo header 484 15 0 ------------------------------------------------------------- Enqueue activity for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> ordered by waits desc, gets desc Enqueue Gets Waits ---------- ------------ ---------- TX 226,433 59 ------------------------------------------------------------- Rollback Segment Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->A high value for "Pct Waits" suggests more rollback segments may be required Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ ------------ ------- --------------- -------- -------- -------- 0 13.0 0.00 0 0 0 0 1 85,305.0 0.00 9,323,064 11 0 0 2 54,436.0 0.00 5,395,402 7 0 0 3 65,729.0 0.00 6,698,996 9 0 0 4 62,382.0 0.00 6,244,062 8 0 0 5 55,789.0 0.00 5,506,724 8 0 0 6 58,101.0 0.00 5,815,126 8 0 0 7 53,718.0 0.00 5,391,898 7 0 0 8 55,947.0 0.00 5,503,808 7 0 0 9 56,368.0 0.00 5,448,502 7 0 0 10 53,655.0 0.00 5,323,546 7 0 0 11 55,144.0 0.00 5,475,204 7 0 0 12 53,208.0 0.00 5,276,022 7 0 0 13 72,284.0 0.00 7,384,118 9 0 0 14 75,212.0 0.00 7,606,568 9 0 0 15 54,545.0 0.00 5,268,604 7 0 0 16 53,692.0 0.00 5,273,918 7 0 0 17 55,131.0 0.00 5,505,138 8 0 0 18 53,835.0 0.00 5,453,198 8 0 0 19 57,383.0 0.00 5,662,274 8 0 0 20 54,468.0 0.00 5,509,002 8 0 0 21 53,821.0 0.00 5,274,142 7 0 0 22 54,377.0 0.00 5,454,466 7 0 0 23 54,795.0 0.00 6,175,956 9 0 0 24 56,586.0 0.00 5,645,200 8 0 0 25 66,144.0 0.00 6,679,328 9 0 0 26 63,773.0 0.00 6,363,000 8 0 0 27 54,459.0 0.00 5,432,770 7 0 0 28 55,969.0 0.00 5,445,604 7 0 0 29 64,346.0 0.00 6,636,966 9 0 0 31 56,485.0 0.00 5,553,578 7 0 0 ------------------------------------------------------------- Rollback Segment Storage for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 581,632 17,203 581,632 1 34,594,816 1,048,210 34,594,816 2 36,691,968 1,048,398 36,691,968 3 35,643,392 1,048,244 35,643,392 4 37,740,544 1,048,358 37,740,544 5 34,594,816 1,048,487 34,594,816 6 45,080,576 1,048,550 45,080,576 7 33,546,240 1,048,444 33,546,240 8 35,643,392 1,048,123 35,643,392 9 33,546,240 1,048,444 33,546,240 10 34,594,816 1,048,167 34,594,816 11 44,032,000 1,047,900 44,032,000 12 22,011,904 1,047,901 22,011,904 13 34,594,816 1,048,531 34,594,816 14 53,469,184 1,048,402 53,469,184 15 42,983,424 1,048,500 42,983,424 16 41,934,848 1,048,545 41,934,848 17 34,594,816 1,048,487 34,594,816 18 49,274,880 1,048,339 49,274,880 19 33,546,240 1,048,276 33,546,240 20 34,594,816 1,048,017 34,594,816 21 33,546,240 1,048,457 33,546,240 22 24,109,056 1,048,483 24,109,056 23 39,837,696 1,049,833 39,837,696 24 47,177,728 1,048,383 47,177,728 25 33,546,240 1,048,591 33,546,240 26 34,594,816 1,048,503 34,594,816 27 34,594,816 1,048,072 34,594,816 28 20,963,328 1,048,334 20,963,328 29 34,594,816 1,048,306 34,594,816 31 33,546,240 1,048,414 33,546,240 ------------------------------------------------------------- Latch Activity for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->"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 Pct Get Get Slps NoWait NoWait Latch Name Requests Miss /Miss Requests Miss ----------------------------- -------------- ------ ------ ------------ ------ Token Manager 1,813 0.0 0 active checkpoint queue latch 5,545 0.0 0 archive control 12 0.0 0 archive process latch 11 0.0 0 cache buffer handles 2,302,850 0.1 0.0 0 cache buffers chains 480,477,131 0.2 0.0 401,003 0.1 cache buffers lru chain 960,545 0.0 0.1 365,302 0.0 channel handle pool latch 327 0.0 0 channel operations parent lat 491 0.0 0 checkpoint queue latch 880,660 0.0 0.1 0 dml lock allocation 626,371 0.1 0.0 0 enqueue hash chains 14,725,932 0.2 0.0 0 enqueues 22,585,089 1.1 0.0 0 event group latch 164 0.0 0 file number translation table 84 0.0 0 global transaction 9,426,251 0.0 0.0 0 global tx free list 1,553,173 0.1 0.0 0 global tx hash mapping 6,731,346 0.1 0.0 0 job_queue_processes parameter 55 0.0 0 ktm global data 12 0.0 0 latch wait list 133 2.3 0.0 128 0.0 library cache 9,962,528 0.2 0.0 0 list of block allocation 423,785 0.0 0.0 0 loader state object freelist 584 0.0 0 longop free list 12 0.0 0 messages 1,029,985 0.2 0.0 0 multiblock read objects 2,156 0.0 0 ncodef allocation latch 55 0.0 0 process allocation 164 0.0 164 0.0 process group creation 327 0.0 0 redo allocation 2,193,752 0.1 0.0 0 redo writing 1,039,241 0.8 0.0 0 row cache objects 71,240 0.0 0.0 0 sequence cache 374,717 0.0 0.0 0 session allocation 1,942,744 0.1 0.0 0 session idle bit 17,664,325 0.0 0.0 0 session switching 55 0.0 0 shared pool 29,327 0.0 0 sort extent pool 516 0.0 0 transaction allocation 1,966,929 0.1 0.0 0 transaction branch allocation 5,356,669 0.3 0.0 0 undo global data 2,143,523 0.1 0.0 0 user lock 650 0.0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers chains 480,477,131 934,745 4,356 930419/4306/ 17/3/0 enqueues 22,585,089 243,769 366 243403/366/0 /0/0 enqueue hash chains 14,725,932 28,017 215 27817/186/13 /1/0 library cache 9,962,528 18,927 230 18799/57/49/ 22/0 transaction branch allocat 5,356,669 16,491 73 16418/73/0/0 /0 redo writing 1,039,241 8,593 55 8538/55/0/0/ 0 global tx hash mapping 6,731,346 6,647 40 6607/40/0/0/ 0 session idle bit 17,664,325 3,526 43 3483/43/0/0/ 0 redo allocation 2,193,752 2,714 15 2699/15/0/0/ 0 session allocation 1,942,744 2,656 19 2637/19/0/0/ 0 transaction allocation 1,966,929 1,972 16 1956/16/0/0/ 0 messages 1,029,985 1,909 17 1892/17/0/0/ 0 undo global data 2,143,523 1,891 5 1886/5/0/0/0 cache buffer handles 2,302,850 1,286 7 1279/7/0/0/0 global tx free list 1,553,173 883 2 881/2/0/0/0 dml lock allocation 626,371 315 2 313/2/0/0/0 cache buffers lru chain 960,545 280 23 257/23/0/0/0 checkpoint queue latch 880,660 152 10 142/10/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- ------- cache buffer handles kcbzgs 0 5 4 cache buffer handles kcbzfs 0 2 3 cache buffers chains kcbgtcr: kslbegin 0 3,517 2,351 cache buffers chains kcbrls: kslbegin 0 672 1,546 cache buffers chains kcbchg: kslbegin: bufs not 0 111 68 cache buffers chains kcbgcur: kslbegin 0 28 15 cache buffers chains kcbzwb 0 10 3 cache buffers chains kcbget: pin buffer 0 4 5 cache buffers chains kcbchg: kslbegin: call CR 0 3 273 cache buffers chains kcbso1: set no access 0 3 5 cache buffers chains kcbnlc 0 2 77 cache buffers chains kcbbxsv 0 1 0 cache buffers lru chain kcbbiop: lru scan 0 22 0 cache buffers lru chain kcbzgb: multiple sets nowa 0 1 0 checkpoint queue latch kcbk0rrd: update recovery 0 9 0 checkpoint queue latch kcbbwthc: thread checkpoin 0 1 2 dml lock allocation ktaiam 0 1 1 dml lock allocation ktaidm 0 1 1 enqueue hash chains ksqrcl 0 104 43 enqueue hash chains ksqgtl3 0 90 159 enqueue hash chains ksqcnl 0 20 13 enqueue hash chains ksqcmi: kslgpl 0 1 0 enqueues ksqgtl2 0 157 76 enqueues ksqgel: create enqueue 0 112 145 enqueues ksqdel 0 49 4 enqueues ksqrcl 0 41 109 enqueues ksqies 0 7 32 global tx free list k2gfegte 0 2 1 global tx hash mapping K2GTBlatch 0 40 40 library cache kglsca: parent 0 86 0 library cache kglic 0 79 0 library cache kglpnal: child: alloc spac 0 23 21 library cache kglpnal: child: before pro 0 15 76 library cache kglhdgn: child: 0 7 1 library cache kglhdgc: child: 0 6 4 library cache kgldte: child 0 0 3 100 library cache kglpnc: child 0 3 6 library cache kglget: child: KGLDSBRD 0 2 0 library cache kgllkdl: child: free pin 0 2 1 library cache kglupc: child 0 2 17 library cache kgllkdl: child: cleanup 0 1 3 messages ksarcv: after wait 0 12 1 messages ksaamb: after wakeup 0 4 16 messages ksarcv 0 1 0 redo allocation kcrfwr: redo allocation 0 12 13 redo allocation kcrfwi: before write 0 2 1 redo allocation kcrfwi: more space 0 1 1 redo writing kcrfsr 0 48 1 redo writing kcrfss 0 5 53 redo writing kcrfwi: after write 0 2 0 session allocation ksuxds: not user session 0 8 11 session allocation ksuxds: KSUSFCLC not set 0 6 0 session allocation ksucri 0 5 8 Latch Miss Sources for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- ------- session idle bit ksupuc: clear busy 0 30 22 session idle bit ksuxds 0 8 2 session idle bit ksupuc: set busy 0 5 19 transaction allocation ktcxba 0 10 11 transaction allocation ktcdso 0 6 5 transaction branch alloc ktcbba 0 23 14 transaction branch alloc ktcsbr 0 19 12 transaction branch alloc ktcdbr 0 15 6 transaction branch alloc ksupuc 0 9 32 transaction branch alloc ktcbod 0 7 9 undo global data ktubnd 0 2 1 undo global data ktudax: KSLBEGIN 0 1 0 undo global data ktudba: KSLBEGIN 0 1 4 undo global data ktudnx: KSLBEGIN 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->"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 Pct Cache Requests Miss Requests Miss Req Usage SGA ---------------------- ------------ ------ -------- ----- -------- ------ ---- dc_constraints 0 0 0 8 67 dc_database_links 0 0 0 0 0 dc_files 1,454 0.0 0 0 121 95 dc_free_extents 252 0.0 0 0 23 85 dc_global_oids 0 0 0 0 0 dc_histogram_data 0 0 0 238 100 dc_histogram_data_valu 0 0 0 50 93 dc_histogram_defs 0 0 0 1,748 99 dc_object_ids 1 0.0 0 0 527 99 dc_objects 164 0.0 0 0 744 100 dc_outlines 0 0 0 0 0 dc_profiles 163 0.0 0 0 1 13 dc_rollback_segments 768 0.0 0 0 33 80 dc_segments 14 0.0 0 4 460 99 dc_sequence_grants 0 0 0 0 0 dc_sequences 6,085 0.0 0 6,085 32 80 dc_synonyms 0 0 0 77 93 dc_tablespace_quotas 14 0.0 0 14 12 80 dc_tablespaces 4,261 0.0 0 0 16 89 dc_used_extents 0 0 0 11 65 dc_user_grants 1,312 0.0 0 0 16 40 dc_usernames 324 0.0 0 0 18 95 dc_users 1,889 0.0 0 0 17 81 ifs_acl_cache_entries 0 0 0 0 0 ------------------------------------------------------------- Library Cache Activity for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 349 0.0 349 0.0 0 0 CLUSTER 0 0 0 0 INDEX 0 0 0 0 OBJECT 0 0 0 0 PIPE 0 0 0 0 SQL AREA 7,375 0.0 3,430,389 -0.0 0 0 TABLE/PROCEDURE 242 0.0 317,652 0.0 0 0 TRIGGER 21,879 0.0 21,879 0.0 0 0 ------------------------------------------------------------- SGA Memory Summary for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 8,053,063,680 Fixed Size 102,076 Redo Buffers 278,528 Variable Size 530,587,648 ---------------- sum 8,584,031,932 ------------------------------------------------------------- SGA breakdown difference for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 Pool Name Begin value End value Difference ----------- ------------------------ -------------- -------------- ----------- java pool free memory 32,768 32,768 0 large pool free memory 10,240,000 10,240,000 0 shared pool DML locks 1,680,000 1,680,000 0 shared pool KGFF heap 89,760 89,760 0 shared pool KGK heap 13,096 13,096 0 shared pool KQLS heap 2,949,672 2,949,672 0 shared pool PL/SQL DIANA 1,266,888 1,266,888 0 shared pool PL/SQL MPCODE 1,220,504 1,220,504 0 shared pool PL/SQL SOURCE 7,384 7,384 0 shared pool PLS non-lib hp 2,136 2,136 0 shared pool State objects 1,645,760 1,645,760 0 shared pool db_block_buffers 204,472,320 204,472,320 0 shared pool db_block_hash_buckets 32,965,296 32,965,296 0 shared pool dictionary cache 2,313,344 2,313,344 0 shared pool distributed_transactions 992,224 992,224 0 shared pool enqueue_resources 1,062,880 1,062,880 0 shared pool errors 251,064 251,064 0 shared pool event statistics per ses 3,072,720 3,072,720 0 shared pool fixed allocation callbac 4,872 4,872 0 shared pool free memory 165,245,200 165,237,736 -7,464 shared pool ktlbk state objects 1,340,000 1,340,000 0 shared pool library cache 37,725,048 37,740,840 15,792 shared pool miscellaneous 7,793,168 7,784,840 -8,328 shared pool processes 857,600 857,600 0 shared pool sessions 2,152,320 2,152,320 0 shared pool sql area 44,976,408 44,976,408 0 shared pool state objects 781,192 781,192 0 shared pool table columns 37,216 37,216 0 shared pool table definiti 12,472 12,472 0 shared pool transaction_branches 1,536,000 1,536,000 0 shared pool transactions 3,800,000 3,800,000 0 shared pool trigger defini 8,088 8,088 0 shared pool trigger inform 992 992 0 shared pool trigger source 1,984 1,984 0 db_block_buffers 8,053,063,680 8,053,063,680 0 fixed_sga 102,076 102,076 0 log_buffer 256,000 256,000 0 ------------------------------------------------------------- init.ora Parameters for DB: EACHDB2 Instance: eachdb2 Snaps: 7413 -7414 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _sqlexec_progression_cost 0 always_anti_join HASH background_dump_dest /export/home/oracle/admin/eachdb2 compatible 8.1.7 control_files /oradata/eachdb2/ctl/eachdb2ctl1. core_dump_dest /export/home/oracle/admin/eachdb2 cursor_sharing FORCE cursor_space_for_time TRUE db_block_buffers 983040 db_block_size 8192 db_domain eachnet.com db_file_direct_io_count 128 db_file_multiblock_read_count 16 db_name eachdb2 distributed_transactions 2000 instance_name eachdb2 java_pool_size 32768 job_queue_interval 60 job_queue_processes 2 large_pool_size 10240000 log_archive_dest /export/home/oracle/admin/eachdb2 log_archive_format eachdb2_arch_%t_%s.arc log_archive_start TRUE log_buffer 256000 log_checkpoint_interval 1000000 log_checkpoint_timeout 180000 max_enabled_roles 30 open_cursors 800 open_links 4 optimizer_mode FIRST_ROWS os_authent_prefix parallel_max_servers 40 processes 800 query_rewrite_enabled TRUE rollback_segments RBS1, RBS2, RBS3, RBS4, RBS5, RBS service_names eachdb2.eachnet.com session_cached_cursors 200 shared_pool_size 262144000 sort_area_size 1048576 timed_statistics TRUE transactions 2500 user_dump_dest /export/home/oracle/admin/eachdb2 utl_file_dir /export/home/oracle/search ------------------------------------------------------------- End of Report -- Attached file included as plaintext by Ecartis -- -- File: spreport-after-highio.txt STATSPACK report for DB Name DB Id Instance Inst Num Release OPS Host ------------ ----------- ------------ -------- ----------- --- ------------ EACHDB2 3249187745 eachdb2 1 8.1.7.2.0 NO main-db3 Snap Id Snap Time Sessions ------- ------------------ -------- Begin Snap: 7414 18-Feb-04 23:00:17 544 End Snap: 7415 19-Feb-04 00:00:17 544 Elapsed: 60.00 (mins) Cache Sizes ~~~~~~~~~~~ db_block_buffers: 983040 log_buffer: 256000 db_block_size: 8192 shared_pool_size: 262144000 Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 111,519.16 1,746.77 Logical reads: 56,690.06 887.96 Block changes: 790.08 12.38 Physical reads: 47.73 0.75 Physical writes: 151.26 2.37 User calls: 2,005.56 31.41 Parses: 399.37 6.26 Hard parses: 0.00 0.00 Sorts: 76.70 1.20 Logons: 0.11 0.00 Executes: 1,090.28 17.08 Transactions: 63.84 % Blocks changed per Read: 1.39 Recursive Call %: 11.93 Rollback per transaction %: 30.37 Rows per Sort: 98.91 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.92 In-memory Sort %: 99.98 Library Hit %: 100.01 Soft Parse %: 100.00 Execute to Parse %: 63.37 Latch Hit %: 99.83 Parse CPU to Parse Elapsd %: 92.55 % Non-Parse CPU: 99.99 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 68.24 68.27 % SQL with executions>1: 74.15 74.15 % Memory for SQL w/exec>1: 89.78 89.79 Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- enqueue 18,120 450,681 58.18 db file sequential read 150,866 137,470 17.75 log file sync 169,979 70,695 9.13 db file parallel write 9,759 48,311 6.24 log file parallel write 172,140 36,595 4.72 ------------------------------------------------------------- Wait Events for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ enqueue 18,120 90 450,681 249 0.1 db file sequential read 150,866 0 137,470 9 0.7 log file sync 169,979 14 70,695 4 0.7 db file parallel write 9,759 0 48,311 50 0.0 log file parallel write 172,140 0 36,595 2 0.7 buffer busy waits 7,324 11 11,263 15 0.0 SQL*Net more data to client 649,959 0 7,138 0 2.8 latch free 2,804 2,759 5,588 20 0.0 log file sequential read 13,393 0 3,374 3 0.1 direct path write 2,319 0 1,152 5 0.0 control file sequential read 4,867 0 594 1 0.0 db file scattered read 433 0 580 13 0.0 direct path read 2,229 0 425 2 0.0 refresh controlfile command 920 0 263 3 0.0 control file parallel write 1,313 0 262 2 0.0 inactive transaction branch 4 0 135 338 0.0 log file switch completion 14 0 57 41 0.0 file open 3,516 0 48 0 0.0 LGWR wait for redo copy 1,129 2 13 0 0.0 single-task message 24 0 6 3 0.0 log file single write 10 0 5 5 0.0 switch logfile command 1 0 4 40 0.0 log buffer space 2 0 2 10 0.0 SQL*Net break/reset to clien 80 0 1 0 0.0 file identify 25 0 0 0 0.0 buffer deadlock 24 24 0 0 0.0 db file single write 5 0 0 0 0.0 SQL*Net message from client 7,221,780 0 ########### 166 31.4 SQL*Net message to client 7,221,762 0 1,980 0 31.4 SQL*Net more data from clien 21,793 0 225 0 0.1 ------------------------------------------------------------- Background Wait Events for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ db file parallel write 9,759 0 48,311 50 0.0 log file parallel write 172,203 0 36,605 2 0.7 log file sequential read 12,809 0 3,261 3 0.1 control file parallel write 1,309 0 259 2 0.0 control file sequential read 2,134 0 236 1 0.0 direct path read 1,625 0 161 1 0.0 latch free 28 28 57 20 0.0 file open 3,448 0 48 0 0.0 db file sequential read 19 0 16 8 0.0 db file scattered read 10 0 16 16 0.0 LGWR wait for redo copy 1,127 2 13 0 0.0 enqueue 1 0 9 90 0.0 log file single write 10 0 5 5 0.0 direct path write 600 0 2 0 0.0 file identify 22 0 0 0 0.0 db file single write 5 0 0 0 0.0 rdbms ipc message 322,293 3,003 2,083,476 65 1.4 pmon timer 1,169 1,169 360,052 3080 0.0 smon timer 11 11 337,931 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Buffer Gets Threshold: 30000 -> 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 Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 37,221,874 18,620 1,999.0 18.2 594340633 select CRIDIT_ID ,CRIDIT_ACCOUNT ,TRUST_CLASS ,(((((TO_CHAR(TIME _DTM,'YYYY')||'年')||TO_CHAR(TIME_DTM,'MM'))||'月')||TO_CHAR(TIM E_DTM,'DD'))||'日') A_TIME_DTM ,PRODUCT_ID ,TITLE ,PRICE ,CRITIC _REMARK ,JUDGE_STATE ,nvl(CRITIC_COMMENT,' ') A_CRITIC_COMMENT , nvl(MSG_CONTENT,' ') A_MSG_CONTENT ,nvl(MSG_STATE,0) A_MSG_STATE 23,290,976 18,622 1,250.7 11.4 3065721488 select count(*) into :b0 from USERJUDGE A ,USERPRIVATEINFO B w here ((((((A.JUDGE_ID>0 and A.CRITIC_ID=B.USER_ID) and A.JUDGE_S TATE not in (0,88)) and A.ACCEPTER_ID=:b1) and A.CRITIC_IDENTIT Y in (:b2,:b3)) and A.ACCP_VISIBLE=1) and A.CRITIC_REMARK in (:b 4,:b5,:b6)) order by A.CRITIC_TIME_DTM desc 15,762,940 18,628 846.2 7.7 2353964393 select count(*) into :b0 from USERJUDGE where ((CRITIC_ID=:b1 and (:b2 is null or RECORD_TIME_DTM>=TO_DATE(:b2,:b4))) and (:b 5 is null or RECORD_TIME_DTM<=TO_DATE(:b5,:b4))) 15,762,899 18,630 846.1 7.7 69757181 select count(*) into :b0 from USERJUDGE where (((CRITIC_ID=:b1 and JUDGE_STATE not in (:b2,:b3)) and (:b4 is null or RECORD_ TIME_DTM>=TO_DATE(:b4,:b6))) and (:b7 is null or RECORD_TIME_DT M<=TO_DATE(:b7,:b6))) 14,872,443 37,611 395.4 7.3 2203634836 select CRITIC_IDENTITY ,CRITIC_REMARK ,count(*) from UserJudge where (((ACCEPTER_ID=:b0 and JUDGE_STATE=:b1) and (SYSDATE-CRIT IC_TIME_DTM)<=:b2) and ACCP_VISIBLE=1) group by CRITIC_IDENTITY, CRITIC_REMARK 14,258,185 7,398 1,927.3 7.0 152563559 select A_JUDGE_ID ,A_AUCTION_ID ,A_PRODUCT_ID ,A_TITLE ,A_AUCTIO N_TYPE ,A_PRICE ,(((((TO_CHAR(A_AUCTION_DATE,'YYYY')||'年')||TO_ CHAR(A_AUCTION_DATE,'MM'))||'月')||TO_CHAR(A_AUCTION_DATE,'DD')) ||'日') AUCTION_DATE ,A_ACCEPT_ID ,C_ACCEPT_ACCOUNT ,C_TRUST_CLA SS ,B_JUDGE_STATE ,A_CRITIC_IDENTITY ,D_CHARGE_TYPE ,D_CHARGE_ID 7,436,220 18,803 395.5 3.6 2579060820 select CRITIC_IDENTITY ,CRITIC_REMARK ,count(*) from UserJudge where ((ACCEPTER_ID=:b0 and JUDGE_STATE=:b1) and ACCP_VISIBLE=1 ) group by CRITIC_IDENTITY,CRITIC_REMARK 7,427,639 18,620 398.9 3.6 3362851951 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S YSDATE-7)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 7,427,624 18,620 398.9 3.6 2408713440 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S SQL ordered by Gets for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Buffer Gets Threshold: 30000 -> 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 Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ YSDATE-30)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 7,426,818 18,619 398.9 3.6 2534678434 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S YSDATE-180)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 6,671,661 12,536 532.2 3.3 2291175285 SELECT /*+ index(a,IDX2_USERJUDGE) */TO_CHAR(A.JUDGE_ID) || C HR(9) || TO_CHAR(A.AUCTION_ID) || CHR(9) || TO_CHAR(A.PRODUCT _ID) || CHR(9) || RTRIM(A.TITLE) || CHR(9) || RTRIM(A.AUCTIO N_TYPE) || CHR(9) || TO_CHAR(A.CRITIC_ID) || CHR(9) || RTRIM (B.USER_ACCOUNT) || CHR(9) || TO_CHAR(B.USER_TRUST_SCORE) || 6,040,907 18,804 321.3 3.0 54290869 select A.CRITIC_IDENTITY ,A.CRITIC_REMARK ,count(*) from UserJ udge A ,USERPRIVATEINFO B where ((((A.CRITIC_ID=B.USER_ID and B. USER_TYPE>=:b0) and A.ACCEPTER_ID=:b1) and A.JUDGE_STATE=:b2) an d A.ACCP_VISIBLE=1) group by A.CRITIC_IDENTITY,A.CRITIC_REMARK 4,601,948 20,078 229.2 2.3 1607768868 select count(*) into :b0 from USERJUDGE where ((((JUDGE_ID>0 a nd CRITIC_ID=:b1) and JUDGE_STATE=:b2) and RECORD_TIME_DTM>=(SYS DATE-:b3)) and RECORD_TIME_DTM<(SYSDATE-:b4)) 3,780,270 9,318 405.7 1.9 2641148933 select count(*) into :b0 from USERJUDGE where ((((JUDGE_ID>0 a nd CRITIC_ID=:b1) and JUDGE_STATE=:b2) and CRITIC_IDENTITY=:b3) and RECORD_TIME_DTM>=(SYSDATE-:b4)) 3,457,321 18,803 183.9 1.7 3786195604 select A.CRITIC_IDENTITY ,A.CRITIC_REMARK ,count(*) from UserJ udge A ,USERPRIVATEINFO B where ((((A.CRITIC_ID=B.USER_ID and B. USER_TYPE between :b0 and :b1) and A.ACCEPTER_ID=:b2) and A.JUDG E_STATE in (:b3,:b4)) and A.ACCP_VISIBLE=1) group by A.CRITIC_ID ENTITY,A.CRITIC_REMARK 1,792,015 214,866 8.3 0.9 1640050967 select STD.STD_StatementDetailID ,STD.STD_BeginningBalance ,STD. STD_EndingBalance ,FT.FT_FundTypeDesc ,FT.FT_FundTypeID into :b0 ,:b1,:b2,:b3,:b4 from STD_StatementDetail STD ,FT_FundType FT w here ((STD.ST_StatementID=:b5 and STD.FT_FundTypeID=:b4) and FT. FT_FundTypeID=:b4) ------------------------------------------------------------- SQL ordered by Reads for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Disk Reads Threshold: 1000 Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 17,787 18,620 1.0 10.4 594340633 select CRIDIT_ID ,CRIDIT_ACCOUNT ,TRUST_CLASS ,(((((TO_CHAR(TIME _DTM,'YYYY')||'年')||TO_CHAR(TIME_DTM,'MM'))||'月')||TO_CHAR(TIM E_DTM,'DD'))||'日') A_TIME_DTM ,PRODUCT_ID ,TITLE ,PRICE ,CRITIC _REMARK ,JUDGE_STATE ,nvl(CRITIC_COMMENT,' ') A_CRITIC_COMMENT , nvl(MSG_CONTENT,' ') A_MSG_CONTENT ,nvl(MSG_STATE,0) A_MSG_STATE 11,089 100,021 0.1 6.5 4017178300 select JUDGE_STATE into :b0 from USERJUDGE where ((((JUDGE_ID>0 and CRITIC_ID=:b1) and ACCEPTER_ID=:b2) and AUCTION_ID=:b3) and AUCTION_TYPE=:b4) 10,686 19,581 0.5 6.2 479371298 SELECT USER_ID, USER_TYPE, USER_STATUS, USER_EACHPAY, USER_REGIO N_CODE FROM USERPRIVATEINFO WHERE USER_ACCOUNT = LOWER(:user_acc ount) OR USER_EMAIl = LOWER(:user_account) 8,667 404 21.5 5.0 1907026973 SELECT NVL(TX_TRANSACTIONID,0),NVL(TO_CHAR(NVL(TX_TRANSACTIONPOS TDATE, NULL ),'YYYY-MM-DD HH24:MI:SS'),' '),NVL(TO_CHAR(NVL(TX_T RANSACTIONSETTLEMENTDATE, NULL ),'YYYY-MM-DD HH24:MI:SS'),' '),N VL(TXT_TRANSACTIONTYPEID,0),NVL(TX_TRANSACTIONDESC,' '),NVL(TX_S TATUS,0),NVL(TX_TRANSACTIONAMOUNT,0),NVL(TX_TRANSACTIONSETTLEMEN 6,948 42,330 0.2 4.0 593732686 select max(ST_STATEMENTDATEYYYYMM) into :b0 from ST_Statement w here PA_AccountID=:b1 6,556 17,244 0.4 3.8 1507661013 select STATUS into :b0 from USEREVENTCOLLATE where (USER_ID=:b1 and EVENT_ID=:b2) 5,131 44,133 0.1 3.0 940098683 select nvl(A.user_account,' ') ,nvl(A.USER_TYPE,0) ,NVL(A.USER_E MAIL,' ') ,NVL(B.USER_CITY,' ') ,NVL(B.ADDRESS_OFFICE,' ') ,NVL( B.ZIPCODE_OFFICE,' ') ,NVL(B.ADDRESS_HOME,' ') ,NVL(B.ZIPCODE_HO ME,' ') ,NVL(B.PHONE_OFFICE,' ') ,NVL(B.PHONE_HOME,' ') ,NVL(B.P HONE_MOBILE,' ') ,NVL(B.PAGER,' ') ,NVL(B.HOMEPAGE,' ') ,NVL(B.I 4,972 17,443 0.3 2.9 1180587233 SELECT NVL(PHONE_MOBILE, :"SYS_B_0"), NVL(TO_NUMBER(TO_CHAR(SYSD ATE, :"SYS_B_1") - TO_CHAR(BIRTHDAY, :"SYS_B_2")), :"SYS_B_3"), NVL(CAREER, :"SYS_B_4"), NVL(INCOME, :"SYS_B_5"), NVL(EDUCATION, :"SYS_B_6"), NVL(SEX, :"SYS_B_7") FROM USERALLINFO WHERE USER_I D = :iuser_id 4,431 16,775 0.3 2.6 2587615371 insert into TXD_TransactionDetail (TXD_TransactionDetailID,TXD_T ransactionDetailPostDate,TXD_TransactionDetailAmount,TXD_Beginni ngBalance,TXD_EndingBalance,TX_TransactionID,AB_AccountBalanceID ,UL_LoginName) values (:b0,SYSDATE,:b1,:b2,:b3,:b4,:b5,:b6) 4,185 232,838 0.0 2.4 1258347700 select count(*) into :b0 from REALNAMEVERIFYINFO where (USER_I D=:b1 and ITEM_STATUS in (1,7,5)) SQL ordered by Reads for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Disk Reads Threshold: 1000 Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 3,753 5,615 0.7 2.2 3543481043 insert into TX_Transaction (TX_TransactionID,TX_TransactionDate, TX_TransactionPostDate,TX_TransactionSettlementDate,TX_Reference Number1,TX_ReferenceNumber2,TX_ReferenceString1,TX_ReferenceStri ng2,TX_ReferenceString3,TX_ReferenceString4,TX_TransactionDesc,T X_TransactionAmount,TX_TransactionSettlementAmount,TX_InvoiceIss 3,311 18,620 0.2 1.9 3362851951 select CRITIC_REMARK ,count(*) into :b0,:b1 from UserJudge whe re (((ACCEPTER_ID=:b2 and JUDGE_STATE=1) and CRITIC_TIME_DTM>=(S YSDATE-7)) and ACCP_VISIBLE=1) group by CRITIC_REMARK 3,242 214,866 0.0 1.9 1640050967 select STD.STD_StatementDetailID ,STD.STD_BeginningBalance ,STD. STD_EndingBalance ,FT.FT_FundTypeDesc ,FT.FT_FundTypeID into :b0 ,:b1,:b2,:b3,:b4 from STD_StatementDetail STD ,FT_FundType FT w here ((STD.ST_StatementID=:b5 and STD.FT_FundTypeID=:b4) and FT. FT_FundTypeID=:b4) 3,205 20,078 0.2 1.9 1607768868 select count(*) into :b0 from USERJUDGE where ((((JUDGE_ID>0 a nd CRITIC_ID=:b1) and JUDGE_STATE=:b2) and RECORD_TIME_DTM>=(SYS DATE-:b3)) and RECORD_TIME_DTM<(SYSDATE-:b4)) 2,976 1,686 1.8 1.7 3414358947 insert into TXI_TRANSACTIONINFO (CT_CATEGORYID,TXI_TRANSACTIONIN FOID,TXI_CATEGORYNAME,SR_SERVICEID,TXI_ONSHELLTYPEID,PR_PRODUCTI D,TXI_PRODUCTDESC,TXI_TRANSACTIONAMOUNT,TXI_TRANSACTIONREALAMOUN T,TXI_GMSAMOUNT,PA_ACCOUNTID,TXI_TRANSACTIONDATE,TXI_REFERENCENU MBER1,TXI_REFERENCENUMBER2,TXI_REFERENCENUMBER3,TXI_REFERENCENUM 2,901 19,860 0.1 1.7 2018138957 select UL_STATUS ,LOWER(UL_PASSWORD) ,ULT_USERLOGINTYPEID ,UL_LO GINID into :b0,:b1,:b2,:b3 from UL_UserLogin where UL_LOGINNAME =:b4 2,600 7,398 0.4 1.5 152563559 select A_JUDGE_ID ,A_AUCTION_ID ,A_PRODUCT_ID ,A_TITLE ,A_AUCTIO N_TYPE ,A_PRICE ,(((((TO_CHAR(A_AUCTION_DATE,'YYYY')||'年')||TO_ CHAR(A_AUCTION_DATE,'MM'))||'月')||TO_CHAR(A_AUCTION_DATE,'DD')) ||'日') AUCTION_DATE ,A_ACCEPT_ID ,C_ACCEPT_ACCOUNT ,C_TRUST_CLA ------------------------------------------------------------- SQL ordered by Executions for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Executions Threshold: 100 Executions Rows Processed Rows per Exec Hash Value ------------ ---------------- ---------------- ------------ 232,838 268,279 1.2 1258347700 select count(*) into :b0 from REALNAMEVERIFYINFO where (USER_I D=:b1 and ITEM_STATUS in (1,7,5)) 232,835 267,884 1.2 1650541524 select DECODE(SUCCESS_TIME,null ,' ',(((((TO_CHAR(SUCCESS_TIME,: b0)||:b1)||TO_CHAR(SUCCESS_TIME,:b2))||:b3)||TO_CHAR(SUCCESS_TIM E,:b4))||:b5)) into :b6 from UserStatisInfo where USER_ID=:b7 232,825 134,239 0.6 1405787545 select (((((((TO_CHAR(CHECK_DATE,:b0)||:b1)||TO_CHAR(CHECK_DATE, :b2))||:b3)||TO_CHAR(CHECK_DATE,:b4))||:b5)||CHR(9))||RTRIM(ITEM _TYPE)) into :b6 from USERVERIFYINFO where USER_ID=:b7 232,823 268,230 1.2 3035567900 select (((((((((((((RTRIM(USER_ACCOUNT)||CHR(9))||TO_CHAR(USER_R EGISTER_DATE,:b0))||:b1)||TO_CHAR(USER_REGISTER_DATE,:b2))||:b3) ||TO_CHAR(USER_REGISTER_DATE,:b4))||:b5)||CHR(9))||TO_CHAR(USER_ TRUST_CLASS))||CHR(9))||TO_CHAR(USER_TRUST_SCORE))||CHR(9))||TO_ CHAR(USER_STATUS)) ,USER_STORE_GRADE ,USER_VERIFY_TYPE ,USER_TYP 214,866 292,703 1.4 1640050967 select STD.STD_StatementDetailID ,STD.STD_BeginningBalance ,STD. STD_EndingBalance ,FT.FT_FundTypeDesc ,FT.FT_FundTypeID into :b0 ,:b1,:b2,:b3,:b4 from STD_StatementDetail STD ,FT_FundType FT w here ((STD.ST_StatementID=:b5 and STD.FT_FundTypeID=:b4) and FT. FT_FundTypeID=:b4) 155,105 189,766 1.2 140681161 select RTRIM(LOWER(:b0)) into :b0 from DUAL 100,021 100,060 1.0 4017178300 select JUDGE_STATE into :b0 from USERJUDGE where ((((JUDGE_ID>0 and CRITIC_ID=:b1) and ACCEPTER_ID=:b2) and AUCTION_ID=:b3) and AUCTION_TYPE=:b4) 54,837 54,810 1.0 1942863705 select AB_Balance into :b0 from AB_AccountBalance where (PA_Acc ountID=:b1 and FT_FundTypeID=:b2) 52,236 52,871 1.0 1173476487 select count(*) into :b0 from US_UserSession where US_SESSIONN AME=:b1 44,133 44,142 1.0 940098683 select nvl(A.user_account,' ') ,nvl(A.USER_TYPE,0) ,NVL(A.USER_E MAIL,' ') ,NVL(B.USER_CITY,' ') ,NVL(B.ADDRESS_OFFICE,' ') ,NVL( B.ZIPCODE_OFFICE,' ') ,NVL(B.ADDRESS_HOME,' ') ,NVL(B.ZIPCODE_HO ME,' ') ,NVL(B.PHONE_OFFICE,' ') ,NVL(B.PHONE_HOME,' ') ,NVL(B.P HONE_MOBILE,' ') ,NVL(B.PAGER,' ') ,NVL(B.HOMEPAGE,' ') ,NVL(B.I 42,330 55,506 1.3 593732686 select max(ST_STATEMENTDATEYYYYMM) into :b0 from ST_Statement w here PA_AccountID=:b1 SQL ordered by Executions for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Executions Threshold: 100 Executions Rows Processed Rows per Exec Hash Value ------------ ---------------- ---------------- ------------ 42,327 55,506 1.3 640575081 select ST_STATEMENTID into :b0 from ST_Statement where (ST_STAT EMENTDATEYYYYMM=:b1 and PA_AccountID=:b2) 42,256 55,433 1.3 4107473742 select PA_AccountID into :b0 from UL_UserLogin where UL_LoginNa me=:b1 42,114 48,786 1.2 4222868888 select ST_StatementID ,ST_Status ,NVL(TO_CHAR(NVL(ST_LastEmailDa te,null ),'YYYY-MM-DD HH24:MI:SS'),' ') ,NVL(TO_CHAR(NVL(ST_Last FileDate,null ),'YYYY-MM-DD HH24:MI:SS'),' ') into :b0,:b1,:b2,: b3 from ST_Statement a where (PA_AccountID=:b4 and ST_STATEMENT DATEYYYYMM=:b5) 42,111 55,290 1.3 3681105500 select TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM')) into :b0 from DUAL 42,105 807 0.0 2028936444 update ST_Statement set ST_EndingDateYYYYMMDD=TO_NUMBER(TO_CHAR (SYSDATE,'YYYYMMDD')),ST_Status=:b0 where (ST_STATEMENTID=:b1 an d ST_EndingDateYYYYMMDD<>TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD'))) 37,743 37,722 1.0 1398313222 update STD_StatementDetail set STD_EndingBalance=:b0 where (ST_ StatementID=:b1 and FT_FundTypeID=:b2) 37,611 87,207 2.3 2203634836 select CRITIC_IDENTITY ,CRITIC_REMARK ,count(*) from UserJudge where (((ACCEPTER_ID=:b0 and JUDGE_STATE=:b1) and (SYSDATE-CRIT IC_TIME_DTM)<=:b2) and ACCP_VISIBLE=1) group by CRITIC_IDENTITY, CRITIC_REMARK 37,186 37,187 1.0 2294325282 select STD_EndingBalance into :b0 from STD_StatementDetail wher e (ST_StatementID=:b1 and FT_FundTypeID=:b2) for update 36,999 55,546 1.5 2187975665 select UL_STATUS ,ULT_USERLOGINTYPEID ,NVL(PA_ACCOUNTID,(-1)) in to :b0,:b1,:b2 from UL_UserLogin where UL_LoginName=:b3 36,978 55,532 1.5 3665067949 select PA_Status ,NVL(PA_OverdueBill,'F') ,NVL(PA_OverCreditLimi t,'F') into :b0,:b1,:b2 from PA_PaymentAccount where PA_Account ID=:b3 35,445 35,444 1.0 2260653710 select UL_Status ,ULT_USERLOGINTYPEID ,NVL(PA_ACCOUNTID,(-1)) in to :b0,:b1,:b2 from UL_UserLogin where UL_LoginName=:b3 35,437 35,440 1.0 3368664705 select AB_BALANCE into :b0 from AB_AccountBalance where (PA_Acc SQL ordered by Executions for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> End Executions Threshold: 100 Executions Rows Processed Rows per Exec Hash Value ------------ ---------------- ---------------- ------------ ------------------------------------------------------------- SQL ordered by Sharable Memory for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 - -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ 61,881,168 4 11.9 1706575516 select count(*) as CT from TX_Transaction t where t.pa_accounti d<>:"SYS_B_0" and (t.TX_Status in (:"SYS_B_1", :"SYS_B_2", :"SY S_B_3", :"SYS_B_4")) and t.PA_AccountID = :"SYS_B_5" ORDER BY t .TX_TRANSACTIONID DESC ------------------------------------------------------------- SQL ordered by Version Count for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -74 -> End Version Count Threshold: 20 Version Count Executions Hash Value -------- ------------ ------------ 816 4 1706575516 select count(*) as CT from TX_Transaction t where t.pa_accounti d<>:"SYS_B_0" and (t.TX_Status in (:"SYS_B_1", :"SYS_B_2", :"SY S_B_3", :"SYS_B_4")) and t.PA_AccountID = :"SYS_B_5" ORDER BY t .TX_TRANSACTIONID DESC ------------------------------------------------------------- Instance Activity Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ CPU used by this session 747,853 207.7 3.3 CPU used when call started 747,854 207.7 3.3 CR blocks created 99,939 27.8 0.4 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DBWR buffers scanned 15,969 4.4 0.1 DBWR checkpoint buffers written 524,339 145.7 2.3 DBWR checkpoints 5 0.0 0.0 DBWR free buffers found 15,803 4.4 0.1 DBWR lru scans 395 0.1 0.0 DBWR make free requests 442 0.1 0.0 DBWR revisited being-written buff 0 0.0 0.0 DBWR summed scan depth 15,969 4.4 0.1 DBWR transaction table writes 63,086 17.5 0.3 DBWR undo block writes 88,024 24.5 0.4 SQL*Net roundtrips to/from client 7,231,031 2,008.6 31.5 background checkpoints completed 5 0.0 0.0 background checkpoints started 5 0.0 0.0 background timeouts 3,619 1.0 0.0 branch node splits 3 0.0 0.0 buffer is not pinned count 158,089,599 43,913.8 687.8 buffer is pinned count 157,434,487 43,731.8 685.0 bytes received via SQL*Net from c 1,100,974,531 305,826.3 4,790.3 bytes sent via SQL*Net to client 2,132,280,026 592,300.0 9,277.4 calls to get snapshot scn: kcmgss 4,833,180 1,342.6 21.0 calls to kcmgas 175,604 48.8 0.8 calls to kcmgcs 21,370 5.9 0.1 change write time 4,872 1.4 0.0 cleanouts and rollbacks - consist 21,458 6.0 0.1 cleanouts only - consistent read 39,973 11.1 0.2 cluster key scan block gets 107,777 29.9 0.5 cluster key scans 64,301 17.9 0.3 commit cleanout failures: block l 22,818 6.3 0.1 commit cleanout failures: buffer 10,079 2.8 0.0 commit cleanout failures: callbac 464 0.1 0.0 commit cleanout failures: cannot 3,112 0.9 0.0 commit cleanouts 673,700 187.1 2.9 commit cleanouts successfully com 637,227 177.0 2.8 consistent changes 120,740 33.5 0.5 consistent gets 197,578,892 54,883.0 859.7 current blocks converted for CR cursor authentications 2,561 0.7 0.0 data blocks consistent reads - un 119,338 33.2 0.5 db block changes 2,844,295 790.1 12.4 db block gets 6,505,788 1,807.2 28.3 deferred (CURRENT) block cleanout 234,803 65.2 1.0 dirty buffers inspected 369 0.1 0.0 enqueue conversions 2,804,079 778.9 12.2 enqueue deadlocks 0 0.0 0.0 enqueue releases 4,921,096 1,367.0 21.4 enqueue requests 4,921,171 1,367.0 21.4 enqueue timeouts 58 0.0 0.0 enqueue waits 76 0.0 0.0 exchange deadlocks 24 0.0 0.0 execute count 3,924,997 1,090.3 17.1 free buffer inspected 648 0.2 0.0 Instance Activity Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ free buffer requested 468,722 130.2 2.0 hot buffers moved to head of LRU 560,446 155.7 2.4 immediate (CR) block cleanout app 61,431 17.1 0.3 immediate (CURRENT) block cleanou 31,560 8.8 0.1 index fast full scans (full) 3 0.0 0.0 leaf node splits 1,783 0.5 0.0 logons cumulative 405 0.1 0.0 logons current messages received 185,806 51.6 0.8 messages sent 185,806 51.6 0.8 no buffer to keep pinned count 4,134,108 1,148.4 18.0 no work - consistent read gets 130,980,837 36,383.6 569.9 opened cursors cumulative 99,824 27.7 0.4 opened cursors current parse count (hard) 4 0.0 0.0 parse count (total) 1,437,738 399.4 6.3 parse time cpu 10,603 3.0 0.1 parse time elapsed 11,457 3.2 0.1 physical reads 171,827 47.7 0.8 physical reads direct 18,969 5.3 0.1 physical writes 544,533 151.3 2.4 physical writes direct 19,777 5.5 0.1 physical writes non checkpoint 93,864 26.1 0.4 pinned buffers inspected 0 0.0 0.0 prefetched blocks 1,537 0.4 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 184,187,226,282 ############ 801,388.9 recursive calls 978,101 271.7 4.3 recursive cpu usage 13,489 3.8 0.1 redo blocks written 905,273 251.5 3.9 redo buffer allocation retries 16 0.0 0.0 redo entries 1,479,766 411.1 6.4 redo log space requests 14 0.0 0.0 redo log space wait time 57 0.0 0.0 redo size 401,468,988 111,519.2 1,746.8 redo synch time 71,389 19.8 0.3 redo synch writes 170,254 47.3 0.7 redo wastage 47,431,284 13,175.4 206.4 redo write time 55,583 15.4 0.2 redo writer latching time 13 0.0 0.0 redo writes 172,319 47.9 0.8 rollback changes - undo records a 3,475 1.0 0.0 rollbacks only - consistent read 79,540 22.1 0.4 rows fetched via callback 12,462,190 3,461.7 54.2 session connect time 184,187,226,282 ############ 801,388.9 session cursor cache count 37 0.0 0.0 session cursor cache hits 1,374,679 381.9 6.0 session logical reads 204,084,199 56,690.1 888.0 session pga memory 1,245,044,688 345,845.8 5,417.1 session pga memory max 1,245,111,800 345,864.4 5,417.4 session uga memory 2,804,856 779.1 12.2 session uga memory max 28,366,536 7,879.6 123.4 sorts (disk) 65 0.0 0.0 sorts (memory) 276,068 76.7 1.2 sorts (rows) 27,312,898 7,586.9 118.8 summed dirty queue length 252 0.1 0.0 Instance Activity Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ switch current to new buffer table fetch by rowid 145,959,626 40,544.3 635.1 table fetch continued row 5,875,016 1,632.0 25.6 table scan blocks gotten 836,617 232.4 3.6 table scan rows gotten 13,895,697 3,859.9 60.5 table scans (long tables) 0 0.0 0.0 table scans (short tables) 402,768 111.9 1.8 total file opens 3,516 1.0 0.0 transaction rollbacks 109 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 user calls 7,220,021 2,005.6 31.4 user commits 160,043 44.5 0.7 user rollbacks 69,792 19.4 0.3 write clones created in backgroun 160 0.0 0.0 write clones created in foregroun 81,627 22.7 0.4 ------------------------------------------------------------- Tablespace IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ BIDDB_TBL 53,885 15 8.5 1.0 121,543 34 3,948 26.8 BIDDB_IDX 27,657 8 9.4 1.0 146,401 41 84 0.6 RBS 5 0 0.0 1.0 150,667 42 899 0.6 ACCOUNT_TS 65,021 18 9.3 1.0 54,236 15 12 13.3 MBUS_TS 30 0 0.0 1.0 20,706 6 2,371 3.1 EACHPAY_IDX 3,895 1 9.6 1.0 14,934 4 0 0.0 EACHPAY_BIG 1,672 0 10.3 1.0 12,120 3 0 0.0 TEMP_LOCAL 710 0 0.0 24.7 3,357 1 0 0.0 SYSTEM 110 0 12.1 3.7 2,787 1 4 5.0 EACHPAY_M 371 0 6.7 1.0 93 0 0 0.0 USERS 231 0 5.4 1.0 207 0 0 0.0 TOOLS 32 0 2.2 1.0 29 0 0 0.0 DICT_TS 10 0 0.0 1.0 5 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ ACCOUNT_TS /oradata/eachdb2/table/account1.dbf 6,939 2 9.5 1.0 3,976 1 1 10.0 /oradata/eachdb2/table/account10.dbf 5,709 2 9.5 1.0 10,994 3 3 13.3 /oradata/eachdb2/table/account11.dbf 2,067 1 10.5 1.0 8,887 2 0 /oradata/eachdb2/table/account2.dbf 5,902 2 9.5 1.0 1,584 0 0 /oradata/eachdb2/table/account3.dbf 7,150 2 9.4 1.0 3,909 1 0 /oradata/eachdb2/table/account4.dbf 5,969 2 9.2 1.0 1,856 1 2 5.0 /oradata/eachdb2/table/account5.dbf 4,816 1 9.1 1.0 2,171 1 0 /oradata/eachdb2/table/account6.dbf 5,249 1 8.8 1.0 2,414 1 1 10.0 /oradata/eachdb2/table/account7.dbf 7,781 2 9.0 1.0 3,807 1 2 0.0 /oradata/eachdb2/table/account8.dbf 7,363 2 9.2 1.0 3,837 1 0 /oradata/eachdb2/table/account9.dbf 6,076 2 9.4 1.0 10,801 3 3 30.0 BIDDB_IDX /oradata/eachdb2/index/index001.dbf 144 0 8.1 1.0 91 0 0 /oradata/eachdb2/index/index002.dbf 1,198 0 10.5 1.0 45,750 13 2 5.0 /oradata/eachdb2/index/index003.dbf 954 0 9.8 1.3 3,045 1 0 /oradata/eachdb2/index/index004.dbf 376 0 10.4 1.6 94 0 0 /oradata/eachdb2/index/index005.dbf 7 0 4.3 1.0 9 0 0 /oradata/eachdb2/index/index006.dbf 775 0 9.5 1.0 2,272 1 0 /oradata/eachdb2/index/index007.dbf 1,039 0 10.5 1.0 16,215 5 81 0.5 /oradata/eachdb2/index/index008.dbf 799 0 9.5 1.0 669 0 0 /oradata/eachdb2/index/index009.dbf 726 0 9.1 1.0 1,350 0 0 /oradata/eachdb2/index/index010.dbf 725 0 9.8 1.0 872 0 0 /oradata/eachdb2/index02/index011.dbf 691 0 9.0 1.0 1,252 0 0 /oradata/eachdb2/index02/index012.dbf 968 0 9.3 1.0 1,327 0 0 /oradata/eachdb2/index02/index013.dbf 896 0 9.7 1.0 4,103 1 0 /oradata/eachdb2/index02/index014.dbf 1,080 0 10.1 1.0 842 0 0 /oradata/eachdb2/index02/index015.dbf File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ 841 0 8.9 1.0 833 0 0 BIDDB_IDX /oradata/eachdb2/index02/index016.dbf 666 0 10.5 1.0 549 0 0 /oradata/eachdb2/index02/index017.dbf 641 0 9.4 1.0 699 0 0 /oradata/eachdb2/index02/index018.dbf 841 0 9.1 1.0 922 0 0 /oradata/eachdb2/index02/index019.dbf 677 0 9.1 1.0 3,159 1 0 /oradata/eachdb2/index02/index020.dbf 808 0 9.5 1.0 1,009 0 0 /oradata/eachdb2/index02/index021.dbf 593 0 9.1 1.0 8,534 2 0 /oradata/eachdb2/index02/index022.dbf 832 0 9.3 1.0 1,459 0 0 /oradata/eachdb2/index02/index033.dbf 743 0 10.1 1.0 1,562 0 0 /oradata/eachdb2/index02/index034.dbf 553 0 8.3 1.0 4,033 1 0 /oradata/eachdb2/index02/index035.dbf 725 0 9.2 1.0 5,039 1 1 0.0 /oradata/eachdb2/index02/index036.dbf 766 0 9.5 1.0 1,563 0 0 /oradata/eachdb2/index02/index037.dbf 899 0 9.5 1.0 1,663 0 0 /oradata/eachdb2/index02/index038.dbf 637 0 8.6 1.0 1,892 1 0 /oradata/eachdb2/index02/index039.dbf 487 0 9.4 1.0 1,772 0 0 /oradata/eachdb2/index02/index040.dbf 438 0 8.1 1.0 3,919 1 0 /oradata/eachdb2/index02/index041.dbf 421 0 9.4 1.0 1,374 0 0 /oradata/eachdb2/index02/index042.dbf 472 0 9.3 1.0 4,025 1 0 /oradata/eachdb2/index02/index043.dbf 510 0 8.8 1.0 1,646 0 0 /oradata/eachdb2/index02/index23.dbf 395 0 9.6 1.0 640 0 0 /oradata/eachdb2/index02/index24.dbf 679 0 8.6 1.0 7,753 2 0 /oradata/eachdb2/index02/index25.dbf 270 0 8.6 1.0 2,798 1 0 /oradata/eachdb2/index02/index26.dbf 646 0 9.3 1.0 1,325 0 0 /oradata/eachdb2/index02/index27.dbf 321 0 9.1 1.0 864 0 0 /oradata/eachdb2/index02/index28.dbf 527 0 9.0 1.0 1,208 0 0 /oradata/eachdb2/index02/index29.dbf 516 0 8.3 1.0 984 0 0 /oradata/eachdb2/index02/index30.dbf File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ 263 0 8.4 1.0 798 0 0 BIDDB_IDX /oradata/eachdb2/index02/index31.dbf 765 0 8.9 1.0 1,796 0 0 /oradata/eachdb2/index02/index32.dbf 347 0 9.3 1.0 4,692 1 0 BIDDB_TBL /oradata/eachdb2/table/table001.dbf 1,808 1 8.7 1.3 16,814 5 45 0.9 /oradata/eachdb2/table/table002.dbf 1,223 0 8.6 1.0 1,708 0 0 /oradata/eachdb2/table/table003.dbf 1,452 0 8.5 1.0 1,991 1 0 /oradata/eachdb2/table/table004.dbf 6 0 0.0 1.0 5 0 0 /oradata/eachdb2/table/table005.dbf 5 0 0.0 1.0 5 0 0 /oradata/eachdb2/table/table006.dbf 322 0 9.5 1.0 1,009 0 0 /oradata/eachdb2/table/table007.dbf 1,999 1 9.2 1.0 1,270 0 0 /oradata/eachdb2/table/table008.dbf 51 0 7.5 1.0 853 0 0 /oradata/eachdb2/table/table009.dbf 78 0 10.6 1.0 1,326 0 1 0.0 /oradata/eachdb2/table/table010.dbf 898 0 9.7 1.1 2,030 1 0 /oradata/eachdb2/table/table011.dbf 1,381 0 8.7 1.1 2,148 1 0 /oradata/eachdb2/table/table012.dbf 991 0 8.9 1.0 1,425 0 0 /oradata/eachdb2/table/table013.dbf 1,230 0 9.1 1.0 2,121 1 1 0.0 /oradata/eachdb2/table/table014.dbf 1,354 0 8.6 1.0 5,568 2 1 10.0 /oradata/eachdb2/table/table015.dbf 2,261 1 8.9 1.0 1,955 1 0 /oradata/eachdb2/table/table016.dbf 1,302 0 8.4 1.0 2,154 1 0 /oradata/eachdb2/table/table017.dbf 1,212 0 9.0 1.0 2,108 1 0 /oradata/eachdb2/table/table018.dbf 1,055 0 8.6 1.0 1,146 0 0 /oradata/eachdb2/table/table019.dbf 987 0 8.8 1.0 1,099 0 0 /oradata/eachdb2/table/table020.dbf 1,226 0 9.2 1.0 1,077 0 0 /oradata/eachdb2/table/table021.dbf 969 0 8.6 1.0 629 0 0 /oradata/eachdb2/table/table022.dbf 1,389 0 9.0 1.0 946 0 0 /oradata/eachdb2/table/table023.dbf 1,806 1 9.5 1.0 1,299 0 0 File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ BIDDB_TBL /oradata/eachdb2/table/table024.dbf 1,415 0 8.3 1.0 1,100 0 0 /oradata/eachdb2/table/table025.dbf 1,353 0 8.2 1.0 1,140 0 0 /oradata/eachdb2/table/table026.dbf 2,113 1 7.9 1.0 393 0 0 /oradata/eachdb2/table/table027.dbf 1,716 0 9.6 1.0 2,487 1 0 /oradata/eachdb2/table/table028.dbf 721 0 9.6 1.0 1,031 0 0 /oradata/eachdb2/table/table029.dbf 2,701 1 8.2 1.0 1,891 1 0 /oradata/eachdb2/table/table030.dbf 691 0 8.6 1.0 794 0 0 /oradata/eachdb2/table/table031.dbf 772 0 8.9 1.0 611 0 0 /oradata/eachdb2/table/table032.dbf 1,054 0 9.1 1.0 1,973 1 0 /oradata/eachdb2/table/table033.dbf 734 0 9.2 1.0 451 0 0 /oradata/eachdb2/table/table034.dbf 976 0 8.8 1.0 2,957 1 0 /oradata/eachdb2/table/table035.dbf 786 0 8.9 1.0 837 0 2 10.0 /oradata/eachdb2/table/table036.dbf 2,035 1 7.9 1.0 9,733 3 543 157.0 /oradata/eachdb2/table/table037.dbf 2,416 1 8.5 1.0 18,090 5 464 5.4 /oradata/eachdb2/table/table038.dbf 1,521 0 6.9 1.0 13,716 4 333 5.6 /oradata/eachdb2/table/table039.dbf 1,374 0 7.7 1.0 4,635 1 367 6.2 /oradata/eachdb2/table/table040.dbf 1,397 0 7.3 1.0 2,386 1 360 5.9 /oradata/eachdb2/table/table041.dbf 1,311 0 7.2 1.0 1,313 0 424 5.9 /oradata/eachdb2/table/table042.dbf 910 0 7.0 1.0 269 0 370 5.8 /oradata/eachdb2/table/table043.dbf 964 0 6.9 1.0 3,733 1 311 7.1 /oradata/eachdb2/table/table044.dbf 1,127 0 6.8 1.0 323 0 435 6.8 /oradata/eachdb2/table/table045.dbf 793 0 6.8 1.0 994 0 291 6.0 DICT_TS /oradata/eachdb2/system/dict_ts.dbf 10 0 0.0 1.0 5 0 0 EACHPAY_BIG /oradata/eachdb2/table/eachpay_big1.dbf 169 0 10.4 1.0 621 0 0 /oradata/eachdb2/table/eachpay_big2.dbf 128 0 8.8 1.0 261 0 0 File IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ EACHPAY_BIG /oradata/eachdb2/table/eachpay_big3.dbf 95 0 8.8 1.0 269 0 0 /oradata/eachdb2/table/eachpay_big4.dbf 147 0 8.9 1.0 290 0 0 /oradata/eachdb2/table/eachpay_big5.dbf 339 0 10.1 1.0 965 0 0 /oradata/eachdb2/table/eachpay_big6.dbf 456 0 11.9 1.0 6,091 2 0 /oradata/eachdb2/table/eachpay_big7.dbf 300 0 10.7 1.0 3,618 1 0 /oradata/eachdb2/table/eachpay_big8.dbf 38 0 3.2 1.0 5 0 0 EACHPAY_IDX /oradata/eachdb2/index/eachpay_idx01.dbf 1,257 0 9.8 1.0 3,986 1 0 /oradata/eachdb2/index/eachpay_idx02.dbf 1,326 0 9.4 1.0 6,504 2 0 /oradata/eachdb2/index/eachpay_idx03.dbf 1,312 0 9.5 1.0 4,444 1 0 EACHPAY_M /oradata/eachdb2/table/eachpay_m1.dbf 371 0 6.7 1.0 93 0 0 MBUS_TS /oradata/eachdb2/table/mbus01.dbf 10 0 0.0 1.0 8,098 2 1,073 2.9 /oradata/eachdb2/table/mbus02.dbf 20 0 0.0 1.0 12,608 4 1,298 3.3 RBS /oradata/eachdb2/undo/rbs01.dbf 5 0 0.0 1.0 150,667 42 899 0.6 SYSTEM /oradata/eachdb2/system/system01.dbf 110 0 12.1 3.7 2,787 1 4 5.0 TEMP_LOCAL /oradata/eachdb2/temp/temp1.tmp 710 0 0.0 24.7 3,357 1 0 TOOLS /oradata/eachdb2/system/tools01.dbf 6 0 1.7 1.0 7 0 0 /oradata/eachdb2/system/tools02.dbf 26 0 2.3 1.0 22 0 0 USERS /oradata/eachdb2/system/user02.dbf 55 0 4.9 1.0 28 0 0 /oradata/eachdb2/system/user03.dbf 71 0 4.9 1.0 40 0 0 /oradata/eachdb2/system/users01.dbf 105 0 5.9 1.0 139 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> Pools D: default pool, K: keep pool, R: recycle pool Free Write Buffer Buffer Consistent Physical Physical Buffer Complete Busy P Gets Gets Reads Writes Waits Waits Waits - ----------- ------------- ----------- ---------- ------- -------- ---------- D 468,006 135,144,358 152,843 524,102 0 0 7,324 ------------------------------------------------------------- Buffer wait Statistics for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (cs) Time (cs) ------------------ ----------- ---------- --------- data block 6,494 11,572 2 undo block 500 38 0 undo header 404 15 0 segment header 2 2 1 ------------------------------------------------------------- Enqueue activity for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> ordered by waits desc, gets desc Enqueue Gets Waits ---------- ------------ ---------- TX 184,432 76 ------------------------------------------------------------- Rollback Segment Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->A high value for "Pct Waits" suggests more rollback segments may be required Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ ------------ ------- --------------- -------- -------- -------- 0 12.0 0.00 0 0 0 0 1 47,501.0 0.00 4,662,608 7 0 0 2 44,408.0 0.00 4,429,878 6 0 0 3 59,749.0 0.00 6,022,616 8 0 0 4 52,089.0 0.00 5,268,002 7 0 0 5 45,517.0 0.00 4,514,474 6 0 0 6 45,168.0 0.00 4,570,930 6 0 0 7 46,680.0 0.00 4,497,112 6 0 0 8 46,094.0 0.00 4,428,432 6 0 0 9 43,970.0 0.00 4,426,254 6 0 0 10 43,986.0 0.00 4,807,916 7 0 0 11 43,897.0 0.00 4,384,902 6 0 0 12 45,516.0 0.00 4,495,742 6 0 0 13 44,082.0 0.00 4,500,374 6 0 0 14 45,475.0 0.00 4,441,634 6 0 0 15 54,258.0 0.00 5,616,498 8 0 0 16 43,608.0 0.00 4,296,922 6 0 0 17 45,333.0 0.00 4,461,218 6 0 0 18 61,685.0 0.00 6,322,568 8 0 0 19 54,624.0 0.00 6,040,868 8 0 0 20 53,928.0 0.00 5,302,474 7 0 0 21 52,509.0 0.00 5,252,296 7 0 0 22 43,450.0 0.00 4,354,578 6 0 0 23 45,725.0 0.00 4,641,848 6 0 0 24 61,284.0 0.00 6,253,432 8 0 0 25 44,742.0 0.00 4,341,304 6 0 0 26 44,235.0 0.00 4,443,142 6 0 0 27 43,876.0 0.00 4,422,466 6 0 0 28 46,940.0 0.00 4,607,700 7 0 0 29 54,856.0 0.00 5,512,432 7 0 0 31 44,178.0 0.00 4,368,804 6 0 0 ------------------------------------------------------------- Rollback Segment Storage for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 581,632 17,203 581,632 1 34,594,816 1,048,398 34,594,816 2 36,691,968 1,048,479 36,691,968 3 35,643,392 1,048,430 35,643,392 4 37,740,544 1,048,469 37,740,544 5 34,594,816 1,048,527 34,594,816 6 45,080,576 1,048,560 45,080,576 7 33,546,240 1,048,503 33,546,240 8 35,643,392 1,048,333 35,643,392 9 33,546,240 1,048,503 33,546,240 10 34,594,816 1,048,378 34,594,816 11 44,032,000 1,048,214 44,032,000 12 22,011,904 1,048,215 22,011,904 13 34,594,816 1,047,887 34,594,816 14 53,469,184 1,048,481 53,469,184 15 42,983,424 1,048,540 42,983,424 16 41,934,848 1,048,558 41,934,848 17 34,594,816 1,048,527 34,594,816 18 49,274,880 1,048,471 49,274,880 19 33,546,240 1,048,444 33,546,240 20 34,594,816 1,048,306 34,594,816 21 33,546,240 1,048,516 33,546,240 22 24,109,056 1,048,041 24,109,056 23 39,837,696 1,049,242 39,837,696 24 47,177,728 1,048,490 47,177,728 25 33,546,240 1,047,845 33,546,240 26 34,594,816 1,048,535 34,594,816 27 34,594,816 1,048,306 34,594,816 28 20,963,328 1,047,638 20,963,328 29 34,594,816 1,048,444 34,594,816 31 33,546,240 1,048,487 33,546,240 ------------------------------------------------------------- Latch Activity for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->"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 Pct Get Get Slps NoWait NoWait Latch Name Requests Miss /Miss Requests Miss ----------------------------- -------------- ------ ------ ------------ ------ Token Manager 1,291 0.0 0 active checkpoint queue latch 12,223 0.0 0 archive control 10 0.0 0 archive process latch 9 0.0 0 cache buffer handles 1,616,102 0.0 0.0 0 cache buffers chains 354,214,885 0.1 0.0 378,019 0.1 cache buffers lru chain 1,315,571 0.0 0.0 280,541 0.0 channel handle pool latch 342 0.0 0 channel operations parent lat 513 0.0 0 checkpoint queue latch 1,841,325 0.0 0.0 0 dml lock allocation 547,681 0.0 0.0 0 enqueue hash chains 12,665,509 0.1 0.0 0 enqueues 19,431,010 1.0 0.0 0 event group latch 171 0.0 0 file number translation table 65 0.0 0 global transaction 7,997,155 0.0 0.0 0 global tx free list 1,342,511 0.0 0.0 0 global tx hash mapping 5,799,148 0.1 0.0 0 job_queue_processes parameter 56 0.0 0 ktm global data 11 0.0 0 latch wait list 100 2.0 0.0 97 0.0 library cache 8,888,847 0.2 0.0 0 list of block allocation 347,450 0.0 0.0 0 loader state object freelist 434 0.0 0 messages 906,044 0.2 0.0 0 multiblock read objects 1,844 0.0 0 ncodef allocation latch 56 0.0 0 process allocation 171 0.0 171 0.0 process group creation 342 0.0 0 redo allocation 1,826,036 0.1 0.0 0 redo writing 877,624 0.8 0.0 0 row cache objects 63,051 0.0 0.0 0 sequence cache 308,923 0.0 0.0 0 session allocation 1,667,502 0.1 0.0 0 session idle bit 15,272,199 0.0 0.0 0 session switching 56 0.0 0 shared pool 27,955 0.0 0.0 0 sort extent pool 401 0.0 0 transaction allocation 1,681,929 0.1 0.0 0 transaction branch allocation 4,608,437 0.3 0.0 0 undo global data 1,797,013 0.1 0.0 0 user lock 680 0.0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers chains 354,214,885 484,588 1,987 482608/1973/ 7/0/0 enqueues 19,431,010 186,216 254 185962/254/0 /0/0 library cache 8,888,847 17,514 163 17417/50/34/ 13/0 enqueue hash chains 12,665,509 17,439 132 17316/114/9/ 0/0 transaction branch allocat 4,608,437 12,018 52 11966/52/0/0 /0 redo writing 877,624 6,796 62 6734/62/0/0/ 0 global tx hash mapping 5,799,148 5,623 32 5591/32/0/0/ 0 session idle bit 15,272,199 2,727 43 2685/41/1/0/ 0 redo allocation 1,826,036 2,010 11 1999/11/0/0/ 0 session allocation 1,667,502 1,985 5 1980/5/0/0/0 messages 906,044 1,492 20 1472/20/0/0/ 0 transaction allocation 1,681,929 1,421 5 1416/5/0/0/0 undo global data 1,797,013 1,387 12 1375/12/0/0/ 0 global tx free list 1,342,511 639 6 633/6/0/0/0 checkpoint queue latch 1,841,325 588 14 574/14/0/0/0 cache buffers lru chain 1,315,571 390 19 371/19/0/0/0 dml lock allocation 547,681 266 2 264/2/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- ------- cache buffers chains kcbgtcr: kslbegin 0 1,568 990 cache buffers chains kcbrls: kslbegin 0 281 599 cache buffers chains kcbchg: kslbegin: bufs not 0 72 75 cache buffers chains kcbgcur: kslbegin 0 28 13 cache buffers chains kcbzwb 0 15 2 cache buffers chains kcbget: pin buffer 0 7 3 cache buffers chains kcbbxsv 0 4 12 cache buffers chains kcbbwb1 0 3 1 cache buffers chains kcbso1: set no access 0 3 2 cache buffers chains kcbzgb: scan from tail. no 0 2 0 cache buffers chains kcbchg: kslbegin: call CR 0 1 249 cache buffers chains kcbnlc 0 1 38 cache buffers chains kcbget: exchange rls 0 1 0 cache buffers lru chain kcbbiop: lru scan 0 12 0 cache buffers lru chain kcbzgb: multiple sets nowa 0 4 0 cache buffers lru chain kcbzgb: posted for free bu 0 3 6 checkpoint queue latch kcbk0rrd: update recovery 0 13 0 checkpoint queue latch kcbbwthc: thread checkpoin 0 1 3 dml lock allocation ktaiam 0 2 1 enqueue hash chains ksqrcl 0 75 25 enqueue hash chains ksqgtl3 0 45 100 enqueue hash chains ksqcnl 0 10 7 enqueue hash chains ksqcmi: get hash chain lat 0 2 0 enqueues ksqgtl2 0 129 45 enqueues ksqgel: create enqueue 0 62 87 enqueues ksqrcl 0 30 84 enqueues ksqdel 0 29 5 enqueues ksqies 0 4 33 global tx free list k2gfegte 0 4 4 global tx free list k2ghashdel 0 2 2 global tx hash mapping K2GTBlatch 0 32 32 library cache kglsca: parent 0 59 0 library cache kglic 0 50 0 library cache kglpnal: child: alloc spac 0 25 18 library cache kglpnal: child: before pro 0 14 35 library cache kglhdgn: child: 0 5 2 library cache kglhdgc: child: 0 2 3 library cache kgllkdl: child: free pin 0 2 2 library cache kglupc: child 0 2 16 library cache kgllkdl: child: cleanup 0 2 0 library cache kglget: child: KGLDSBYD 0 1 0 library cache kglpnc: child 0 1 11 messages ksarcv: after wait 0 11 1 messages ksaamb: after wakeup 0 8 18 messages ksarcv 0 1 1 redo allocation kcrfwr: redo allocation 0 8 8 redo allocation kcrfwi: before write 0 2 3 redo allocation kcrfwi: more space 0 1 0 redo writing kcrfsr 0 55 1 redo writing kcrfss 0 5 60 redo writing kcrfwi: after write 0 2 0 session allocation ksucri 0 3 3 session allocation ksuxds: not user session 0 2 2 Latch Miss Sources for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- ------- session idle bit ksupuc: clear busy 0 21 26 session idle bit ksupuc: set busy 0 17 16 session idle bit ksuxds 0 5 1 transaction allocation ktcdso 0 4 0 transaction allocation ktcxba 0 1 5 transaction branch alloc ktcbba 0 19 11 transaction branch alloc ktcdbr 0 12 10 transaction branch alloc ktcsbr 0 12 7 transaction branch alloc ktcbod 0 5 6 transaction branch alloc ksupuc 0 4 18 undo global data ktudba: KSLBEGIN 0 8 9 undo global data ktubnd 0 2 2 undo global data ktudax: KSLBEGIN 0 1 0 undo global data ktudnx: KSLBEGIN 0 1 1 ------------------------------------------------------------- Dictionary Cache Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->"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 Pct Cache Requests Miss Requests Miss Req Usage SGA ---------------------- ------------ ------ -------- ----- -------- ------ ---- dc_constraints 0 0 0 8 67 dc_database_links 0 0 0 0 0 dc_files 1,453 0.0 0 0 121 95 dc_free_extents 231 0.0 0 0 23 85 dc_global_oids 0 0 0 0 0 dc_histogram_data 12 0.0 0 0 238 100 dc_histogram_data_valu 0 0 0 50 93 dc_histogram_defs 12 0.0 0 0 1,748 99 dc_object_ids 320 0.0 0 0 527 99 dc_objects 178 0.0 0 0 744 100 dc_outlines 0 0 0 0 0 dc_profiles 173 0.0 0 0 1 13 dc_rollback_segments 704 0.0 0 0 33 80 dc_segments 48 0.0 0 2 460 99 dc_sequence_grants 0 0 0 0 0 dc_sequences 5,022 0.0 0 5,022 32 80 dc_synonyms 0 0 0 77 93 dc_tablespace_quotas 8 0.0 0 8 12 80 dc_tablespaces 3,456 0.0 0 0 16 89 dc_used_extents 0 0 0 11 65 dc_user_grants 1,380 0.0 0 0 16 40 dc_usernames 344 0.0 0 0 18 95 dc_users 1,973 0.0 0 0 17 81 ifs_acl_cache_entries 0 0 0 0 0 ------------------------------------------------------------- Library Cache Activity for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 369 0.0 369 0.0 0 0 CLUSTER 0 0 0 0 INDEX 0 0 0 0 OBJECT 0 0 0 0 PIPE 0 0 0 0 SQL AREA 6,293 0.0 3,147,188 -0.0 4 0 TABLE/PROCEDURE 257 0.0 258,970 0.0 0 0 TRIGGER 17,669 0.0 17,670 0.0 0 0 ------------------------------------------------------------- SGA Memory Summary for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 8,053,063,680 Fixed Size 102,076 Redo Buffers 278,528 Variable Size 530,587,648 ---------------- sum 8,584,031,932 ------------------------------------------------------------- SGA breakdown difference for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 Pool Name Begin value End value Difference ----------- ------------------------ -------------- -------------- ----------- java pool free memory 32,768 32,768 0 large pool free memory 10,240,000 10,240,000 0 shared pool DML locks 1,680,000 1,680,000 0 shared pool KGFF heap 89,760 89,760 0 shared pool KGK heap 13,096 13,096 0 shared pool KQLS heap 2,949,672 2,949,672 0 shared pool PL/SQL DIANA 1,266,888 1,266,888 0 shared pool PL/SQL MPCODE 1,220,504 1,220,504 0 shared pool PL/SQL SOURCE 7,384 7,384 0 shared pool PLS non-lib hp 2,136 2,136 0 shared pool State objects 1,645,760 1,645,760 0 shared pool db_block_buffers 204,472,320 204,472,320 0 shared pool db_block_hash_buckets 32,965,296 32,965,296 0 shared pool dictionary cache 2,313,344 2,313,344 0 shared pool distributed_transactions 992,224 992,224 0 shared pool enqueue_resources 1,062,880 1,062,880 0 shared pool errors 251,064 251,064 0 shared pool event statistics per ses 3,072,720 3,072,720 0 shared pool fixed allocation callbac 4,872 4,872 0 shared pool free memory 165,237,736 165,088,392 -149,344 shared pool ktlbk state objects 1,340,000 1,340,000 0 shared pool library cache 37,740,840 37,826,472 85,632 shared pool miscellaneous 7,784,840 7,809,824 24,984 shared pool processes 857,600 857,600 0 shared pool sessions 2,152,320 2,152,320 0 shared pool sql area 44,976,408 45,015,136 38,728 shared pool state objects 781,192 781,192 0 shared pool table columns 37,216 37,216 0 shared pool table definiti 12,472 12,472 0 shared pool transaction_branches 1,536,000 1,536,000 0 shared pool transactions 3,800,000 3,800,000 0 shared pool trigger defini 8,088 8,088 0 shared pool trigger inform 992 992 0 shared pool trigger source 1,984 1,984 0 db_block_buffers 8,053,063,680 8,053,063,680 0 fixed_sga 102,076 102,076 0 log_buffer 256,000 256,000 0 ------------------------------------------------------------- init.ora Parameters for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _sqlexec_progression_cost 0 always_anti_join HASH background_dump_dest /export/home/oracle/admin/eachdb2 compatible 8.1.7 control_files /oradata/eachdb2/ctl/eachdb2ctl1. core_dump_dest /export/home/oracle/admin/eachdb2 cursor_sharing FORCE cursor_space_for_time TRUE db_block_buffers 983040 db_block_size 8192 db_domain eachnet.com db_file_direct_io_count 128 db_file_multiblock_read_count 16 db_name eachdb2 distributed_transactions 2000 instance_name eachdb2 java_pool_size 32768 job_queue_interval 60 job_queue_processes 2 large_pool_size 10240000 log_archive_dest /export/home/oracle/admin/eachdb2 log_archive_format eachdb2_arch_%t_%s.arc log_archive_start TRUE log_buffer 256000 log_checkpoint_interval 1000000 log_checkpoint_timeout 180000 max_enabled_roles 30 open_cursors 800 open_links 4 optimizer_mode FIRST_ROWS os_authent_prefix parallel_max_servers 40 processes 800 query_rewrite_enabled TRUE rollback_segments RBS1, RBS2, RBS3, RBS4, RBS5, RBS service_names eachdb2.eachnet.com session_cached_cursors 200 shared_pool_size 262144000 sort_area_size 1048576 timed_statistics TRUE transactions 2500 user_dump_dest /export/home/oracle/admin/eachdb2 utl_file_dir /export/home/oracle/search ------------------------------------------------------------- End of Report ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
 |
 |