Re: Inserts waiting on enqueue

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 26 Aug 2009 04:23:47 -0700 (PDT)
Message-ID: <83ef069d-8a93-463c-866a-784c7ebaa15c_at_i8g2000pro.googlegroups.com>

Thanks for responding

Enqueue stats section:

                                                        Avg Wt
Wait
Eq     Requests    Succ Gets Failed Gets       Waits   Time (ms)

Time (s)
-- ------------ ------------ ----------- ----------- -------------

---

TX        7,923        7,923           0         866
12,420.35       10,756
SQ           86           86           0          29
203.14            6
CF          623          623           0           3
23.67            0

So inserts were waiting on TX enqueue.

Large-scale deletes: there is a daily purge job that runs approx between 2:30 am and 3 am. It runs every day and deletes 1/90 of the table as we keep 90 days. The enqueue problem was observed around 9am.

Yes, this is array insert, with 7.2 rows per execution:

                                                       % Snap
                     Statement Total      Per Execute   Total
                     ---------------  ---------------  ------
        Buffer Gets:         288,278             91.8   25.38
         Disk Reads:          77,650             24.7   11.61
     Rows processed:          22,717              7.2
     CPU Time(s/ms):               7              2.1
 Elapsed Time(s/ms):          10,349          3,295.9
              Sorts:               0               .0
        Parse Calls:              27               .0
      Invalidations:               0
      Version count:              32
    Sharable Mem(K):             308
         Executions:           3,140

The table has 3 indexes: primary key and 2 non-unique indexes. Received on Wed Aug 26 2009 - 06:23:47 CDT