Unrestricted Parallel DMLs and Direct Loads

Oracle University Podcast

In this episode, hosts Lois Houston and Nikita Abraham discuss new features in Oracle Database 23ai related to Data Manipulation Language (DML). They are joined by Senior Principal Database & MySQL Instructor, Bill Millar, who explains the concept of unrestricted parallel DMLs and their importance in speeding up large operations and maintaining summary tables. The discussion then turns to unrestricted direct loads, examining the evolution of direct loads with 23ai and the broader impact of these changes. Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/140830/ Oracle University Learning Community: https://education.oracle.com/ou-community LinkedIn: https://www.linkedin.com/showcase/oracle-university/ X: https://twitter.com/Oracle_Edu Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode. -------------------------------------------------------- Episode Transcript:

00:00

Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started!

00:26

Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Principal Technical Editor with Oracle University, and with me is Lois Houston, Director of Innovation Programs.

Lois: Hi there! In our last episode, we discussed a ground-breaking caching solution in Oracle Database 23ai, known as True Cache. We spoke about its configuration and deployment, and explored how to apply True Cache to our applications.

Nikita: Today, we’re going to talk about two Oracle Database 23ai new features related to Data Manipulation Language, or DML. The first is Unrestricted Parallel DMLs and then we’ll move on to Unrestricted Direct Loads. We’ll talk about the situation prior to 23ai, identify the improvements that have been made, and look at their benefits.

01:15

Lois: And returning for another episode is Bill Millar, our Senior Principal Database & MySQL Instructor with Oracle University. Hi Bill! So, to start, can you explain what unrestricted parallel DMLs are and why they are important, especially in the context of Oracle Database?

Bill: The Oracle Database allows DML statements such as inserts, updates, deletes, merge to be executed in parallel by breaking those statements into smaller task. These transactions can contain multiple DML statements. And they can modify multiple different tables.

So transactions with the parallel DML is going to use the execution method by breaking up those large operations to execute the transaction in parallel. It helps speed up the large operations. And it's advantageous to data warehouse environments where we're maintaining like summary tables, historical tables. And even in OLTP systems, it can be beneficial for long-running batch jobs.

The scale up. Well, it's basically dividing the executing SQL against those large tables and indexes into those smaller units of work.

02:36

Nikita: So, what were the limitations prior to 23ai?

Bill: So once that object was modified by APLL statement, the object cannot be read or modified later in the same transaction. After that parallel DML modifies a table, there is no follow-on DML or query on the same table within that same transaction. If any attempt to access a table modified by that parallel statement, the transaction would be rejected.

You're only allowed to query on those tables prior to that DML on that object itself.

03:16

Lois: Ok… So with these new improvements, I’m guessing some of these restrictions have been removed?

Bill: In this case, in the same session, you can query the table multiple times. You can perform conventional DML on the same table within the same session. And you can also have multiple direct loads in the same session without having to do that commit.

But there are still some restrictions with it. Heap tables only. You can't do it with any clustered tables or IOT, Index Organized Tables. Non-ASSM, the Automatic Segment Space Management tables. The temp table is not under ASSM. Why? Because it has to have uniform extents or any other tablespaces that you created with the uniform extents. So those restrictions still apply.

So some of the improvements are some of the restrictions can help reduce the overhead. We can enable Parallel DML within that session. Allows the multiple operations on the same object. And it doesn't require that commit for each separate operation.

Makes it a little bit easier to use by removing some of these limitations. Now users can run parallel DMLs and any combination of statements within that same transaction. And it can help simplify and speed up data loading analytic processes by making the database, the parallel execution and parallel queries, at the same time within that same session, again, eliminating having to do commits.

04:58

Nikita: Thanks for that summary of all the improvements, Bill. Now, how do you enable this? Is it enabled by default?

Bill: To enable the Parallel DML mode, it is required for a session. It is disabled by default. That's because the Parallel DML and Serial DML, they have different locking, different ways to handle the transactions, different disk space requirements.

When Parallel DML is enabled in a session, all DML statements are considered for parallel execution. Only a statement is considered for parallel execution when the Enable Parallel DML hint is used if I don't set it for a session. The sessions DML mode does not influence any parallelism of DDL statements. When the Parallel DML is disabled, no DML is executed in parallel, even if the hint is used.

05:59

Lois: Bill, I would like to dig a little deeper into the benefits. How do these lifted restrictions improve the overall performance and reduce overhead?

Bill: There's no longer that requirement to commit everything separately. So that's going to reduce the overhead, not having to do the commit all the time.

