通过获取用户侧实际慢的SQL查询AUDIT信息如下。
通过 GV$PLAN_CACHE_PLAN_EXPLIAN 获取执行慢时的物理执行计划。
=====================================================================================================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|0 |COUNT | |50 |26308 |
|1 |└─SUBPLAN SCAN |VIEW1 |50 |26308 |
|2 | └─LIMIT | |50 |26308 |
|3 | └─HASH RIGHT OUTER JOIN | |50 |26308 |
|4 | ├─PX COORDINATOR | |197 |1242 |
|5 | │ └─EXCHANGE OUT DISTR |:EX10000 |197 |1074 |
|6 | │ └─TABLE RANGE SCAN |BFCODEITEMS_JYBM(CODEITEMOFSET) |197 |696 |
|7 | └─NESTED-LOOP OUTER JOIN | |50 |24953 |
|8 | ├─HASH RIGHT OUTER JOIN | |50 |24041 |
|9 | │ ├─PX COORDINATOR | |182 |413 |
|10| │ │ └─EXCHANGE OUT DISTR |:EX20000 |182 |290 |
|11| │ │ └─TABLE FULL SCAN |BFCURRENCY_PAY |182 |15 |
|12| │ └─NESTED-LOOP OUTER JOIN | |50 |23529 |
|13| │ ├─NESTED-LOOP OUTER JOIN | |50 |22614 |
|14| │ │ ├─NESTED-LOOP OUTER JOIN | |50 |21701 |
|15| │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |20788 |
|16| │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |19875 |
|17| │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |18962 |
|18| │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |18050 |
|19| │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |17137 |
|20| │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |16224 |
|21| │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |15312 |
|22| │ │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |14399 |
|23| │ │ │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |13486 |
|24| │ │ │ │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |12573 |
|25| │ │ │ │ │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |11661 |
|26| │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |10749 |
|27| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |50 |9837 |
|28| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─HASH RIGHT OUTER JOIN | |50 |8925 |
|29| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |71 |484 |
|30| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX30000 |71 |423 |
|31| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE RANGE SCAN |BFCODEITEMS_HKYT(CODEITEMOFSET) |71 |287 |
|32| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |8391 |
|33| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |182 |413 |
|34| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX40000 |182 |290 |
|35| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFCURRENCY_SXF |182 |15 |
|36| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |7879 |
|37| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |253 |548 |
|38| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX50000 |253 |385 |
|39| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFNATIONALANDREGIONALDICT |253 |18 |
|40| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |7202 |
|41| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |182 |413 |
|42| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX60000 |182 |290 |
|43| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFCURRENCY_C |182 |15 |
|44| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |6691 |
|45| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |182 |413 |
|46| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX70000 |182 |290 |
|47| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFCURRENCY |182 |15 |
|48| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |6179 |
|49| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |90 |297 |
|50| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX80000 |90 |209 |
|51| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFCASHFLOWTYPE |90 |11 |
|52| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |5820 |
|53| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |13 |74 |
|54| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX90000 |13 |53 |
|55| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFSETTLEMENTWAY |13 |5 |
|56| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |5722 |
|57| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |5 |16 |
|58| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX100000 |5 |12 |
|59| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFFINANCIALSUBJECTS |5 |5 |
|60| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─HASH RIGHT OUTER JOIN | |50 |5688 |
|61| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─PX COORDINATOR | |8 |21 |
|62| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX110000 |8 |16 |
|63| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE FULL SCAN |BFBUSINESSMATTER |8 |5 |
|64| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─SUBPLAN SCAN |VIEW3 |50 |5648 |
|65| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─LIMIT | |50 |5648 |
|66| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─NESTED-LOOP JOIN | |50 |5647 |
|67| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─TABLE FULL SCAN |TMPAYMENTSETTLEMENT |47 |1211 |
|68| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─PX COORDINATOR | |1 |45 |
|69| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX120000 |1 |45 |
|70| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─SUBPLAN SCAN |VIEW2 |1 |45 |
|71| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─MERGE UNION DISTINCT | |1 |45 |
|72| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├─TABLE RANGE SCAN |GSPUSERPOSORG(IDX_USERPOSORG_USERORGPOS)|1 |25 |
|73| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET|BFADMINORGANIZATION |1 |21 |
|74| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFADMINDIVISION |1 |18 |
|75| │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFADMINORGANIZATION_100 |1 |18 |
|76| │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFFUNDSPROP |1 |18 |
|77| │ │ │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS_YSGLLX |1 |18 |
|78| │ │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS_JJFL |1 |18 |
|79| │ │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS_ZCLX |1 |18 |
|80| │ │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS_YSLY |1 |18 |
|81| │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS |1 |18 |
|82| │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFACCOUNTINGORGANIZATION |1 |18 |
|83| │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS_SKRZHLX |1 |18 |
|84| │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFCODEITEMS_FKXZ |1 |18 |
|85| │ │ │ │ │ └─DISTRIBUTED TABLE GET |BFBANKACCOUNTS_CSH |1 |18 |
|86| │ │ │ │ └─DISTRIBUTED TABLE GET |BFBANKACCOUNTS_SXF |1 |18 |
|87| │ │ │ └─DISTRIBUTED TABLE GET |BFBANKACCOUNTS_300 |1 |18 |
|88| │ │ └─DISTRIBUTED TABLE GET |BFBANK_705 |1 |18 |
|89| │ └─DISTRIBUTED TABLE GET |BFBANKACCOUNTS_PAY |1 |18 |
|90| └─DISTRIBUTED TABLE GET |BFBANK_1 |1 |18 |
=====================================================================================================================================================================
可以看到，两者的执行计划不一致：
执行快时：执行计划有82个算子
执行慢时，执行计划有90个算子
截取快的SQL部分算子：
1.两者的该部分执行计划都是语义上相同：
2.但是可以明显看到慢的计划中：
3.为什么有这种现象，猜测大可能的原因是不同表的Leader分布在不同。
我们继续分析下各个基表的Leader分布，查询语句如下
select DATABASE_NAME,TABLE_NAME,TABLE_TYPE,ZONE,SVR_IP,ROLE from dba_ob_table_locations where role = 'LEADER' and database_name = 'RENZYTEST1' and table_name in (
'TMPAYMENTSETTLEMENT',
'BFNATIONALANDREGIONALDICT',
'BFADMINDIVISION',
'BFADMINORGANIZATION',
'BFSETTLEMENTWAY',
'BFCODEITEMS',
'BFCURRENCY',
'BFCASHFLOWTYPE',
'BFBUSINESSMATTER',
'BFBANKACCOUNTS',
'BFBANK',
'BFFINANCIALSUBJECTS',
'BFFUNDSPROP',
'BFACCOUNTINGORGANIZATION',
'GSPUSERPOSORG',
'BFADMINORGANIZATION'
)
order by zone;
+---------------+---------------------------+------------+-------+-------------+--------+
| DATABASE_NAME | TABLE_NAME | TABLE_TYPE | ZONE | SVR_IP | ROLE |
+---------------+---------------------------+------------+-------+-------------+--------+
| RENZYTEST1 | TMPAYMENTSETTLEMENT | USER TABLE | zone1 | 10.18.38.54 | LEADER |
| RENZYTEST1 | BFFUNDSPROP | USER TABLE | zone1 | 10.18.38.54 | LEADER |
| RENZYTEST1 | GSPUSERPOSORG | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFSETTLEMENTWAY | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFNATIONALANDREGIONALDICT | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFFINANCIALSUBJECTS | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFBUSINESSMATTER | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFADMINORGANIZATION | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFADMINDIVISION | USER TABLE | zone2 | 10.18.38.55 | LEADER |
| RENZYTEST1 | BFACCOUNTINGORGANIZATION | USER TABLE | zone3 | 10.18.38.56 | LEADER |
| RENZYTEST1 | BFCURRENCY | USER TABLE | zone3 | 10.18.38.56 | LEADER |
| RENZYTEST1 | BFCODEITEMS | USER TABLE | zone3 | 10.18.38.56 | LEADER |
| RENZYTEST1 | BFBANKACCOUNTS | USER TABLE | zone3 | 10.18.38.56 | LEADER |
| RENZYTEST1 | BFBANK | USER TABLE | zone3 | 10.18.38.56 | LEADER |
| RENZYTEST1 | BFCASHFLOWTYPE | USER TABLE | zone3 | 10.18.38.56 | LEADER |
+---------------+---------------------------+------------+-------+-------------+--------+
15 rows in set (0.157 sec)
可以看到，上述执行计划章节中部分差异 算子涉及的基表 Leader 分布确实不同：
由此可知：因为涉及到跨节点分布式访问多表，SQL在不同节点生成的执行计划是不一致的，所以其耗时效率也是不一致的。
继续多次执行该条SQL，获取下耗时分布：
可以看到发往不同svr_ip的SQL，其SQL一样（SQL_ID相同），但是其产生的执行计划确截然不同（通过Plan_id可以获知）。
通过分析过程，我们可以获知问题原因如下：
上述问题在分布式数据库中无法避免，那面临该种CASE时，OB如何优化？
在业务SQL中加并发HINT，例如代码中最外层投影中加上 SELECT /*+ PARALLEL(9) */ ，即可充分利用分布式的并行查询优势。
对于Hint也无法解决，或者需要深度SQL优化的场景，OB提供了表组[注1 ]功能进行优化，。
表组（Table Group）是一个逻辑概念，表示一组表的集合，即将一组表的Leader集中在一个OBserver节点上。默认情况下，不同表之间的数据副本Leader是随机分布的。使用 Table Group 将一组表Leader分布在相同的节点上，以减少跨节点的数据交互。
# 创建表组
CREATE TABLEGROUP tg_sk1 SHARDING = 'NONE';
# 将SQL涉及的基表添加到表组中
ALTER TABLEGROUP tg_sk1 ADD TMPAYMENTSETTLEMENT,BFNATIONALANDREGIONALDICT,BFADMINDIVISION,BFADMINORGANIZATION,BFSETTLEMENTWAY,BFCODEITEMS,BFCURRENCY,BFCASHFLOWTYPE,BFBUSINESSMATTER,BFBANKACCOUNTS,BFBANK,BFFINANCIALSUBJECTS,BFFUNDSPROP,BFACCOUNTINGORGANIZATION,GSPUSERPOSORG,BFADMINORGANIZATION;
+---------------+---------------------------+------------+-------+-------------+----------------+--------+
| DATABASE_NAME | TABLE_NAME | TABLE_TYPE | ZONE | SVR_IP | PARTITION_NAME | ROLE |
+---------------+---------------------------+------------+-------+-------------+----------------+--------+
| RENZYTEST1 | BFACCOUNTINGORGANIZATION | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | TMPAYMENTSETTLEMENT | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | GSPUSERPOSORG | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFSETTLEMENTWAY | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFNATIONALANDREGIONALDICT | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFFUNDSPROP | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFFINANCIALSUBJECTS | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFCURRENCY | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFCODEITEMS | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFBUSINESSMATTER | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFBANKACCOUNTS | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFBANK | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFCASHFLOWTYPE | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFADMINORGANIZATION | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
| RENZYTEST1 | BFADMINDIVISION | USER TABLE | zone2 | 10.18.38.55 | NULL | LEADER |
+---------------+---------------------------+------------+-------+-------------+----------------+--------+
多次执行该SQL，可以看到该SQL无论发往哪个节点上，执行耗时基本稳定在400ms左右。
因为所有的基表Leader都在一个节点上，减少了分布式算子的开销（如下图中，PLAN_TYPE基本为1、2,未再有3）。
1为本地执行计划，2为远程执行计划，3为分布式执行计划，其原理不展开赘述。
为何会有3s，1.8s，1.7s的SQL执行，相同SQL_ID执行在不同OBserver上执行时，第一次执行时都会进行硬解析，去生成执行计划缓存，可以看到各节点上后续的执行耗时基本能稳定。
