Hi Tom,

Thanks in advance for your assistance with this one.

RDBMS: 10.2.0.4
OS: HP-UX PA-Risc

Users are complaining that a specific job is "sometimes" slow, normally this job would run in 10 minutes but sometimes it finishes in over an hour.

Tracing the job shows the following when the job takes more than 1 hour:

truncate table ARC4


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          1           0
Execute      1      0.83    3663.42        687       2277       2602           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.86    3663.45        687       2277       2603           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 21     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       689        0.03          1.97
  reliable message                                9        0.00          0.00
  enq: RO - fast object reuse                    44        2.94        106.30
  local write wait                             3868        0.99       3553.96
  free buffer waits                               8        0.03          0.06
  latch: cache buffers lru chain                  3        0.00          0.00
  log file switch (private strand flush incomplete)
                                                  4        0.10          0.27
  latch: cache buffers chains                     1        0.00          0.00
  latch: messages                                 1        0.00          0.00
  latch: shared pool                              1        0.00          0.00
  latch: library cache                            1        0.00          0.00
********************************************************************************

As opposed to the following when the job takes 15 minutes:

truncate table ARC4


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          2          5           0
Execute      1      0.27      70.01        666       2746       2610           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.30      70.05        666       2748       2615           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 21     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       666        0.02          1.32
  reliable message                                9        0.02          0.02
  enq: RO - fast object reuse                    12        2.94         12.28
  local write wait                              359        0.98         56.00
  latch: cache buffers chains                     1        0.00          0.00
********************************************************************************

As you can see here, the main wait event to tackle is "local write wait".
My understanding is that for Oracle to perform the truncate, it needs to scan the whole buffer cache checking for any buffers that belong to that object, and dump these dirty buffers on disk, then clean those buffers. Our DB_CACHE_SIZE is 1GB.

My questions are:

1. Apart from using Global Temporary Tables which is not an option because of business need, is there anything else you could think of with respect to this?

Would it be better if we use delete (no logging) from the table rather than truncate?

I am trying to find other alternatives than using truncate really.

The table is on a locally managed tablespace uniform allocation and manual segment space management. The block size for this tablespace is 4k


2. Might be a different question but it's actually related to the same job, developers are using the following logic in the job:

Select count(*) into VAR1 from Table1;

If VAR1 > 10000 then
...

End if


I know that you are totally against using count(*), but in this specific situation where they need to know if the table has a fixed number of rows to process a request, what other alternative do you recommend?

I was thinking of adding a column with a sequence but there could always be gaps in there.


Thanks Tom.