ADDM Report for Task 'TASK_7901'
          --------------------------------
 
Analysis Period
---------------
AWR snapshot range from 2490 to 2493.
Time period starts at 24-NOV-10 09.00.20 AM
Time period ends at 24-NOV-10 12.00.21 PM
 
Analysis Target
---------------
Database 'CCPROD' with DB ID 1971250524.
Database version 11.1.0.7.0.
ADDM performed an analysis of all instances.
 
Activity During the Analysis Period
-----------------------------------
Total database time was 12416 seconds.
The average number of active sessions was 1.15.
 
Summary of Findings
-------------------
    Description                      Active Sessions      Recommendations
                                     Percent of Activity   
    -------------------------------  -------------------  ---------------
1   Top SQL by DB Time               .53 | 45.8           4
2   PL/SQL Execution                 .33 | 28.82          1
3   Buffer Busy                      .26 | 22.58          1
4   Top SQL by "Cluster" Wait        .24 | 21.26          5
5   Commits and Rollbacks            .2 | 17.04           2
6   CPU Usage                        .19 | 16.3           0
7   Hard Parse                       .11 | 9.83           0
8   Hard Parse Due to Literal Usage  .1 | 8.97            0
9   Index Block Split                .04 | 3.52           1
10  PL/SQL Compilation               .04 | 3.15           1
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
          Findings and Recommendations
          ----------------------------
 
Finding 1: Top SQL by DB Time
Impact is .53 active sessions, 45.8% of total activity.
-------------------------------------------------------
SQL statements consuming significant database time were found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .25 active sessions, 21.44% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "ffb720ahf723v".
      Related Object
         SQL statement with SQL_ID ffb720ahf723v.
         insert into AUDIT_FILTER_LOG (SESSION_LOG_ID, USER_ID, AUDIT_TIME, 
         URI, MODULE_NAME, HTTP_METHOD, OPERATION_NAME, QUERY_STRING, DATA, 
         AUDIT_LOG_ID) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , 
         :10 )
   Rationale
      SQL statement with SQL_ID "ffb720ahf723v" was executed 240798 times and 
      had an average elapsed time of 0.014 seconds.
 
   Recommendation 2: SQL Tuning
   Estimated benefit is .13 active sessions, 10.93% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "0wxwchgtc0uxh" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 0wxwchgtc0uxh.
         select AUDIT_FILTER_LOG_SEQ.nextval from dual
   Rationale
      SQL statement with SQL_ID "0wxwchgtc0uxh" was executed 240798 times and 
      had an average elapsed time of 0.0056 seconds.
 
   Recommendation 3: SQL Tuning
   Estimated benefit is .05 active sessions, 4.53% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "7643umz9rujym" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 7643umz9rujym.
         select NETWORK_ROLE_PERMISSION_SEQ.nextval from dual
   Rationale
      SQL statement with SQL_ID "7643umz9rujym" was executed 176664 times and 
      had an average elapsed time of 0.0031 seconds.
 
   Recommendation 4: SQL Tuning
   Estimated benefit is .03 active sessions, 2.23% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "59nf178vrwr1k".
      Related Object
         SQL statement with SQL_ID 59nf178vrwr1k.
         select this_.SESSION_LOG_ID as SESSION1_48_0_, this_.USER_ID as 
         USER2_48_0_, this_.LOGIN_TIME as LOGIN3_48_0_, this_.LOGOUT_TIME as 
         LOGOUT4_48_0_, this_.SESSION_TOKEN as SESSION5_48_0_, this_.IP_ADD as 
         IP6_48_0_, this_.AGENT as AGENT48_0_, this_.DESCRIPTION as 
         DESCRIPT8_48_0_, this_.IS_AUTO_LOGGED_OUT as IS9_48_0_ from 
         SESSION_LOG this_ where (this_.USER_ID=:1  and this_.SESSION_TOKEN=:2 
         )
   Action
      Investigate the SQL statement with SQL_ID "59nf178vrwr1k" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 59nf178vrwr1k.
         select this_.SESSION_LOG_ID as SESSION1_48_0_, this_.USER_ID as 
         USER2_48_0_, this_.LOGIN_TIME as LOGIN3_48_0_, this_.LOGOUT_TIME as 
         LOGOUT4_48_0_, this_.SESSION_TOKEN as SESSION5_48_0_, this_.IP_ADD as 
         IP6_48_0_, this_.AGENT as AGENT48_0_, this_.DESCRIPTION as 
         DESCRIPT8_48_0_, this_.IS_AUTO_LOGGED_OUT as IS9_48_0_ from 
         SESSION_LOG this_ where (this_.USER_ID=:1  and this_.SESSION_TOKEN=:2 
         )
   Rationale
      SQL statement with SQL_ID "59nf178vrwr1k" was executed 356304 times and 
      had an average elapsed time of 0.00073 seconds.
 
 
