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

From: <dominica_at_gmail.com>
Date: 2 May 2005 18:45:25 -0700
Message-ID: <1115084725.713064.277720@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 Received on Mon May 02 2005 - 20:45:25 CDT