helping-optimizer-to-take-decision-10g.

Give more knowledge to cost based optimizer about object statistics
and make it intelligent..

U remember Knowledge is Power - Aristotle

-- Gather stats for a table. Auto sampling and auto number of histogram
-- buckets, might not work in previous Oracle version.begin
dbms_stats.gather_table_stats('&OWNER','&TABLE_NAME',null,
DBMS_STATS.AUTO_SAMPLE_SIZE -- put null or percentage for older oracle
,false,'FOR ALL COLUMNS SIZE AUTO' -- put 1 for older oracle
,1,'GLOBAL',false,null,null,null,false);
end;
/
--Gather stats for a schema
begin
dbms_stats.gather_schema_stats('&OWNER',DBMS_STATS.AUTO_SAMPLE_SIZE,false,
'FOR ALL COLUMNS SIZE AUTO',1,'GLOBAL',true,null,null,'GATHER AUTO');
end;
/
--See some column statistics
select column_name,num_distinct,rawtohex(low_value),rawtohex(high_value),density
from dba_tab_col_statistics
where owner='&OWNER' and table_name='&TABLE_NAME';

--See some histogram info
select * from dba_tab_histograms
where owner='&OWNER' and table_name='&TABLE_NAME'
order by column_name, endpoint_number;

--Old fashion analyze
analyze table &OWNER.&TABLE_NAME compute statistics;
analyze table &OWNER.&TABLE_NAME estimate statistics;
analyze table &OWNER.&TABLE_NAME delete statistics;

--Turning table monitoring on:
select 'alter table "'||owner||'"."'||table_name||'" monitoring;' stmt
from all_tables
where monitoring ='NO' and tablespace_name <>'SYSTEM';