Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

STATSPACK report

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

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL