Oracle statistics collection

Oracle statistics collection
Collect statistics

1: Create an analysis table

$ Cd $ ORACLE_HOME/rdbms/admin $ sqlplus/as sysdba> @ utlxplan. SQL

2: For convenience, you can create a synonymous table (by default, only sys users can use it)
SQL> create public synonym plan_tables for plan_table;


3: grant the table permission to the owner or the specified person.
SQL> grant all on plan_tables to public;


4: Create the role of plustrace

$ Cd $ ORACLE_HOME/sqlplus/admin $ sqlplus/as sysdba> @ plustrce. SQL

5: grant its permission to public
SQL> grant plustrace to public;


6. Set the display mode.

SQL> set autotrace off; # this option is used by default. autotraceSQL> set autotrace explain is not displayed; # only show the optimizer execution path SQL> set autotrace traceonly; # SQL statements output by unrealistic users> set autotrace on; # report display optimizer execution path