如何查看 OceanBase 数据库所有租户中的查询计划？

可以通过 sys 租户查看 OceanBase 数据库所有租户中的查询计划以及清理执行计划缓存。

1. 登录 sys 租户，可以通过以下命令查询 OceanBase 数据库所有租户的查询计划。

   obclient> SELECT svr_ip,svr_port, plan_Id,TYPE,d.database_name, query_sql, first_load_time, avg_exe_usec, slow_count,executions, slowest_exe_usec, sql_id FROM `gv$plan_cache_plan_stat` s JOIN `gv$database` d ON (s.tenant_id=d.tenant_id AND s.db_id=d.database_id) WHERE s.tenant_id=1001 AND d.database_name LIKE 'TPCC%' AND query_sql LIKE '%"BMSQL_WAREHOUSE"%' ORDER BY avg_exe_usec desc;
   

2. 根据上述得到的 TENANT_ID、PLAN_ID、SVR_IP 和 SVR_PORT，查询 gv$plan_cache_plan_explain 视图，展示对应的执行计划。

   oceanbase 16:01:01> SELECT * FROM gv$plan_cache_plan_explain WHERE tenant_id=1 AND plan_id=582518 and ip='xx.xxx.xx.xxx' and port=2882;
   +-----------+---------------+------+---------+------------------+-----------------------+------+------+-------------------------------------------------------------------------------------------------------------------------------+
   | TENANT_ID | IP            | PORT | PLAN_ID | OPERATOR         | NAME                  | ROWS | COST | PROPERTY                                                                                                                      |
   +-----------+---------------+------+---------+------------------+-----------------------+------+------+-------------------------------------------------------------------------------------------------------------------------------+
   |         1 | xx.xxx.xx.xxx | 2882 |  582518 |  PHY_SORT        | NULL                  |  100 | 2418 | NULL                                                                                                                          |
   |         1 | xx.xxx.xx.xxx | 2882 |  582518 |   PHY_TABLE_SCAN | __all_virtual_sysstat |  100 | 2000 | table_rows:100000, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:basic_stat |
   +-----------+---------------+------+---------+------------------+-----------------------+------+------+-------------------------------------------------------------------------------------------------------------------------------+
   2 rows in set (0.03 sec)
   

   注意

   如果不提供上述的四个字段，查询 gv$plan_cache_plan_explain 将不会返回任何结果

此外，如果您想清理执行计划缓存，可以通过 root 用户登录到 sys 租户，执行以下命令。

obclient> ALTER SYSTEM FLUSH PLAN CACHE GLOBAL;