Skip to Main Content
  • Questions

  • SQL query using Rollback segment heavily

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shankar.

Asked: August 08, 2019 - 8:34 am UTC

Last updated: August 12, 2019 - 3:40 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Dear Team,
I am querying from a table where dml operations are continuously taking place in current financial year(19-20). But I am retrieving data of 17-18 financial year's and it was pretty slow. On investigating, I found that it was using rollback segment data. I got a bit confused. Because to best of my knowledge RBS comes into play when someone is querying data which is also undergoing some changes.
Appreciate your insight.

Best Wishes

and Connor said...

Consider the following scenario

- Your query starts at 9am, looking for all the 2017 data. Its been running now for (say) 5 mins

- It hits a block that
- contains all 2019 data
- was last changed at 9:02am

Can I discard this block from consideration? No I can't because it *might* have contained 2017 data at 9am (when your query started).

For each block we visit, if it is "newer" then when your query started, we must take it back in time (using the information in the undo segments) before we can *know* that the data in that block is relevant or not.

Hope that clarifies it

Rating

  (3 ratings)

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

Comments

An observation....

J. Laurindo Chiappa, August 09, 2019 - 12:17 pm UTC

imho, this situation is for sure a reinforcement regarding the (extreme!!) utility of PARTITIONING when analyzing a large portion of data : surely, if the table in question is partitioned, the RDBMS will be 100% sure that no 2017 data will exists in any of the blocks containing 2019 data/live data - of course, 2017/old data WILL reside in 2017 partition, in DIFFERENT extents/blocks than 2019/live data... SO, NO NEED to check UNDO data when querying 2017 data whatever be the modifications in 2019 data blocks....

Regards,

Chiappa
Connor McDonald
August 12, 2019 - 3:40 am UTC

Good input.

To Shankar

J. Laurindo Chiappa, August 09, 2019 - 1:20 pm UTC

hi : speaking about Performance when reading old data, another point is : if you were using Partitions, this easily lets you have different tablespaces for each Partition, thus you could COMPRESS the tablespace containing old data (normally less blocks to read implies in better performance) AND you could even put the tablespace in READ-ONLY mode, making UPDATEs in old data impossible and avoiding ANY need to read UNDO , matters not what happens....

Regards,

Chiappa

Shankar, August 12, 2019 - 5:21 am UTC

I was guessing what connor had said. Still I was checking with you if i am missing out on anything else. Thanks to chiappa also for his valuable inputs. Thanks for your response.
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 channel and Chris's channel 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