time_remaining ever increasing in v$session_longops

Printable View

07-21-2008, 12:29 AM
gtcol

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.

Thanks,
gtcol
07-21-2008, 02:53 AM
mdvreddy

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

Cheers..
/MR
07-21-2008, 03:56 AM
akas_chauhan

Did you take trace of this session before killing it?
If yes analyze that, or take the sql, analyze the explain plan.
07-21-2008, 04:18 AM
gtcol

1 Attachment(s)

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,
gtcol
07-21-2008, 06:24 AM
gtcol

hello all
any suggestion/advice is highly appreciated on the issue.

Thanks
gtcol
07-21-2008, 09:01 AM
akas_chauhan

you got lot of FTS on big tables (more than 1 million record). You got to see that first.
07-23-2008, 08:38 AM
gtcol

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.

gtcol
07-24-2008, 05:58 AM
PAVB

Quote:

Originally Posted by gtcol

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