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 histograms, index availability, and index clustering.

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 histograms, index availability, and index clustering.

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
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0-64bit Production

-- Create a table with 10000 million records
Scott @ SYBO2SZ> @ cr_big_tb

Check total rows for big_table
==========================================
COUNT (*)
----------
1000000

-- Create an index for a 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 dbms_workload_repository.drop_snapshot_range (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 $ _ sqlstat
Sys @ SYBO2SZ> truncate table wrh $ _ SQL _plan;

-- Clear the dba_hist_ SQL _sqltext and dba_hist_sqlstat views

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