V45 Update/Migration Questions Part 2

[CDrozdowski]

Hi Again.
Second big bottleneck...any ideas and suggestions are appreciated:

From dbmigrate_45 Log:

_______
START of mig38940 14:39:11
END of mig38940 Elapsed Time: 7 Hr 44 Mi 16 Sec





From create_dbmigrate_45.sql script:

PROCEDURE mig38940 is
CURSOR c_mat IS
SELECT tolerance_rule_gid,
monitor_agent_gid,
is_active
FROM XX45_MONITOR_AGENT_TOLERANCE;
BEGIN
FOR mat_rec in c_mat LOOP
update monitor_agent
set tolerance_rule_gid = mat_rec.tolerance_rule_gid,
is_tolerance_rule_active = mat_rec.is_active
where monitor_agent_gid = mat_rec.monitor_agent_gid;

GLOG_UTIL.COMMIT_IT(C_MAT%ROWCOUNT,100);

END LOOP;
COMMIT;

UPDATE saved_query
SET is_hidden_for_milestones = 'Y'
WHERE saved_query_gid in (SELECT saved_condition_gid from monitor_tolerance_rule);

COMMIT;

UPDATE MONITOR_AGENT
SET MONITOR_AGENT_TYPE_GID = 'PLANNED'
WHERE MONITOR_AGENT_TYPE_GID IN ('UNPLANNED','EXCEPTION');

DELETE FROM MONITOR_AGENT WHERE MONITOR_AGENT_TYPE_GID = 'EXECUTION';
COMMIT;

DELETE FROM MONITOR_AGENT_TYPE WHERE MONITOR_AGENT_TYPE_GID IN ('UNPLANNED','EXCEPTION','EXECUTION');
COMMIT;

DELETE FROM MONITOR_PROFILE MP
WHERE NOT EXISTS
(SELECT 'X' FROM MONITOR_AGENT MA
WHERE MA.MONITOR_PROFILE_GID = MP.MONITOR_PROFILE_GID
AND MONITOR_AGENT_TYPE_GID = 'PLANNED');
COMMIT;

END mig38940;

ISSUE: again we have large MONITOR_AGENT table....how to change the commit count.... I am tempted to just modify the value.... any issues

catharine

[chrisplough]

Re: V45 Update/Migration Questions Part 2

Catharine,

For me, this one is an easy choice - I would definitely increase the commit value from 100 to something considerably higher. As long as (and I know you are) testing the migration process prior to doing it in PROD, there is nothing to lose!

--Chris