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).

1) Learn what is a cartesian product and how to avoid it

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.

3) Create indexes based on the most frequently used predicate columns.

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.

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).

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.

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.

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?]

19) How to cope with the "Snapshot too old" problem.

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.

23) The only tablespace that should be explicitly named is the one for
indexes.

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).

b) How to force execution on the remote host side when using a
database link (if appropriate).

c) 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

Rick Denoire wrote:
[...snip...]
> 6) Always do an "insert /*+ APPEND */ " into an empty table, perhaps
> in nologging mode.

TRADE-OFFS

Do you realize that after NOLOGGING is used, you've just rendered the
previous hotback useless past this point in time? Since the data
being inserted is NOT written to the redo logfiles, any recovery of
this instance can only be made valid to the time of when NOLOGGING
is used.

Our inhouse Best Practices Manual explicitly states that developer
should NEVER use NOLOGGING, because we desire to maintain a continous
recovery DB for failover capability.

"Rick Denoire" <100.17706@germanynet.de> wrote in message
news:mras209smjn3g6ndo2o6119f5dhoo666t5@4ax.com...
> 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>".
>


well that isnt even valid syntax - I fear some sharp replies coming

DJ wrote:

> "Rick Denoire" <100.17706@germanynet.de> wrote in message
> news:mras209smjn3g6ndo2o6119f5dhoo666t5@4ax.com...
>
>>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>".
>>
>
>
>
> well that isnt even valid syntax - I fear some sharp replies coming

I suspect Rick was just emphasizing the point.

Rick ... there are numerous books on best practices. But the most
important one is to find an indendent expert ... not just a paper
expert but a real one ... to review your documents and comment on them
before putting them into force.

Perhaps you can find someone outside of your organization or with a
college or university program that can perform that service at minimal
charge.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

"Ana C. Dent" <anacedent@hotmail.com> wrote:

>Rick Denoire wrote:
>[...snip...]
>> 6) Always do an "insert /*+ APPEND */ " into an empty table, perhaps
>> in nologging mode.
>
>TRADE-OFFS
>
>Do you realize that after NOLOGGING is used, you've just rendered the
>previous hotback useless past this point in time? Since the data
>being inserted is NOT written to the redo logfiles, any recovery of
>this instance can only be made valid to the time of when NOLOGGING
>is used.

May be I understand something wrong, correct me please. When NOLOGGING
has been used, recovery is still possible, but NOLOGGING
objects/operations won't be restored; the rest, written to the redo
files, should be recoverable.

In our case, NOLOGGING is encouraged for objects containing redundant
or reproducible data.

I am still not sure about the impact of setting all indexes to
NOLOGGING. After all, they can be dropped and recreated.

Bye
Rick Denoire

"DJ" <nospamplease@goaway.com> wrote:


>> Well, I am not specially proficient in SQL either, but I would never
>> do a "delete * from <table>".
>
>well that isnt even valid syntax - I fear some sharp replies coming
>

Didn't I say I would never do that? ;-)

Rick Denoire

"Rick Denoire" <100.17706@germanynet.de> wrote in message
news:eivs209v1hkdfksgd4iqpcmtu3fucv8nc3@4ax.com...
> "Ana C. Dent" <anacedent@hotmail.com> wrote:
>
> >Rick Denoire wrote:
> >[...snip...]
> >> 6) Always do an "insert /*+ APPEND */ " into an empty table, perhaps
> >> in nologging mode.
> >
> >TRADE-OFFS
> >
> >Do you realize that after NOLOGGING is used, you've just rendered the
> >previous hotback useless past this point in time? Since the data
> >being inserted is NOT written to the redo logfiles, any recovery of
> >this instance can only be made valid to the time of when NOLOGGING
> >is used.
>
> May be I understand something wrong, correct me please. When NOLOGGING
> has been used, recovery is still possible, but NOLOGGING
> objects/operations won't be restored; the rest, written to the redo
> files, should be recoverable.
>

You've got this part right.

> In our case, NOLOGGING is encouraged for objects containing redundant
> or reproducible data.
>
> I am still not sure about the impact of setting all indexes to
> NOLOGGING. After all, they can be dropped and recreated.
>
> Bye
> Rick Denoire
>

In the case of infrequently changed large tables, you may not want to do
this because it will take much longer to recreate indexes than applying
small amount of logs.

"Ana C. Dent" <anacedent@hotmail.com> wrote in message news:<v_qXb.40806$P17.33929@fed1read03>...
> Rick Denoire wrote:
> [...snip...]
> > 6) Always do an "insert /*+ APPEND */ " into an empty table, perhaps
> > in nologging mode.
>
> TRADE-OFFS
>
> Do you realize that after NOLOGGING is used, you've just rendered the
> previous hotback useless past this point in time? Since the data
> being inserted is NOT written to the redo logfiles, any recovery of
> this instance can only be made valid to the time of when NOLOGGING
> is used.
>
> Our inhouse Best Practices Manual explicitly states that developer
> should NEVER use NOLOGGING, because we desire to maintain a continous
> recovery DB for failover capability.

I see no problem with having a disposable tablespace that is NOLOGGING
for non-critical segments, that would be offline dropped during
recovery. Provided that everyone understands that the segments in the
tablespace would be trashed, the payoff in terms of reduced generation
of redo may be worthwhile, YMMV.

in 9.2 the dba can specify that logging be forced at the tablespace
level, which will override the operation being executed as NOLOGGING
(unrecoverable).

big differences between whether this is in the developer's own schema,
an app-owner schema, etc.

Pd

Random thoughts in line.
HJR
--
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------

"Rick Denoire" <100.17706@germanynet.de> wrote in message
news:mras209smjn3g6ndo2o6119f5dhoo666t5@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

drak0nian@yahoo.com (Paul Drake) wrote:


>in 9.2 the dba can specify that logging be forced at the tablespace
>level, which will override the operation being executed as NOLOGGING
>(unrecoverable).

In 9.2.0.4 LOGGING can be forced via init parameter at the DB level,
don't remember the name from my head.

Bye
Rick Denoire