This discussion is archived
11 Replies Latest reply: Apr 19, 2013 6:04 AM by Mark D Powell RSS

Disk Usage with Partitioning in MSSQL versus Oracle

Richard C Evans Newbie
Currently Being Moderated
Hi All,

Although this isn't a likely setup I would encounter/recommend, this question has been driving me nuts because I can't find the answer and I'm not sure how to test it on my own (lack of hardware/knowledge).

I encountered a document on SQL Server 2012, and 2008R2, that states:
When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.
http://msdn.microsoft.com/en-us/library/ms190787.aspx
>

I'm curious if Oracle has the same 'limitation.' I see in the 10.2 docs that they say:
Although you are not required to keep each table or index partition (or subpartition) in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces enables you to:
* Reduce the possibility of data corruption in multiple partitions

* Back up and recover each partition independently

* Control the mapping of partitions to disk drives (important for balancing I/O load)

* Improve manageability, availability, and performance

http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm
>

But this does not specifically say it can access more than one drive at a time. I can't tell whether this applies to Oracle or not. Sorry for the reference to a previous version but I looked for this statement in the 11.2 documentation but couldn't find it.

Can anyone shed some light on this? It's eating at me :)

Thanks,
Rich
  • 1. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    Mark D Powell Guru
    Currently Being Moderated
    Rich, Oracle actually asks the OS to perform IO. If the logical data files presented to Oracle via the OS logical disk manager are stripped then multiple disks are accessed to support multi-block read requests. A single block read request would likely be on a single disk no matter the state of stripping at the OS level. But honestly what disk storage unit is not stripped in today's world?

    SQL Server just like Oracle gets its files from the OS. If you have a storage unit like we do then SQL Server has no idea the files are stripped across multiple disks. It just asks for IO against D:/MSSQL/data/what_ever. It is the disk storage unit that handles the details in most cases.

    HTH -- Mark D Powell --
  • 2. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    To add to Mark's posting - there is a distinct border between the logical data layer, and the physical.

    In the Oracle database, you deal (or should deal) with the logical layer. This logical layer consists of segments, extents, datafiles, tablespaces, block sizes, pctfree/pctused, deferred segment creation and so on. You do not (and should not) consider physical storage issues such as striping, at this logical level. The closest "thing" to the physical layer is a datafile of a tablespace - this is the physical object on the physical storage device that will be read and written via I/O calls.

    That datafile can be a raw partition. It can be a file on a cooked file system. It can be a scsi device such as a SAN LUN, or NAS device. It can be a SSD. It can be (if you really want), a USB drive. Or SD card.

    The datafile can be striped by the storage system across multiple devices. It can be mirrored. RAID'ed. Whatever. Managed by Oracle ASM, or Veritas, or numerous other storage manager.

    An Oracle server process will issue an I/O request (via the kernel driver for that device) to read from a file/device handle (the datafile), at a certain offset, a certain number of bytes/blocks. Whether the data being read is for a table, cluster, partition, index, or whatever, is on a single device, or a mirrored device, or a RAID device, has no bearing. This Oracle server process does not care how the physical storage layer looks like, works, or is structured. It issues an I/O read or write on a datafile - what happens below that is not relevant to the Oracle server process issuing that I/O call.
  • 3. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    Richard C Evans Newbie
    Currently Being Moderated
    Thanks Mark,

    I appreciate the response and explanation!

    I thought it was interesting (unusual?) that Microsoft specifically states what to do versus what not to do so it made me wonder if this was an RDBMS or Operating System 'limitation'. As you point out, the storage unit handles these things today so it shouldn't be a concern in 99.99% of environments.

    It's something my brain is trying to process still. I feel like there is a piece of this puzzle that's missing :)

    Regards,
    Rich
  • 4. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    Richard C Evans Newbie
    Currently Being Moderated
    Thanks for adding to the discussion Billy,

    I appreciate your detailed description of the processes.
  • 5. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    Mark D Powell Guru
    Currently Being Moderated
    My home PC came with two 1T hard drives. By default HP would deliver them set up in a stripped configuration for performance (though I had the disk set up as a mirror). To Windows there is only a 'C' drive. Yet, most of the documentation you read still refers to disk drives like your disk consists of physical disk drives that you have access to. In today's world disk is often a block box, which is another issue and discussion.

    HTH -- Mark D Powell --
  • 6. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    Richard C Evans Newbie
    Currently Being Moderated
    I was thinking the documentation must have been carrying this paragraph from way back when we had JBODs and we might have put a partition on each disk instead of using RAID. But that raises the question.. did MSSQL even support partitioning way back then? :)

    Thanks for playing along, I appreciate it!
  • 7. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    jgarry Guru
    Currently Being Moderated
    You might also consider that Oracle can do parallel operations, which each slave asking for different things. See http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html http://dbareactions.tumblr.com/post/48229870306/my-queries-before-the-junior-guy-set-maxdop-1
  • 8. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    jgarry Guru
    Currently Being Moderated
    Mark, we're going to put you in a striped prison outfit if you keep on talking about stripping! :p
  • 9. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    rp0428 Guru
    Currently Being Moderated
    >
    Mark, we're going to put you in a striped prison outfit if you keep on talking about stripping!
    >
    You may want to mind your "p's and q's" (especially your "p's).

    Using two "p's" instead of one gives that sentence an entirely different meaning. ;)
  • 10. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    jgarry Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    Mark, we're going to put you in a striped prison outfit if you keep on talking about stripping!
    >
    You may want to mind your "p's and q's" (especially your "p's).

    Using two "p's" instead of one gives that sentence an entirely different meaning. ;)
    Go back and look at Mark's post, and we can all smile more.
  • 11. Re: Disk Usage with Partitioning in MSSQL versus Oracle
    Mark D Powell Guru
    Currently Being Moderated
    Ooops. LOL. I will try to watch for that error next time. -- Mark --

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points