New application and AWR report advice

Hi All,

We just added a new application onto our database server, which had been running smoothly until the new third party application was added. The AWR report which I have summarized below is over a 2 hr period, and the server has 8 x cpu each a quad core and 32GB of memory.  

Before I push back on the vendor I would link to sanity check what I have concluded, which is the application would appear to be parsing more statements than it is actually executing.  

Before Application was loaded this is a summary of database activity:  

Begin Snap:     7931    02-Aug-08 14:00:16    355    6.3
End Snap:       7933    02-Aug-08 16:00:10    356    4.3
Elapsed:         119.92 (mins)          
DB Time:          42.96 (mins)
 

Load Profile  

                         Per Second      Per Transaction
Redo size:            5,647.31          3,053.65
Logical reads:     17,116.26          9,255.21
Block changes:         37.29             20.17
Physical reads:           1.59              0.86
Physical writes:           2.31              1.25
User calls:               271.12            146.60
Parses:                     75.47             40.81
Hard parses:               1.81              0.98
Sorts:                       15.77              8.53
Logons:                      0.05              0.02
Executes:                104.82             56.68
Transactions:           1.85
 
 

Instance Efficiency Percentages (Target 100%)    

Buffer Nowait %:                100.00    Redo NoWait %:       100.00
Buffer Hit %:                        99.99    In-memory Sort %:    100.00
Library Hit %:                       97.92    Soft Parse %:         97.61
Execute to Parse %:             28.00    Latch Hit %:          99.75
Parse CPU to Parse Elapsd %:    70.35    % Non-Parse CPU:      97.12
 
 

Top 5 Timed Events    

Event                                 Waits    Time(s)    Avg Wait(ms)    % Total Call Time    Wait Class
CPU time                                        2,360                               91.6     
control file sequential read    45,602    78            2                    3.0                         System I/O
log file parallel write               9,769    50            5                     1.9                        System I/O
log file sync                          6,937    44            6                      1.7                        Commit
db file scattered read            1,372     25           18                     1.0                        User I/O
 
 
Statistic Name                                Time (s)    % of DB Time
sql execute elapsed time                      2,471.37    95.89
DB CPU                                              2,359.96    91.57
parse time elapsed                                   96.51     3.74
hard parse elapsed time                            78.78     3.06
PL/SQL execution elapsed time                 18.92     0.73
hard parse (sharing criteria) elapsed time      1.09     0.04
PL/SQL compilation elapsed time                 0.46     0.02
connection management call elapsed time     0.45     0.02
sequence load elapsed time                        0.18     0.01
repeated bind elapsed time                          0.17     0.01
failed parse elapsed time                            0.08     0.00
hard parse (bind mismatch) elapsed time      0.01     0.00
DB time                                                 2,577.30     
background elapsed time                         108.94     
background cpu time                               24.34   
 
 

After the new application was loaded:  

                     Snap Id      Snap Time      Sessions Curs/Sess
 
                       --------- ------------------- -------- --------- 
Begin Snap:     16318 19-Sep-08 14:00:08       106       9.8
  End Snap:     16320 19-Sep-08 16:00:32        93      10.7
   Elapsed:              120.40 (mins)
   DB Time:              794.98 (mins)
 
 
 

Load Profile  

~~~~~~~~~~~~                            Per Second       Per Transaction
 
                                   ---------------       --------------- 
                  Redo size:            131,778.41              2,163.38
              Logical reads:              4,351.54                 71.44
              Blockchanges:                854.87                 14.03
             Physical reads:                 37.68                  0.62
            Physical writes:                 46.68                  0.77
                 User calls:                953.03                 15.65
                     Parses:                346.70                  5.69
                Hard parses:                 84.93                  1.39
                      Sorts:                 36.30                  0.60
                     Logons:                  0.25                  0.00
                   Executes:                341.72                  5.61

               Transactions:                 60.91
 

Instance Efficiency Percentages (Target 100%)  

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.79    In-memory Sort %:  100.00
            Library Hit   %:   74.34        Soft Parse %:   75.50
         Execute to Parse %:   -1.46         Latch Hit %:   99.82

Parse CPU to Parse Elapsd %:   31.69     % Non-Parse CPU:   51.05
 
Top 5 Timed Events                                                  Avg   %Total
~~~~~~~~~~~~~~~~~~                                           wait   Call
Event                                 Waits          Time (s)       (ms)   Time      Wait Class
------------------------------           ------------      ------------        -----    ------       ----------
CPU time                                               14,520                 30.4           

log file sync                               417,305   8,732         21   18.3        Commit
direct path read temp                  223,033   2,975         13    6.2         User I/O
log file parallel write                     309,832  2,557          8     5.4          System I/O
SQL*Net break/reset to client       177,654    342           2    0.7         Applicati

          -------------------------------------------------------------      
 
Statistic Name                                       Time (s)       % of DB Time
------------------------------------------                   ------------------ -----------
parse time elapsed                                   22,200.0         46.5
DB CPU                                                   14,519.7         30.4
sql execute elapsed time                           13,836.0         29.0
hard parse elapsed time                                 990.9          2.1
PL/SQL execution elapsed time                        22.7           .0
connection management call elapsed time           6.9           .0
PL/SQL compilation elapsed time                        3.6           .0
hard parse (sharing criteria) elapsed time             3.0           .0
sequence load elapsed time                                0.5           .0
failed parse elapsed time                                     0.2           .0
repeated bind elapsed time                                  0.1           .0
DB time                                                     47,699.0          N/A
background elapsed time                               2,893.5          N/A
background cpu time                                         70.9          N/A
          -------------------------------------------------------------  
 
 
 Parse Calls  Executions     Parses    SQL Id    

------------ ------------ --------- -------------
      79,285            0      3.17 cugfcrccvdb7w

Module: dllhost.exe
SELECT VAR_VALUE,CATEGORY_ID FROM VARIABLE           65,099 0 2.60 3bh0n0n7f92t6

Module: dllhost.exe
SELECT OWNER_ID,ENTITY_ID, VAR_ID, VERSION, VAR_TYPE, VAR_VALUE, DISPLAY_NAME, D YNAMIC FROM VARIABLE         47,361 0 1.89 ag56wuczvmppx

Module: dllhost.exe
SELECT OWNER_ID, VAR_ID, VERSION, VAR_TYPE , VAR_VALUE, DISPLAY_NAME, DYNAMIC F ROM FINISHED_JOB_VARIABLE         28,582 0 1.14 6aswwzmuc7tcj Module: dllhost.exe

SELECT OWNER_ID, ENTITY_ID, VAR_ID, VERSION, VAR_TYPE, VAR_VALUE, DISPLAY_NAME,
DYNAMIC FROM VARIABLE       Thanks  

Fred.

--
http://www.freelists.org/webpage/oracle-l