Oracle - 10053 Event

 

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/