time_remaining ever increasing in v$session_longops
Hello all
i've a query running since last 5 hours, which should have finished by 4 hours. Upon checking v$session_lops, I found that time_remamining is ever increasing. I had to kill the session and now I'll have to run the proc again. Could anybody please suggest/advice what could have gone wrong there or if there is anything, I can do to prevent in the next run? Any help is appreciated.
look into V$ACTIVE_SESSION_HISTORY table as it consist last 30 minutes execution details of sql statements and look for events .
whats your oracle DB version !!?
mdvreddy
Oracle version is 10.2.0.3.0 on Red Hat 3.4.6-3. I apologize that I wasn't descriptive on my earlier post. I've attached the plan for the query. I've created all the zzz tables in recycle pool. SGA_MAX_SIZE is 15G. DB_MULTIBLOCK_READ_COUNT is set to 128.
Thanks akas_chauhan for sharing your view. For the time being, I did rewrite the query, couldn't avoid the FTS though. And also changed the disk (as advised by our IT team). Seems like we'd problem with the disk itself. The query has finished within the regular time.
... also changed the disk (as advised by our IT team)
It is expected to get better performance when you "change the disk", this happens because of when you move from the "bad-old" disk to the "new-good" disk usually you do it by resorting to exp/imp or sqlcopy command or create-table-as-select over a dblink.
In all the above mentioned cases you are actually reorganizing the objects, rebuilding indexes and perhaps gathering fresh statistics. In such an scenario you would get better peformance no matter if you move the data to a "new" disk or if you stay in your "old"one.
Interesting enough, Systems guys usually have a genetic disorder than prevents them for understanding or even acknowledging that fact then they keep suggesting moving to a new-good disk... it works all the time!
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.
Reply With Quote
Originally Posted by gtcol
