We are seeing a big slowdown in a job we are running and are not sure why. We have a database that is housing around 480,000,000 records and there are 80 other DBs reading and deleting from those records. Below is our developers description:
We have a big table BIG_TABLE (~ 500,000,000 records) in our ORA DB.
About 80 clients connect to DB and doing next steps:
- UPDATE BIG_TABLES
WHERE IN_PROGRESS = ‘Y’
AND LOCATIONS_ID = *unique client ID*;
- DELETE FROM BIG_TABLE
WHERE IN_PROGRESS = ‘Y’
AND LOCATIONS_ID = *unique client ID*;
First few hours we did not have any issues, but the more records were deleted in the BIG_TABLE, the more time that three steps take.
When we started it took about a second, few days later it takes about minute. There are about 280,000,000 records now (less almost in twice than in beginning, and in 60 times slower).
There are two indexes on the BIG_ TABLE: IN_PROGRESS and LOCATIONS_ID. We tried to add one more for CREATED_DATE, but looks like it even more slowed down the processing.
No “Begin/commit/rollback Transaction”, no “for update nowait”. Server type is shared.
I realize that more information will be needed to provide some possible help, so let me know what else I can post up and I will.
Thanks