Oracle-execution plan


Several ways to generate execution plans

1. EXPLAIN FOR

grammar:

EXPLAIN PLAN FOR
SQL statement

SELECT * FROM TABLE(dbms_xplan.display());

advantage:

  1. No need to actually execute SQL

shortcoming:

  1. No relevant statistical information is output, such as how many logical reads, physical reads, recursive calls, etc. are generated.
  2. Unable to tell how many rows were processed
  3. Unable to determine how many times the table has been executed

example:

EXPLAIN PLAN FOR
SELECT *
  FROM kceb_pzkcun
 WHERE yngyjigo = '9501'
   AND weixdhao = '2001'
   AND pingzhzl = '101'
   AND fenhdaim = '99'
   AND pngzphao = 'LN22'
   AND qishipzh <= 4070660
   AND 4070661 <= zzzpzhhao
   AND pzdanwei = '1'
   AND jiluztai = '0';

SELECT * FROM TABLE(dbms_xplan.display());

2. SET AUTOTRACE ON|TRACEONLY[EXPLAIN]

Note: This function can only be used in SQLPLUS mode
grammar:

SET AUTOTRACE ON|TRACEONLY;
SQL statement;
SET AUTOTRACE OFF;

advantage:

  1. Can output relevant statistical information during runtime
  2. Although you have to wait for the statement to be executed, you can control the return result not to be output through the TRACEONLY option.

shortcoming:

  1. Must wait for statement execution to complete
  2. Unable to see how many times the table has been accessed

example:

SET AUTOTRACE ON;
SELECT * FROM untifa_test t WHERE t.child_id = '1' AND t.relation_type = 'A' AND t.parent_id = '1';
SET AUTOTRACE TRACEONLY;
SELECT * FROM untifa_test t WHERE t.child_id = '1' AND t.relation_type = 'A' AND t.parent_id = '1';
SET AUTOTRACE OFF;


3. STATISTICS_LEVEL=ALL

The above two methods, use AUTOTRACE or EXPLAIN PLAN FOR to obtain the execution plan from PLAN_TABLE. PLAN_TABLE is a session-level temporary table. The execution plan inside is not the actual SQL execution plan, it is only estimated by the optimizer. The real execution plan should not be estimated, but should be actually executed. The execution plan executed by SQL exists in the shared pool, specifically in the data dictionary V$SQL_PLAN. The execution plan with A-Time comes from V$SQL_PLAN, which is the real execution plan, and through AUTOTRACE, through EXPLAIN PLAN FOR The execution plan obtained is only the execution plan estimated by the optimizer.

The following method can obtain the true execution plan

grammar:

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SQL statement;

SELECT v.last_active_time, v.*
  FROM v$sql v
 WHERE v.last_active_time >
       to_date('date', 'date format')
   AND v.parsing_schema_name = 'SCHEMA'
   AND v.sql_text LIKE '%SQL statement%'
 ORDER BY v.last_active_time DESC;
 
SELECT *
  FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cwn6p7zs926rv',
                                       cursor_child_no => NULL,
                                       format => 'allstats last'));

advantage:

  1. Get runtime related information

shortcoming:

  1. You must wait for the statement to complete before you can get the result.
  2. No control over result printout

Starts indicates the number of times this operation is performed
E-Rows represents the number of rows estimated by the optimizer, which is the Rows in the ordinary execution plan.
A-Rows represents the real number of rows
A-Time represents the total accumulated time. The difference from the ordinary execution plan is that the Time in the ordinary execution plan is false, while the A-Time is real.
Buffers represent accumulated logical reads
Reads represents the accumulated physical reads

It should be noted that the number of rows estimated by ordinary execution plans is affected by histogram statistics, which may cause the optimizer to misjudge the selection of execution plans (for example, HASH JOIN should be used, but the result becomes NESTED LOOPS). Therefore, histogram statistics should be updated regularly.

Obtaining the true execution plan requires corresponding permissions

GRANT SELECT ANY DICTIONARY TO HXAPP;

example:

