Monday, December 24, 2007

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

1 comment:

Xia Zhang said...

http://www.shopbestgoods.com/
http://www.bestcustomsonline.com/
http://www.nike-jordanshoes.com/
http://www.polo-tshirts.com/
http://www.burberry-factory.com/
http://www.kate-spades.com/
http://www.barbour-factory.com/
http://www.coachlosangeles.com/
http://www.official-coachoutlet.com/
http://www.louisvuittonas.com/
http://www.burberryoutlet2014.com/
http://www.official-mkoutlet.com/
http://www.official-pradaoutlet.com/
http://www.beatsbydreoutlet.net/
http://www.michaelkorsonlineusa.com/
http://www.northsclearance.com/
http://www.ralph-laurensale.com/
http://www.gucci-shoesuk2014.com/
http://www.michael-korsusa.com/
http://www.polo-outlets.com/
http://www.hermes-outletonline.com/
http://www.ralphslauren.co.uk/
http://www.marcjacobsonsale.com/
http://www.mcmworldwides.com/
http://www.warmbootssale.com/
http://www.salongchamppairs.com/
http://www.canada-gooser.com/
http://www.michaelkors.so/
http://www.oakley-sunglassoutlet.com/
http://www.north-faceoutlets.net/
http://www.moncler-clearance.com/
http://www.woolrich-clearance.com/
http://www.barbour-jacketsoutlet.com/
http://www.moncler-jacketsoutletonline.com/
http://www.monsterbeatsbydres.net/
http://www.lv-guccishoesfactory.com/
http://www.cheapdiscountoutlet.com/
http://www.coachsfactoryoutlet.com/
http://www.coach-blackfriday2014.com/
http://www.coach-storeoutletonline.com/
http://www.coach-factorysoutletonline.com/
http://www.coachccoachoutlet.com/
http://www.coach-factories.net/
http://www.coach-pursesoutletonline.com/
http://www.coach-outletsusa.com/
http://www.zxcoachoutlet.com/
http://www.mischristmas.com/
http://www.misblackfriday.com/
http://www.newoutletonlinemall.com/
http://www.ralphlaurenepolo.com/
http://michaelkorsoutlet.mischristmas.com/
http://mcmbackpack.mischristmas.com/
http://monsterbeats.mischristmas.com/
http://northfaceoutlet.mischristmas.com/
http://mk.misblackfriday.com/
http://coachoutlet.misblackfriday.com/
http://coachfactory.misblackfriday.com/
http://uggaustralia.misblackfriday.com/
http://coachpurses.misblackfriday.com/
http://coachusa.misblackfriday.com/
http://coach.misblackfriday.com/
http://michaelkorss.misblackfriday.com/
http://michaelkors.misblackfriday.com/
http://airmax.misblackfriday.com/
http://michael-kors.misblackfriday.com/
https://twitter.com/CoachOutlet2014
https://www.facebook.com/coachoutletstoreonline
https://www.facebook.com/ralphlaurenoutletonline