[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语句都要重新分析。