Need help in Performance - execution plan got changed !!
I have third party application running on Oracle 10gR2 db. Last week one of appilcation job using excessive CPU and taking way longer to complete. I mean from 30 miin usual to 260 min.
Based on further troubleshooting I found out that now it uses diff. execution plan !!
job/sql contain few bind variables and it schedule to run every morning which executes total ~1300 times.
I checked involved statistics for those objects( table and indexes) and they were not being updated in month (means last time we ran stats on those tables month ago).
no parameter changes in Oracle side as well as code remain same (this job has history of running > 3 years without an issue).
Based on further troubleshooting I found out that now it uses diff. execution plan !!
job/sql contain few bind variables and it schedule to run every morning which executes total ~1300 times.
I checked involved statistics for those objects( table and indexes) and they were not being updated in month (means last time we ran stats on those tables month ago).
no parameter changes in Oracle side as well as code remain same (this job has history of running > 3 years without an issue).
0