Re: Querying awr to hunt down source of sql executions

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@xxxxxxx>wrote:

>  Absolutely but why don’t the modules sync up between different awr tables
> and v$active_session_history?
>
>
>
> *From:* Dominic.Brooks@xxxxxxxxxxxxxxxxxxx [mailto:
> Dominic.Brooks@xxxxxxxxxxxxxxxxxxx]
> *Sent:* Wednesday, March 02, 2011 9:56 AM
> *To:* Stephens, Chris; oracle-l@xxxxxxxxxxxxx
> *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?
>
>
>

Other related posts: