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

通过上面的例子可以看出，使用最频繁的情况(变量类型改变,变量长度改变,优化器设置改变等)均会导致重复的解析和新游标产生，但复杂且非常长的SQL在系统中是司空见惯的，如果才能避免或减少重复硬解析和资源的使用，又在一定程度上保护执行计划呢?
10g以前有outline，但使用受限；10g及以后有sql profile；让我们以第一次解析来创建SQL profile，看会发生什么；

SQL> @sqlprofile/create_sql_profile.sql '1dmmz4yh0hrzx' 0 Enter value for sql_id: 1dmmz4yh0hrzx Enter value for child_no (0): Enter value for profile_name (PROF_sqlid_planhash): Enter value for category (DEFAULT): Enter value for force_matching (FALSE): SQL> alter system flush shared_pool;

创建好SQL profile后清空共享池，然后再重新运行上面的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 1010 22 0 20.996 24.27 1dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 3 2.783 1dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 2 2.473 select t.ADDRESS,t.CHILD_ADDRESS,child_number,t.BIND_MISMATCH,t.OPTIMIZER_MODE_MISMATCH,t.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor t where sql_id='1dmmz4yh0hrzx'; ADDRESS CHILD_ADDRESS CHILD_NUMBER BI OP BI ---------------- ---------------- ------------ -- -- -- 0000000069AC2D28 0000000062F19D70 0 N N N 0000000069AC2D28 00000000696F7E48 1 Y N N 0000000069AC2D28 000000006A3E05A8 2 Y N Y

仅产生2个子游标，一次因为变量类型改变了，一次为变量类型和变量值长度改变了；优化器环境改变并没有影响到优化器；再继续查询优化器的行为；

SQL> @sql 2684903421 Show SQL text, child cursors and execution stats for SQL hash value 2684903421 child 0 HASH_VALUE CH# PLAN_HASH SQL_TEXT FIRST_LOAD_TIME LAST_LOAD_TIME SQL_PROFILE ---------- ----- ---------- -------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------------------ 2684903421 0 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765 2684903421 1 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765 2684903421 2 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765 3 rows selected. CH# PARENT_HANDLE OBJECT_HANDLE PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED LIOS PIOS SORTS CPU_MS ELA_MS USERS_EXECUTING ----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------- 0 0000000069AC2D28 0000000062F19D70 3 7 2 2 0 1010 22 0 20.996 24.27 0 1 0000000069AC2D28 00000000696F7E48 2 7 2 2 0 2 0 0 3 2.783 0 2 0000000069AC2D28 000000006A3E05A8 0 7 2 2 0 4 0 0 2 2.473 0

三个游标均使用了同样的SQL Profile，执行计划因SQL Profile而受到保护。