本文通过分析一个具体的SQL案例，展示了如何识别并解决ViewMerging导致的性能问题。通过禁用ViewMerging，将原本需要六小时才能完成的查询缩短至十几秒内得出结果。
今天遇到一个View Merging 的案例
具体SQL如下：
SELECT /*+ INDEX(d CUST_ASSOC_DNORM_IDX2) */
DISTINCT e.geo_id, d.ctrl_perd cust_ctrl_perd, d.parnt_id cust_id
FROM
( -- customer list
SELECT s.node_id cust_id
, s.node_id_2 geo_id
FROM idwsu3.smrt_node_val s
WHERE s.cube_id = 'SCE07'
AND s.col_name_2 = 'GEO_ID'
AND s.col_name = 'CUST_ID'
) e,
idwsu3.cust_assoc_dnorm d
WHERE d.strct_code='898'
AND d.ctrl_perd IN ( SELECT ctrl_perd
FROM idwsu3.ref_ctrl
WHERE fact_type_code = 'BS'
AND stage_id IN (30,31)
AND strct_code = '898'
)
AND d.dsend_id = e.cust_id;
这个SQL跑了6个多小时还没跑完
执行计划如下：
SQL> explain plan for SELECT /*+ INDEX(d CUST_ASSOC_DNORM_IDX2) */
2 DISTINCT e.geo_id, d.ctrl_perd cust_ctrl_perd, d.parnt_id cust_id
3 FROM
4 ( -- customer list
5 SELECT s.node_id cust_id
6 , s.node_id_2 geo_id
7 FROM idwsu3.smrt_node_val s
8 WHERE s.cube_id = 'SCE07'
9 AND s.col_name_2 = 'GEO_ID'
10 AND s.col_name = 'CUST_ID'
11 ) e,
12 idwsu3.cust_assoc_dnorm d
13 WHERE d.strct_code='898'
14 AND d.ctrl_perd IN ( SELECT ctrl_perd
15 FROM idwsu3.ref_ctrl
16 WHERE fact_type_code = 'BS'
17 AND stage_id IN (30,31)
18 AND strct_code = '898'
19 )
20 AND d.dsend_id = e.cust_id;
Explained.
Elapsed: 00:00:13.10
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 535292439
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 57 (4)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 88 | 57 (4)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 88 | 56 (2)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 53 | 6 (0)| 00:00:01 | | |
| 4 | INLIST ITERATOR | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | REF_CTRL | 1 | 19 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX UNIQUE SCAN | REF_CTRL_PK | 1 | | 2 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ITERATOR | | 1 | 34 | 3 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| CUST_ASSOC_DNORM | 1 | 34 | 3 (0)| 00:00:01 | KEY | KEY |
|* 9 | INDEX RANGE SCAN | CUST_ASSOC_DNORM_IDX2 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
| 10 | PARTITION RANGE SINGLE | | 1 | 35 | 50 (2)| 00:00:01 | 51 | 51 |
|* 11 | INDEX RANGE SCAN | SMRT_NODE_VAL_IDX2 | 1 | 35 | 50 (2)| 00:00:01 | 51 | 51 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("FACT_TYPE_CODE"='BS' AND ("STAGE_ID"=30 OR "STAGE_ID"=31) AND "STRCT_CODE"='898')
9 - access("D"."STRCT_CODE"='898' AND "D"."CTRL_PERD"="CTRL_PERD")
filter("D"."CTRL_PERD"="CTRL_PERD")
11 - access("S"."CUBE_ID"='SCE07' AND "S"."COL_NAME"='CUST_ID' AND "D"."DSEND_ID"="S"."NODE_ID" AND
"S"."COL_NAME_2"='GEO_ID')
filter("S"."COL_NAME_2"='GEO_ID' AND "D"."DSEND_ID"="S"."NODE_ID")
28 rows selected.
这个SQL用到的表如下(带星号表示用了索引)：
OWNER TABLE_NAME Size(Mb) PARTITIONED DEGREE NUM_ROWS
-------------------- ------------------------------ ---------- -------------------- ---------- -------------
IDWSU3 *REF_CTRL .038383484 NO 1 1118
IDWSU3 REF_CTRL .038383484 NO 1 1118
IDWSU3 *SMRT_NODE_VAL 148.379308 YES 1 4714757
IDWSU3 *CUST_ASSOC_DNORM 88388.443 YES 1 975600000
可以看到表CUST_ASSOC_DNORM非常大，有88G，一般我都是遇到加HINT的问题SQL，直接去掉HINT，执行计划如下：
SQL> explain plan for SELECT
2 DISTINCT e.geo_id, d.ctrl_perd cust_ctrl_perd, d.parnt_id cust_id
3 FROM
4 ( -- customer list
5 SELECT s.node_id cust_id
6 , s.node_id_2 geo_id
7 FROM idwsu3.smrt_node_val s
8 WHERE s.cube_id = 'SCE07'
9 AND s.col_name_2 = 'GEO_ID'
10 AND s.col_name = 'CUST_ID'
11 ) e,
12 idwsu3.cust_assoc_dnorm d
13 WHERE d.strct_code='898'
14 AND d.ctrl_perd IN ( SELECT ctrl_perd
15 FROM idwsu3.ref_ctrl
16 WHERE fact_type_code = 'BS'
17 AND stage_id IN (30,31)
18 AND strct_code = '898'
19 )
20 AND d.dsend_id = e.cust_id;
Explained.
Elapsed: 00:00:53.97
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3859030211
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 56 (4)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 88 | 56 (4)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 88 | 55 (2)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 53 | 5 (0)| 00:00:01 | | |
| 4 | INLIST ITERATOR | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| REF_CTRL | 1 | 19 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX UNIQUE SCAN | REF_CTRL_PK | 1 | | 2 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ITERATOR | | 1 | 34 | 2 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | CUST_ASSOC_DNORM_PK | 1 | 34 | 2 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION RANGE SINGLE | | 1 | 35 | 50 (2)| 00:00:01 | 51 | 51 |
|* 10 | INDEX RANGE SCAN | SMRT_NODE_VAL_IDX2 | 1 | 35 | 50 (2)| 00:00:01 | 51 | 51 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("FACT_TYPE_CODE"='BS' AND ("STAGE_ID"=30 OR "STAGE_ID"=31) AND "STRCT_CODE"='898')
8 - access("D"."CTRL_PERD"="CTRL_PERD" AND "D"."STRCT_CODE"='898')
10 - access("S"."CUBE_ID"='SCE07' AND "S"."COL_NAME"='CUST_ID' AND "D"."DSEND_ID"="S"."NODE_ID" AND
"S"."COL_NAME_2"='GEO_ID')
filter("S"."COL_NAME_2"='GEO_ID' AND "D"."DSEND_ID"="S"."NODE_ID")
26 rows selected.
Elapsed: 00:00:02.17
执行计划变了，这个执行计划没有用到 HINT 的索引，而是用了 CUST_ASSOC_DNORM_PK，现在来看一下这些索引有多大
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Gb)
------------------------------ ------------------------------ ------------------------------ ----------
IDWSU3 CUST_ASSOC_DNORM_PK INDEX PARTITION 14.9101563
IDWSU3 CUST_ASSOC_DNORM_IDX2 INDEX PARTITION 13.484375
IDWSU3 SMRT_NODE_VAL_IDX2 INDEX PARTITION .286987305
查看大表上有哪些索引，分别建立在什么列上
SQL> select index_name,column_position,descend,column_name from dba_ind_columns
2 where table_owner=upper('IDWSU3') and table_name=upper('CUST_ASSOC_DNORM') order by 1,2;
Index Name Pos# Order Column Name
------------------------------ ---- -------- ------------------------------
CUST_ASSOC_DNORM_IDX2 1 ASC STRCT_CODE
2 ASC DSEND_ID
3 ASC CTRL_PERD
4 ASC NET_LVL
5 ASC DSEND_LVL
CUST_ASSOC_DNORM_IDX3 1 ASC STRCT_CODE
2 ASC CTRL_PERD
3 ASC PARNT_ID
4 ASC NET_LVL
CUST_ASSOC_DNORM_PK 1 ASC CTRL_PERD
2 ASC STRCT_CODE
3 ASC PARNT_ID
4 ASC DSEND_ID
根据以上信息，结合大表的过滤条件，索引CUST_ASSOC_DNORM_IDX2 和 CUST_ASSOC_DNORM_PK 都可以用来 检索数据
再仔细分析这个SQL
SELECT
DISTINCT e.geo_id, d.ctrl_perd cust_ctrl_perd, d.parnt_id cust_id
FROM
( -- customer list
SELECT s.node_id cust_id
, s.node_id_2 geo_id
FROM idwsu3.smrt_node_val s
WHERE s.cube_id = 'SCE07'
AND s.col_name_2 = 'GEO_ID'
AND s.col_name = 'CUST_ID'
) e,
idwsu3.cust_assoc_dnorm d
WHERE d.strct_code='898'
AND d.ctrl_perd IN ( SELECT ctrl_perd
FROM idwsu3.ref_ctrl
WHERE fact_type_code = 'BS'
AND stage_id IN (30,31)
AND strct_code = '898'
)
AND d.dsend_id = e.cust_id;
看一下 in 会返回多少数据
SQL> SELECT ctrl_perd
2 FROM idwsu3.ref_ctrl
3 WHERE fact_type_code = 'BS'
4 AND stage_id IN (30,31)
5 AND strct_code = '898';
CTRL_PERD
------------------
25-APR-11
08-MAY-11
Elapsed: 00:00:00.93
只返回2行数据，再看看 FROM 后面的子查询会返回多少数据，这里我用Explain plan 看一下
SQL> explain plan for SELECT s.node_id cust_id
2 , s.node_id_2 geo_id
3 FROM idwsu3.smrt_node_val s
4 WHERE s.cube_id = 'SCE07'
5 AND s.col_name_2 = 'GEO_ID'
6 AND s.col_name = 'CUST_ID';
Explained.
Elapsed: 00:00:05.21
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1519841108
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14392 | 491K| 51 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 14392 | 491K| 51 (2)| 00:00:01 | 51 | 51 |
|* 2 | INDEX RANGE SCAN | SMRT_NODE_VAL_IDX2 | 14392 | 491K| 51 (2)| 00:00:01 | 51 | 51 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CUBE_ID"='SCE07' AND "S"."COL_NAME"='CUST_ID' AND "S"."COL_NAME_2"='GEO_ID')
filter("S"."COL_NAME_2"='GEO_ID')
15 rows selected.
Elapsed: 00:00:01.25
这里和之前的执行计划返回的结果完全不一样，所以怀疑CBO 对这个子查询进行 View Merging 出错了，使用hint /*+ NO_MERGE*/ 禁止View Merging
SQL> explain plan for SELECT
2 DISTINCT e.geo_id, d.ctrl_perd cust_ctrl_perd, d.parnt_id cust_id
3 FROM
4 ( SELECT /*+ NO_MERGE*/ s.node_id cust_id
5 , s.node_id_2 geo_id
6 FROM idwsu3.smrt_node_val s
7 WHERE s.cube_id = 'SCE07'
8 AND s.col_name_2 = 'GEO_ID'
9 AND s.col_name = 'CUST_ID'
10 ) e,
11 idwsu3.cust_assoc_dnorm d
12 WHERE d.strct_code='898'
13 AND d.ctrl_perd IN ( SELECT ctrl_perd
14 FROM idwsu3.ref_ctrl
15 WHERE fact_type_code = 'BS'
16 AND stage_id IN (30,31)
17 AND strct_code = '898'
18 )
19 AND d.dsend_id = e.cust_id;
Explained.
Elapsed: 00:00:14.71
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2600639289
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 57 (4)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 67 | 57 (4)| 00:00:01 | | |
|* 2 | HASH JOIN | | 1 | 67 | 56 (2)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 53 | 5 (0)| 00:00:01 | | |
| 4 | INLIST ITERATOR | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| REF_CTRL | 1 | 19 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX UNIQUE SCAN | REF_CTRL_PK | 1 | | 2 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ITERATOR | | 1 | 34 | 2 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | CUST_ASSOC_DNORM_PK | 1 | 34 | 2 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION RANGE SINGLE | | 14392 | 196K| 51 (2)| 00:00:01 | 51 | 51 |
| 10 | VIEW | | 14392 | 196K| 51 (2)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | SMRT_NODE_VAL_IDX2 | 14392 | 491K| 51 (2)| 00:00:01 | 51 | 51 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DSEND_ID"="E"."CUST_ID")
6 - access("FACT_TYPE_CODE"='BS' AND ("STAGE_ID"=30 OR "STAGE_ID"=31) AND "STRCT_CODE"='898')
8 - access("D"."CTRL_PERD"="CTRL_PERD" AND "D"."STRCT_CODE"='898')
11 - access("S"."CUBE_ID"='SCE07' AND "S"."COL_NAME"='CUST_ID' AND "S"."COL_NAME_2"='GEO_ID')
filter("S"."COL_NAME_2"='GEO_ID')
27 rows selected.
Elapsed: 00:00:01.67
大家注意看执行计划，变成了HASH JOIN了，跑一下SQL
.....................省略..................................................
000 25-APR-11 2000379797
000 08-MAY-11 2000380033
151 rows selected.
Elapsed: 00:00:43.02
其实这个SQL没跑完，我ctrl+c 了，但是它能在10多秒以内出结果，而非以前6小时不动。
为了简便起见，我省略了其他信息，本SQL没有统计信息过期问题。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
