I need to write PL/SQL script which need to migrate 190 k subscriber from one Telco to other. With this script , I need to update different tables of subscriber and Account and do service agreement changes which has complex logic. Once each subscriber is migrated then I need to maintain status that "particular subscriber successfully Migrated". For this to achieve , I opened cursor for 190 k subscribers and each subscriber is migrated one by one in loop. While performance testing , I found only 19 k subscriber got successfully updated in 1.5. Hour. It is taking hell lot time and my deployment window is only of 3 hrs. Please advice me , how to proceed technically in such case.
190k records to process in modern databases really isn't a lot. It's hard though to give any general advice as what to do depends on the particular situation. My first guess is that as your target database grows during the migration you might suffer from tables stats logged as 0 resulting in FTS when doing UPDATES and checking integrity and unique constraints. Try loosening the constraints on the target. Monitor the execution plans on the target and if any are showing FTS or high IO then periodically gather stats, or use DBMS_STATS package to set the stats prior to the migration. Divide and conquer is another general approach you could take - paramaterise you migration routine so that you can operate on a range of records, then run the migration in parallel, with each one processing a different range. Perhaps you can also split the migration and validation processes, i.e. run the migration to copy the accounts, and set the status to "COPIED" (from "COPYING"), then have a validation process (possibly parallelised) which polls for "COPIED" records and then validates them setting the status to "CHECKING" then "OK" or "FAILED", or whatever.
More specific help requires more details.