ITPub博客

首页 > 数据库 > Oracle > [20201126]11g VPD的问题.txt

[20201126]11g VPD的问题.txt

原创 Oracle 作者:lfree 时间:2020-11-26 09:54:46 0 删除 编辑

[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
--//这样就不会出现前面遇到的情况。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2737068/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2814
  • 访问量
    6614743