Investigate dividing Oracle procedure COLL_UPDATED_REPLICAS using virtual scope

[yuyiguo]

The problem:
The Oracle procedure runs every two minutes. The maximum number of rows in the table that the procedure can handle is about 40 million. However, when CMS has big data operations, such as massive deletion or data movement, the number of rows increased significantly in a short time, for example, we had 144+ m rows on June 28.
After talking about this in Rucio devl meeting. We learned that ALTAS is providing one single procedure into 8 different procedures simultaneously based on the data's scope.

So we will look into how CMS can improve our procedure.

[ericvaandering]

To answer your question in chat, ATLAS uses scope more like we use campaign. So they have lots of different scopes. I don't know what we could do instead. Would we be able to segment on the first N characters of the LFN? Hash and mod 8 of the LFN?

[yuyiguo]

This is a good idea. The collection_replicas table for CMS has rucio datasets/CMS blocks. scopes and RSEs, but not campaign or LFN in it. CMS block names have something like /primaryds/processedds-version/data-tire#UUID. We may hash the primary DS and mod 8.
I have an idea that we can divide the data into tiers. CMS has 64 data tiers so far. We can group them into 8 to 10 groups.

[yuyiguo]

@ericvaandering
I talked with Kate about improving this procedure. She agreed that ORA_HASH on NAME/DID is reasonable. So I made the below scripts. Please comment.

1. Table definition: https://github.com/yuyiguo/rucio/blob/Oracle-procedure/etc/sql/oracle/schema.sql#L1613-L1642
2. Procedure definition: https://github.com/yuyiguo/rucio/blob/Oracle-procedure/etc/sql/oracle/procedures.sql#L955-L1063
3. Job definition: https://github.com/yuyiguo/rucio/blob/Oracle-procedure/etc/sql/oracle/jobs.sql#L374-L593

[yuyiguo]

I created the pull request for comments.
yuyiguo/rucio#7

[ericvaandering]

I've dropped the priority of this as I believe the issue will become more or less mute with a future release of Rucio. Yuyi says the procedure is working well now.