-
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?
-
what is the EVENT in v$SESSION_WAIT for your session? Check SEQ# is changing or static.
-
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.
-
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
-
Originally Posted by Warluck
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.
-
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
-
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)
-
Originally Posted by Warluck
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.
-
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!?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|