High table INSERT perfomance - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: High table INSERT perfomance

  1. #11
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Firstly people, I must say I'm extreamly please with the feedback you are providing. I also made a similar posting to Oracle's Metalink site and received only 1 reply (I'm glad that person did reply, but no reposne from Oracle). Now, let me examine your feedback.

    Table structure.

    Parent table

    ID, TYPE, DATE_CREATED

    Child Table

    ID (Forein Key), PID (Primary), TAG, TEXT, NUMERIC, CID (Child).

    Basically, for every parent reco there are at least 50 children records. Becasue Oracle INTERMEDIA can't index (to be queried/ordered) a numeric value's asshole, I've been forced to try this as an alternative method.

    Basically splitting the tags for each record into the table. With their corresponding tag values, in either TEXT or NUMERIC fields.

    Zaggy.

    1. Parallel inserts (Increases the use of multiple CPUs)

    If I an inserting from only one process (i.e only one session is inserting these rows, am I able to parralel insert. And if there is ref integrity, would parrallel be affected.

    2. Set LOG_CHECKPOINT_INTERVAL to a huge setting, thus turning it off

    Check

    3. Set LOG_CHECKPOINT_TIMEOUT to 0, thus turning it off

    Check

    4. PCT_USED to 0 (This gets rid of freelists)

    Not quite sure the benefit of this, I ned to reuse the blocks space when it come available, if there are no freelist, how does Oracle know that the bloack is available for NEW INSERTS?

    5. Redo Logs on RAID 0 mount points

    Check.

    I would greatly increase your DB_Block_Buffers and Log_buffers. Watch out for redo latch contention and redo log I/O contention.

    My Buffer Cache is already 600 Mb, I have a combined SGA of 860 Mb, there are mulitple of Non-Oracle process runnig on the box which need RAM resources also, and I dn't want the box to start paging.

    I like to use OEM, "iostat -xnc 3", "vmstat 3" and top to diagnos my problems.

    Cheers

    tamilselvan
    Moderator

    Thanks for your feedback tamilselvan.

    1. Use parallel query where ever possible. Remember that parallel query is effective only when the box has more than one CPU and the data files on which the table resides are more than one disk.

    No sure if this will help for inserts.

    2. If more than one CPU in the box and the inserts are coming from multiple sources, set FREELIST for the table = Number of CPUs.

    One session performs ths inserts

    3. Use RAID 0 ( striping the data file)

    Check

    4 Pre-create extents for the table. This will help Oracle not to figure out space as and when needed.

    In a LOCALLY managed tablespace DATA UNIFORM 1 GB extents, INDEX UNIFORM 200 MB extents.

    5 Never put the REDO log files on RAID DISK Array. This will slow down very much, because Oracle writes Redo Log serially, not randomly. Put these files onto non-striped and non-mirrored disks.

    They are on my RAID 0 disk array. I could move them.

    6 Do not multiplex REDO log files both HW and SW levels.

    Hardware and Software level... I might check this out again, I'm not sure.

    7 Keep maximum memory for data buffer.

    Check

    8 Keep the TEMP tablespace in a separate disk. It is advisable to maintain the data file onto non-striped and non-mirrored disks.

    Not able to do so.

    9 Separate INDEX and DATA table spaces’ data files under different controllers. Even if you use RAID 0, identify the controllers and place these data files carefully.

    Different tablespaces, all under a RAID 0 config, on the one filesystem.

    10 Identify each disk’s through put. The sum of disks’ throughput under one controller should not exceed the disk controller’s through. If more disks are configured under one controller, then the system will experience IO bottleneck.

    Is this a UNIX side task. I'm not sure about how to find ou thte actual disk's thoughput.

    11 IF you are using import to load data into the big table, use DIRECT=TRUE and PARALLEL options, also set the table in NOLOGGING mode.

    No direct loading.

    12 Partition the table and Index. Of course in your case, the partition key is date. In 8I, you can add new partition and drop existing partition very easily.

    I definatly think I'll have to partition, another column will have to be created (date) and partition on this, day by day.

    1). The referential integrity of

    13 Set LOG_CHECKPOINT_INTERVAL = 1000000000 and LOG_CHECKPOINT_TIMEOUT = 0. Remember the unit of LOG_CHECKPOINT_INTERVAL is number of OS blocks not the Oracle block. In Unix, it is normally 512 bytes.

    Check

    14 Do not delete two days old rows when other sessions are inserting data into the table. Do it at nighttime.

    This insert volume is the same 24x7x356, there isn't any peak or low times.

    15 Check with your system admin that the Unix is configured to ASYNC IO.

    Check

    16 Always maintain 4 or 5 disks (non-striped and non-mirrored) for storing the export files / flat files. From here you should load the data into table.

    N/A -- I only perform FULL schema backups

    Thanks guys, I was failry concerned maybe I was missing something, but from the feedback I am receiving, it seems the mojority of your suggestions, I have already implemented, which is releiving to say the least.

    I might also check out IOT's and see if they can help the situaltion.

    If anyone else has any additional suggestions, I'd love to hear from you,

    Cheers,


  2. #12
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Done a bit more research into the matter. In regard to this Table with 6 columns, if I index all 6 columns, I get the performance of a snail. If I have five indexes, insert perfomance is still to slow.. now, If i have FOUR indexes, it inserts with the desired speed, i.e Oracle keeps up.

    I then tried, creating only 4 indexes on different columns, and it didn't matter which column I added the FIFTH index on, as soon as I did this, performance went out the window.

    My query now moves to, does Oracle have problems with the precentage of Indexes to columns on a table. I've seen tables with more columns and more indexes than this.

    Input anyone?

  3. #13
    Join Date
    Apr 2001
    Posts
    219
    1. Parallel inserts (Increases the use of multiple CPUs)

    If I an inserting from only one process (i.e only one session is inserting these rows, am I able to parralel insert. And if there is ref integrity, would parrallel be affected.

    Answer: This is based on my program that uses my database. This database is primarily populated by SQL*Loader. I noticed that 1 SQL*loader was running and the program was lagging behind, becuase the SQL*Loader process was tied to one CPU. This restriction to one CPU was killing total insert speed. So, I recommended parallel SQL*Loaders, this worked great and we keep up with the load. I have a 4 CPU Sun box with 2GB of RAM passing our bigger box with 8 CPUs and 10GB of RAM. So, a single process maybe restricted to a single CPU, so try parallel, if needed.


    Also, watch out for locking, like:
    1. The insert order
    2. Direct inserts
    3. Self deadlocking (interesting one! Has to do with INITTRANS, so set it high enough, so it never has to increase as needed)
    4. If using partitions, watchout for DDL operation while inserting.


    4. PCT_USED to 0 (This gets rid of freelists)

    Not quite sure the benefit of this, I ned to reuse the blocks space when it come available, if there are no freelist, how does Oracle know that the bloack is available for NEW INSERTS?

    Answer: Your freelists can become a problem if not managed properly. By setting PCT_FREE to 0 you get rid of freelists, but those blocks are never reused once they are over the PCT_FREE setting. This great for partitioned tables, where a rolling window is applied, which is my case. Becuase I drop partitions as needed to make new ones, which means I never do major deletes from a partition segment. So, I do not need the headache of freelists, but you may if you are concerned about space and large deletes. If you are going to use freelists, learn how to keep them maintained.

    ______________________
    Applications come and go,
    but the data remains!

  4. #14
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Zaggy, can you tell me more abou the rolling windows concept please.

  5. #15
    Join Date
    Apr 2001
    Posts
    219
    The rolling window concept is a made up of partitions. These partitions span a given amount of days, in my case I have a requirement for 90 days. To support the rolling window we have created software to drop and create partitions as needed to support 90 days. So, you end up with exactly the amount of days you want and you can move partitions around for max. performance. Also, dropping a partition is easy, you truncate it, then drop it. This prevents you from doing deletes and creating unneeded redo.

    I believe Oracle provides software support for automatic rolling, but I am not sure, never had a need to research it.

    The problems with partitions is the indexing, which is limited to global or local. Local indexes require little maintenance, but are not as optimal as global indexes in performance. Global indexes on the other hand are great performers, but require maintenance after almost any DDL operation.
    ______________________
    Applications come and go,
    but the data remains!

  6. #16
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    HA ha, a little maintenance.. yeah, like a complete index rebuild after you drop a partition right. Could you provide me with an information, scripts etc for this rolling window concept?

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