[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
--//這樣就不會出現前面遇到的情況，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/228969.html
標籤：Oracle