7.5 PS2 Upgrade Scripts Perform Unnecessary Work (Doc ID 1538327.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.5.0.0.0 and later
Information in this document applies to any platform.

Symptoms

During upgrade to 7.5 PS2 the upgrade scripts are doing full table scans of some tables to verify if a field exists in a table.Also the scripts spools the output in a text file in $PIN_HOME/tmp. This could potentially fill the filesystem when event_t table is very large.

Noticed that during upgrade there was the query "select SESSION_OBJ_ID0 from EVENT_T;" running that was doing a full table scan on event_t table. There is no need to full scan such big tables. The most efficient option would be to verify in user_tab_partitions if column exists for required table, alternatively a where rownum < 2 would avoid the full scan.

In the add_new_objects_75.pl script you can find:

} elsif ( $file_mod eq "dd_objects_event_def_taxes.source" )
{
$tablename = "EVENT_BILLING_TAXES_T";
$columnname = "TAX_CODE"; $column_exist_flag = "TRUE";
} elsif ( $file_mod eq "dd_objects_event.source" )
{
$tablename = "EVENT_T";
$columnname = "SESSION_OBJ_ID0"; $column_exist_flag = "TRUE";
}

...
and then

if($column_exist_flag eq "TRUE")
{
if (VerifyPresenceOfFieldName( $columnname, $tablename, %{ $DM{"db"} }) == 0)
{
# column does not exist.
$Result = 1;
print "$columnname not in $tablename. Script Execution is unsuccessful for $file_mod \n";
open( TMPFILE3, ">>$modifyobjectspinlog " )|| die "cannot read $modifyobjectspinlog \n"; $writestatement = "$columnname not in $tablename. Script Execution is unsuccessful for $file_mod \n";
print TMPFILE3 ("$writestatement \n") ; close(TMPFILE3);
last;
}
}

and in pin_oracle_functions.pl, the definition of VerifyPresenceOfFieldName functoin, it builds a query like

select $FieldName from $TableName;

This will do full table scan on event_t and event_billing_taxes_t, just to check if the field exists in the table. If in the VerifyPresenceOfFieldName we replace with one of the 2 options below the execution it will be very quick and will not require full table scan of potentially huge tables in production:

preferred option using database data dictionary and will not perform and read from the table:
select field_name from user_tab_columns where upper($TableName) = table_name and upper($FieldName) = column_name;

alternatively could be limited only to first row from table
select $FieldName from $TableName where rownum < 2;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms