@[TOC](Oracle SQL Tuning Series understand execution plan Explain)

1. Introduction to article writing

SQL Tuning series blog links: SQL tuning column

Once read “harvest, not just SQL tuning” book, this book is a very good tuning type of book written by domestic DBA, is some very good tuning experience to share. Although I read it again and took notes on the book, I feel that I have gained something, but I still feel that I lack practice in practical practice. Just recently, there was another SQL tuning training activity. After attending it, I reviewed the Oracle execution plan again, so I sorted out the data and made notes to share them

2. What is an execution plan?

An execution plan is a description of the execution or access path of a query statement in Oracle.

An execution plan describes what the SQL engine does to execute SQL statements. When analyzing performance issues related to SQL statements or simply questioning the query optimizer’s decisions, you must know the execution plan; So execution plans are often used for SQL tuning.

3. How to view the execution plan?

There are many ways to view an Oracle execution plan, refer to my previous reading notes for details, but this blog will cover only the most common methods

SQL > execute a plan on sqlPlus

explain plan for select 1 from t
Copy the code

However, if you are using PLSQL, you can use the query execution plan provided by PLSQL, which is F5

Open PLSQL Tools -> Preferences -> Window Types -> Schedule window and add the parameters needed to execute the plan here

I’m going to take a SQL, and I’m going to execute it with PLSQL, which is a simple use of PLSQL

Explain what these parameters mean:

  • Rows: The number of Rows in the result set returned by the current step estimated by Oracle
  • Bytes: indicates the number of Bytes returned by the SQL step
  • COST and CPU COST: the estimated COST and CPU COST of executing this step by Oracle
  • Time: the Time estimated by Oracle to execute SQL for a step

4. View the actual execution plan

I also used to press F5 to view the execution plan. However, I recently went to training and heard from a DBA that sometimes this method cannot obtain the real execution plan and the information collected is not comprehensive. Then how to view the real information of the SQL execution process? Take notes on lessons learned from the training

Sqlplus window execution:

  • Step1: set the statistics_level
alter session set statistics_level=ALL;
Copy the code
  • Step2: execute the service SQL
select /*+ monitor */ * from.where. ;Copy the code
  • Step3: for styles, set linesize
set linesize 200 pagesize 300;
Copy the code
  • Step4: query the actual execution plan
select * from table(dbms_xplan.display_cursor(null.null.'iostats last'));
Copy the code

Sqlplus is generally available to database administrators, but if you are not a DBA and can only use PLSQL Developer, you can only use the following methods, which are learned from training

Using stored procedures, SQL:

declare
  b1 date;
begin
  execute immediate 'alter session set statistics_level=ALL';
  b1 := sysdate - 1;
  for test in (
               SQL > alter table SQL > alter table SQL ) * /
               select * from t) loop
    null;
  end loop;
  for x in (select p.plan_table_output
              from table(dbms_xplan.display_cursor(null.null.'advanced -bytes -PROJECTION allstats last')) p) loop
    dbms_output.put_line(x.plan_table_output);
  end loop;
  rollback;
end;
/
Copy the code

Two types of Windows:

  • 1, SQL window, after executing SQL can only go to output view;
  • 2, Command window, need to set firstset serveroutput on size unlimited, and then execute the stored procedure

Output or command window to view the actual execution plan and statistics:

SQL_ID  abk3ghv9u1tvb, child number 0
-------------------------------------
SELECT /*+ monitor */ * FROM APPR_HANDLE_INFO
 
Plan hash value: 885170757
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |      1 |        |   210 (100) | |72059 |00:00:00.06 |    2460 |
|   1 |  TABLE ACCESS FULL| APPR_HANDLE_INFO |      1 |  32752 |   210   (1) |00:00:03 |  72059 |00:00:00.06 |    2460 |
------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id) :-------------------------------------------------------------
 
   1 - SEL$1 / APPR_HANDLE_INFO@SEL$1
 
Outline Data
-------------
 
  /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS  OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "APPR_HANDLE_INFO"@"SEL$1") END_OUTLINE_DATA */
 
Copy the code

Key information:

  • Starts: indicates the number of times the SQL is executed
  • E-rows: The expected number of Rows for the execution plan
  • A-rows: The actual number of Rows returned. E-rows is compared to A-rows to see which step of the execution plan is broken
  • A-time: indicates the actual execution Time of each step
  • Buffers: Logical or consistent reads that are actually performed at each step

5. Understand Oracle execution plan

Now that we have described how to view the execution plan, let’s briefly introduce some basic methods and related theoretical knowledge

5.1 check the explain

Select a complex SQL statement and execute:

F5 Viewing:

SQL_ID  4qfq3t2ukm0y1, child number 0
-------------------------------------
SELECT /*+ monitor*/ A.USER_CODE, A.FULL_NAME, A.USER_PWD, C.UNIT_CODE, 
C.UNIT_NAME FROM BASE_USER A LEFT JOIN (SELECT UR.USER_CODE, 
UR.UNIT_CODE FROM APPR_USER_ROLE UR WHERE UR.USER_ROLE < 10) B ON 
A.USER_CODE = B.USER_CODE LEFT JOIN LZCITY_APPROVE_UNIT_INFO C ON 
B.UNIT_CODE = C.UNIT_CODE WHERE C.UNIT_CODE ='15803'
 
Plan hashvalue: 3288287052 ------------------------------------------------------------------------------------------------------------------------ ------------------------ | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | | 0 SELECT STATEMENT | | 1 | | 3 (100) | | | 16 00:00:00. 01 38 | | | 1 | NESTED LOOPS | | 1 | 1 3 (0) | | 00:00:01 16 | | 00:00:00. 01 38 | | | 2 | NESTED LOOPS | | | 1 | 3 | (0) 00:00:01 16 | | 00:00:00. 01 22 | | | 3 | NESTED LOOPS | | 1 | 1 | 2 (0) | 00:00:01 16 | | 00:00:00. 01 5 | | | | 4 TABLE ACCESS BY INDEX ROWID | LZCITY_APPROVE_UNIT_INFO | 1 | | 1 (0) | 00:00:01 | 1 | 00:00:00. 01 | 3 | | | * 5 INDEX UNIQUE SCAN | PK_LZCITY_APPROVE_UNIT_INFO | | 1 | 0 (zero) | | 1 | 00:00:00. 01 | 2 | | | * 6 INDEX RANGE SCAN | PK_APPR_USER_ROLE | 1 | 1 1 (0) | | 00:00:01 16 | | 00:00:00. 01 2 | | | * 7 | INDEX UNIQUE SCAN | PK_BASE_USER 16 | 1 | 0 (zero) | | | | 16 00:00:00. | 01 17 | | | 8 TABLE ACCESS BY INDEX ROWID | BASE_USER 16 | | | 1 (0) | 00:00:01 16 | | 00:00:00. 01 16 | | ------------------------------------------------------------------------------------------------------------------------ ------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$E3445A69
   4 - SEL$E3445A69 / C@SEL$4
   5 - SEL$E3445A69 / C@SEL$4
   6 - SEL$E3445A69 / UR@SEL$2
   7 - SEL$E3445A69 / A@SEL$3
   8 - SEL$E3445A69 / A@SEL$3
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$E3445A69")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$A2E96217")
      OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "B"@"SELThe $1")
      OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "C"@"SEL$4")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$E9F4A6F9")
      MERGE(@"SEL$80808B20")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$80808B20")
      MERGE(@"SEL$4")
      MERGE(@"SEL$F1D6E378")
      OUTLINE(@"SELA $5")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$F1D6E378")
      MERGE(@"SELThe $1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SELThe $1")
      INDEX_RS_ASC(@"SEL$E3445A69" "C"@"SEL$4" ("LZCITY_APPROVE_UNIT_INFO"."UNIT_CODE"))
      INDEX(@"SEL$E3445A69" "UR"@"SEL$2" ("APPR_USER_ROLE"."UNIT_CODE" "APPR_USER_ROLE"."USER_CODE" "APPR_USER_ROLE"."AREA_SEQ" 
              "APPR_USER_ROLE"."USER_ROLE"))
      INDEX(@"SEL$E3445A69" "A"@"SEL$3" ("BASE_USER"."USER_CODE"))
      LEADING(@"SEL$E3445A69" "C"@"SEL$4" "UR"@"SEL$2" "A"@"SEL$3")
      USE_NL(@"SEL$E3445A69" "UR"@"SEL$2")
      USE_NL(@"SEL$E3445A69" "A"@"SEL$3")
      NLJ_BATCHING(@"SEL$E3445A69" "A"@"SEL$3")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("C"."UNIT_CODE"='15803')
   6 - access("UR"."UNIT_CODE"='15803' AND "UR"."USER_ROLE"<10)
       filter("UR"."USER_ROLE"<10)
   7 - access("A"."USER_CODE"="UR"."USER_CODE")
 