SHOW PARAMETER STATISTICS_LEVEL;

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SELECT *
  FROM kceb_pzkcun
 WHERE yngyjigo = '9501'
   AND weixdhao = '2001'
   AND pingzhzl = '101'
   AND fenhdaim = '99'
   AND pngzphao = 'LN22'
   AND qishipzh <= 4070660
   AND 4070661 <= zzzpzhhao
   AND pzdanwei = '1'
   AND jiluztai = '0';

SELECT v.last_active_time, v.*
  FROM v$sql v
 WHERE v.last_active_time >
       to_date('2023/11/02 14:00:00', 'yyyy/mm/dd hh24:mi:ss')
   AND v.parsing_schema_name = 'HXAPP'
   AND v.sql_text LIKE '%kceb_pzkcun%'
 ORDER BY v.last_active_time DESC;

SELECT *
  FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cwn6p7zs926rv',
                                       cursor_child_no => NULL,
                                       format => 'allstats last'));

4. dbms_xplan.display_cursor

grammar:

SELECT *
  FROM TABLE(dbms_xplan.display_cursor(sql_id => '',
                                       cursor_child_no => n,
                                       format => 'allstats last'));

select * from table( dbms_xplan.display_awr( & amp;sql_id’) ); –This method is obtained from the awr performance view
If there are multiple execution plans, you can use the following method to find out:
select * from table(dbms_xplan.display_cursor( & amp;sql_id’,0));
select * from table(dbms_xplan.display_cursor( & s ql_id’,1));

5. Event 10046 trace (not verified)

Step 1: alter session set events 10046 trace name context forever,level 12’; –enable tracing
Step 2: Execute sql statement;
Step 3: alter session set events 10046 trace name context off’; –turn off tracing
Step 4: select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum<=1)); --Find the file generated after tracing
Step 5: tkprof trc file generates target file sys=no sort=prsela,exeela,fchela –formatting command

6.awrsqrpt.sql

Generate awr report to view
Specific reference:
Oracle-AWR report generation method


A brief analysis of several ways to access data in the database

Common execution plans and simple explanations

1. Access table execution plan

1. table access full: full table scan. It accesses every record in the table (reads every block of data within the high water mark).

2. Table access by user rowid: The input source rowid is specified by the user.

3. table access by index rowid: The input source rowid comes from the index.

4. Table access by global index rowid: The global index obtains the rowid and then returns it to the table.

5. Table access by local index rowid: The partition index obtains the rowid and then returns to the table.

6. Table access cluster: Access the index table through the key of the index cluster.

7. External table access: access external tables.

8. Result cache: The result set may come from the cache.

9. mat_view rewrite access: materialized view.

2. Execution plan related to B-TREE index

1. Index unique scan: An index scan that returns only one rowid, or an equivalent scan of a unique index.

2. Index range scan: Returns an index scan of multiple rowids.

3. Index full scan: Scan the entire index sequentially.

4. Index fast full scan: Scan the entire index in multi-block reading mode.

5. Index skip scan: It is mostly used in combined indexes to guide the index scan when the key value is empty.

6. and-equal: Merge result sets from one or more indexes.

7. Domain index: application domain index.

3. Execution plan related to BIT-MAP index

1. Bitmap conversion: Convert bits to rowid or vice versa.

2. Bitmap index: Take a value or a range from the bitmap.

3. bitmap merge

4. bitmap minus:

5. bitmap or：

4. Execution plan related to table connection

1. Merge join: Sort merge join.

2. Nested loops: Nested loop connections.

3. Hash join: Hash join.

4. cartesian: Cartesian product connection.

5. connect by: hierarchical query index, mostly from start with clause.

6. outer: external link.

1) merge join outer:

2) nested loops outer:

3) hash join outer:

7. anti: anti-connection.

1) merge join anti:

2) nested loops anti:

3) hash join anti:

8. semi: semi-connected.

1) merge join semi:

2) nested loops semi:

3) hash join semi:

5. Execution plans related to collections

1. union-all:

2. union (union-all, sort unique):

3. concatenation:

4.intersection:

5. minus:

6. Indexes related to partitions

1. partition single: access a single partition.

