Why Is My Query Sometimes Slower Than Other Times with Higher Consistent Gets Although No Change in Execution Plan? (Doc ID 1558349.1)
SYMPTOMS
A new query is tested on a test database. When the same query is tested in a production environment, the elapsed time is higher.
CHANGES
The procedures in package DBMS_XPLAN indicate no change in execution plan between the two environments. The test environment is restored back-up of the production database with the same volume of data.
CAUSE
The difference is between executing the query in an isolated environment versus within an environment in which DML is being applied to the table while the query is executing.
SOLUTION:
The activity described above is normal and necessary activity required for Oracle Database to maintain consistency of data. The only real way to solve this issue is one of following options:
1.Schedule DML activity separately from reporting activity.
2.If slight staleness of the data may be tolerated, then perform reporting activity upon a materialized view or upon a read-only copy of the production database which may be refreshed on a routine schedule.
3.Execute reports against a Data Guard Standby Database opened in read-only mode.
