DBMS_STATS
dbms_stats , ， ， ， gather_databases_stats,gather_table_stats,gather_schema_stats
1.
SQL> exec dbms_stats.gather_schema_stats('SCOTT',cascade=>TRUE);
PL/SQL procedure successfully completed.
2.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T',cascade=>TRUE);
PL/SQL procedure successfully completed.
cascade=>true ， ， cascade=>true, Oracle ， ， ， cascade
3.
SQL> exec dbms_stats.gather_index_stats('scott','idx_t');
PL/SQL procedure successfully completed.
4. ( )
SQL>set serveroutput on
SQL>begin
for ind in
(select object_name
from user_objects
where object_type='INDEX')
loop
dbms_output.put_line(
'Gathering Index Statistics for '|| ind.object_name ||'...');
dbms_stats.gather_index_stats(user,ind.object_name,estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '|| ind.object_name||' is Coplete!');
end loop;
end;
/
Gathering Index Statistics for IDX_EMP_HIRE.....
Gathering Index Statistics for IDX_EMP_HIRE is Coplete!
Gathering Index Statistics for IDX_ID.....
Gathering Index Statistics for IDX_ID is Coplete!
Gathering Index Statistics for IDX_ID2.....
Gathering Index Statistics for IDX_ID2 is Coplete!
Gathering Index Statistics for PK_DEPT.....
Gathering Index Statistics for PK_DEPT is Coplete!
Gathering Index Statistics for PK_EMP.....
Gathering Index Statistics for PK_EMP is Coplete!
Gathering Index Statistics for IDX_T.....
Gathering Index Statistics for IDX_T is Coplete!
Gathering Index Statistics for PK1_OBJECT_ID.....
Gathering Index Statistics for PK1_OBJECT_ID is Coplete!
Gathering Index Statistics for IDX_PART_ID.....
Gathering Index Statistics for IDX_PART_ID is Coplete!
Gathering Index Statistics for IDX_PART_NBR.....
Gathering Index Statistics for IDX_PART_NBR is Coplete!
Gathering Index Statistics for IDX_I.....
Gathering Index Statistics for IDX_I is Coplete!
Gathering Index Statistics for TEST_INDEX_B.....
Gathering Index Statistics for TEST_INDEX_B is Coplete!
PL/SQL procedure successfully completed.