10053 Event Analysis Case 1

Two tables in the test database have the same data (the TABLE has A composite primary key A + B), but the DELETE TABLE FROM T1/T2 WHERE A = '1' AND ROWNUM statement is also executed <100; table T1. In PLSQL or sqlplus, the execution plan is the same, indicating that index range scanning is used.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | delete statement | 1000 | 12000 | 3217 |
| 1 | DELETE | T1 |
| * 2 | count stopkey |
| * 3 | index range scan | IDX_T1 | 420K | 4931K | 3217 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id ):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2-filter (ROWNUM <= 1000)
3-access ("T1". "A" = '1 ')
Note: cpu costing is off

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | delete statement | 1000 | 12000 | 2965 |
| 1 | DELETE | T2 | |||
| * 2 | count stopkey |
| * 3 | index range scan | IDX_T2 | 393K | 4607K | 2965 |
--------------------------------------------------------------------
Predicate Information (identified by operation id ):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2-filter (ROWNUM <= 1000)
3-access ("T2". "A" = '1 ')
Note: cpu costing is off

Obviously, the actual operations of the two tables are not consistent with the execution plan, and the 10053 event plays a role.

10053 introduction:

10053 event is an internal event provided by oracle to track SQL statement cost calculation. It records how the oracle optimizer calculates SQL costs in CBO mode and generates corresponding execution plans. It is used to describe how oracle selects the execution plan and then outputs the plan to the trace file, because we often see how the execution plan consumes resources, instead of looking at how the execution plan is selected.

10053 features:

(1) only understand the selection process of oracle execution plan

(2) The cost calculation formula cannot be known because it is an internal trade secret of oracle, and the computing formulas of optimizer in each oracle version are quite different, different versions of the same statement have different costs. The optimizer is not yet mature and needs to be improved.

(3) In this case, we need to know how the "price" is calculated and how the execution plan is selected.

(4) In section 10053, you can find out which factors affect the SQL Execution cost.

(5) oracle 8i cost equivalent IO resources consume 9i and later cost equivalent IO + CPU + network + wait event + other costs

10053 event information of table T1:

***************************************

BASE STATISTICAL INFORMATION
***********************
Table stats Table: T1 Alias: T1 from user_tables View
TOTAL: CDN: 2341358 NBLKS: 13921 AVG_ROW_LEN: 40
-- Index stats from user_indexes View
Index name: IDX_STAROTHER COL #: 2 3
TOTAL: LVLS: 2 # LB: 13609 # DK: 2156054 LB/K: 1 DB/K: 1 CLUF: 165252
_ OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO Col #: 2 Table: T1Alias: T1
Newcastle: 7 NULLS: 0 DENS: 1.4286e-01
No histogram: # BKT: 1 # VAL: 2
TABLE: starotherprf orig cdn: 2341358 rounded cdn: 334480 cmptd cdn: 334480
Access path: tsc Resc: 1340 Resp: 1340 full table scan cost (1340). Here, tsc should be abbreviated as TableScan.
Skip scan: ss-sel 0 andv 308008
Ss cost 308008 index skip scanning cost (1945)
Index io scan cost 1945
Access path: index (index-only) index (range) scan cost (1947)
Index: IDX_T1
TABLE: T1
RSC_CPU: 0 RSC_IO: 1947
IX_SEL: 1.4286e-01 TB_SEL: 1.4286e-01

BEST_CST: 1340.00 PATH: 2 Degree: 1 the best price is 1340, that is, full table scan.

Corresponding execution plan:

***************************************
GENERAL PLANS
***********************
Join order [1]: STAROTHERPRF [STAROTHERPRF] #0
Best so far: TABLE #: 0 CST: 1340 CDN: 334480 BYTES: 4348240
Final-All Rows Plan:
Join order: 1
CST: 1340 CDN: 334480 RSC: 1340 RSP: 1340 BYTES: 4348240
IO-RSC: 1340 IO-RSP: 1340 CPU-RSC: 0 CPU-RSP: 0
QUERY
Explain plan for delete from starotherprf WHERE AIRLINE_CODE = 'us' and rownum <= 1000
PLAN
Cost of plan: 1340
Operation... Object name... Options ..
Delete statement 0
Delete starotherprf 1
Count stopkey 2 1
Table access T1 FULL 3 2
QUERY

Full table scan is used for display.

Event 10053 in Table T2:

***************************************

SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO Col #: 1 Table: T2 Alias: T2
Newcastle: 19 NULLS: 0 DENS: 5.2632e-02
No histogram: # BKT: 1 # VAL: 2
TABLE: castarprf orig cdn: 6665065 rounded cdn: 350793 CMPTD cdns: 350793
Access path: tsc Resc: 4275 Resp: 4275 full table scan cost (4275)
Skip scan: ss-sel 0 andv 413617 index Skip scan cost (413617)
Ss cost: 413617
Index io scan cost 1973
Access path: index (index-only) index (range) scan cost (1975)
Index: IDX_T2
TABLE: T2
RSC_CPU: 0 RSC_IO: 1975
IX_SEL: 5.2632e-02 TB_SEL: 5.2632e-02
BEST_CST: 1975.00 PATH: 4 Degree: 1 the best price is 1975, that is, index scanning.

Corresponding execution plan:

***************************************
GENERAL PLANS
***********************
Join order [1]: CASTARPRF [CASTARPRF] #0
Best so far: TABLE #: 0 CST: 1975 CDN: 350793 BYTES: 4911102
Prefetching is on for IDX_CASTAR
Final-All Rows Plan:
Join order: 1
CST: 1975 CDN: 350793 RSC: 1975 RSP: 1975 BYTES: 4911102
IO-RSC: 1975 IO-RSP: 1975 CPU-RSC: 0 CPU-RSP: 0
QUERY
Explain plan for delete from castarprf WHERE AIRLINE_CODE = 'us' and rownum <= 1000
PLAN
Cost of plan: 1975
Operation... Object name... Options ..
Delete statement 0
Delete castarprf 1
Count stopkey 2 1
INDEX IDX_T2 range scan 3 2
QUERY

Index scanning is used for display.

Now we can know why the two tables have different deletion times, because the CBO of table T1 selects an incorrect execution plan, resulting in full table scan, therefore, millions of data will take longer.

Conclusion: when the execution of an SQL statement is incorrect but the cause cannot be found, use 10053 to analyze the cause. This is the applicable scenario of 10053.