Hello
I am preparing a small presentation offering DB users some assistance
about "best practices" when using Oracle, and defining the general
policy in the Admin scope. Most of the users are not specially
proficient in SQL and are even completely ignorant about DB internals.
Well, I am not specially proficient in SQL either, but I would never
do a "delete * from <table>".
These points come into my mind. You are invited to add yours (take a
look at "still missing" below).
- Learn what is a cartesian product and how to avoid it
- 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.
- Create indexes based on the most frequently used predicate columns.
- 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.
- 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.
- Always do an "insert /*+ APPEND */ " into an empty table, perhaps
in nologging mode.
- Do not delete large amounts of data from a table. Transfer data to
be kept to another table instead, and rename as appropriate.
- 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).
- 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.
- Avoid right-handed indexes based on a growing sequence.
- Be aware of the degree of selectivity of indexes. Use B*tree
indexes for large cardinality columns and bitmap indexes for low
cardinality columns.
- 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.
- Avoid IN lists with more than aprox. 10 entries.
[but how to rewrite the code?]
- Avoid NULL values in indexes columns.
- Prefer joins instead of subselects.
- Avoid outer joins and full outer joins [but how to rewrite the
code?].
- Avoid PL/SQL loops of single steps if it can all be done in SQL.
[examples?]
- Avoid uncommitted long-running DML operations in order not to
overflow the Undo tablespace [best simple solution?]
- How to cope with the "Snapshot too old" problem.
- 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).
- Always name your objects (for example: indexes). System given
names can cause trouble for the Admin.
- Do not specify any storage clauses without a special reason. The
Admin should be consulted in this case.
- The only tablespace that should be explicitly named is the one for
indexes.
- Do not grant DML rights to others acting on your schema.
- 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.
- 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:
- Guidelines for use of temporary tables, examples (improving
performance).
- How to force execution on the remote host side when using a
database link (if appropriate).
- Best GUI or RAD or IDE ("grafical user interface", "rapid
application development", "integrated development environment") for
Oracle [some use Access as a Front-End and get into problems]; either
an Oracle product or third party one (?); recommendations on
advantages/disadvantages.
My problem with this is that users expect from me very specific
information about how to make their queries run faster. The
performance resources known to me reside on the Admin's responsibility
and are mostly already enforced. What I need now is a compilation of
understandable guidelines for sql writers. I can't find anything
related in the Web. If you take a look at
http://www.dbdomain.com/article8.htm
you will realize that all this stuff is for the Admin in the first
place, and not appropriate for casual DB users.
Your input will be high appreciated!
Regards
Rick Denoire
Received on Sat Feb 14 2004 - 08:20:56 CST
