Re: Querying awr to hunt down source of sql executions
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: Chris.Stephens@xxxxxxx
- Date: Wed, 2 Mar 2011 18:31:15 +0200
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:
