          ADDM Report for Task 'TASK_462'
          -------------------------------

Analysis Period
---------------
AWR snapshot range from 347 to 348.
Time period starts at 04-AUG-08 11.43.45 AM
Time period ends at 04-AUG-08 11.47.28 AM

Analysis Target
---------------
Database '15V' with DB ID 1635817246.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance 15v, numbered 1 and hosted at stade50.

Activity During the Analysis Period
-----------------------------------
Total database time was 670 seconds.
The average number of active sessions was 3.01.

Summary of Findings
-------------------
   Description                      Active Sessions      Recommendations
                                    Percent of Activity
   -------------------------------  -------------------  ---------------
1  CPU Usage                        3.01 | 100           2
2  Session Connect and Disconnect   1.38 | 45.93         1
3  Soft Parse                       .67 | 22.2           2
4  Hard Parse Due to Literal Usage  .43 | 14.33          1
5  Top SQL by DB Time               .37 | 12.47          3
6  Commits and Rollbacks            .29 | 9.55           1


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations
          ----------------------------

Finding 1: CPU Usage
Impact is 3.01 active sessions, 100% of total activity.
-------------------------------------------------------
Host CPU was a bottleneck and the instance was consuming 72% of the host CPU.
All wait times will be inflated by wait for CPU.
Host CPU consumption was 97%.  CPU runqueue statistics are not available from
the host's OS. This disables ADDM's ability to estimate the impact of this
finding.  The instance spent significant time on CPU. However, there were no
predominant SQL statements responsible for the CPU load.

   Recommendation 1: Host Configuration
   Estimated benefit is 3.01 active sessions, 100% of total activity.
   ------------------------------------------------------------------
   Action
      Consider adding more CPUs to the host or adding instances serving the
      database on other hosts.
   Action
      Also consider using Oracle Database Resource Manager to prioritize the
      workload from various consumer groups.

   Recommendation 2: Application Analysis
   Estimated benefit is .21 active sessions, 7.11% of total activity.
   ------------------------------------------------------------------
   Action
      Parsing SQL statements were consuming significant CPU. Please refer to
      other findings in this task about parsing for further details.


Finding 2: Session Connect and Disconnect
Impact is 1.38 active sessions, 45.93% of total activity.
---------------------------------------------------------
Session connect and disconnect calls were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is 1.38 active sessions, 45.93% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate application logic for possible reduction of connect and
      disconnect calls. For example, you might use a connection pool scheme in
      the middle tier.


Finding 3: Soft Parse
Impact is .67 active sessions, 22.2% of total activity.
-------------------------------------------------------
Soft parsing of SQL statements was consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is .67 active sessions, 22.2% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic to keep open the frequently used cursors.
      Note that cursors are closed by both cursor close calls and session
      disconnects.

   Recommendation 2: Database Configuration
   Estimated benefit is .67 active sessions, 22.2% of total activity.
   ------------------------------------------------------------------
   Action
      Consider increasing the maximum number of open cursors a session can
      have by increasing the value of parameter "open_cursors".
   Action
      Consider increasing the session cursor cache size by increasing the
      value of parameter "session_cached_cursors".
   Rationale
      The value of parameter "open_cursors" was "52" during the analysis
      period.
   Rationale
      The value of parameter "session_cached_cursors" was "50" during the
      analysis period.

   Symptoms That Led to the Finding:
   ---------------------------------
      Contention for latches related to the shared pool was consuming
      significant database time.
      Impact is 1.39 active sessions, 46.23% of total activity.
         Wait class "Concurrency" was consuming significant database time.
         Impact is 1.42 active sessions, 47.18% of total activity.


Finding 4: Hard Parse Due to Literal Usage
Impact is .43 active sessions, 14.33% of total activity.
--------------------------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is .43 active sessions, 14.33% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate application logic for possible use of bind variables instead
      of literals.
   Action
      Alternatively, you may set the parameter "cursor_sharing" to "force".

   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is .43 active sessions, 14.33% of total activity.
         Contention for latches related to the shared pool was consuming
         significant database time.
         Impact is 1.39 active sessions, 46.23% of total activity.
            Wait class "Concurrency" was consuming significant database time.
            Impact is 1.42 active sessions, 47.18% of total activity.


Finding 5: Top SQL by DB Time
Impact is .37 active sessions, 12.47% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.

   Recommendation 1: SQL Tuning
   Estimated benefit is .25 active sessions, 8.22% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "0h6b2sajwb74n" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 0h6b2sajwb74n and PLAN_HASH 3899174842.
         select privilege#,level from sysauth$ connect by grantee#=prior
         privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
   Rationale
      SQL statement with SQL_ID "0h6b2sajwb74n" was executed 2400 times and
      had an average elapsed time of 0.022 seconds.

   Recommendation 2: SQL Tuning
   Estimated benefit is .06 active sessions, 2.13% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "209fr01svbb5s" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 209fr01svbb5s and PLAN_HASH 1388258015.
         update sys.aud$ set action#=:2, returncode=:3,
         logoff$time=cast(SYS_EXTRACT_UTC(systimestamp) as date),
         logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7,
         sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
   Rationale
      SQL statement with SQL_ID "209fr01svbb5s" was executed 2400 times and
      had an average elapsed time of 0.0059 seconds.

   Recommendation 3: SQL Tuning
   Estimated benefit is .06 active sessions, 2.12% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "4vs91dcv7u1p6".
      Related Object
         SQL statement with SQL_ID 4vs91dcv7u1p6.
         insert into sys.aud$( sessionid,entryid,statement,ntimestamp#,
         userid,userhost,terminal,action#,returncode,
         obj$creator,obj$name,auth$privileges,auth$grantee,
         new$owner,new$name,ses$actions,ses$tid,logoff$pread,
         logoff$lwrite,logoff$dead,comment$text,spare1,spare2,
         priv$used,clientid,sessioncpu,proxy$sid,user$guid,
         instance#,process#,xid,scn,auditid,
         sqlbind,sqltext,obj$edition,dbid)
         values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),     :4,:5,:6,:7,:8,
         :9,:10,:11,:12,     :13,:14,:15,:16,:17,     :18,:19,:20,:21,:22,
         :23,:24,:25,:26,:27,     :28,:29,:30,:31,:32,     :33,:34,:35,:36)
   Action
      Investigate the SQL statement with SQL_ID "4vs91dcv7u1p6" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 4vs91dcv7u1p6.
         insert into sys.aud$( sessionid,entryid,statement,ntimestamp#,
         userid,userhost,terminal,action#,returncode,
         obj$creator,obj$name,auth$privileges,auth$grantee,
         new$owner,new$name,ses$actions,ses$tid,logoff$pread,
         logoff$lwrite,logoff$dead,comment$text,spare1,spare2,
         priv$used,clientid,sessioncpu,proxy$sid,user$guid,
         instance#,process#,xid,scn,auditid,
         sqlbind,sqltext,obj$edition,dbid)
         values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),     :4,:5,:6,:7,:8,
         :9,:10,:11,:12,     :13,:14,:15,:16,:17,     :18,:19,:20,:21,:22,
         :23,:24,:25,:26,:27,     :28,:29,:30,:31,:32,     :33,:34,:35,:36)
   Rationale
      SQL statement with SQL_ID "4vs91dcv7u1p6" was executed 2400 times and
      had an average elapsed time of 0.0059 seconds.


Finding 6: Commits and Rollbacks
Impact is .29 active sessions, 9.55% of total activity.
-------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

   Recommendation 1: Host Configuration
   Estimated benefit is .29 active sessions, 9.55% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 1 K and the
      average time per write was 5 milliseconds.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is .29 active sessions, 9.55% of total activity.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.

