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

     创建一个简单的表：

    1.  
    2. scott@orclpdb1:orclcdb> create table t (x int);
    3.  
    4. Table created.
    5.  
    6. scott@orclpdb1:orclcdb>

    再创建两个非常简单的存储过程，它们都向这个表中插入数字1 到10000.不过，第一个过程使用了一条带绑定变量的SQL语句。

    1.  
    2. scott@orclpdb1:orclcdb> create or replace procedure proc1
    3.   2  as
    4.   3  begin
    5.   4     for i in 1 .. 10000
    6.   5     loop
    7.   6        execute immediate 'insert into t values( :x )' using i;
    8.   7     end loop;
    9.   8  end;
    10.   9  /
    11.  
    12. Procedure created.
    13.  
    14. scott@orclpdb1:orclcdb> 
    1. scott@orclpdb1:orclcdb> 
    2. scott@orclpdb1:orclcdb> create or replace procedure proc2
    3.   2  as
    4.   3  begin
    5.   4      for i in 1 .. 10000
    6.   5      loop
    7.   6         execute immediate
    8.   7         'insert into t values ( '||i||' )';
    9.   8      end loop;
    10.   9  end;
    11.  10  /
    12.  
    13. Procedure created.
    14.  
    15. scott@orclpdb1:orclcdb>

    二者之间唯一的差别是一个过程使用了绑定变量，而另一个没有使用。它们都使用了动态SQL，而且过程中的逻辑也是相同的。不同之处只在于是否首先使用了绑定变量。

    现在使用工具runstats来比较两种方法。

    1. scott@orclpdb1:orclcdb> exec runstats_pkg.rs_start
    2.  
    3. PL/SQL procedure successfully completed.
    4.  
    5. scott@orclpdb1:orclcdb> exec proc1
    6.  
    7. PL/SQL procedure successfully completed.
    8.  
    9. scott@orclpdb1:orclcdb> exec runstats_pkg.rs_middle
    10.  
    11. PL/SQL procedure successfully completed.
    12.  
    13. scott@orclpdb1:orclcdb> exec proc2
    14.  
    15. PL/SQL procedure successfully completed.
    16.  
    17. scott@orclpdb1:orclcdb> exec runstats_pkg.rs_stop(10000)
    18. Run1 ran in 13cpu hsecs
    19. Run2 ran in 1372cpu hsecs
    20. run1 ran in .95% of the time
    21.  
    22. Name                                  Run1        Run2        Diff
    23. STAT...calls to kcmgcs                 167      10,199      10,032
    24. STAT...enqueue requests                 49      10,096      10,047
    25. STAT...enqueue releases                 47      10,096      10,049
    26. LATCH.shardgroup list latch              5      10,074      10,069
    27. STAT...parse count (hard)                5      10,099      10,094
    28. STAT...parse count (total)              33      10,138      10,105
    29. STAT...calls to get snapshot s          43      10,933      10,890
    30. STAT...session logical reads        10,787      23,801      13,014
    31. LATCH.PDB Hash Table Latch              20      20,095      20,075
    32. LATCH.enqueue hash chains              647      21,196      20,549
    33. STAT...table scan disk non-IMC         401      21,654      21,253
    34. STAT...table scan rows gotten          401      21,654      21,253
    35. LATCH.cache buffers chains          52,196      77,322      25,126
    36. STAT...recursive calls              10,181      42,109      31,928
    37. LATCH.shared pool simulator             17      41,868      41,851
    38. STAT...session uga memory max      192,576     130,976     -61,600
    39. STAT...session uga memory          130,968      65,472     -65,496
    40. STAT...KTFB alloc space (block   1,245,184   1,179,648     -65,536
    41. STAT...session pga memory          131,072           0    -131,072
    42. LATCH.shared pool                      434     444,245     443,811
    43. STAT...physical read total byt       8,192   1,015,808   1,007,616
    44. STAT...cell physical IO interc       8,192   1,015,808   1,007,616
    45. STAT...physical read bytes           8,192   1,015,808   1,007,616
    46. STAT...logical read bytes from  88,367,104 194,977,792 106,610,688
    47.  
    48. Run1 latches total versus runs -- difference and pct
    49. Run1        Run2        Diff       Pct
    50. 57,116     620,092     562,976      9.21%
    51.  
    52. PL/SQL procedure successfully completed.
    53.  
    54. 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）

    )

    1. [oracle@MaxwellDBA ~]$ sqlplus sys/sys as sysdba
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 29 17:11:24 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;
    14.  
    15. Session altered.
    16.  
    17. sys@cdb$root:orclcdb> grant execute on dbms_flashback to SCOTT;
    18.  
    19. Grant succeeded.
    20.  
    21. scott@orclpdb1:orclcdb> variable scn number
    22. scott@orclpdb1:orclcdb> exec :scn := dbms_flashback.get_system_change_number;
    23.  
    24. PL/SQL procedure successfully completed.
    25.  
    26. scott@orclpdb1:orclcdb> print scn
    27.  
    28.        SCN
    29. ----------
    30.   12092779
    31.  
    32. scott@orclpdb1:orclcdb>

    我们有了SCN，告诉Oracle我们要查询的时间点，以后要查询Oracle时，就能看看这一时刻表中的内容。

    1. scott@orclpdb1:orclcdb> 
    2. scott@orclpdb1:orclcdb> 
    3. scott@orclpdb1:orclcdb> select count(*) from emp;
    4.  
    5.   COUNT(*)
    6. ----------
    7.         14
    8.  
    9. 1 row selected.
    10.  
    11. scott@orclpdb1:orclcdb> delete from emp;
    12.  
    13. 14 rows deleted.
    14.  
    15. scott@orclpdb1:orclcdb> select count(*) from emp;
    16.  
    17.   COUNT(*)
    18. ----------
    19.          0
    20.  
    21. 1 row selected.
    22.  
    23. scott@orclpdb1:orclcdb> 
    24. scott@orclpdb1:orclcdb> select count(*),:scn then_scn, dbms_flashback.get_system_change_number now_scn from emp as of scn :scn;
    25.  
    26.   COUNT(*)   THEN_SCN    NOW_SCN
    27. ---------- ---------- ----------
    28.         14   12092779   12093230
    29.  
    30. 1 row selected.
    31.  
    32. scott@orclpdb1:orclcdb> commit;
    33.  
    34. Commit complete.
    35.  
    36. 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)
    37.   2  /
    38.  
    39.    CNT_NOW   CNT_THEN   THEN_SCN    NOW_SCN
    40. ---------- ---------- ---------- ----------
    41.          0         14   12092779   12093289
    42.  
    43. 1 row selected.
    44.  
    45. scott@orclpdb1:orclcdb> flashback table emp to scn :scn;
    46. flashback table emp to scn :scn
    47.                 *
    48. ERROR at line 1:
    49. ORA-08189: cannot flashback the table because row movement is not enabled
    50.  
    51. scott@orclpdb1:orclcdb> flashback table emp to scn :scn;
    52.  
    53. Flashback complete.
    54.  
    55. scott@orclpdb1:orclcdb>


    ORA-08189: cannot flashback the table because row movement is not enabled

    Solution:

    1. sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;
    2.  
    3. Session altered.
    4.  
    5. sys@cdb$root:orclcdb> select table_name,ROW_MOVEMENT from dba_tables where table_name = 'EMP';
    6.  
    7. TABLE_NAME
    8. ----------------------------------------------------------------------------------------------------
    9. ROW_MOVE
    10. --------
    11. EMP
    12. DISABLED
    13.  
    14.  
    15. 1 row selected.
    16.  
    17. sys@cdb$root:orclcdb> alter table SCOTT.EMP enable row movement;
    18.  
    19. Table altered.
    20.  
    21. sys@cdb$root:orclcdb> select table_name,ROW_MOVEMENT from dba_tables where table_name = 'EMP';
    22.  
    23. TABLE_NAME
    24. ----------------------------------------------------------------------------------------------------
    25. ROW_MOVE
    26. --------
    27. EMP
    28. ENABLED
    29.  
    30.  
    31. 1 row selected.
    32.  
    33. sys@cdb$root:orclcdb>

  • 相关阅读:
    阿里云配置https，配置SSL证书
    Vue项目重新部署后，提示用户刷新网页
    关于gdb调试: 你的问题可能会在这里找到答案
    猿创征文｜【JavaSE】Java概述与配置问题解决
    HTML+CSS大作业：众志成城 抗击疫情 抗击疫情网页制作作业 疫情防控网页设计
    【iOS开发】——属性关键字
    数据结构—队列的实现
    5分钟安装Kubernetes+带你轻松安装istio服务网格指南
    Postman如何导出接口的几种方法？
    宝塔面板日志和缓存占用磁盘空间很大，如何清理？
  • 原文地址：https://blog.csdn.net/u011868279/article/details/127108041