Re: process running slowly, lots of yield() calls ?

On Nov 20, 9:09 am, Andy <andy..._at_gmail.com> wrote:
> 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/explain-
> plan 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

Since it looks like it is reading database blocks (8192 byte reads), could the yield calls be in place while waiting for the I/O to return? Are the disks slow? What is the event in Oracle on which the statement is spending the most of its time? I'm guessing single block I/O, unless your multiblock read count is set really low? Received on Thu Nov 20 2008 - 21:52:02 CST