Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Check.

Asked: April 29, 2019 - 1:51 pm UTC

Last updated: May 06, 2019 - 4:39 pm UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi Tom,

How exactly big insert or update getting processed in oracle?

Suppose I have to insert >=10GB more records in this table will this much of insert floods the db buffer cache or how it affect the SGA.

Is it the same case with update statement if we will have to make big updates.



and Connor said...

The database will manage that for you without any dramas.

If you need to insert 10G+, then typically you'd be looking at direct path load for that. That has performance benefits AND locking implications, so read the docs carefully

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#SQLRF51109

Big inserts (not in direct path) and big updates need enough UNDO tablespace to be able to rollback in the event of an error, but other than that, the database will manage buffers etc just fine.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

on Direct path loads.

Rajeshwaran, Jeyabal, April 30, 2019 - 11:36 am UTC

Also please look into this optimizer blog post, where the optimizer Product manager have gone through the flavors of Direct path loads (like High water mark loading, temp segment merging, high water mark brokering, Hybrid (temp segment merging/high water mark brokering) ) in detail along with how the segment space got managed during each of these operations.

hope this helps.

https://blogs.oracle.com/optimizer/space-management-and-oracle-direct-path-load

A reader, May 06, 2019 - 9:13 am UTC

Thanks Tom for your response.

Is there anything like buffer cache flooding. If yes, what causes this and how to avoid this.
Connor McDonald
May 06, 2019 - 4:39 pm UTC

Since we changed the way the buffer cache is managed in 8.1, I can't recall a time or scenario where this is possible.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database