SQL Tuning Introduction

• NOT IN vs NOT EXIST

NOT IN subqueries may return null values

• Booleans predicate in WHERE clause

Placing most restrictive Boolean predicate first after WHERE clause is a good practice

• Tables & Index Statistics vs SQL Profiles & Optimizer Plan stability

The dynamic shop does not use plan stability because they want their SQL plan to change whenever there is major change inside tables

•  Adaptative Cursor Sharing

Shop that are plugged with non-reusable SQL can adopt either persistent or dynamic philosophy. To use persistent with non-reusable SQL DBA will set CUSROR_SHARING=force

Persistent shop : only one optimal SQL plan for any SQL, SQL plan rarely changed regardless how often statistics are computed

• PL/SQL procdeures

They are load once in Shared pool and remain there unless they become paged out, subsequent executions are faster than SQL

• Function Base index Statistics

dbms_stats.gather_table_stats(owner=>'SCOTT', table=>'SALES'

method_opt=>'FOR ALL COLUMNS FOR COLUMNS(compute_total(cust_sales))') : compute_total is a function

• DBMS_SQLTUNE.import_sql_profile

Use of sqlprof_attr( init parameter can be changed)

• dbms_stats.gather_system_stats

CPU speed, single disk read speed, multi-block reads

• Reducing hard parse

Place SQL inside stored procedures

Use bind variable

A high value for "parse call" (>10/sec) ==> high income unique SQL statement or SQL is not reentrant

• Optimizer_mode

opt_param hint

select /*+ opt_param('optimizer_mode','first_rows_10' */  col1 ...

all_rows : minimize computing resources and favors full-table scans

For batch use all_rows

first_row_1, first_row_10, first_rows_100, first_rows_1000 : minimize time response and favors index access

Index access involves additional I/O vs Full scan : multiple blockread

choose :  all_rows or rule depending on statistics

• Optimizer_index_cost_adj 

Alter the costing algorithm for access path involving indexes, the smaller the value, the lower the cost of index access.

The small value 10 to 30 index access become less costly ==> OLTP system

default value 100 

Usefull <10g, dbms_stats.gather_system_stats make this parameter less useful
0 to 10 000

-- measuring I/O costs on databas and get a recommended starting value for optimizer_index_cost_adj
col c1 heading 'Average Waits|forFull| Scan Read I/O'
col c2 heading 'Average Waits|for Index|Read I/O'
col c3 heading 'Percent of| I/O Waits|for Full Scans'
col c4 heading 'Percent of| I/O Waits|for Index Scans'
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj'
select
a.average_wait c1, b.average_wait c2, a.total_waits /(a.total_waits + b.total_waits) c3, b.total_waits /(a.total_waits + b.total_waits) c4,
format 9999.999 format 9999.999 format 9.99 format 9.99 format 999
(b.average_wait / a.average_wait)*100 from
v$system_event a,
v$system_event b where
a.event = 'db file scattered read' and
b.event = 'db file sequential read' ;

• Optimizer_index_caching 

How much index is likely to be in the RAM

Affects decision to use an index for a table join(nested loops) or favor a full-table scan

• db_file_multiblock_read_count

Autotuned > 10GR2

Use plan10g.sql

• Setting SQL Opimizer Cost Model

_optmizer_cost_model : choose, io, cpu(default)