Cancel of a long-running, latch-holding query takes long (8i) - why?
- From: Peter Alteheld <palteheld@xxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Wed, 29 Jun 2005 10:36:57 +0200 (CEST)
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
---------------------------------
Gesendet von Yahoo! Mail - Jetzt mit 1GB kostenlosem Speicher
Other related posts:
