0

In my system, I have a set of Oracle 19c database tables which stored temparary data, which are getting expired. I have a solution where I partitioned these tables with the create timestamp of these entries.

CREATE TABLE TEMP (
            ID VARCHAR (100) NOT NULL,
            TYPE VARCHAR(100) NOT NULL,
            CONTEXT_OBJECT BLOB,
            TIME_CREATED NUMBER(19)
)partition by range (TIME_CREATED) INTERVAL(<some value>)

In this I have tested that setting the INTERVAL to for a value equivalent to a one day. In this scenarios, partition size is growing about 300 GB during the day. I can see that initial insert queries are file but with the DB load INSERT INTO VALUES (...) ... queries getting sometimes 10 seconds to complete.

So I wanted to try reducing the interval to may be 1 hour instead of a day and check if it improves performance of the insert queries. As per the analysis the root cause for the insert query to get slow is due to DW – contention.

FYI The index for this table is global indexes. One column also contains a BLOB object too.

The reason for partitioning is to drop partitions based on their TIME_CREATED once a expirey time exceeds.

Can someone help me figure this out?

Improve this question
11
  • The size of the partition should not affect the performance of the INSERTs. As your index is global, you could have some contention there if there are a lot of INSERTs going on in several partitions.
    – Diego Ferruchelli
    Commented Aug 22 at 5:01
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Marcello Miorelli
    Commented Aug 22 at 12:51
  • 1
    I agree with @DiegoFerruchelli, the table/partition size will not have an impact on the insert operation. When you see "DW – contention" contention then you can try to find out what this means and look for countermeasures. A quick look at Oracle's support portal shows "Wait Event - enq: DW - contention (Doc ID 1331665.1)", with the information:
    – miracle173
    Commented Aug 22 at 14:28
  • 1
    WaitEvent-enq:DW-contention(DocID1331665.1) Goal This Note describes the Enqueue "enq: DW - contention" which is related to SecureFile LOBS and acquiring the In-Memory Dispenser Latch. Solution The DW enqueue wait event is related to SecureFile LOBs and acquiring the In-Memory Dispenser Latch. In-Memory Dispenser - Free space allocations to SecureFile objects are managed in-memory through a shared-memory data structure (part of SGA) known as the In-Memory Space Dispenser. References NOTE:743773.1-SMCO (SpaceManagementCoordinator)ForAutoextend On Datafiles And How To Disable/Enable
    – miracle173
    Commented Aug 22 at 14:29
  • 1
    Global indexes are bad when you truncate/drop expired data, because the entire index needs to be rebuild. Better create local indexes, then you can drop/truncate outdated partitions which is very fast. I guess the insert are not slow because of the insert commands itself. They might be slow, because the database is busy with removing expired data.
    – Wernfried Domscheit
    Commented Aug 22 at 18:22

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.