Query Plan - Query Tuning

Query Tuning

The query optimizer will not always choose the best query plan for a given query. In some databases the query plan can be reviewed, problems found, and then the query optimizer given hints on how to improve it. In other databases alternatives to express the same query (other queries that return the same results) can be tried. Some query tools can generate embedded hints in the query, for use by the optimizer.

Some databases like Oracle provide a Plan table for query tuning. This plan table will return the cost and time for executing a Query. In Oracle there are 2 optimization techniques:

  1. CBO or Cost Based Optimization
  2. RBO or Rule Based Optimization

The RBO is slowly being deprecated. For CBO to be used, all the tables referenced by the query must be analyzed. To analyze a table, a package DBMS_STATS can be made use of.

The others methods for query optimization include:

  1. SQL Trace
  2. Oracle Trace
  3. TKPROF
  • Video tutorial on how to perform SQL performance tuning with reference to Oracle
Database management systems
  • Database models
  • Database normalization
  • Database storage
  • Distributed DBMS
  • Federated database system
  • Referential integrity
  • Relational algebra
  • Relational calculus
  • Relational database
  • Relational DBMS
  • Relational model
  • Object-relational database
  • Transaction processing
Concepts
  • Database
  • ACID
  • CRUD
  • Null
  • Candidate key
  • Foreign key
  • Primary key
  • Superkey
  • Surrogate key
  • Armstrong's axioms
  • NoSQL
Objects
  • Relation
    • table
    • column
    • row
  • View
  • Transaction
  • Log
  • Trigger
  • Index
  • Stored procedure
  • Cursor
  • Partition
Components
  • Concurrency control
  • Data dictionary
  • JDBC
  • XQJ
  • ODBC
  • Query language
  • Query optimizer
  • Query plan
Functions
  • Administration and automation
  • Query optimization
  • Replication
Database products
Object-oriented
comparison
Relational
comparison
Document-oriented
NewSQL

Read more about this topic:  Query Plan

Famous quotes containing the word query:

    “Such condition of suspended judgment indeed, in its more genial development and under felicitous culture, is but the expectation, the receptivity, of the faithful scholar, determined not to foreclose what is still a question—the “philosophic temper,” in short, for which a survival of query will be still the salt of truth, even in the most absolutely ascertained knowledge.”
    —Walter Pater (1839–1894)