Repair is slow due to wrong Hint usage (4233830)

Repair is slow due to wrong Hint usage

Originally, if user specifies Oracle hints, those one are also used in Repair phase. In this case, the inefficient SQL Plan is chosen and eventually Repair time would be very high.

select /*+ FULL(A) PARALLEL(A 10) */ distinct rowid, ....

where ROWID IN (SELECT OOS_ROWID FROM SHAREPLEX_DATAEQUATOR_UPD_TEMP ) order by 2 ......

From SQL Plan information, FULL Table scan is used for the main table which is not efficient way.

Repair SELECT statement should not use the user specified hints at all. There is no reason to access the full table, instead Repair should visit the table from the rowid information driven by SharePlex internal table(SHAREPLEX_DATAEQUATOR_UPD_TEMP).

Workaround: None.

Status : Fixed by a patch ( ST111178 ). It will be included in next release(8.0.8).

With this patch, Repair would not use the provied hints at all. Finally the main table is access by the relevant rowid information.

This patch also includes the improved hints for DELETE statement of the Repair.

Contact Support to get the patch.