Query execution time vary time to time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query execution time vary time to time

  1. #1
    Join Date
    Jun 2002
    Posts
    3
    gurus,

    i have a query with "union" and over the time the execution time is changing. somethimes the execution time vary with more than 2 minutes. It happends randomly w/o any pattern. No statistics are being analyzed between the slow down occurances, moreover the query does not use any bind variables.

    my execution plan is:

    SELECT STATEMENT Cost = 6108
    SORT UNIQUE
    UNION-ALL
    TABLE ACCESS FULL
    NESTED LOOPS
    TABLE ACCESS FULL
    TABLE ACCESS BY INDEX ROWID
    INDEX UNIQUE SCAN

    I have checked the sort_area_size and sort_area_retained_size, sorts memory/disk ratio, library cache hit ratio, and they all looks ok.

    anyone has any idea what might causing this to happend?

    thank you in advance for looking into this issue.
    vincent



  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What are these parameters DBWR(s), db_file_multiblock_read_count, and optimizer_mode set to?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2002
    Posts
    3
    They are:

    db_file_multiblock_read_count = 16

    db_block_buffers = 51200

    shared_pool_size = 209715200

    large_pool_size = 614400
    java_pool_size = 20971520

    parallel_max_servers = 5

    log_buffer = 10485760

    optimizer_mode=choose

    db_block_size = 8192

    DB_WRITER_PROCESSES=1


    Oracle version is 8.1.6.0.0 and it is Windows NT 4.0 SP6.

    vince

    [Edited by vince on 06-11-2002 at 12:52 PM]

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Try increasing your DB writer process. Also remember to increase your latches in parallel. For every thing, I would suggest that you run a statspack and gather the stats to make a wise decisions...

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Jun 2002
    Posts
    3

    Cool

    What is the idea of increasing DBWR processes? My query gets randomly executed with diff times. It is not constant issue.

    I am not convinced that is the right way. Any one else experienced same issue ??

    vincent

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    If the database is a very active one, then what happens was that the one DBWR process that has to do the work of reading and writing. If your reports are going to put in some load the DBWR would be a slowdown point. This would be the general notion. Now further, as I said above, I strongly would suggest that you run the statspack during the regular time and one when running the report. then do an analysis and see where exactly the cholk point comes from.

    Hope this would explain.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    we do because we run queries at different time, sometimes morning sometimes afternoon and sometimes in earli morning, early morning runs faster

    we have a materiliazed view, takes 30 secs to refresh at 3:00am, 2 minutes at 12:00pm

    btw your

    db_file_multiblock_read_count is a bit big, I cannot assure you is too big because I dont know your OS max I/O but seems a bit big...

    log_buffer is definitely too big, more than 3MB wont give you extra benefit

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