To: "'Mohammad Rafiq'" <[EMAIL PROTECTED]>
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
[EMAIL PROTECTED]
-----Original Message-----
Sent: Friday, February 08, 2002 2:34 PM
To: [EMAIL PROTECTED]
fyi...
Reply-To: [EMAIL PROTECTED]
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.
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
________________________________________________________________
The information contained in this message is intended only for the
recipient, may be privileged and confidential and protected from disclosure.
If the reader of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the intended recipient,
please be aware that any dissemination or copying of this communication is
strictly prohibited. If you have received this communication in error,
please immediately notify us by replying to the message and deleting it from
your computer.
Thank you,
Standard & Poor's
MOHAMMAD RAFIQ
_________________________________________________________________
Join the world�s largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
