重庆思庄Oracle、Redhat认证学习论坛
标题: 12C Temporal Validity （TV）功能探疑 [打印本页]
作者: windjack 时间: 2020-7-29 23:12
标题: 12C Temporal Validity （TV）功能探疑
本帖最后由 windjack 于 2020-7-29 23:14 编辑
12C Temporal Validity （TV）功能探疑
环境：
linux centos 7.8 x86-64
oracle 12.1.0.2 64bit
-----------------------------------------------------------------------------------------------------------------
TV简介：
时间有效期管理（Temporal Validity）以下简称 TV，TV 的功能大致上可以这样描述：
在表中手动或者自动建两个时间类型的字段，一个表示有效期的开始时间，一个表示有效期的结束时间，
就可以通过设置让只有在有效期内的记录才会被选择出来。
--相关实验如下 ：
SQL> conn / as sysdba
Connected.
SQL> create table emp(empno number,salary number,user_start_time date,user_end_time date,period for user_time (user_start_time,user_end_time));
Table created.
SQL> insert into emp values(100,2500,to_date('2020-07-29 22:05:03','yyyy-mm-dd hh24:mi:ss'),to_date('2020-07-29 22:07:03','yyyy-mm-dd hh24:mi:ss'));
1 row created.
SQL> insert into emp values(100,2500,to_date('2020-07-29 22:10:27','yyyy-mm-dd hh24:mi:ss'),to_date('2020-07-29 22:12:27','yyyy-mm-dd hh24:mi:ss'));
1 row created.
SQL> insert into emp values(100,2500,to_date('2020-07-29 22:05:03','yyyy-mm-dd hh24:mi:ss'),to_date('2020-07-29 22:12:27','yyyy-mm-dd hh24:mi:ss'));
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col column_name for a30
SQL> select column_name,hidden_column from user_tab_cols where table_name='EMP';
COLUMN_NAME HIDDEN_COLUMN
------------------------------ ------------------------
USER_TIME YES --自动增加的一个隐藏的用于时间验证的字段
EMPNO NO
SALARY NO
USER_START_TIME NO
USER_END_TIME NO
--未启用时间验证，查询表，有3条数据如下：
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
200 4700 2020-07-29 22:10:27 2020-07-29 22:12:27
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
--启用会话时间有效性设置：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('ASOF',(to_date('2020-07-29 22:11:01','yyyy-mm-dd hh24:mi:ss')));
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
200 4700 2020-07-29 22:10:27 2020-07-29 22:12:27
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
--结果异常，100这条记录不符合要求
SQL> exec dbms_flashback_Archive.enable_at_valid_time('ASOF',(to_date('2020-07-29 22:07:01','yyyy-mm-dd hh24:mi:ss')));
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
200 4700 2020-07-29 22:10:27 2020-07-29 22:12:27
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
-- 结果异常，200这条记录不符要求。
清除共享池：
SQL> alter system flush shared_pool;
System altered.
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
结果正确。
--查看执行计划
SQL> set autotrace on
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 88 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
22:07:01.000000000'))
--ORACLE会自动加上filter
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--修改时间验证，显示所有记录：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('all');
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
结果不正确。
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 88 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
22:07:01.000000000'))
--此处执行计划过滤器异常，ORACLE错误的进行了SQL执行计划重用。
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--清除共享池
SQL> alter system flush shared_pool;
System altered.
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 88 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
22:07:01.000000000'))
--执行计划未变
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--修改时间验证：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('current');
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
--结果不正确
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 88 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
22:07:01.000000000'))
--执行计划未变
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--修改时间验证：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('ASOF',(to_date('2020-07-29 23:07:01','yyyy-mm-dd hh24:mi:ss')));
PL/SQL procedure successfully completed.
SQL> select * from emp;
no rows selected
--结果正常
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 44 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
23:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
23:07:01.000000000'))
--执行计划更新了
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--直接使用as of period for user_time来查询
SQL> select * from emp as of period for user_time to_date('2020-07-29 23:07:01','yyyy-mm-dd hh24:mi:ss');
no rows selected
--结果准确
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 44 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
"T"."USER_START_TIME"<=TO_DATE(' 2020-07-29 23:07:01', 'syyyy-mm-d
d
hh24:mi:ss')) AND ("T"."USER_END_TIME" IS NULL OR
"T"."USER_END_TIME">TO_DATE(' 2020-07-29 23:07:01', 'syyyy-mm-dd
hh24:mi:ss')))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--再次使用as of period for user_time来查询
SQL> select * from emp as of period for user_time to_date('2020-07-29 22:07:01','yyyy-mm-dd hh24:mi:ss');
EMPNO SALARY USER_START_TIME USER_END_TIME
---------- ---------- ------------------- -------------------
100 2500 2020-07-29 22:05:03 2020-07-29 22:07:03
300 5500 2020-07-29 22:05:03 2020-07-29 22:12:27
--结果准确
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 88 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T"."USER_START_TIME" IS NULL OR
"T"."USER_START_TIME"<=TO_DATE(' 2020-07-29 22:07:01', 'syyyy-mm-d
d
hh24:mi:ss')) AND ("T"."USER_END_TIME" IS NULL OR
"T"."USER_END_TIME">TO_DATE(' 2020-07-29 22:07:01', 'syyyy-mm-dd
hh24:mi:ss')))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
总结：
TV在12.1.0.2版本中使用会话来启用时间有效性验证时非常不稳定，错误的进行cursor执行计划重用，导致结果不准。
all 和current两个参数更不靠谱。
建议大家使用as of period for user_time来进行查询，但网上有人说即使这样用也会出现结果不稳定的现象。
另外，从执行计划中也可以看出来，TV功能使用了filter来处理数据。如果原表有100百条数据，满足要求的只有一条，
ORACLE也会先扫描100万条，再过滤掉999999万条，性能堪忧！
总之，12.1.0.2中 TV功能慎用。
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |