10g optimizer

Another day the 10.2.0.1.0 face issues with 10g optimizer

Issue:
Same Query is not slow

Fix:
Put '/*+ RULE */ hints, even thought RULE is no longer supported, this has trully fixed.

Also note same db in office, one verrion 10.2.0.1

Update in parallel mode

Change overview and monitoring

alter table mytable parallel 5;

-- As system:
select pid, status, server_name from v$px_process;


My request for change:

Hi,

Since we are upgrading to v3 for many of our customers, so far Customer_A and Customer_2 and Customer_Big in progress.
One thing clearly stood out was that the script "rel_2_0_4_field_answers_upgrade.sql" took around 1hr for Customer_B, 6hr for Customer_A and a massive 12hr for Customer_Big.

I would like to propose a change to this script (hopefully it will speed up the script without changing any biz-logic), due my lack of knowledge of the application and data model, I am not 100% sure if my change impacts any business logic.

Can someone please take this proposal forward and see if we can go ahead with this, extensive testing required.

Since yesterday, I have done two test runs with Customer_Big (slim db from last Friday, note 7.5 million rows on field_answers), both showed similar results.

Old script took: 4 hour (or 3hr 55min)
New script took: 30 minutes

Please allocate a developer to look into this suggestion, ideally the one who wrote the original script.
For your information this script was our biggest blocker for the Customer_Big migration.

Proposed New Script (using parallel execution):

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

alter table field_answers parallel 5;

UPDATE field_answers set
id='FN' || lpad(keygen('FN'),8,'0'),
type=decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA')
WHERE id is null;

alter table field_answers parallel 1;


Current Live Script:

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

DECLARE
CURSOR c1 IS SELECT rowid, decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA') as fatype FROM field_answers where id is null;
faid varchar2(10);

BEGIN
FOR i IN c1 LOOP
BEGIN
faid:='FN' || lpad(keygen('FN'),8,'0');
EXECUTE IMMEDIATE 'UPDATE field_answers set id=''' || faid || ''', type=''' || i.fatype || ''' WHERE rowid = ''' || i.rowid || '''';
END;
END LOOP;

END;
/