This discussion is archived
9 Replies Latest reply: Jul 8, 2013 10:00 AM by sb92075 RSS

Sessions/connections gets hang during update and select operations.

AnkitAshokAggarwal Explorer
Currently Being Moderated

A table with 3 million records, which has customer details data.

Everyday application is executing select and update queries on that table.

Sessions/connections gets hang during update and select operations.

 

 

After checking ADDM report, following are the findings:

Please suggest the solutions

 

Findings and Recommendations
----------------------------

 

Finding 1: Row Lock Waits
Impact is 145.22 active sessions, 99.77% of total activity.
-----------------------------------------------------------
SQL statements were found waiting for row lock waits.

 

Recommendation 1: Application Analysis
Estimated benefit is 145.22 active sessions, 99.77% of total activity.
----------------------------------------------------------------------
Action
Significant row contention was detected in the TABLE
"AVAYA.AIRTEL_CUSTOMER_MASTER" with object ID 82155. Trace the cause of
row contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 82155.
Rationale
The SQL statement with SQL_ID "974vg65j29pmv" was blocked on row locks.
Related Object
SQL statement with SQL_ID 974vg65j29pmv.
UPDATE AVAYA.AIRTEL_CUSTOMER_MASTER SET PREFERRED_LANGUAGE = :1
WHERE ( AIRTEL_CUSTOMER_MASTER.MSISDN = :2 )
Rationale
The session with ID 50 and serial number 34525 in instance number 1 was
the blocking session responsible for 100% of this recommendation's
benefit.

 

Symptoms That Led to the Finding:
---------------------------------
Wait class "Application" was consuming significant database time.
Impact is 145.22 active sessions, 99.77% of total activity.

 


