当前位置:网站首页>[explanation] get ora-12838: cannot read / modify an object after modifying it in parallel
[explanation] get ora-12838: cannot read / modify an object after modifying it in parallel
2022-04-23 08:21:00 【maray】
The content of this article is basically from the link https://apprize.best/php/oracle_4/16.html
Used to explain in detail why Oracle PDML After the operation ,commit/rollback Before ,
Read operation is not allowed .
See the red part for the main explanation . Carelessness :PDML The implementation of , Multiple threads start independent transactions to update ,
Then commit all transactions with a two-phase commit , So as to achieve PDML Submission of major affairs .
It is similar to an application layer scheme .
Parallel DML
The Oracle documentation limits the scope of parallel DML (PDML) to include only INSERT, UPDATE, DELETE, and MERGE (it does not include SELECT as normal DML does). During PDML, Oracle may use many parallel execution servers to perform your INSERT, UPDATE, DELETE, or MERGE instead of a single serial process. On a multi-CPU machine with plenty of I/O bandwidth, the potential increase in speed may be large for mass DML operations.
However, you should not look to PDML as a feature to speed up your OLTP-based applications. As stated previously, parallel operations are designed to fully and totally maximize the utilization of a machine. They are designed so that a single user can completely use all of the disks, CPU, and memory on the machine. In a certain data warehouse (with lots of data and few users), this is something you may want to achieve. In an OLTP system (with a lot of users all doing short, fast transactions), you do not want to give a user the ability to fully take over the machine resources.
This sounds contradictory: we use parallel query to scale up, so how could it not be scalable? When applied to an OLTP system, the statement is quite accurate. Parallel query is not something that scales up as the number of concurrent users increases. Parallel query was designed to allow a single session to generate as much work as 100 concurrent sessions would. In our OLTP system, we really do not want a single user to generate the work of 100 users.
PDML is useful in a large data warehousing environment to facilitate bulk updates to massive amounts of data. The PDML operation is executed in much the same way as a distributed query would be executed by Oracle, with each parallel execution server acting like a process in a separate database instance. Each slice of the table is modified by a separate thread with its own independent transaction (and hence its own undo segment, hopefully). After they are all done, the equivalent of a fast two-phase commit is performed to commit the separate, independent transactions. Figure 14-2 depicts a parallel update using four parallel execution servers. Each of the parallel execution servers has its own independent transaction, in which either all are committed with the PDML coordinating session or none commits.
We can actually observe the fact that there are separate independent transactions created for the parallel execution servers. We’ll use two sessions again. In the session with SID=258, we explicitly enable parallel DML. PDML differs from parallel query in that regard; unless you explicitly ask for it, you will not get it:
EODA@ORA12CR1> alter session enable parallel dml;
Session altered.
You can verify that parallel DML has been enabled for your session via:
EODA@ORA12CR1> select pdml_enabled from v$session where sid = sys_context('userenv','sid');
PDM
---
YES
The fact that the table is “parallel” is not sufficient, as it was for parallel query. The reasoning behind the need to explicitly enable PDML in your session is the fact that PDML has certain limitations associated with it, which I list after this example.
In the same session, we do a bulk UPDATE that, because the table is “parallel enabled,” will in fact be done in parallel:
EODA@ORA12CR1> update big_table set status = 'done';
In the other session, we’ll join V$SESSION to V$TRANSACTION to show the active sessions for our PDML operation, as well as their independent transaction information:
EODA@ORA12CR1> select a.sid, a.program, b.start_time, b.used_ublk,
2 b.xidusn ||'.'|| b.xidslot || '.' || b.xidsqn trans_id
3 from v$session a, v$transaction b
4 where a.taddr = b.addr
5 and a.sid in ( select sid
6 from v$px_session
7 where qcsid = 258)
8 order by sid
9 /
SID PROGRAM START_TIME USED_UBLK TRANS_ID
---------- ------------------------------ -------------------- ---------- ---------------
11 oracle@heera07 (P00B) 02/25/14 14:10:17 13985 26.32.15
12 oracle@heera07 (P000) 02/25/14 14:10:17 1 70.16.6
21 oracle@heera07 (P00F) 02/25/14 14:10:17 13559 20.18.37
23 oracle@heera07 (P007) 02/25/14 14:10:17 1 12.3.62
26 oracle@heera07 (P004) 02/25/14 14:10:17 1 33.4.11
95 oracle@heera07 (P005) 02/25/14 14:10:17 1 48.15.10
97 oracle@heera07 (P00C) 02/25/14 14:10:17 12676 9.5.1730
103 oracle@heera07 (P008) 02/25/14 14:10:17 14434 44.32.10
105 oracle@heera07 (P001) 02/25/14 14:10:17 1 64.0.9
169 oracle@heera07 (P002) 02/25/14 14:10:17 1 34.19.11
176 oracle@heera07 (P00D) 02/25/14 14:10:17 14621 4.22.1739
177 oracle@heera07 (P006) 02/25/14 14:10:17 1 74.14.6
191 oracle@heera07 (P009) 02/25/14 14:10:17 13070 54.11.10
258 sqlplus@heera07 (TNS V1-V3) 02/25/14 14:10:17 1 59.8.12
261 oracle@heera07 (P00A) 02/25/14 14:10:17 13521 7.13.1748
263 oracle@heera07 (P00E) 02/25/14 14:10:17 12186 14.23.76
267 oracle@heera07 (P003) 02/25/14 14:10:17 1 28.23.19
17 rows selected.
As you can see, there is more happening here than when we simply queried the table in parallel. We have 17 processes working on this operation, not just 9 as before. This is because the plan that was developed includes a step to update the table and independent steps to update the index entries. Look at a BASIC plus PARALLEL-enabled explain plan output from DBMS_XPLAN:
EODA@ORA12CR1> explain plan for update big_table set status = 'done';
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
We see the following:
---------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | BIG_TABLE | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 6 | UPDATE | BIG_TABLE | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| BIG_TABLE | Q1,00 | PCWP | |
---------------------------------------------------------------------------
As a result of the pseudo-distributed implementation of PDML, certain limitations are associated with it:
· Triggers are not supported during a PDML operation. This is a reasonable limitation in my opinion, since triggers tend to add a large amount of overhead to the update, and you are using PDML to go fast—the two features don’t go together.
· There are certain declarative RI constraints that are not supported during the PDML, since each slice of the table is modified as a separate transaction in the separate session. Self-referential integrity is not supported, for example. Consider the deadlocks and other locking issues that would occur if it were supported.
· You cannot access the table you’ve modified with PDML until you commit or roll back.
· Advanced replication is not supported with PDML (because the implementation of advanced replication is trigger-based).
· Deferred constraints (i.e., constraints that are in the deferred mode) are not supported.
· PDML may only be performed on tables that have bitmap indexes or LOB columns if the table is partitioned, and then the degree of parallelism would be capped at the number of partitions. You cannot parallelize an operation within partitions in this case, as each partition would get a single parallel execution server to operate on it. We should note that starting with Oracle 12c, you can run PDML on SecureFiles LOBs without partitioning.
· Distributed transactions are not supported when performing PDML.
· Clustered tables are not supported with PDML.
If you violate any of those restrictions, one of two things will happen: either the statement will be performed serially (no parallelism will be involved) or an error will be raised. For example, if you already performed the PDML against table T and then attempted to query table T before ending your transaction, then you will receive the error ORA-12838: cannot read/modify an object after modifying it in parallel.
It’s also worth noting in the prior example in this section, that had you not enabled parallel DML for the UPDATE statement, then the explain plan output would look quite different, for example:
------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | BIG_TABLE | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| BIG_TABLE | Q1,00 | PCWP | |
------------------------------------------------------------------------
To the untrained eye it may look like the UPDATE happened in parallel, but in fact it did not. What the prior output shows is that the UPDATE is serial and that the full scan (read) of the table was parallel. So there was parallel query involved, but not PDML.
VERIFYING PARALLEL OPERATIONS
You can quickly verify the parallel operations that have occurred in a session by querying the data dictionary. For example, here’s a parallel DML operation:
EODA@ORA12CR1> alter session enable parallel dml;
EODA@ORA12CR1> update big_table set status='AGAIN';
Next verify the type and number of parallel activities via:
EODA@ORA12CR1> select name, value from v$statname a, v$mystat b
2 where a.statistic# = b.statistic# and name like '%parallel%';
Here is some sample output for this session:
NAME VALUE
----------------------------------------------- ----------
DBWR parallel query checkpoint buffers written 0
queries parallelized 0
DML statements parallelized 1
DDL statements parallelized 0
DFO trees parallelized 1
The prior output verifies that one parallel DML statement has executed in this session.
版权声明
本文为[maray]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230712063321.html
边栏推荐
猜你喜欢
Transformer-XL: Attentive Language ModelsBeyond a Fixed-Length Context 论文总结
My heart's broken! A woman's circle of friends envied others for paying wages on time and was fired. Even her colleagues who liked her were fired together
岛屿的个数
Rearranging log files for leetcode simple question
WordPress love navigation theme 1.1.3 simple atmosphere website navigation source code website navigation source code
An article understands variable lifting
Qt编译QtXlsx库
WordPress爱导航主题 1.1.3 简约大气网站导航源码网址导航源码
【深度好文】Flink SQL流批⼀体化技术详解(一)
【Appium】测试时遇到手机内嵌H5页面的切换问题
随机推荐
Data deletion and modification (MySQL)
How to read books and papers
青苹果影视系统源码 影视聚合 影视导航 影视点播网站源码
Community group purchase applet source code + interface DIY + nearby leader + supplier + group collage + recipe + second kill + pre-sale + distribution + live broadcast
[effective go Chinese translation] part I
每周leetcode - 06 数组专题 7~739~50~offer 62~26~189~9
CGM优化血糖监测管理——移宇科技亮相四川省国际医学交流促进会
[effective go Chinese translation] function
[untitled]
二维01背包
Qt读取路径下所有文件或指定类型文件(含递归、判断是否为空、创建路径)
总线结构概述
QFileDialog 选择多个文件或文件夹
浅谈ES6尾调优化
Jetson Xavier NX (3) bazel mediapipe installation
[appium] encountered the problem of switching the H5 page embedded in the mobile phone during the test
396. Rotate Function
室内定位技术对比
将实例化对象的方法 给新的对象用
跨域配置报错: When allowCredentials is true, allowedOrigins cannot contain the special value “*“