Copy the code

5.2 Explain execution sequence

Therefore, whether you use the F5 or set statistics_level=ALL mode, there are Operation parameters. Operation indicates the SQL execution process. There are two rules to check the execution:

  • According to Operation indentation, the one with the most indentation is executed first.
  • Operation is indent the same, the uppermost is executed first;

As shown in the figure, the execution order can be obtained according to the rules: INDEX UNIQUE SCAN->TABLE ACCESS BY INDEX ROWID->INDEX RANGE SCAN ->NESTED LOOPS ->INDEX UNIQUE SCAN->NESTED LOOPS ->TABLE ACCESS BY INDEX ROWID->NESTED LOOPS-> SELECT STATEMENT

5.3 Methods of accessing data

If the index data does not match the target SQL, the index will be returned to the table. If the index data matches the target SQL, the index will not be returned to the table.

Oracle directly accesses table data in two methods: one is full table scan; The other is ROWID scanning

5.3.1 TABLE ACCESS FULL
  • Full table scan; (TABLE ACCESS FULL)

A full table scan is a method that Oracle uses to directly access data. A full table scan starts from the first BLOCK of the first EXTENT to the High Water Mark of a table. All data blocks within the EXTENT will be detected

A full table scan is to use multiple data block esau’s together, not one by one sweep database, and then we often say a full table scan was slow in the presence of data amount, less amount of data, a full table scan is not slow, but with the amount of data, the more the higher the high water level, that is to say, the more you need to scan the database, IO, the more natural scan needed more time

SQL > alter table delete table delete table delete table delete table Faster queries? Well, it doesn’t, because even if we delete the data, the high water line doesn’t change, which means we have to scan the same number of blocks

5.3.2 ROWID Scanning (TABLE ACCESS BY ROWID)
  • TABLE ACCESS BY ROWID

ROWID is the physical storage address where the data row of the table is located. The so-called ROWID scan is to locate the data row record where the ROWID is located. ROWID is a pseudo column that does not exist in the database. It is a physical address obtained during database query and used to represent the number of rows corresponding to the data. Query with SQL:

Select t.*, rowid from tableCopy the code

Get a random ROWID sequence: AAAWSJAAFAAAWwUAAA, the first 6 digits represent the Data Object number, the next 3 digits are Relative file number, and the next 6 digits represent the Block number. The next three digits indicate the Row number.

ROWID coding method is: A ~ Z represents 0 to 25; A to z means 26 to 51; 0 to 9 means 52 to 61; + means 62; / represents 63; Exactly 64 characters.

Here is a random table to look up the file number, the area number, the line number, A series of rowiD physical addresses and file numbers (rowid_relatiVE_fNO (ROWID)), block numbers (rowid_block_number(ROWID)), and row numbers (rowid_ROW_number (ROWID)) are returned.

select t.seq,
       rowid,
       dbms_rowid.rowid_relative_fno(rowid),
       dbms_rowid.rowid_block_number(rowid),
       dbms_rowid.rowid_row_number(rowid)
  from t_info t
Copy the code

SQL > select * from TABLE where name = ‘TABLE’

select owner,object_id,data_object_id,status from dba_objects where object_name='TABLE';
Copy the code

Relative file IDS and absolute file encodings Relative file IDS are unique in the table space relative to the table space. Absolute file encoding is equivalent to global database, globally unique; SQL > query relative file ID and absolute file id

select file_name,file_id,relative_fno from dba_data_files;

Copy the code

TABLE ACCESS BY INDEX SCAN can be divided into:

  • INDEX UNIQUE SCAN
  • INDEX FULL SCAN
  • INDEX RANGE SCAN
  • INDEX FAST FULL SCAN
  • INDEX SKIP SCAN
5.3.3 INDEX UNIQUE SCAN
  • INDEX UNIQUE SCAN

INDEX UNIQUE SCAN is for UNIQUE INDEX, that is, only a UNIQUE INDEX can INDEX a UNIQUE SCAN, and only one record will be returned in the result set of the UNIQUE SCAN.

  • INDEX RANGE SCAN
