[20201126]11g VPD的问题.txt

[20201126]11g VPD的问题.txt --//链接https://hourim.wordpress.com/2020/09/30/ddl-optimization-and-vpd/提到的问题在测试环境测试看看。 --//你可以查看中文版本更加详细的介绍10g，11g与12c增加列的一些操作方式上的变化： --//https://www.oracle.com/technetwork/cn/articles/database/ddl-optimizaton-in-odb12c-2331068-zhs.html --//我仅仅测试11g环境。 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> create table t1 as select rownum n1, lpad('x',5) v1 from dual connect by level <=5; Table created. --//分析表略。 SCOTT@book>  alter table t1 add c1 number default 42 not null; Table altered. SCOTT@book> alter session set statistics_level = all; Session altered. SCOTT@book> select count(1) from t1 where c1=42;   COUNT(1) ----------          5 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  a4v8hg2qxzp1g, child number 0 ------------------------------------- select count(1) from t1 where c1=42 Plan hash value: 3724264953 --------------------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 | |   1 |  SORT AGGREGATE    |      |      1 |      1 |    13 |            |          |      1 |00:00:00.01 |       3 | |*  2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    65 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter(NVL("C1",42)=42) --//注意filter条件。是NVL("C1",42)=42。因为这个字段并不保存在数据段中。 3.建立VPD policy： SCOTT@book> select sys_context('USERENV','LANG') from dual; SYS_CONTEXT('USERENV','LANG') ----------------------------- US create or replace function     f_t1_policy(piv_schema in varchar2                ,piv_object in varchar2) return varchar2 is   lv_return_value varchar2(4000); begin   if sys_context('USERENV','LANG') = 'US'   then     lv_return_value := '1=1';   else     lv_return_value := '1=0';   end if;    return lv_return_value; end f_t1_policy; / --//也就是测试返回 lv_return_value := '1=1'; -- assign this policy to t1 table begin  dbms_rls.add_policy    (object_schema    => user,     object_name      => 'T1',        policy_name      => 'F_T1_POLICY',        function_schema    => user,        policy_function  => 'F_T1_POLICY',        statement_types  => 'SELECT'     ); end; / SCOTT@book> alter table t1 add c2 number default 43 not null; Table altered. SCOTT@book> select count(1) from t1 where c2=43;   COUNT(1) ----------          5 --//嗯，我的测试是ok的。 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  6vk08skyq9v43, child number 0 ------------------------------------- select count(1) from t1 where c2=43 Plan hash value: 3724264953 --------------------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       2 | |   1 |  SORT AGGREGATE    |      |      1 |      1 |    12 |            |          |      1 |00:00:00.01 |       2 | |*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |    12 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       2 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$F5BB74E1    2 - SEL$F5BB74E1 / T1@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("C2"=43) --//注意过滤条件"C2"=43。这样就奇怪了，明明过滤条件是C2=43,按照前面的测试如果数据不在段中，应该count(1)是0才对啊。 4.继续探究： SCOTT@book> select rowid,t1.* from t1 where rownum=1; ROWID                      N1 V1            C1         C2 ------------------ ---------- ----- ---------- ---------- AAAXJRAAEAAAAILAAA          1     x         42         43 SCOTT@book> @ rowid AAAXJRAAEAAAAILAAA     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      94801          4        523          0  0x100020B           4,523                alter system dump datafile 4 block 523 ; --//通过bbed观察,注意执行一次刷新数据缓存，不然看到的可能不真实。 SCOTT@book> alter system flush buffer_cache; System altered. BBED> x  /rncnn dba  4,523 *kdbr[1] rowdata[48]                                 @8096 ----------- flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8097: 0x02 cols@8098:    4 col    0[2] @8099: 2 col    1[5] @8102:     x col    2[0] @8108: *NULL* col    3[2] @8109: 43 --//噢，注意看第4个字段通过类似的方式加入表中，而rls的存在改变的操作模式直接插入到数据段中。 SCOTT@book> column BINARYDEFVAL format a20 SCOTT@book> select * from sys.ecol$;    TABOBJ#     COLNUM BINARYDEFVAL ---------- ---------- --------------------      94801          3 C12B --//启用rls后，在数据字段sys.ecol$，仅仅出现字段3. --//注42的oracle数字编码就是C12B。 SCOTT@book> select dump(42,16),dump(43,16) from dual ; DUMP(42,16)        DUMP(43,16) ------------------ ------------------ Typ=2 Len=2: c1,2b Typ=2 Len=2: c1,2c --//也就是这样操作模式可能会导致表产生大量的redo，甚至影响前台的操作，在工作中要引起注意。 --//作者的测试在19c上视乎遇到了bug，不过对方还提到sys用户的一些情况我也测试看看。 SYS@book> show user USER is "SYS" SYS@book> alter table scott.t1 add c3 number default 44 not null; Table altered. SYS@book> select count(1) from scott.t1 where c3=44;   COUNT(1) ----------          5 --//OK,正确。 SYS@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  92qghqsahftp5, child number 0 ------------------------------------- select count(1) from scott.t1 where c3=44 Plan hash value: 3724264953 ---------------------------------------------------------------------------- | Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |        |       |     3 (100)|          | |   1 |  SORT AGGREGATE    |      |      1 |    12 |            |          | |*  2 |   TABLE ACCESS FULL| T1   |      1 |    12 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter(NVL("C3",44)=44) --//注意看过滤条件。现在是filter(NVL("C3",44)=44)。 SCOTT@book> select * from sys.ecol$;    TABOBJ#     COLNUM BINARYDEFVAL ---------- ---------- --------------------      94801          3 C12B      94801          5 C12D --//也就是以sys用户操作缺省值记录在sys.ecol$中，绕过了这个问题。 SCOTT@book> @ desc_proc sys dbms_rls DROP_POLICY INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME         OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT    DEFAULTED ---------- -------------------- ----------- -------- ------------- --------- --------- ---------- SYS        DBMS_RLS             DROP_POLICY        3 POLICY_NAME   VARCHAR2  IN        N                                                    1 OBJECT_SCHEMA VARCHAR2  IN        Y                                                    2 OBJECT_NAME   VARCHAR2  IN        N SCOTT@book> exec    dbms_rls.drop_policy (object_schema=> user,object_name=> 'T1',  policy_name=> 'F_T1_POLICY'); PL/SQL procedure successfully completed.                                                                          SCOTT@book> alter table t1 add c4 number default 45 not null; Table altered. SCOTT@book> select * from sys.ecol$;    TABOBJ#     COLNUM BINARYDEFVAL ---------- ---------- --------------------      94801          6 C12E      94801          3 C12B      94801          5 C12D --//这样就不会出现前面遇到的情况。