Hi,
We have a query which is running for hours under normal conditions. The query runs monthly once.
I have tried the below steps to run it faster.
Try : 1
=====
We have gathered full stats with cursor invalidate option . but no Help
Try 2 :
====
We flushed shared pool for the particular query and tried. But no help.
Try :3
====
We flushed the shared pool of the database and tried. Its running in 2 hrs.
When we analyzed the sql plan, In good run it avoids the "TABLE ACCESS BY LOCAL INDEX ROWID TABLE " and picks "TABLE ACCESS BY GLOBAL INDEX ROWID TABLE " which makes the run faster.
I cannot push sql profile for this, since everytime the sql_id is changing for same query.
Please suggest if any other option is available to force the query to pick good plan.
Thanks.