When query pass the bind variable to the remote sub-query, the query will be parsed by local database optimizer and sub-query will be parsed/run in remote database. If the local DB and remote DB are in difference versions, then bind peeking behalves will be diffs.
When local database in 11G, as showed in above, when bind variable identified as “sensitive”, then execute plan would switching or re-gen even w/o statistics gathering happened for underline objects; however if DB is 10G, the plan only be switching/re-gen after statistics (exact to say, should dependency of plan) be gathering and “_optimizer_invalidation_period” is over.
When object’s statistics in local 11g database be updated, remote sub-query would be not triggered to switching the plan or generate new plan if the DB version on 10G; and sub-query would do plan switch and generate new version only for bind variable peeking with marked as “sensitive”.
When object’s statistics in remote database be updated, sub-query in remote DB would be triggered for plan switching /generate new version for 10G, and only generate new version of plan for 11G as descript in Part 1. If the parent plan in local DB is not depended to remote objects.
Below is the test case, repeated to run ‘6t5c5czfxq3g1′ and gathered statistic in local DB 11G, and only gathered statistics in remote DB 10G at frist time, monitor in remote DB. Bind data in local query showed NULL, and new version of execution plan would be generated after underline statistics be gathered in local 11G DB. In the remote 10G DB, even just gathered statistics only once, the execution plan would keep switch and final new version of plan be re-gen. Last round, query be called in 11G DB within “invalid interval” after statistics gathering, so no new version of plan in 11G be gathered, and in remote 10G DB, since no statistics be gathered, last execution plan be re-used.
============== before call in local ==============================
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='6t5c5czfxq3g1'; 2 3 SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I --------------------------- ---------------------------------------- - - - - 6t5c5czfxq3g1 3 3722120673 4132145321 0 0 Y 13-FEB-2013:17:18:10 2013-02-13/17:14:21 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 1 Y 13-FEB-2013:17:31:00 2013-02-13/17:30:58 N N N N 6t5c5czfxq3g1 4 3722120673 4132145321 0 2 Y 13-FEB-2013:17:38:18 2013-02-13/17:31:58 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 3 Y 13-FEB-2013:17:41:25 2013-02-13/17:41:25 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 4 Y 13-FEB-2013:17:41:53 2013-02-13/17:41:52 N N N N 6t5c5czfxq3g1 6 3722120673 4132145321 0 5 Y 13-FEB-2013:17:55:13 2013-02-13/17:41:58 N N N Y 6 rows selected. ================ after re-run 5 times ========================= SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='6t5c5czfxq3g1' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ---------------------------------- ---------------------------------- - - - - 6t5c5czfxq3g1 3 3722120673 4132145321 0 0 Y 13-FEB-2013:17:18:10 2013-02-13/17:14:21 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 1 Y 13-FEB-2013:17:31:00 2013-02-13/17:30:58 N N N N 6t5c5czfxq3g1 4 3722120673 4132145321 0 2 Y 13-FEB-2013:17:38:18 2013-02-13/17:31:58 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 3 Y 13-FEB-2013:17:41:25 2013-02-13/17:41:25 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 4 Y 13-FEB-2013:17:41:53 2013-02-13/17:41:52 N N N N 6t5c5czfxq3g1 6 3722120673 4132145321 0 5 Y 13-FEB-2013:17:55:13 2013-02-13/17:41:58 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 6 Y 13-FEB-2013:17:56:49 2013-02-13/17:56:49 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 7 Y 13-FEB-2013:17:59:28 2013-02-13/17:59:27 N N N N 6t5c5czfxq3g1 1 3722120673 4132145321 0 8 Y 13-FEB-2013:18:03:40 2013-02-13/18:03:40 N N N N 6t5c5czfxq3g1 2 3722120673 4132145321 0 9 Y 13-FEB-2013:18:08:41 2013-02-13/18:06:09 N N N Y 10 rows selected. SQL> ============== in DB 10G, remote site, before remote call ============================== SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE 2* from v$sql where sql_id='fva6vxg5vdn10' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ------------------------------------------------------------------------------- ----------------- - fva6vxg5vdn10 5 3417755680 3646009071 3214516708 0 N 13-FEB-2013:17:41:25 BEDA0A200300511B5D52000101F0018003691432372D4445432D323031323A31303A35323A3333 2013-02-13/17:14:21 N fva6vxg5vdn10 6 3417755680 3646009071 3214516708 1 N 13-FEB-2013:17:41:58 BEDA0A200300511B5D8E000101F0018003691432372D4445432D323031323A31303A35323A3333 2013-02-13/17:31:59 N fva6vxg5vdn10 1 3417755680 3646009071 3214516708 2 N 13-FEB-2013:17:43:07 BEDA0A200300511B602B000101F0018003691432362D4A414E2D323031333A31303A35323A3034 2013-02-13/17:43:07 N SQL> select distinct t.sql_text SQL_TEXT, t.PLAN_HASH_VALUE, b.name BIND_NAME, b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED 2 from v$sql t join v$sql_bind_capture b using (sql_id) 3 where b.value_string is not null and sql_id='fva6vxg5vdn10'; SQL_TEXT -------------------------------------------------------------------------------------------------------------PLAN_HASH_VALUE BIND_NAME BIND_STRING LAST_CAPTURED WAS --------------- ----------------- -------------------------- -------------------- --- SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 27-DEC-2012:10:52:33 13-FEB-2013:17:30:58 YES SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 26-JAN-2013:10:52:04 13-FEB-2013:17:43:07 YES SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 27-DEC-2012:10:52:33 13-FEB-2013:17:31:58 YES ============== after remote call 5 times =================== SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE 2* from v$sql where sql_id='fva6vxg5vdn10' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ------------------------------------------------------------------------------- ----------------- - fva6vxg5vdn10 6 3417755680 3646009071 3214516708 0 N 13-FEB-2013:17:56:49 BEDA0A200300511B635E000101F0018003691432312D5345502D323031323A30303A30303A3030 2013-02-13/17:14:21 N fva6vxg5vdn10 7 3417755680 3646009071 3214516708 1 N 13-FEB-2013:17:59:25 BEDA0A200300511B63FD000101F0018003691432312D5345502D323031323A30303A30303A3030 2013-02-13/17:31:59 N fva6vxg5vdn10 6 3417755680 3646009071 3214516708 2 N 13-FEB-2013:18:03:40 BEDA0A200300511B64F9000101F0018003691432312D5345502D323031323A30303A30303A3030 2013-02-13/17:43:07 N fva6vxg5vdn10 2 3417755680 3646009071 3214516708 3 N 13-FEB-2013:18:08:40 BEDA0A200300511B658F000101F0018003691432312D5345502D323031323A30303A30303A3030 2013-02-13/18:06:09 N SQL> select distinct t.sql_text SQL_TEXT, t.PLAN_HASH_VALUE, b.name BIND_NAME, b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED 2 from v$sql t join v$sql_bind_capture b using (sql_id) 3 where b.value_string is not null and sql_id='fva6vxg5vdn10'; SQL_TEXT -------------------------------------------------------------------------------------------------------------PLAN_HASH_VALUE BIND_NAME BIND_STRING LAST_CAPTURED WAS --------------- ----------------- -------------------------- -------------------- --- SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 21-SEP-2012:00:00:00 13-FEB-2013:18:06:07 YES SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 21-SEP-2012:00:00:00 13-FEB-2013:18:03:37 YES SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 21-SEP-2012:00:00:00 13-FEB-2013:17:56:46 YES SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE 3214516708 :VDATE 21-SEP-2012:00:00:00 13-FEB-2013:17:59:25 YES
We did the same test in query in DB 10G and remote DB is 11G, the remote sub-query be created as “sensitive”. At this round, we just changed the bind variable value w/o gathering statistics on the local object to invalid the execution plan(in 10G, the new version of plan only generated after all the cached plan be re-tried). Test case showed, remote 11G had same behalves as local query with bind peeking “sensitive”, and two plan for sub-query final on re-useable status and severed for bind variable changed in local 10G DB. In 10G DB since, since no any action to invalidate the execution plan, it just re-used one version of execution plan.
In Local 10G DB :
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE 2 from v$sql where sql_id='0v188ja00rw4a'; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ------------------------- ----------------- - 0v188ja00rw4a 8 2148266122 3380594699 0 0 Y 14-FEB-2013:18:28:01 2013-02-14/18:12:36 N
In remote 11G DB, two shareable plan for this sub-query with two bind values.
SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='1sup3wx6g52r8' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:25:21 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N N 1sup3wx6g52r8 3 1290963688 258167858 46243151 1 N 14-feb-2013:18:27:46 BEDA0B200100511CBB91000101C002160180 2013-02-14/18:25:22 N Y Y Y 1sup3wx6g52r8 2 1290963688 258167858 599033881 2 N 14-feb-2013:18:28:02 BEDA0B200100511CBBD7000101C0021602C102 2013-02-14/18:26:31 N Y Y Y
When the query and remote sub query have same bind variable, “peeking sensitive” may has diffs in local and remote database.
Appendix — Test case on Local ORACLE 10G and remote 11g with Bind variable peeking
---- In local 10G, SQL> r 1* select max(object_id) from object_list@tio2 where type_id = :vtype MAX(OBJECT_ID) -------------- 152388 ---- In remote 11G DB SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='1sup3wx6g52r8' 2 3 ; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:20:23 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N Y ---- In local 10G SQL> exec :vtype := 0; PL/SQL procedure successfully completed. SQL> select max(object_id) from object_list@tio2 where type_id = :vtype; MAX(OBJECT_ID) -------------- 152389 ---- in remote 11G SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='1sup3wx6g52r8' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:25:21 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N Y 1sup3wx6g52r8 1 1290963688 258167858 46243151 1 N 14-feb-2013:18:25:21 BEDA0B200100511CBB91000101C002160180 2013-02-14/18:25:22 N Y Y Y --- in local 10G SQL> exec :vtype := 1; PL/SQL procedure successfully completed. SQL> r 1* select max(object_id) from object_list@tio2 where type_id = :vtype MAX(OBJECT_ID) -------------- 152388 --- in remote 11g, and gather statistics SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='1sup3wx6g52r8' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:25:21 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N N 1sup3wx6g52r8 1 1290963688 258167858 46243151 1 N 14-feb-2013:18:26:31 BEDA0B200100511CBB91000101C002160180 2013-02-14/18:25:22 N Y Y Y 1sup3wx6g52r8 1 1290963688 258167858 599033881 2 N 14-feb-2013:18:26:31 BEDA0B200100511CBBD7000101C0021602C102 2013-02-14/18:26:31 N Y Y Y --- in local 10G SQL> exec :vtype := 0; PL/SQL procedure successfully completed. SQL> r 1* select max(object_id) from object_list@tio2 where type_id = :vtype MAX(OBJECT_ID) -------------- 152389 --- in remote 11G SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='1sup3wx6g52r8' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:25:21 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N N 1sup3wx6g52r8 2 1290963688 258167858 46243151 1 N 14-feb-2013:18:27:14 BEDA0B200100511CBB91000101C002160180 2013-02-14/18:25:22 N Y Y Y 1sup3wx6g52r8 1 1290963688 258167858 599033881 2 N 14-feb-2013:18:27:14 BEDA0B200100511CBBD7000101C0021602C102 2013-02-14/18:26:31 N Y Y Y --- in local 10G SQL> r 1* select max(object_id) from object_list@tio2 where type_id = :vtype MAX(OBJECT_ID) -------------- 152389 --- in remote 11g SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='1sup3wx6g52r8' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:25:21 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N N 1sup3wx6g52r8 3 1290963688 258167858 46243151 1 N 14-feb-2013:18:27:46 BEDA0B200100511CBB91000101C002160180 2013-02-14/18:25:22 N Y Y Y 1sup3wx6g52r8 1 1290963688 258167858 599033881 2 N 14-feb-2013:18:27:46 BEDA0B200100511CBBD7000101C0021602C102 2013-02-14/18:26:31 N Y Y Y --- in local 10G SQL> exec :vtype := 1; PL/SQL procedure successfully completed. SQL> r 1* select max(object_id) from object_list@tio2 where type_id = :vtype MAX(OBJECT_ID) -------------- 152388 ------ in remote 11G SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, 2 LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE 3* from v$sql where sql_id='1sup3wx6g52r8' SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I I I I ------------------------------------- ----------------------- - - - - 1sup3wx6g52r8 3 1290963688 258167858 599033881 0 N 14-feb-2013:18:25:21 BEDA0B200100511CB894000101C0021602C102 2013-02-14/18:12:36 N Y N N 1sup3wx6g52r8 3 1290963688 258167858 46243151 1 N 14-feb-2013:18:27:46 BEDA0B200100511CBB91000101C002160180 2013-02-14/18:25:22 N Y Y Y 1sup3wx6g52r8 2 1290963688 258167858 599033881 2 N 14-feb-2013:18:28:02 BEDA0B200100511CBBD7000101C0021602C102 2013-02-14/18:26:31 N Y Y Y ---- check the SQL plan version in local 10G SQL> select * from v$sql_bind_capture where sql_id='0v188ja00rw4a'; no rows selected SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE 2 from v$sql where sql_id='0v188ja00rw4a'; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ------------------------- ----------------- - 0v188ja00rw4a 8 2148266122 3380594699 0 0 Y 14-FEB-2013:18:28:01 2013-02-14/18:12:36 N
Fred's ORACLE DBA Works 