How can I speed up Oracle performance on Unix?

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?

    Requires Free Membership to View

    Login

    When you register, you’ll also receive targeted alerts from my team of editorial writers and independent industry experts with the latest news, tips, and advice to help you do your job more efficiently and effectively. Our goal is to keep you informed on the hottest topics and biggest challenges faced by IT professionals today working with data center technologies.

    Margie Semilof, Editorial Director

    By submitting your registration information to SearchDataCenter.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchDataCenter.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.

This was first published in January 2006

Back to top