2. partition iterator: access multiple partitions.

3. partition all: access all partitions.

4. Partition inlist: Access partitions based on the values in the in list.

7. Execution plan related to sort

1. sort unique: sorting and deduplication.

2. Sort join: It is the first step of merge join, a sorting operation, and is generally used in conjunction with merge join.

3. Sort aggregate: When using the grouping function on grouped data.

4. sort order by: simple sorting

5. sort group by: sort and group

6. Buffer sort: Perform a memory sort on temporary results.

8. Other execution plans

1. view:

2. count:

3. Stopkey: Rownum<10 exists in the target sql.

4. hash group by:

5. inlist iterator:

6. Filter: Filtering, equivalent to processed sorting and merging connections.

7. remote: execution plan related to dblink.

8. for update:

9. Sequence: Oracle sequence is used.

10. Collection iterator: Use table functions to extract records.

11. Fast dual: access the dual table.

12. first row: Get the first record of the query.

13. Load as select: Use select to perform direct path insert operation, usually adding / + append/ prompt.

14. Fixed table: Access fixed (X/V) table.

15. Fixed index: access a fixed index.

16. Window buffer: Supports the internal operations of analysis functions.

Understand several basic concepts

  • 1. The concept of ROWID, a virtual column of Oracle, is used to return to the table after hitting the index (read data from a certain location in the file block based on ROWID). ROWID is unique within the life cycle of the row, that is, even if the row When row migration occurs, the ROWID of the row will not change (changes to the table space, table reconstruction or renaming, impdp or expdp, database backup and recovery, partition changes, table compression, etc. will cause ROWID to change)
  • 2. Recursive SQL concept: The user’s ddl and dml operations will bring some hidden operations. The obvious one is that the data dictionary will be modified, and the data dictionary information is stored in the memory.
  • 3. Row Source: In layman’s terms, it is the result set remaining after filtering a table when querying or connecting.
  • 4. Driving Table: This table is also called the outer table (OUTER TABLE). This concept is used in nested and HASH joins.
  • 5. Probed Table (probed table) is the opposite of 4
  • 6. Concatenated index: An index composed of multiple columns, such as create index idx_emp on emp(col1, col2, col3, …), then we call the idx_emp index a composite index. There is an important concept in the combined index: leading column. In the above example, the col1 column is the leading column. When we make a query, we can use “where col1 = ?” or “where col1 = ? and col2 = ?”. Such restrictions will use the index, but the “where col2 = ?” query will not use the index. . Therefore, only when the leading column is included in the restriction, the combined index will be used for the restriction.
  • 7. Selectivity: In fact, it is the distinct column/count(1)
  • 8. The logical structure of the ORACLE database includes: database block, extent, segment, and tablespace. The high water mark exists in a segment and is used to identify the boundary between used data blocks and unused data blocks in the segment. When scanning table data, all data blocks below the high water mark must be scanned.
    The high water mark exists on a segment, and its position is recorded in the segment header, which is the first data block of the segment. Therefore, you can dump the segment header information to see the high-water mark information. Segments are divided into data segments, index segments, temporary segments, rollback segments, etc. When a segment is created, an area will be allocated. The area is composed of several physically continuous data blocks. The allocation of the area requires initialization of the data block, which is initialized by default. The unit is 1M. Note: The high water mark is not the initialized boundary slice, and the initialized data block does not necessarily have to be used.

Understand how to read data from Oracle database

Direct access to data

  • 1. Full table scan
    Start scanning from the first block (BLOCK) of the first area (EXTENT) and read all formatted blocks below the high water mark (High Water Mark).
    It’s OK to overthink
    Perform multi-block reads if allowed DB_FILE_MULTIBLOCK_READ_COUNT
    When the amount of data is large, it is faster than index range scanning

