Comparison and Analysis of Oracle historical SQL statement execution plans

In a CBO optimizer-based environment, the generation of SQL Execution plans depends on the authenticity and completeness 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

-- 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_tbcheck 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 records (30,31 ); sys @ sybo2sz> alter system flush shared_pool; sys @ sybo2sz> alter system flush buffer_cache; -- clear the dba_hist_ SQL _plan view and actually clear wrh $ _ SQL _plan, wrh $ _ sqltext, wrh $ _ sqlstatsys @ 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 (*) -------- 0sys @ sybo2sz> select count (*) from dba_hist_sqltext; count (*) ---------- 0

2. generate historical SQL statements and their execution plans

Sys @ sybo2sz> conn Scott/tigerscott @ sybo2sz> select count (*) from big_table where owner = 'goex _ admin'; count (*) ---------- 43560scott @ sybo2sz> @ my_last_sqladdress hash_value SQL _id command_type piece SQL _text ------------------ ---------- -------------- ---------- limit 243468085 limit 3 0 select count (*) from big_table where owner = 'goex _ admin' -- query the SQL Execution Plan from AWR. Because no snapshot is generated, no execution plan is available. Scott @ sybo2sz> @ SQL _plan_disp_awrenter value for input_sqlid: 4hqyjwh7861tpno 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_awrenter value for input_sqlid: ------------------------------------------------------------------------------------------------------------------------ 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 | 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 | average ------------------------------------------------------------------------------------

3. generate different historical SQL statements and compare execution plans

-- Move the table big_table Scott @ sybo2sz> alter table big_table move; -- check the index on the table. The index is invalid as follows: Scott @ sybo2sz> @ idx_infoenter value for owner: scottenter value for table_name: big_tabletable_name index_name cl_nam cl_pos status idx_typ dscd contains invalid values ------ -------- tables ---- big_table big_table_pk Id 1 unusable normal tables internal owner 1 unusable normal ASC -- execute the same SQL statement Scott @ sybo2sz> select count (*) from big_table where owner = 'goex _ admin'; count (*) ---------- 43560scott @ sybo2sz> @ my_last_sqladdress 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 sqlscott @ sybo2sz> exec dbms_workload_repository.create_snapshot (); -- View SQL Execution Plan Scott @ sybo2sz> @ SQL _plan_disp_awrenter value for input_sqlid: Explain explain SQL _id else ------------------ select count (*) from big_table where owner = 'goex _ admin' plan hash value: 334839806 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 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 | bytes | 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 execution plan of the SQL statement directly from dba_hist_ SQL _plan-this view records the execution plan of all historical SQL statements captured by the AWR snapshot and the generation time of the Execution Plan Scott @ sybo2sz> RUN SQL _plan_his 1 select ID, 2 operation, 3 options, 4 object_name, 5 bytes, 6 cpu_cost, -----> author: Robinson 7 io_cost, -----> blog: http://blog.csdn.net/robinson_0612 8 timestamp 9 from dba_hist_ SQL _plan 10 where SQL _id = '& input_ SQL _id' 11 * order by timestamp, identer value for input_ SQL _id: upload ID operation options object_name bytes cpu_cost io_cost timestamp --- hour ------------- hour ---------- hour 0 SELECT statement 20130517 11:23:20 1 sort aggregate 17 20130517 11:23:20 2 index range scan limit 171241 1789880 139 20130517 11:23:20 0 select statement 20130517 11:27:16 1 sort aggregate 17 20130517 11:27:16 2 Table access full big_table 171241 325825194 3203 20130517 11: 27: 166 rows selected.

4. Modify the SQL Execution Plan

-- As we can see before, because the index is unavailable, the SQL statement performs 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 ". & quot; big_table_pk & quot; or partition of such index is in unusable stateORA-06512: AT & quot; sys. dbms_stats ", line 13182ora-06512: At" sys. dbms_stats ", line 13202ora-06512: At Line 1 -- when the preceding statistics are collected, the system prompts that the index is unavailable. You Need To rebulidscott @ 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 ')

5. Postscript
A. For the big_table script created in the example, see: Common Oracle test table big_table
B. The ALTER TABLE move method is used to achieve CIDR and move the high watermark, but does not release the requested space and cause index failure.
C. For historical SQL statements, you must execute snapshot before filling them into the dba_hist_ SQL _plan, dba_hist_sqlstat, and dba_hist_snapshot data dictionaries.
D. If your test cannot obtain the historical SQL statement and its execution plan, it is usually caused by AWR threshold settings. For details, refer to: Oracle AWR threshold value affects the historical execution plan.
E. You can use $ ORACLE_HOME/rdbms/admin/awrsqrpt. SQL to generate txt or HTML files.
F. There are many situations that cause changes to the same SQL Execution Plan, such as missing statistics, index failure, and parameter changes at different levels.
H. For instances, sessions, and statement-level environment changes that cause the same SQL Execution Plan to change, you can also track this. Reference: Use the optimizer performance view to obtain the SQL statement execution environment

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)