ADDM Report for Task 'TASK_5638'
--------------------------------
Analysis Period
---------------
AWR snapshot range from 1784 to 1799.
Time period starts at 26-OCT-10 12.00.05 AM
Time period ends at 26-OCT-10 03.00.34 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 10261 seconds.
The average number of active sessions was .19.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
------------------------------ ------------------- ---------------
1 Top SQL by DB Time .11 | 57.19 5
2 Commits and Rollbacks .06 | 31.07 2
3 Buffer Busy .05 | 28.81 0
4 Top SQL by "Cluster" Wait .05 | 25.03 5
5 Interconnect Buffer Busy .01 | 5.99 1
6 PL/SQL Execution .01 | 5.45 2
7 CPU Usage .01 | 5.13 0
8 Index Block Split .01 | 4.93 1
9 Top Segments by I/O .01 | 3.32 1
10 I/O Throughput .01 | 2.82 3
11 Session Connect and Disconnect .01 | 2.78 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL by DB Time
Impact is .11 active sessions, 57.19% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .07 active sessions, 38.76% 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 )
Action
Investigate the SQL statement with SQL_ID "ffb720ahf723v" for possible
performance improvements.
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 146231 times and
had an average elapsed time of 0.027 seconds.
Rationale
Waiting for event "gc buffer busy acquire" in wait class "Cluster"
accounted for 27% of the database time spent in processing the SQL
statement with SQL_ID "ffb720ahf723v".
Rationale
Waiting for event "gc buffer busy release" in wait class "Cluster"
accounted for 19% of the database time spent in processing the SQL
statement with SQL_ID "ffb720ahf723v".
Rationale
Waiting for event "gc current block busy" in wait class "Cluster"
accounted for 16% of the database time spent in processing the SQL
statement with SQL_ID "ffb720ahf723v".
Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, 7.12% 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 146693 times and
had an average elapsed time of 0.0049 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .01 active sessions, 5.6% of total activity.
-----------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "7frqszzdu8sgg" for possible
performance improvements.
Related Object
SQL statement with SQL_ID 7frqszzdu8sgg.
select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N')
,DECODE(UPPER(i.STATUS),'OPEN','O','N') into :b0,:b1 from v$database
d ,gv$instance i where i.INSTANCE_NAME=:b2
Rationale
SQL statement with SQL_ID "7frqszzdu8sgg" was executed 10734 times and
had an average elapsed time of 0.053 seconds.
Rationale
At least one execution of the statement ran in parallel.
Rationale
Waiting for event "control file sequential read" in wait class "System
I/O" accounted for 55% of the database time spent in processing the SQL
statement with SQL_ID "7frqszzdu8sgg".
Recommendation 4: SQL Tuning
Estimated benefit is .01 active sessions, 3.06% 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 125969 times and
had an average elapsed time of 0.0024 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, 2.58% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "d15cdr0zt3vtp".
Related Object
SQL statement with SQL_ID d15cdr0zt3vtp.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD
HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session WHERE returncode != 0 AND
TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >=
TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD
HH24:MI:SS')
Rationale
SQL statement with SQL_ID "d15cdr0zt3vtp" was executed 28 times and had
an average elapsed time of 9.7 seconds.
Finding 2: Commits and Rollbacks
Impact is .06 active sessions, 31.07% 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 72.22 TASK_5638$2
1 CCPROD1 27.78 TASK_5638$1
Recommendation 1: Application Analysis
Estimated benefit is .06 active sessions, 31.07% 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 236 transactions per minute with an
average redo size of 2693 bytes per transaction.
Recommendation 2: Host Configuration
Estimated benefit is .06 active sessions, 31.07% 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 2 K and the
average time per write was 6 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .04 active sessions, 23.45% of total activity.
Finding 3: Buffer Busy
Impact is .05 active sessions, 28.81% of total activity.
--------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.
No recommendations are available.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time.
Impact is .04 active sessions, 22.09% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .06 active sessions, 29.82% of total activity.
Wait class "Concurrency" was consuming significant database time.
Impact is .01 active sessions, 6.97% of total activity.
Finding 4: Top SQL by "Cluster" Wait
Impact is .05 active sessions, 25.03% of total activity.
--------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were
found.
Recommendation 1: SQL Tuning
Estimated benefit is .07 active sessions, 38.76% 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 )
Action
Investigate the SQL statement with SQL_ID "ffb720ahf723v" for possible
performance improvements.
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 146231 times and
had an average elapsed time of 0.027 seconds.
Rationale
Waiting for event "gc buffer busy acquire" in wait class "Cluster"
accounted for 27% of the database time spent in processing the SQL
statement with SQL_ID "ffb720ahf723v".
Rationale
Waiting for event "gc buffer busy release" in wait class "Cluster"
accounted for 19% of the database time spent in processing the SQL
statement with SQL_ID "ffb720ahf723v".
Rationale
Waiting for event "gc current block busy" in wait class "Cluster"
accounted for 16% of the database time spent in processing the SQL
statement with SQL_ID "ffb720ahf723v".
Rationale
Average time spent in Cluster wait events per execution was 0.017
seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, 7.12% 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 146693 times and
had an average elapsed time of 0.0049 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.00013
seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .01 active sessions, 3.06% 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 125969 times and
had an average elapsed time of 0.0024 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.00016
seconds.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 2.58% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "d15cdr0zt3vtp".
Related Object
SQL statement with SQL_ID d15cdr0zt3vtp.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD
HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session WHERE returncode != 0 AND
TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >=
TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD
HH24:MI:SS')
Rationale
SQL statement with SQL_ID "d15cdr0zt3vtp" was executed 28 times and had
an average elapsed time of 9.7 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.000074
seconds.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, 2.2% of total activity.
---------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "fzkvszqchfpc1".
Related Object
SQL statement with SQL_ID fzkvszqchfpc1.
Select PATIENT_INSURANCE_ID,
PATIENT_ID,encryption.decrypt_char(PATIENT_NAME,'0123456789ABCDEFFEDC
BA9876543210') as PATIENT_NAME,encryption.decrypt_char(PLAN_ID,'01234
56789ABCDEFFEDCBA9876543210') as
PLAN_ID,encryption.decrypt_char(PLAN_NAME,'0123456789ABCDEFFEDCBA9876
543210') as PLAN_NAME,encryption.decrypt_char(INSURANCE_NAME,'0123456
789ABCDEFFEDCBA9876543210') as
INSURANCE_NAME,encryption.decrypt_char(GROUP_NUMBER,'0123456789ABCDEF
FEDCBA9876543210') as
GROUP_NUMBER,encryption.decrypt_char(NOTES,'0123456789ABCDEFFEDCBA987
6543210') as NOTES,encryption.decrypt_char(CONTACT_NUMBER,'0123456789
ABCDEFFEDCBA9876543210') as CONTACT_NUMBER,
encryption.decrypt_char(PRIMARY_INSURANCE_HOLDER,'0123456789ABCDEFFED
CBA9876543210') as PRIMARY_INSURANCE_HOLDER,encryption.decrypt_char(P
ENSION_NUMBER,'0123456789ABCDEFFEDCBA9876543210') as
PENSION_NUMBER,encryption.decrypt_char(DEDUCTIBLE,'0123456789ABCDEFFE
DCBA9876543210') as DEDUCTIBLE,encryption.decrypt_char(MEDICAL_NUMBER
,'0123456789ABCDEFFEDCBA9876543210') as
MEDICAL_NUMBER,encryption.decrypt_char(EMPLOYER,'0123456789ABCDEFFEDC
BA9876543210') as EMPLOYER,encryption.decrypt_char(STATUS,'0123456789
ABCDEFFEDCBA9876543210') as
STATUS,encryption.decrypt_char(POLICY_NUMBER,'0123456789ABCDEFFEDCBA9
876543210') as POLICY_NUMBER,INSURANCE_TYPE_LKP_ID,INSURANCE_TYPE as
INSURANCETYPE from vw_insurance_lkp where patient_id = 1857 order by
INSURANCE_TYPE asc
Rationale
SQL statement with SQL_ID "fzkvszqchfpc1" was executed 1814 times and
had an average elapsed time of 0.12 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.00036
seconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Cluster" was consuming significant database time.
Impact is .06 active sessions, 29.82% of total activity.
Finding 5: Interconnect Buffer Busy
Impact is .01 active sessions, 5.99% of total activity.
-------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time in the cluster.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------- -------------- --------------
1 CCPROD1 74.32 TASK_5638$1
Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 5.99% of total activity.
------------------------------------------------------------------
Action
Verify that the set of services used by the application to connect to
the database are optimally distributed if response time is critical.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time.
Impact is .04 active sessions, 22.09% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .06 active sessions, 29.82% of total activity.
Finding 6: PL/SQL Execution
Impact is .01 active sessions, 5.45% 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
------ ------- -------------- --------------
1 CCPROD1 52.77 TASK_5638$1
2 CCPROD2 45.6 TASK_5638$2
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 4.88% of total activity.
------------------------------------------------------------------
Action
Tune the entry point PL/SQL "CCUSER.ENCRYPTION.DECRYPT_CHAR" of type
"PACKAGE" and ID 79351. Refer to the PL/SQL documentation for addition
information.
Rationale
163 seconds spent in executing PL/SQL "SYS.DBMS_CRYPTO_FFI.DECRYPT#1" of
type "PACKAGE" and ID 10580.
Rationale
134 seconds spent in executing PL/SQL ID 7223.
Rationale
77 seconds spent in executing PL/SQL "CCUSER.ENCRYPTION.DECRYPT_CHAR" of
type "PACKAGE" and ID 79351.
Rationale
48 seconds spent in executing PL/SQL ID 7223.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, .56% of total activity.
---------------------------------------------------------------
Action
Tune the entry point PL/SQL
"SYSMAN.EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS" of type "PACKAGE" and
ID 66507. Refer to the PL/SQL documentation for addition information.
Finding 7: CPU Usage
Impact is .01 active sessions, 5.13% 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
------ ------- -------------- --------------
1 CCPROD1 59.61 TASK_5638$1
2 CCPROD2 40.39 TASK_5638$2
Check the ADDM analysis of affected instances for recommendations.
Finding 8: Index Block Split
Impact is .01 active sessions, 4.93% 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 52.85 TASK_5638$2
1 CCPROD1 47.15 TASK_5638$1
Recommendation 1: Schema Changes
Estimated benefit is .01 active sessions, 3.8% of total activity.
-----------------------------------------------------------------
Action
Consider hash partitioning the INDEX "CCUSER.AUDIT_FILTER_LOG_PK" with
object ID 79328 in a manner that will evenly distribute concurrent DML
across multiple partitions.
Related Object
Database object with ID 79328.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Concurrency" was consuming significant database time.
Impact is .01 active sessions, 6.97% of total activity.
Finding 9: Top Segments by I/O
Impact is .01 active sessions, 3.32% of total activity.
-------------------------------------------------------
Individual database segments responsible for significant user I/O wait were
found.
Recommendation 1: Segment Tuning
Estimated benefit is .01 active sessions, 3.32% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE "SYS.AUD$" with object ID 376.
Related Object
Database object with ID 376.
Action
Investigate application logic involving I/O on TABLE "SYS.AUD$" with
object ID 376.
Related Object
Database object with ID 376.
Rationale
The I/O usage statistics for the object are: 31 full object scans,
422679 physical reads, 504 physical writes and 422679 direct reads.
Rationale
The SQL statement with SQL_ID "d15cdr0zt3vtp" spent significant time
waiting for User I/O on the hot object.
Related Object
SQL statement with SQL_ID d15cdr0zt3vtp.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD
HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session WHERE returncode != 0 AND
TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >=
TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD
HH24:MI:SS')
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 6.44% of total activity.
Finding 10: I/O Throughput
Impact is .01 active sessions, 2.82% of total activity.
-------------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------- -------------- --------------
1 CCPROD1 47.3 TASK_5638$1
Recommendation 1: Host Configuration
Estimated benefit is .01 active sessions, 2.82% of total activity.
------------------------------------------------------------------
Action
Consider increasing the throughput of the I/O subsystem. Oracle's
recommended solution is to stripe all data file using the SAME
methodology. You might also need to increase the number of disks for
better performance. Alternatively, consider using Oracle's Automatic
Storage Management solution.
Rationale
During the analysis period, the average data files' I/O throughput was
64 K per second for reads and 22 K per second for writes. The average
response time for single block reads was 51 milliseconds.
Recommendation 2: Host Configuration
Estimated benefit is 0 active sessions, .96% of total activity.
---------------------------------------------------------------
Action
The performance of file +CCPROD/ccprod/sysaux01.dbf was significantly
worse than other files. If striping all files using the SAME methodology
is not possible, consider striping this file over multiple disks.
Related Object
Database file
"+CCPROD/ccprod/sysaux01.dbf"
Rationale
The average response time for single block reads for this file was 66
milliseconds.
Recommendation 3: Host Configuration
Estimated benefit is 0 active sessions, .96% of total activity.
---------------------------------------------------------------
Action
The performance of file +CCPROD/ccprod/ccdata_01.dbf was significantly
worse than other files. If striping all files using the SAME methodology
is not possible, consider striping this file over multiple disks.
Related Object
Database file
"+CCPROD/ccprod/ccdata_01.dbf"
Rationale
The average response time for single block reads for this file was 149
milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 6.44% of total activity.
Finding 11: Session Connect and Disconnect
Impact is .01 active sessions, 2.78% of total activity.
-------------------------------------------------------
Session connect and disconnect calls were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 2.78% 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Instances that were analyzed:
-----------------------------
Number Name Host Name Active Sessions Percent of Activity
------ ------- ----------------------------- --------------- -------------------
2 CCPROD2 prod-rac02.cc.cl.datapipe.net .11 57.08
1 CCPROD1 prod-rac01.cc.cl.datapipe.net .08 42.92
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.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
The database's maintenance windows were active during 13% of the analysis
period.