statspack分析
STATSPACK report forDatabase DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1467075257 ora8 1 12-5月 -08 23:2 10.2.0.1.0 NO
8
Host Name: dbserver Num CPUs: 16 Phys Memory (MB): 15,808
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 13-5月 -08 09:29:06 571 37.2
End Snap: 13 13-5月 -08 11:29:03 635 39.0
Elapsed: 119.95 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 6,544M Std Block Size: 8K
Shared Pool Size: 480M Log Buffer: 13,959K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 104,111.64 9,266.87
Logical reads: 139,647.02 12,429.84
Block changes: 762.96 67.91
Physical reads: 383.41 34.13
Physical writes: 64.74 5.76
User calls: 270.12 24.04
Parses: 256.39 22.82
Hard parses: 8.85 0.79
Sorts: 50.72 4.51
Logons: 0.15 0.01
Executes: 673.96 59.99
Transactions: 11.23
% Blocks changed per Read: 0.55 Recursive Call %: 81.24
Rollback per transaction %: 7.14 Rows per Sort: 892.72
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 99.75 In-memory Sort %: 100.00
Library Hit %: 98.92 Soft Parse %: 96.55
Execute to Parse %: 61.96 Latch Hit %: 99.93
Parse CPU to Parse Elapsd %: 27.29 % Non-Parse CPU: 95.86
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 79.37 82.53
% SQL with executions>1: 86.13 88.41
% Memory for SQL w/exec>1: 97.22 96.34
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 29,921 58.0
db file sequential read 1,104,695 8,149 7 15.8
log file sync 79,947 4,136 52 8.0
log file parallel write 74,473 2,531 34 4.9
library cache pin 1,907 2,237 1173 4.3
-------------------------------------------------------------
Host CPU (CPUs: 16)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.02 0.02 30.02 2.03 67.95 12.84 29.83
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 26.67
% of busy CPU for Instance: 83.19
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 15,808.0 15,808.0
SGA use (MB): 7,088.0 7,088.0
PGA use (MB): 1,820.9 1,800.2
% Host Mem used for SGA+PGA: 56.4 56.2
-------------------------------------------------------------
Time Model System Stats DB/Inst: ORA8/ora8 Snaps: 1-13
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 47,848.4 84.8
DB CPU 30,598.6 54.2
parse time elapsed 4,749.8 8.4
hard parse elapsed time 2,129.9 3.8
PL/SQL execution elapsed time 564.1 1.0
hard parse (sharing criteria) elaps 435.9 .8
PL/SQL compilation elapsed time 152.8 .3
sequence load elapsed time 18.5 .0
connection management call elapsed 5.7 .0
hard parse (bind mismatch) elapsed 3.0 .0
failed parse elapsed time 1.3 .0
repeated bind elapsed time 0.5 .0
DB time 56,417.7
background elapsed time 4,943.0
background cpu time 86.1
-------------------------------------------------------------
Wait Events DB/Inst: ORA8/ora8 Snaps: 1-13
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file sequential read 1,104,695 0 8,149 7 13.7
log file sync 79,947 0 4,136 52 1.0
log file parallel write 74,473 0 2,531 34 0.9
library cache pin 1,907 21 2,237 1173 0.0
db file parallel write 29,777 0 1,697 57 0.4
db file scattered read 99,317 0 724 7 1.2
SGA: allocation forcing component 34,748 98 341 10 0.4
latch: library cache 2,638 0 314 119 0.0
log file switch completion 619 13 218 352 0.0
latch: cache buffers chains 4,216 0 209 50 0.1
direct path write temp 12,282 0 206 17 0.2
library cache load lock 356 4 154 434 0.0
log file sequential read 1,169 0 111 95 0.0
control file parallel write 4,124 0 111 27 0.1
Log archive I/O 926 0 102 110 0.0
enq: TX - row lock contention 191 9 81 425 0.0
buffer exterminate 96 65 71 735 0.0
SQL*Net break/reset to client 4,014 0 58 14 0.0
read by other session 4,984 0 46 9 0.1
direct path read temp 12,282 0 32 3 0.2
direct path read 28,821 0 23 1 0.4
SQL*Net more data to client 668,927 0 18 0 8.3
buffer busy waits 768 1 13 17 0.0
latch: shared pool 554 1 12 22 0.0
direct path write 2,352 0 10 4 0.0
os thread startup 97 0 9 91 0.0
enq: CF - contention 5 0 9 1735 0.0
latch free 658 0 8 13 0.0
latch: enqueue hash chains 54 0 8 145 0.0
latch: cache buffer handles 78 0 7 87 0.0
control file sequential read 56,922 0 5 0 0.7
kksfbc child completion 83 100 4 49 0.0
log file single write 180 0 4 21 0.0
LGWR wait for redo copy 1,004 28 4 4 0.0
db file parallel read 256 0 2 9 0.0
latch: library cache lock 36 0 2 62 0.0
library cache lock 1 0 2 2091 0.0
SGA: MMAN sleep for component shr 183 93 2 9 0.0
latch: session allocation 31 0 1 38 0.0
latch: redo allocation 60 0 1 18 0.0
latch: cache buffers lru chain 34 0 1 24 0.0
latch: row cache objects 52 0 1 15 0.0
rdbms ipc reply 705 0 0 0 0.0
latch: In memory undo latch 6 0 0 30 0.0
enq: TX - index contention 12 0 0 12 0.0
cursor: mutex X 99,268 0 0 0 1.2
latch: object queue header operat 5 0 0 25 0.0
latch: messages 3 0 0 34 0.0
latch: redo writing 6 0 0 10 0.0
latch: undo global data 2 0 0 13 0.0
Wait Events DB/Inst: ORA8/ora8 Snaps: 1-13
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
undo segment extension 313 100 0 0 0.0
latch: library cache pin 4 0 0 2 0.0
row cache lock 5 0 0 0 0.0
SQL*Net message from client 1,935,132 0 3,634,596 1878 23.9
Streams AQ: qmn slave idle wait 257 0 7,004 27252 0.0
Streams AQ: qmn coordinator idle 514 50 7,004 13626 0.0
jobq slave wait 2,260 98 6,578 2911 0.0
Streams AQ: waiting for time mana 32 97 6,475 ###### 0.0
SQL*Net more data from client 92,834 0 1,737 19 1.1
class slave wait 5 100 24 4883 0.0
SQL*Net message to client 1,935,215 0 4 0 23.9
-------------------------------------------------------------
Background Wait Events DB/Inst: ORA8/ora8 Snaps: 1-13
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 74,469 0 2,530 34 0.9
db file parallel write 29,775 0 1,697 57 0.4
log file sequential read 1,169 0 111 95 0.0
control file parallel write 4,123 0 111 27 0.1
Log archive I/O 926 0 102 110 0.0
events in waitclass Other 1,800 15 14 8 0.0
direct path write 2,160 0 10 5 0.0
os thread startup 97 0 9 91 0.0
db file sequential read 2,304 0 8 4 0.0
control file sequential read 5,694 0 4 1 0.1
log file single write 180 0 4 21 0.0
direct path read 4,680 0 4 1 0.1
db file scattered read 191 0 3 15 0.0
SGA: MMAN sleep for component shr 183 93 2 9 0.0
latch: library cache 12 25 2 143 0.0
latch: cache buffers chains 13 0 0 15 0.0
buffer busy waits 31 0 0 5 0.0
latch: row cache objects 4 0 0 25 0.0
latch: shared pool 11 45 0 6 0.0
latch: redo writing 3 0 0 18 0.0
latch: library cache pin 1 0 0 2 0.0
rdbms ipc message 90,195 27 97,330 1079 1.1
pmon timer 3,232 92 7,008 2168 0.0
Streams AQ: qmn slave idle wait 257 0 7,004 27252 0.0
Streams AQ: qmn coordinator idle 514 50 7,004 13626 0.0
smon timer 403 0 6,968 17289 0.0
Streams AQ: waiting for time mana 32 97 6,475 ###### 0.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORA8/ora8 Snaps: 1-13
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 1004 54.7 2.6 4.2 8.0 30.6
Log archive I/O 926 89.8 .1 .2 .1 3.6 6.2
SGA: MMAN sleep for compon 183 .5 .5 1.1 2.2 95.6
SGA: allocation forcing co 34K .0 99.8 .2 .0
SQL*Net break/reset to cli 4014 59.2 .7 3.2 13.9 12.6 6.3 3.8 .2
SQL*Net more data to clien 668K 100.0 .0 .0 .0 .0 .0
buffer busy waits 768 83.3 2.1 1.4 2.5 3.0 2.3 5.3
buffer deadlock 3 100.0
buffer exterminate 97 1.0 1.0 97.9
control file parallel writ 4123 9.5 68.4 22.0
control file sequential re 56K 99.5 .0 .1 .2 .1 .0 .0
cursor: mutex X 98K 100.0 .0 .0 .0 .0
db file parallel read 256 47.3 .8 1.2 14.1 16.0 14.5 6.3
db file parallel write 29K .2 13.7 28.6 57.4 .1
db file scattered read 99K 61.7 3.0 3.0 8.0 9.8 8.7 5.8 .0
db file sequential read 1104K 25.6 6.4 15.0 28.1 14.4 7.8 2.7 .0
direct path read 28K 95.6 .1 .3 1.3 1.5 .7 .4
direct path read temp 12K 83.5 1.0 2.1 3.8 4.6 3.5 1.5
direct path write 2352 95.7 .0 .1 4.1 .0
direct path write temp 12K 46.7 .7 1.4 3.6 10.7 23.1 13.8
enq: CF - contention 5 20.0 80.0
enq: TX - index contention 12 25.0 8.3 8.3 8.3 33.3 16.7
enq: TX - row lock content 191 1.0 1.0 1.6 3.1 3.7 37.7 38.2 13.6
kksfbc child completion 89 100.0
latch free 642 17.8 12.8 21.8 19.0 10.0 6.7 12.0
latch: In memory undo latc 6 16.7 33.3 16.7 33.3
latch: cache buffer handle 69 1.4 10.1 10.1 7.2 8.7 62.3
latch: cache buffers chain 3703 16.6 7.2 13.1 20.1 15.6 12.0 14.4 1.1
latch: cache buffers lru c 34 17.6 11.8 5.9 20.6 8.8 17.6 17.6
latch: enqueue hash chains 42 4.8 7.1 4.8 2.4 4.8 76.2
latch: library cache 1757 8.5 7.9 12.6 15.8 17.8 13.0 20.7 3.8
latch: library cache lock 33 3.0 12.1 3.0 12.1 21.2 48.5
latch: library cache pin 4 25.0 50.0 25.0
latch: messages 3 33.3 33.3 33.3
latch: object queue header 5 20.0 40.0 40.0
latch: redo allocation 60 8.3 6.7 10.0 20.0 21.7 11.7 21.7
latch: redo writing 6 16.7 33.3 16.7 33.3
latch: row cache objects 51 11.8 7.8 19.6 19.6 15.7 11.8 13.7
latch: session allocation 31 9.7 6.5 12.9 6.5 9.7 16.1 38.7
latch: shared pool 539 12.1 6.1 13.5 18.6 20.2 13.2 16.3
latch: undo global data 2 50.0 50.0
library cache load lock 355 14.9 16.9 2.5 3.7 4.5 5.4 38.3 13.8
library cache lock 1 100.0
library cache pin 1905 3.7 4.0 3.0 2.2 3.6 3.0 37.0 43.5
log file parallel write 74K .0 16.8 50.8 32.4 .0
log file sequential read 1169 34.7 .2 .3 1.3 4.2 5.0 52.2 2.1
log file single write 180 1.7 2.2 53.3 37.2 5.6
log file switch completion 619 1.3 .2 .5 .5 1.3 2.6 93.7
log file sync 79K .2 .0 .0 .0 11.4 41.3 47.1
os thread startup 97 100.0
rdbms ipc reply 705 96.7 1.3 .7 .4 .4 .3 .1
Wait Event Histogram DB/Inst: ORA8/ora8 Snaps: 1-13
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
read by other session 4984 58.3 2.0 4.1 10.2 11.7 9.1 4.6
row cache lock 5 100.0
undo segment extension 313 99.7 .3
SQL*Net message from clien 1935K 19.3 5.6 3.2 18.3 25.7 4.6 15.1 8.1
SQL*Net message to client 1935K 100.0 .0 .0 .0 .0 .0 .0
SQL*Net more data from cli 92K 42.3 1.5 20.5 12.1 14.4 5.0 4.2 .1
Streams AQ: qmn coordinato 514 46.7 1.6 .4 1.0 .2 .2 50.0
Streams AQ: qmn slave idle 257 100.0
Streams AQ: waiting for ti 32 3.1 78.1 9.4 9.4
class slave wait 5 100.0
jobq slave wait 2260 .0 .4 99.6
pmon timer 3231 19.0 .3 .3 .2 .2 .1 4.2 75.7
rdbms ipc message 90K 16.3 1.4 3.1 3.2 6.7 12.9 38.7 17.6
smon timer 403 .2 .5 10.9 88.3
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 30,599
-> Captured SQL accounts for 91.3% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
10526.34 17,454 0.60 34.4 13338.04 87,050,006 1551469478
Module: eiip.exe
select * from
(
select t_account_stock.artid as artid,
t_account_stock.accid as accid,
t_account_stock.packageid
as packageid,
t_art_serial.serial as serial,
min(t_article.name) as name,
min(t_article.spec
) as spec,
min(t_article.factory) as factory,
1680.95 3,552 0.47 5.5 2233.90 11,159,671 381636793
Module: eiip.exe
select * from
(
select t_account_stksum.artid as artid,
t_account_stksum.accid as accid,
t_account_stksum.packag
eid as packageid,
min(t_article.name) as name,
min(t_article.spec) as spec,
min(t_arti
cle.factory) as factory,
min(t_article.unit) as
1184.46 157,636 0.01 3.9 3219.81 96,676,213 1682856450
Module: eiip.exe
BEGIN EIIP_SESSION.SP_SESSION_SET( :0,:1,:2); END;
1025.87 155,105 0.01 3.4 2010.18 26,662,202 1453269353
Module: eiip.exe
INSERT INTO T_ACTIVE_USER(SESSIONID,USERID) VALUES (:B2 ,:B1 )
1005.84 154,928 0.01 3.3 1723.63 25,551,838 3286148528
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
603.03 47 12.83 2.0 763.95 27,460,906 2357096082
Module: eiip.exe
select min(a.groupid) classid,
min(b.classcode) classcod
e,
c.deptid,
c.conterid,
c.conterid as d
ynamicid,
min(a.username) contername,
sum(c.amou
nt) saleamount,
sum(c.cashamount) cashamount,
su
m(c.tickamount) tickamount,
sum(c.sumarunpaid) sumunpaid
540.18 272 1.99 1.8 635.95 6,987,536 2979906516
select a.ARBSeqid,
a.lineno,
(a.unpaid - nvl(b.acka
mount,0)) as unpaidamount,
(a.tax - nvl(b.acktax,0)) a
s unpaidtax,
a.amount as billamount,
a.artyp
eid,
a.billtypeid,
a.deptid,
(select branc
hid from t_acc_bill_header where bseqid=a.bseqid) branchid,
497.24 1,131 0.44 1.6 949.50 13,600,211 3147084271
Module: eiip.exe
BEGIN :rc := EIIP_FINANCE_AR.SP_FINANCE_AR_BILL_SUBMIT(
:0,:1,:2,:3); END;
458.25 14 32.73 1.5 523.74 9,457,001 2896896999
Module: eiip.exe
SQL ordered by CPU DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 30,599
-> Captured SQL accounts for 91.3% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
select a.bseqid,
a.writeruid,
a.mast
ercustid as mastercustid,
a.custid as custid,
b.timewritten as timewritten,
b.notes,
b.writername,
t_bill_type.billtypename,
x0.cust
name custname,
x1.custname mastercustname,
t_user.userna
457.07 2,588 0.18 1.5 550.20 5,263,431 2343371767
Module: eiip.exe
select a.custid custid,
a.custname custname,
a.nickname nickname,
b.contactperson co
ntactperson,
b.contactphone contactphone,
b.h
eader header,
a.versionno versonno,
a
.mastercustid mastercustid,
mastercust.custname as masterc
455.92 1,496 0.30 1.5 529.24 11,578,500 2761638158
Module: eiip.exe
SELECT BSEQID FROM T_ACCBILL_STORE WHERE SBSEQID=:B1
446.56 231 1.93 1.5 533.07 37,208,778 1984092380
Module: eiip.exe
select a.classid,
b.classcode,
c.dynbseqid,
c.dynamicidone,
c.dynamicidtwo,
c.dynamicidt
hree,
c.catid,
a.artid,
a.name,
a.spec,
a.factory,
c.packquantities,
a.u
nit,
c.taxrate,
c.serial,
c.total,
443.32 35 12.67 1.4 586.80 46,564,995 3599591513
Module: eiip.exe
select a.zoneid as classid,
b.classcode,
a.custt
ypeid,
c.dynamicidone,
c.dynamicidtwo,
c
.endarunpaid,
c.arunpaid,
c.account,
a.c
ustname as dynamname,
(select classname from t_cust_type
where custtypeid = a.custtypeid) as classname,
(select
350.52 158,314 0.00 1.1 633.70 68,970,679 223026855
Module: eiip.exe
SELECT * FROM T_SESSION WHERE SESSIONID=:B1
334.33 432 0.77 1.1 393.02 16,754,493 2077050675
Module: eiip.exe
select a.classid,
b.classcode,
c.storeid,
a.name,
a.spec,
a.factory,
a.unit,
c.packquantities,
c.serial,
c.manufactured,
c.expiry,
c.storename,
c.artid as dynam
icidone,
c.artid as dynamicidtwo,
nvl(c.stocktot
326.83 872 0.37 1.1 388.22 2,511,845 898367765
Module: eiip.exe
SELECT a.artid,
a.name,
a.spec,
a.factory,
a.unit,
(
select sum(tota
SQL ordered by CPU DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 30,599
-> Captured SQL accounts for 91.3% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
l)
from t_account_stock,
t_dept_account
where t_
account_stock.accid = t_dept_account.accid and
t_account
_stock.artid = a.artid and
t_dept_account.deptid = :an_d
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 56,418
-> Captured SQL accounts for 78.8% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
13338.04 17,454 0.76 23.6 10526.34 81,674 1551469478
Module: eiip.exe
select * from
(
select t_account_stock.artid as artid,
t_account_stock.accid as accid,
t_account_stock.packageid
as packageid,
t_art_serial.serial as serial,
min(t_article.name) as name,
min(t_article.spec
) as spec,
min(t_article.factory) as factory,
3219.81 157,636 0.02 5.7 1184.46 1,266 1682856450
Module: eiip.exe
BEGIN EIIP_SESSION.SP_SESSION_SET( :0,:1,:2); END;
2233.90 3,552 0.63 4.0 1680.95 13,794 381636793
Module: eiip.exe
select * from
(
select t_account_stksum.artid as artid,
t_account_stksum.accid as accid,
t_account_stksum.packag
eid as packageid,
min(t_article.name) as name,
min(t_article.spec) as spec,
min(t_arti
cle.factory) as factory,
min(t_article.unit) as
2010.18 155,105 0.01 3.6 1025.87 52 1453269353
Module: eiip.exe
INSERT INTO T_ACTIVE_USER(SESSIONID,USERID) VALUES (:B2 ,:B1 )
1723.63 154,928 0.01 3.1 1005.84 15 3286148528
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
949.50 1,131 0.84 1.7 497.24 28,974 3147084271
Module: eiip.exe
BEGIN :rc := EIIP_FINANCE_AR.SP_FINANCE_AR_BILL_SUBMIT(
:0,:1,:2,:3); END;
763.95 47 16.25 1.4 603.03 6,630 2357096082
Module: eiip.exe
select min(a.groupid) classid,
min(b.classcode) classcod
e,
c.deptid,
c.conterid,
c.conterid as d
ynamicid,
min(a.username) contername,
sum(c.amou
nt) saleamount,
sum(c.cashamount) cashamount,
su
m(c.tickamount) tickamount,
sum(c.sumarunpaid) sumunpaid
694.49 71 9.78 1.2 24.05 87,210 163114745
Module: eiip.exe
select *
from
(
select nvl(t_acc_bill_detail.finaltotal,t_a
cc_bill_detail.total) total,
nvl(t_acc_bill_detail.fin
alprice,t_acc_bill_detail.price) price,
t_acc_bill_header.bi
lldate
from t_acc_bill_detail,
t_acc_bill_head
er
where ( t_acc_bill_detail.bseqid = t_acc_bill_header.bseq
635.95 272 2.34 1.1 540.18 930 2979906516
SQL ordered by Elapsed DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 56,418
-> Captured SQL accounts for 78.8% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
select a.ARBSeqid,
a.lineno,
(a.unpaid - nvl(b.acka
mount,0)) as unpaidamount,
(a.tax - nvl(b.acktax,0)) a
s unpaidtax,
a.amount as billamount,
a.artyp
eid,
a.billtypeid,
a.deptid,
(select branc
hid from t_acc_bill_header where bseqid=a.bseqid) branchid,
633.70 158,314 0.00 1.1 350.52 492 223026855
Module: eiip.exe
SELECT * FROM T_SESSION WHERE SESSIONID=:B1
606.12 11,876 0.05 1.1 14.25 3 2633677349
Module: eiip.exe
select a.mseqid, a.senduid, to_char(a.sendtime,'yyyy-mm-dd
hh24:mi:ss') sendtime, a.msg, b.username sendusername fr
om t_msg a, t_user b where sysdate > a.sendtime and a.s
endTime + a.BroadcastValidTime/60/24 >= sysdate and a.isBroa
dcast = 1 and a.senduid = b.userid(+)
586.80 35 16.77 1.0 443.32 2,774 3599591513
Module: eiip.exe
select a.zoneid as classid,
b.classcode,
a.custt
ypeid,
c.dynamicidone,
c.dynamicidtwo,
c
.endarunpaid,
c.arunpaid,
c.account,
a.c
ustname as dynamname,
(select classname from t_cust_type
where custtypeid = a.custtypeid) as classname,
(select
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 1,005,039,569
-> Captured SQL accounts for 71.5% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
96,676,213 157,636 613.3 9.6 1184.46 3219.81 1682856450
Module: eiip.exe
BEGIN EIIP_SESSION.SP_SESSION_SET( :0,:1,:2); END;
87,050,006 17,454 4,987.4 8.7 ######## 13338.04 1551469478
Module: eiip.exe
select * from
(
select t_account_stock.artid as artid,
t_account_stock.accid as accid,
t_account_stock.packageid
as packageid,
t_art_serial.serial as serial,
min(t_article.name) as name,
min(t_article.spec
) as spec,
min(t_article.factory) as factory,
68,970,679 158,314 435.7 6.9 350.52 633.70 223026855
Module: eiip.exe
SELECT * FROM T_SESSION WHERE SESSIONID=:B1
46,564,995 35 1,330,428.4 4.6 443.32 586.80 3599591513
Module: eiip.exe
select a.zoneid as classid,
b.classcode,
a.custt
ypeid,
c.dynamicidone,
c.dynamicidtwo,
c
.endarunpaid,
c.arunpaid,
c.account,
a.c
ustname as dynamname,
(select classname from t_cust_type
where custtypeid = a.custtypeid) as classname,
(select
44,770,178 86 520,583.5 4.5 299.43 427.20 3251837007
Module: eiip.exe
select a.classid,
b.classcode,
c.branchid,
c.bseqid,
a.artid,
a.name,
a.spec,
a.factory,
a.unit,
c.taxrate,
c.se
rial,
c.total,
c.price,
c.amount,
c.hscb,
c.hsml,
c.wsxs,
c.wscb,
37,208,778 231 161,077.0 3.7 446.56 533.07 1984092380
Module: eiip.exe
select a.classid,
b.classcode,
c.dynbseqid,
c.dynamicidone,
c.dynamicidtwo,
c.dynamicidt
hree,
c.catid,
a.artid,
a.name,
a.spec,
a.factory,
c.packquantities,
a.u
nit,
c.taxrate,
c.serial,
c.total,
32,591,461 148 220,212.6 3.2 295.88 429.05 3986488233
Module: eiip.exe
select b.zoneid as classid,
b.classcode,
0 statu
s,
c.custid,
a.custname,
a.custtypeid,
c.sellcashamount,
c.sellfundamount,
c.sel
lbillamount,
c.tickcashamount,
c.tickfundamount,
c.tickbillamount,
c.aheadcashamount,
c.
27,949,702 405 69,011.6 2.8 289.01 410.70 2267882680
Module: eiip.exe
SQL ordered by Gets DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 1,005,039,569
-> Captured SQL accounts for 71.5% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
select a.classid,
b.classcode,
c.accid,
a.name,
a.factory,
a.spec,
a.unit,
c.stktotal,
c.stkamount,
c.sumgjtotal,
c.stocktotal,
c.stockamount,
c.artid as dynami
cidone,
c.artid as dynamicidtwo,
nvl(c.reserveto
27,460,906 47 584,274.6 2.7 603.03 763.95 2357096082
Module: eiip.exe
select min(a.groupid) classid,
min(b.classcode) classcod
e,
c.deptid,
c.conterid,
c.conterid as d
ynamicid,
min(a.username) contername,
sum(c.amou
nt) saleamount,
sum(c.cashamount) cashamount,
su
m(c.tickamount) tickamount,
sum(c.sumarunpaid) sumunpaid
26,840,575 40 671,014.4 2.7 229.32 313.03 1093159929
Module: eiip.exe
select a.zoneid as classid,
b.classcode,
a.custt
ypeid,
c.dynamicidone,
c.dynamicidtwo,
c
.endarunpaid,
c.arunpaid,
c.account,
a.c
ustname as dynamname,
(select classname from t_cust_type
where custtypeid = a.custtypeid) as classname,
(select
26,662,202 155,105 171.9 2.7 1025.87 2010.18 1453269353
Module: eiip.exe
INSERT INTO T_ACTIVE_USER(SESSIONID,USERID) VALUES (:B2 ,:B1 )
25,551,838 154,928 164.9 2.5 1005.84 1723.63 3286148528
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
16,754,493 432 38,783.5 1.7 334.33 393.02 2077050675
Module: eiip.exe
select a.classid,
b.classcode,
c.storeid,
a.name,
a.spec,
a.factory,
a.unit,
c.packquantities,
c.serial,
c.manufactured,
c.expiry,
c.storename,
c.artid as dynam
icidone,
c.artid as dynamicidtwo,
nvl(c.stocktot
13,600,211 1,131 12,024.9 1.4 497.24 949.50 3147084271
Module: eiip.exe
BEGIN :rc := EIIP_FINANCE_AR.SP_FINANCE_AR_BILL_SUBMIT(
:0,:1,:2,:3); END;
11,578,500 1,496 7,739.6 1.2 455.92 529.24 2761638158
Module: eiip.exe
SELECT BSEQID FROM T_ACCBILL_STORE WHERE SBSEQID=:B1
11,159,671 3,552 3,141.8 1.1 1680.95 2233.90 381636793
Module: eiip.exe
select * from
(
select t_account_stksum.artid as artid,
SQL ordered by Gets DB/Inst: ORA8/ora8 Snaps: 1-13
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 1,005,039,569
-> Captured SQL accounts for 71.5% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
t_account_stksum.accid as accid,
t_account_stksum.packag
eid as packageid,
min(t_article.name) as name,
min(t_article.spec) as spec,
min(t_arti
cle.factory) as factory,
min(t_article.unit) as
10,766,767 43 250,389.9 1.1 97.34 157.19 1045025802
Module: eiip.exe
select /*+rule*/ a.APBSeqid,
a.lineno,
(a.unpaid -
nvl(b.ackamount,0)) as unpaidamount,
(a.tax - nvl(b.ac
ktax,0)) as unpaidtax,
a.amount as billamount,
a.aptypeid,
a.billtypeid,
a.deptid,
(select branchid from t_acc_bill_header where bseqid = a.bseqi
10,608,915 398 26,655.6 1.1 49.64 63.49 1767407931
Module: eiip.exe
SELECT COUNT(*) FROM T_SESSION WHERE (SYSDATE - LASTACTIVETIME)<
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: ORA8/ora8 Snaps: 1-13
-> End Disk Reads Threshold: 1000 Total Disk Reads: 2,759,400
-> Captured SQL accounts for 46.4% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
726,334 6 121,055.7 26.3 133.42 520.39 1182428097
Module: eiip.exe
select a.userid,
a.username,
count(b.pageno) pagecount,
count(distinct b.sbseqid) billcount,
sum(c.artcount) artcoun
t,
sum(c.packcount) packcount,
min(t_org_unit.classcode) c
lasscode
from (
select aa.sbseqid,
bb.pageno,
count(cc.artid) artcount,
sum(trunc(cc.totalrequest / ff.p
87,210 71 1,228.3 3.2 24.05 694.49 163114745
Module: eiip.exe
select *
from
(
select nvl(t_acc_bill_detail.finaltotal,t_a
cc_bill_detail.total) total,
nvl(t_acc_bill_detail.fin
alprice,t_acc_bill_detail.price) price,
t_acc_bill_header.bi
lldate
from t_acc_bill_detail,
t_acc_bill_head
er
where ( t_acc_bill_detail.bseqid = t_acc_bill_header.bseq
81,674 17,454 4.7 3.0 ######## 13338.04 1551469478
Module: eiip.exe
select * from
(
select t_account_stock.artid as artid,
t_account_stock.accid as accid,
t_account_stock.packageid
as packageid,
t_art_serial.serial as serial,
min(t_article.name) as name,
min(t_article.spec
) as spec,
min(t_article.factory) as factory,
41,301 132 312.9 1.5 97.30 179.81 1312032146
Module: eiip.exe
select min(a.groupid) classid,
min(b.classcode) classcod
e,
c.deptid,
c.writeruid,
c.writeruid as
dynamicid,
c.catid,
c.catid as dynamicidtow,
min(a.username) writername,
sum(c.artidcont) artid
cont,
sum(c.amount) amount,
sum(c.hscb) hscb,
40,564 8 5,070.5 1.5 81.55 502.71 3282137894
Module: eiip.exe
select a.classid,
b.classcode,
c.branchid,
c.catid,
c.dynamicidone,
c.dynamicidtwo,
a.name,
a.spec,
a.factory,
a.unit,
c.taxrate,
c.total,
c.amount,
c.h
scb,
c.hsml,
c.wsxs,
c.wscb,
c.w
28,974 1,131 25.6 1.1 497.24 949.50 3147084271
Module: eiip.exe
BEGIN :rc := EIIP_FINANCE_AR.SP_FINANCE_AR_BILL_SUBMIT(
:0,:1,:2,:3); END;
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: ORA8/ora8 Snaps: 1-13
-> End Executions Threshold: 100 Total Executions: 4,850,515
-> Captured SQL accounts for 91.7% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
165,615 165,615 1.0 0.00 0.00 419847860
Module: eiip.exe
SELECT SUBJECTNAME,PARENT,CLASSLEVEL FROM T_SUBJECT WHERE SUBJEC
TID=:B1
158,314 158,343 1.0 0.00 0.00 223026855
Module: eiip.exe
SELECT * FROM T_SESSION WHERE SESSIONID=:B1
157,636 157,534 1.0 0.01 0.02 1682856450
Module: eiip.exe
BEGIN EIIP_SESSION.SP_SESSION_SET( :0,:1,:2); END;
155,132 155,117 1.0 0.00 0.00 2682616573
Module: eiip.exe
UPDATE T_SESSION SET DEPTID=:B2 WHERE SESSIONID=:B1
155,105 356 0.0 0.01 0.01 1453269353
Module: eiip.exe
INSERT INTO T_ACTIVE_USER(SESSIONID,USERID) VALUES (:B2 ,:B1 )
154,928 154,845 1.0 0.01 0.01 3286148528
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
154,778 154,772 1.0 0.00 0.00 412005419
Module: eiip.exe
UPDATE T_SESSION SET USERID=:B2 WHERE SESSIONID=:B1
49,222 49,220 1.0 0.00 0.00 415249194
Module: eiip.exe
SELECT NVL(BATCHMATCH,0),NVL(NEGATIVESTOCK,0),NVL(PACKAGEMATCH,0
) FROM T_STORE_PARAM WHERE STOREID=:B1
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: ORA8/ora8 Snaps: 1-13
-> End Parse Calls Threshold: 1000 Total Parse Calls: 1,845,240
-> Captured SQL accounts for 91.3% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
157,671 157,636 8.54 1682856450
Module: eiip.exe
BEGIN EIIP_SESSION.SP_SESSION_SET( :0,:1,:2); END;
154,478 154,928 8.37 3286148528
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
48,486 48,483 2.63 33511211
Module: eiip.exe
select sys_version , nvl ( forceupgrade , 0 ) from t_sysinfo whe
re sys_id =:1
43,611 43,610 2.36 3428178452
Module: eiip.exe
BEGIN EIIP_MISC.SP_SERVER_DATETIME( :0); END;
38,030 38,030 2.06 1175164739
Module: eiip.exe
select zonelimited , custlimited from t_article where artid =:1
30,500 30,499 1.65 2928023125
Module: eiip.exe
BEGIN :rc := EIIP_SALE.SP_CUST_ART_LIMIT_TYPE( :0,:1); E
ND;
20,967 20,967 1.14 620732336
Module: eiip.exe
BEGIN :rc := EIIP_SALE.SP_CUST_ART_LIMIT_ZONE( :0,:1); E
ND;
20,955 20,955 1.14 3071972652
Module: eiip.exe
select nvl ( allowdecimal , 0 ) , nvl ( salelimitcontrol , 0 ) ,
nvl ( smallpackcontrol , 0 ) from t_dept_param where deptid =:1
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORA8/ora8 Snaps: 1-13
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 2,992,103 415.7 37.0
CPU used when call started 2,983,300 414.5 36.9
CR blocks created 359,513 50.0 4.5
Cached Commit SCN referenced 195,827,155 27,209.6 2,421.9
Commit SCN cached 2,253 0.3 0.0
DB time 6,515,503 905.3 80.6
DBWR checkpoint buffers written 226,826 31.5 2.8
DBWR checkpoints 18 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR transaction table writes 5,962 0.8 0.1
DBWR undo block writes 43,358 6.0 0.5
IMU CR rollbacks 77,460 10.8 1.0
IMU Flushes 1,141 0.2 0.0
IMU Redo allocation size 9,037,500 1,255.7 111.8
IMU commits 1,352 0.2 0.0
IMU contention 84 0.0 0.0
IMU pool not allocated 78,895 11.0 1.0
IMU recursive-transaction flush 0 0.0 0.0
IMU undo allocation size 23,269,648 3,233.2 287.8
IMU- failed to get a private stra 78,895 11.0 1.0
SMON posted for undo segment reco 9 0.0 0.0
SMON posted for undo segment shri 208 0.0 0.0
SQL*Net roundtrips to/from client 1,931,996 268.4 23.9
active txn count during cleanout 1,617,315 224.7 20.0
application wait time 13,093 1.8 0.2
background checkpoints completed 18 0.0 0.0
background checkpoints started 18 0.0 0.0
background timeouts 25,340 3.5 0.3
branch node splits 3 0.0 0.0
buffer is not pinned count 583,642,643 81,095.3 7,218.2
buffer is pinned count 2,483,538,011 345,079.6 30,715.2
bytes received via SQL*Net from c 526,883,882 73,208.8 6,516.2
bytes sent via SQL*Net to client 1,698,597,312 236,014.6 21,007.4
calls to get snapshot scn: kcmgss 7,620,991 1,058.9 94.3
calls to kcmgas 532,756 74.0 6.6
calls to kcmgcs 13,748 1.9 0.2
change write time 7,949 1.1 0.1
cleanout - number of ktugct calls 301,359 41.9 3.7
cleanouts and rollbacks - consist 285,363 39.7 3.5
cleanouts only - consistent read 3,389 0.5 0.0
cluster key scan block gets 122,992 17.1 1.5
cluster key scans 57,108 7.9 0.7
commit batch/immediate performed 3,239 0.5 0.0
commit batch/immediate requested 3,239 0.5 0.0
commit cleanout failures: block l 18 0.0 0.0
commit cleanout failures: buffer 105 0.0 0.0
commit cleanout failures: callbac 430 0.1 0.0
commit cleanout failures: cannot 93 0.0 0.0
commit cleanouts 762,676 106.0 9.4
commit cleanouts successfully com 762,030 105.9 9.4
commit immediate performed 3,239 0.5 0.0
commit immediate requested 3,239 0.5 0.0
commit txn count during cleanout 6,126 0.9 0.1
concurrency wait time 295,449 41.1 3.7
consistent changes 78,257,664 10,873.7 967.9
consistent gets 998,904,627 138,794.6 12,354.0
Instance Activity Stats DB/Inst: ORA8/ora8 Snaps: 1-13
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
consistent gets - examination 413,543,265 57,460.5 5,114.5
consistent gets direct 25,025 3.5 0.3
consistent gets from cache 998,879,604 138,791.1 12,353.7
current blocks converted for CR 8 0.0 0.0
cursor authentications 55,979 7.8 0.7
data blocks consistent reads - un 78,255,817 10,873.4 967.8
db block changes 5,491,001 763.0 67.9
db block gets 6,134,640 852.4 75.9
db block gets direct 241 0.0 0.0
db block gets from cache 6,134,399 852.4 75.9
deferred (CURRENT) block cleanout 264,991 36.8 3.3
dirty buffers inspected 3,856 0.5 0.1
enqueue conversions 74,267 10.3 0.9
enqueue deadlocks 3 0.0 0.0
enqueue releases 1,269,903 176.5 15.7
enqueue requests 1,270,055 176.5 15.7
enqueue timeouts 13 0.0 0.0
enqueue waits 193 0.0 0.0
exchange deadlocks 3 0.0 0.0
execute count 4,850,515 674.0 60.0
frame signature mismatch 0 0.0 0.0
free buffer inspected 2,557,204 355.3 31.6
free buffer requested 2,957,267 410.9 36.6
heap block compress 32,606 4.5 0.4
hot buffers moved to head of LRU 4,350,214 604.5 53.8
immediate (CR) block cleanout app 288,752 40.1 3.6
immediate (CURRENT) block cleanou 52,191 7.3 0.7
index fast full scans (full) 5,264 0.7 0.1
index fetch by key 244,279,564 33,941.9 3,021.1
index scans kdiixs1 25,277,023 3,512.2 312.6
leaf node 90-10 splits 458 0.1 0.0
leaf node splits 2,276 0.3 0.0
lob reads 6,555 0.9 0.1
lob writes 323 0.0 0.0
lob writes unaligned 323 0.0 0.0
logons cumulative 1,044 0.2 0.0
messages received 107,603 15.0 1.3
messages sent 107,602 15.0 1.3
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 556,936,849 77,384.6 6,887.9
opened cursors cumulative 892,717 &