Oracle uses 10053 events to analyze how Oracle makes the final execution plan

We all know what kind of execution method is selected for Oracle SQL statements starting from 10 Gb, whether it is full table scan, or whether the index is based on the execution cost. How can we see the execution cost?

We all know what kind of execution method is selected for Oracle SQL statements starting from 10 Gb, whether it is full table scan, or whether the index is based on the execution cost. How can we see the execution cost?

We all know what kind of execution method is selected for Oracle SQL statements starting from 10 Gb, whether full table scanning or indexing is based on the execution cost. How can we see the execution cost information? With the 10053 event, Oracle can determine the execution cost and how to make the execution plan. if we find that the execution plan of an SQL statement is different from what we imagined, we can check whether the statistical analysis data used by Oracle is accurate, whether the statistical information has not been analyzed for too long, re-analyze the problematic objects and finally let Oracle make the correct execution plan.

Let's take an example of a 10053 event:

SQL> create table t1 as select rownum rn from dba_objects;
Table created.
SQL> create index ind_t1 on t1 (rn );
Index created.
SQL> exec dbms_stats.gather_table_stats (user, 't1', cascade => true );
PL/SQL procedure successfully completed.
SQL> create table t2 as select rn, 't2' name from t1 where rn <10000;
Table created.
SQL> create index ind_t2 on t2 (rn );
Index created.
SQL> exec dbms_stats.gather_table_stats (user, 't2', cascade => true );
PL/SQL procedure successfully completed.
SQL> alter session set tracefile_identifier = 'mysession ';
Session altered.
SQL> alter session set events '10053 trace name context forever, level 1 ';
Session altered.
SQL> explain plan for select t2. * from t1, t2 where t1.rn <100 and t1.rn = t2.rn;
Explained.
SQL> alter session set events '10053 trace name context off ';
Session altered.
The path of the generated trace file is the $ ORACLE_BASE/admin/SID/udump directory.
Unlike SQL _TRACE and 10046, the generated trace file cannot be processed by tkprof, but can only read the original trace file.
Give a general introduction to the trace file:
**************************
Predicate Move-Around (PM)
**************************
SQL: ******* UNPARSED QUERY IS *******
SELECT "T2 ". "RN" "RN", "T2 ". "NAME" "NAME" FROM "YORKER ". "T1" "T1", "YORKER ". "T2" "T2" WHERE "T1 ". "RN" <100 AND "T1 ". "RN" = "T2 ". "RN"
FPD: Current where clause predicates in SEL $1 (#0 ):
"T1". "RN" <100 AND "T1". "RN" = "T2". "RN"
Kkogcp: try to generate transitive predicate from check constraints for SEL $1 (#0)
Predicates with check contraints: "T1". "RN" <100 AND "T1". "RN" = "T2". "RN" AND "T2". "RN" <100
After transitive predicate generation: "T1". "RN" <100 AND "T1". "RN" = "T2". "RN" AND "T2". "RN" <100
Finally: "T1". "RN" <100 AND "T1". "RN" = "T2". "RN" AND "T2". "RN" <100
FPD: Following transitive predicates are generated in SEL $1 (#0 ):
& Quot; T2 & quot;. & quot; RN & quot; <100
Apadrv-start: call (in-use = 340, alloc = 0), compile (in-use = 31192, alloc = 0)
Kkoqbc-start
: Call (in-use = 344, alloc = 0), compile (in-use = 31976, alloc = 0)
**************************************** **
We will find that Oracle will convert the SQL statement to the statement that best fits the Oracle processing.
For example, the query condition is changed to finally: "T1 ". "RN" <100 AND "T1 ". "RN" = "T2 ". "RN" AND "T2 ". & quot; RN & quot; <100
The following describes the abbreviations used in some trace files and the use of variable binding:
The following abbreviations are used by optimizer trace.
CBQT-cost-based query transformation
JPPD-join predicate push-down
FPD-filter push-down
PM-predicate move-around
CVM-complex view merging
SPJ-select-project-join
**************************************** ***
Peeked values of the binds in SQL statement
**************************************** ***
Next, we will analyze the index statistics of the tables used by SQL. If we find that this is not in line with the actual conditions, we may need to analyze the objects.
This information includes the number of rows, number of data blocks, and average length of rows.
Average column length, non-repeated values, number of null values, density, minimum and maximum values.
The index height, the number of leaf blocks, the number of blocks occupied by each index key value (LB/K), and the number of data blocks (DB/K) for each index key value ).
Index aggregation factor (CLUF ).
An aggregation factor refers to the relationship between the distribution of the index's key values and the data block distribution in the corresponding table.
When the index key value is roughly the same as the data in the table, the smaller the value is. When the data of an index key value is distributed in more tables
The larger the value, the higher the cost of using the index.
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats ::
Table: T2 Alias: T2
# Rows: 9999 # Blks: 21 AvgRowLen: 6.00
Column (#1): RN (NUMBER)
AvgLen: 4.00 Newcastle: 9999 Nulls: 0 Density: 1.0001e-004 Min: 1 Max: 9999
Index Stats ::
Index: IND_T2 Col #: 1
LVLS: 1 # LB: 21 # DK: 9999 LB/K: 1.00 DB/K: 1.00 CLUF: 17.00
***********************
Table Stats ::
Table: T1 Alias: T1
# Rows: 51060 # Blks: 86 AvgRowLen: 4.00
Column (#1): RN (NUMBER)
AvgLen: 5.00 Newcastle: 51060 Nulls: 0 Density: 1.9585e-005 Min: 5 Max: 51055
Index Stats ::
Index: IND_T1 Col #: 1
LVLS: 1 # LB: 113 # DK: 51060 LB/K: 1.00 DB/K: 1.00 CLUF: 78.00
***************************************
Next, compare the cost of all data access methods and select the method with the minimum cost.
As an execution plan.
For Table T1
Access Path: TableScan full table scan Cost: 22.86
Access Path: index (FFS) index quick scan Cost: 27.63
Access Path: index (IndexOnly) Only accesses the index. (because only the index data RN is used) Cost: 2.01
We can see that the minimum cost of accessing the index is.
Table T2 has the following access method: (index (RangeScan) is used at the end ))
Access Path: TableScan Cost: 6.37
Access Path: index (RangeScan) Cost: 3.01
Then consider the order of association:
T1 associated with T2
NL Join (nested loops join) Cost: 2051.15
SM Join (Sort merge join) SM cost: 6.02
HA Join (Hash join) hacost: 5.52
T2 associated T1
NL Join (nested loops join) Cost: 475.12
SM Join (Sort merge join) SM cost: 6.02
HA Join (Hash join) hacost: 5.52
T1 is associated with T2 at a lower CPU cost, and T1 is associated with T2.
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 51060 Rounded: 95 Computed: 95.02 Non Adjusted: 95.02
Access Path: TableScan
Cost: 22.86 Resp: 22.86 Degree: 0
Cost_io: 21.00 Cost_cpu: 10824444
Resp_io: 21.00 resp_cpus: 10824444
Access Path: index (FFS ))
Index: IND_T1
Resc_io: 26.00 resc_cpu: 9484923
Ix_sel: 0.20.e + 000 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 27.63 Resp: 27.63 Degree: 1
Cost_io: 26.00 Cost_cpu: 9484923
Resp_io: 26.00 resp_cpus: 9484923
Access Path: index (IndexOnly)
Index: IND_T1
Resc_io: 2.00 resc_cpu: 33443
Ix_sel: 0.0018609 ix_sel_with_filters: 0.0018609
Cost: 2.01 Resp: 2.01 Degree: 1
Best: AccessPath: IndexRange Index: IND_T1
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 95.02 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: T2 Alias: T2
Card: Original: 9999 Rounded: 99 Computed: 99.01 Non Adjusted: 99.01
Access Path: TableScan
Cost: 6.37 Resp: 6.37 Degree: 0
Cost_io: 6.00 Cost_cpu: 2151330
Resp_io: 6.00 resp_cpus: 2151330
Access Path: index (RangeScan)
Index: IND_T2
Resc_io: 3.00 resc_cpu: 58364
Ix_sel: 0.009902 ix_sel_with_filters: 0.009902
Cost: 3.01 Resp: 3.01 Degree: 1
Best: AccessPath: IndexRange Index: IND_T2
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 99.01 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order [1]: T1 [T1] #0 T2 [T2] #1
***************
Now joining: T2 [T2] #1
***************
NL Join
Outer table: Card: 95.02 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 4
Inner table: T2 Alias: T2
Access Path: TableScan
NL Join: Cost: 475.12 Resp: 475.12 Degree: 0
Cost_io: 440.00 Cost_cpu: 204409816
Resp_io: 440.00 resp_cpus: 204409816
Kkofmx: index filter: "T2". "RN" <100 AND "T1". "RN" <100
Access Path: index (AllEqJoinGuess)
Index: IND_T2
Resc_io: 2.00 resc_cpu: 15463
Ix_sel: 1.0001e-004 ix_sel_with_filters: 9.9030e-007
NL Join (ordered): Cost: 115.77 Resp: 115.77 Degree: 1
Cost_io: 115.60 Cost_cpu: 950127
Resp_io: 115.60 resp_cpus: 950127
Best NL cost: 115.77
Resc: 115.77 resc_io: 115.60 resc_cpu: 950127
Resp: 115.77 resp_io: 115.60 resp_cpu: 950127
Join Card: 94.08 = outer (95.02) * inner (99.01) * sel (0.01)
Join Card-Rounded: 94 Computed: 94.08
SM Join
Outer table:
Resc: 2.01 card 95.02 bytes: 4 deg: 1 resp: 2.01
Inner table: T2 Alias: T2
Resc: 3.01 card: 99.01 bytes: 6 deg: 1 resp: 3.01
Using dmeth: 2 # groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 17 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost/pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5849269
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost = 0.00]
SMS cost: 6.02
Resc: 6.02 resc_io: 5.00 resc_cpu: 5941076
Resp: 6.02 resp_io: 5.00 resp_cpu: 5941076
HA Join
Outer table:
Resc: 2.01 card 95.02 bytes: 4 deg: 1 resp: 2.01
Inner table: T2 Alias: T2
Resc: 3.01 card: 99.01 bytes: 6 deg: 1 resp: 3.01
Using dmeth: 2 # groups: 1
Cost per ptn: 0.50 # ptns: 1
Hash_area: 0 (max = 0) Hash join: Resc: 5.52 Resp: 5.52 [multiMatchCost = 0.00]
HA cost: 5.52
Resc: 5.52 resc_io: 5.00 resc_cpu: 3025807
Resp: 5.52 resp_io: 5.00 resp_cpu: 3025807
Best: JoinMethod: Hash
Cost: 5.52 Degree: 1 Resp: 5.52 Card: 94.08 Bytes: 10
***********************
Best so far: Table #: 0 cost: 2.0057 cards: 95.0186 bytes: 380
Table #: 1 cost: 5.5199 card: 94.0778 bytes: 940
***********************
Join order [2]: T2 [T2] #1 T1 [T1] #0
***************
Now joining: T1 [T1] #0
***************
NL Join
Outer table: Card: 99.01 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 6
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 2051.15 Resp: 2051.15 Degree: 0
Cost_io: 1867.00 Cost_cpu: 1071678304
Resp_io: 1867.00 resp_cpus: 1071678304
Access Path: index (FFS ))
Index: IND_T1
Resc_io: 24.74 resc_cpu: 9484923
Ix_sel: 0.20.e + 000 ix_sel_with_filters: 1
Inner table: T1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 2613.36 Resp: 2613.36 Degree: 0
Cost_io: 2452.00 Cost_cpu: 939065714
Resp_io: 2452.00 resp_cpus: 939065714
Kkofmx: index filter: "T1". "RN" <100
Access Path: index (AllEqJoinGuess)
Index: IND_T1
Resc_io: 1.00 resc_cpu: 8171
Ix_sel: 1.9585e-005 ix_sel_with_filters: 3.6446e-008
NL Join (ordered): Cost: 102.15 Resp: 102.15 Degree: 1
Cost_io: 102.00 Cost_cpu: 872287
Resp_io: 102.00 resp_cpus: 872287
Best NL cost: 102.15
Resc: 102.15 resc_io: 102.00 resc_cpu: 872287
Resp: 102.15 resp_io: 102.00 resp_cpu: 872287
Join Card: 94.08 = outer (99.01) * inner (95.02) * sel (0.01)
Join Card-Rounded: 94 Computed: 94.08
SM Join
Outer table:
Resc: 3.01 card 99.01 bytes: 6 deg: 1 resp: 3.01
Inner table: T1 Alias: T1
Resc: 2.01 card: 95.02 bytes: 4 deg: 1 resp: 2.01
Using dmeth: 2 # groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 15 Total Rows: 95
Initial runs: 1 Merge passes: 0 IO Cost/pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5847820
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost = 0.00]
SMS cost: 6.02
Resc: 6.02 resc_io: 5.00 resc_cpu: 5939627
Resp: 6.02 resp_io: 5.00 resp_cpu: 5939627
HA Join
Outer table:
Resc: 3.01 card 99.01 bytes: 6 deg: 1 resp: 3.01
Inner table: T1 Alias: T1
Resc: 2.01 card: 95.02 bytes: 4 deg: 1 resp: 2.01
Using dmeth: 2 # groups: 1
Cost per ptn: 0.50 # ptns: 1
Hash_area: 0 (max = 0) Hash join: Resc: 5.52 Resp: 5.52 [multiMatchCost = 0.00]
HA cost: 5.52
Resc: 5.52 resc_io: 5.00 resc_cpu: 3026007
Resp: 5.52 resp_io: 5.00 resp_cpu: 3026007
Join order aborted: cost> best plan cost
***********************
(Newjo-stop-1) k: 0, spcnt: 0, perm: 2, maxperm: 2000
*********************************
Number of join permutations tried: 2
*********************************
(Newjo-save) [1 0]
Final-All Rows Plan: Best join order: 1
Cost: 5.5199 Degree: 1 Card: 94.0000 Bytes: 940
Resc: 5.5199 Resc_io: 5.0000 Resc_cpu: 3025807
Resp: 5.5199 Resp_io: 5.0000 Resc_cpu: 3025807
Kkoept: Query block SEL $1 (#0)
* ****** Unparsed query is *******
SELECT "T2 ". "RN" "RN", "T2 ". "NAME" "NAME" FROM "YORKER ". "T1" "T1", "YORKER ". "T2" "T2" WHERE "T1 ". "RN" <100 AND "T2 ". "RN" <100 AND "T1 ". "RN" = "T2 ". "RN"
Kkoqbc-end
: Call (in-use = 31732, alloc = 0), compile (in-use = 33436, alloc = 0)
Apadrv-end: call (in-use = 31732, alloc = 0), compile (in-use = 34024, alloc = 0)

SQL _id = ar0vn3xs804bs.
Current SQL statement for this session:
Explain plan for select t2. * from t1, t2 where t1.rn <100 and t1.rn = t2.rn

================
Plan Table
================
------------------------------------------------ + ----------------------------------- +
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------ + ----------------------------------- +
| 0 | select statement | 6 |
| 1 | hash join | 94 | 940 | 6 | 00:00:01 |
| 2 | index range scan | IND_T1 | 95 | 380 | 2 | 00:00:01 |
| 3 | table access by index rowid | T2 | 99 | 594 | 3 | 00:00:01 |
| 4 | index range scan | IND_T2 | 99 | 2 | 00:00:01 |
------------------------------------------------ + ----------------------------------- +

,