Oracle SQL Tuning Quick Reference cheatsheet

SQL Tuning Cheat sheet
by Donald K. Burleson

-- Create a function-based index
create index idx_rtrim_title on titles ( rtrim(title));
 

Gather stats
 
exec dbms_stats.gather_schema_stats('scott?);
exec dbms_stats.gather_table_stats ('scott?,?emp)
 
-- system statistics during peak workload period
execute dbms_stats.gather_system_stats('Start');
-- delay while you watch the workload
execute dbms_stats.gather_system_stats('Stop');
 
Autotrace commands
 
set autotrace on 
set autotrace on explain
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonly statistics
 
Alter session commands
 
-- enable commands
alter system enable parallel query;
alter system enable parallel dml;
alter system enable parallel ddl;
 
-- change optimizer parms
alter session set optimizer_index_cost_adj=20 -- default is 100;
alter session set optimizer_index_caching=50 -- default is 0;
alter session set "_optimizer_cost_model"=io; -- default is cpu;
 
-- change optimizer modes
alter session set optimizer_mode=rule;
alter session set optimizer_mode=first_rows_1;
alter session set optimizer_mode=first_rows_10;
alter session set optimizer_mode=first_rows_100;
alter session set optimizer_mode=first_rows_1000;
alter session set optimizer_mode=all_rows;
 
Good Hints:
 
-- Table join order hints
select /*+ ordered */ -- join tables in order of FROM clause
select /*+ leading */ -- Driving table is 1st table in FROM clause
 
-- optimizer mode hints
select /*+ all_rows */ -- optimizer to minimize CPU resources
select /*+ first_rows_1 */ -- optimize to minimize response time
select /*+ first_rows_10 */ -- optimize to minimize response time
select /*+ first_rows_100 */ -- minimize response time
select /*+ first_rows_1000 */ -- minimize response time
 
insert /*+ append */ into. . .  - use a empty data block for inserts
 
select /*+ dynamic_sampling(customer 4) */ - Use dynamic sampling
 
select /*+ cardinality( gtt, 500 ) */ -- estimate result set size
 
select /*+driving_site(huge_table)*/ -- In distributed SQL, largest table is the driving table
 
select /*+ no_cpu_costing */ -- use older i/o-based optimizer costing
 
-- table join exclusion hints
select /*+ no_use_hash(titles, sales) */ -- don't use a hash join
select /*+ no_use_nl(titles, sales) */ -- don't use nested loops
select /*+ no_use_merge(titles, sales) */ -- don't use merge join
 
select /*+ parallel (book 4) */
 
 
 
 
Bad Hints: (for testing only)
 
select /*+ rule */ - great for testing for bad metadata
 
-- table access Hints
select /*+ index (emp, emp_lname_idx) */ -- only use for testing

select /*+ full use_hash(emp, jobs) */

 
Table join hints:
select /*+ use_hash(titles, sales) */ -- force a hash join
select /*+ use_nl(titles, sales) */ -- force nested loops
select /*+ use_merge(titles, sales) */ -- force merge join
 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.