课程第40次
AWR是工具调优，AWR研究的对象之一是优化器统计信息，而书上详细介绍了AWR是怎么研究它的这个对象的，所以优化器统计信息就属于本质调优。
（农业上播种的机器--->我们只要学会怎么按按钮就行了（你不需要学习植物学）-->但是如果播种机的说明文档里头拼命地介绍要挖多深坑。。。要间隔多少。-->就在直接介绍植物学）
selectt.num_rows from dba_Tables t where t.owner='HR' and t.table_name='TBIG';
select* from dba_tab_col_statistics t where t.owner='HR' and t.table_name='TBIG';
selectdbms_Stats.get_prefs(pname => 'METHOD_OPT')from dual;
selectdbms_Stats.get_prefs(pname => 'STALE_PERCENT')from dual;
selectdbms_Stats.get_prefs(pname => 'ESTIMATE_PERCENT')from dual;
selectdbms_stats.get_prefs(pname=>'NO_INVALIDATE') from dual;
select* from dba_tab_stat_prefs;
select text from hr.tbig;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG',
method_opt =>'for columns text size 254 for columns size auto');
end;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG');
end;
select* from dba_tab_stat_prefs where owner='HR';
select text from hr.tbig;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG',
method_opt =>'for columns text size 254 for columns size auto');
end;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG');
end;
select* from dba_tab_pending_stats where owner='HR';
begin
dbms_stats.set_table_prefs(ownname => 'HR',
tabname => 'TBIG',
pname => 'PUBLISH',pvalue => 'FALSE');
end;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG'
);
end;
begin
dbms_stats.publish_pending_stats(ownname => 'HR' ,tabname => 'TBIG');
end;
select* from dba_tab_pending_stats where owner='HR';
select* from dba_tables where table_name like 'WR_$\_%SNAPSHOT%' escape '\';
OWNERTABLE_NAMETABLESPACE_NAMECLUSTER_NAMEIOT_NAMESTATUSPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBACKED_UPNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSDEGREEINSTANCESCACHETABLE_LOCKSAMPLE_SIZELAST_ANALYZEDPARTITIONEDIOT_TYPETEMPORARYSECONDARYNESTEDBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEROW_MOVEMENTGLOBAL_STATSUSER_STATSDURATIONSKIP_CORRUPTMONITORINGCLUSTER_OWNERDEPENDENCIESCOMPRESSIONCOMPRESS_FORDROPPEDREAD_ONLYSEGMENT_CREATEDRESULT_CACHE
1SYSWRM$_SNAPSHOTSYSAUX VALID10 125565536104857612147483645 YESN8150007900 1 1 NENABLED812019/8/10 14:10:26NO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULT
2SYSWRM$_SNAPSHOT_DETAILSSYSAUX VALID10 125565536104857612147483645 YESN6723430003800 1 1 NENABLED67232019/8/10 14:10:27NO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULT
页:
[1]