Problem solve Get help with specific problems with your technologies, process and projects.

How can I speed up Oracle performance on Unix?

What is the best way to structure an Oracle database for a faster performance in a Unix production field?

What is the best way to structure an Oracle database for a faster performance in a Unix production field?
The best way to provide superior performance is through careful schema design and SQL programming. If poor choices are made at design and programming time, almost no "database tuning" can solve the inevitable performance problems.

Careful schema design means good data modeling and sufficient normalization -- at least third normal form; some experts advocate Boyce-Codd Normal Form (BCNF). Careful SQL programming means avoiding practices that inhibit scalability. Use bind variables, not literals; take advantage of SQL's set orientation and declarative nature to operate on many table rows at a time; in PL/SQL code, use procedural capabilities only to the extent necessary, and don't revert to SQL that operates on a row at a time unless absolutely required. One of the biggest mistakes in PL/SQL programming is to treat a PL/SQL procedure or function as though it were written in C or another 3GL, and to embed row-at-a-time SQL operations in loops or other iterative constructs. The rule of thumb should be to accomplish all database access using SQL, unless impossible; use PL/SQL only if SQL can't do the job.

If designers and developers do these jobs correctly, you can expect reasonable application performance unless your database configuration is very poor -- examples of things to avoid are placing Oracle database files on RAID 5 disk arrays in a write-intensive application, providing insufficient memory to the Oracle SGA and placing online redo logs on disk devices for which there is a lot of contention.

Briefly, the answer to your question could (and does!) fill several books, but I've tried to lay out the major principles here. I hope you find it helpful.

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.