Loadingqa804

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