Dear All,
My database is Oracle 11gr2 (11.2.0.4) Enterprise Edition installed on Windows 2012 Standard Edition.
Recently some of my queries (Only some queries, not the whole database) that were quite efficient before degraded in performance. These queries used to get execute in 1 second and now were taking 20 to 30 seconds.
I solved the issue by creating a tuning task and passing SQL_ID using DBMS_SQLTUNE.CREATE_TUNING_TASK package. The query was efficient once again once I accepted the finding and changed SQL Profile.
This was the second issue within 6 months. I need to know why did it happened? Why SQL profile was changed suddenly? How to avoid this happening in future. Please note that for these particular queries there was no alteration made to under lying tables/objects. Also the data was not inserted or deleted in bulk. Normal transactions are happening.
Looking forward for your kind reply.
Regard, Imran