process running slowly, lots of yield() calls ?

From: Andy <andyjgw_at_gmail.com>
Date: Thu, 20 Nov 2008 06:09:23 -0800 (PST)
Message-ID: <f7571f0a-c9cd-4b08-9d26-fd510754852e@v13g2000yqm.googlegroups.com>

We upgraded database from 9i to 10g at the weekend. One of the processes (whose statement basically consists of an "create table as select etc") is taking +7hrs and still running instead of the more usual 30 mins. OK, I would think that this is a statistics/explainplan  related issue and we have the vendor investigating.

However, what I am more curious about is the truss output on the spawned oracle process. It's peppered with yield() calls and I'm suspicious. Why would it be running a processor flat out, and yet yielding so much ? Can anyone throw some light on this please ?

sample:
...

yield()                                         = 0
times(0xFFFFFFFF7FFF4070)                       = 164268253
times(0xFFFFFFFF7FFF3FC0)                       = 164268253
yield()                                         = 0
yield()                                         = 0
pread(268, "0602\0\001 B M A0781F492".., 8192, 0x49A82000) = 8192
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
times(0xFFFFFFFF7FFF4070)                       = 164268870
times(0xFFFFFFFF7FFF3FC0)                       = 164268870
pread(266, "0602\0\00102 ^9407 pB9B3".., 8192, 0x4BD28000) = 8192
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0
yield()                                         = 0

...

truss -cpf 8881

syscall              seconds   calls  errors
times                    .00      60
yield                    .01     330
pread                    .01     163
                     -------  ------   ----
sys totals:              .02     553      0
usr time:             173.64
elapsed:              175.24

I know times() is used by Oracle to help in its timing statistics, but why the yield() so much ?

Thanks

AW Received on Thu Nov 20 2008 - 08:09:23 CST