OB怎么查询使用了HINT未生效的SQL

概述

如果SQL中指定了HINT，一般是已知可能存在优化器默认生成的执行计划不优，或不稳定的情况，需要SQL中使用HINT干预执行计划生成，但有时HINT使用错误可能导致未达到预期目的，OB gv$plan_cache_plan_stat视图中hints_all_worked列标识SQL 计划中的 Hint 是否都已经生效,如果为0则说明存在HINT未生效的情况。可用于排查筛选该类SQL。

示例

MySQL [oceanbase]> select  sql_id,tenant_id,db_id,svr_ip,plan_id,plan_hash,type,first_load_time,executions,round(avg_exe_usec/1000) avg_exe_ms  from gv$plan_cache_plan_stat  where db_id>0 and plan_hash>0 and hints_all_worked!=1 order by avg_exe_ms desc,executions desc;
+----------------------------------+-----------+------------------+---------------+----------+----------------------+------+----------------------------+------------+------------+
| sql_id                           | tenant_id | db_id            | svr_ip        | plan_id  | plan_hash            | type | first_load_time            | executions | avg_exe_ms |
+----------------------------------+-----------+------------------+---------------+----------+----------------------+------+----------------------------+------------+------------+
| 571A4BFF9660F57398E6D354C37508D4 |      1001 | 1100611139404827 | 192.168.56.2  | 21447722 | 14836983998124181413 |    3 | 2024-07-04 05:27:26.819133 |          1 |       1341 |
| B802167F24F21BA04FFB460E3F6B6270 |      1001 | 1100611139404827 | 192.168.56.2  | 21447641 | 14836983998124181413 |    3 | 2024-07-04 05:26:33.370809 |          1 |       1304 |
| 88727C73580AC4AA7AAA98ECCC7DDBCB |      1001 | 1100611139404827 | 192.168.56.20 | 33630169 | 10110837992829868111 |    3 | 2024-07-04 10:00:36.225878 |          1 |        139 |
| CB86FE432B94B16139672546F04A598F |      1001 | 1100611139404827 | 192.168.56.20 | 33630230 |  3565053025673864369 |    3 | 2024-07-04 10:00:48.894566 |       7220 |        113 |
| 88727C73580AC4AA7AAA98ECCC7DDBCB |      1001 | 1100611139404827 | 192.168.56.20 | 33630168 |  9107288126125346488 |    3 | 2024-07-04 10:00:36.193794 |       7210 |        113 |
| 48A9B79A5337A8C400F218BE962736DA |      1001 | 1100611139404840 | 192.168.56.2  | 21482729 |  3322018207688878557 |    3 | 2024-07-04 08:29:50.767135 |         12 |         71 |
| 393C2FD8E56EF848E8047AEDCB283D79 |      1001 | 1100611139404827 | 192.168.56.2  | 21447718 |  9006399396330124769 |    3 | 2024-07-04 05:27:25.847050 |          1 |         60 |
......
157 rows in set (0.293 sec)