Note: The larger the amount of data, the more time it takes to scan the full table. What if you directly delete the table data? Will the query speed become faster? Actually it won’t, because even if we delete the data, the high waterline will not change, that is, we still need to scan so many data blocks.

  • 2. Through ROWID
    rowid requires 10 bytes to store and consists of 18 characters divided into 4 parts. Pseudo columns are like table columns, but do not actually exist in the table. ROWID is the physical storage address where the table data row is located. The so-called ROWID scan is located through the data row record where the ROWID is located.
    The first 6 digits represent the Data Object number, the next 3 digits represent the Relative file number, the next 6 digits represent the Block number, and the next 3 digits represent the Row number.
    The ROWID encoding method is: A ~ Z represents 0 to 25; a ~ z represents 26 to 51; 0 ~ 9 represents 52 to 61; + represents 62; / represents 63; exactly 64 characters.
  • 3. Random access data block SAMPLE table scan 
    SELECT * FROM untifa_test SAMPLE BLOCK (41);

Index access data

![Insert image description here](https://img-blog.csdnimg.cn/f68d1bcce7f44d709dd12866f96f8527.png)

  • 1. Index unique scan (INDEX UNIQUE SCAN)
    Index unique scan (INDEX UNIQUE SCAN) is for unique index (UNIQUE INDEX), that is, a unique index must be established to index unique scan. Unique scan will only return one record in the result set.
  • 2. Index range scan (INDEX RANGE SCAN)
    Index range scan (INDEX RANGE SCAN) is applicable to all types of B-tree indexes, generally excluding unique indexes, because unique indexes use index unique scans. When the object being scanned is a non-unique index, and the WHERE conditions are BETWEEN, =, <, >, etc., it is an index range scan. Note that it can be an equal value query or a range query. If there is an index key value column in the WHERE condition that is not limited to non-empty, then an index range scan can be performed. If the index column is non-empty, then an index full scan can be performed.
    As mentioned earlier, if the same SQL builds different indexes, it may be an index unique scan or an index range scan. Under the same conditions, compared with the logical read required by the index range scan and the index unique scan, the index range scan may return multiple records, so the optimizer will definitely scan more in order to confirm, so under the same conditions, the index range scan requires The required logical reads will be at least 1 more than the corresponding unique scan logical reads.
  • 3. Index full scan (INDEX FULL SCAN)
    Applicable to all types of B-tree indexes (including unique indexes and non-unique indexes).
    Brief description of the index full scan process: An index full scan refers to scanning the index rows of all leaf blocks of the target index, but it does not mean that all branch blocks need to be scanned. During an index full scan, you only need to access the necessary branch blocks, and then locate the location where the index is changed. To index the first index row of the leftmost leaf block, you can use the bidirectional pointer linked list between the index leaf blocks to scan the index rows of all leaf blocks sequentially from left to right.
  • 4. Index fast full scan (INDEX FAST FULL SCAN)
    Index fast full scan is very similar to index full scan and is also applicable to all types of B-tree indexes (including unique indexes and non-unique indexes). Similar to index full scan, it also scans the index rows of all leaf blocks. These are the same points between index fast full scan and index full scan.
    The difference between index fast full scan and index full scan:
    Index fast full scan is only applicable to CBO (cost-based optimizer)
    Index fast full scans can use multi-block reads and can also be executed in parallel
    An index full scan will return in the order of leaf blocks, while an index fast full scan will return in the order of storage blocks in the index segment.
    The execution results of index fast full scan are not necessarily in order, but the execution results of index full scan are in order, because index fast full scan scans according to the physical storage order of index rows on the disk, not according to the order of index rows. Scanned in logical order
  • 5. Index skip scan (INDEX SKIP SCAN)
    Index skip scan (INDEX SKIP SCAN) is applicable to all types of compound B-tree indexes (including unique indexes and non-unique indexes). Index skip scan can specify query conditions for leading columns that do not have a target index in the where condition. However, target SQL with indexed non-leading columns specifying query conditions can still use skip indexes. The definition and explanation are a bit convoluted. For example, a new composite index is created:
create index UNTIFA_TEST_IDX1 on UNTIFA_TEST (PARENT_ID, RELATION_TYPE)
  tablespace TEST_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
EXPLAIN PLAN FOR
SELECT * FROM untifa_test t WHERE t.relation_type = 'A';
SELECT * FROM TABLE(dbms_xplan.display());