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.