| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Performance issues
To: "'Mohammad Rafiq'" <rafiq9857_at_hotmail.com> Date: Fri, 8 Feb 2002 15:25:32 -0500
There are two parts to this issue.
1. DBA issue, that Bj0rn Engsig talked about.
2. System performance.
We have couple of systems where one of our table has 70 million rows and the other 110 million rows. We just converted our 70 million records table to a partitioned table. Table is spread over 12 drives is using 0+1 RAID. Index is partitioned but is on 4 drives.
Reason for doing this was that when 12 processes were trying to insert into the table we were getting buffer busy waits. Most of the processes were trying to write to the same block. Partitioning the table cut down the buffer busy waits. Spreading the table on more drives removed the db sequential read problems. We still had to go further and optimize the SQL to minimize disk reads and so on, but we cut down the I/O wait time.
By partitioning the table and index, you are dealing with a piece of the
table for access and insert.
That means rather than dealing with 100 million rows table, you will be
dealing with say 10 million rows table. We used to have a 31 million records
table for 31 days data. About 10 years go we split this table into 31 of 1
million rows tables and associated indexes. This way we deal with only 1
million records tables, which were much smaller to manage as well as
provided faster access. With partitioning option you are basically doing the
same thing, except you have one table but it is broken up in 31 tables. It
still has one name but it could be spread of 3 to 30 drives and no one but
the DBA knows the difference. So divide and conquer is the strategy. Make it
smaller and make it manageable. Reduce I/O contention and increase access
time.
Table and index Partitioning will help but it is not the only thing. Most important of all is how optimized, well structured and architected code is. This is where we spent most of the time. To modify the code, so we minimize number of SQL executions. Remove SQL from inner loops, optimize the SQL and specially process all the data as it came from the disks, so we do not have to go back to disk to get the data for processing it later.
Keep your disk I/O down, buffer_get(reads) low by optimizing the INDEXes. >From your email I can not tell what type of application you are working on. If it is data warehouse type application, Oracle has a good article on their site about using bitmap indexes and partitioning that might help you. If you are going to partition tables and indexes, you may want to consider Oracle parallel processing. That means you may need a multi-processor machine. You would consider having multiple controllers, if table spread over many drives. The list can be quite long depending on what your requirements are.
Shakir
shakir_at_compu-soft.com
-----Original Message-----
Sent: Friday, February 08, 2002 2:34 PM
To: Mohammed_Shakir_at_standardandpoors.com
fyi...
Reply-To: ORACLE-L_at_fatcity.com
You should not expect to see much performance improvement, except in special cases where you can replace large deletes or loads by simpler partition operations. Your decision to use partitioning should be based on the ability to handle (i.e. DBA work) partitions separately, where you can e.g. mass delete/load, make parts read-only to reduce backup, or enable partial recovery during disk outages. Note, however, that much of this depends on your ability to partition indexes and data equivalently so that you avoid global indexes.
Sathish Tatikonda wrote:
>Hi All,
>
>We are developing a system in which some tables in the database might be
>having about 100 Million records. We are planning to use table and index
>partition's as a means to improve performance. Could you please share
>your experiences/views about handling such huge tables. Is this
>partitioning sufficient or do we have to look in to some other means.
>
>It would also be of great help if you could provide me some pointers to
>documents which gives some insight for handling such tables and
>databases.
>
>thanks in advance,
>Sathish.
Thank you,
Standard & Poor's
MOHAMMAD RAFIQ
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Feb 08 2002 - 14:42:21 CST
 |
 |