创建一个简单的表:
scott@orclpdb1:orclcdb> create table t (x int);
Table created.
scott@orclpdb1:orclcdb>
再创建两个非常简单的存储过程,它们都向这个表中插入数字1 到10000.不过,第一个过程使用了一条带绑定变量的SQL语句。
scott@orclpdb1:orclcdb> create or replace procedure proc1
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate 'insert into t values( :x )' using i;
7 end loop;
8 end;
9 /
Procedure created.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> create or replace procedure proc2
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'insert into t values ( '||i||' )';
8 end loop;
9 end;
10 /
Procedure created.
scott@orclpdb1:orclcdb>
二者之间唯一的差别是一个过程使用了绑定变量,而另一个没有使用。它们都使用了动态SQL,而且过程中的逻辑也是相同的。不同之处只在于是否首先使用了绑定变量。
现在使用工具runstats来比较两种方法。
scott@orclpdb1:orclcdb> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> exec proc1
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> exec proc2
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> exec runstats_pkg.rs_stop(10000)
Run1 ran in 13cpu hsecs
Run2 ran in 1372cpu hsecs
run1 ran in .95% of the time
Name Run1 Run2 Diff
STAT...calls to kcmgcs 167 10,199 10,032
STAT...enqueue requests 49 10,096 10,047
STAT...enqueue releases 47 10,096 10,049
LATCH.shardgroup list latch 5 10,074 10,069
STAT...parse count (hard) 5 10,099 10,094
STAT...parse count (total) 33 10,138 10,105
STAT...calls to get snapshot s 43 10,933 10,890
STAT...session logical reads 10,787 23,801 13,014
LATCH.PDB Hash Table Latch 20 20,095 20,075
LATCH.enqueue hash chains 647 21,196 20,549
STAT...table scan disk non-IMC 401 21,654 21,253
STAT...table scan rows gotten 401 21,654 21,253
LATCH.cache buffers chains 52,196 77,322 25,126
STAT...recursive calls 10,181 42,109 31,928
LATCH.shared pool simulator 17 41,868 41,851
STAT...session uga memory max 192,576 130,976 -61,600
STAT...session uga memory 130,968 65,472 -65,496
STAT...KTFB alloc space (block 1,245,184 1,179,648 -65,536
STAT...session pga memory 131,072 0 -131,072
LATCH.shared pool 434 444,245 443,811
STAT...physical read total byt 8,192 1,015,808 1,007,616
STAT...cell physical IO interc 8,192 1,015,808 1,007,616
STAT...physical read bytes 8,192 1,015,808 1,007,616
STAT...logical read bytes from 88,367,104 194,977,792 106,610,688
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
57,116 620,092 562,976 9.21%
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb>
前面的结果清楚地显示出,根据CPU时间来看,不使用绑定变量插入10000行与绑定变量时相比,需要更长的时间,所需的资源也大幅度增加。实际上,如果不使用绑定变量,插入这些记录行所需要的CPU时间几乎是使用绑定变量时的20倍。对于每一个不使用绑定变量的插入,执行语句的大部分时间只是用来解析语句。
理解并发控制
如果对数据库如何实现并发控制不了解,会出现以下几种问题:
- 破坏数据的完整性
- 随着用户数的增多,应用的运行速度减慢。
- 不能很好地扩展应用来支持大量用户。
实现锁定
数据库使用锁来保证任何给定时刻最多只有一个事务在修改给定的一段数据。实质上讲,正是锁机制才使并发控制成为可能。
对Oracle 锁定策略的总结:
- Oracle只在修改时才对数据加行级锁。正常情况下不会升级到块级锁或表级锁。
- 如果只是读数据,Oracle绝不会对数据锁定。不会因为简单的读操作在数据行上锁定。
- 写入器(writer)不会阻塞读取器(reader)。换句话说,读(read)不会被写(write)阻塞。
- 写入器向写某行数据,但另一个写入器已经锁定了这行数据,此时该写入器才会被阻塞。读取器绝对不会阻塞写入器。
多版本控制
Oracle 采用了一种多版本、读一致(read-consistent)的并发模型。
Oracle利用了多版本控制机制提供了以下特性:
- 读一致查询:对于一个时间点(point in time)、查询会产生一致的结果。
- 非阻塞查询:查询不会被写入器阻塞,但在其他数据库中可能不是这样。
Oracle数据库中有两个非常重要的概念。
- 多版本(multi-versioning)指Oracle能够从数据库同时物化多个版本的数据。
- 读一致性。Oracle中的查询会从某个一致的时间点开始返回,查询使用的每个块都从同一个时间点开始,即使它在你执行查询时被修改或锁定。
闪回
Oracle总是基于查询的某个时间点来做决定,Oracle会保证打开的结果集肯定是以下两个时间点之一的当前结果集。
- 游标打开时的时间点。这是read commited 隔离模式的默认行为。该模式是默认的事务模式。
- 查询所属事务开始的时间点。这是READ ONLY和SERIALIZABLE 隔离级别中的默认行为。
闪回数据归档用于长期闪回查询(过去的数月甚至数年)
SCN ( System Change Number 系统修改号 或系统提交号(System Commit Number)
)
[oracle@MaxwellDBA ~]$ sqlplus sys/sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 29 17:11:24 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;
Session altered.
sys@cdb$root:orclcdb> grant execute on dbms_flashback to SCOTT;
Grant succeeded.
scott@orclpdb1:orclcdb> variable scn number
scott@orclpdb1:orclcdb> exec :scn := dbms_flashback.get_system_change_number;
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> print scn
SCN
----------
12092779
scott@orclpdb1:orclcdb>
我们有了SCN,告诉Oracle我们要查询的时间点,以后要查询Oracle时,就能看看这一时刻表中的内容。
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
scott@orclpdb1:orclcdb> delete from emp;
14 rows deleted.
scott@orclpdb1:orclcdb> select count(*) from emp;
COUNT(*)
----------
0
1 row selected.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select count(*),:scn then_scn, dbms_flashback.get_system_change_number now_scn from emp as of scn :scn;
COUNT(*) THEN_SCN NOW_SCN
---------- ---------- ----------
14 12092779 12093230
1 row selected.
scott@orclpdb1:orclcdb> commit;
Commit complete.
scott@orclpdb1:orclcdb> select cnt_now, cnt_then,:scn then_scn, dbms_flashback.get_system_change_number now_scn from (select count(*) cnt_now from emp),(select count(*) cnt_then from emp as of scn :scn)
2 /
CNT_NOW CNT_THEN THEN_SCN NOW_SCN
---------- ---------- ---------- ----------
0 14 12092779 12093289
1 row selected.
scott@orclpdb1:orclcdb> flashback table emp to scn :scn;
flashback table emp to scn :scn
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
scott@orclpdb1:orclcdb> flashback table emp to scn :scn;
Flashback complete.
scott@orclpdb1:orclcdb>
ORA-08189: cannot flashback the table because row movement is not enabled
Solution:
sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;
Session altered.
sys@cdb$root:orclcdb> select table_name,ROW_MOVEMENT from dba_tables where table_name = 'EMP';
TABLE_NAME
----------------------------------------------------------------------------------------------------
ROW_MOVE
--------
EMP
DISABLED
1 row selected.
sys@cdb$root:orclcdb> alter table SCOTT.EMP enable row movement;
Table altered.
sys@cdb$root:orclcdb> select table_name,ROW_MOVEMENT from dba_tables where table_name = 'EMP';
TABLE_NAME
----------------------------------------------------------------------------------------------------
ROW_MOVE
--------
EMP
ENABLED
1 row selected.
sys@cdb$root:orclcdb>