<SNIP>

1. Not taking backups :o)
2. Adding indexes all over the place for performance reasons.
3. No (proper) change control procedures.
4. Exporting from the database and defaulting COMPRESS to Y, even worse,
exporting with COMPRESS=Y explicitly.
5. Allowing developers to create obbjects in a tablespace with a
different extent size to the tablespace defaults.
6. Having numerous indexes (on the same table) which could be replaced by
one or two carefully thought out indexes. (you know, indexes on A and B
and A,B and C and A,B,C and B,C ....)
7. Putting the ref int checks in the application instead of the database.
8. Ditto, but keeping business rules in the app instead of the db.
9. Using standalone procs and functions instead of packages.
10. When actually writing packages, not separating the PACKAGE specs from
the PACKAGE BODY so every time you change the code in a body, you
re-create the spec as well and invalidate everything that depends upon it.
11. Using a table to hold a sequence number is a bad one. You know, read
the number and lock it, increment it and write it back - then commit at
some point in the far future. Even committing ASAP is a major problem as
soon as you get more than 5 or so users all doing the same thing.
12. Even worse, having a single table for all the sequence numbers in the
system.
13. Creating 'temporary' tables to hold intermediate results MS SQL Server
style.
14. Converting triggers from another db system into Oracle and discovering
that the other system allows you to update rows in the table that has
triggered the trigger leading to mutating table errors on Oracle (which
IMHO does it correctly !)
15. Not enough SQL reuse.

I'm sure that there are more :o)

Cheers,
Norm.

No primary key - rare
No foreign keys - common
No bind variables
Create intermediate tables as often as possible and then drop them when done
with.
Use PL/SQL instead of SQL
No Bind variable.
Use Java SP instead of PL/SQL
Use EJB instead of the Database.
Use Net instead of the Database
No bind variables   .
Avoid array fetch at all costs.
Parse repeatedly.
Test on your laptop with a maximum of 5 rows per table.
Don't test at all, that is what pilots are for.
nO binD variableS.
Return all of the table to your client and filter on the client to avoid
loading the server.
Never document anything - noone will read it.
Never have a database model - that is just for classes.
no bind variables

The number one performance rule for Oracle or any other RDBMS is
"Don't Move the Data". The biggest sin is treating the DBMS as a
dumb filing system, and pulling huge wads of raw data out into a
client to massage and return row-by-row or digest into a report.
DBMSes are designed for efficient set-based processing. Try to
extract only the data needed to display. For any data extracted that
is non-volatile or under the total control of the one client application,
to cache retrieved data in a long-running client (or middle tier) to
avoid burdening the DBMS with repeated identical queries.
   Do all the heavy big-volume processing where the data is, in
the DBMS with procedures. You build your sawmill where the trees are.
   I know of one big application vendor whose payroll program is
a straight port of an ISAM app to RDBMS. It takes 8 hours on big
iron to 'balance the checkbook' of 40,000 employees. If this were
done in procedures, it'd take minutes. I know of another big app
vendor whose client interface has about 250 separate business methods.
A typical user would do his/her work calling these methods about 50-100
times a day. More than 80% of these methods have processing that
includes a fresh query to the DBMS to return a list of all the countries
in Europe. I understand the political volatility in Europe has been higher
recently... ;-)

Joe Weinstein at BEA

Aside from the traditional onces (appropriate SGA sizing, BIND
variables, etc.), some negative impact development
philosophy/implementation items that I've observed:

1) Attempt to do the security in application
- critical hole when multiple access techniques are allowed
- requires duplication of the s/w
- usually not as efficient as using Oracle's 'row level security'

1a) Not using Oracle's Resource manager
   
2) Attempt to do ALL application logic in front/mid tier
- invariably moves excess amounts of data
- move data should be restricted to minimum resolved set so use joins
and where clause properly

3) Using a procedural language (C, C++, PL/SQL, VB) when the same
results could be obtained by one (or more) well-thought-out SQL
statement(s)
- usually a procedural language involves stepping through a cursor
- if a cursor loop ONLY involves data transformation, chances are high
that it could be accomplished by a single SQL statement with appropriate
selectivity

4) Using TEMP tables
- There are valid reasons for temp tables;
- However, several uses of temp tables are simply a workaround to
"advanced" isolation level capabilities found in some other RDBMSs.
Oracle generally does not require such a workaround as it ensures that
the data in a transaction can not be seen outside the transaction's
session.

5) IMO, every index should be reviewed every 6 months toi determine
whether it's needed
- Too often have I seen indexes created to meet 1-shot or limited use
requirements and left around;
-  Remove unneeded or unnecessary (or duplicate) indexes!

