Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Posts
    24

    Unanswered: Help: Dealing with large tables.

    I have an intersection table comprised of 3 fields (two foreign keys and a third field containing floating points of various precision). I'm running Oracle 10g Standard Edition One on Windows server 2003.

    The table contains 44 million rows, and will only get bigger as time goes on. I've been running into some strange performance issues with this table. Queries will occasionally hang for long stretches of time, unpredictably. My databrowser applications behave strangely when scrolling through datasets queried from this table (as you would in Oracle Sql Developer's data window, for example). They will spontaneously freeze up for long periods of time, before eventually letting me continue to scroll.

    I have no previous experience dealing with such large amounts of data. I'm assuming this table is in serious need of partitioning, but I'm not sure what the size of each partition should be. Any advise would be appreciated.

    Here are some table details:

    Code:
    SQL> desc descriptors;
     Name                                      Null?    Type
     ----------------------------------------- -------- -------
     CHEMISTRY_ID                              NOT NULL NUMBER
     DESCRIPTOR_ID                             NOT NULL NUMBER
     VALUE                                              NUMBER
    And some sample data:

    Code:
    SQL> select * from descriptors
      2  where chemistry_id < 3;
    
    CHEMISTRY_ID DESCRIPTOR_ID      VALUE
    ------------ ------------- ----------
               1             1   4.124234
               1             2     .23423
               1             3          1
               1             4          0
               1             5          0
               1             6          0
               1             7    1.45345
               1             8   7.345345
               1             9      3.223
               2             1      .4356
               2             2          0
    
    CHEMISTRY_ID DESCRIPTOR_ID      VALUE
    ------------ ------------- ----------
               2             3          0
               2             4    1.34534
               2             5    3.76867
               2             6     3.2342
               2             7          0
               2             8          1
               2             9          2
    
    18 rows selected.
    
    SQL>
    Last edited by Narwe; 06-11-07 at 16:00.

  2. #2
    Join Date
    Jun 2007
    Posts
    3
    I do not believe partitioning is an option with Standard edition, and it cost extra for Enterprise edition. It may be a buffer size issue. How much memory do you have to use and how much is allocated to buffers? Are you using automatic memory allocation?

  3. #3
    Join Date
    Jan 2007
    Posts
    24
    The server itself has 1GB of RAM. The workstation I use to query the database has 4GB of RAM. I assume Windows reserves half of whatever RAM is available, leaving me with very little RAM on the server (500MB) and 2G on the workstation. I have not setup automatic memory allocation. Should I play around with that?

    Edit: For the record, I didn't realize the server had so little memory on board until now. I had always been under the impression for our network people that it was much more robust.
    Last edited by Narwe; 06-11-07 at 17:25.

  4. #4
    Join Date
    Jun 2007
    Posts
    3
    I would recommend trying to use automatic memory allocation. I doubt this has anything to do with the client's memory.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Ready, Fire, Aim is not an effective troubleshooting approach; while hoping to get lucky.
    IMO, if you do NOT have any verifiable metrics indicating a problem, you should NOT be making any change(s).
    It is YOUR database, so you are free to dig your own grave.

    IMO, size is relative & 44 million is NOT a large table.
    I have 1 production table that contains 31+ billion rows

    HTH & YMMV!
    Last edited by anacedent; 06-11-07 at 17:38.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2007
    Posts
    24
    Quote Originally Posted by anacedent
    Ready, Fire, Aim is not an effective troubleshooting approach; while hoping to get lucky.
    IMO, if you do NOT have any verifiable metrics indicating a problem, you should NOT be making any change(s).
    It is YOUR database, so you are free to dig your own grave.

    IMO, size is relative & 44 million is NOT a large table.
    I have 1 production table that contains 31+ billion rows

    HTH & YMMV!
    Ok. Can you tell me from your own experience what you may or may not have done to tune the performance of your 31+ billion row table?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what you may or may not have done to tune the performance of your 31+ billion row table?
    You do NOT tune tables!
    You tune specific SQL statements that are 'slow' by using SQL_TRACE & TKPROF.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2007
    Posts
    24
    Haha. OK! Obviously this is somewhat new to me. No need to be so grumpy. Anyway, I appreciate the advice.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Mentioning databrowser application, is it some kind of a (Oracle) form? If so, Forms Builder allows you to specify minimum number of records buffered in memory during a query in the block. By default, it is set to 0. If you (or your developer(s)) can adjust it, try to do so and see how it behaves. It can found in the block Property Palette, Records section, Number of Records Buffered property.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    One thing that you should really be looking into is to increase the memory on the database server. while your table is not very large, with more memory, the entire table will be held in memory and will be MUCH more responsive. One gig with the overhead of the O/S is not very much.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    Narwe:
    this is more complex problem.
    - please insert ddl for indexes - use you composite indexes?
    - is application written with bind variables?
    - how many distinct ID exists in table? CHEMISTRY_ID, DESCRIPTOR_ID?
    - is CHEMISTRY_ID DESCRIPTOR_ID combination unique ? use IOT
    - not is used some stupid query, which force full table scan?
    - how frequently are captured statistics?
    ----------------------------------------------------------
    my recommendation is: use statspack report in 15 minutes snapshots and identify top x consumer select. may be that problem is in data model.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  12. #12
    Join Date
    Jan 2007
    Posts
    24
    baloo99:

    Yes, it's a composite index.

    No, the application is not written with bind variables; however, the database is not heavily trafficked and very, very rarely will the same query be used more than once and not be preserved as a view.

    A typical query on the table looks like the following:

    Code:
    SELECT descriptors.CHEMISTRY_ID, descriptors.DESCRIPTOR_ID, descriptors.VALUE 
    FROM descriptors 
    WHERE descriptors.CHEMISTRY_ID IN 
    (SELECT CHEMISTRY_ID FROM <some table> WHERE <some clause>)
    AND descriptors.DESCRIPTOR_ID IN 
    (SELECT DESCRIPTOR_ID FROM <some table> WHERE <some clause>);
    There are 400K distinct CHEMISTRY_ID, and growing. There are 450 distinct DESCRIPTOR_ID, and growing.

    I am not capturing statistics at all. The take-home message of this thread is to start using statistics gathering tools. I will also be upgrading the RAM on the server.

    Thank you all for the replies.
    Last edited by Narwe; 06-12-07 at 10:47.

  13. #13
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    Narwe:
    how many rows is returned by query?
    not is solution reduce of number of rows? for example instead IN clause use "="?
    what is execution plan of this query ?
    what is 5 top wait event on this db?
    when you upgrade ram, may be that top wait event not will be full table scan but wait for cpu.

    -------
    but you can create index over CHEMISTRY_ID column

    and check in all_tables, if some table not was analyzed in empty state.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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