Reading Notes-Oracle-based SQL Optimization-Chapter 2-1

Chapter 2: Execution Plan in Oracle 2.1 What is a combination of the steps Oracle uses to execute the target SQL statement is called the execution plan. The execution PLAN can be divided into the following three parts: 1. The body of the target SQL statement, the SQL ID, and the PLAN HASH VALUE corresponding to the execution PLAN. 2. Main Part of the execution plan. You can see the internal execution steps used by Oracle to execute the target SQL statement, the execution sequence of these steps, the corresponding predicate information and column information, the optimizer evaluates the Cardinality and cost of the result set after performing these steps. * Characters before the execution plan line indicate that the execution steps have corresponding drivers or filter query conditions, the specific driver or filter query conditions corresponding to this asterisk can be found in "Predicate Information (identified y operation id)" of the execution plan. In fact, ELE. Me in this part is the predicate information corresponding to the above execution steps. Access indicates the driver query condition. 3. Additional supplementary information of the execution plan. Whether dynamic sampling (dynamic sampling) is used with Cardinality Feedback (a technical means of correcting the Cardinality of the returned result set in the execution plan introduced in Oracle 11g) whether to use SQL Profile (a method introduced in Oracle 10 Gb to adjust and stabilize the execution plan ).
2.2 how to view the Execution plan (1) and explain plan command press F5, and PL/SQL Developer calls the explain plan command. F5 is only an encapsulation of the explain plan command. Syntax: explain plan for + target SQLselect * from table (dbms_xplan.display) execute the explain plan command, then Oracle writes the specific execution steps of the execution plan generated by parsing the target SQL into PLAN_TABLE $, the subsequent select * from table (dbms_xplan.display) only displays the specific execution steps in formatting mode from PLAN_TABLE $. PLAN_TABLE $ is a global temporary table on commit preserve rows. Therefore, Oracle can only view the execution plan generated by the SQL statement executed by itself in each session, in addition, each session writes an execution plan to PLAN_TABLE $ without mutual interference. SQL> select dbms_metadata.get_ddl ('table', 'Plan _ TABLE $ ', 'sys') from dual; CREATE GLOBAL TEMPORARY TABLE "SYS ". "PLAN_TABLE $" ("STATEMENT_ID" VARCHAR2 (30), "PLAN_ID" NUMBER, "TIMESTAMP" DATE, "REMARKS" VARCHAR2 (4000), "OPERATION" VARCHAR2 (30 ), "OPTIONS" VARCHAR2 (255), "OBJECT_NODE" VARCHAR2 (128), "OBJECT_OWNER" VARCHAR2 (30), "OBJECT_NAME" VARCHAR2 (30), "OBJECT_ALIAS" VARCHAR2 (65 ), "OBJECT_INSTANCE" NUMBER (*, 0 ),
"OBJECT_TYPE" VARCHAR2 (30), "OPTIMIZER" VARCHAR2 (255), "SEARCH_COLUMNS" NUMBER, "ID" NUMBER (*, 0), "PARENT_ID" NUMBER (*, 0), "DEPTH" NUMBER (*, 0), "POSITION" NUMBER (*, 0), "COST" NUMBER (*, 0), "CARDINALITY" NUMBER (*, 0), "BYTES" NUMBER (*, 0), "OTHER_TAG" VARCHAR2 (255), "PARTITION_START" VARCHAR2 (255), "PARTITION_STOP" VARCHAR2 (255 ),
"PARTITION_ID" NUMBER (*, 0), "OTHER" LONG, "OTHER_XML" CLOB, "DISTRIBUTION" VARCHAR2 (30), "CPU_COST" NUMBER (*, 0 ), "IO_COST" NUMBER (*, 0), "TEMP_SPACE" NUMBER (*, 0), "ACCESS_PREDICATES" VARCHAR2 (4000), "FILTER_PREDICATES" VARCHAR2 (4000 ), "PROJECTION" VARCHAR2 (4000), "TIME" NUMBER (*, 0), "QBLOCK_NAME" VARCHAR2 (30) ON COMMIT PRESERVE ROWS
For Oracle 10 GB and later versions, the explain plan command writes the specific steps of the execution plan generated by parsing the target SQL statement to PLAN_TABLE $ after execution, and then runs the select * from table (dbms_xplan.display) command) only the detailed execution steps are displayed in the format from PLAN_TABLE $. SQL> select count (*) from sys. plan_table $; COUNT (*)
----------
0

