-
I have a serious performance issue and would like to receive some feedback from and dba who have similar experinces with oracle.
We are running:
Sune450 4 CPU 2GB Ram 250GB Hard Drive SPACE.
Oradata 212 GB DISK Raid 0 Striped.
Have a database 16 K block size.
I have 1 major table, it is 6 columns long, each columns HAS to have an index, for search capabilites. And the table accepts 52 Million records a day. Funny enough, Oracle is having trouble accepting this amount of records. Commits are happening every 100,000 rows. After 5 days (to keep it simple) and 260 million records, I have to then remove the OLDEST 52 million records. Therefore, manplating 104 Million records per day.
I have set the tables and indexes on two different Local Tablespaces, NOLOGGING on tablespaces and Tables (Not that make any difference on normal DML.. when will Oracle realise that NOLOGGING should really be NOLOGGING. My database in in NOARCHIVELOG mode also.
Now both TABLESPACE are on the ONE mount point, but remember this single mount point is RAID 0 STRIPED, and spreads the I/O evenly.
Can somebody offer any suggestions in how to increase insert performance on this ONE table.
INIT parameters of interest.
open_cursors = 300
db_block_buffers = 35000
shared_pool_size = 250000000
large_pool_size = 15000000
java_pool_size = 30000000
log_buffer = 4194304
db_block_size = 16384
db_block_lru_latches = 4
sort_area_size = 131072
sort_area_retained_size = 65536
Suggestions PLEASE anyone.
-
Not realy enough info
What is your pct_free and pct_used set to on your table, try to increase pct_free.
What other contentions are you experianceing ie I/O or latches.
How large are you log_files.
What version of database are you running?
Have you tried partitioning.
-
What is your pct_free and pct_used set to on your table, try to increase pct_free.
PCT_FREE 5 PCT USED 70.
Increase the PCTFREE would only be helpful on UPDATES. The data in this table isn't updated. The FREELISTS are also set to 2, because there is only one process inserting the data.
What other contentions are you experianceing ie I/O or latches.
How large are you log_files.
Redo log files are 250Mb
What version of database are you running?
8.1.7.1.B
Have you tried partitioning.
Thought about it over and over, but I beleive that once a partition is droped the indexes on the table need to be rebuild
-
the indexes doeesnt necessary be rebuilt if they are also partitioned, also depends if they are global index or local
-
Where's the bottleneck? CPU/IO/Memory/Swap?
-
Have you investigated using a RAW system as aposed to cooked. This would speed up access.
-
Hey guys.. thanks for your help.
pando - yeah, I've been noticing that about the global/local. Obviously, I'd be looing to go LOCAL, but determining if this is feasible or not is something else.
truma1 Have you investigated using a RAW system as aposed to cooked. This would speed up access.
I haven't had much experience with the ol RAW file system, and I've done a bit of reading that suggest the perfomace difference between RAW and COOKED is decreasing all the time, plus manipulation of the RAW files can't be done via normal UNIX cammands.
marist89 Where's the bottleneck? CPU/IO/Memory/Swap?
Jeff, I'm still trying to determine this issue. And will run some more stats tomorrow at work. At present I'm concentrating on the application side, but was looking for any other suggestions to handle this volume.
-
I have a DB similar to what you have. My DB has 10-15 million inserts a day and I did the following to get performance.
1. Parallel inserts (Increases the use of multiple CPUs)
2. Set LOG_CHECKPOINT_INTERVAL to a huge setting, thus turning it off
3. Set LOG_CHECKPOINT_TIMEOUT to 0, thus turning it off
4. PCT_USED to 0 (This gets rid of freelists)
5. Redo Logs on RAID 0 mount points
I would greatly increase your DB_Block_Buffers and Log_buffers. Watch out for redo latch contention and redo log I/O contention.
I like to use OEM, "iostat -xnc 3", "vmstat 3" and top to diagnos my problems.
-
How are the rows inserted into table?
Is it through import utility or directly from the application?
-
Heavy Inserts in to a table:
Following are my suggestions; you may not follow all of them. Study them and carefully implement them with your system admin.
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.
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.
3 Use RAID 0 ( striping the data file)
4 Pre-create extents for the table. This will help Oracle not to figure out space as and when needed.
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.
6 Do not multiplex REDO log files both HW and SW levels.
7 Keep maximum memory for data buffer.
8 Keep the TEMP tablespace in a separate disk. It is advisable to maintain the data file onto non-striped and non-mirrored disks.
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.
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.
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.
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.
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.
14 Do not delete two days old rows when other sessions are inserting data into the table. Do it at nighttime.
15 Check with your system admin that the Unix is configured to ASYNC IO.
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.
Closed mouths gather no feet.
Tamilselvan
-
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,
-
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?
-
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.
-
Zaggy, can you tell me more abou the rolling windows concept please.
-
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.
-
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?