Event 10053 is used to dump optimizer decisions to trace file.
Usage:
{
alter session set tracefile_identifier=' id_flag';
alter session set events '10053 trace name context forever, level 1';
issue ' your sql';
alter session set events '10053 trace name context off';
}
It works only to CBO, if found nothing in trace file, then :
(1) issue ' show parameter optimizer_mode', if mode is CHOOSE, then the lack of statistics on table queried will result in that SQL is parsed using RBO.
(2) gather statistics on the table:
exec dbms_stats.gather_table_stats('schema','table_name');
{ exec dbms_stats.gather_database_stats;
exec dbms_stats.gather_schema_stats('schema');
exec dbms_stats.gather_index_stats('schema','index_name');
exec dbms_stats.delete_database_stats;
exec dbms_stats.delete_schema_stats('schema');
exec dbms_stats.delete_table_stats('schema','table_name');
exec dbma_stats.delete_index_stats('schema','index_name');
}
(3) re-issue sql to generate trace file and than analyze.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24123206/viewspace-692458/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24123206/viewspace-692458/