首页 » OceanBase » Oceanbase中的 Optimizer trace Event （Similar to Oracle 10053 event）

Oceanbase中的 Optimizer trace Event （Similar to Oracle 10053 event）

前一篇记录了《Oceanbase中的Session SQL Trace DBMS_MONITOR （Similar to Oracle 10046 event）》， 这里我简单记录Oracle 诊断SQL问题时的另一个常用event 10053在Oceanbase中的体验， 对应的是dbms_xplan.enable_opt_trace(); 使用SET_OPT_TRACE_PARAMETER 配置当前session。下面记录如果OCEANBASE 优化器没有产生预期的执行计划时，如何使用trace跟踪生成更多的诊断信息。

在Oceanbase中同样会储存历史执行过的执行计划, __all_virtual_sql_plan数据字典中 ，在[G]v$ob_sql_plan 系统视图可以查询明细，使用dbms_xplan可以格式化的执行计划，如使用display_cursor，只是比oracle中增加了server和port，因为分布式可能在不同的机器上执行计划不同。

-- display sql plan table`s plan
function display_cursor(plan_id      integer default 0,             -- default value: last plan
                        format		 varchar2 default 'TYPICAL',
                        svr_ip       varchar2 default null,         -- default value: server connected by client
                        svr_port     integer default 0,             -- default value: server connected by client
                        tenant_id	 integer default 0              -- default value: current tenant
                        )
return dbms_xplan_type_table;

还有display_active_session_plan查看当前正在执行的其他session的执行计划。

-- disable real time plan
function display_active_session_plan(
           session_id   integer default 0,
           format       varchar2  default  'TYPICAL',
           svr_ip       varchar2 default null,      -- default value: server connected by client
           svr_port     integer default 0           -- default value: server connected by client
           )
return dbms_xplan_type_table;

执行计划的生成是优化器复杂的处理过程，有时需要更多的信息分析生成非预期执行计划的原因，如是否是数据倾斜产生错误的COST等，可以使用optimizer trace如oracle的10053 trace一样生成更多的数据信息到trace文件，从trace logfile中查看环境、SQL转换、优化器、统计信息、cost，Join order等记录。

配置optimizer trace 参数语法

DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(
    sql_id          IN VARCHAR2 DEFAULT '',
    identifier      IN VARCHAR2 DEFAULT DEFAULT_INENTIFIER,
    level           IN INT DEFAULT DEFAULT_LEVEL
);

Parameters

Parameter Description
sql_id The ID of the SQL statement to be traced. For example, if you want to run a PL program in a test and trace only a specific SQL statement in a PL function, you can specify sql_id to mark that SQL statement. If you do not specify a sql_id, all SQL statements are traced.
identifier The suffix of the trace file, which helps you easily find the trace file.
level The level of tracing. Valid values:

  • 0: Perform default actions.
  • 1: Print the memory usage and running duration of each module apart from performing level-0 tracing actions.
  • 2: Print the SQL statement corresponding to each rewrite query block, regardless of whether the SQL statement is rewritten or not, apart from performing level-0 and level-1 tracing actions.

Note: level is a database keyword and therefore must be enclosed by double quotation marks (” “) in an Oracle tenant and by grave accents (` `) in a MySQL tenant.

 

DEFAULT_INENTIFIER  constant VARCHAR2(20) := '';
DEFAULT_LEVEL       constant INT : = 1;
PROCEDURE enable_opt_trace(
    sql_id          IN VARCHAR2 DEFAULT '',
    identifier      IN VARCHAR2 DEFAULT DEFAULT_INENTIFIER,
    level           IN INT DEFAULT DEFAULT_LEVEL
);

 

典型的诊断方法

1， 如诊断slow sql

Log in to the sys tenant

select id, svr_ip, svr_ip, svr_port, info from __all_virtual_processlist where info like "%select%"\G

Query details of plans in the session.

select dbms_xplan.display_active_session_plan(3221668463, 'all', '11.xxx.xxx.xxx', 50000);

如果产生了非预期的执行计划，收信诊断信息

  1. Use ODP to retain the session.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  2. Enable tracing for the optimizer of the current session. 
    call dbms_xplan.enable_opt_trace();
  3. Specify the tracing level and the file name extension of the trace log file. 
    call dbms_xplan.set_opt_trace_parameter(identifier=>'trace_test', "level"=>3);
  4. Query plans. 
    explain select * from t1;
  5. Query trace log files whose file names end with trace_test.trac from the log directory of the OBServer node. 
    vi /home/admin/oceanbase/log/optimizer_trace_BkkGn1_trace_test.trac
  6. Disable tracing for the optimizer of the current session. 
    call dbms_xplan.disable_opt_trace();

 

实际测试

# obclient -h172.20.23.207 -uanbob@test -p*********4 -P2881 -DANBOB
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221508640
Server version: OceanBase 4.2.5.3 (r103000152025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:37:26)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


obclient(ANBOB@test)[ANBOB]> CALL DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(identifier=>'trace_anbob', "level"=>2);
Query OK, 0 rows affected (0.116 sec)


obclient(ANBOB@test)[ANBOB]> call dbms_xplan.enable_opt_trace();
Query OK, 0 rows affected (0.009 sec)

obclient(ANBOB@test)[ANBOB]> explain select count(*) from t1,t2 where t1.id=t2.id ;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ======================================================                             |
| |ID|OPERATOR           |NAME   |EST.ROWS|EST.TIME(us)|                             |
| ------------------------------------------------------                             |
| |0 |SCALAR GROUP BY    |       |1       |1578        |                             |
| |1 |└─MERGE JOIN       |       |10000   |1396        |                             |
| |2 |  ├─TABLE FULL SCAN|T1(ID1)|10000   |275         |                             |
| |3 |  └─TABLE FULL SCAN|T2(ID2)|10000   |275         |                             |
| ======================================================                             |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                            |
|       group(nil), agg_func([T_FUN_COUNT(*)])                                       |
|   1 - output(nil), filter(nil), rowset=256                                         |
|       equal_conds([T1.ID = T2.ID]), other_conds(nil)                               |
|       merge_directions([ASC])                                                      |
|   2 - output([T1.ID]), filter(nil), rowset=256                                     |
|       access([T1.ID]), partitions(p0)                                              |
|       is_index_back=false, is_global_index=false,                                  |
|       range_key([T1.ID], [T1.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true |
|   3 - output([T2.ID]), filter(nil), rowset=256                                     |
|       access([T2.ID]), partitions(p0)                                              |
|       is_index_back=false, is_global_index=false,                                  |
|       range_key([T2.ID], [T2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true |
+------------------------------------------------------------------------------------+
23 rows in set (0.010 sec)

obclient(ANBOB@test)[ANBOB]> call dbms_xplan.disable_opt_trace();
Query OK, 0 rows affected (0.007 sec)

阅读trace日志，有助于了解OB优化器的判断逻辑。

cd $OB_HOME/log
[xxx log]# cat optimizer_trace_anbob.trac

------------------------------------------------------
SYSTEM ENVIRONMENT
------------------------------------------------------
Version:        4.2.5.3_103000152025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b(Mar 31 2025 11:37:26)
------------------------------------------------------
SESSION INFO
------------------------------------------------------
Client Address: 172.20.23.207:41578
Proxy Address:  0.0.0.0:0
Tenant Name:    test
User Name:      ANBOB
Trace ID:       YB42AC1417CF-00063B4A70A35169-0-0
------------------------------------------------------
OPTIMIZER PARAMETERS
------------------------------------------------------
tenant config:
   enable_ps_parameterize  =  False
   _enable_ddl_worker_isolation  =  True
   partition_balance_schedule_interval  =  0
   _parallel_ddl_control  =  TRUNCATE_TABLE:ON, SET_COMMENT:ON, CREATE_INDEX:ON, CREATE_VIEW:ON, DROP_TABLE:ON
   _enable_mysql_compatible_dates  =  True
system variables:
   autocommit  =  0
   character_set_connection  =  46
   character_set_database  =  46
   character_set_server  =  46
   collation_connection  =  46
   collation_database  =  46
   collation_server  =  46
   sql_mode  =  2151677954
   datadir  =  '/data/1/ob_one'
   group_concat_max_len  =  32767
   version  =  '5.7.25-OceanBase-v4.2.5.3'
   server_uuid  =  'd14e673b-14fe-11f0-a671-52540033a742'
   ob_query_timeout  =  3600000000
   ob_trx_timeout  =  3600000000
   ob_capability_flag  =  1360128
   ob_compatibility_mode  =  1
   ob_tcp_invited_nodes  =  '%'
   parallel_servers_target  =  60
   ob_trx_idle_timeout  =  3600000000
   secure_file_priv  =  NULL
   privilege_features_enable  =  '4.2.5.3'
   _enable_mysql_pl_priv_check  =  1
   ob_compatibility_version  =  17180001539
   ob_security_version  =  17180001539
   __ob_client_capability_flag  =  1
   external_user  =  NULL
------------------------------------------------------
CURRENT SQL TEXT
------------------------------------------------------
sql_id = AE83E46F81715815630E220CB019715A
explain select count(*) from t1,t2 where t1.id=t2.id
------------------------------------------------------

...
transform for grouping sets and multi rollup: False
transform happened: True
select count(0) from "ANBOB"."T1","ANBOB"."T2" where ("ANBOB"."T1"."ID" = "ANBOB"."T2"."ID")
SECTION TIME USAGE: 446 us

...
------------------------------------------------------
BASIC TABLE STATISTICS
------------------------------------------------------

T1 :
    rows: 10000.000000 base rows: 10000.000000 statis type: OPTIMIZER version: 1751047964497055
    used partitions: [500075] normal stat partitions: [500075] histogram stat partitions: [500075]

    __pk_increment :
        NDV: 10000.000000
        BASE NDV: 10000.000000
        Null: 0.000000
        hist scale: -1.000000
        Min: __OB__MIN__
        Max: __OB__MAX__

    ID :
        NDV: 10000.000000
        BASE NDV: 10000.000000
        Null: 0.000000
        hist scale: -1.000000
        Min: __OB__MIN__
        Max: __OB__MAX__

T2 :
    rows: 10000.000000 base rows: 10000.000000 statis type: OPTIMIZER version: 1751047964550539
    used partitions: [500076] normal stat partitions: [500076] histogram stat partitions: [500076]

    __pk_increment :
        NDV: 10000.000000
        BASE NDV: 10000.000000
        Null: 0.000000
        hist scale: -1.000000
        Min: __OB__MIN__
        Max: __OB__MAX__

    ID :
        NDV: 10000.000000
        BASE NDV: 10000.000000
        Null: 0.000000
        hist scale: -1.000000
        Min: __OB__MIN__
        Max: __OB__MAX__

------------------------------------------------------
...

后面如果有时间，再继续写 解读trace的方法。

— over —

References
https://en.oceanbase.com/docs/common-oceanbase-cloud-10000000002694812

打赏

, ,

目前这篇文章还没有评论(Rss)

我要评论

上一篇：

下一篇：