Hung query - What's going on?

[Warluck]

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?

[Thomasps]

what is the EVENT in v$SESSION_WAIT for your session? Check SEQ# is changing or static.

[vnktummala]

how about tracing with 10046 ?

Thanks,

[mkatwal]

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

[PAVB]

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?

[JChiappa]

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

[Warluck]

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)

[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.

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.

[Warluck]

Every step seems to lead nowhere ...!

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

SQL> select sid, message
  2  from v$session_longops
  3  where sid = 39
  4  order by start_time;

no rows selected

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!?

[PAVB]

May be SID is wrong