Again. Since you have SQL_ID you may want to use SQLT or SQLd360 to facilitate
diagnostics. There are many possible reason for a Plan to change.
Sent from my iPhone
On Apr 2, 2015, at 20:54, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
Hello:
Thank you for all the inputs so far.
I did actually get the concurrent program name and other details associated
(like program name, module name and even user name). Running this
select sql_id, sql_plan_hash_value, count(*) cnt
from dba_hist_active_sess_history
where sql_id = 'cd18pgvsvmg8c'
group by sql_id, SQL_PLAN_HASH_VALUE;
I found sql_plan_hash_value 2624643529 was executed 4100 times and this was
what was running yesterday when the problem happened(can confirm that from
the time period).
Now the question for me is what caused this bad plan to be produced ? The
user says nothing changed on their side. I thinking flushing the sql above
helped in making the program complete. But why that bad plan gets generated
(I mean how can I track it back to the process that caused this to happen).
Thank you
Kumar
On Thu, Apr 2, 2015 at 5:39 AM, Tim Gorman <tim@xxxxxxxxx> wrote:
Kumar,
You had found the related rows in v$session and v$active_session_history, so
yes you certainly have tied it back to a session. Perhaps not to a person's
name and a workstation, but you could check MOS for notes like " When Users
Login In Apps Where Is Registered Login Information Stored? [ID 436512.1]"
to determine that.
Since this is EBiz, that means you have other information such as the PL/SQL
package and procedure, as well as the EBiz PROGRAM, MODULE and ACTION, so
you should have some idea of what is executing (i.e. interactive user,
ConcMgr, report, etc).
You can run a query like...
select trunc(timestamp) day, sql_plan_hash_value, count(*) cnt
from dba_hist_active_sess_history
where sql_id = 'value'
group by trunc(timestamp)
order by day;
...to see if the SQL execution plan (i.e. SQL_PLAN_HASH_VALUE) has changed
over time. If it has, then you have the answer to the variability in
elapsed time of the SQL. Now you just need to track down the cause.
Let us know what you find?
Hope this helps...
-Tim
On 4/2/15 2:06, Kumar Madduri 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
