Re: Is database read activity primary sequential or random?
Date: Fri, 17 Apr 2015 01:04:00 -0400
Message-ID: <CAAaXtLDU+E3WmMbs1Hx0gk=nuiNakcW=E9xerCv3ghuFKnDtUg_at_mail.gmail.com>
But that doesn't mean that the database isn't *trying* to do sequential access. Nor does it mean it doesn't *think* that it actually is doing sequential access.
There are a few more cases like this. Many SAN-attached disk arrays these days are highly virtualized, and offer "cool" services like thin provisioning and snapshot capabilities. When the disk array does relocate-on-write (a common way to support snapshots) it may not take a long time before IO operations that *should* be sequential have become almost entirely random. Again, the database won't know this -- but the disk heads certainly will.
But let's not confuse the issue. I am pretty sure that the storage administrators in this case are interested in what the database is *trying* to do, not what the underlying physical disks actually are doing. They are probably trying to decide what data to assign to which tier of storage.
On Thu, Apr 16, 2015 at 10:01 PM, Mladen Gogala <dmarc-noreply_at_freelists.org
> wrote:
> There is one notable to case in which the answer is certain: if the
> database is virtualized and if the database files are on VMDK "disks". you
> are only doing random access.
>
>
> On 04/16/2015 01:20 PM, MARK BRINSMEAD wrote:
>
> *Yes*.
>
> Well, more accurately, *both*.
>
> Whether IO is random or sequential is determined by the execution plan.
> When the optimiser chooses "indexed" access methods, the IO will be random,
> and when it chooses full table scans (or index fast full scans) it will be
> sequential.
>
> In most OLTP databases, you will see a bias toward small fast queries
> using indexes, and usually you will see much more random IO than
> sequential. In most data warehouse applications, you will see a bias
> toward very large queries without indexes (or using big bitmap indexes) and
> lots of full table scans. Such databases might be inclined to do most
> sequential IO.
>
> But there is really no such thing these days as a "purely OLTP" or
> "purely Data Warehouse" database. Almost everything seems to lie somewhere
> in between.
>
> Look to your performance monitoring tools. AWR or StatsPack. If I
> recall correctly, your site uses Ignite. See whether the IO events for
> your databases are predominantly single block or multiblock reads, and you
> will have your answer. For regular database queries, anyway.
>
> Some parts of the database are "special", though. REDO logs (both online
> and archived) are read and written sequentially.
>
> Backups are always written sequentially, and during backups the database
> is usually read sequentially -- but IO for incremental backups may become
> randomised if you use CBT.
>
> Take care in answering this question. And maybe spend some time with
> your storage administrators to make sure you (and they) understand *why*
> they are asking it.
>
> They may simply be trying to choose between RAID-1 and RAID-5. (As a
> DBA, it is your job to understand the differences as well as or better than
> the storage administrators do. If you don't, start googling now.)
>
> Unfortunately, they may also be trying to choose between "tiers" of
> storage -- large (relatively) numbers of small disks with fast interfaces,
> versus small numbers of very large (and cheap) disks with slow interfaces.
>
> It doesn't much matter whether your IO is random or sequential -- you
> want "Tier-1" storage for your database, your online redologs, and probably
> your archived redologs. Lower-tier storage is probably acceptable for
> backups, but if you go too low, you may prolong the backup windows.
>
> Good luck.
>
> On Thu, Apr 16, 2015 at 12:51 PM, Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
>
>> EE 11.2.0.4
>>
>> My SEs are asking me to tell them which databases are primarily
>> sequential reads vs random reads. It will make a difference in how they
>> configure the LUNs on the new storage when we move these databases. Most
>> of the people who were familiar with the applications were laid off last
>> year, others aren't sure how their applications are really used by the
>> customers. Is there a script or something I can use to tell?
>>
>> --
>> Sandy
>> GHX
>>
>
>
>
> --
> Mladen Gogala
> Oracle DBAhttp://mgogala.freehostia.com
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 17 2015 - 07:04:00 CEST