Re: Re: suddenly high "physical write" from statspack
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: "Yong Huang" <yong321@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Feb 2004 17:20:46 +0800
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.
----- Original Message -----
From: "Yong Huang" <yong321@xxxxxxxxx>
To: "zhuchao" <chao_ping@xxxxxxxxxxx>
Cc: <ruDOLF_lu@xxxxxxxxxxxxxxx>; <red_zhuyi@xxxxxxxxxxx>
Sent: Monday, February 23, 2004 10:00 AM
Subject: Re: Re: suddenly high "physical write" from statspack
> 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@xxxxxxxxxxx> 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)
>
> -------------- ------- ------ ------- ------------ -------- ---------- ---
---
> > 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
