0

I have a partitioned table in oracle. Partition key is the year. If I consider the partition for the year 2022, do the extents belonging to the segments of this concrete partition contain only records with partition key 2022? What about the extents for the indexes?

Since I have problems with hot blocks. I would like to distribute the block id's of the extents of different partitions to different child processes so that each child works on a set of distinct database blocks where no other process works on.

Ali Tc
  • 1
  • 1
  • 1
    Segment > Extent > Data block. Each partition is a separate segment, so yes, all extents of that partition have the same value of year. – astentx May 21 '23 at 14:25
  • Thank you very much for your answer, can it possibly be that in the extents of the partition with the partition key 2022, records exist with other partitions key than 2022? like records with partition key 2021? – Ali Tc May 21 '23 at 17:09
  • 1
    No. Partition is a separate segment. Extent is part of the segment. See [logical structures](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/introduction-to-oracle-database.html#GUID-6DA14086-C6A4-422E-8131-BE00B32CD01B) – astentx May 21 '23 at 17:45
  • To deal with hot blocks, you need to distribute the rows being updated (shouldn't have a hot block problem with inserts, unless it's the indexes causing the issue) by some column that will be roughly randomized among your concurrent processes. I doubt RANGE partitioning on a date column would be a good candidate for that. If you can't find another good candidate, you should consider using HASH partitioning on the primary key. Keep in mind that only local indexes will enjoy the benefits of this distribution. Global indexes could still cause concurrency limitations with inserts. – Paul W May 21 '23 at 18:32
  • Could you post the nature of the "hot block" issue you're seeing. What wait event do you see taking up an excessive amount of clock time for your processes? – Paul W May 21 '23 at 18:33
  • Table T1 is LIST partitioned by USAGE_FILE_ID. Each time you receive a file you have a couple of million "usages" which get inserted in the table T1. For each usage all the shares (1:n) get inserted into another table T2 which is also partitioned in the same way and is a detail to the table T1. Records in T1 get updated and that means millions of updates. When I start, say, 40 child processes, at some point in time there is a contention on data blocks which leads to waits. I would like to give distinct extents to each child process. I will try to ask the DBAs for details of the hot block issue – Ali Tc May 22 '23 at 15:23
  • @AliTc What version of Oracle are you using? For example, if you're on 18c or above, [scalable sequences](https://oracle-base.com/articles/18c/scalable-sequences-18c) may be a perfect solution for hot blocks. – Jon Heller May 23 '23 at 05:09
  • I am using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production. – Ali Tc May 23 '23 at 07:04
  • By the time these processes start all of the usages have already been loaded in the system days ago. So it is the processing itself that has issues with data blocks not the loading. Table T1 has 70 and table T2 200 billion records. – Ali Tc May 23 '23 at 07:10

0 Answers0