View SQL Execution plans without using third-party tools

Without third-party tools, view SQL Execution plans SQL> connect sys as sysdba -- create the table SQL used by the execution plan> @? \ Rdbms \ admin \ utlxplan grants the Autotrace permission to each user by using the following methods. If you need to restrict the Autotrace permission, you can change the public authorization to the specific user authorization. Www.2cto.com 1. grant all permissions to the plan_table table to the public role SQL> grant all on plan_table to public; 2. create a role plustraceSQL> create role plustrace; 3. grant SQL> grant select on v _ $ sesstat to plustrace; SQL> grant select on v _ $ statname to plustrace; SQL> grant select on v _ $ session to plustrace;
4. Authorize the role plstrace to DBASQL> grant plustrace to dba with admin option; 5. the DBA grants the permission to publicSQL> grant plustrace to public; in this way, you can SET the following option set autotrace off ---------------- in sqlplus without generating the AUTOTRACE report, this is the default mode set autotrace on explain ------ AUTOTRACE only displays the optimizer execution path report set autotrace on statistics -- only displays the execution statistics set autotrace on ----------------- contains the execution plan and statistical information set autotrace traceonly ------ same as set autotrace on, however, the query output is not displayed.
Www.2cto.com SQL> SET autotrace on; SQL> select * from emp; empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- 7369 smith clerk 7902 17-12 month-80 1171.28 allen salesman 207499 20-2 month-81 7698 2342.56 300 ward salesman 307521 22-2 month-81 1830.13 500 307566 jones manager 7839 month-81 4355.7 207654 martin salesman 7698 month- 81 1830.13 1400 307698 blake manager 7839 01-5 months-81 4172.69 307782 clark manager 7839 09-6 months-81 3587.05 107788 scott analyst 7566 19-4 month-87 3000 207839 king president 17-11 month-81 7320.5 TURNER SALESMAN 7698 2196.15-81 307876 0 7788 adams clerk 1100 23-5 months-87 207900 7698 james clerk 1390.9 03-12 months-81 307902 7566 ford analyst 4392.3 03-12 months-81 207934 miller clerk 7782 23-1 month-82 1903.33 10 You have selected 14 rows for www.2cto.com. Execution Plan -------------------------------------------------------- 0 select statement Optimizer = CHOOSE1 0 table access (FULL) OF 'emp' Guo xuepeng