Re: "read by other session" scenario

That's a good catch! that's possible and I'll look into it.. :)

But let's say that's the case.. could this be the reason why 7kugzf4d2vbbm does not appear in v$active_session_history or dba_hist_active_sess_history at all ... hmm..

On Mon, Jul 19, 2010 at 9:43 PM, Rhojel Echano <rhojel.echano_at_gmail.com>wrote:

> Hi Karl,
>
> I had a quick look at your notes... If you've noticed the part in the
> ASH report that shows the current PL/SQL subprograms, it shows what
> appears to be a trigger: TRIG_RCN_DL_RECN_TRXN. That could be a clue
> to help you answer your question on how RCN_DL_RECN_TRXN could be
> affecting ISWITCH_TRANSACTIONS (or the other way around). Might be
> worthwhile looking through the code if you haven't yet.
>
> Regards,
> Rhojel
>
>
>
>
> On Monday, July 19, 2010, Karl Arao <karlarao_at_gmail.com> wrote:
> > Hi list,
> > I've got an interesting "read by other session" scenario.. and I
> documented the details here
> http://karlarao.tiddlyspot.com/#%5B%5Bread%20by%20other%20session%20scenario%5D%5D
> >
> > that includes the following... workload (CPU,IO,DB), top events, file io,
> top sqls, ASH report, ASH drill down
> > on the last part of the document, I got the following questions:
> >
> >
> >
> > 1) The SQL_ID 7kugzf4d2vbbm
> >
> > .. hmm.. i got this bank application (oltp) with intermittent failures on
> transactions.. then i was focusing on the time of the issue at 12pm-1pm and
> as per the awr report of that period "update" statement with SQLID
> 7kugzf4d2vbbm has the top elapsed with high AAS... and i also have the ASH
> report of the same period but it does not appear there even if I look for
> v$active_session_history or dba_hist_active_sess_history.. what appears in
> ASH as the top SQL was the 2nd top on that AWR top sql report with SQLID
> 8z5wnj8yn5m68...
> >
> > What's weird for me is that 7kugzf4d2vbbm does not appear in
> v$active_session_history or dba_hist_active_sess_history at all
> >
> > I know that the top SQL from AWR gets data from DBA_HIST*SQLSTATS.. and
> if it's consuming some amount of work, and it's always on the top then it's
> got to be somewhere in the ASH but it's not..
> >
> >
> > 2) Read by other session
> >
> > How would it be possible that the SQLs on the ASH report waits mostly on
> this table RCN_DL_RECN_TRXN? And this table is being accessed
> by SQL_ID 7kugzf4d2vbbm... And the table of the SQLs on the ASH report is
> coming from ISWITCH_TRANSACTIONS...
> >
> > How does the update operation on RCN_DL_RECN_TRXN affect the
> table ISWITCH_TRANSACTIONS? or vice versa..
> >
> >
> >
> >
> >
> > I would appreciate your comments on this...
> > --
> > Karl Arao
> > karlarao.wordpress.com
> > karlarao.tiddlyspot.com
> >
>

-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com

--
http://www.freelists.org/webpage/oracle-l