|
View previous topic :: View next topic
|
| Author |
Message |
saurabh39
Warnings : 1
Active User
.jpg)
Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur
|
|
|
|
Hi All,
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 -
| Code: |
Getpage Requests = 1001M Failed Getpage Requests = 0
Synchronous Read I/O = 45183 Getpage/Read I/O = 22159.25
Page Updates = 57103 Seq Prefetch Requests = 206075
List Prefetch Requests = 4074 Dynamic Prefetch Requests = 98400K
Prefetch Pages Read = 6525225 Prefetch Pages In Hiperpool= 0
|
6. Wait time is not large, that means my SQL is getting CPU.
| Code: |
Synchronous I/O Wait 45183 00:01:17.843
Asynchronous Read I/O Wait 193907 00:02:57.242
Asynchronous Write I/O Wait 0 00:00:00.000
Local Lock/Latch Wait 159625 00:00:05.557 |
I have contacted my DBA/Performance team, but of no help.
Can anyone tell me, what else I can do.I am at my wit's end.
Code'd |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| i would imaginge the third line of your WHERE clause is the culprit |
|
| Back to top |
|
|
saurabh39
Warnings : 1
Active User
Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur
|
|
|
|
| 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. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What has changed in the environment since it worked "normally"?
Does it always take the 10 hours now, or was this a one-time problem?
| Quote: |
| I know my stuff and I know what query I wrote and I know there is nothing wrong in the query |
We do NOT know this . . . Rather than complaining about the reply given, why not simply post the problem query? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Tushar,
* 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.
Regards,
sushanth |
|
| Back to top |
|
|
|
|
