OracleSQL execution plan and analysis summary

Oracle has multiple ways to obtain a query plan for an SQL statement, such as using the explain Plan command and pressing the F5 shortcut in PLSQLDeveloper, but it is used for SQL optimization,

Oracle has multiple ways to obtain a query plan for an SQL statement, such as using the explain plan command and pressing the F5 shortcut in PLSQL Developer, but as an SQL optimization,

Oracle has multiple ways to obtain a query plan for an SQL statement, such as using the explain plan command and pressing the F5 shortcut in PLSQL Developer. However, it is easier to use it for SQL optimization, use the set autotrace on and set timing on methods in sqlplus to get the actual SQL running time and query plan;

First, autotrace in SQLPLUS is a simple and convenient tool for analyzing SQL Execution plans. autotrace does not generate trace files. The SQL Execution Plan and execution status provided by the autotrace tool can provide a basis for optimizing the SQL statements and compare the results.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle SQL engine architecture

Oracle SQL Basics

Oracle SQL advanced

AutoTrace usage:

Set autot [RACE] {OFF | ON | TRACE [ONLY]} | [EXP [LAIN] [STAT [ISTICS]

For example:

Set autotrace off stop AutoTrace
Set autotrace on enables AutoTrace to display AutoTrace information and SQL Execution results
Set autotrace traceonly enables AutoTrace and only displays AutoTrace information.
Set autotrace on explain to enable AutoTrace. Only the Autotrace EXPLAIN information is displayed.
Set autotrace statistics to enable AutoTrace. Only the STATISTICS information of Autotrace is displayed.

AutoTrace enabled:

1. Use the dba user sys to set permissions and execute the script plustrce. SQL.

The Oracle10g storage directory is $ {ORACLE installation directory} \ product \ 10.2.0 \ db_1 \ sqlplus \ admin \ plustrce. SQL.

The Oracle11g storage directory is $ {ORACLE installation directory} \ product \ 11.2.0 \ dbhome_1 \ sqlplus \ admin \ plustrce. SQL.

The plustrce. SQL script is used to create a role plustrace for the SQL * Plus Set AutoTrace command to access the dynamic performance view. The script must be executed under the permissions of the DBA role,

After the execution is complete, grant permissions to users who need to use the AutoTrace function.

The content of the plustrce. SQL script is as follows:

Set echo on

Drop role plustrace;
Create role plustrace;

Grant select on v _ $ sesstat to plustrace;
Grant select on v _ $ statname to plustrace;
Grant select on v _ $ mystat to plustrace;
Grant plustrace to dba with admin option;

Set echo off

For more details, please continue to read the highlights on the next page: