oracle 的10046和10053 event是SQL优化时研究优化器的一些手段，前面测试过Oceanbase 的10053 event，本篇尝试YashanDB的。及以一个小例子看看在order by消除等查询转换是否支持。
Oracle 19C
选择一个较常见的场景，有时应用框架在做分布查询前需要记录总页数，而会直接把分页的SQL前直接套上count算总记录数，这里子查询中可能有分页的order by，其实对于count总数，排序是没有必要的。如果消除一些不必要的同时又不影响最终结果，这算是优化器的强大之处。
SQL> explain plan for select count(*) from (select distinct * from test_ec1 t1 order by 1);
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4118444652
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 10 | | 3 (34)| 00:00:01 |
| 3 | HASH UNIQUE | | 10 | 210 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST_EC1 | 10 | 210 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
11 rows selected.
SQL> alter table test_ec1 add primary key(id);
Table altered.
SQL> explain plan for select count(*) from (select distinct * from test_ec1 t1 order by 1);
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2720188007
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_EC1_ID | 10 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------
Note: 可见oracle有order by 消除，还有增加了pk后还有distinct消除，及查询merge合并。
看看oracle 10053中的记录
SQL> ALTER SESSION SET EVENTS = '10053 TRACE NAME CONTEXT FOREVER, LEVEL 2';
Session altered.
SQL> explain plan for select count(*) from (select distinct * from test_ec1 t1 order by id);
Explained.
SQL> @t
TRACEFILE
---------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_3796316.trc
SQL> ALTER SESSION SET EVENTS = '10053 TRACE NAME CONTEXT off';
Session altered.
==========
Registered qb: SEL$1 0x766a9750 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"
Registered qb: SEL$2 0xc5c4ab60 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$2 nbfros=1 flg=0
fro(0): flg=4 objn=131491 hint_alias="T1"@"SEL$2"
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=5f9nd9f27z64r) -----
explain plan for select count(*) from (select distinct * from test_ec1 t1 order by id)
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
AAT - Approximate Aggregate Transformation
ORE - CBQT OR-Expansion
LORE - Legacy OR-Expansion
...
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 19.1.0
_optimizer_search_limit = 5
cpu_count = 4
active_instance_count = 1
parallel_threads_per_cpu = 1
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
...
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: Considering Order-by Elimination from view SEL$2 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: Removing order by from query block SEL$2 (#0) (order not used)
Registered qb: SEL$73523A42 0xc5c4ab60 (ORDER BY REMOVED FROM QUERY BLOCK SEL$2; SEL$2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$73523A42 nbfros=1 flg=0
fro(0): flg=0 objn=131491 hint_alias="T1"@"SEL$2"
OBYE: OBYE performed.
OBYE: OBYE bypassed: no order by to eliminate.
Eliminated SELECT DISTINCT from query block SEL$73523A42 (#0)
CVM: Merging SPJ view SEL$73523A42 (#0) into SEL$1 (#0)
Registered qb: SEL$51F12574 0x766a9750 (VIEW MERGE SEL$1; SEL$73523A42; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$51F12574 nbfros=1 flg=0
fro(0): flg=0 objn=131491 hint_alias="T1"@"SEL$2"
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "SYS"."TEST_EC1" "T1"
kkoqbc: optimizing query block SEL$51F12574 (#1)
...
Note： 优化器参数，一些优化转换，后面是cost及最佳执行计划，只粘贴了部分。
YashanDB 23.5
同样的表结构，注意test_ec1表上有pk主键。
SQL> explain select count(*) from (select distinct * from test_ec1 t1 order by 1);
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 3637367764
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | AGGREGATE | | | 1| 19( 0)| |
| 2 | VIEW | | | 2349| 19( 0)| |
| 3 | INDEX FAST FULL SCAN | IDX_EC1_ID | SYS | 2349| 19( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Note: yashanDB 同样也消除了Order by ,和distinct 消除，但是没有做 view merge.
看看yashanDB 10053 event
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> ALTER SESSION SET TRACE_FILE_IDENTIFIER = 'YashanDB_Trace';
Succeed.
SQL> ALTER SESSION SET EVENTS = '10053 TRACE NAME CONTEXT FOREVER, LEVEL 2';
Succeed.
SQL> explain select count(*) from (select distinct * from test_ec1 t1 order by object_id);
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 432293891
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | AGGREGATE | | | 1| 19( 0)| |
| 2 | VIEW | | | 2349| 19( 0)| |
| 3 | INDEX FAST FULL SCAN | IDX_EC1_ID | SYS | 2349| 19( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
11 rows fetched.
SQL> ALTER SESSION SET EVENTS = '10053 TRACE NAME CONTEXT off';
Succeed.查看trace 内容
[yashan@yashan-74 ~]$ cd $YASDB_DATA/diag/trace/
[yashan@yashan-74 trace]$ ls -lrt
-rw-r----- 1 yashan yashan 8711 Jan 30 17:23 yashandb_20260130_79_YashanDB_Trace.trc
[yashan@yashan-74 trace]$ vi yashandb_20260130_79_YashanDB_Trace.trc
ashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 037bc48c23
Session ID: 79 (2026-01-30 04:57:25.447)
**************** Trace Start [Event: 10053 Level: 2] ****************
Current SQL statement:
select count(*) from (select distinct * from test_ec1 t1 order by object_id)
*************** CBO Optimize Trace Start *************
Group 0
Logical : Select [ 1 ]
Physical 0 : Select [ 1 ]
Group 1
Logical : Project [ 2 ]
Physical 0 : Project [ 2 ]
Group 2
Logical : Aggr [ 3 ]
Physical 0 : Aggr [ 3 ]
Group 3
Logical : ViewScan [ 4 ]
Physical 0 : ViewScan [ 4 ]
Group 4
Logical : Select [ 5 ]
Physical 0 : Select [ 5 ]
Group 5
Logical : Project [ 6 ]
Physical 0 : Project [ 6 ]
Group 6
Logical : Scan Table Name: T1
Physical 0 : IndexScan Index Name: IDX_EC1_ID Type: INDEX_FULL_SCAN
Physical 1 : IndexScan Index Name: IDX_EC1_ID Type: INDEX_FAST_FULL_SCAN
Physical 2 : TableFullScan Table Name: T1
Physical 0: IndexScan
IndexName: IDX_EC1_ID IndexType: INDEX_FULL_SCAN
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=20.066507 total=20.066507 fetch=0.208940
Physical 1: IndexScan
IndexName: IDX_EC1_ID IndexType: INDEX_FAST_FULL_SCAN
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=19.935234 total=19.935234 fetch=0.208916
Physical 2: TableFullScan
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=26.645645 total=26.645645 fetch=0.602583
Group 6 Best :
Logical : Scan Table Name: T1
Best Physical : IndexScan Index Name: IDX_EC1_ID Type: INDEX_FAST_FULL_SCAN
Cost: self=19.935234 total=19.935234 fetch=0.208916
Physical 0: Project
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=0.000000 total=19.935234 fetch=0.208916
...
*************** Trace Bottom Up **********************
Group 6
Logical : Scan Table Name: T1
Physical 0 : IndexScan Index Name: IDX_EC1_ID Type: INDEX_FULL_SCAN
Physical 1 : IndexScan Index Name: IDX_EC1_ID Type: INDEX_FAST_FULL_SCAN
Physical 2 : TableFullScan Table Name: T1
Physical 0: IndexScan
IndexName: IDX_EC1_ID IndexType: INDEX_FULL_SCAN
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=26.645645 total=26.645645 fetch=0.602583
Physical 1: IndexScan
IndexName: IDX_EC1_ID IndexType: INDEX_FAST_FULL_SCAN
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=26.645645 total=26.645645 fetch=0.602583
Physical 2: TableFullScan
Stats: rows=2349 rowSize=1 keyDistinct=1 columns=1 selectivity=1.000000
Cost: self=26.645645 total=26.645645 fetch=0.602583
Group 6 Best :
Logical : Scan Table Name: T1
Best Physical : IndexScan Index Name: IDX_EC1_ID Type: INDEX_FAST_FULL_SCAN
Cost: self=19.935234 total=19.935234 fetch=0.208916
...
***************** CBO Optimize Trace End *************
...
Note: 和oracle比，一个字形容：简陋，支持的消除和查询转换trace中没有输出，当时的CBO parameter在这个版本都没有，文档上看23.4还有。 后面有执行计划cost的对比和执行计划。
小结：
yashanDB 目前支持像oracle中的10053 event，且语法相似， 但是目前的内容输出有较大的提升空间。至少像优化器参数应该输出，方便分析是关因关闭了某参数特性导致的执行计划改变。