Oracle SQL 硬解析和子游标(2)

这样就会在v$sqlarea,v$sql,v$sql_shared_cursor产生如下的记录；

SQL> select sql_text,hash_value,sharable_mem,buffer_gets,loads,fetches,executions,optimizer_mode,PARSING_SCHEMA_NAME from v$sqlarea where sql_id='5b42g2fkrrzss'; SQL_TEXT HASH_VALUE SHARABLE_MEM BUFFER_GETS LOADS FETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME -------------------- ---------- ------------ ----------- ---------- ---------- ---------- -------------------- ------------------------------------------------------------ select * from tab01 2776366872 85836 220 2 2 2 ALL_ROWS USERB SQL> select t.CHILD_NUMBER,sql_text,hash_value,sharable_mem,buffer_gets,loads,fetches,executions,optimizer_mode,t.PARSING_SCHEMA_NAME from v$sql t where sql_id='5b42g2fkrrzss'; CHILD_NUMBER SQL_TEXT HASH_VALUE SHARABLE_MEM BUFFER_GETS LOADS FETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME ------------ -------------------- ---------- ------------ ----------- ---------- ---------- ---------- -------------------- ------------------------------------------------------------ 0 select * from tab01 2776366872 44868 110 1 1 1 ALL_ROWS USERA 1 select * from tab01 2776366872 44868 110 1 1 1 ALL_ROWS USERB SQL> select child_number,t.AUTH_CHECK_MISMATCH,t.TRANSLATION_MISMATCH from v$sql_shared_cursor t where sql_id='5b42g2fkrrzss'; CHILD_NUMBER AU TR ------------ -- -- 0 N N 1 Y Y

v$sqlarea中记录父游标，统计所有包括子游标的数据(buffer_gets,loads,fetches,executions)，PARSING_SCHEMA_NAME记录最后一次解析的用户；

v$sql中记录所有子游标，游标号码从0开始递增，每个游标记录自身的统计信息，这里需要注意，对于非长事务而言，oracle在运行完成后更新统计信息；但对于长事务，oracle每5秒钟更新一次统计信息；

v$sql_shared_cursor 中记录为什么子游标没有使用共享池里存在的游标而重新解析原因；上面的例子导致硬解析和产生子游标的原因是授权检查(AUTH_CHECK_MISMATCH)和对象检查(TRANSLATION_MISMATCH)失败；

其它还有非常多的原因导致硬解析和产生子游标，接下来会讨论一些日常开发中容易导致的原因；

create table tparse( x number primary key, y varchar2(30) ); begin dbms_stats.set_table_stats ( user,'tparse', numrows=>10000000, numblks=>100000 ); end; / begin dbms_stats.set_index_stats ( user,'SYS_C0013113', numrows=>10000000 ); end; /

这里创建了tparse表，然后虚拟设置了表和索引的统计信息;接着在pl/sql里用不同的优化器环境和不同的条件下执行SQL；

declare l_num_x number; l_var_x varchar2(30); l_var_x1 varchar2(300); begin execute immediate 'alter session set optimizer_mode=all_rows'; for i in (select * from tparse where x>l_num_x)loop null; end loop; for i in (select * from tparse where x>l_var_x)loop null; end loop; execute immediate 'alter session set optimizer_mode=first_rows_10'; for i in (select * from tparse where x>l_num_x)loop null; end loop; for i in (select * from tparse where x>l_var_x)loop null; end loop; for i in (select * from tparse where x>l_var_x1)loop null; end loop; end; /

成功执行pl/sql后，检查v$sql表；

col SQL_TEXT for a50 select sql_id,CHILD_NUMBER,hash_value,SQL_TEXT , buffer_gets LIOS, disk_reads PIOS, sorts, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms from v$sql where sql_text like 'SELECT %TPARSE WHERE X%' order by CHILD_NUMBER ; SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOS SORTS CPU_MS ELA_MS -------------------------- ------------ ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- 1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 26 3 0 2 1.733 1dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 1.998 1.331 1dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 2 1.673 1dmmz4yh0hrzx 3 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 2.999 3.286 1dmmz4yh0hrzx 4 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 1 .783

这里产生了5条记录，sql_id,hash_value都相同，但是它们有不同之处；

第一次解析，optimizer_mode值为all_rows；谓语条件的值类型与主键值类型相同，此时共享池里没有匹配的已经共享的游标，oracle硬解析并共享游标；

第二次解析，optimizer_mode值为all_rows，谓语条件的值为类型为varchar，与主键值类型不相同；优化器隐形转换值类型,然后对比第一次共享的游标时因为值变量类型不同，所以硬解析和产生新游标;

第三次解析，optimizer_mode值为first_rows；谓语条件的值类型与主键值类型相同，优化器在对比第一次共享的游标时发现环境不一致，所以硬解析和产生新游标;