一次嵌套循环的优化

于 2010-12-04 16:18:00 发布 · 206 阅读 · 0
文章标签：

#数据库

本文分析了一个长期运行的SQL作业效率低下问题，通过调整执行计划和更新统计信息，将执行时间从2小时缩短到8分钟。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

主要环境如下:

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

4节点 HPUX RAC OLAP 环境

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_block_size integer 16384
SQL> show parameter db_file

NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_file_multiblock_read_count integer 64


ETL 开发人员发来邮件说有个long running job，跑了2小时左右 还未完成 叫我check一下

SQL> select * from table(dbms_xplan.display_cursor('gh1hw18uz6dcm',0));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID gh1hw18uz6dcm, child number 0
-------------------------------------
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 TABLESPACE
OPTIMA01M nologging as SELECT PROD_SKID, RELTV_CURR_QTY,
STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VW

Plan hash value: 2933813170

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 8883 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | HASH GROUP BY | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 9 | NESTED LOOPS | | 3134 | 165K| 8881 (1)| 00:01:02 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| 355 (13)| 00:00:03 | Q1,00 | PCWP | |
|* 12 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 13 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

11 - access(:Z>=:Z AND :Z<=:Z)
filter("UOM"."RELTV_CURR_QTY"=1)
12 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
13 - filter(("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID"))


36 rows selected.

那么这个JOB 很简单，就是一个 create table xxx as select

上面的执行计划是有问题的，执行计划里面显示有PX 操作，其实我没发现有并行，不过上面的执行计划访问路径是对的
关于执行计划显示错误，这里不予讨论，11G上面有很多BUG 我们已经遇到5个了，另外也不要问我为什么升级到11gR1

那么实际上要执行的SQL就是
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 TABLESPACE
OPTIMA01M nologging as SELECT PROD_SKID, RELTV_CURR_QTY,
STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VW

OPT_REF_BASE_UOM_DIM_VW是一个视图，该视图定义如下

SELECT UOM.PROD_SKID,
MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
FROM OPT_REF_UOM_TEMP_SDIM UOM,
REF_PROD_DIM PROD
WHERE UOM.RELTV_CURR_QTY = 1
AND PROD.CURR_IND = 'Y'
AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
AND PROD.PROD_SKID = UOM.PROD_SKID
AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
GROUP BY UOM.PROD_SKID

那么现在 这个视图的查询效率就直接决定了该JOB的效率，我们 现在来看这个视图的执行计划

SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;

Explained.

Elapsed: 00:00:01.29
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3215660883

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 1 | HASH GROUP BY | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3034 | 159K| 15506 (1)| 00:01:47 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 2967 | 101K| 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")

22 rows selected.

Elapsed: 00:00:02.06

大家请看执行计划 中ID=3的步骤，cost从 650一下子飙升到15506,根据我的SQL调优经验，这是非常不能理解的。
我怀疑CBO选出了执行计划。

那么这里OPT_REF_UOM_TEMP_SDIM 作为驱动表，Oracle先对它做一次全表扫描，然后应用过滤条件
WHERE UOM.RELTV_CURR_QTY = 1 所得到的数据作为驱动行源(你可以看 filter("UOM"."RELTV_CURR_QTY"=1))
那么这里 CBO计算出 根据 filter("UOM"."RELTV_CURR_QTY"=1) 过滤后 会返回2967条数据


SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM;

COUNT(*)
----------
2137706

Elapsed: 00:00:08.87
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;

COUNT(*)
----------
946432

Elapsed: 00:00:01.54
SQL> select 946432/2137706 from dual;

946432/2137706
--------------
.442732537

Elapsed: 00:00:02.04

根据我下面的查询，那么很明显CBO计算错误，filter("UOM"."RELTV_CURR_QTY"=1) 过滤之后实际上会返回946432条数据
然而 CBO认为只返回 2967条数据，所以这里不应该走嵌套循环，应该走 hash。
为什么不应该走嵌套循环？原因在于

SQL> select 946432/2137706 from dual;

946432/2137706
--------------
.442732537
全表扫描然后过滤之后 会返回44%的数据， 什么时候应该选择嵌套循环 ，通常情况下驱动表(行源) 应该返回
少量数据，而且被驱动表上面有选择性很高的索引才选择 嵌套循环。也就是说通常情况下驱动表应该返回不超过
源表10%的数据，而这里居然返回44%的数据，明显打错特错了。

