Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
196335337 DB11G 1 05-Oct-10 12:52 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
oel5-11gR2-demo. Linux x86 64-bit 1 0 0 2.0
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 3 05-Oct-10 16:00:03 40 5.1
End Snap: 4 05-Oct-10 17:00:05 40 5.1
Elapsed: 60.03 (mins) Av Act Sess: 0.4
DB time: 25.94 (mins) DB CPU: 13.92 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 188M Std Block Size: 8K
Shared Pool: 288M Log Buffer: 2,160K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.4 0.0 0.01 0.06
DB CPU(s): 0.2 0.0 0.00 0.03
Redo size: 20,766.2 2,092.1
Logical reads: 548.5 55.3
Block changes: 138.3 13.9
Physical reads: 22.9 2.3
Physical writes: 16.5 1.7
User calls: 7.0 0.7
Parses: 9.2 0.9
Hard parses: 0.0 0.0
W/A MB processed: 1.8 0.2
Logons: 0.1 0.0
Executes: 62.6 6.3
Rollbacks: 0.0 0.0
Transactions: 9.9
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 95.83 Optimal W/A Exec %: 100.00
Library Hit %: 99.99 Soft Parse %: 99.94
Execute to Parse %: 85.33 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 46.70 % Non-Parse CPU: 99.64
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 80.20 80.54
% SQL with executions>1: 58.08 59.47
% Memory for SQL w/exec>1: 61.45 65.67
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
PL/SQL lock timer 144,426 24,011 166 95.4
CPU time 771 3.1
log file sync 11,808 142 12 .6
db file sequential read 78,045 62 1 .2
os thread startup 138 52 380 .2
-------------------------------------------------------------
Host CPU (CPUs: 1 Cores: 0 Sockets: 0)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
1.98 0.00 22.28 4.71 71.94 0.08
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 3,549.9
Host: Busy CPU time (s): 996.2
% of time Host is Busy: 28.1
Instance: Total CPU time (s): 965.3
% of Busy CPU used for Instance: 96.9
Instance: Total Database time (s): 1,984.6
%DB time waiting for CPU (Resource Mgr): 0.0
Virtual Memory Paging
~~~~~~~~~~~~~~~~~~~~~
KB paged out per sec: 209.0
KB paged in per sec: 14.5
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 2,011.3 2,011.3
SGA use (MB): 800.4 800.4
PGA use (MB): 129.6 127.7
% Host Mem used for SGA+PGA: 46.2 46.1
-------------------------------------------------------------
Time Model System Stats DB/Inst: DB11G/DB11G Snaps: 3-4
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,306.2 83.9
DB CPU 835.3 53.7
PL/SQL execution elapsed time 455.7 29.3
parse time elapsed 5.2 .3
repeated bind elapsed time 0.0 .0
sequence load elapsed time 0.0 .0
DB time 1,556.2
background elapsed time 428.3
background cpu time 130.0
-------------------------------------------------------------
Foreground Wait Events DB/Inst: DB11G/DB11G Snaps: 3-4
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer 144,426 100 24,011 166 4.0 95.4
log file sync 11,808 0 142 12 0.3 .6
db file sequential read 76,864 0 60 1 2.1 .2
latch free 698 0 10 14 0.0 .0
latch: cache buffers lru cha 430 0 7 16 0.0 .0
library cache: mutex X 152 0 6 40 0.0 .0
latch: cache buffers chains 332 0 5 17 0.0 .0
latch: row cache objects 206 0 3 15 0.0 .0
latch: object queue header o 97 0 1 12 0.0 .0
buffer busy waits 70 0 1 15 0.0 .0
latch: In memory undo latch 63 0 1 16 0.0 .0
latch: redo allocation 62 0 1 13 0.0 .0
cursor: pin S 31 0 1 25 0.0 .0
db file parallel read 524 0 0 1 0.0 .0
read by other session 21 0 0 20 0.0 .0
latch: enqueue hash chains 26 0 0 15 0.0 .0
Disk file operations I/O 112 0 0 2 0.0 .0
latch: redo copy 7 0 0 27 0.0 .0
enq: TX - row lock contentio 10 0 0 17 0.0 .0
db file scattered read 107 0 0 2 0.0 .0
latch: session allocation 8 0 0 16 0.0 .0
latch: messages 6 0 0 11 0.0 .0
latch: shared pool 2 0 0 16 0.0 .0
cursor: mutex S 1 0 0 10 0.0 .0
control file sequential read 468 0 0 0 0.0 .0
asynch descriptor resize 710 100 0 0 0.0 .0
jobq slave wait 7,631 100 3,916 513 0.2
SQL*Net message from client 24,737 0 3,908 158 0.7
SQL*Net message to client 24,748 0 3 0 0.7
-------------------------------------------------------------
Background Wait Events DB/Inst: DB11G/DB11G Snaps: 3-4
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
os thread startup 138 0 52 380 0.0 .2
log file parallel write 30,685 0 45 1 0.9 .2
db file async I/O submit 17,082 0 38 2 0.5 .1
control file parallel write 1,595 0 3 2 0.0 .0
db file sequential read 1,181 0 2 2 0.0 .0
LGWR wait for redo copy 137 0 2 12 0.0 .0
control file sequential read 3,080 0 1 0 0.1 .0
latch: redo allocation 46 0 0 8 0.0 .0
latch: cache buffers lru cha 26 0 0 13 0.0 .0
db file scattered read 124 0 0 1 0.0 .0
latch free 12 0 0 11 0.0 .0
Disk file operations I/O 76 0 0 1 0.0 .0
latch: object queue header o 8 0 0 10 0.0 .0
latch: redo writing 2 0 0 42 0.0 .0
latch: cache buffers chains 2 0 0 35 0.0 .0
direct path sync 2 0 0 34 0.0 .0
reliable message 6 0 0 11 0.0 .0
rdbms ipc reply 8 0 0 6 0.0 .0
enq: PR - contention 1 0 0 46 0.0 .0
latch: messages 6 0 0 7 0.0 .0
asynch descriptor resize 201 100 0 0 0.0 .0
latch: shared pool 1 0 0 23 0.0 .0
latch: checkpoint queue latc 1 0 0 21 0.0 .0
ADR block file read 15 0 0 1 0.0 .0
buffer busy waits 5 0 0 3 0.0 .0
latch: enqueue hash chains 1 0 0 5 0.0 .0
latch: call allocation 1 0 0 5 0.0 .0
direct path write 4 0 0 1 0.0 .0
latch: In memory undo latch 4 0 0 1 0.0 .0
db file single write 12 0 0 0 0.0 .0
log file single write 4 0 0 0 0.0 .0
ADR block file write 5 0 0 0 0.0 .0
rdbms ipc message 47,627 36 42,817 899 1.3
Streams AQ: waiting for time 5 60 7,540 ###### 0.0
DIAG idle wait 7,039 100 7,196 1022 0.2
Space Manager: slave idle wa 1,425 93 6,826 4790 0.0
smon timer 33 18 3,657 ###### 0.0
Streams AQ: qmn coordinator 258 50 3,615 14010 0.0
Streams AQ: qmn slave idle w 132 0 3,614 27382 0.0
shared server idle wait 120 100 3,602 30019 0.0
dispatcher timer 60 100 3,601 60011 0.0
pmon timer 1,197 100 3,598 3006 0.0
class slave wait 20 0 0 0 0.0
-------------------------------------------------------------
Wait Events (fg and bg) DB/Inst: DB11G/DB11G Snaps: 3-4
-> 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 %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer 144,426 100 24,011 166 4.0 95.4
log file sync 11,808 0 142 12 0.3 .6
db file sequential read 78,045 0 62 1 2.2 .2
os thread startup 138 0 52 380 0.0 .2
log file parallel write 30,685 0 45 1 0.9 .2
db file async I/O submit 17,082 0 38 2 0.5 .1
latch free 710 0 10 14 0.0 .0
latch: cache buffers lru cha 456 0 7 16 0.0 .0
library cache: mutex X 152 0 6 40 0.0 .0
latch: cache buffers chains 334 0 6 17 0.0 .0
latch: row cache objects 206 0 3 15 0.0 .0
control file parallel write 1,595 0 3 2 0.0 .0
LGWR wait for redo copy 137 0 2 12 0.0 .0
latch: object queue header o 105 0 1 12 0.0 .0
latch: redo allocation 108 0 1 11 0.0 .0
buffer busy waits 75 0 1 14 0.0 .0
latch: In memory undo latch 67 0 1 15 0.0 .0
cursor: pin S 31 0 1 25 0.0 .0
control file sequential read 3,548 0 1 0 0.1 .0
db file parallel read 524 0 0 1 0.0 .0
read by other session 21 0 0 20 0.0 .0
latch: enqueue hash chains 27 0 0 14 0.0 .0
Disk file operations I/O 188 0 0 2 0.0 .0
db file scattered read 231 0 0 1 0.0 .0
latch: redo copy 7 0 0 27 0.0 .0
enq: TX - row lock contentio 10 0 0 17 0.0 .0
latch: session allocation 8 0 0 16 0.0 .0
latch: messages 12 0 0 9 0.0 .0
latch: redo writing 2 0 0 42 0.0 .0
direct path sync 2 0 0 34 0.0 .0
reliable message 6 0 0 11 0.0 .0
latch: shared pool 3 0 0 18 0.0 .0
rdbms ipc reply 8 0 0 6 0.0 .0
enq: PR - contention 1 0 0 46 0.0 .0
asynch descriptor resize 911 100 0 0 0.0 .0
latch: checkpoint queue latc 1 0 0 21 0.0 .0
ADR block file read 15 0 0 1 0.0 .0
cursor: mutex S 1 0 0 10 0.0 .0
latch: call allocation 1 0 0 5 0.0 .0
direct path write 4 0 0 1 0.0 .0
db file single write 12 0 0 0 0.0 .0
log file single write 4 0 0 0 0.0 .0
ADR block file write 5 0 0 0 0.0 .0
rdbms ipc message 47,627 36 42,817 899 1.3
Streams AQ: waiting for time 5 60 7,540 ###### 0.0
DIAG idle wait 7,039 100 7,196 1022 0.2
Space Manager: slave idle wa 1,425 93 6,826 4790 0.0
jobq slave wait 7,631 100 3,916 513 0.2
SQL*Net message from client 24,737 0 3,908 158 0.7
Wait Events (fg and bg) DB/Inst: DB11G/DB11G Snaps: 3-4
-> 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 %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
smon timer 33 18 3,657 ###### 0.0
Streams AQ: qmn coordinator 258 50 3,615 14010 0.0
Streams AQ: qmn slave idle w 132 0 3,614 27382 0.0
shared server idle wait 120 100 3,602 30019 0.0
dispatcher timer 60 100 3,601 60011 0.0
pmon timer 1,197 100 3,598 3006 0.0
SQL*Net message to client 24,748 0 3 0 0.7
class slave wait 20 0 0 0 0.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: DB11G/DB11G Snaps: 3-4
-> 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
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read 15 86.7 6.7 6.7
ADR block file write 5 100.0
ADR file lock 6 100.0
Disk file operations I/O 188 84.6 12.2 .5 .5 .5 .5 1.1
LGWR wait for redo copy 137 8.8 5.1 18.2 16.1 30.7 17.5 3.6
PL/SQL lock timer 144K 100.0
asynch descriptor resize 911 99.9 .1
buffer busy waits 75 6.7 6.7 10.7 17.3 30.7 18.7 9.3
control file parallel writ 1595 34.7 51.2 11.1 .8 1.7 .5 .1
control file sequential re 3548 98.2 1.5 .0 .1 .1 .1
cursor: mutex S 1 100.0
cursor: pin S 31 12.9 3.2 12.9 12.9 25.8 16.1 16.1
db file async I/O submit 17K 90.1 2.5 .6 1.2 1.8 2.1 1.8
db file parallel read 524 93.3 3.1 .2 1.7 .6 1.1
db file scattered read 231 88.3 7.8 1.3 .4 1.3 .9
db file sequential read 78K 92.1 4.5 .7 .6 1.2 .6 .2
db file single write 12 100.0
direct path sync 2 50.0 50.0
direct path write 4 75.0 25.0
enq: PR - contention 1 100.0
enq: TX - row lock content 10 30.0 10.0 60.0
latch free 710 4.1 1.1 6.5 24.6 31.4 25.8 6.5
latch: In memory undo latc 67 11.9 3.0 10.4 7.5 23.9 37.3 6.0
latch: cache buffers chain 334 2.1 5.4 9.0 15.6 28.7 28.4 10.8
latch: cache buffers lru c 456 3.5 6.1 11.4 15.6 27.9 24.8 10.7
latch: call allocation 1 100.0
latch: checkpoint queue la 1 100.0
latch: enqueue hash chains 27 7.4 25.9 51.9 7.4 7.4
latch: messages 12 8.3 16.7 25.0 33.3 16.7
latch: object queue header 105 10.5 3.8 19.0 22.9 17.1 19.0 7.6
latch: redo allocation 108 4.6 12.0 14.8 15.7 32.4 16.7 3.7
latch: redo copy 7 28.6 14.3 28.6 28.6
latch: redo writing 2 50.0 50.0
latch: row cache objects 206 1.0 1.0 10.2 27.2 29.6 22.3 8.7
latch: session allocation 8 12.5 12.5 25.0 37.5 12.5
latch: shared pool 3 33.3 66.7
library cache: mutex X 152 5.9 2.0 12.5 11.8 13.8 22.4 31.6
log file parallel write 30K 48.0 37.9 11.0 1.0 1.6 .4 .0
log file sequential read 4 100.0
log file single write 4 100.0
log file sync 11K 12.1 5.3 10.2 14.3 32.2 21.1 4.8
os thread startup 138 98.6 1.4
rdbms ipc reply 8 50.0 12.5 12.5 25.0
read by other session 21 4.8 9.5 4.8 9.5 42.9 9.5 19.0
reliable message 6 16.7 16.7 33.3 33.3
DIAG idle wait 7037 90.6 9.4
SQL*Net message from clien 24K 100.0
SQL*Net message to client 24K 99.3 .1 .1 .2 .3 .0 .0
Space Manager: slave idle 1425 1.2 .1 .2 2.0 96.4
Streams AQ: qmn coordinato 258 14.3 18.2 4.3 2.7 7.8 2.7 50.0
Streams AQ: qmn slave idle 132 2.3 97.7
Wait Event Histogram DB/Inst: DB11G/DB11G Snaps: 3-4
-> 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
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Streams AQ: waiting for ti 5 40.0 20.0 40.0
class slave wait 20 100.0
dispatcher timer 60 100.0
jobq slave wait 7631 100.0
pmon timer 1197 100.0
rdbms ipc message 47K 1.3 .3 .7 3.1 7.5 12.9 53.8 20.2
shared server idle wait 120 100.0
smon timer 33 6.1 30.3 63.6
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: DB11G/DB11G Snaps: 3-4
-> Total DB CPU (s): 835
-> Captured SQL accounts for 125.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
---------- ------------ ---------- ------ ---------- --------------- ----------
431.07 7,229 0.06 51.6 769.82 1,346,309 3565022785
Module: JDBC Thin Client
BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END;
167.32 8,689 0.02 20.0 317.47 305,874 1852190137
Module: JDBC Thin Client
BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END;
99.40 21,680 0.00 11.9 172.85 610,438 2319948924
Module: New Order
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_
STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO
M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV
52.39 4,343 0.01 6.3 91.75 145,639 3934876099
Module: JDBC Thin Client
BEGIN :1 := orderentry.processorders(:2 ,:3 ); END;
40.39 19,495 0.00 4.8 76.46 299,605 2698772428
Module: New Order
INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT
_PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1)
31.21 20,344 0.00 3.7 50.80 81,376 3107051069
Module: Browse and Update Orders
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG
E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM
CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1
27.86 2,969 0.01 3.3 44.67 82,763 3646320859
Module: JDBC Thin Client
BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END
;
26.62 25,980 0.00 3.2 60.25 236,021 1113394757
Module: Browse Products
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_
STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO
M PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVE
23.97 1,469 0.02 2.9 45.38 24,384 2835506982
Module: JDBC Thin Client
BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END;
22.05 7,226 0.00 2.6 47.56 110,594 2082097592
Module: New Order
INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR
DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO
:O0
SQL ordered by CPU DB/Inst: DB11G/DB11G Snaps: 3-4
-> Total DB CPU (s): 835
-> Captured SQL accounts for 125.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
---------- ------------ ---------- ------ ---------- --------------- ----------
16.33 21,460 0.00 2.0 36.07 122,865 2308915322
Module: New Order
SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES
I WHERE I.PRODUCT_ID = :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I
.WAREHOUSE_ID = :B1
14.11 4,342 0.00 1.7 26.86 83,202 3149415580
Module: Process Orders
WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER
S WHERE ORDER_STATUS <= 4 AND ROWNUM < 10 ) SELECT O.ORDER_ID, O
I.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORD
ER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOT
14.03 7,229 0.00 1.7 31.42 58,677 3812575787
Module: New Order
UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOOR(DB
MS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = :B
1
12.14 59 0.21 1.5 22.44 28,852 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;
11.25 20,346 0.00 1.3 24.98 61,698 1809837244
Module: New Order
INSERT INTO LOGON VALUES (:B2 , :B1 )
10.52 2,969 0.00 1.3 17.76 55,967 3172424902
Module: New Customer
INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NA
ME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOU
NT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RAN
DOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOO
9.83 4,342 0.00 1.2 21.68 58,063 4220433507
Module: Process Orders
UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS =
FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: DB11G Instance: DB11G Snaps: 3 -4
-> Total DB Time (s): 1,556
-> Captured SQL accounts for 124.7% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
769.82 7,229 0.11 49.5 431.07 49,112 3565022785
Module: JDBC Thin Client
BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END;
317.47 8,689 0.04 20.4 167.32 14,233 1852190137
Module: JDBC Thin Client
BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END;
172.85 21,680 0.01 11.1 99.40 11,928 2319948924
Module: New Order
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_
STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO
M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV
91.75 4,343 0.02 5.9 52.39 3,940 3934876099
Module: JDBC Thin Client
BEGIN :1 := orderentry.processorders(:2 ,:3 ); END;
76.46 19,495 0.00 4.9 40.39 15,399 2698772428
Module: New Order
INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT
_PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1)
60.25 25,980 0.00 3.9 26.62 58 1113394757
Module: Browse Products
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_
STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO
M PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVE
50.80 20,344 0.00 3.3 31.21 28,303 3107051069
Module: Browse and Update Orders
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG
E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM
CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1
47.56 7,226 0.01 3.1 22.05 7,876 2082097592
Module: New Order
INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR
DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO
:O0
45.38 1,469 0.03 2.9 23.97 7,133 2835506982
Module: JDBC Thin Client
BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END;
44.67 2,969 0.02 2.9 27.86 2,599 3646320859
Module: JDBC Thin Client
BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END
;
SQL ordered by Elapsed time for DB: DB11G Instance: DB11G Snaps: 3 -4
-> Total DB Time (s): 1,556
-> Captured SQL accounts for 124.7% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
36.07 21,460 0.00 2.3 16.33 2,007 2308915322
Module: New Order
SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES
I WHERE I.PRODUCT_ID = :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I
.WAREHOUSE_ID = :B1
31.42 7,229 0.00 2.0 14.03 50 3812575787
Module: New Order
UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOOR(DB
MS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = :B
1
Comments
Post a Comment