- For Client-side SQL, use bind variables, this allows an efficient re-use of SQL contexts in the shared pool. Queries with bind variables are hard-parsed only once, and then soft-parsed. Hard parse is a heavy task because it includes the optimization phase which is CPU intensive, while a soft parse is just the retrieval and re-use of the existing context (which includes the already computed execution plan) in the shared pool (both also have a syntax check and a check on the existence and state of the objects (tables, indexes...) used in the query). Also prepare your statements once and then keep execute them (prepare once, execute many VS (prepare, execute) many)
- In your procedures, try to use as little procedural code as possible, ie as often as you can, try to do what you want in a single SQL statement (even a (very) complicated one) instead of using loops on cursors (which are in general very inefficient, and not scalable).
- Have your statistics correctly gathered (think about histograms if your data distribution is not uniform), and be sure they are up-to-date.
- Benchmark, Benchmark, Benchmark, because your environment is unique and a query that is awful in another environment may work like a charm on yours.
For more info on these, see Tom Kyte's excellent site asktom.oracle.com, and also read the very good Oracle docs.