5.3.4 INDEX RANGE SCAN
  • INDEX RANGE SCAN applies to all types of B-tree indexes, excluding unique indexes because unique indexes follow unique INDEX SCAN. Index range scanning is performed when the object being scanned is a non-unique index and where predicate conditions are Between, =, <, >, etc. If one of the key columns in the WHERE condition is not specified to be non-empty, then an index range scan is performed. If the changed key column is specified to be non-empty, then an index full scan is performed

As mentioned above, the same SQL SQL build different index, may be the index unique scan, may also be the index range scan. Under the same conditions, how about the comparison between the logical read required by the index range scan and the index uniqueness scan? An index range scan may return multiple records, so the optimizer must scan multiple records for confirmation, so all things being equal, an index range scan requires at least one more logical read than the corresponding unique scan

5.3.5 INDEX FULL SCAN
  • INDEX FULL SCAN

INDEX FULL SCAN applies to all types of B-tree indexes (including unique and non-unique indexes).

Description of index full scan process: Index scans all refers to scan the target index all leaf block index line, but does not mean the need to scan all the branch block, index scan all only need access to the necessary branch block, then positioning to the change in the index is the most on the left side of the leaf index of the first line of the line, you can use change index leaf block between the two-way pointer list, Scan the index rows of all leaf blocks from left to right

5.3.6 INDEX FAST FULL SCAN
  • INDEX FAST FULL SCAN

Index quick full scan is similar to index full scan and applies to all types of B-tree indexes (both unique and non-unique). Similar to index full scan, index rows of all leaf blocks are also scanned. These are the similarities between index fast full scan and index full scan

Index quick full scan and index full scan differences:

  • Index fast full scan only works with CBO(cost-based optimizer)
  • Quick full scan of an index can use multiple block reads or can be performed in parallel
  • An index full scan returns in order of leaf blocks, whereas an index quick full scan returns in order of storage blocks in the index segment
  • The results of quick full scan may not be ordered, because the results are scanned according to the physical storage order of index rows on disk, not the logical order of index rows
5.3.7 INDEX SKIP SCAN
  • INDEX SKIP SCAN

INDEX SKIP SCAN applies to all types of *** composite B-tree indexes ***(both unique and non-unique indexes), Index skip scan enables target SQL that does not have a target index specified by a leading column in a WHERE condition but has an index specified by a non-leading column to still use jump indexes

The execution plan shown in the figure includes INDEX RANGE SCAN, INDEX UNIQUE SCAN, and so on

5.4 Table Join method

Diagrams, execution plans have the following NESTED LOOPS, etc. What are these? This is exactly how Oracle joins tables

Table join methods between two tables include sort join, nested loop join, hash join, and Cartesian join

  • Merge sort join Merge sort join is to sort the associated columns of an associated table separately and then extract data from the respective sort tables to match data in another sort table

  • Nested loop joins work by looping data from one table (driving the outer table) and then accessing another table (the inner table being looked up, usually with an index). Each row in the drive table joins the corresponding record in the inner table. It’s like a nested loop. Nested loop joins are a better choice for cases where the subset of data being joined is small

  • Hash join is a common way for CBO to join large data sets. The optimizer uses the smaller of the two tables (or data sources) to create a Hash table in memory using join keys, then scans the larger table and probes the Hash table for rows that match the Hash table.

  • Cross join (Cross join) If two tables are joined without joining conditions, cartesian product will be generated. In practice, cartesian product should be avoided as much as possible

For a detailed description of these connections, check out Harvest, More than SQL Tuning, or check out my book notes

5.5 Explain parameter information

As shown in the preceding section, we already know the execution order of the execution plan, whether the SQL is indexed, full table scan, or ROWID scan, but there are also many parameters of the execution plan, such as Starts, e-rows, Cost (%CPU), etc.

  • Starts: indicates the number of times the SQL is executed
  • E-rows: The expected number of Rows for the execution plan
  • Cost (%CPU) : indicates the percentage of CPU Cost in the total Cost
  • A-rows: The actual number of Rows returned. E-rows is compared to A-rows to see which step of the execution plan is broken
  • A-time: indicates the actual execution Time of each step
  • Buffers: Logical or consistent reads that are actually performed at each step

Related scripts: SQL Download

Appreciation: Wechat rewards