Check the execution plan of SQL without using third-party tools

Check the execution plan of SQL without using third-party tools
the
SQL> connect sys as sysdba
–Create the table used by the execution plan
SQL> @?\rdbms\admin\utlxplan
The Autotrace permission can be granted to each user through the following methods. If you need to limit the Autotrace permission, you can change the authorization to the public to the authorization to a specific user.
www.2cto.com
1. Assign all permissions to the plan_table table to the public role
SQL> grant all on plan_table to public;
2. Create a role plustrace
SQL> create role plustrace;
3. Authorize to the plustrace role
SQL> grant select on v_$sesstat to plustrace;
SQL> grant select on v_$statname to plustrace;
SQL> grant select on v_$session to plustrace;
the
4. Authorize the role plstrace to DBA
SQL> grant plusrace to dba with admin option;
5. DBA grants authority to public
SQL> grant plusrace to public;
In this way, users can set the following options in sqlplus
SET AUTOTRACE OFF —————- Do not generate AUTOTRACE reports, this is the default mode
SET AUTOTRACE ON EXPLAIN —— AUTOTRACE only displays the optimizer execution path report
SET AUTOTRACE ON STATISTICS — show only execution statistics
SET AUTOTRACE ON —————– Include execution plan and statistics
SET AUTOTRACE TRACEONLY —— Same as set autotrace on, but does not display query output
the
www.2cto.com
SQL>SET autotrace on;
SQL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ———- – ——— ———- ———-
7369 SMITH CLERK 7902 17-Dec-80 1171.28 20
7499 ALLEN SALESMAN 7698 20-2-81 2342.56 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1830.13 500 30
7566 JONES MANAGER 7839 February-April -81 4355.7 20
7654 MARTIN SALESMAN 7698 28-9-81 1830.13 1400 30
7698 BLAKE MANAGER 7839 January-May -81 4172.69 30
7782 CLARK MANAGER 7839 September-June -81 3587.05 10
7788 SCOTT ANALYST 7566 19-Apr -87 3000 20
7839 KING PRESIDENT 17-11-81 7320.5 10
7844 TURNER SALESMAN 7698 08-Sep-81 2196.15 0 30
7876 Adams Clerk 7788 23-May-87 1100 20
7900 JAMES CLERK 7698 03-12-81 1390.9 30
7902 FORD ANALYST 7566 03-12-81 4392.3 20
7934 MILLER CLERK 7782 23-Jan-82 1903.33 10
www.2cto.com
14 rows have been selected.
Execution Plan
————————————————– ——–
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'
the
the
Author Guo Xuepeng