7 Replies Latest reply: Feb 13, 2013 2:08 PM by Billy~Verreynne RSS

    Does table or index partitioning over the RAC give more performance?

    1225566
      Does the partitioning [table partitioning or indexes partitioning] give more performance value over the RAC since i'm using only one disk +DATA
      Thanks

      Edited by: Nayef Mehdawi on Feb 9, 2013 2:32 AM
        • 1. Re: Does table or index partitioning over the RAC give more performance?
          asahide
          Hi,

          Maybe yes,
          Logical access will be reduced.

          Regards,
          • 2. Re: Does table or index partitioning over the RAC give more performance?
            JohnWatson
            Nayef Mehdawi wrote:
            Does the partitioning [table partitioning or indexes partitioning] give more performance value over the RAC since i'm using only one disk +DATA
            Possibly. You may be able to establish an affinity between instances and partitions, which can reduce contention for global enqueues and interconnect traffic.
            But be warned: partitioning can deliver wonderful results if done appropriately, but will be disastrous if done badly. You need to be absolutely clear on the problems you are addressing, and why your chosen strategy will fix them.
            --
            John Watson
            http://skillbuilders.com
            • 3. Re: Does table or index partitioning over the RAC give more performance?
              1225566
              Thanks to All, but it Gives more performance and reduces disk contention!! How!!

              When I asked my question I said that I'm using one Disk which is +DATA.

              I think No , we will not give more performance or reduce disk contention in such this case because the aim from the partitioning that to distribute the IO by using different tablespaces which are using deferent IO Disks , but in our case all tablespaces will be created over one disk +DATA.

              I think even if the partitioning will use some algorithm when read/write data on the disk to minimize the range of the selective table rows but finally the read/write will be done on some tablespaces which will be stored to the same disk +DATA , so IO contention will not being reduced. so regular index will be enough in the RAC in such this case.

              Any Advice Please. also if there's any technical documents can help.
              Thanks

              Edited by: Nayef Mehdawi on Feb 12, 2013 10:26 PM
              • 4. Re: Does table or index partitioning over the RAC give more performance?
                Hemant K Chitale
                +DATA is a disk group, not a disk.  The disk group is spread across multiple disks  (Query v$ASM_DISKGROUP and v$ASM_DISK to find out)

                It is not correct to assume that, in Partitioning, the partitions are spread across multiple tablespaces to spread I/O. The partitions may all be in the same Tablespace (which consists of one or more datafiles which are distributed across multiple physical disks , although other tablespace datafiles may be "sharing" the same disks).

                I/O in Partitioning is attempted by reducing the I/O -- instead of doing a Full Table Scan, scan only one Partition of the table for satisfying a query.


                Hemant K Chitale
                • 5. Re: Does table or index partitioning over the RAC give more performance?
                  1225566
                  Thanks Sir , I agree.

                  I know that the storage +Data is a disk group , so with partitioning or without the disk IO accessing will be same. [Yes/No] ,

                  If [No] , How the difference will be ,so I haven't any control over the disk groups [I'm using only one disk unit which is +DATA]

                  If [yes] , The characteristic which you mentioned that there is no Full Table Scan can be done by regular index so with index will be no full table scan as well.


                  I searched alot for technical documents regarding partitioning over the RAC , performance and value but i couldn't find any link or oracle document.
                  Any Advice please.
                  Thanks
                  • 6. Re: Does table or index partitioning over the RAC give more performance?
                    Hemant K Chitale
                    Partitioning is NOT AWARE of the number of disks. It only knows the tablespace(s) the partition(s) are in.
                    so I haven't any control over the disk groups
                    You should talk to the storage team that assigned the disks / LUNs. You should talk to the DBA that created the Disk Group over those disks / LUNs.
                    The characteristic which you mentioned that there is no Full Table Scan can be done by regular index so with index will be no full table scan as well
                    It depends. Many a times a Full Table Scan or a Full Table Scan on only target partition(s) is better than using an index. It really depends on the number of rows and blocks to be read and the distribution of the target rows across the table / partition (the index clustering factor).

                    DO NOT CONFUSE RAC AND PARTITIONING AND DISK GROUPS. THEY ARE THREE INDEPENDENT IMPLEMENTATIONS.


                    Hemant K Chitale
                    • 7. Re: Does table or index partitioning over the RAC give more performance?
                      Billy~Verreynne
                      Nayef Mehdawi wrote:

                      I searched alot for technical documents regarding partitioning over the RAC , performance and value but i couldn't find any link or oracle document.
                      Partitioning is not a RAC feature. It is an Enterprise Edition feature. It addresses two primary issues.

                      Simplistically, it provides the means of subdividing a table into smaller logical pieces that are physically stored separately. Instead of having to access the entire table, only a logical piece can be accessed physically - meaning less I/O and faster performance.

                      It enables better data management via DDL, instead of having to be restricted to DML only.

                      This has nothing to do with RAC. What RAC provides, amongst others, is scalability. Which means more I/O pipes to database (shared) storage. For example, a single server may have dual 4Gb fibre channels to disk. Using 4 cluster nodes, means having 4 x dual 4 Gb fibre channels and the ability to do 4x the I/O. Yes, the same database is being used - but there are more "traffic lanes" for I/O trucks to move data from disk to the database engine (instance), and from the database engine to disk.

                      What does this mean for partitioning? Basically the same thing it means for any other table and index. The ability to read more data from that partition/table/index in parallel across multiple servers using multiple I/O paths.

                      RAC has the ability to scale, when designed, implemented and used correctly, all database processing that is not inherently restricted to serialised processing. In simple terms it means enabling you to scan tables and indexes a heck of a lot faster using parallel I/O processing.