/************上面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. |