Oracle autotrace Parameters

Oracle autotrace parameter description SQL> set autotrace traceonly explainSP2-0613: unable to verify PLAN_TABLE format or entity SP2-0611: Error resolution when enabling EXPLAIN Report: www.2cto.com 1. log on to CONNECT/@ as SYSDBA as a SYS user; 1. create PLAN_TABLE (if not created) and run utlxplan. SQL (ORACLE_HOME/rdbms/admin) script; 2. create a plustrace role (if not created) and execute plustrce. SQL (ORACLE_HOME/sqlplus/admin/plustrce. SQL) script; 3. grant the plustrace role to the current user grant plustrace to scott; or grant plustrace to public; specific operations: www.2cto.com 1. Create a basic table and run the $ ORACLE_HOME/rdbms/admin/utlxplan script to create plan_tablescott @ ORCL> conn system/redhat. Use the system account to log on to Connected. system @ ORCL> start $ ORACLE_HOME/rdbms/admin/utlxplan -- execute the utlxplan script Table created. system @ ORCL> create public synonym plan_table for plan_table; -- create a public Synonym for the table plan_table synonym created. system @ ORCL> grant all on plan_table to public; -- Grant the synonym plan_table to all users grant succeeded. 2. Create a role and run $ ORACLE_HOME/sqlplus/admin/plustrce. SQL script system @ ORCL> conn/as sysdba -- use the sysdba account to log on to onnected. sys @ ORCL> start $ ORACLE_HOME/sqlplus/admin/plustrce. SQL -- execute the role creation script sys @ ORCL> create Role plustrace; role created. sys @ ORCL> grant select on v _ $ sesstat to plustrace; Grant succeeded. sys @ ORCL> grant select on v _ $ statname to plustrace; Grant succeeded. sys @ ORCL> grant select on v _ $ mystat to plustrace; Grant succeeded. sys @ ORCL> grant plustrace to dba with admin option; Grant succeeded. set Autotrace off: the default value. autotrace reports will not be generated. set autotrace on: Contains execution plans and statistics. set autotrace traceonly: equivalent to set autotrace on, but do not display the query output result set autotrace on explain: only display the optimizer execution path report set autotrace on statistics: show only execution statistics meaning of the columns in the Autotrace execution plan column name explanation 1 ID_PLUS_EXP row number of each step 2 PARENT_ID_PLUS_EXP level Number of the Parent in each step 3 PLAN_PLUS_EXP actual step 4 OBJECT_NODE_PLUS_EXP Dblink or in parallel query, AUTOTRACE Statistics is used to explain the number of commonly used columns. Column name interpretation 1 db block gets reads the number of blocks from the buffer cache 2 consistent gets reads the undo data from the buffer cache number of Blocks 3 physical reads number of blocks read from the disk 4 redo size DML generated redo size 5 sorts (memory) sort by memory 6 sorts (disk) sort by disk