SQL TUNING

SQL Statements are used to retrieve data from the database. We can get same results by writing different SQL queries. But use of the best query is important when performance is considered. So you need to SQL query tuning based on the requirement.

1. EXPLAIN and STORED OUTLINES

2. Hint Syntax

  • Top hints used
  • FIRST_ROWS hint
  • ALL_ROWS hint
  • FULL hint
  • INDEX hint
  • NO_INDEX hint
  • INDEX_JOIN hint
  • INDEX_ASC hint
  • INDEX_DESC hint
  • AND_EQUAL hint
  • INDEX_COMBINE hint
  • INDEX_FFS hint
  • ORDERED hint
  • LEADING hint
  • NO_EXPAND hint
  • DRIVING_SITE hint
  • USE_MERGE hint
  • PUSH_SUBQ
  • PARALLEL and NO_PARALLEL
  • APPEND and NOAPPEND
  • CACHE hint
  • CLUSTER hint
  • HASH hint
  • CURSOR_SHARING_EXACT hint

3. Query Tuning

  • What queries do I tune
  • When an index should be used
  • What if I forget the index?
  • Creating and checking an index
  • What if you create a bad index
  • Dropping an index
  • Increasing performance by indexing the SELECT and WHERE columns
  • Use the Fast Full Scan feature
  • Making queries “magically” faster
  • Caching a table into memory
  • Choosing between multiple indexes on a table
  • Indexes that get suppressed
  • Using the EXISTS clause and the nested Subquery

4. Table Joins and Other Advanced Tuning

  • Join methods
  • Table join initialization parameters
  • Comparing the primary join methods
  • Two-table join(cost based)
  • Two-table INDEXED join(cost based)
  • Forcing a specific join method
  • Eliminating join records
  • Bitmap join indexes
  • Tuning distributed queries
  • When you have everything tuned

5. Enhance Performance Using PL/SQL

  • DBMS_APPLICATION_INFO
  • Reduce PL/SQL program unit iterations and iteration time
  • Use ROWID for iterative processing
  • Standardize on data types
  • Reduce the calls to SYSDATE and MOD Function

6. Parallel Features

  • Basic concepts of parallel operations
  • Parallel DML and DDL statements and operations
  • Parallelism and partitions
  • Using EXPLAIN PLAN and AUTOTRACE on parallel operations




Subscribe to: Posts (Atom)

DBMS_XPLAN

One more way we can see the Explain Plan by using DBMS_XPLAN package. DBMS_XPLAN automatically queries the last plan in PLAN_TABLE. It u...

  • DBMS_XPLAN.DISPLAY_CURSOR
    DBMS_XPLAN.DISPLAY_CURSOR show actual execution plan from dynamic performance views. DBMS_XPLAN:   The DBMS_XPLAN package provides an eas...
  • Difference Between Collection and Record
    What is the difference between Collection and Records? Collection Record The internal components always have the same data type, ...
  • Bulk Bind in Oracle
    Bulk Bind or we can say FORALL in Oracle. The assigning of values to PL/SQL variables in SQL statements is called binding. The binding of ...