Finding 2: PL/SQL Execution
Impact is .33 active sessions, 28.82% of total activity.
--------------------------------------------------------
PL/SQL execution consumed significant database time.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  57.12           TASK_7901$2
    1       CCPROD1  42.88           TASK_7901$1
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .33 active sessions, 28.82% of total activity.
   -------------------------------------------------------------------
   Action
      Tune the entry point PL/SQL "CCUSER.ENCRYPTION.DECRYPT_CHAR" of type 
      "PACKAGE" and ID 83941. Refer to the PL/SQL documentation for addition 
      information.
   Rationale
      1192 seconds spent in executing PL/SQL "SYS.DBMS_CRYPTO_FFI.DECRYPT#1" 
      of type "PACKAGE" and ID 10580.
   Rationale
      734 seconds spent in executing PL/SQL ID 7223.
   Rationale
      596 seconds spent in executing PL/SQL ID 7223.
   Rationale
      479 seconds spent in executing PL/SQL "SYS.UTL_I18N.STRING_TO_RAW" of 
      type "PACKAGE" and ID 7223.
   Rationale
      330 seconds spent in executing PL/SQL "CCUSER.ENCRYPTION.DECRYPT_CHAR" 
      of type "PACKAGE" and ID 83941.
 
 
Finding 3: Buffer Busy
Impact is .26 active sessions, 22.58% of total activity.
--------------------------------------------------------
Read and write contention on database blocks was consuming significant 
database time.
 
   Recommendation 1: Schema Changes
   Estimated benefit is .24 active sessions, 20.47% of total activity.
   -------------------------------------------------------------------
   Action
      Consider hash partitioning the INDEX "CCUSER.AUDIT_FILTER_LOG_PK" with 
      object ID 83915 in a manner that will evenly distribute concurrent DML 
      across multiple partitions.
      Related Object
         Database object with ID 83915.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Inter-instance messaging was consuming significant database time.
      Impact is .22 active sessions, 19.27% of total activity.
         Wait class "Cluster" was consuming significant database time.
         Impact is .28 active sessions, 24.51% of total activity.
      Wait class "Concurrency" was consuming significant database time.
      Impact is .05 active sessions, 3.98% of total activity.
 
 
