ADDM Report for Task 'TASK_11735'
---------------------------------
Analysis Period
---------------
AWR snapshot range from 3725 to 3766.
Time period starts at 01-FEB-11 04.00.59 PM
Time period ends at 03-FEB-11 09.00.19 AM
Analysis Target
---------------
Database 'INTOUCHD' with DB ID 3592226595.
Database version 11.1.0.7.0.
ADDM performed an analysis of all instances.
Activity During the Analysis Period
-----------------------------------
Total database time was 21476 seconds.
The average number of active sessions was .15.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------------------- ------------------- ---------------
1 Top SQL by DB Time .04 | 27.38 5
2 I/O Throughput .02 | 12.85 3
3 Interconnect Multiblock Requests .02 | 12.27 5
4 Interconnect Congestion .02 | 11.13 1
5 Top SQL by "Cluster" Wait .01 | 8.33 5
6 Session Connect and Disconnect .01 | 7.21 1
7 Top SQL By I/O .01 | 6.51 1
8 Commits and Rollbacks .01 | 5.79 1
9 PL/SQL Execution .01 | 4.32 5
10 Unusual "Other" Wait Event .01 | 3.44 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL by DB Time
Impact is .04 active sessions, 27.38% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 8.37% of total activity.
------------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for possible
performance improvements.
Related Object
SQL statement with SQL_ID b6usrg82hwsa3.
call dbms_stats.gather_database_stats_job_proc ( )
Rationale
SQL statement with SQL_ID "b6usrg82hwsa3" was executed 2 times and had
an average elapsed time of 899 seconds.
Rationale
Waiting for event "db file sequential read" in wait class "User I/O"
accounted for 4% of the database time spent in processing the SQL
statement with SQL_ID "b6usrg82hwsa3".
Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, 7.67% of total activity.
------------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "d15cdr0zt3vtp" for possible
performance improvements.
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 58 times and had
an average elapsed time of 28 seconds.
Rationale
Waiting for event "ksfd: async disk IO" in wait class "System I/O"
accounted for 75% of the database time spent in processing the SQL
statement with SQL_ID "d15cdr0zt3vtp".
Rationale
Waiting for event "direct path read" in wait class "User I/O" accounted
for 18% of the database time spent in processing the SQL statement with
SQL_ID "d15cdr0zt3vtp".
Recommendation 3: SQL Tuning
Estimated benefit is .01 active sessions, 5.12% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "f8fjqp8vb3f1j".
Related Object
SQL statement with SQL_ID f8fjqp8vb3f1j.
select /*+ all_rows */ count(1) from "MCONSER_USER"."CGM_LOG" where
"GLOBAL_USERS_ID" = :1
Rationale
SQL statement with SQL_ID "f8fjqp8vb3f1j" was executed 58 times and had
an average elapsed time of 18 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .01 active sessions, 3.85% 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 29369 times and
had an average elapsed time of 0.028 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 72% of the database time spent in processing the SQL
statement with SQL_ID "7frqszzdu8sgg".
Rationale
Waiting for event "reliable message" in wait class "Other" accounted for
8% of the database time spent in processing the SQL statement with
SQL_ID "7frqszzdu8sgg".
Recommendation 5: SQL Tuning
Estimated benefit is .01 active sessions, 3.65% of total activity.
------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6gvch1xu9ca3g.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
Rationale
SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 2456 times and
had an average elapsed time of 0.34 seconds.
Finding 2: I/O Throughput
Impact is .02 active sessions, 12.85% 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 INTOUCHD1 56.24 TASK_11735$1
2 INTOUCHD2 40.98 TASK_11735$2
Recommendation 1: Host Configuration
Estimated benefit is .02 active sessions, 12.85% 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
461 K per second for reads and 42 K per second for writes. The average
response time for single block reads was 18 milliseconds.
Recommendation 2: Host Configuration
Estimated benefit is 0 active sessions, 2.1% of total activity.
---------------------------------------------------------------
Action
The performance of file +DATA/intouchd/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
"+DATA/intouchd/sysaux01.dbf"
Rationale
The average response time for single block reads for this file was 44
milliseconds.
Recommendation 3: Host Configuration
Estimated benefit is 0 active sessions, 1.24% of total activity.
----------------------------------------------------------------
Action
The performance of file +DATA/intouchd/system01.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
"+DATA/intouchd/system01.dbf"
Rationale
The average response time for single block reads for this file was 47
milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .03 active sessions, 18.55% of total activity.
Finding 3: Interconnect Multiblock Requests
Impact is .02 active sessions, 12.27% of total activity.
--------------------------------------------------------
Cluster multi-block requests were consuming significant database time.
Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 0% of total activity.
-------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1fpcwx5qrtjur".
Look for an alternative plan that does not use object scans.
Related Object
SQL statement with SQL_ID 1fpcwx5qrtjur.
delete from mconser_user.USERS where
global_users_id='ABCD1234ABCD1234ABCD1234ABCD1237'
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 0% of total activity.
-------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "c4g0t88q8xkyh".
Look for an alternative plan that does not use object scans.
Related Object
SQL statement with SQL_ID c4g0t88q8xkyh.
delete from SDEVEL_USER.USERS where
GLOBAL_USERS_ID='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 0% of total activity.
-------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "6zfsn47199d7u".
Look for an alternative plan that does not use object scans.
Related Object
SQL statement with SQL_ID 6zfsn47199d7u.
select this_.CGM_LOG_ID as CGM1_10_0_, this_.CREATED_BY as
CREATED2_10_0_, this_.CREATED_DT as CREATED3_10_0_,
this_.LAST_UPDATED_BY as LAST4_10_0_, this_.LAST_UPDATED_DT as
LAST5_10_0_, this_.PUBLISHED_DT as PUBLISHED6_10_0_, this_.ACTIVE as
ACTIVE10_0_, this_.CLOCK_OFF as CLOCK8_10_0_, this_.DATE_OFF as
DATE9_10_0_, this_.DEVICE_SERIAL_NUMBER as DEVICE10_10_0_,
this_.DEVICE_TYPES_LOOKUP_ID as DEVICE22_10_0_, this_.EDITED as
EDITED10_0_, this_.ENTRY_TYPES_ID as ENTRY23_10_0_, this_.IMPORTED as
IMPORTED10_0_, this_.MANUAL as MANUAL10_0_, this_.OMIT as OMIT10_0_,
this_.READING_TIME as READING15_10_0_, this_.TRANSMISSION_SETS_ID as
TRANSMI24_10_0_, this_.GLOBAL_USERS_ID as GLOBAL25_10_0_,
this_.CGM_SENSOR_ID as CGM16_10_0_, this_.CGM_VALUE as CGM17_10_0_,
this_.IS_END_SESSION as IS18_10_0_, this_.IS_LOST_OF_TIME as
IS19_10_0_, this_.IS_START_SESSION as IS20_10_0_,
this_.IS_TIME_CHANGED as IS21_10_0_ from mconser_user.CGM_LOG this_
where ((((this_.GLOBAL_USERS_ID=:1 and this_.ACTIVE=:2) and
(this_.OMIT is null or this_.OMIT=:3)) and this_.READING_TIME>=:4)
and this_.READING_TIME<:5)
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 0% of total activity.
-------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "77362xaxm06y5".
Look for an alternative plan that does not use object scans.
Related Object
SQL statement with SQL_ID 77362xaxm06y5.
delete from /*+ NO_PARALLEL (mconser_user.USERS) */
mconser_user.USERS where
global_users_id='ABCD1234ABCD1234ABCD1234ABCD1237'
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, 0% of total activity.
-------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "39yk3fsamzktj".
Look for an alternative plan that does not use object scans.
Related Object
SQL statement with SQL_ID 39yk3fsamzktj.
delete
from mconser_user.USERS where
global_users_id='ABCD1234ABCD1234ABCD1234ABCD1237'
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time.
Impact is .02 active sessions, 14.06% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .02 active sessions, 14.08% of total activity.
Finding 4: Interconnect Congestion
Impact is .02 active sessions, 11.13% of total activity.
--------------------------------------------------------
Global Cache Service Processes (LMSn) were not processing requests fast
enough.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ --------- -------------- --------------
1 INTOUCHD1 65.44 TASK_11735$1
2 INTOUCHD2 34.56 TASK_11735$2
Recommendation 1: Database Configuration
Estimated benefit is .02 active sessions, 11.13% of total activity.
-------------------------------------------------------------------
Action
Increase throughput of the Global Cache Service (LMSn) processes.
Increase the number of Global Cache Service processes by increasing the
value of the parameter "gcs_server_processes". Alternatively, if the
host is CPU bound consider increasing the OS priority of the Global
Cache Service processes.
Rationale
The value of parameter "gcs_server_processes" was "2" during the
analysis period.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time.
Impact is .02 active sessions, 14.06% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .02 active sessions, 14.08% of total activity.
Finding 5: Top SQL by "Cluster" Wait
Impact is .01 active sessions, 8.33% of total activity.
-------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were
found.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 8.37% of total activity.
------------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for possible
performance improvements.
Related Object
SQL statement with SQL_ID b6usrg82hwsa3.
call dbms_stats.gather_database_stats_job_proc ( )
Rationale
SQL statement with SQL_ID "b6usrg82hwsa3" was executed 2 times and had
an average elapsed time of 899 seconds.
Rationale
Waiting for event "db file sequential read" in wait class "User I/O"
accounted for 4% of the database time spent in processing the SQL
statement with SQL_ID "b6usrg82hwsa3".
Rationale
Average time spent in Cluster wait events per execution was 43 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, 5.12% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "f8fjqp8vb3f1j".
Related Object
SQL statement with SQL_ID f8fjqp8vb3f1j.
select /*+ all_rows */ count(1) from "MCONSER_USER"."CGM_LOG" where
"GLOBAL_USERS_ID" = :1
Rationale
SQL statement with SQL_ID "f8fjqp8vb3f1j" was executed 58 times and had
an average elapsed time of 18 seconds.
Rationale
Average time spent in Cluster wait events per execution was 17 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 2.1% of total activity.
---------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "ffj9qtrhhyz1b".
Related Object
SQL statement with SQL_ID ffj9qtrhhyz1b.
select /*+ all_rows */ count(1) from "SDEVEL_USER"."CGM_LOG" where
"GLOBAL_USERS_ID" = :1
Rationale
SQL statement with SQL_ID "ffj9qtrhhyz1b" was executed 183 times and had
an average elapsed time of 2.4 seconds.
Rationale
Average time spent in Cluster wait events per execution was 2.2 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, .71% of total activity.
---------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6jbrg916bjmqc". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6jbrg916bjmqc.
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME
ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) :=
:job_name; job_subname VARCHAR2(30) := :job_subname; job_owner
VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE :=
:job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE :=
:job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE :=
:window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end;
BEGIN DECLARE
ename VARCHAR2(30);
BEGIN
ename := dbms_sqltune.execute_tuning_task(
'SYS_AUTO_SQL_TUNING_TASK');
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END
IF; END;
Rationale
SQL statement with SQL_ID "6jbrg916bjmqc" was executed 2 times and had
an average elapsed time of 268 seconds.
Rationale
Average time spent in Cluster wait events per execution was 76 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, .52% of total activity.
---------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6gvch1xu9ca3g.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
Rationale
SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 2456 times and
had an average elapsed time of 0.34 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.045
seconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Cluster" was consuming significant database time.
Impact is .02 active sessions, 14.08% of total activity.
Finding 6: Session Connect and Disconnect
Impact is .01 active sessions, 7.21% of total activity.
-------------------------------------------------------
Session connect and disconnect calls were consuming significant database time.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ --------- -------------- --------------
1 INTOUCHD1 58.22 TASK_11735$1
2 INTOUCHD2 41.78 TASK_11735$2
Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 7.21% 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 7: Top SQL By I/O
Impact is .01 active sessions, 6.51% of total activity.
-------------------------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 8.37% of total activity.
------------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for possible
performance improvements.
Related Object
SQL statement with SQL_ID b6usrg82hwsa3.
call dbms_stats.gather_database_stats_job_proc ( )
Rationale
SQL statement with SQL_ID "b6usrg82hwsa3" was executed 2 times and had
an average elapsed time of 899 seconds.
Rationale
Waiting for event "db file sequential read" in wait class "User I/O"
accounted for 4% of the database time spent in processing the SQL
statement with SQL_ID "b6usrg82hwsa3".
Rationale
Average time spent in User I/O wait events per execution was 699
seconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .03 active sessions, 18.55% of total activity.
Finding 8: Commits and Rollbacks
Impact is .01 active sessions, 5.79% of total activity.
-------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ --------- -------------- --------------
1 INTOUCHD1 60.45 TASK_11735$1
2 INTOUCHD2 39.55 TASK_11735$2
Recommendation 1: Host Configuration
Estimated benefit is .01 active sessions, 5.79% 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 12 K and the
average time per write was 0 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .01 active sessions, 5.78% of total activity.
Finding 9: PL/SQL Execution
Impact is .01 active sessions, 4.32% 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 INTOUCHD1 64.3 TASK_11735$1
Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 1.16% of total activity.
----------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC"
of type "PACKAGE" and ID 7262. Refer to the PL/SQL documentation for
addition information.
Rationale
238 seconds spent in executing PL/SQL
"SYS.DBMS_SPACE.OBJECT_SPACE_USAGE" of type "PACKAGE" and ID 7262.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, .93% of total activity.
---------------------------------------------------------------
Action
Tune the entry point PL/SQL
"SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC" of type "PACKAGE" and ID
7348. Refer to the PL/SQL documentation for addition information.
Rationale
57 seconds spent in executing PL/SQL ID 8007.
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, .8% of total activity.
--------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.DBMS_STATS.GATHER_SCHEMA_STATS#2" of
type "PACKAGE" and ID 7348. Refer to the PL/SQL documentation for
addition information.
Rationale
76 seconds spent in executing PL/SQL
"SYS.DBMS_STATS.GATHER_SCHEMA_STATS#1" of type "PACKAGE" and ID 7348.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, .76% of total activity.
---------------------------------------------------------------
Action
Tune the entry point PL/SQL "DVSYS.OLS_INIT_SESSION" of type "PROCEDURE"
and ID 72104. Refer to the PL/SQL documentation for addition
information.
Rationale
57 seconds spent in executing PL/SQL
"LBACSYS.LBAC_CACHE.INITIALIZE_CACHE" of type "PACKAGE" and ID 71538.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, .67% of total activity.
---------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.UTL_INADDR.GET_HOST_ADDRESS" of type
"PACKAGE" and ID 7211. Refer to the PL/SQL documentation for addition
information.
Rationale
143 seconds spent in executing PL/SQL ID 7211.
Finding 10: Unusual "Other" Wait Event
Impact is .01 active sessions, 3.44% of total activity.
-------------------------------------------------------
Wait event "reliable message" in wait class "Other" was consuming significant
database time.
Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 3.44% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "reliable message" waits. Refer to
Oracle's "Database Reference" for the description of this wait event.
Use given SQL for further investigation.
Rationale
The SQL statement with SQL_ID "7frqszzdu8sgg" was found waiting for
"reliable message" wait event.
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
Recommendation 2: Application Analysis
Estimated benefit is 0 active sessions, 3.39% of total activity.
----------------------------------------------------------------
Action
Investigate the cause for high "reliable message" waits in Service
"SYS$USERS".
Recommendation 3: Application Analysis
Estimated benefit is 0 active sessions, 2.79% of total activity.
----------------------------------------------------------------
Action
Investigate the cause for high "reliable message" waits in Module
"perl@stage-rac02.symcare.com (TNS V1-V3)".
Symptoms That Led to the Finding:
---------------------------------
Wait class "Other" was consuming significant database time.
Impact is .02 active sessions, 11.77% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Instances that were analyzed:
-----------------------------
Number Name Host Name Active Sessions Percent of Activity
------ --------- ----------------------- --------------- -------------------
1 INTOUCHD1 stage-rac01.symcare.com .07 51.43
2 INTOUCHD2 stage-rac02.symcare.com .07 48.57
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" 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 19% of the analysis
period.