FW: Single block reads instead of multiblock reads
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Aug 2010 10:14:21 -0400
<snipped to fit>
To repeat myself, this is probably due to having 600 columns. Tanel
suggested examining checking the increase in continued reads, which will
provide you evidence.
As a practical manner, you can also check:
select c1,c2,.,cn from tab
where c# is the column name for column position left to right with n large
enough so that you have an unindexed column set (so that you still get a
full table scan).
Then, if you don't really need all the columns from the table, and none of
the columns you need for a particular table are beyond column 254
or reach beyond row column values not stored within the first block piece of
an individual row, you could restore multiblock reading for a particular
query. You may provide a view that retrieves columns that most often fit in
the first row piece. Of course updates may move things around and Oracle
posts no guarantee that column values are stored left to right in the block
when row chaining is required.
(I wrote 254 rather than 255, because if I remember correctly when a row is
continued because of being over 255 columns you only get 254 in the first
block and the row continuation location is in the array slot for the 255th
column. I could be wrong about that. Since you already have 600 columns, it
is moot. You've got three row pieces per row unless you have a pile of
trailing null column values, and even then I'm not sure and I'd have to test
it.)
After you do the entity relationship requirements for a logical schema
design, you also need to do physical schema design if performance
requirements are among your considerations. One of the bits of physical
design for entities that require more columns than the RDBMS in question
stores conveniently in the first block (for block oriented storage models)
is putting the most commonly required column values left most in the table
definition and putting most likely to be unreferenced and null right most if
a single row is unlikely to fit in the first storage block. While this is no
guarantee, since updates may force a column value into a later rowpiece
block, it is useful statistically. For short rows (in columns or actual sum
of vsize of columns) this only costs you the extra storage from having null
values non-last in a row. When there is mnemonic value to having certain
columns in a certain order and grouped together in the table definition you
may face a trade-off, but that is rarely a practical consideration and I
would tend to group columns favoring any such mnemonic value.
For a particular case, you can determine whether a rebuild rearranging the
column order might be worth the effort. But you need to know the details of
the functional use of the object. Sometimes it may even be worthwhile to
divide a logically cohesive entity's row definition into multiple physical
table pieces linked by a common unique id column set. Existing select *
queries can be serviced by a view while the rest of the queries are
repaired, and you can make sure you follow all the rules so that it is an
updatable view for existing dml queries. I'm always a bit skeptical about
the logical schema design when a table has this many columns, but of course
that ship may have sailed before you arrived and it is not my intention to
address the question of whether an entity really needs 600 columns, but
rather, to help you with remediation possibilites.
If
select c1, c2, ., cn (etc) restores multiblock read, please be sure to
compare the total read time required versus the single block read time for
the same using complete rows before embarking on remediation efforts.
Depending on the hierarchy of your cache structures down to the "spinning
rust" your mileage may vary. Of course if you mostly only need column values
that are stored most often in the first row piece, the big value of not
reading the other row piece blocks is avoiding the LIO work. Right Kevin? So
if you reimplement on exadata and convert your queries to only use the
columns you need, that would be another way to go. Quite a bit more
expensive experiment though.
Good luck.
mwf
<snip>
Other related posts:
