Random thoughts in line.
HJR
--
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:mras209smjn3g6ndo2o6119f5dhoo666t5_at_4ax.com...
>
> 1) Learn what is a cartesian product and how to avoid it
Wrong way round, don't you think? I'm not sure anyone actually needs to know
what a Cartesian product is. They should learn, however, how to write a
decent where clause.
> 2) Be aware of the two major access paths: FTS and indexed. For
> queries delivering a small subset of data from a large table, index
> access should be preferred.
Mmmm. Index access might be preferred if the index is a good one. But not
always. Not even for small subsets of data from a large table. IE, if you're
teaching utter newbies, don't even start suggesting that "FTS bad, Index
Good". It all depends!
> 3) Create indexes based on the most frequently used predicate columns.
Yes, but within that... what about concatenated indexes? What about the use
of skip-scanning and key compression, which together suggest that if there's
a choice of leading edge to a concatenated index, the least cardinal one
should go first.
> 4) When you create an index, confirm that it is actually being used in
> the query. Avoid access to an indexed column through a function,
> otherwise the index will be ignored.
>
> 5) When doing a full refresh of materialized views, drop indexes first
> and recreate them afterwards, or make them unusuable and rebuild them
> after the insert is completed.
>
> 6) Always do an "insert /*+ APPEND */ " into an empty table, perhaps
> in nologging mode.
And always do a backup afterwards if you care about the data. I try to make
the general point that you can't win with Oracle (or any database come to
that). You can have safety (recoverability), or you can have speed
(nologging), but you can't have both. And where you get the speed on the
insert with nologging, if you value your data, the nologging operation
requires a fresh backup of that tablespace. So what you gain on the insert,
you lose on the backup.
I dislike the "always", too. bearing in mind the trade-offs between speed
and performance, the approach should be to do whatever is appropriate at the
time. Which might include doing a direct load insert, and might not.
> 7) Do not delete large amounts of data from a table. Transfer data to
> be kept to another table instead, and rename as appropriate.
>
> 8) If a fresh table or materialized view is created and populated,
> gather statistics immediately afterwards (the Admin will do it sure,
> but only later or on weekends).
I'm not so sure about this. In 9iR2, we can do dynamic sampling for things
that don't have their own statistics, so the lack of statistics does not
absolutely have to be the show-stopper it might have been. On the other
hand, having people fire off full scans as statistics are collected at a
time and place of *their* choice might not be such good news for other
people attempting to use the database. Statistics collection should surely
be a planned activity, not a knee-jerk response to the fact that a segment's
just been created.
> 9) If a table or materialized view is recreated or repopulated with a
> similar amount of data, do not gather any statistics. Rather save
> statistics data from the table prior to the recreation and transfer
> them back again afterwards.
>
> 10) Avoid right-handed indexes based on a growing sequence.
Frequently, you won't have a choice about whether the sequence is indexed or
not. The issue is not "avoid the index", but how best to avoid the
performance issues that may arise from such an index. Presumably you will
discuss reverse key indexes and hash partitioning?
> 11) Be aware of the degree of selectivity of indexes. Use B*tree
> indexes for large cardinality columns and bitmap indexes for low
> cardinality columns.
Be careful there. What's "large" and "low". Cardinality is relative, not
absolute. A bitmap index on a column containing thousands of values might
well be appropriate even so, if the table itself contains hundreds of
millions of rows.
> 12) Avoid sort, distinct, union when possible [the 24GB TEMP TS gets
> full up to the top - how do they manage to do that?]; "union all" does
> not need sorting.
>
> 13) Avoid IN lists with more than aprox. 10 entries.
> [but how to rewrite the code?]
>
> 14) Avoid NULL values in indexes columns.
>
> 15) Prefer joins instead of subselects.
>
> 16) Avoid outer joins and full outer joins [but how to rewrite the
> code?].
>
> 17) Avoid PL/SQL loops of single steps if it can all be done in SQL.
> [examples?]
>
> 18) Avoid uncommitted long-running DML operations in order not to
> overflow the Undo tablespace [best simple solution?]
Dangerous. Commit when appropriate is surely the approach. To avoid
long-running uncommitted DML, one might commit very frequently. And then you
have a 1555 on your hands.
> 19) How to cope with the "Snapshot too old" problem.
See above!!
> 20) Avoid filling rows in two steps: insert and update. Better combine
> rows together and insert them in one strike [example?]. Even insert
> into several tables is possible (9i).
>
> 21) Always name your objects (for example: indexes). System given
> names can cause trouble for the Admin.
>
> 22) Do not specify any storage clauses without a special reason. The
> Admin should be consulted in this case.
Do not specify a storage clause under any circumstances, because the locally
managed tablespaces your Admin has set up will utterly ignore them anyway.
Well, there are always exceptions of course...
> 23) The only tablespace that should be explicitly named is the one for
> indexes.
This one I just don't get. Are you still talking about when you create
segments? Why should indexes be treated any differently from any other form
of segment? Segments of whatever type should always be housed in an
explicitly-named tablespace, because relying on users' default tablespaces
is dangerous. And why separate indexes into a separate tablespace anyway,
just because they are an index??!!! ;-)
And I don't see a point about: always think about PCTFREE and PCTUSED for
any segment that you create, because you can cause all sorts of performance
woes if you get it wrong.
>
> 24) Do not grant DML rights to others acting on your schema.
>
> 25) If the use of your scripts is not restricted by yourself or if
> they are going to be used reliably on a regular base (for example,
> monthly reports), then write your statements based on views and/or
> synonyms to avoid a direct dependency on the underlying schema. If the
> schema (removing column etc.) is changed, a redefinition of the view
> will be enough not to break all dependent scripts.
>
> 26) If a GUI is planned for a set of scripts, put all your scripts in
> the DB as stored procedures; they will be available no matter which
> GUI is going to be used.
>
> For more advanced users:
> 27) Learn how to use the autotrace mode of sqlplus.
>
> 28) Learn how to use the explain plan command and interpret results.
>
> 29) Using hints to improve performance
>
> Still missing:
>
> a) Guidelines for use of temporary tables, examples (improving
> performance).
Don't, is my advice, generally. If you're going to use a temporary table as
a form of temporary de-normalisation, I'd consider a materialised view
instead, though it would depend upon the circumstances. But there are many
ways to physically de-normalise data and they should all be investigated.
Byt the way... I don't recall seeing a mention of key compression on
indexes. Should always be looked at, even if only to dismiss it as
non-viable.
I would also suggest that designers should nearly always make their unique
and primary key constraints deferrable, even if not deferred. Saves a lot of
I/O when indexes don't disappear without warning.
There's a lot more, but that would do for starters, I think.
Regards
HJR
Received on Sat Feb 14 2004 - 20:16:40 CST
