7.5 PS2 Upgrade Scripts Perform Unnecessary Work

(Doc ID 1538327.1)

Last updated on DECEMBER 14, 2017

Applies to:

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


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);

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;




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