I am facing an intriguing situation with a Oracle database.
A stored procedure is exhibiting a significantly high execution time, and I am exploring ways to optimize it. To test on the production database with minimal risk of impact, I created a copy of this procedure for optimization purposes. However, before making any modifications to the procedure, I noticed that its execution time, with the same parameters, is much faster compared to the original procedure. To emphasize: nothing was changed, only a copy was created.
I attempted to recreate the original procedure, but the sluggish performance persists.
I lack in-depth knowledge of how a procedure is compiled and executed in the database to formulate any hypothesis.
What could be the possible explanation for this phenomenon?
It appears be an error in the library cache. Have you tried restarting the database?