Hi Rhojel, 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@xxxxxxxxx>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@xxxxxxxxx> 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