I have a job that normally runs for 45 min but today it is executing for more than 10 hrs. I did the following -
1. Checked the table to see when Runstat was last done, it was done 2 days back.
2. Implemented the module again and rebounded the plan. We cannot just bind the package in our production site, so had to implement the program.
3. Did the explain of the query in test region, it gave matching index scan. I have asked for explain result in production, but it is taking hell lot of time for DBA team to get back to me.
4. I checked Omegammon, following are the findings
BP Hit Percentage = 99.3%
Which I think means that we are getting data from buffer pool.
5. But I see a huge getpage count -
dbz.. with all due respect, I was not asking what went wrong with the query. I know my stuff and I know what query I wrote and I know there is nothing wrong in the query. I was asking, if there are any troubleshooting step which I missed. I request you to please keep your sarcasm with yourself for some other time.
* Orginally when was the 45min program implemented(date) in production
* When did the 45min program became 10hr program(date)
* Did any rebind/auto-rebind happen in-between, auto-rebind can happen J-I-C any table in the query was altered. If so, statisitcs might have got changed for the tables because of which accesspath changed.
Check MSTR log to find out if auto-bind has happened.