1 Reply Latest reply: Jul 21, 2013 7:48 PM by User177447 RSS

    Active session Spike on Oracle RAC 11G R2 on HP UX

    User177447

      Dear Experts,

       

      We need urgent help please, as we are facing very low performance in production database.

       

      We are having oracle 11G RAC on HP Unix environment. Following is the ADDM report. Kindly check and please help me to figure it out the issue and resolve it at earliest.

       

      ---------Instance 1---------------


                ADDM Report for Task 'TASK_36650'

                ---------------------------------

       

      Analysis Period

      ---------------

      AWR snapshot range from 11634 to 11636.

      Time period starts at 21-JUL-13 07.00.03 PM

      Time period ends at 21-JUL-13 09.00.49 PM

       

      Analysis Target

      ---------------

      Database 'MCMSDRAC' with DB ID 2894940361.

      Database version 11.2.0.1.0.

      ADDM performed an analysis of instance mcmsdrac1, numbered 1 and hosted at

      mcmsdbl1.

       

      Activity During the Analysis Period

      -----------------------------------

      Total database time was 38466 seconds.

      The average number of active sessions was 5.31.

       

      Summary of Findings

      -------------------

         Description           Active Sessions      Recommendations

                               Percent of Activity  

         --------------------  -------------------  ---------------

      1  CPU Usage             1.44 | 27.08         1

      2  Interconnect Latency  .07 | 1.33           1

       

       

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

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

       

       

                Findings and Recommendations

                ----------------------------

       

      Finding 1: CPU Usage

      Impact is 1.44 active sessions, 27.08% of total activity.

      ---------------------------------------------------------

      Host CPU was a bottleneck and the instance was consuming 99% of the host CPU.

      All wait times will be inflated by wait for CPU.

      Host CPU consumption was 99%.

       

         Recommendation 1: Host Configuration

         Estimated benefit is 1.44 active sessions, 27.08% of total activity.

         --------------------------------------------------------------------

         Action

            Consider adding more CPUs to the host or adding instances serving the

            database on other hosts.

         Action

            Session CPU consumption was throttled by the Oracle Resource Manager.

            Consider revising the resource plan that was active during the analysis

            period.

       

       

      Finding 2: Interconnect Latency

      Impact is .07 active sessions, 1.33% of total activity.

      -------------------------------------------------------

      Higher than expected latency of the cluster interconnect was responsible for

      significant database time on this instance.

      The instance was consuming 110 kilo bits per second of interconnect bandwidth.

      20% of this interconnect bandwidth was used for global cache messaging, 21%

      for parallel query messaging and 7% for database lock management.

      The average latency for 8K interconnect messages was 42153 microseconds.

      The instance is using the private interconnect device "lan2" with IP address

      172.16.200.71 and source "Oracle Cluster Repository".

      The device "lan2" was used for 100% of interconnect traffic and experienced 0

      send or receive errors during the analysis period.

       

         Recommendation 1: Host Configuration

         Estimated benefit is .07 active sessions, 1.33% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate cause of high network interconnect latency between database

            instances. Oracle's recommended solution is to use a high speed

            dedicated network.

         Action

            Check the configuration of the cluster interconnect. Check OS setup like

            adapter setting, firmware and driver release. Check that the OS's socket

            receive buffers are large enough to store an entire multiblock read. The

            value of parameter "db_file_multiblock_read_count" may be decreased as a

            workaround.

       

       

       

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

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

       

                Additional Information

                ----------------------

       

      Miscellaneous Information

      -------------------------

      Wait class "Application" was not consuming significant database time.

      Wait class "Cluster" was not consuming significant database time.

      Wait class "Commit" 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.

      Wait class "User I/O" was not consuming significant database time.

      Session connect and disconnect calls were not consuming significant database

      time.

      Hard parsing of SQL statements was not consuming significant database time.

       

      The database's maintenance windows were active during 100% of the analysis

      period.

       

      ----------------Instance 2 --------------------

       

       

                ADDM Report for Task 'TASK_36652'

                ---------------------------------

       

      Analysis Period

      ---------------

      AWR snapshot range from 11634 to 11636.

      Time period starts at 21-JUL-13 07.00.03 PM

      Time period ends at 21-JUL-13 09.00.49 PM

       

      Analysis Target

      ---------------

      Database 'MCMSDRAC' with DB ID 2894940361.

      Database version 11.2.0.1.0.

      ADDM performed an analysis of instance mcmsdrac2, numbered 2 and hosted at

      mcmsdbl2.

       

      Activity During the Analysis Period

      -----------------------------------

      Total database time was 2898 seconds.

      The average number of active sessions was .4.

       

      Summary of Findings

      -------------------

          Description                 Active Sessions      Recommendations

                                      Percent of Activity  

          --------------------------  -------------------  ---------------

      1   Top SQL Statements          .11 | 27.65          5

      2   Interconnect Latency        .1 | 24.15           1

      3   Shared Pool Latches         .09 | 22.42          1

      4   PL/SQL Execution            .06 | 14.39          2

      5   Unusual "Other" Wait Event  .03 | 8.73           4

      6   Unusual "Other" Wait Event  .03 | 6.42           3

      7   Unusual "Other" Wait Event  .03 | 6.29           6

      8   Hard Parse                  .02 | 5.5            0

      9   Soft Parse                  .02 | 3.86           2

      10  Unusual "Other" Wait Event  .01 | 3.75           4

       

       

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

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

       

       

                Findings and Recommendations

                ----------------------------

       

      Finding 1: Top SQL Statements

      Impact is .11 active sessions, 27.65% of total activity.

      --------------------------------------------------------

      SQL statements consuming significant database time were found. These

      statements offer a good opportunity for performance improvement.

       

         Recommendation 1: SQL Tuning

         Estimated benefit is .05 active sessions, 12.88% of total activity.

         -------------------------------------------------------------------

         Action

            Investigate the PL/SQL statement with SQL_ID "d1s02myktu19h" for

            possible performance improvements. You can supplement the information

            given here with an ASH report for this SQL_ID.

            Related Object

               SQL statement with SQL_ID d1s02myktu19h.

               begin dbms_utility.validate(:1,:2,:3,:4); end;

         Rationale

            The SQL Tuning Advisor cannot operate on PL/SQL statements.

         Rationale

            Database time for this SQL was divided as follows: 13% for SQL

            execution, 2% for parsing, 85% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "d1s02myktu19h" was executed 48 times and had

            an average elapsed time of 7 seconds.

         Rationale

            Waiting for event "library cache pin" in wait class "Concurrency"

            accounted for 70% of the database time spent in processing the SQL

            statement with SQL_ID "d1s02myktu19h".

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "63wt8yna5umd6" are responsible for 100% of the database time spent on

            the PL/SQL statement with SQL_ID "d1s02myktu19h".

            Related Object

               SQL statement with SQL_ID 63wt8yna5umd6.

               begin DBMS_UTILITY.COMPILE_SCHEMA( 'TPAUSER', FALSE ); end;

       

         Recommendation 2: SQL Tuning

         Estimated benefit is .02 active sessions, 4.55% of total activity.

         ------------------------------------------------------------------

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "fk3bh3t41101x".

            Related Object

               SQL statement with SQL_ID fk3bh3t41101x.

               SELECT MEM.MEMBER_CODE ,MEM.E_NAME,Pol.Policy_no

               ,pol.date_from,pol.date_to,POL.E_NAME,MEM.SEX,(SYSDATE-MEM.BIRTH_DATE

               ) AGE,POL.SCHEME_NO FROM TPAUSER.MEMBERS MEM,TPAUSER.POLICY POL WHERE

               POL.QUOTATION_NO=MEM.QUOTATION_NO AND POL.BRANCH_CODE=MEM.BRANCH_CODE

               and endt_no=(select max(endt_no) from tpauser.members mm where

               mm.member_code=mem.member_code AND mm.QUOTATION_NO=MEM.QUOTATION_NO)

               and member_code like '%' || nvl(:1,null) ||'%' ORDER BY MEMBER_CODE

         Rationale

            The SQL spent 92% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "fk3bh3t41101x" was executed 14 times and had

            an average elapsed time of 4.9 seconds.

         Rationale

            At least one execution of the statement ran in parallel.

       

         Recommendation 3: SQL Tuning

         Estimated benefit is .02 active sessions, 3.79% of total activity.

         ------------------------------------------------------------------

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "7mhjbjg9ntqf5".

            Related Object

               SQL statement with SQL_ID 7mhjbjg9ntqf5.

               SELECT SUM(CNT) FROM (SELECT COUNT(PROC_CODE) CNT FROM

               TPAUSER.TORBINY_PROCEDURE WHERE BRANCH_CODE = :B6 AND QUOTATION_NO =

               :B5 AND CLASS_NO = :B4 AND OPTION_NO = :B3 AND PR_EFFECTIVE_DATE<=

               :B2 AND PROC_CODE = :B1 UNION SELECT COUNT(MED_CODE) CNT FROM

               TPAUSER.TORBINY_MEDICINE WHERE BRANCH_CODE = :B6 AND QUOTATION_NO =

               :B5 AND CLASS_NO = :B4 AND OPTION_NO = :B3 AND M_EFFECTIVE_DATE<= :B2

               AND MED_CODE = :B1 UNION SELECT COUNT(LAB_CODE) CNT FROM

               TPAUSER.TORBINY_LAB WHERE BRANCH_CODE = :B6 AND QUOTATION_NO = :B5

               AND CLASS_NO = :B4 AND OPTION_NO = :B3 AND L_EFFECTIVE_DATE<= :B2 AND

               LAB_CODE = :B1 )

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 0% for SQL execution,

            0% for parsing, 100% for PL/SQL execution and 0% for Java execution.

         Rationale

            SQL statement with SQL_ID "7mhjbjg9ntqf5" was executed 31 times and had

            an average elapsed time of 3.4 seconds.

         Rationale

            Top level calls to execute the SELECT statement with SQL_ID

            "a11nzdnd91gsg" are responsible for 100% of the database time spent on

            the SELECT statement with SQL_ID "7mhjbjg9ntqf5".

            Related Object

               SQL statement with SQL_ID a11nzdnd91gsg.

               SELECT POLICY_NO,SCHEME_NO FROM TPAUSER.POLICY WHERE QUOTATION_NO

               =:B1

       

         Recommendation 4: SQL Tuning

         Estimated benefit is .01 active sessions, 3.03% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the SELECT statement with SQL_ID "4uqs4jt7aca5s" for

            possible performance improvements. You can supplement the information

            given here with an ASH report for this SQL_ID.

            Related Object

               SQL statement with SQL_ID 4uqs4jt7aca5s.

               SELECT DISTINCT USER_ID FROM GV$SESSION, USERS WHERE UPPER (USERNAME)

               = UPPER (USER_ID) AND USERS.APPROVAL_CLAIM='VC' AND USER_ID=:B1

         Rationale

            The SQL spent only 0% of its database time on CPU, I/O and Cluster

            waits. Therefore, the SQL Tuning Advisor is not applicable in this case.

            Look at performance data for the SQL to find potential improvements.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "4uqs4jt7aca5s" was executed 261 times and had

            an average elapsed time of 0.35 seconds.

         Rationale

            At least one execution of the statement ran in parallel.

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "91vt043t78460" are responsible for 100% of the database time spent on

            the SELECT statement with SQL_ID "4uqs4jt7aca5s".

            Related Object

               SQL statement with SQL_ID 91vt043t78460.

               begin TPAUSER.RECEIVE_NEW_FAX_APRROVAL(:V00001,:V00002,:V00003,:V0000

               4); end;

       

         Recommendation 5: SQL Tuning

         Estimated benefit is .01 active sessions, 3.03% of total activity.

         ------------------------------------------------------------------

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "7kt28fkc0yn5f".

            Related Object

               SQL statement with SQL_ID 7kt28fkc0yn5f.

               SELECT COUNT(*) FROM TPAUSER.APPROVAL_MASTER WHERE APPROVAL_STATUS IS

               NULL AND (UPPER(CODED) = UPPER(:B1 ) OR UPPER(PROCESSED_BY) =

               UPPER(:B1 ))

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "7kt28fkc0yn5f" was executed 1034 times and

            had an average elapsed time of 0.063 seconds.

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "91vt043t78460" are responsible for 100% of the database time spent on

            the SELECT statement with SQL_ID "7kt28fkc0yn5f".

            Related Object

               SQL statement with SQL_ID 91vt043t78460.

               begin TPAUSER.RECEIVE_NEW_FAX_APRROVAL(:V00001,:V00002,:V00003,:V0000

               4); end;

       

       

      Finding 2: Interconnect Latency

      Impact is .1 active sessions, 24.15% of total activity.

      -------------------------------------------------------

      Higher than expected latency of the cluster interconnect was responsible for

      significant database time on this instance.

      The instance was consuming 128 kilo bits per second of interconnect bandwidth.

      17% of this interconnect bandwidth was used for global cache messaging, 6% for

      parallel query messaging and 8% for database lock management.

      The average latency for 8K interconnect messages was 41863 microseconds.

      The instance is using the private interconnect device "lan2" with IP address

      172.16.200.72 and source "Oracle Cluster Repository".

      The device "lan2" was used for 100% of interconnect traffic and experienced 0

      send or receive errors during the analysis period.

       

         Recommendation 1: Host Configuration

         Estimated benefit is .1 active sessions, 24.15% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate cause of high network interconnect latency between database

            instances. Oracle's recommended solution is to use a high speed

            dedicated network.

         Action

            Check the configuration of the cluster interconnect. Check OS setup like

            adapter setting, firmware and driver release. Check that the OS's socket

            receive buffers are large enough to store an entire multiblock read. The

            value of parameter "db_file_multiblock_read_count" may be decreased as a

            workaround.

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Inter-instance messaging was consuming significant database time on this

            instance.

            Impact is .06 active sessions, 14.23% of total activity.

               Wait class "Cluster" was consuming significant database time.

               Impact is .06 active sessions, 14.23% of total activity.

       

       

      Finding 3: Shared Pool Latches

      Impact is .09 active sessions, 22.42% of total activity.

      --------------------------------------------------------

      Contention for latches related to the shared pool was consuming significant

      database time.

      Waits for "library cache lock" amounted to 5% of database time.

      Waits for "library cache pin" amounted to 17% of database time.

       

         Recommendation 1: Application Analysis

         Estimated benefit is .09 active sessions, 22.42% of total activity.

         -------------------------------------------------------------------

         Action

            Investigate the cause for latch contention using the given blocking

            sessions or modules.

         Rationale

            The session with ID 17 and serial number 15595 in instance number 1 was

            the blocking session responsible for 34% of this recommendation's

            benefit.

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Wait class "Concurrency" was consuming significant database time.

            Impact is .1 active sessions, 24.96% of total activity.

       

       

      Finding 4: PL/SQL Execution

      Impact is .06 active sessions, 14.39% of total activity.

      --------------------------------------------------------

      PL/SQL execution consumed significant database time.

       

         Recommendation 1: SQL Tuning

         Estimated benefit is .05 active sessions, 12.5% of total activity.

         ------------------------------------------------------------------

         Action

            Tune the entry point PL/SQL "SYS.DBMS_UTILITY.COMPILE_SCHEMA" of type

            "PACKAGE" and ID 6019. Refer to the PL/SQL documentation for addition

            information.

         Rationale

            318 seconds spent in executing PL/SQL "SYS.DBMS_UTILITY.VALIDATE#2" of

            type "PACKAGE" and ID 6019.

       

         Recommendation 2: SQL Tuning

         Estimated benefit is .01 active sessions, 1.89% of total activity.

         ------------------------------------------------------------------

         Action

            Tune the entry point PL/SQL

            "SYSMAN.EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS" of type "PACKAGE" and

            ID 68654. Refer to the PL/SQL documentation for addition information.

       

       

      Finding 5: Unusual "Other" Wait Event

      Impact is .03 active sessions, 8.73% of total activity.

      -------------------------------------------------------

      Wait event "DFS lock handle" in wait class "Other" was consuming significant

      database time.

       

         Recommendation 1: Application Analysis

         Estimated benefit is .03 active sessions, 8.73% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "DFS lock handle" waits. Refer to

            Oracle's "Database Reference" for the description of this wait event.

       

         Recommendation 2: Application Analysis

         Estimated benefit is .03 active sessions, 8.27% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "DFS lock handle" waits in Service

            "mcmsdrac".

       

         Recommendation 3: Application Analysis

         Estimated benefit is .02 active sessions, 5.05% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "DFS lock handle" waits in Module "TOAD

            9.7.2.5".

       

         Recommendation 4: Application Analysis

         Estimated benefit is .01 active sessions, 3.21% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "DFS lock handle" waits in Module

            "toad.exe".

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Wait class "Other" was consuming significant database time.

            Impact is .15 active sessions, 38.29% of total activity.

       

       

      Finding 6: Unusual "Other" Wait Event

      Impact is .03 active sessions, 6.42% of total activity.

      -------------------------------------------------------

      Wait event "reliable message" in wait class "Other" was consuming significant

      database time.

       

         Recommendation 1: Application Analysis

         Estimated benefit is .03 active sessions, 6.42% 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.

       

         Recommendation 2: Application Analysis

         Estimated benefit is .03 active sessions, 6.42% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "reliable message" waits in Service

            "mcmsdrac".

       

         Recommendation 3: Application Analysis

         Estimated benefit is .02 active sessions, 4.13% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "reliable message" waits in Module "TOAD

            9.7.2.5".

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Wait class "Other" was consuming significant database time.

            Impact is .15 active sessions, 38.29% of total activity.

       

       

      Finding 7: Unusual "Other" Wait Event

      Impact is .03 active sessions, 6.29% of total activity.

      -------------------------------------------------------

      Wait event "enq: PS - contention" in wait class "Other" was consuming

      significant database time.

       

         Recommendation 1: Application Analysis

         Estimated benefit is .03 active sessions, 6.29% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "enq: PS - contention" waits. Refer to

            Oracle's "Database Reference" for the description of this wait event.

       

         Recommendation 2: Application Analysis

         Estimated benefit is .02 active sessions, 6.02% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "enq: PS - contention" waits in Service

            "mcmsdrac".

       

         Recommendation 3: Application Analysis

         Estimated benefit is .02 active sessions, 4.93% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "enq: PS - contention" waits with

            P1,P2,P3 ("name|mode, instance, slave ID") values "1347616774", "1" and

            "3599" respectively.

       

         Recommendation 4: Application Analysis

         Estimated benefit is .01 active sessions, 2.74% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "enq: PS - contention" waits in Module

            "Inbox Reader_92.exe".

       

         Recommendation 5: Application Analysis

         Estimated benefit is .01 active sessions, 2.74% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "enq: PS - contention" waits in Module

            "TOAD 9.7.2.5".

       

         Recommendation 6: Application Analysis

         Estimated benefit is .01 active sessions, 1.37% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "enq: PS - contention" waits with

            P1,P2,P3 ("name|mode, instance, slave ID") values "1347616774", "1" and

            "3598" respectively.

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Wait class "Other" was consuming significant database time.

            Impact is .15 active sessions, 38.29% of total activity.

       

       

      Finding 8: Hard Parse

      Impact is .02 active sessions, 5.5% of total activity.

      ------------------------------------------------------

      Hard parsing of SQL statements was consuming significant database time.

      Hard parses due to cursor environment mismatch were not consuming significant

      database time.

      Hard parsing SQL statements that encountered parse errors was not consuming

      significant database time.

      Hard parses due to literal usage and cursor invalidation were not consuming

      significant database time.

      The Oracle instance memory (SGA and PGA) was adequately sized.

       

         No recommendations are available.

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Contention for latches related to the shared pool was consuming

            significant database time.

            Impact is .09 active sessions, 22.42% of total activity.

               Wait class "Concurrency" was consuming significant database time.

               Impact is .1 active sessions, 24.96% of total activity.

       

       

      Finding 9: Soft Parse

      Impact is .02 active sessions, 3.86% of total activity.

      -------------------------------------------------------

      Soft parsing of SQL statements was consuming significant database time.

       

         Recommendation 1: Application Analysis

         Estimated benefit is .02 active sessions, 3.86% 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 .02 active sessions, 3.86% of total activity.

         ------------------------------------------------------------------

         Action

            Consider increasing the session cursor cache size by increasing the

            value of parameter "session_cached_cursors".

         Rationale

            The value of parameter "session_cached_cursors" was "100" 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 .09 active sessions, 22.42% of total activity.

               Wait class "Concurrency" was consuming significant database time.

               Impact is .1 active sessions, 24.96% of total activity.

       

       

      Finding 10: Unusual "Other" Wait Event

      Impact is .01 active sessions, 3.75% of total activity.

      -------------------------------------------------------

      Wait event "IPC send completion sync" in wait class "Other" was consuming

      significant database time.

       

         Recommendation 1: Application Analysis

         Estimated benefit is .01 active sessions, 3.75% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "IPC send completion sync" waits. Refer

            to Oracle's "Database Reference" for the description of this wait event.

       

         Recommendation 2: Application Analysis

         Estimated benefit is .01 active sessions, 3.75% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "IPC send completion sync" waits with P1

            ("send count") value "1".

       

         Recommendation 3: Application Analysis

         Estimated benefit is .01 active sessions, 2.59% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "IPC send completion sync" waits in

            Service "mcmsdrac".

       

         Recommendation 4: Application Analysis

         Estimated benefit is .01 active sessions, 1.73% of total activity.

         ------------------------------------------------------------------

         Action

            Investigate the cause for high "IPC send completion sync" waits in

            Module "TOAD 9.7.2.5".

       

         Symptoms That Led to the Finding:

         ---------------------------------

            Wait class "Other" was consuming significant database time.

            Impact is .15 active sessions, 38.29% of total activity.

       

       

       

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

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

       

                Additional Information

                ----------------------

       

      Miscellaneous Information

      -------------------------

      Wait class "Application" was not consuming significant database time.

      Wait class "Commit" was not consuming significant database time.

      Wait class "Configuration" was not consuming significant database time.

      CPU was not a bottleneck for the instance.

      Wait class "Network" was not consuming significant database time.

      Wait class "User I/O" was not consuming significant database time.

      Session connect and disconnect calls were not consuming significant database

      time.

       

      The database's maintenance windows were active during 100% of the analysis

      period.

       

      Please help.