Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003

    Unanswered: Urgent: Rules for writing SQL queries


    I need to check a set of queries for proper tuning rules being followed.
    In other words I need to tune the select queries.

    Can you give me a set of rules to be followed while writing SQL SELECT queries to make the query execution faster.

    Any URL which gives these rules is also ok.

    We are using ALL_ROWS cost optimizer.

  2. #2
    Join Date
    Aug 2004
    Here are some important rules :

    - 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, and also read the very good Oracle docs.

    HTH and Regards,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts