Re: how to find the SQL statement in V$SQLAREA with bind variable

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Tue, 03 May 2005 02:17:10 GMT
Message-ID: <Xns964AC430BE616SunnySD@68.6.19.6>

"dominica_at_gmail.com" <dominica_at_gmail.com> wrote in news:1115084725.713064.277720_at_f14g2000cwb.googlegroups.com:

> Hi All,
>
>
> I have this task.
>
> Basically, I have find a bunch of frequently RUN SQL statements in the
> database. (later on to run explain plan on them).
>
> I am running statspak and I do know some sql statements that run a lot
> in the database.
>
> My problem is I could find the HASH_VALUE, I could go to
> V$SQL or V$SQLAREA to find the SQL STATEMENT, but the
> SQL statement I find has BIND variables.
>
> Now, my question is how to find those BIND varaible related to
> that particular SQL statements.
>
> SELECT ABC.CONDITION, DDD.S_ADDED_P
> FROM SEG_RULE_SQL ABC,
> SEG_RULE DDD
> WHERE DDD.SEGMENT_ID = :b1
> AND DDD.RULE_ID = ABC.RULE_ID
> AND ABC.QUERY_TYPE = :b2
> AND DDD.SEGMENT_ID != :b3
> ORDER BY DECODE(S_ADDED_P,'t',0,1),DDD.DISPLAY_ORDER
>

>>From which V$SQL???

>
> Otherwise, could I "EXPLAIN PLAN" with a SQL statement with BIND
> VARIABLE. Because I have to RECORD the plan in 8.1.7.4 database,
> and then run a test upgrade to oracle 10g and then
> explain plan (only, not actually run the sql statement) and
> record the plan path in the 10g database.
>
>
>
>
> Thanks
>
> Dominica
>
>

Do some research on EVENT 10046 with a value of 12; Received on Mon May 02 2005 - 21:17:10 CDT