我自己测试了一下，如果走嵌套循环要花 2小时40分钟

867176 rows selected.

Elapsed: 02:39:58.33

Execution Plan
----------------------------------------------------------
Plan hash value: 3215660883

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 108 | 15996 (1)| 00:01:50 |
| 1 | HASH GROUP BY | | 2 | 108 | 15996 (1)| 00:01:50 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3134 | 165K| 15995 (1)| 00:01:50 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| 649 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")


注意，这个嵌套循环的数据和上面的嵌套循环的数据有点出入，原因是数据有变动，统计信息有变动，不过不影响案例

如果走 HASH 连接 ，只要 8 分钟左右，怎么让它走hash? 这里我给大家列举3中方法

1 使用hint full 强制PROD_DIM 走全表扫描
2 使用hint use_hash 强制2表做hash连接
3 使用hint leading 强制 PROD_DIM 作为驱动表

867176 rows selected.

Elapsed: 00:07:52.33

Execution Plan
----------------------------------------------------------
Plan hash value: 612020119

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 3240 | | 44136 (5)| 00:05:04 |
| 1 | HASH GROUP BY | | 60 | 3240 | | 44136 (5)| 00:05:04 |
|* 2 | HASH JOIN | | 3065 | 161K| 29M| 44135 (5)| 00:05:04 |
|* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)| 00:04:56 |
|* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| | 649 (14)| 00:00:05 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
4 - filter("UOM"."RELTV_CURR_QTY"=1)

那么到这里 还没完，我们不仅仅要对SQL做优化，还要搞清楚为什么CBO选择错了执行计划，根据上面的分析，
CBO选择错误的执行计划的原因在于 它认为 filter("UOM"."RELTV_CURR_QTY"=1) 只返回2967 条数据，所以
为题出现在统计信息 上面，而且是列统计信息上面 那么我们来查询一些 列的统计信息

SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');

NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NUM_NULLS DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- --------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2160000 728 .000337037 0 .001373626 NONE

大家请看，列上面一共只有728个基数(唯一值)，然而表有200多万的数据，并且没有对列收集过直方图统计
那么我现在对该列收集直方图

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
3 tabname => 'OPT_REF_UOM_TEMP_SDIM',
4 estimate_percent => 100,
5 method_opt => 'for columns RELTV_CURR_QTY size 200',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

我再来查看一下该列的统计信息，这里基数上升到2110，并且直方图也收集了

SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');

NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NUM_NULLS DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- ----------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2137706 2110 .000987039 0 .00217122 HEIGHT BALANCED

我们再来查看一下执行计划

SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;

Explained.

Elapsed: 00:00:00.82

这回执行计划就走对了

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

Plan hash value: 612020119

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12097 | 637K| | 44911 (5)| 00:05:09 |
| 1 | HASH GROUP BY | | 12097 | 637K| | 44911 (5)| 00:05:09 |
|* 2 | HASH JOIN | | 951K| 48M| 29M| 44799 (5)| 00:05:08 |
|* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)| 00:04:56 |
|* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 951K| 31M| | 654 (15)| 00:00:05 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
4 - filter("UOM"."RELTV_CURR_QTY"=1)

20 rows selected.

也许你们说 是由于统计信息过期导致的，那么我现在把直方图删除

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
3 tabname => 'OPT_REF_UOM_TEMP_SDIM',
4 estimate_percent => 100,
5 method_opt => 'for columns RELTV_CURR_QTY size 1',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.65
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;

Explained.

Elapsed: 00:00:00.82
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------

Plan hash value: 3215660883

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 5722 (2)| 00:00:40 |
| 1 | HASH GROUP BY | | 9 | 486 | 5722 (2)| 00:00:40 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1036 | 55944 | 5721 (2)| 00:00:40 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 1013 | 35455 | 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")

22 rows selected.

Elapsed: 00:00:01.86

所以，最终导致CBO选错执行计划的罪魁祸首就是列没有收集直方图统计

通过这一次的案例分析，相信大家对嵌套循环的优化应该不成问题了，大家一定要深刻理解嵌套循环的原理
另外一个就是，大家要深入研究统计信息，CBO能不能选对执行计划，很关键的就在于统计信息的准确与否。

iteye_20478
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值