9 Replies Latest reply: Dec 12, 2012 1:46 AM by Santosh Pradhan RSS

    UPDATE TAKING LONG TIME

    Santosh Pradhan
      HELLO
      MY DATABASE VERSION IS 11.2.0.1.0 ON WINDOWS SERVER 2003

      i queried for inactive sessions more than 90 min and found some sessions.

      Before killing those i queried for what those sessions are doing. When i got the output no rows i killed those sessions, But there was this 1 session which had an update.

      1) Now my question why such long time for this update

      I have checked for any blocking sessions but there are none.

      So i thought of getting the explain plan for that query. I will post the output. Can any body explain it to me.

      THE QUERY WAS

      UPDATE SYS.SNAP$ SET AUTO_FUN = 'null' WHERE (SOWNER,VNAME) IN (SELECT RC.OWNER, RC.NAME FROM SYS.RGCHILD$ RC,
      SYS.RGROUP$ R WHERE R.JOB = :B1 AND RC.REFGROUP = R.REFGROUP) AND INSTSITE = 0


      Explain plan for above query is

      SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------
      Plan hash value: 3291263927

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------------------------
      | 0 | UPDATE STATEMENT | | 1 | 143 | 2 (0) | 00:00:01 |
      | 1 | UPDATE | SNAP$ | | | | |
      | 2 | NESTED LOOPS SEMI | | 1 | 143 | 2 (0) | 00:00:01 |
      |* 3 | TABLE ACCESS FULL | SNAP$ | 1 | 139 | 2 (0) | 00:00:01 |
      | 4 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 4 | 0 (0) | 00:00:01 |
      | 5 | NESTED LOOPS | | 1 | 73 | 0 (0) | 00:00:01 |
      | 6 | TABLE ACCESS BY INDEX ROWID | RGCHILD$ | 1 | 47 | 0 (0) | 00:00:01 |
      |* 7 | INDEX RANGE SCAN | I_RGCHILD | 1 | | 0 (0) | 00:00:01 |
      |* 8 | TABLE ACCESS CLUSTER | RGROUP$ | 1 | 26 | | |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      3 - filter("INSTSITE"=0)
      7 - access("RC"."OWNER"="SOWNER" AND "RC"."NAME"="VNAME")
      8 - filter("R"."JOB"=TO_NUMBER(:B1) AND "RC"."REFGROUP"="R"."REFGROUP")

      Edited by: user12045405 on Dec 12, 2012 11:46 AM
        • 1. Re: UPDATE TAKING LONG TIME
          Santosh Pradhan
          Can anyone explain whats happening
          • 2. Re: UPDATE TAKING LONG TIME
            Nikolay Savvinov
            Hi,

            what you've posted is just an explain plan, which is an optimizer estimate of what would happen during runtime, and not what actually happened. Oracle 11.2 has a rich set of performance diagnostic tools, if you have the Diagnostic Pack License, especially SQL real time monitoring and improved ASH. Using them, it's easy to understand where the query spends most of the time and why.

            But since this is a relatively simple statement, you can also try to tackle it the old way, but checking row counts against cardinality estimates in the plan. For example, the optimizer claims that the full table scan at step 3 returns just 1 row and only costs an equivalent of 2 single-block reads. You can check that:
            select count(*) from snap$ where INSTSITE=0
            
            select sum(blocks) from dba_segments where segment_name = 'SNAP$'
            The first query returns the actual number of rows that would be returned at step 3 (provided data in that table hasn't changed much since the query was run), the second query shows the actual size of the table in blocks. Post the output of these two queries and we'll have something to compare optimizer estimates against.

            Best regards,
            Nikolay
            • 3. Re: UPDATE TAKING LONG TIME
              Santosh Pradhan
              SQL> select count(*) from snap$ where INSTSITE=0;

              COUNT(*)
              ----------
              1

              SQL> select sum(blocks) from dba_segments where segment_name = 'SNAP$';

              SUM(BLOCKS)
              -----------
              8
              • 4. Re: UPDATE TAKING LONG TIME
                Nikolay Savvinov
                Hi,

                thanks, looks like the optimizer is correct about these steps. Can you post the output of:
                select count(*) from rgchild$ rc where (owner, name) in (select sowner, vname from snap$ where instsite=0);
                Best regards,
                Nikolay
                • 5. Re: UPDATE TAKING LONG TIME
                  Santosh Pradhan
                  Count is zero.


                  My question is the user session firing this session was inactive for more than 90 min. Was the query still running. I also checked for long operation but it returned no rows.
                  What am i missing.
                  And can u please meake me understand how to intepret the explain plan. Any good links or docs will be helpful. Found some but couldnt get much out of it.

                  Thanks.
                  • 6. Re: UPDATE TAKING LONG TIME
                    Nikolay Savvinov
                    Hi,

                    sorry for the confusion -- I thought that the query had been running for 90 min. If the session was inactive then it's probably not the case. The UI you used to analyze unused sessions probably displayed the last SQL captured (which could have completed hours before that).

                    Best regards,
                    Nikolay
                    • 7. Re: UPDATE TAKING LONG TIME
                      Santosh Pradhan
                      ohhhh

                      thanks
                      but how can i be sure that the query isnt running. I mean if there is no output in long operation query then is it ok to carry ahead and kill that session.

                      Also if u know any good links for explain plan please share.

                      Thanks and really i appreciate for ur help.
                      • 8. Re: UPDATE TAKING LONG TIME
                        Nikolay Savvinov
                        Hi,

                        if the query is running, then the session is active, and it's state is either on CPU or it's waiting on a non-idle wait event (such as db file sequential/scattered read, direct path read temp etc.). Since the session is not active, then the query is not running.
                        However, this doesn't mean that it's okay to kill it -- it may contain uncommited changes. At very least, you should check if the session is holding any locks. But more generally, you should also ask yourself why do you want to kill inactive sessions? If you think that they consume some valuable resources and the database will run faster with them killed, then you are wrong (well, with 99.9% of chance).

                        Best regards,
                        Nikolay
                        • 9. Re: UPDATE TAKING LONG TIME
                          Santosh Pradhan
                          Thanks a lot