统计信息收集

QINV_ORG_SUB_ACCESS

        gather_database_stats：收集数据库对象统计信息

        gather_dictionary_stats：收集数据字典对象统计信息

        gather_schema_stats：收集整个模式的所有对象统计信息

        gather_table_stats：收集表的对象统计信息

        gather_index_stats：收集索引的对象统计信息

---------------收集统计信息-------------
--系统的
execute dbms_stats.gather_system_stats('start');
execute dbms_stats.gather_system_stats('stop');
查询系统收集的信息
SELECT * FROM AUX_STATS$;

查询对象的统计信息
SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE table_name='MTL_MATERIAL_TRANSACTIONS'
--用户的
execute DBMS_stats.gather_schema_stats('TGK_INTERFACE_DIDI',100,CASCADE=>TRUE);
execute DBMS_stats.gather_schema_stats('BBB',100,CASCADE=>TRUE);
execute DBMS_stats.DELETE_SCHEMA_STATS('SYS');
execute DBMS_stats.gather_schema_stats('SYS');
--表和索引的
execute DBMS_stats.gather_table_stats('XA_USER','TBUSER');
execute DBMS_stats.gather_INDEX_stats('GL','gl_code_combinations');
execute DBMS_stats.gather_INDEX_stats('FA','FA_MASS_ADDITIONS_N5');
execute DBMS_stats.gather_table_stats('FA','FA_MASS_ADDITIONS');
execute DBMS_stats.gather_table_stats('FA','FA_MASS_ADDITIONS'，drgee>'4'); 并行

分析某个用户下的所有索引
declare
v3 varchar2(100);
begin
v3:='a';
for k in (select index_name from dba_indexes where table_name='FA_MASS_ADDITIONS' and owner='FA') loop
v3:=k.index_name;
dbms_stats.gather_index_stats('FA',V3);
END LOOP;
END;
/

分析表对应的索引的统计信息
declare
v1 dba_indexes.owner%type;
v2 dba_indexes.index_name%type;
cursor c2 is select owner,index_name from dba_indexes where table_name in ('GL_CODE_COMBINATIONS' ,'GL_ACCOUNT_HIERARCHIES','GL_BC_PACKETS','GL_JE_BATCHES','GL_JE_HEADERS','GL_JE_LINES');
begin
if not c2%isopen then
open c2;
end if;
loop
fetch c2 into v1,v2;
dbms_stats.gather_index_stats(V1,V2);
exit when  c2%notfound;
end loop;
close c2;
end;
/

分析某个用户下的所有表
declare
v2 varchar2(100);
begin
v2:='a';
for k in (select table_name from user_tables order by 1) loop
v2:=k.table_name;
dbms_stats.gather_table_stats('XJPRD',V2);
END LOOP;
END;
/

分析多个表的统计信息
declare
v2 varchar2(100);
begin
v2:='a';
for k in (select table_name from dba_tables where table_name in ('GL_ACCOUNT_HIERARCHIES','GL_BC_PACKETS','GL_JE_BATCHES','GL_JE_HEADERS','GL_JE_LINES') )loop
v2:=k.table_name;
dbms_stats.gather_table_stats('GL',V2);
END LOOP;
END;
/

--列的
execute DBMS_stats.gather_table_stats('SCOTT','T1',method_opt=>'FOR COLUMNS SAL');  

------------------------------------------
alter session set events '10046 trace name context forever, level 12';
1: Standard SQL tracing 
4: Level 1 + tracing bind variables 
8: Level 1 + tracing wait events 
12: Level 4 + Level 8 (SQL tracing, bind vars and wait events)