Re: what package/procedure did SQL come from?
- From: Jurijs Velikanovs <j.velikanovs@xxxxxxxxx>
- To: kylelf@xxxxxxxxx
- Date: Tue, 2 Jun 2009 11:12:51 +1000
Hi Kyle,
> Is there a way to do this before 10.2.0.4?
The only way I could think about is to search through the code:
select --+ FULL(s) PARALLEL (s,8)
OBJ#, LINE, SOURCE from sys.source$ s where 1=1
and upper(s.source) like upper('%< good part of SQL you are looking for >%');
I know that it isn't something that you asked for but it might help.
Yury
On Tue, Jun 2, 2009 at 7:58 AM, kyle Hailey <kylelf@xxxxxxxxx> wrote:
> I want to correlate SQL to the packages and procedures they came from.
> Is there a way to do this before 10.2.0.4?
>
> Starting in 10.2.0.4 this is pretty easy thanks to the fields
>
> PLSQL_ENTRY_OBJECT_ID
> PLSQL_ENTRY_SUBPROGRAM_ID
> PLSQL_OBJECT_ID
> PLSQL_SUBPROGRAM
>
> in v$session and v$active_session_history. A nice output can be put
> out using a script like
>
> http://www.perfvision.com/ash/ashpl2.sql
>
> to give
>
> COUNT(*) SQL_ID calling_code
> --------- -------------
> --------------------------------------------------------------------
> 2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_RANDOM.VALUE
> 2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_LOCK.SLEEP
> 3 1xxksrhwtz3zf ORDERENTRY.NEWORDER =>
> DBMS_APPLICATION_INFO.SET_ACTION
> 13 1xxksrhwtz3zf ORDERENTRY.NEWORDER
> 76 dw2zgaapax1sg ORDERENTRY.NEWORDER
> 131 75621g9y3xmvd ORDERENTRY.BROWSEANDUPDATEORDERS
> 163 0uuqgjq7k12nf ORDERENTRY.NEWORDER
>
> (the count could be changed to %activity or average active sessions )
>
> Best
> Kyle Hailey
> http://oraclemonitor.com
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Jurijs
+371 29268222 (+2 GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
