|
ORACLE 时间有效性测试
----------------------------------------------------------------------------------------------------
DB VERSION:12.1.0.2
OS VERSION:Centos 7.8
----------------------------------------------------------------------------------------------------
创建测试表：
SQL> create table tb_valid(id number,name varchar2(20),period for valid);
Table created.
查看表的字段（自动创建了两个字段valid_start和valid_end，表示有效性验证的开始时间和结束时间）：
SQL> select column_name,data_type,data_length,column_id,segment_column_id, hidden_column from user_tab_cols where table_name='TB_VALID';
COLUMN_NAME DATA_TYPE DATA_LENGTH COLUMN_ID SEGMENT_COLUMN_ID HID
-------------------- ---------------------------------------- ----------------- ------------------ ----------------------------------- ---------
VALID_START TIMESTAMP(6) WITH TIME ZONE 13 1 YES
VALID_END TIMESTAMP(6) WITH TIME ZONE 13 2 YES
VALID NUMBER 22 YES
ID NUMBER 22 1 3 NO
NAME VARCHAR2 20 2 4 NO
插入三条记录：
第一条记录时间范围为1分钟：
SQL> insert into tb_valid(id,name,valid_start,valid_end) values(100,'A',sysdate,sysdate+1/1440);
1 row created.
第二条记录时间范围为5分钟：
SQL> insert into tb_valid(id,name,valid_start,valid_end) values(101,'B',sysdate,sysdate+5/1440);
1 row created.
第三条记录时间范围为30分钟：
SQL> insert into tb_valid(id,name,valid_start,valid_end) values(102,'C',sysdate,sysdate+30/1440);
1 row created.
SQL> COMMIT;
Commit complete.
查看所有记录：
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
下面测试通过指定一个验证时间进行查询测试（参照时间属于开始和结束时间范围内才会显示出来）
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid as of period for valid to_date('2020-12-20 20:40:00','yyyy-mm-dd hh24:mi:ss');
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- -------------------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 2020-12-20 20:42:53
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid as of period for valid to_date('2020-12-20 20:24:00','yyyy-mm-dd hh24:mi:ss');
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- -------------------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 2020-12-20 20:43:24
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 2020-12-20 20:43:24
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 2020-12-20 20:43:24
以上两次验证均可以显示正确结果。
下面验证会话级别的可见性控制：
1.会话设置某个验证时间
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('2020-12-20 20:40:00','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- -------------------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 2020-12-20 20:44:56
结果正确。
2.会话设置验证类型为显示所有记录
SQL> exec dbms_flashback_archive.enable_at_valid_time('all')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- -------------------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 2020-12-20 20:45:52
输入小写的all不起作用，改写为大写的ALL后，结果显示正确：
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- -------------------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 2020-12-20 20:46:04
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 2020-12-20 20:46:04
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 2020-12-20 20:46:04
3.会话设置验证时间为当前时间
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果不准确。
重新登录HR用户进行测试：
SQL> conn hr/hr
Connected.
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
重新登录并设置会话验证类型后，结果正确。
接着在会话中测试不同验证类型：
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('2020-12-20 20:40:00','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果不准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果不准确。
--清除相关的内存
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush GLOBAL CONTEXT;
System altered.
SQL> alter system flush BUFFER_POOL default;
System altered.
SQL> alter system flush BUFFER_cache;
System altered.
接着测试：
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('2020-12-20 20:40:00','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果不准确。
--重新收集统计信息后，再进行测试
SQL> execute dbms_stats.gather_table_stats('hr','tb_valid');
PL/SQL procedure successfully completed.
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
no rows selected
结果准确。
Execution Plan
----------------------------------------------------------
Plan hash value: 1794612013
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_VALID | 1 | 32 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((SYS_EXTRACT_UTC("T"."VALID_START")<=SYS_EXTRACT_UTC(SYSTI
MESTAMP(6)) OR "T"."VALID_START" IS NULL) AND
(SYS_EXTRACT_UTC("T"."VALID_END")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) OR
"T"."VALID_END" IS NULL))
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 id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
100 A 20-DEC-20 08.23.23.000000 PM -05:00 20-DEC-20 08.24.23.000000 PM -05:00 20-DEC-20
101 B 20-DEC-20 08.23.37.000000 PM -05:00 20-DEC-20 08.28.37.000000 PM -05:00 20-DEC-20
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
Execution Plan
----------------------------------------------------------
Plan hash value: 1794612013
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_VALID | 3 | 96 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('2020-12-20 20:40:00','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> select id,name,valid_start,valid_end,sysdate from tb_valid ;
ID NAME VALID_START VALID_END SYSDATE
---------- -------------------- ---------------------------------------- ---------------------------------------- ---------
102 C 20-DEC-20 08.23.48.000000 PM -05:00 20-DEC-20 08.53.48.000000 PM -05:00 20-DEC-20
结果准确。
Execution Plan
----------------------------------------------------------
Plan hash value: 1794612013
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_VALID | 1 | 32 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((SYS_EXTRACT_UTC("T"."VALID_START")<=SYS_EXTRACT_UTC(TIMES
TAMP' 2020-12-20 20:40:00.000000000') OR "T"."VALID_START" IS NULL) AND
(SYS_EXTRACT_UTC("T"."VALID_END")>SYS_EXTRACT_UTC(TIMESTAMP' 2020-12-20
20:40:00.000000000') OR "T"."VALID_END" IS NULL))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
-----------------------------------------------------------------------------------------
总结：
在我的测试过程中，TV 并不稳定，没有详细跟踪不稳定的原因，但是猜测与 cursor 执行计划重用有关，毕竟 Oracle 的实现只是增加了一个 filter 条件，如果由于某种原因，之前 cursor 的执行计划被重用，那么很可能这个 filter 条件就没有加上，随之而来的也就会返回所有记录。
通过执行计划显示后台机制是一方面，另一方面我们也可以看到实际上 TV 是会有性能问题的，
如果 WHERE 条件中无法使用到索引而执行了全表扫描（我这里因为没有 WHERE 条件所以只能是全表扫描），那么无论最终符合有效期的记录是多少，总要先进行所有记录的扫描，
我们可以通过前后两次的 consistent gets 基本相同来获得这个结论。
更直白的说，如果作为系统设计人员不去考虑索引的构建，而仅仅是启用了 TV，
那么哪怕根据有效期限制，有10万记录的表只有1条会被显示出来，也仍然需要先扫描10万记录，
然后再filter掉 99999条，这对于程序员来说，如果不仔细阅读执行计划，就可能会造成很大的困扰，
程序员会很奇怪，为什么这张表里面看上去只有1条记录，但是却要扫描那么长时间呢？
结论：
数据有效期是 Oracle 利用隐藏字段和 Flashback Query 技术作的一个有趣的功能，
在当前版本中确实存在一定的不稳定性，
建议定时收集表的统计信息，
同时数据架构人员在规划的时候一定要考虑性能因素。
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2020-12-24 17:39:33
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对