Finding 2: Top SQL Statements
Impact is 46.39 active sessions, 31.87% 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 46.39 active sessions, 31.87% of total activity.
---------------------------------------------------------------------
Action
Investigate the UPDATE statement with SQL_ID "974vg65j29pmv" 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 974vg65j29pmv.
UPDATE AVAYA.AIRTEL_CUSTOMER_MASTER SET PREFERRED_LANGUAGE = :1
WHERE ( AIRTEL_CUSTOMER_MASTER.MSISDN = :2 )
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 "974vg65j29pmv" was executed 212 times and had
an average elapsed time of 2494 seconds.
Rationale
Waiting for event "enq: TX - row lock contention" in wait class
"Application" accounted for 100% of the database time spent in
processing the SQL statement with SQL_ID "974vg65j29pmv".

 

  • 1. Re: Sessions/connections gets hang during update and select operations.
    Justin Cave Oracle ACE
    Currently Being Moderated

    Is MSISDN the primary key of the table?  If not, how many rows is the UPDATE statement affecting?

     

    Why are multiple sessions trying to update the same row in the table at the same time?  That generally indicates a problem with the design of the application.  Either the application is allowing transactions to remain open across user interactions, in which case a user can lock a row indefinitely if they forget to log out and go to lunch, or the application is not distributing work among users properly or some combination of the two.  That generally requires addressing the underlying application architecture issues.    

     

    Justin

  • 2. Re: Sessions/connections gets hang during update and select operations.
    AnkitAshokAggarwal Explorer
    Currently Being Moderated

    MSISDN is a primary key of table.

  • 3. Re: Sessions/connections gets hang during update and select operations.
    Justin Cave Oracle ACE
    Currently Being Moderated

    OK.  Then why are multiple sessions trying to update the same row at the same time?

     

    Justin

  • 4. Re: Sessions/connections gets hang during update and select operations.
    AnkitAshokAggarwal Explorer
    Currently Being Moderated

    IVR system is doing select/update operations from the master table mentioned in discussion from the database.

     

    No multiple sessions can select/update same row as every row can be only access by unique customer id at one point of time while they are dialling through their phones and getting route towards IVR system later IVR interact with database.

  • 5. Re: Sessions/connections gets hang during update and select operations.
    Justin Cave Oracle ACE
    Currently Being Moderated

    AnkitAshokAggarwal wrote:

     

    No multiple sessions can select/update same row as every row can be only access by unique customer id at one point of time while they are dialling through their phones and getting route towards IVR system later IVR interact with database.

    Oracle disagrees with you.  If your update statement is waiting on row lock contention and it is updating a single row using the primary key, the only possible explanation is that some other session has locked the same row.  Readers do not block writers in Oracle so the only explanation is that some other session has locked the row and that other transaction is still ongoing.  The vast majority of time, that indicates that the other session has updated the same row but it is possible that the other session merely did a SELECT ... FOR UPDATE which would lock the row.  Normal SELECT operations do not lock rows (readers do not block writers and writers do not block readers).

     

    I'd wager that Oracle is correct here and that you are wrong and that there are multiple sessions updating (or at least locking) the same row.  You'll need to figure out why your application isn't behaving as it was, apparently, designed and correct the application bug (assuming your description is the behavior you would deem to be correct)

     

    Justin

  • 6. Re: Sessions/connections gets hang during update and select operations.
    AnkitAshokAggarwal Explorer
    Currently Being Moderated

    ....  I noticed a version bin mismatch  ... 32 bit installed on the 64 bit server and alertlog throws error. We can correct this at an agreed time

     

    SQL> select * from v$version ;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    Excerpt from alertlog ::::

     

    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:46:25 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:46:39 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:46:53 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:47:15 2013
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:47:37 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:47:48 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:47:59 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Jul 08 12:48:11 2013
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !

     

     

    is this can be any help in this ???

  • 7. Re: Sessions/connections gets hang during update and select operations.
    AnkitAshokAggarwal Explorer
    Currently Being Moderated

    **addm report **

     

     

              ADDM Report for Task 'TASK_7526'

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

     

     

    Analysis Period

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

    AWR snapshot range from 5003 to 5004.

    Time period starts at 08-JUL-13 11.00.27 AM

    Time period ends at 08-JUL-13 12.00.45 PM

     

     

    Analysis Target

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

    Database 'AVAYADB' with DB ID 2878789264.

    Database version 11.2.0.1.0.

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

    NG-LA04AVAYA01.

     

     

    Activity During the Analysis Period

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

    Total database time was 563062 seconds.

    The average number of active sessions was 155.63.

     

     

    Summary of Findings

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

       Description         Active Sessions      Recommendations

                           Percent of Activity

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

    1  Row Lock Waits      155.44 | 99.88       1

    2  Top SQL Statements  26.67 | 17.14        1

     

     

     

     

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

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

     

     

     

     

              Findings and Recommendations

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

     

     

    Finding 1: Row Lock Waits

    Impact is 155.4 active sessions, 99.88% of total activity.

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

    SQL statements were found waiting for row lock waits.

     

     

       Recommendation 1: Application Analysis

       Estimated benefit is 155.44 active sessions, 99.88% of total activity.

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

       Action

          Significant row contention was detected in the TABLE

          "AVAYA.AIRTEL_CUSTOMER_MASTER" with object ID 82155. Trace the cause of

          row contention in the application logic using the given blocked SQL.

          Related Object

             Database object with ID 82155.

       Rationale

          The SQL statement with SQL_ID "974vg65j29pmv" was blocked on row locks.

          Related Object

             SQL statement with SQL_ID 974vg65j29pmv.

             UPDATE AVAYA.AIRTEL_CUSTOMER_MASTER SET PREFERRED_LANGUAGE = :1

             WHERE  ( AIRTEL_CUSTOMER_MASTER.MSISDN = :2 )

       Rationale

          The session with ID 167 and serial number 6084 in instance number 1 was

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

          benefit.

     

     

       Symptoms That Led to the Finding:

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

          Wait class "Application" was consuming significant database time.

          Impact is 155.45 active sessions, 99.88% of total activity.

     

     

     

     

    Finding 2: Top SQL Statements

    Impact is 26.66 active sessions, 17.14% 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 26.67 active sessions, 17.14% of total activity.

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

       Action

          Investigate the UPDATE statement with SQL_ID "974vg65j29pmv" 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 974vg65j29pmv.

             UPDATE AVAYA.AIRTEL_CUSTOMER_MASTER SET PREFERRED_LANGUAGE = :1

             WHERE  ( AIRTEL_CUSTOMER_MASTER.MSISDN = :2 )

       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 "974vg65j29pmv" was executed 707 times and had

          an average elapsed time of 794 seconds.

       Rationale

          Waiting for event "enq: TX - row lock contention" in wait class

          "Application" accounted for 100% of the database time spent in

          processing the SQL statement with SQL_ID "974vg65j29pmv".

     

     

     

     

     

     

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

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

     

     

              Additional Information

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

     

     

    Miscellaneous Information

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

    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.

  • 8. Re: Sessions/connections gets hang during update and select operations.
    AnkitAshokAggarwal Explorer
    Currently Being Moderated

    Top 5 Timed Foreground Events from AWR

     

    EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
    enq: TX - row lock contention64562,398878746999.88Application
    db file sequential read17,3813,6872120.65User I/O
    log file sync15,967193120.03Commit
    DB CPU 50 0.01
    db file scattered read67526390.00User I/O

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points