6) Reinventing the wheel.  
- I've seen a lot of organizations literally spend millions of dollars
duplicating built-in functionality of (intermedia's text search,
workflow, workspace management/long transaction, advanced queueing,
htp/htf, advanced grouping functions, spatial/location management,
etc.);
- This not only wastes development effort and costs extra maintenance,
it dilutes the value of the product, making it appear more expensive
than it is;
- Developers should know and understand Oracle's capabilities;
- Reinventing the wheel invariably violates 1) 2) and 3) above.

7) Insisting in yet another instance on that machine, 'cause each
application should have it's own instance
- likely can't be tuned
- wastes license $ due to semaphore, shmem and CPU overhead for each
instance

8) Insisting that the machine should host ALL of the database instance,
the access tier, the report generator, etc.  
- likely can't be tuned
- wastes license $ (eg: need 4 CPU to do 2 CPU's worth of database work)

9) Using RBO; not running appropriate stats
- In 9i, CBO is quite acceptable AND will take into account the changes
in environment.

10) Not using appropriate index types (eg: Function Based indexes)

11) Not allowing the DBA to actually administer the DB
- Some organizations don't get apprpriate DBA skills or allow developers
to override DBA suggestions

12) Not evaluating or using appropriate options
- Partitioning can and does provide significant performance and
scalability benefits and is relatively easy to cost justify if the
organization has any sort of 'business case justification' process.

13) Blindly following rules of thumb without understanding them.

Having read everyone else's list.

1. Developers that don't understand the implications of the multiversion
concurrency model.

2. Developers that don't understand the implications of reads not
blocking writes and writes not blocking reads.

3. DBAs that don't understand the implications of ORA-01555.

4. Those schooled in other RDBMS products that insist on treating Oracle
as if were the other product.

5. Organizations that don't conduct code reviews.

6. Developers of testing prorams that can't read code and don't know how
to identify potential weaknesses and test for them.

Norman did a great job listing the database development sins, but I
have 1 more: Doing everything that Siebel enforces its customers to
do! For example:
- Enforce the use of RBO instead of CBO!
- Rebuild indexes periodically, in the hope that "it will help
performance"
- create gazillions of indices on an OLTP system (it's not common in
Siebel that more than 100 indices are defined on a table)
- Have no reference integrity at the database level!
- Avoid at all costs to use MV's or views!!!! (they prefer instead to
use giant SQL statements).

Daniel Roy
Siebel Developer!

Letting developers into the production database.

jg
--
@home.com is bogus.
http://www.gripe2ed.com/scoop/story/2003/11/24/02356/143

1.  Developers that can't/won't read the docs.
2.  Developers that can't/won't take advantage of Oracle strengths
(black box phenomona).
3.  Lack of bind vars; lack of reusable SQL; too much LIOs.
4.  Overuse of hints w/o understanding the CBO and how it works.
5.  Lack of budget to get the proper resources (people and hardware).
6.  Propogation of "mythical cure-alls" for performance issues.
7.  Badly architected software (see #3).
8.  The "internet-time" syndrome vs. carefull, well thought out analysis.

Lot's more, I'm sure.

Only one...

People on a project who aren't
- passionate about what they do.  
- keen to learn

Everything else falls into place if you can satisfy the above

Dusan, I would suggest you change your wording about issuing commit
within a loop since in many cases the commits need to be performed
within a loop.  I think it is the frequency of commits rather than the
logical coding structure that is the issue.  In my opinion how
frequently a commit should be issued is dependent on how likely
another session will need update access to the rows being processed.
In a transaction driven system the inventory rows may well require
commits be issued on a per-row or very few row basis.  While another
program in the application, which is the only source of update
activity for a table that has low select activity, would best be
served by one or very few commits.

Be careful in the process of trying to get something into the hands of
developers that you do not over simplify the problem and end up making
a statement that you later consider "dumb".  Writing for developers is
not an easy task.

Good luck
-- Mark D Powell --

Datatypes is an issue I'm constantly dealing with :-((

Using a unproper (is that correct English ?) datatype for column
e.g. NUMBER for a date (number of days/seconds elapsed)
      VARCHAR2 instead of NUMBER for a numeric

Being unaware of nearly deprecated datatypes and keeping on using them
(VARCHAR, RAW)

Oversizing variable length datatypes

hth
Alkos

(1)
SELECT DISTINCT ...

(2)
...  WHERE <attribute> IN (SELECT DISTINCT ...)

(3)
LOOP
  EXECUTE IMMEDIATE some_sql;
END LOOP;

(4)
SELECT MAX(id) + 1 AS new_id FROM big_table

(5)
WHERE TRUNC(date_attr) = TRUNC(date_variable)

--
Marc Blum
mailto:blumNOSPAM@marcblum.de
http://www.marcblum.de