Comparison and Analysis of Oracle historical SQL statement execution plans

Comparison and analysis of historical SQL statement execution plans in Oracle Based on the CBO optimizer environment, the generation of SQL Execution plans relies on the authenticity and integrity of statistical information. Such as column discretization, column histogram, index availability, clustering factor on the index. When the information is true and complete, the CBO optimizer can usually develop the optimal execution plan. Therefore, the CBO optimizer is flexible and difficult to control. The inaccuracy or absence of any information may lead to changes in the execution plan and multiple versions. It is often encountered that a previous SQL statement was not top SQL for a while, but recently it became top SQL. In other words, although it was previously top SQL, it has recently become TOP 1. In this case, we can compare the historical execution plans of SQL statements to analyze the causes of slow SQL statements or changes in the execution plans. The following example is used to simulate the SQL Execution Plan variation. 1. Create a demo Environment [SQL] -- demo environment scott @ SYBO2SZ> select * from v $ version where rownum <2; BANNER orders Oracle Database 10g Release 10.2.0.3.0-64bit Production -- create a 10000 million-record table scott @ SYBO2SZ> @ cr_big_tb check total rows for big_table ======== ======================================= COUNT (*) ---------- 1000000 -- create an index for the table scott @ SYBO2SZ> create index I _big_tb_owner on Big_table (owner); sys @ SYBO2SZ> conn/as sysdba; sys @ SYBO2SZ> select snap_id from dba_hist_snapshot order by snap_id; SNAP_ID -------- 30 31 -- clear awr history, shared pool and buffer cache sys @ SYBO2SZ> exec buffers (); sys @ SYBO2SZ> alter system flush shared_pool; sys @ SYBO2SZ> alter system flush buffer_cache; -- clear dba_hist_ SQL _plan view, actually clear wrh $ _ SQL _plan, wrh $ _ sqltext, w Rh $ _ sqlstat sys @ SYBO2SZ> truncate table wrh $ _ SQL _plan; -- clear dba_hist_ SQL _sqltext and dba_hist_sqlstat view sys @ SYBO2SZ> truncate table wrh $ _ sqltext; sys @ SYBO2SZ> truncate table wrh $ _ sqlstat; sys @ SYBO2SZ> select count (*) from dba_hist_ SQL _plan; COUNT (*) ---------- 0 sys @ SYBO2SZ> select count (*) from dba_hist_sqltext; COUNT (*) ---------- 0 2. generate historical SQL statements and their execution plans [SQL] sys @ SYBO2SZ> conn scott/tiger scott @ SYBO2SZ> s Elect count (*) from big_table where owner = 'goex _ admin'; COUNT (*) ---------- 43560 scott @ SYBO2SZ> @ my_last_ SQL ADDRESS HASH_VALUE SQL _ID COMMAND_TYPE PIECE SQL _TEXT ---------------- ---------- ------------------ ---------- limit 243468085 limit 3 0 select count (*) from big_table where owner = 'goex _ admin' -- Query sq from awr L execution plan, because no snapshot is generated, so no execution plan scott @ SYBO2SZ> @ SQL _plan_disp_awr Enter value for input_sqlid: 4hqyjwh7861tp no rows selected -- create a snapshot scott @ SYBO2SZ> exec dbms_workload_repository.create_snapshot (); PL/SQL procedure successfully completed. -- view the SQL history execution plan scott @ SYBO2SZ> @ SQL _plan_disp_awr Enter value for input_sqlid: 4hqyjwh7861tp PLAN_TABLE_OUTPUT Scheduler ----------------------------------------------------------- ------------------------------------------------------------- SQL _ID 4hqyjwh7861tp ------------------ select count (*) from big_table where owner = 'goex _ admin' Plan hash value: 334839806 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ------------------------------------------------------------------- ----------------- | 0 | select statement | 139 (100) | 1 | sort aggregate | 1 | 17 | 2 | index range scan | I _BIG_TB_OWNER | 10073 | 167K | 139 (0) | 00:00:02 | Exercise 3. generate different historical SQL statements and compare the execution plan [SQL] -- move the table big_table scott @ SYBO2SZ> alter table big_table move; -- check the indexes on the table, as follows, the index has expired scott @ SYBO2SZ> @ idx_info Ente R value for owner: scott Enter value for table_name: big_table TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD contains invalid values ------ -------- tables ---- BIG_TABLE BIG_TABLE_PK ID 1 unusable normal asc BIG_TABLE partition OWNER 1 unusable normal asc -- execute the same SQL statement scott @ SYBO2SZ> select count (*) from big_table where owner = 'Goex _ admin'; COUNT (*) ---------- 43560 scott @ SYBO2SZ> @ brief ADDRESS HASH_VALUE SQL _ID COMMAND_TYPE PIECE SQL _TEXT ---------------- ---------- ------------- ------------ limit 243468085 limit 3 0 select count (*) from big_table where owner = 'goex _ admin' -- create a new snapshot to make it a historical SQL scott @ SYBO2SZ> exec dbms_worklo Explain (); -- view the SQL Execution Plan scott @ SYBO2SZ> @ SQL _plan_disp_awr Enter value for input_sqlid: 4hqyjwh7861tp PLAN_TABLE_OUTPUT limit SQL _ID limit ------------------ select count (*) from big_table where owner = 'goex _ admin' Plan hash value: 334839806 ------------- Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement ||| 139 (100) | 1 | sort aggregate | 1 | 17 | 2 | index range scan | I _BIG_TB_OWNER | 10073 | 167K | 139 (0) | 00:00:02 | ------------------------- ----------------------------------------------------------- SQL _ID 4hqyjwh7861tp ------------------ select count (*) from big_table where owner = 'goex _ admin' Plan hash value: 599409829 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ------------------------------------------------------------------------- ------- | 0 | select statement | 3221 (100) | 1 | sort aggregate | 1 | 17 | 2 | table access full | BIG_TABLE | 10073 | 167K | 3221 (1) | 00:00:39 | Route 28 rows selected. -- from the preceding query results, we can see that the same historical SQL statement has different plan_hash_value and uses different execution plans. The earliest one is index range scanning, one is full table scan-view the SQL statement execution plan directly from dba_hist_ SQL _plan below-this view records all captured by awr snapshots Run SQL _plan_his 1 SELECT id, 2 operation, 3 options, 4 object_name, 5 bytes, 6 cpu_cost, -----> Author: Robinson 7 io_cost, 8 timestamp 9 FROM dba_hist_ SQL _plan 10 WHERE SQL _id = '& input_ SQL _id '11 * order by timestamp, id Enter value for input_ SQL _id: 4hqyjwh7861tp id operation options OBJECT_NAME BYTES CPU_COST IO_COST TIMESTAMP --------------------------- -------------- ----------------- ---------- ------------ --------------------- 0 select statement 20130517 11:23:20 1 sort aggregate 17 20130517 11:23:20 2 index range scan limit 171241 1789880 139 11:23:20 0 select statement 20130517 11:27:16 1 sort aggregate 17 11:27:16 2 table access full BIG_TABLE 171241 325825194 3203 20130517 11:27:16 6 rows selected. 4. Modify SQL statements Execution Plan [SQL]-as we can see before, because the index is unavailable, the SQL statement executes a full table scan. -- In fact, the full table scan has many problems. If you use the predicate column function, convert the Data Type of the predicate column, use not equal to, and use the predicate column for calculation, not listed one by one -- in the above case, we should collect statistics and rebuild the index scott @ SYBO2SZ> exec dbms_stats.gather_table_stats ('Scott ', 'Big _ table', cascade => true ); BEGIN dbms_stats.gather_table_stats ('Scott ', 'Big _ table', cascade => true); END; * ERROR at line 1: ORA-20000: index "SCOTT ". "BIG_TABLE_PK" or partition of such index is in unusable state ORA-06512: at "SYS. DBMS_STATS ", line 13182 ORA-06512: at" SYS. DBMS_STATS ", line 13202 ORA-06512: at line 1 -- when collecting statistics above, it prompts that the index is not available, You Need To rebulid scott @ SYBO2SZ> alter index I _big_tb_owner rebuild nologging; scott @ SYBO2SZ> alter index big_table_pk rebuild nologging; scott @ SYBO2SZ> exec dbms_stats.gather_table_stats ('Scott ', 'Big _ table', cascade => true ); -- Next we will execute the original SQL statement again. We can see that SQL has used the optimal execution plan scott @ SYBO2SZ> set autot trace exp; scott @ SYBO2SZ> select count (*) from big_table where owner = 'goex _ admin'; Execution Plan hash value: 334839806 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 6 | 108 (1) | 00:00:02 | 1 | sort aggregate | 1 | 6 | * 2 | index range scan | I _BIG_TB_OWNER | 44750 | 262K | 108 (1) | 00:00:02 | identified by operation id: --------------------------------------------------------------- 2-access ("OWNER" = 'goex _ admin ')