[20201105]再分析sql语句.txt

[20201105]再分析sql语句.txt --//有时候工作需要再分析执行某个sql语句，一般常用的方法就是从共享池里面清除，刷新共享池，但是这样做代价太大。 --//还有一种方式使用 dbms_shared_pool.purge命令。我使用的脚本如下： $ cat flush_sql.sql DECLARE  name varchar2(100);  version varchar2(3); BEGIN  select regexp_replace(version,'\..*') into version from v$instance;  if version = '10' then  execute immediate  q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport  end if;  select address||','||hash_value into name from v$sqlarea where sql_id like '&1';  dbms_shared_pool.purge(name,'C',&2); END; / --//当然还有一些简单的方法就是grant或者comment注解表信息，这样可以导致执行时重新分析。 --//前一段时间看链接： https://martincarstenbach.wordpress.com/2020/10/26/enforcing-a-re-parse-of-a-cursor-in-autonomous-database-using-a-hammer/ --//作者提供了另外的方法，设置表noparallel，实际上根本没有改动，导致再次执行时重新分析。自己测试看看： 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试： SCOTT@book> select count(*) from dept ;   COUNT(*) ----------          4 SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3940382763 dh11gtgpduy1b            0  eadd782b SCOTT@book> select plan_hash_value, child_number, invalidations, executions, is_bind_aware, is_bind_sensitive from v$sql where sql_id = 'dh11gtgpduy1b'; PLAN_HASH_VALUE CHILD_NUMBER INVALIDATIONS EXECUTIONS I I --------------- ------------ ------------- ---------- - -      3051237957            0             0          1 N N SCOTT@book> select object_name, object_type, sysdate as now, last_ddl_time from   dba_objects where object_name = 'DEPT' and owner = user; OBJECT_NAME          OBJECT_TYPE         NOW                 LAST_DDL_TIME -------------------- ------------------- ------------------- ------------------- DEPT                 TABLE               2020-11-05 08:47:56 2020-03-25 10:08:55 SCOTT@book>  select degree from dba_tables where owner = user and table_name = 'DEPT'; DEGREE --------------------          1 SCOTT@book> alter table dept noparallel; Table altered. SCOTT@book>  select degree from dba_tables where owner = user and table_name = 'DEPT'; DEGREE --------------------          1 --//可以发现DEGREE没有变化。 SCOTT@book> select object_name, object_type, sysdate as now, last_ddl_time from   dba_objects where object_name = 'DEPT' and owner = user; OBJECT_NAME          OBJECT_TYPE         NOW                 LAST_DDL_TIME -------------------- ------------------- ------------------- ------------------- DEPT                 TABLE               2020-11-05 08:50:27 2020-11-05 08:49:08 --//LAST_DDL_TIME发生了变化。 SCOTT@book> select count(*) from dept ;   COUNT(*) ----------          4 SCOTT@book> select plan_hash_value, child_number, invalidations, executions, is_bind_aware, is_bind_sensitive from v$sql where sql_id = 'dh11gtgpduy1b'; PLAN_HASH_VALUE CHILD_NUMBER INVALIDATIONS EXECUTIONS I I --------------- ------------ ------------- ---------- - -      3051237957            0             1          1 N N --//可以发现重新分析sql语句。而且并没有生成新的子光标CHILD_NUMBER=0,作者最后的结论： Remember that a DML operation as the one shown in this post is a blunt weapon and only to be used as a last resort. --//请记住，DML操作，如本文所示，是一种钝器，仅作为最后手段使用。 --//实际上如果想想也许还能想出许多方法。 --//当然这样的方式导致只要涉及到这个表的sql语句都要重新分析。