[RAC性能调优] [SQL Tuning]一个订单查询sql总是走错索引，帮忙分析下，谢谢

2^#

发表于 2013-8-27 17:00:48

给出使用 HINT 和不用HINT的 10053 trace level 1

repentance

3^#

发表于 2013-8-27 17:18:43

本帖最后由 repentance 于 2013-8-27 17:21 编辑

AND o.cust_id IN (SELECT info.cust_id
                     FROM t_cum_info info
                     WHERE 1 = 1
                     AND info.prtn_id IN
                           (SELECT prtn_id
                              FROM t_pnm_partner
                           START WITH prtn_id = 7640
                           CONNECT BY PRIOR prtn_id = parent_id)
                     AND info.stat = 'S0A'
                     AND info.stat = 'S0A')
上面in这个子句的结果集估算记录数过小，有235条记录，实际值大概是多少条记录？

使用/*+gather_plan_statistics*/执行一下语句，看下实际的执行信息。

4^#

发表于 2013-8-27 17:43:38

Maclean Liu(刘相兵发表于 2013-8-27 17:00
给出使用 HINT 和不用HINT的 10053 trace level 1

hello，感谢
信息见附件

sql_10053_trace_lev1.rar

37.12 KB, 阅读权限: 10, 下载次数: 19

5^#

发表于 2013-8-27 17:46:04

repentance 发表于 2013-8-27 17:18
AND o.cust_id IN (SELECT info.cust_id
FROM t_cum_info info
...

你好，谢谢;
实际值
SQL> SELECT count(info.cust_id)
  2                         FROM t_cum_info info
  3                      WHERE 1 = 1
  4                         AND info.prtn_id IN
  5                            (SELECT prtn_id
  6                               FROM t_pnm_partner
  7                               START WITH prtn_id = 7640
  8                               CONNECT BY PRIOR prtn_id = parent_id);

COUNT(INFO.CUST_ID)
-------------------
         113838

6^#

发表于 2013-8-27 19:34:10

-------------------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 45K | |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 982 | 25K | 45K | 00:09:10 |
| 4 | VIEW | VW_NSO_2 | 235 | 3055 | 402 | 00:00:05 |
| 5 | HASH UNIQUE | | 235 | 6345 | | |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 235 | 6345 | 402 | 00:00:05 |
| 8 | VIEW | VW_NSO_1 | 188 | 2444 | 26 | 00:00:01 |
| 9 | HASH UNIQUE | | 188 | 3384 | 26 | 00:00:01 |
| 10 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | T_PNM_PARTNER | 1 | 10 | 2 | 00:00:01 |
| 12 | INDEX UNIQUE SCAN | PK_T_PNM_PARTNER | 1 | | 1 | 00:00:01 |
| 13 | NESTED LOOPS | | 187 | 4301 | 23 | 00:00:01 |
| 14 | CONNECT BY PUMP | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | T_PNM_PARTNER | 187 | 1870 | 21 | 00:00:01 |
| 16 | INDEX RANGE SCAN | IX_PNM_PARTNER_PARENT_ID| 187 | | 1 | 00:00:01 |
| 17 | INDEX RANGE SCAN | IX_CUM_INFO_ORGID | 1 | | 1 | 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | T_CUM_INFO | 1 | 14 | 2 | 00:00:01 |
| 19 | INDEX RANGE SCAN | IX_SCS_ORDER_CUSTID | 552 | | 3 | 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | T_SCS_ORDER | 4 | 52 | 444 | 00:00:06 |
-------------------------------------------------------------------------------+-----------------------------------+

复制代码

Predicate Information:
----------------------
10 - access("PARENT_ID"=PRIOR NULL)
12 - access("PRTN_ID"=7640)
16 - access("connect$_by$_pump$_004"."PRIOR prtn_id "="PARENT_ID")
17 - access("INFO"."PRTN_ID"="PRTN_ID")
18 - filter("INFO"."STAT"='S0A')
19 - access("O"."CUST_ID"="CUST_ID")
20 - filter(("O"."ACCT_DATE">=TO_DATE(' 2013-08-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O"."ACCT_DATE"<=TO_DATE(' 2013-08-19 23:59:59', 'syyyy-mm-dd hh24:mi:ss')))

^^^^^^^^^^ NOT HINT

============
-----------------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 177K | |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | HASH JOIN RIGHT SEMI | | 982 | 25K | 177K | 00:36:21 |
| 3 | VIEW | VW_NSO_2 | 235 | 3055 | 402 | 00:00:05 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 235 | 6345 | 402 | 00:00:05 |
| 6 | VIEW | VW_NSO_1 | 188 | 2444 | 26 | 00:00:01 |
| 7 | HASH UNIQUE | | 188 | 3384 | 26 | 00:00:01 |
| 8 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | T_PNM_PARTNER | 1 | 10 | 2 | 00:00:01 |
| 10 | INDEX UNIQUE SCAN | PK_T_PNM_PARTNER | 1 | | 1 | 00:00:01 |
| 11 | NESTED LOOPS | | 187 | 4301 | 23 | 00:00:01 |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | T_PNM_PARTNER | 187 | 1870 | 21 | 00:00:01 |
| 14 | INDEX RANGE SCAN | IX_PNM_PARTNER_PARENT_ID| 187 | | 1 | 00:00:01 |
| 15 | INDEX RANGE SCAN | IX_CUM_INFO_ORGID | 1 | | 1 | 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | T_CUM_INFO | 1 | 14 | 2 | 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | T_SCS_ORDER | 188K | 2446K | 177K | 00:36:16 |
| 18 | INDEX RANGE SCAN | IX_SCS_ORDER_ACCTDATE | 195K | | 1344 | 00:00:17 |
-----------------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------

复制代码

Predicate Information:
----------------------
2 - access("O"."CUST_ID"="CUST_ID")
8 - access("PARENT_ID"=PRIOR NULL)
10 - access("PRTN_ID"=7640)
14 - access("connect$_by$_pump$_004"."PRIOR prtn_id "="PARENT_ID")
15 - access("INFO"."PRTN_ID"="PRTN_ID")
16 - filter("INFO"."STAT"='S0A')
18 - access("O"."ACCT_DATE">=TO_DATE(' 2013-08-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O"."ACCT_DATE"<=TO_DATE(' 2013-08-19 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

^^^^^^ HINT

7^#

发表于 2013-8-27 19:42:38

SQL> SELECT count(info.cust_id)
  2                         FROM t_cum_info info
  3                      WHERE 1 = 1
  4                         AND info.prtn_id IN
  5                            (SELECT prtn_id
  6                               FROM t_pnm_partner
  7                               START WITH prtn_id = 7640
  8                               CONNECT BY PRIOR prtn_id = parent_id);

COUNT(INFO.CUST_ID)
-------------------
         113838

==>这里CBO 犯了错误

NL Join
  Outer table: Card: 188.00  Cost: 26.04  Resp: 26.04  Degree: 1  Bytes: 13
Access path analysis for T_CUM_INFO
  Inner table: T_CUM_INFO  Alias: INFO
  Access Path: TableScan
NL Join:  Cost: 98042.62  Resp: 98042.62  Degree: 1
   Cost_io: 96055.00  Cost_cpu: 16438539726
   Resp_io: 96055.00  Resp_cpu: 16438539726
  Column (#1): PRTN_ID(
AvgLen: 5 NDV: 83971 Nulls: 0 Density: 0.000012 Min: 7640 Max: 111189
  Access Path: index (AllEqJoinGuess)
Index: IX_CUM_INFO_ORGID
resc_io: 2.00  resc_cpu: 16671
ix_sel: 0.000012  ix_sel_with_filters: 0.000012
NL Join (ordered): Cost: 402.42  Resp: 402.42  Degree: 1
   Cost_io: 401.00  Cost_cpu: 11737992
   Resp_io: 401.00  Resp_cpu: 11737992

  Best NL cost: 402.42
      resc: 402.42  resc_io: 401.00  resc_cpu: 11737992
      resp: 402.42  resp_io: 401.00  resc_cpu: 11737992
Join Card:  235.363347 = outer (188.000000) * inner (106096.288513) * sel (0.000012)

CBO  认为上述子查询返回 235. ，但是实际为113838 这导致后续的 19 - access("O"."CUST_ID"="CUST_ID") + IX_SCS_ORDER_CUSTID    选择不正确

8^#

发表于 2013-8-27 19:43:46

在查一下:

SELECT count (prtn_id)
                           FROM t_pnm_partner
                           START WITH prtn_id = 7640
                           CONNECT BY PRIOR prtn_id = parent_id

9^#

发表于 2013-8-27 20:46:23

Maclean Liu(刘相兵发表于 2013-8-27 19:43
在查一下:

SELECT count (prtn_id)

结果如下：

SQL> SELECT count (prtn_id)
  2                               FROM t_pnm_partner
  3                               START WITH prtn_id = 7640
  4                            CONNECT BY PRIOR prtn_id = parent_id;

COUNT(PRTN_ID)
--------------
      85595

10^#

发表于 2013-8-27 20:54:12

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: connect$_by$_work$_set$_008  Alias: connect$_by$_work$_set$_008  (NOT ANALYZED)
#Rows: 0  #Blks:  0  AvgRowLen:  0.00  ChainCnt:  0.00
  Column (#2): PRTN_ID(
AvgLen: 5 NDV: 83971 Nulls: 0 Density: 0.000012 Min: 7640 Max: 111189
Access path analysis for connect$_by$_work$_set$_008

Grouping column cardinality [ PRTN_ID] 188
***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  connect$_by$_work$_set$_008[connect$_by$_work$_set$_008]#0
***********************
Best so far:  Table#: 0  cost: 27.0403  card: 188.0000  bytes: 3384
***********************

Grouping column cardinality [ PRTN_ID] 188
***************************************

188.0000  对比 85595 ，显然connect by PRIOR 的情况下  CBO的基数估算有问题

11^#

发表于 2013-8-27 20:55:09

对于这个问题可以考虑删除统计信息并锁定统计信息，使用动态采样保证基数评估准确，前提是这些SQL 能共足够好的共享游标，不频繁硬解析

12^#

发表于 2013-8-27 22:25:33

Maclean Liu(刘相兵发表于 2013-8-27 20:54
***************************************
BASE STATISTICAL INFORMATION
***********************

谢谢！仔细看了你的分析，貌似是CBO根据统计信息进行了基数评估，而评估与实际的rows差距较大，导致CBO走错了索引。
那是否使用其他方式，可以把准确的统计信息收集完整呢？我其实对订单表也收集了直方图，对cum和partner表做了基于所有字段的表分析，还是没有改善。
我们也考虑过，建1个(CUST_ID, ACCT_DATE)的复合索引，但是表上的索引较多了，建多了，也影响插入、更新的性能。
另外，CBO的基数估算有问题，12c有没有要修复的迹象呀？你在内部是否有消息

13^#

发表于 2013-8-29 13:40:06

我建议你先试一下我11楼的建议

对于connect by 等复杂一些的操作 CBO真的很难估算正确的基数

14^#

发表于 2013-9-17 17:24:37

Maclean Liu(刘相兵发表于 2013-8-29 13:40
我建议你先试一下我11楼的建议

对于connect by 等复杂一些的操作 CBO真的很难估算正确的基数 ...

好的，刘大，感谢