父游标、子游标及共享游标 - 数据库编程

/************上面3个父游标对应的子游标可以在v$sql中获得*******************/
SQL> select sql_id,hash_value,child_number,plan_hash_value,sql_text,executions from v$sql
2 where sql_text like '%empno=7782%' and sql_text not like '%from v$sql%';

SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT EXECUTIONS
------------- ---------- ------------ --------------- ---------------------------------------- ----------
4rs2136z084y1 3187938241 0 1601196873 SELECT * from t where empno=7782 1
84w067b4n91h5 3376711173 0 1601196873 SELECT * FROM t WHERE empno=7782 1
2r6rbdp92kyh9 1378449929 0 1601196873 select * from t where empno=7782 2

/******************调整optimizer_index_caching 参数并执行聚合查询************************/

SQL> alter session set optimizer_index_caching=40;

Session altered.

SQL> select sum(sal) from t;

SUM(SAL)
----------
8750

SQL> alter session set optimizer_index_caching=100;

Session altered.

父游标、子游标及共享游标(五)