ORA-12847 select parallel 遇到rebuild index

Goal

Error upon running a custom BI report. Fails with below error.

ORA-12847: retry parsing due to concurrent DDL operation.
 

Solution

DB Instance 2 was coming up with a different action plan than DB instance 1,which was preventing the SQL running in parallel across both instances. Issue was resolved after flushing the shared pool in both the instances.

ALTER SYSTEM FLUSH SHARED_POOL;

There is no need of a downtime or for users to logout from the application in order to flush the shared pool.

因为我在1号机rebuild index，同时在 1号机 parallel select，

ORA-12847 由于执行计划不同，在另一个机器上产生的是全表扫描，一个机器上产生的是index scan？ 但我两个预计是在同一个node执行的。有点不同。

Symptoms

When a Procedure that was executed successfully in 12.1.0.2 is executed in 12.2.0.1, ORA-12847 occurs.

Changes

Upgrade 12.1.0.2 to 12.2.0.1

Cause

This message was added in BUG 22494599.
In versions prior to 12.1.0.2, it was treated as the same error number as ORA-12842, which occurs when a cursor is invalidated during parallel query execution.
Therefore, it was added in this Bug so that you can see that re-parse was executed in PL/SQL.
If reparse is performed 10 times, ORA-12842 will occur.   10次是Oracle 策略