Kumar,
I suggest you execute SQLT (MOS 215187.1) or sqld360 (http://mauro-pagano.com/
<http://mauro-pagano.com/>) against that SQL and start by reviewing Execution
Plan and all related metadata as provided by those two tools.
The fact the program definition has not change it does not guarantee one of the
SQL statements behind may not had changed its Execution Plan.
Cheers
Carlos Sierra
Life is Good!
On Apr 2, 2015, at 04:06, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
Hello:
A program that normally runs for 30 minutes was running for hours and this
what I observed.
1. From v$session and v$active_session_history noticed that it was waiting on
the same sql (waited short time).
2. v$session.row_wait_obj#, v$session.row_Wait_file#,
v$session.row_wait_block# was pointing to the same object (which was an
index). But the rowid that I got from this was also changing periodically
(every 5 min)
3. The wait event was direct path temp read
4. The program definition was not changed recently
Question is how to proceed further once we find the offending object and rows
(index and rows which were changing). I was not able to tie this back to a
session.
Thank you
Kumar
