Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Unanswered: performance problem ...help!!

    Hello All,

    I have a table having 30 fields and more than 50 million row,
    in one of the reuirement i have to fetch the rows based on date range.
    This query is taking more than half an our, please suggest,
    how do i improve the performace ??


    Thanks!!

    -Pd

  2. #2
    Join Date
    Oct 2003
    Posts
    20
    Hi,

    Define index over date fields

    Marcelo E.

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    Hello Marcelo,

    Thank you.
    It's a datamart and we have no contraints and no indexes on the same, hence the problem.
    Please suggest, what do i do in this situation?

    Thanks !!
    PD

  4. #4
    Join Date
    Oct 2003
    Posts
    20
    Hi,

    Two possible solutions.

    1. Redefine extent size for tha table, first unload the table , second drop table, create again the table with a new extent definition and load it.

    2. Physical reorganitation, to do that, only for once you must create a cluster index, after the data is reallocated, drop the index.



    Marcelo E.

  5. #5
    Join Date
    Sep 2003
    Posts
    22
    Hello Marcelo,

    Thank you.
    I will opt for 1st option, as i need to ask my superiors for creating index.

    Currently the extent allocation is done like this:

    extent size 16 next size 16 lock mode page;

    for all the tables.

    What should i change it to ? shall i make it big 32 ?

    Thanks!!
    PD

  6. #6
    Join Date
    Oct 2003
    Posts
    20
    To give an correct extent size, give me your table structure, but, I insist, use an index in that fields, evaluate how many time take: create an index before your select run and your select run, versus original time to select run.

    Marcelo E.

  7. #7
    Join Date
    Sep 2003
    Posts
    22
    Hello Marcelo,

    Thank you.
    This is the status info of the table

    row size 167
    no. of row 47150521
    columns 37

    This table is fragmented by expression.
    Just wondering how much performace will be gained by index.

    In where clause i am including, fragment and date.

    Thanks!!
    PD

  8. #8
    Join Date
    Oct 2003
    Posts
    20
    Hi,

    The estent size must be 512, to store 3066 rows aprox. , and the next size must be 256, when query read that rows, the time will be reduced.

    The unload and load task, put all data together.

    Don´t forget see onconfig DDS parameters to improve performance.

    Marcelo E.

  9. #9
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Given your extent size I'd guess that that large a table is fragmented amoung hundreds of extents. If you're going to unload and reload the table you need to make your initial extent size large enough to hold the entire table or an entire fragment (if fragmenting). Next extents should then be a percent based upon expected growth - usually 25% if the table is going to grow at all. The game, if you can play it, is to have the table and a years worth of growth in <10 extents.

    Next question, you are fragmenting, why? With no index all you're accomplishing is to have multiple scans going that are walking through every record looking for your date criteria. Unless you've laid out your fragments on disk perfectly, you're just thrashing against each other.

    I think you need to rethink how this table is structured vs. queried.
    Fred Prose

  10. #10
    Join Date
    Sep 2003
    Posts
    22
    Thank you, Fred.

    I was trying to unload the data and gives me problem of filesystem full.
    Is there any way i can break the file and give path from different filesystem?

    I never tried this with unload, but external table is there any way giving different files for unloading data ? May be i will try one of them.

    As this is kind of data warehousing/datamart but the updates are done on daily basis. So, we have to be careful on next extent size as well.

    But as this is test table for me i can change it to appropriate extent size for me. Daily updates are not done on this table.

    Regards,
    PD

Posting Permissions

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