Re: Querying awr to hunt down source of sql executions
Date: Wed, 2 Mar 2011 18:31:15 +0200
Message-ID: <AANLkTi=yhy2V7D7AdWuGZ7yAMghJrVkNwSPF8BQLY3gM_at_mail.gmail.com>
V$SQL% views show the MODULE and ACTION of that session who *hard parsed* the query. And it's stored only once - for parent cursor and not for children.
So all subsequent executions of any child of that parent cursor will still show the original parsing session's module & action, regardless of what these attributes are for executing sessions....
ASH doesn't have that problem as it doesn't take the module/action from v$sql cursor but from executing session's attributes.
That's only one of the reasons... Another may be that recursive calls (Calling pl/sql from sql etc) may not set or clear their module/action and even sql_id attributes properly when context switching from SQL to PL/SQL and back... these are due to bugs or design limitations... and that's why there are some new columns like TOP_LEVEL_SQL_ID in addition to just SQL_ID to differentiate between the call which application made vs. something executed recursively...
P.S. To Exadata geeks out there - our book can be purchased as an
Alpha/Draft PDF from Apress already!
http://blog.tanelpoder.com/2011/03/02/expert-oracle-exadata-book-alpha-chapters-available-for-purchase/
-- Tanel Poder Oracle Troubleshooting Blog - http://blog.tanelpoder.com Online Troubleshooting Seminars - http://tech.e2sn.com/oracle-training-seminars On Wed, Mar 2, 2011 at 5:57 PM, Stephens, Chris <Chris.Stephens_at_adm.com>wrote:Received on Wed Mar 02 2011 - 10:31:15 CST
> Absolutely but why don’t the modules sync up between different awr tables
> and v$active_session_history?
>
>
>
> *From:* Dominic.Brooks_at_barclayscapital.com [mailto:
> Dominic.Brooks_at_barclayscapital.com]
> *Sent:* Wednesday, March 02, 2011 9:56 AM
> *To:* Stephens, Chris; oracle-l_at_freelists.org
> *Subject:* RE: Querying awr to hunt down source of sql executions
>
>
>
> Isn’t it that this recording of module comes from v$sql, ultimately, and so
> it’s whatever module was executing when the statement was parsed?
>
>
>
-- http://www.freelists.org/webpage/oracle-l