The scalability of accessing those large objects, executing parallel makes the decision support systems, those data warehouses and batch OLTP jobs or any other larger DML operation execute faster.

By removing that one touch limitation, it allows the parallel DML statements to be read or modified by later statements of the same transaction in the same session. It's very similar to the non-parallel statements. And even OLTP systems can also benefit, for example, maintaining a larger operation, such as the creation of indexes, refreshing tables, or even creating summary tables.

07:14

Did you know that Oracle University offers free courses on Oracle Cloud Infrastructure? You’ll find training on everything from cloud computing, database, and security to artificial intelligence and machine learning, all free to subscribers. So, what are you waiting for? Pick a topic, leverage the Oracle University Learning Community to ask questions, and then sit for your certification. Visit mylearn.oracle.com to get started.

07:42

Nikita: Welcome back! Let's move on to the next new feature, which is unrestricted direct loads. Bill, what was the situation with direct loads like, prior to 23ai?

Bill: After a direct load and prior-- it was always prior to a commit, queries in additional DMLs were not allowed on that same table. You might encounter the ORA error, the 12838, saying, hey, you can't read or modify this in parallel. That's because the DML on that direct load had access to that and that session for that. So you might have received that error.

The enq contention, the wait event for the direct load issue in a different session from the other sessions during the direct load is having to wait, because of that queuing that-- because a transaction gets that table, locks that information to keep that table from being modified until that direct load has actually committed.

Within the same transaction, within the same session, trying to do multiple DMLs with the-- while it is being modified with the direct loads itself. Unlike conventional loads, the direct loads, as the new blocks and extents are added to the segment, the high water mark does not actually get moved until the actual commit itself. So that's why there is restrictions in the same session or even in other sessions to be able to do anything. So to prevent the errors, the applications had to do a commit immediately after that direct load to prevent those errors from happening.

Well now, there are restrictions when that direct load was done prior to that commit for that. The same table in the same session, couldn't query, couldn't do any additional DMLs, couldn't do any additional parallel DMLs. And even in other sessions, queries were not allowed on the same tables that was in use by the other session. So no additional conventional DMLs, no additional parallel DMLs were allowed.

10:09

Lois: Ok.. it was restrictive in what could be done. So, how have direct loads evolved with the 23ai release?

Bill: Some of those previous restrictions have been lifted in that same session with that same table. So now you can immediately-- and notice that we're talking here, same session, same table. All right. So you can query multiple times within that same session. You can perform additional DML and you can also do multiple direct loads in the same session without having to do that commit.

However, there still are restrictions. It has to be a heap table. It does not work with index organized tables or clustered tables. And the tablespace, if it's not using the automatic segment space management, it cannot-- it does not apply to those either, or if tables with a uniform extents-- tablespace with uniform extents. That's why anything in the temporary table is also included. Why? Because the temporary tablespace has to be uniform extents.

11:17

Nikita: So, what are the restrictions lifted for different sessions on the same table?

Bill: Sessions can query that table, can perform conventional DML on that, able to also concurrently perform a direct load, and I can roll back to a save point. So you can see those added features can be very beneficial.

But there's still restrictions that apply. It still applies to heap tables only, and it still applies to only tablespaces that are using the automatic segment space management for that. Of course, that includes the temporary tablespace and it doesn't work with tablespaces that have uniform extents.

Your application DML might need to query the data after that direct load without committing, applications that might need to modify data within that same transaction as that direct load. You can enable multiple append hint. So you can specify the hint in addition to pending hint to disable. You can specify the no multi-append hint to disable it.

12:27

Lois: Bill, what’s the broader impact of these changes. How do these improvements make things more development-friendly?

Bill: So changes to the direct load make things a little bit more development friendly by removing those directions after that direct load itself. So previous restrictions when loading-- querying the data kept us from doing multiple things at the same time. So now I can query on that table direct load from the same session, from a different session. I can do conventional DMLs on the table within the same session. It allows me to do a rollback on it.

I can do direct loads on the same table within the same session. Again, I can also allow rollback to a save point. As long as my compatibility is set to 21.0.0.0, I will be able to go ahead and benefit from this feature. And there is no increase with it as far as the space usage or causing any fragmentation to the table. So that will not be an issue.

13:35

Nikita: Well, that’s the end of our time together, but I want to thank you, Bill, for sharing your expertise with us.

Lois: To learn more about what we discussed today, visit mylearn.oracle.com and search for the Oracle Database 23ai New Features for Administrators course. Join us next week for a discussion on some more Oracle Database 23ai new features. Until then, this is Lois Houston…

Nikita: And Nikita Abraham signing off!

14:03

That’s all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We’d also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.