Oracle SQL Tuning Cheat sheet

 ORACLE SQL TUNING CHET SHEET

-- Create a function-based index

create index idx_rtrim_title on titles ( rtrim(title));

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 1^st 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) */ -- optimize to 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(hugetable)*/ -- In distr SQL, largest table driving

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

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');