SQL> select sid from v $ mystat where rownum <2;
SID
----------
1178

SQL> select count (*) from v $ mystat;
COUNT (*)
----------
604

SQL & gt; select saddr from v $ session where sid = 1178;
SADDR
----------------
20170001eec37778

SQL> select count (*) from v $ transaction where ses_addr = '00000001eec37778 ';
COUNT (*)
----------
0

SQL> select count (*) from v $ locked_object;
COUNT (*)
----------
0
SQL> explain plan for select empno, ename, dname from scott. emp, scott. dept where emp. deptno = dept. deptno;
Explained.
SQL> set long 90000
SQL> set heading off
SQL> set serveroutput on size 1000000
SQL> select operation, options, object_name, id, cardinality, cost from sys. plan_table $;
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
-------------------------------------------------------------
SELECT STATEMENT
0 14 6
MERGE JOIN
1 14 6
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
-------------------------------------------------------------

TABLE ACCESS
BY INDEX ROWID
DEPT 2 4 2

INDEX
FULL SCAN

OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
-------------------------------------------------------------
PK_DEPT 3 4 1
SORT
JOIN
4 14 4
TABLE ACCESS
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
-------------------------------------------------------------
FULL
EMP 5 14 3
6 rows selected.
SQL> select count (*) from v $ transaction where ses_addr = '00000001eec37778 ';
1

SQL> select count (*) from v $ locked_object;
1
Select object_id from v $ locked_object; 5003
SQL> select owner, object_name from dba_objects where object_id = 5003; SYSPLAN_TABLE $

Oracle 10g: SQL> select version from v $ instance;
10.2.0.4.0

SQL> desc t;
Name Null? Type
-----------------------------------------------------------------------------
X NUMBER
SQL> select operation, options, object_name, id, cardinality, cost from sys. plan_table $;
SELECT STATEMENT
0 10013476 483
PX COORDINATOR
1
PX SEND
QC (RANDOM)
: TQ10000 2 10013476 483
PX BLOCK
ITERATOR
3 10013476 483
TABLE ACCESS
FULL
T 4 10013476 483

SQL> select count (*) from v $ transaction where ses_addr = 'your region a5a07a70 ';
1

SQL> select count (*) from v $ locked_object;
0
SQL> select table_name, degree from user_tables; TABLE_NAME DEGREE
------------------------------ -------------------- T 8
Alter table t parallel (degree 1 );

