Incase of sqlid having multiple child cursors, any option available there to flush a particular "child cursor" ?
demo@ORA11G> create table t as
2 select a.*, decode(rownum,1,1,99) id
3 from all_objects a;
Table created.
demo@ORA11G> create index t_idx on t(id);
Index created.
demo@ORA11G> begin
2 dbms_stats.gather_table_stats(user,'T',
3 estimate_percent=>100,
4 method_opt=>'for all indexed columns size 2',
5 cascade=>true);
6 end;
7 /
PL/SQL procedure successfully completed.
demo@ORA11G> variable x number
demo@ORA11G> exec :x := 1;
PL/SQL procedure successfully completed.
demo@ORA11G> select max(object_id) from t where id=:x;
MAX(OBJECT_ID)
--------------
20
1 row selected.
demo@ORA11G> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3ggtvprg4js0q, child number 0
-------------------------------------
select max(object_id) from t where id=:x
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:X)
20 rows selected.
demo@ORA11G> exec :x := 99;
PL/SQL procedure successfully completed.
demo@ORA11G> select max(object_id) from t where id=:x;
MAX(OBJECT_ID)
--------------
107246
1 row selected.
demo@ORA11G> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3ggtvprg4js0q, child number 0
-------------------------------------
select max(object_id) from t where id=:x
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:X)
20 rows selected.
demo@ORA11G> select max(object_id) from t where id=:x;
MAX(OBJECT_ID)
--------------
107246
1 row selected.
demo@ORA11G> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3ggtvprg4js0q, child number 1
-------------------------------------
select max(object_id) from t where id=:x
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 667 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| T | 84858 | 1325K| 667 (4)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:X)
19 rows selected.
demo@ORA11G> select address,hash_value from v$sqlarea where sql_id='3ggtvprg4js0q';
ADDRESS HASH_VALUE
---------------- ----------
000007FF0A7BCE50 3729317910
1 row selected.
demo@ORA11G> exec sys.dbms_shared_pool.purge(name=>'000007FF0A7BCE50,3729317910',flag=>'c');
PL/SQL procedure successfully completed.
demo@ORA11G> select address,hash_value from v$sqlarea where sql_id='3ggtvprg4js0q';
no rows selected
demo@ORA11G>