Truncate is slow on Oracle 9.2.0.6.0 (HP-UX)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Truncate is slow on Oracle 9.2.0.6.0 (HP-UX)

  1. #1
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245

    Truncate is slow on Oracle 9.2.0.6.0 (HP-UX)

    Hi,
    Just 2-3 days back i started facing this strange problem.
    If you try to truncate empty table it takes too long. Prev'ly it used to happen in fraction of seconds. I am running Oracle Oracle 9.2.0.6.0 on HP UX.

    I traced the session which truncates empty table found following waits are coming..


    HTML Code:
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.03          0          0          0           0
    Execute      1      0.09     508.50         32        154         18           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.09     508.53         32        154         18           0
    
    Misses in library cache during parse: 1
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                         5        0.00          0.00
      enqueue                                        62        2.94        180.51
      rdbms ipc reply                                81        1.96        156.03
      local write wait                              171        1.01        168.37
      log file sync                                   1        0.07          0.07
      SQL*Net message to client                       1        0.00          0.00
      SQL*Net message from client                     1       44.09         44.09
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       22      0.02       3.33          1          3          0           0
    Execute     25      0.04       0.10          8         14          4           4
    Fetch       20      0.00      40.31         22        145          0          17
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       67      0.06      43.75         31        162          4          21
    
    Misses in library cache during parse: 12
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                        30        0.02          0.14
      free buffer waits                              43        0.99         40.19

    Out of which free buffer waits i can take care of but i want to know
    what is "local write wait" i searched for it a lot didnt got anything..

    Also on metalink i found one bug Note:287429.1
    " Bug 3282805 TRUNCATE table scans entire buffer cache for small tables in 9.2.0.4"
    They say 9.2.0.5 includes fix for these bugs but mine is 9.2.0.6 then why i should i get this problem.

    Does any body else facing any kind of these problems.

    Thanks in Advance...

    Rgds
    Parag

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    how many extents is it having to de-allocate?

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    local write wait could indicate a physical storage problem. I had this problem a short time ago and found a faulty disk controller was to blame... Anyway, I found plenty on this when I googled...

    https://metalink.oracle.com/metalink..._id=183745.995
    Assistance is Futile...

  4. #4
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    I am triying to truncate plan_table which has following details...

    SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
    --------------- ---------- -------------- ----------- ----------- -----------
    PLAN_TABLE 1 65536 1 2147483645
    Davey thanks for consideration. I was just serching on this forum also . I found one very old thread raised by you on the same lines. But in your case extents were more. In my case only one extent is there that to of 64k so i am going crazy.

    Rgds
    Parag

  5. #5
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Sorry i am pasting o/p again.....

    HTML Code:
    SEGMENT_NAME     EXTENTS    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS  MAX_EXTENTS
    --------------- ---------- -------------- -----------  -----------   -----------
    PLAN_TABLE        1          65536                       1           2147483645

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width