Oracle 中绑定变量并发控制锁闪回

最新推荐文章于 2023-08-15 09:58:26 发布

DB架构

最新推荐文章于 2023-08-15 09:58:26 发布

阅读量334

点赞数

分类专栏： DBA 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/127108041

版权

DBA 专栏收录该内容

183 篇文章 13 订阅

订阅专栏

本文通过示例展示了使用绑定变量与不使用绑定变量在插入数据时的性能差异，表明绑定变量能显著提高效率。此外，介绍了Oracle数据库的并发控制机制，如行级锁定、多版本控制和读一致性，以及闪回功能。在并发控制中，Oracle通过锁和多版本控制确保数据完整性，同时避免读写阻塞。最后，通过SCN系统改变号展示了闪回查询的用法，强调了其在数据恢复和一致性查询中的重要性。

摘要由CSDN通过智能技术生成

创建一个简单的表：


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>