[CDrozdowski]

Hi Chris,
I have indentified 3 bottlenecks in our v4.5 upgrade/migration process. Here is the first.

Background: We have performed two test upgrade/migrations on non production databases.

Bottleneck 1: preupdate 38940 from dbupdate_45.sql

FROM the dbupdate_45.sql script log:

Test DB 1 MONITOR_AGENT table: 19 Million Records

START of preupdate38940 07:41:34 (editted some input here....)

Pop of MONITOR_AGENT.IS_TOLERANCE_RULE_ACTIVE for value 'N'…...4 Hr 52 Mi 19 Sec

END of preupdate38940 Elapsed Time: 4 Hr 56 Mi 8 Sec


Test DB 2 MONITOR_AGENT table: 8.5 Million Records

START of preupdate38940 22:38:29 (editted for size...)

Pop of MONITOR_AGENT.IS_TOLERANCE_RULE_ACTIVE for value 'N'.........2 Hr 3 Mi 12 Sec


END of preupdate38940 Elapsed Time: 2 Hr 5 Mi 36 Sec


SLEUTHWORK:


FROM create_dbupdate_45.sql

PROCEDURE preupdate38940 IS
BEGIN
ALTER_TABLE.ADD_COLUMN('MONITOR_AGENT','CONTEXT_SA VED_QUERY_GID','VARCHAR2(101)');
ALTER_TABLE.ADD_COLUMN('MONITOR_AGENT','TOLERANCE_ RULE_GID','VARCHAR2(101)');
-- ALTER_TABLE.ADD_COLUMN('MONITOR_AGENT','IS_TOLERAN CE_RULE_ACTIVE','VARCHAR2(1) DEFAULT ''N'' NOT NULL');
ALTER_TABLE.ADD_COLUMN('MONITOR_AGENT','IS_TOLERAN CE_RULE_ACTIVE','VARCHAR2(1)');
ALTER_TABLE.POP_AND_NOTNULL('MONITOR_AGENT','IS_TO LERANCE_RULE_ACTIVE','''N''');
ALTER_TABLE.MODIFY_COLUMN('MONITOR_AGENT','IS_TOLE RANCE_RULE_ACTIVE','DEFAULT ''N'' NOT NULL');


ALTER_TABLE.ADD_COLUMN('MONITOR_PROFILE_DELETED',' MONITOR_PROFILE_TEMPLATE_GID','VARCHAR2(101)');
ALTER_TABLE.ADD_COLUMN('SAVED_QUERY','IS_HIDDEN_FO R_MILESTONES','VARCHAR2(1) DEFAULT ''N''');
END preupdate38940;


I traced this to the procedure POP which is called by POP_AND_NOTNULL that is part of the ALTER_TABLE package.

PROCEDURE pop (p_table varchar2, p_column varchar2, p_value varchar2, p_where_clause VARCHAR2 DEFAULT NULL,
P_COMMIT_RATE INTEGER DEFAULT 3000) is
sql_statement varchar2(5000);
tmp_tab varchar2(100);
start_time DATE;
end_time DATE;
begin
start_time := SYSDATE;
tmp_tab := create_rowid_table (p_table,p_where_clause);
sql_statement :=
'DECLARE '||
' CURSOR C1 IS SELECT ROW_ID FROM '||tmp_tab||';'||
' BEGIN '||
' FOR C1_REC IN C1 LOOP'||
' UPDATE '||p_table||
' SET '||p_column||' = '||P_VALUE||
' WHERE ROWID = C1_REC.ROW_ID; '||
' GLOG_UTIL.COMMIT_IT(c1%rowcount, '||p_commit_rate||');'||
' END LOOP; '||
' COMMIT; '||
' END;';
execute immediate sql_statement;
drop_rowid_table(p_table);
end_time := SYSDATE;
print_time(start_time,end_time,'Pop of '||p_table||'.'||p_column||' for value '||p_value);
exception
when others then
glog_util.print_it(sql_statement);
glog_util.print_it(SQLERRM);
raise;
END pop;

DISCUSSION:
I understand the design and intent of the ALTER_TABLE package...
I think the issue for us is the extremely large table volume in the MONITOR_AGENT table (8.5 and 19 million in test respectively, 10.5 million in production)

I think we would get better performance is we could increase the value for commit count so that we were committing less frequently but bigger batches... the default is 3000.

One problem I see is that POP is called in POP_AND_SETNULL and there is not way to pass a commit count parameter..

Do you have any ideas or suggestions,
thanks !
catharine

[chrisplough]

Catharine,

You've hit exactly on the main issues that our other clients are experiencing while preparing to migration from older OTM versions (4.0,4.5). There's a multi-pronged approach that we normally take to bring these migration windows down into something more acceptable:

1. Purge as much data as possible. Many clients don't have a data retention policy and the OTM databases get out of control. This improves all-around performance as well as migration times.
2. Ensure that your DB is running as efficiently as possible, both from a hardware perspective and an Oracle DB tuning perspective. For instance, our new Linux servers with fast disk arrays run many of these intensive scripts over twice as fast as HP-UX, Solaris and AIX boxes from only a year ago. We've also removed indexes in the past and re-created them post-migration in order to speed up the process.
3. Tune the migration scripts, just as you've suggested.

So - directly to your point, great work in tracking this down to the pop procedure! I definitely agree that increasing the commit count is a great way to decrease the processing time. While I'm not familiar with this procedure directly, I can't see any reason that altering the procedure, if only for the duration of the migration, wouldn't help significantly. I can't think of anything in the migration process that would be adversely affected by this - as long as you have enough rollback and temp space available.

Are you at the point where you can give this a try and report back your results?

Thanks!
--Chris

[chrisplough]

I'm sure you know this, but I just tracked down the ALTER_TABLE package to the following script:

<otm_install_dir>/glog/oracle/script8/create_alter_table_package.sql

Which in turn calls:

<otm_install_dir>/glog/oracle/script8/pkg/alter_table.pks
<otm_install_dir>/glog/oracle/script8/pkg/alter_table.pkb

So by editing the alter_table.pkb file and re-running the create_alter_table_package.sql script, you should be able to define a different default commit rate.

--Chris