Re: "read by other session" scenario
- From: Rhojel Echano <rhojel.echano@xxxxxxxxx>
- To: "karlarao@xxxxxxxxx" <karlarao@xxxxxxxxx>
- Date: Mon, 19 Jul 2010 21:43:40 +0800
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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
