执行计划和SQL解释过程.docVIP

绑定变量SQL执行计划 当使用绑定变量时, oracle使用bind peeking机制，ORACLE对某SQL使用的执行计划是基于首次执行该SQL时变量的实际值来确定其执行计划,随后执行该SQL，不管变量取什么值都使用该执行计划。除非因shared pool的LRU算法SQL被换出去,下次执行时进行硬解释,这就有可能导致某SQL的执行计划发生变化。 对于使用变量取不同值执行计划不同的情况，建议不要使用绑定变量；视SQL执行的频度，加大shared_pools的值可以某种程度上减少SQL被换出的可能。 可以通过隐含的参数来调整数据库默认的bind peeking行为：_OPTIM_PEEK_USER_BINDS。 如果我们想关闭Bind Variable Peeking，我们可以设置该参数为 False 即可。 SQLalter session set _optim_peek_user_binds=false 柱状图也被用于含有绑定变量的sql和开启cursor_sharing的sql。在这些情况下，优化器判定列的值是否能影响执行计划，如果能的话，它会使用某个字面值来代替该绑定变量，并执行一个硬分析。因此，对那些取值分布非常倾斜的列,在表分析时一定要收集柱状图。 SQL var a number;SQL exec :a:=10;SQL select * from test where no=:a;SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ADVANCED));? 改变变量赋值：SQL exec :a:=20;SQL select * from test where no=:a;SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ADVANCED));? 可以看到变量值改变后，Peeked Binds (identified by position)不变 清除缓存的执行计划 1、alter system flush shared_pool; 2、清除某条SQL，可用dbms_shared_pool； 先从v$sqlarea查出该SQL的address, hash_value： select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where lower(sql_text) like %dual%; 清除该SQL的缓存 exec sys.dbms_shared_pool.purge(ADDRESS, HASH_VALUE,C); 3、修改表的索引、新建索引等； 4、dbms_stats收集统计信息时候设置no_invalidate参数； 查看系统中SQL真正执行的执行计划 请注意，在有绑定变量的情况下，sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。由于shared_pools中缓存了执行计划，explain plan 命令得到的执行计划并不一定是数据库缓存的执行计划（主要是变量列取值分布非常倾斜的情况下），应该通过10053 event 或v$sql_plan查看SQL当前在系统中的真实执行计划。 select * from v$sql_plan where sql_id=5zuv91v2y8h7z 查看sql 执行计划的历史变更? 使用如下sql 可以发现某个sql的执行计划什么时候发生了变化！ select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,yyyymmdd hh24:mi:ss) ?TIMESTAMP from dba_hist_sql_plan? where SQL_ID=68wnxdjxwwn2h order by TIMESTAMP; 查看出来执行计划的变化之后 可以使用如下sql查看发生了那些变化！ col options for a15 col operation for a20 col object_name for a20 select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,yyyymmdd hh24:mi:ss) ? ? from DBA_HIST_SQL_PLAN ? ? ? where s