Oracle Performance Tuning

Here are some Oracle Performance tuning frequently asked Questions and Answers.

1) What are the different hints and their usages

• INDEX hint instructs the optimizer to use an index scan for the specified table. You can use the INDEX hint for function-based, domain, B-tree, bitmap, and bitmap join indexes.SELECT /*+ INDEX (employees emp_department_ix)*/
• LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. SELECT /*+ LEADING(e j) */
• PARALLEL hint instructs the optimizer to use the specified number of concurrent servers for a parallel operation. 
• NOAPPEND hint instructs the optimizer to use conventional INSERT by disabling parallel mode, and use the default in serial mode.
• ORDERED hint instructs Oracle to join tables in the order in which they appear in the FROM clause.How ever Oracle recommends that you use the LEADING hint, which is more versatile than the ORDERED hint.SELECT /*+ ORDERED */
• USE_HASH hint instructs the optimizer to join each specified table with another row source using a hash join.  SELECT /*+ USE_HASH(l h) */
• USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table. SELECT /*+ USE_NL(l h) */
• FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently.

2) What is the difference between Hash join and nested loop in query plan

• HASH joins are the usual choice of the Oracle optimizer when the memory is set up to accommodate them. In a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one) and probes the hash table for matches to it. And works only when COST BASED OPTIMISATION is use.
• The HASH join is similar to a NESTED LOOPS join in the sense that there is a nested loop that occurs—Oracle first builds a hash table to facilitate the operation and then loops through the hash table. When using an ORDERED hint, the first table in the FROM clause is the table used to build the hash table.
• The HASH join might be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index.

3) Hash join Vs other joins

• HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient.
• The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted
• As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins.
• As with SORT-MERGE joins, HASH joins use memory resources and can drive up I/O in the temporary tablespace if the sort memory is not sufficient (which can cause this join method to be extremely slow).

4) What is the form of execution for a query.
      When there are several where clauses available
             the execution is from Bottom right to Top left. 
5) How to improve query performance without changing it.  

• Check plan if there are any nested loops, if so you can improve it to use a Hash Join by setting PGA_AGGREGATE_TARGET  to a large enough value.
• Create unique indexes on the table could change the execution plan.
• The optimizer estimates cost of a query plan by computing the estimated I/Os and CPU.These metrics may be influenced by many initialization parameter and session settings at compile time, such as the DB_FILE_MULTI_BLOCK_READ_COUNT setting, system statistics, and so on.
• Check if the tables and Index are in same tablespace if so separate them to avoid contention.
• Use table partitions as and when required.