SQL> select sum(sal) from t;
SUM(SAL)
----------
8750
/***************相同的查询由于不同的运行环境导致产生了不同的子游标，optimizer_env_hash_value值不同**************/
/***************不同的子游标有不同的child_address 值 ****************************/
SQL> select sql_id, child_number, sql_text,optimizer_env_hash_value oehv,child_address
2 from v$sql where sql_text like '%sum(sal)%' and sql_text not like '%from v$sql%';
SQL_ID CHILD_NUMBER SQL_TEXT OEHV CHILD_ADDRESS
------------- ------------ ---------------------------------------- ---------- ----------------
gu68ka2qzx3hh 0 select sum(sal) from t 3620536549 0000000093696D00
gu68ka2qzx3hh 1 select sum(sal) from t 2687219005 0000000093767F58
/********** 查询v$sql_shared_cursor可以跟踪是那些变化导致了子游标不能共享,此例为optimizer_mismatch *****************/
SQL> SELECT child_number, optimizer_mismatch
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '&sql_id';
Enter value for sql_id: gu68ka2qzx3hh
old 3: WHERE sql_id = '&sql_id'
new 3: WHERE sql_id = 'gu68ka2qzx3hh'
|