Brief Analysis of Oracle execution plan

Brief Analysis of Oracle execution plan

1. How to open the execution plan:

Turn on the Execution Plan (execute set autotrace traceonly/off) and if you are prompted for a SP2-0611, SP2-0618, follow these steps to create the appropriate user.

1. log on to SYS and run @ $ Oracle_HOME/rdbms/admin/utlxplan. SQL to create PLAN_TABLE;

2. Run @ $ Oracle_HOME/sqlplus/admin/plustrce. SQL to create the plustrace role;

3. grant the user a grant of plustrace to public;

Run set autotrace traceonly. You can also use the following methods:

1. explain plan for select * from student;

2. select * from table (DBMS_XPLAN.display );

Ii. Analysis of the execution plan:

Currently, the Oracle optimizer uses the cost-based cbo method to optimize SQL statements. Therefore, the optimizer's judgment relies heavily on the statistical analysis information of database objects. Only the correct object information (DBMS_STAT) provided to the optimizer can the optimizer make the right choice.

The optimizer selection method is similar to the following methods in this article. As long as you correctly understand the content in the execution plan, you can improve the SQL Performance Based on the Data Volume and index of the database objects.

When looking at the execution plan, the Operation column first refers to the content of the current Operation. The ROW with the largest indentation is the first step to be executed. For the rows with the same indentation as the two rows, the ROW column is first executed, it is the result set returned by Oracle to estimate the current row; COST and TIME are the COST and TIME estimated by Oracle.

Oracle Data Access methods include:

Full table scan: scans records in all tables. Multiple read operations allow one I/O to read multiple data blocks. This method is often used when table fields do not involve indexes. Full table scan is not recommended for large tables, unless the result data exceeds 10% of the total table data;

Table Access by ROWID: only one data block can be read at a time. You can use rowid to read Table fields. rowid may be the rowid of the index key value;

Index Scan: The Index Scan first scans the Index to obtain the rowid value. The data in this step is read directly from the memory, which is faster. Then, the specific data is read through the rowid, if the table is large, the efficiency will decrease. There are four types of index scanning:

1. unique index scan: If the table field has a UNIQUE or primary key constraint, Oracle performs unique index scan. This scan method has extreme conditions and has fewer results;

2. index range scan is the most common index scan method. Index range scanning is used on non-unique indexes.

Three cases of using index rang scan:

1) the following range operators are used on the unique index column (><>>=<= between, etc)

2) on the composite index, only some columns are used for query, resulting in multiple rows being queried.

3) any queries on non-unique index columns.

3. full index scan: In this case, the queried data belongs to the index field and generally contains sorting operations.

4. index fast full scan: this is the case if the queried data belongs to the index field and is not sorted. The conditions are more extreme and appear less;

The table connection methods are as follows:

1. Sort-Merge Join (Sort Merge Join): This sorting type has a large limit and is rarely used;

Internal Connection process:

1) first generate the data required by Table 1, and then sort the data according to the joined column;

2) generate the data required by Table 2 and sort the data in the joined column corresponding to table 1;

3) At last, the sorted rows on both sides are put together for the merge operation to connect the two tables according to the connection conditions.

2. Nested loop (Nested Loops)

The connection process is a two-layer nested loop, so the number of Outer Loops is smaller, the better. If driving row source (External table) is small and has a unique index on inner row source (internal table), or has a highly selective non-unique index, using this method can improve efficiency.

3. Hash Join)

A relatively good efficiency will be achieved when two large row sources are connected, and a row source can achieve better efficiency when compared with an hour.

Iii. Meaning of statistical data:

1. Number of recursive calls of recursive cballs;
2. db block gets is the latest block data read from the memory during current operations. It is not the number of blocks in the case of consistent read, that is, the number of blocks read through update/delete/select for update;
3. The number of items read by consistent gets in the consistent read State during current operations, that is, the number of items read by select without for update;
4. physical reads physical read: the number of data blocks read by Oracle from the disk is mainly caused by the absence of these blocks in the database cache, full table scan, and disk sorting. Logical read refers to the number of data blocks read from the memory of Oracle. Generally, it is 'consistent gets' + 'db block gets '. If the required data block cannot be found in the memory, it needs to be obtained from the disk, so 'phsical reads' is generated '.
5. redo size redo logs generated during SQL Execution;
6. 423 bytes sent via SQL * Net to client data sent to the client through the network
7. 419 bytes received ed via SQL * Net from client data received from client through network
8. SQL * Net roundtrips to/from client
9. Sorting of sorts (memory) in memory;
10. Sorting of sorts (disks) in the hard disk;
11. rows processed

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------