第41和42次：2015-04-25星期六

botang · 发表于 2015-4-27 10:02:57

select t.num_rows ,t.last_analyzed from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME' ;
select i.index_name,i.num_rows, i.last_analyzed from dba_indexes i where i.owner='HR' and i.table_name='T04209_UNAME';
select * from dba_histograms h where h.owner='HR' and h.table_name='T04209_UNAME';
select * from dba_tab_col_statistics tcs where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
begin
dbms_stats.gather_table_stats(ownname => 'HR',
tabname => 'T04209_UNAME',
estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size skewonly',cascade => true);
end;
begin
dbms_stats.gather_table_stats(ownname => 'HR',
tabname => 'T04209_UNAME',
estimate_percent => 100,
method_opt => 'for columns uvalue size 2 ',cascade => true);
end;
select * from dba_tab_stat_prefs;
select DBMS_STATS.GET_PREFS(pname => 'METHOD_OPT' ) from dual;
select DBMS_STATS.GET_PREFS(pname => 'STALE_PERCENT' ) from dual;
select DBMS_STATS.GET_PREFS(pname => 'PUBLISH' ) from dual;
begin
dbms_stats.gather_table_stats('HR','T04209_UNAME');
end;
begin
dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T04209_UNAME',
pname => 'STALE_PERCENT',
pvalue => '13');
end;
begin
dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T04209_UNAME',
pname => 'PUBLISH',
pvalue => 'FALSE');
end;
select * from dict where table_name like '%PENDING%';
select * from DBA_TAB_PENDING_STATS;
begin
dbms_stats.publish_pending_stats(ownname => 'HR',tabname => 'T04209_UNAME' ,no_invalidate => true);
end;
-----
begin
DBMS_STATS.RESET_PARAM_DEFAULTS ;
end;
select * from ( select * from v$sql order by elapsed_time desc ) where rownum <=6;

复制代码