Finding 4: Top SQL by "Cluster" Wait
Impact is .24 active sessions, 21.26% of total activity.
--------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were 
found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .25 active sessions, 21.44% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "ffb720ahf723v".
      Related Object
         SQL statement with SQL_ID ffb720ahf723v.
         insert into AUDIT_FILTER_LOG (SESSION_LOG_ID, USER_ID, AUDIT_TIME, 
         URI, MODULE_NAME, HTTP_METHOD, OPERATION_NAME, QUERY_STRING, DATA, 
         AUDIT_LOG_ID) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , 
         :10 )
   Rationale
      SQL statement with SQL_ID "ffb720ahf723v" was executed 240798 times and 
      had an average elapsed time of 0.014 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.01 
      seconds.
 
   Recommendation 2: SQL Tuning
   Estimated benefit is .13 active sessions, 10.93% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "0wxwchgtc0uxh" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 0wxwchgtc0uxh.
         select AUDIT_FILTER_LOG_SEQ.nextval from dual
   Rationale
      SQL statement with SQL_ID "0wxwchgtc0uxh" was executed 240798 times and 
      had an average elapsed time of 0.0056 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.000051 
      seconds.
 
   Recommendation 3: SQL Tuning
   Estimated benefit is .05 active sessions, 4.53% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "7643umz9rujym" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 7643umz9rujym.
         select NETWORK_ROLE_PERMISSION_SEQ.nextval from dual
   Rationale
      SQL statement with SQL_ID "7643umz9rujym" was executed 176664 times and 
      had an average elapsed time of 0.0031 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.000027 
      seconds.
 
   Recommendation 4: SQL Tuning
   Estimated benefit is .04 active sessions, 3.54% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "8y3wz9swz7zjp".
      Related Object
         SQL statement with SQL_ID 8y3wz9swz7zjp.
         select user_id from user_profile where 
         (lower(encryption.decrypt_char(user_email,'0123456789ABCDEFFEDCBA9876
         543210')) = 'loadtest222_3' or 
         lower(encryption.decrypt_char(user_name,'0123456789ABCDEFFEDCBA987654
         3210')) = 'loadtest222_3') and password = 
         '7cbfa25a0ba328ee7c8058e62dcdbedf8544f293' and is_active = 1
   Rationale
      SQL statement with SQL_ID "8y3wz9swz7zjp" was executed 1785 times and 
      had an average elapsed time of 0.24 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.00083 
      seconds.
 
   Recommendation 5: SQL Tuning
   Estimated benefit is .03 active sessions, 2.23% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "59nf178vrwr1k".
      Related Object
         SQL statement with SQL_ID 59nf178vrwr1k.
         select this_.SESSION_LOG_ID as SESSION1_48_0_, this_.USER_ID as 
         USER2_48_0_, this_.LOGIN_TIME as LOGIN3_48_0_, this_.LOGOUT_TIME as 
         LOGOUT4_48_0_, this_.SESSION_TOKEN as SESSION5_48_0_, this_.IP_ADD as 
         IP6_48_0_, this_.AGENT as AGENT48_0_, this_.DESCRIPTION as 
         DESCRIPT8_48_0_, this_.IS_AUTO_LOGGED_OUT as IS9_48_0_ from 
         SESSION_LOG this_ where (this_.USER_ID=:1  and this_.SESSION_TOKEN=:2 
         )
   Action
      Investigate the SQL statement with SQL_ID "59nf178vrwr1k" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 59nf178vrwr1k.
         select this_.SESSION_LOG_ID as SESSION1_48_0_, this_.USER_ID as 
         USER2_48_0_, this_.LOGIN_TIME as LOGIN3_48_0_, this_.LOGOUT_TIME as 
         LOGOUT4_48_0_, this_.SESSION_TOKEN as SESSION5_48_0_, this_.IP_ADD as 
         IP6_48_0_, this_.AGENT as AGENT48_0_, this_.DESCRIPTION as 
         DESCRIPT8_48_0_, this_.IS_AUTO_LOGGED_OUT as IS9_48_0_ from 
         SESSION_LOG this_ where (this_.USER_ID=:1  and this_.SESSION_TOKEN=:2 
         )
   Rationale
      SQL statement with SQL_ID "59nf178vrwr1k" was executed 356304 times and 
      had an average elapsed time of 0.00073 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.00012 
      seconds.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is .28 active sessions, 24.51% of total activity.
 
 
Finding 5: Commits and Rollbacks
Impact is .2 active sessions, 17.04% of total activity.
-------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations 
were consuming significant database time.
Waits on event "log file sync" were the cause of significant database wait on 
"gc buffer busy release" when releasing a data block.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  65.74           TASK_7901$2
    1       CCPROD1  34.26           TASK_7901$1
 
   Recommendation 1: Application Analysis
   Estimated benefit is .2 active sessions, 17.04% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic for possible reduction in the number of 
      COMMIT operations by increasing the size of transactions.
   Rationale
      The application was performing 1676 transactions per minute with an 
      average redo size of 2081 bytes per transaction.
 
   Recommendation 2: Host Configuration
   Estimated benefit is .2 active sessions, 17.04% 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 2 milliseconds.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is .14 active sessions, 11.96% of total activity.
 
 
Finding 6: CPU Usage
Impact is .19 active sessions, 16.3% of total activity.
-------------------------------------------------------
Host CPU was a bottleneck for some instances. Wait times will be inflated by 
wait for CPU.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  59.2            TASK_7901$2
    1       CCPROD1  40.8            TASK_7901$1
 
Check the ADDM analysis of affected instances for recommendations.
 
 
Finding 7: Hard Parse
Impact is .11 active sessions, 9.83% of total activity.
-------------------------------------------------------
Hard parsing of SQL statements was consuming significant database time in some 
instances.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  58.78           TASK_7901$2
    1       CCPROD1  41.22           TASK_7901$1
 
Check the ADDM analysis of affected instances for recommendations.
 
 
Finding 8: Hard Parse Due to Literal Usage
Impact is .1 active sessions, 8.97% 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 in some 
instances.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  56.87           TASK_7901$2
    1       CCPROD1  43.13           TASK_7901$1
 
Check the ADDM analysis of affected instances for recommendations.
 
 
Finding 9: Index Block Split
Impact is .04 active sessions, 3.52% of total activity.
-------------------------------------------------------
Contention on index block splits was consuming significant database time.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  53.49           TASK_7901$2
    1       CCPROD1  46.51           TASK_7901$1
 
   Recommendation 1: Schema Changes
   Estimated benefit is .02 active sessions, 1.92% of total activity.
   ------------------------------------------------------------------
   Action
      Consider hash partitioning the INDEX "CCUSER.AUDIT_FILTER_LOG_PK" with 
      object ID 83915 in a manner that will evenly distribute concurrent DML 
      across multiple partitions.
      Related Object
         Database object with ID 83915.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Concurrency" was consuming significant database time.
      Impact is .05 active sessions, 3.98% of total activity.
 
 
Finding 10: PL/SQL Compilation
Impact is .04 active sessions, 3.15% of total activity.
-------------------------------------------------------
PL/SQL compilation consumed significant database time.
 
Instances that were significantly affected by this finding:
    Number  Name     Percent Impact  ADDM Task Name
    ------  -------  --------------  --------------
    2       CCPROD2  57.41           TASK_7901$2
    1       CCPROD1  42.59           TASK_7901$1
 
   Recommendation 1: Application Analysis
   Estimated benefit is .04 active sessions, 3.15% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the appropriateness of PL/SQL compilation. PL/SQL 
      compilation can be caused by DDL on dependent objects.
 
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
          Additional Information
          ----------------------
 
Instances that were analyzed:
-----------------------------
    Number  Name     Host Name                      Active Sessions  Percent of Activity
    ------  -------  -----------------------------  ---------------  -------------------
    2       CCPROD2  prod-rac02.cc.cl.datapipe.net  .7               60.9
    1       CCPROD1  prod-rac01.cc.cl.datapipe.net  .45              39.1
 
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.
The network latency of the cluster interconnect was within acceptable limits 
of 1 milliseconds.
Session connect and disconnect calls were not consuming significant database 
time.