*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@xxxxxxxxx>
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
