| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Cancel of a long-running, latch-holding query takes long (8i) - why?
Hi List,
we had a cancelled query that held 'cache buffer chain' latches on some objects and took a lot of cpu consumption. The time for the cancel operation takes in our production db (8.1.7) about an hour, on a dev db (8.1.7) 30 min and on an upgraded db (10.1.0.2) only some seconds. While we are not able to upgrade production very soon, I am trying to figure out a solution for 8i.
I was able to work around the latch problem by using freelist groups on those objects. But still I am not happy with that cpu usage ogf the cancel operation. We can trigger this also by killing the process on the os level. Then PMON starts and puts as much load on the cpu and as long as before. In production it was a job that broke.
An extended 10046 sql trace only shows all the disk read wait events until I cancel the query and then a single FETCH line of the time where I did the cancel. I just did a 'systemstate level 10' trace but I can't read from it what the session is doing. I suspect the db is doing a latch cleanup operation - but why does that take so long and is there a way to accelerate it?
Peter
 |
 |