Cheatsheet by HC121104175033

VIEWS: 10 PAGES: 2

									                           SQL Tuning Cheat sheet
                            by Donald K. Burleson
-- 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 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 */ -- optimize to minimize response time
select /*+ first_rows_1000 */ -- 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');

								
To top