Hung query - What's going on?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Hung query - What's going on?

  1. #1
    Join Date
    Nov 2010
    Posts
    14

    Hung query - What's going on?

    Bonjour,
    I need help of the community because my search for a solution leads nowhere and I must get the job done but first of all reason, I need to know why it doesn't work.

    I'll try to explain the more clearly I can.

    I have a table that represents around 6G of data. I need to reorganize the table because it has more than 40K of chained rows and queries are getting slower and slower.

    I have this table on two different environment, the same table, one in dev and the other in production. Testing the reorg on the dev has prety much easy, with CATS or dbms_redefinition, the job gets done within an hour or two, no problem!

    The problem is in production. I first tried to reorganize the table using dbms_redefinition, after 6 hours of ongoing job I have to cancel it and abort the process because it hasn't moved at all.

    I tried the second way, with create table as select. But the situation is quite the same as it was with dbms_redefinition! The job as been lauched at midnight and it is now 14h and it is not done yet.

    I can't even find my query in the v$sqlarea! Does queries get flushed from the v$sqlarea after a while or is this an indication that nothing is going on the server?

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    what is the EVENT in v$SESSION_WAIT for your session? Check SEQ# is changing or static.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    how about tracing with 10046 ?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Nov 2010
    Posts
    1
    Are instances in Dev and Prod environments same? Depending on the query itself, you may want to take a look at the SGA structure on both Dev and Prod and compare it.

    The other thing might be the size of the TEMP tablespace. Prod server may need to tune for the memory.

    Best of luck.
    Manoj

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Warluck View Post
    I can't even find my query in the v$sqlarea! Does queries get flushed from the v$sqlarea after a while or is this an indication that nothing is going on the server?
    Did you started the job in the host as nohup or did you started the job thru a GUI tool in a client computer?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    IF the db version is 10g (R2, iirc) or better, AND the no special config was made, THEN the V$SQLxx views will be constantly updated each few seconds, giving to you some useful informations ...

    Beside that,being a long query, V$SESSION_LONGOPS can have more information, if a long operation (ie,things like table scan) are being done...

    Regards,

    Chiappa

  7. #7
    Join Date
    Nov 2010
    Posts
    14
    Thanks to all of you, I am reviwing replies right now;

    @Thomasps
    I can't remember what was in the V$session_wait but I will definetly have a look on it next time. I remember it was something in regard with lob direct path write and read.

    @vnktummala
    Well, I absolutely don't know what you are refering to!

    @mkatwal
    This is interesting! I resized SGA(db_cache_size, shared_pool, java_pool_size, large_pool_size) and PGA last week since they were really not correct, had only 44mo of PGA and now have 200Mo. I've gone through an anylizing process to size all this. I think I've done a good job at it but it change nothing for this particular job. NOW, I have large temp space on dev and qa but only a small 1G of temp space on my production server. How can I know that my redefinition process is using temp space or not besides increasing it?

    I'll make it a question, How can I know if a particular query will use or not the temp space?

    @PAVB
    As a former Sun/Solaris Unix Sysadmin I can't tell you how much I despise GUI ;o) I've started the job in a SQLPLUS session. I've tried with DBArtisan with the same result though.

    @JChiappa
    This instance comes from the dinosaure's era, it is an Oracle 9i and wish it could be moved to 11gR2 any time soon but as an Administrator I am customer dependant so ... 2011 for sure ;p)

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Warluck View Post
    As a former Sun/Solaris Unix Sysadmin I can't tell you how much I despise GUI ;o) I've started the job in a SQLPLUS session. I've tried with DBArtisan with the same result though.
    I'll suggest to wrap the script into a shell script and either execute via cron or nohup. Chances are client timed out, that's why it appears to never comes back.

    As suggested by JChiappa I would monitor v$session_longops and as suggested by Thomas I'll also monitor wait events.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Nov 2010
    Posts
    14


    Every step seems to lead nowhere ...!

    Code:
    SQL> select a.sid, a.serial#, b.sql_text
      2  from v$session a, v$sqlarea b
      3  where a.sql_address=b.address
      4  and a.sid = 39;
    
    no rows selected
    Code:
    SQL> select sid, message
      2  from v$session_longops
      3  where sid = 39
      4  order by start_time;
    
    no rows selected
    Code:
    SQL> select sid, event, wait_time, seconds_in_wait, state
      2  from v$session_wait    
      3  where sid=39;
    
           SID EVENT
    ---------- ----------------------------------------------------------------
     WAIT_TIME SECONDS_IN_WAIT STATE
    ---------- --------------- -------------------
            39 direct path read (lob)
             0               0 WAITING
    Does anyone knows what is wrong?

    Nothing in v$sqlarea and nothing in the v$session_longops neither!

    And the v$session_wait alternate between "direct path write (lob)" and "direct path read (lob)".

    To be completely honest, I don't know where to look now!?

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    May be SID is wrong
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width