SQL> select table_name, degree from user_tables; TABLE_NAME DEGREE
------------------------------ -------------------- T 1
SQL> select operation, options, object_name, id, cardinality, cost from sys. plan_table $;
SELECT STATEMENT
0 10013476 3483
TABLE ACCESS
FULL
T 1 10013476 3483
(2) DBMS_XPLAN package select * from table (dbms_xplan.display); select * from table (dbms_xplan.display_cursor (null, null, 'advanced '); advanced displays more detailed results than all. Select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '); select * from table (dbms_xplan.display_awr (' SQL _ id ')); (3) set autotrace on (set autot on): displays the execution results, execution plans, and resource consumption. Set autotrace off (set autot off): displays only the execution results. Set autotrace traceonly: displays the number of execution results, but not the specific content of the execution results. It is suitable for the SQL statement that is particularly long and flushed ON the screen, in this case, only the execution plan and resource consumption are concerned. Set autotrace traceonly explain (set autot trace exp): Unlike TRACEONLY, resource consumption and execution plan are not displayed, but only execution plan is displayed. Set autottrace traceonly statistics (set autot trace stat): only resource consumption is displayed. Unlike TRACEONLY, the execution plan is not displayed. Only the number of execution results and resource consumption are displayed. (4) The 10046 event and explain plan, dbms_xplan, and autotrace switches are different: the execution plan clearly shows the logical reads, physical reads, and elapsed time for each execution step in the actual execution plan of the target SQL statement. USER_DUMP_DEST generates the trace file. Activate the 10046 event: alter session set events '2017 trace name context forever, level 12' oradebug event 10046 trace name context forever, level 12. This method is recommended, you can run the oradebug tracefile_name command after activating the 10046 event to obtain the specific path and name of the trace file corresponding to the current session. The value is 12, indicating that the generated trace file contains the execution plan and resource consumption details of the target SQL statement, it also contains the value of the variable bound to the target SQL statement and the waiting events experienced by the session. Alter session set events '2014 trace name context off' oradebug event 10046 trace name context off10046 the original trace file generated is often called a raw trace file, which is not intuitive, oracle provides the tkprof command to translate the raw file trace. Oradebug setmypid indicates that you are going to use the oradebug command for the current session. (5) 10053 event (6), AWR report, or Statspack report. (7) Some Existing scripts (display_cursor_9i. SQL ).
2.3 How to get a real execution plan except for the 10046 event: the AUTOTRACE switch in the DBMS_XPLAN package of the explain plan command SQLPLUS may not be accurate. Whether the execution plan is accurate in Oracle is to check whether the target SQL is actually executed. The execution plan corresponding to the actually executed SQL is accurate. Otherwise, it may be inaccurate. Note: The judgment principle here is strictly not applicable to the AUTOTRACE switch, because all the execution plans displayed using the AUTOTRACE switch may be inaccurate, even if the target SQL statement has actually been executed. (1) The explain plan command may be inaccurate because the SQL statement is not actually executed at this time, especially when the SQL statement contains the bound variable. By default, when binding variables are enabled, the execution plan obtained by using the explain plan for the target SQL statement containing the bound variables is only a half-finished product, oracle then snoop on the binding variables of the SQL statement and then obtains the specific values of these binding variables. At this time, Oracle may adjust the execution plan of the preceding semi-finished products, the execution plan obtained by using the explain plan command is not allowed. (2) DBMS_XPLAN package select * from table (dbms_xplan.display); the execution plan may not be accurate because it is only applicable to viewing the execution plan of the target SQL statement obtained using the explain plan command, the target SQL statement has not been executed yet. (3) AUTOTRACE switches set autotrace on and set autotrace traceonly. The target SQL statements are actually executed. Therefore, set autotrace on and set autotrace traceonly can view the actual resource consumption of SQL statements. When set autotrace traceonly explain is used, if the SELECT statement is executed, it is not actually executed. If the DML statement is executed, it is actually executed by Oracle. Be careful when using set autotrace on, set autotrace traceonly, and set autotrace traceonly explain to get the execution plan of DML statements because these DML statements have actually been executed. However, even after the execution, all the execution plans obtained by using the set autotrace command may be inaccurate, the execution plan displayed by using the set autotrace command is derived from calling the explain plan command.
The execution plan is still in the Shared Pool: Script: display_cursor_9i. SQL stored procedure: printsql obtains the actual execution plan and resource consumption. If the execution plan has been out of the shared pool by age, you can run DBMS_XPLAN.DISPLAY_AWR or awr SQL Report (awrsqrpt. SQL) and Statspack SQL Report to obtain its historical execution plan and resource consumption. (Sprepsql)
Display_cursor_9i. SQL is applicable to the SQL hash value and child cursor number of the target SQL statement to be queried when the script is executed and later. 9i does not have the DISPLAY_CURSOR method in the DBMS_XPLAN package. You cannot use select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '));, however, you can execute this script to obtain the actual execution plan.
If the execution plan has been out of the shared pool by Oracle age, whether the execution plan can be obtained depends on:
1. If the SQL Execution Plan of 10 Gb or above is captured by Oracle and stored in AWR Repository, awr SQL can be used for actual execution. 2. 9i, unless the Statspack report is deployed additionally and the level value of the collected Statspack report is greater than or equal to 6.
Like DBMS_XPLAN.DISPLAY_AWR, the execution plan displayed in the awr SQL Report also displays the predicate conditions corresponding to the non-execution step, because Oracle moved the sample data of the execution plan from V $ SQL _PLAN to WRH $ _ SQL _PLAN, the values of ACCESS_PREDICATES and FILTER_PREDICATES in the base table WRH $ _ SQL _PLAN of AWR Repository were not retained.
SQL> desc WRH $ _ SQL _PLAN
Name Null? Type
-----------------------------------------------------------------------------
SNAP_ID NUMBER
DBID NOT NULL NUMBER
SQL _ID NOT NULL VARCHAR2 (13)
PLAN_HASH_VALUE NOT NULL NUMBER
ID NOT NULL NUMBER
OPERATION VARCHAR2 (30)
OPTIONS VARCHAR2 (30)
OBJECT_NODE VARCHAR2 (128)
OBJECT # NUMBER
OBJECT_OWNER VARCHAR2 (30)
OBJECT_NAME VARCHAR2 (31)
OBJECT_ALIAS VARCHAR2 (65)
OBJECT_TYPE VARCHAR2 (20)
OPTIMIZER VARCHAR2 (20)
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2 (35)
PARTITION_START VARCHAR2 (5)
PARTITION_STOP VARCHAR2 (5)
PARTITION_ID NUMBER
OTHER VARCHAR2 (4000)
DISTRIBUTION VARCHAR2 (20)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2 (4000)
FILTER_PREDICATES VARCHAR2 (4000)
PROJECTION VARCHAR2 (4000)
TIME NUMBER
QBLOCK_NAME VARCHAR2 (31)
REMARKS VARCHAR2 (4000)
TIMESTAMP DATE
OTHER_XML CLOB
2.4 how to view the execution sequence of an execution plan first, from the beginning to the right, until you can see the rightmost parallel position; for non-parallel, right-direction first execution; if you see the parallel parts, you can see from the top down, and execute the parallel parts first. Select * from table (dbms_xplan.display_cursor );
The XPLAN package encapsulates the DBMS_XPLAN package. You can use the XPLAN package to clearly view the execution sequence of each step in the execution plan. The execution sequence is displayed in the Order column in the display result of the XPLAN package. The value of Order increases from 1, indicating the Order of execution.
SQL> select/* + xplan_example1 */a from t;
A
----------
1

SQL> select SQL _text, SQL _id, child_number from v $ SQL where SQL _text like 'select/* + xplan_example1 */% ';
SQL _TEXT
--------------------------------------------------------------------------------
SQL _ID CHILD_NUMBER
-------------------------
Select/* + xplan_example1 */a from t
1smx7psgknjbd 0

2.5 common execution plan 2.5.1 in Oracle related to table access 1. Full TABLE scan: table access FULL2, ROWID scan: table access by user rowid or table access by index rowid. It depends on the ROWID source when accessing the table. ROWID is manually specified by the user or comes from the index.
2.5.2 execution plans related to B-tree indexes include INDEX UNIQUE SCAN, INDEX RANGE SCAN, and INDEX FULL SCAN) index fast full scan and index skip scan ).
Unique index scan: create unique index xxx ON xxx (xxx); index range scan: create index... select * from xxx where xxx = xxx; select * (plan_table_output) from table (dbms_xplan.display_cursor (null, null, 'all '));
Begin for I in 1 .. 5000 loop insert into xxx value ('A', I); end loop; commit; end;/exec dbms_stats.gather_table_stats (ownname => 'ipa', tabname => 'xxx ', estimate_percent => 100, cascade => TRUE, no_invalidate => false, method_opt => 'for all columns size 1'); even if select XXX (INDEX) from xxx ;, if HINT is used, full table scans are also used without indexes. Because Oracle always ensures the correctness of the target SQL result in any case, the execution path of the wrong result may not be considered in Oracle. For a single-key B-tree index, the NULL value is not stored in it. Once the index column has a NULL value, the scan index will miss the record where these fields are NULL. -Inaccurate execution plans. Even if HINT is used. In this case, you can only change the column to not null. The index fast full scan is used. If the HINT uses/* + index (INDEX) */, the index full scan is used.
2.5.3 execution plan of Bitmap index blocks related to Bitmap indexes: mainly because Bitmap indexes implement fast bitwise operations. The physical storage structure of the bitmap index is as follows: <the key value to be indexed, corresponding to the lower limit of rowid, corresponding to the upper limit of rowid, bitmap segment>. The bitmap segment is compressed and stored. After decompression, it is a series of binary bitmap sequences of 0 and 1. 1 indicates a valid rowid of the indexed key value, oracle uses a mapping function to combine 1 in the extracted bitmap with the upper and lower limits of the corresponding rowid to convert it to a valid rowid corresponding to the indexed key value. The physical storage structure of the bitmap index determines that the granularity of the bitmap index lock in the Oracle database is on the bitmap segment of the index row. For Bitmap indexes in Oracle databases, the concept of row lock does not exist. to lock the entire bitmap segment of the index row, multiple data rows may correspond to the bitmap of the same index row. Advantages of Bitmap indexes: 1. If the distinct value of the indexed column is small, the bitmap index significantly saves storage space compared with the B-tree index of the same column. 2. If you need to create an index on multiple columns, the bitmap index often significantly saves storage space compared with the B-tree index under the same conditions. 3. Bitmap indexes can quickly process some SQL statements that contain various AND OR query conditions. This is mainly because Bitmap indexes can implement quick bitwise operations.