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