9 Replies Latest reply: Oct 3, 2013 3:45 AM by Flake RSS

    Query stucking

    Flake

      Hi All,

       

      I am facing an issue here. I ran the below query after connecting to database as sysdba, but the query seem to just hang. Do you have any

      idea why this is not returning any result. Any help is very much appreciated.

       

      select a.session_recid, a.output_device_type, a.operation, a.status,

      a.COMMAND_ID,

      a.start_time, a.end_time

      from v$rman_status a,

      v$rman_status b

      where trunc (b.start_time) = (select trunc (max (start_time)) from v$rman_status )

      and a.session_recid = b.session_recid

      and b.operation in ('RMAN')

      connect by a.parent_recid = a.recid

      group by  a.session_recid, a.output_device_type, a.operation, a.status, a.COMMAND_ID, a.start_time, a.end_time

      order by a.session_recid ;

       

      The query completes if "connect by" is removed.

       

      Thanks,

      ..

        • 1. Re: Query stucking
          Hoek


          Then why the connect by in the first place?

          And you probably need to add the PRIOR keyword to one of the columns.

          See:

          Hierarchical Queries

          • 2. Re: Query stucking
            ranit B

            Does a query really "hang"?

             

            Yes, but the client tool (IDE) hangs. In the background the query might be running very well, but due to a costly query IDE go slow.

             

            Try using SQL*Plus and let us know if it hangs.

            • 3. Re: Query stucking
              BluShadow

              It will try and do the connect by clause before applying the where clause, so it's going to try and connect all the data in the tables first, which I'm guessing is quite a lot if it's taking a long time.

               

              Try doing the restrictions in an inner-query and then apply the connect by to get what you want in an outer query.

              • 4. Re: Query stucking
                ranit B

                Can you try this once?

                 

                WITH table_x AS(

                  SELECT s.*, Trunc(Max(start_time) OVER()) sdt

                  FROM v$rman_status s

                )

                SELECT

                    a.session_recid,

                  a.output_device_type,

                  a.operation,

                  a.status,

                  a.command_id,

                  a.start_time,

                  a.end_time

                FROM table_x a, table_x b

                WHERE

                  Trunc(b.start_time) = a.sdt

                  and a.session_recid = b.session_recid

                and b.operation in ('RMAN')

                connect by a.parent_recid = a.recid

                group by  a.session_recid, a.output_device_type, a.operation, a.status, a.COMMAND_ID, a.start_time, a.end_time

                order by a.session_recid;

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

                | Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |

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

                |   0 | SELECT STATEMENT                         |                             |     1 |   153 |    12  (67)| 00:00:01 |

                |   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |         |             |

                |   2 |   LOAD AS SELECT                         | SYS_TEMP_0FD9D66DF_9130442B |       |       |         |             |

                |   3 |    WINDOW BUFFER                         |                             |     1 |   479 |     7 (100)| 00:00:01 |

                |*  4 |     HASH JOIN                            |                             |     1 |   479 |     7 (100)| 00:00:01 |

                |*  5 |      HASH JOIN                           |                             |     1 |   414 |     4 (100)| 00:00:01 |

                |   6 |       VIEW                               |                             |   100 |  3600 |     3 (100)| 00:00:01 |

                |   7 |        SORT UNIQUE                       |                             |   100 |  6200 |     3 (100)| 00:00:01 |

                |*  8 |         HASH JOIN OUTER                  |                             |   100 |  6200 |     2 (100)| 00:00:01 |

                |   9 |          FIXED TABLE FULL                | X$KCCRSR                    |   100 |  2600 |     0   (0)| 00:00:01 |

                |  10 |          VIEW                            |                             |     1 |    36 |     1 (100)| 00:00:01 |

                |  11 |           WINDOW SORT                    |                             |     1 |    49 |     1 (100)| 00:00:01 |

                |* 12 |            FIXED TABLE FULL              | X$KSFQP                     |     1 |    49 |     0   (0)| 00:00:01 |

                |* 13 |       HASH JOIN RIGHT OUTER              |                             |   100 | 37800 |     1 (100)| 00:00:01 |

                |  14 |        VIEW                              |                             |     1 |   149 |     0   (0)| 00:00:01 |

                |* 15 |         FIXED TABLE FULL                 | X$KRBMRST                   |     1 |   151 |     0   (0)| 00:00:01 |

                |  16 |        FIXED TABLE FULL                  | X$KCCRSR                    |   100 | 22900 |     0   (0)| 00:00:01 |

                |  17 |      VIEW                                |                             | 10000 |   634K|     3 (100)| 00:00:01 |

                |  18 |       SORT GROUP BY                      |                             | 10000 |   888K|     3 (100)| 00:00:01 |

                |* 19 |        HASH JOIN OUTER                   |                             | 10000 |   888K|     2 (100)| 00:00:01 |

                |  20 |         FIXED TABLE FULL                 | X$KCCRSR                    |   100 |  2600 |     0   (0)| 00:00:01 |

                |  21 |         VIEW                             |                             |   100 |  6500 |     1 (100)| 00:00:01 |

                |  22 |          SORT GROUP BY                   |                             |   100 |  6500 |     1 (100)| 00:00:01 |

                |  23 |           FIXED TABLE FULL               | X$KSFQP                     |   100 |  6500 |     0   (0)| 00:00:01 |

                |  24 |   SORT GROUP BY                          |                             |     1 |   153 |     6  (34)| 00:00:01 |

                |* 25 |    FILTER                                |                             |       |       |         |             |

                |* 26 |     CONNECT BY WITHOUT FILTERING (UNIQUE)|                             |       |       |         |             |

                |* 27 |      HASH JOIN                           |                             |     1 |   153 |     5  (20)| 00:00:01 |

                |  28 |       VIEW                               |                             |     1 |   116 |     2   (0)| 00:00:01 |

                |  29 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D66DF_9130442B |     1 |   254 |     2   (0)| 00:00:01 |

                |  30 |       VIEW                               |                             |     1 |    37 |     2   (0)| 00:00:01 |

                |  31 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D66DF_9130442B |     1 |   254 |     2   (0)| 00:00:01 |

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

                • 5. Re: Query stucking
                  Flake

                  Hi Ranit,

                   

                    Thanks for taking time to respond with an alternative query. For your first post, connection is made from SQL Plus and query "appears" to be hung. It queries V$RMAN_STATUS and the data volume is not big enough to run longer. To check if it is affected by volume, I left this query running for more than 2 hours of time. But it still didn't return result. This query is a part of package that makes remote connection to all databases and gets data. It still connects to all databases, gets data. But, it hangs on a particular DB and hence, it doesn't proceed to do other DBs.

                   

                  One clue I'd like to give is. this package was early compiled on a 10g DB. I moved this package to 11g db and now it runs from 11g. But, the versions of DBs that it connects to remains the same version.

                  • 6. Re: Query stucking
                    Billy~Verreynne

                    Determine why the session seems stuck/hung. Look at its wait state (V$SESSION_WAIT) and session status (V$SESSION).

                    • 7. Re: Query stucking
                      Flake

                      Hi Billy,

                       

                      Session status is ACTIVE in v$session. v$session_wait table shows below:

                      SEQ# : 52691

                      EVENT : control file sequential read

                      WAIT_CLASS : System I/O

                      WAIT_TIME : -1

                      STATE : WAITED SHORT TIME

                       

                      I also copied the data from V$rman_status from DB that wasn't working to a test box and ran the query. The query completed in no time with results.

                      • 8. Re: Query stucking
                        Billy~Verreynne

                        That may not be the actual cause of wait time - as waited short time does seem to indicate the process spinning on that event. What is the seconds in wait?

                         

                        There are bugs in 10.2.x relating to this event (slow SQL spinning on this event). But it would be premature to say that a bug is the cause.

                         

                        To get a clean baseline, I would start a new sqlplus session, get its SID for monitoring (via another session), and then execute the problem SQL via the new session. And while it executes, monitor its wait states and events (V$SESSION_EVENT) via another session. Note that V$SESSION does provide session blocker details if a blocking session is detected. Wait states are documented, and if I'm not mistaken, their parameters too are explained? This can shed a lot of light on what specifically the wait state is waiting on (e.g. parameter 1could tell the file id, the 2nd parameter the block id, enabling you to identify the object that owns that block that the wait state is attempting to I/O).

                         

                        The fact that the same query is apparently without any issues on another database instance is not really relevant at this stage (no two instances or databases are identical physically and environmentally).

                        • 9. Re: Query stucking
                          Flake

                          Hi Billy,

                           

                          I see in v$session_events, the below creeping high:

                           

                          select sid, event, total_waits, total_timeouts, time_waited, max_wait, event_id, wait_class from v$session_event

                          where sid = 443  order by total_waits;

                           

                             443 SQL*Net break/reset to client           8          0       0      0 1963888671 Application
                             443 events in waitclass Other              10          0       3      1 1736664284 Other
                             443 SQL*Net message from client            46          255687  53430 1421975091 Idle
                             443 SQL*Net message to client              47          0       0      0 2067390145 Network
                             443 control file sequential read          336          0     102      3 3213517201 System I/O
                             443 direct path write temp               3186          0    1094      4   38438084 User I/O
                             443 direct path read temp              489736          163346     37  861319509 User I/O

                           

                          But, only "direct path read temp" seems to be increasing.

                           

                          In v$session_waits:

                          EVENT : direct path read temp

                          P1:801

                          P1TEXT : file number
                          P2 : 1308678

                          P2TEXT : first dba

                          P3 : 1

                          P3TEXT : block cnt

                          WAIT_TIME : -1

                          SECONDS_IN_WAIT : 0

                          STATE :  WAITED SHORT TIME

                           

                          Do you have any clue?

                           

                          Thanks,

                          ...