5 Replies Latest reply: Jan 17, 2013 11:11 PM by Hemant K Chitale RSS

    Query taking so long to execute.

    969526
      I have one table with 211 rows, When i am executing Delete from TEHSIL_TBL; its taking too long time to delete 211 rows. I execute explain plan then i am getting the following results.



      SQL> explain plan for delete from TEHSIL_TBL;

      Explained.

      SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

      PLAN_TABLE_OUTPUT
      -------------------------------------------------------------------------------------------------
      Plan hash value: 3350021484

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -------------------------------------------------------------------------------
      | 0 | DELETE STATEMENT | | 205 | 1435 | 1 (0)| 00:00:01 |
      | 1 | DELETE | TEHSIL_TBL | | | | |
      | 2 | INDEX FULL SCAN| PK_TEH_ID | 205 | 1435 | 1 (0)| 00:00:01 |
      -------------------------------------------------------------------------------


      Please suggest why that query taking so long tome to execute.


      Thanks in Advance...
      Asmit
        • 1. Re: Query taking so long to execute.
          sb92075
          966523 wrote:
          I have one table with 211 rows, When i am executing Delete from TEHSIL_TBL; its taking too long time to delete 211 rows. I execute explain plan then i am getting the following results.



          SQL> explain plan for delete from TEHSIL_TBL;

          Explained.

          SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

          PLAN_TABLE_OUTPUT
          -------------------------------------------------------------------------------------------------
          Plan hash value: 3350021484

          -------------------------------------------------------------------------------
          | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
          -------------------------------------------------------------------------------
          | 0 | DELETE STATEMENT | | 205 | 1435 | 1 (0)| 00:00:01 |
          | 1 | DELETE | TEHSIL_TBL | | | | |
          | 2 | INDEX FULL SCAN| PK_TEH_ID | 205 | 1435 | 1 (0)| 00:00:01 |
          -------------------------------------------------------------------------------


          Please suggest why that query taking so long tome to execute.
          Please quantify "long time".
          >
          >
          Thanks in Advance...
          Asmit
          EXPLAIN PLAN shows time of 1 SECOND!

          How must faster should it be?
          • 2. Re: Query taking so long to execute.
            969526
            The rows are not deleted the database is in hang state.
            • 3. Re: Query taking so long to execute.
              Purvesh K
              966523 wrote:
              The rows are not deleted the database is in hang state.
              Probably because, some other session is Holding a Lock on those rows and preventing you from deleting them. Perhaps a Long running Query/Group of Queries that does not Commit/Rollback or some developer who has executed a DML against the data but not COMMIT/Rollback.

              Check the output of"
              {code}
              SELECT
              s.blocking_session,
              s.sid,
              s.serial#,
              s.seconds_in_wait
              FROM
              v$session s
              WHERE
              blocking_session IS NOT NULL;

              --Blocking session with more information:
              SELECT s1.username || '@' || s1.machine
              || ' ( SID=' || s1.sid || ' ) is blocking '
              || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
              FROM v$lock l1, v$session s1, v$lock l2, v$session s2
              WHERE s1.sid=l1.sid AND s2.sid=l2.sid
              AND l1.BLOCK=1 AND l2.request > 0
              AND l1.id1 = l2.id1
              AND l2.id2 = l2.id2 ;

              --Blocked Objects
              SELECT sid, u.object_id
              FROM v$lock, user_objects u
              WHERE TYPE='TM'
              and u.object_id = id1;
              {code}

              Use the above queries to find if anybody is blocking your deletes. This will have to be executed when you are executing the Delete statements and when they appear stuck.
              • 4. Re: Query taking so long to execute.
                sb92075
                966523 wrote:
                The rows are not deleted the database is in hang state.
                How can we reproduce what you report?
                • 5. Re: Query taking so long to execute.
                  Hemant K Chitale
                  You are attempting to delete all the rows in the table.
                  The DELETE is waiting for an enqueue -- a row lock held on one or more rows by a session that has executed some DML (DELETE or UPDATE) and not committed yet.

                  Query v$session_wait for the session to find out what it is waiting for.

                  Use %ORACLE_HOME%/\rdbms\admin\utllockt.sql to find the lock holder session.


                  Hemant K Chitale