Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › SQL语句执行计划问题请教

0 积分	1 好友	6 主题

发消息

[ORA-600/ORA-7445] SQL语句执行计划问题请教

1^#

发表于 2013-12-11 20:11:19 | 查看: 5942| 回复: 14

本帖最后由 SKYLINE.LIU 于 2013-12-12 09:48 编辑

数据库版本 11.1.0.7.0

SQL语句
select count(*)
from xcxt.t_sj_sjjbxx sj
inner join xcxt.t_sys_organization org
   on org.orgcode = sj.bldwid
where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd hh24:mi:ss')
   and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd hh24:mi:ss')
   and org.treepath like '11000000000000.11010600000000' || '%'

默认使用NESTED LOOPS 方式，逻辑读很大 188731，首次执行时间为6~8s
强制使用HASH JOIN 方式，逻辑读降为 1117，首次执行时间小于1s

t_sj_sjjbxx  lrrq 每天的数据量 8000~10000左右

表数据量情况
T_SJ_SJJBXX 5550289
T_SYS_ORGANIZATION 3522

组合索引情况
t_sj_sjjbxx  lrrq,bldwid
t_sys_organization  treepath,orgcode

INDEX_NAME DISTINCT_KEYS NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
INDEX_SJ_SJJBXX_RQDW 4908281 5526878 2 26028 4734152
CIX_SYS_ORGANIZATION_TREECODE 3522 3522 1 40 444

复制代码

索引列统计信息

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY
T_SJ_SJJBXX BLDWID 575 88460 1 0.00173913043478261
T_SJ_SJJBXX LRRQ 792962 2020 1 1.26109447867615E-6
T_SYS_ORGANIZATION ORGCODE 3517 0 1 0.000284333238555587
T_SYS_ORGANIZATION TREEPATH 3517 0 1 0.000284333238555587

复制代码

获取了语句的10053与10046trace，请各位专家给看看，除了使用Hint 使语句走HASH JOIN 外，还有没有其他办法？

附：
1、建立t_sys_organization 的临时表，创建 treepath,orgcode 的组合索引，使用临时表查询，语句使用 hash join 方式，收集临时表统计信息后，执行计划变为 NESTED LOOPS
2、生产环境不建议使用 hint 呀

HASH_JOIN执行计划.txt

2.49 KB, 下载次数: 563

2.5 KB, 下载次数: 532

34.68 KB, 下载次数: 531

23.1 KB, 下载次数: 524

38.92 KB, 下载次数: 1350

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2013-12-11 20:54:18

select count(*) from xcxt.t_sys_organization org
where org.treepath like '11000000000000.11010600000000' || '%';

select count(*)
from xcxt.t_sj_sjjbxx sj
where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd hh24:mi:ss')
and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd hh24:mi:ss');

给出这2个查询

回复只看该作者道具举报

SKYLINE.LIU

3^#

发表于 2013-12-12 09:30:53

本帖最后由 SKYLINE.LIU 于 2013-12-12 10:41 编辑

SQL> select count(1) from xcxt.t_sys_organization org
2 where org.treepath like '11000000000000.11010600000000' || '%'
3 /
COUNT(1)
----------
157
Elapsed: 00:00:00.01
SQL> select count(1) from xcxt.t_sj_sjjbxx sj
2 where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd hh24:mi:ss')
3 and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd hh24:mi:ss')
4 /
COUNT(1)
----------
253707
Elapsed: 00:00:07.19
SQL> select count(1) from xcxt.t_sj_sjjbxx sj
2 where sj.lrrq >= to_date('2013-09-01', 'yyyy-mm-dd hh24:mi:ss')
3 and sj.lrrq < to_date('2013-09-30', 'yyyy-mm-dd hh24:mi:ss')
4 /
COUNT(1)
----------
234159
Elapsed: 00:00:00.92

复制代码

6.94 KB, 下载次数: 1106

256 KB, 下载次数: 522

4^#

发表于 2013-12-13 10:20:19

补充信息：
T_SJ_SJJBXX 分析表 gather_table_stats estimate_percent 采样行的百分比为5%时，LRRQ列字段的distinct _keys 为79W，与实际不同值数差别很大，实际distinct _keys为 460W

在正式环境测试了 LRRQ区间间隔几天，1个月，1年，使用HASH_JOIN方式比使用NESTED_LOOPS方式效率要高

各位专家，像这种ORACLE选择错误的执行计划的情况，大家通常采取什么方式处理呢？
我所想到的就是加HINT与使用sql profile固定执行计划，加HINT方式，开发一般不会同意。

期待各位专家支招~

回复只看该作者道具举报

bamuta

5^#

发表于 2013-12-13 13:07:20

1 T_SJ_SJJBXX ZFBA 5170580 258529 29-OCT-13 15:47:20 9 1400448 22 0 232555
2 T_SYS_ORGANIZATION ZFBA 3517 3517 14-SEP-13 12:11:24 5 3518 32 0 2016

14-SEP-13 12:11:24 这个是统计信息时间吧，会不会太旧？

回复只看该作者道具举报

SKYLINE.LIU

6^#

发表于 2013-12-13 13:46:24

T_SYS_ORGANIZATION 这个表是机构表，95%以上数据是2011年底系统上线时初始化进去的，一年变动的机构不超过20个，而且这个表的数据量很小，数据才3000多

回复只看该作者道具举报

Liu Maclean(刘相兵

7^#

发表于 2013-12-15 19:25:43

-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                         | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                            |    1 | 93 |    4 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE    |                            |    1 | 93 |          |       |
|*  2 | FILTER          |                            |    |    |          |       |
| 3 | NESTED LOOPS    |                            |    1 | 93 |    4 (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE |    1 | 70 |    2 (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW       |    1 | 23 |    2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
   filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')

id=4的步骤影响  NL 的循环次数，CBO认为返回1条记录，所以成本很低

'11000000000000.11010600000000'

==》虽然只有30位但是可能导致直方图在使用时评估不准确

对于此问题考虑用SQL PROFILE吧

set linesize 200 pagesize 2000
@?/rdbms/admin/sqltrpt
输入 SQL_ID

并给出结果

回复只看该作者道具举报

weiranth

8^#

发表于 2013-12-16 14:36:36

假如把该列直方图的信息删掉，执行计划会是什么样子呢? 会走HASH_JOIN??

回复只看该作者道具举报

SKYLINE.LIU

9^#

发表于 2013-12-16 17:41:57

Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 6d0b2rq5dj5u9
Sql Id specified: 6d0b2rq5dj5u9
Elapsed: 00:00:00.01
Tune the sql
~~~~~~~~~~~~
Elapsed: 00:00:00.00
Elapsed: 00:00:39.57
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_59486
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 12/16/2013 17:18:30
Completed at : 12/16/2013 17:19:08
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 6d0b2rq5dj5u9
SQL Text : select count(*)
from xcxtt_sj_sjjbxx sj
inner join xcxtt_sys_organization org
on org.orgcode = sj.bldwid
where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd
hh24:mi:ss')
and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd
hh24:mi:ss')
and org.treepath like '11000000000000.11010600000000' || '%'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.35%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_59486',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(ms): 7339 50 99.31%
CPU Time(ms): 7430 70 99.05%
User I/O Time(ms): 0 0
Buffer Gets: 188731 1209 99.35%
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2027379502
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 316 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 93 | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 25658 | 2330K| 316 (0)| 00:00:04 |
|* 4 | INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE | 157 | 10990 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW | 442 | 10166 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2013-11-30','yyyy-mm-dd
hh24:mi:ss'))
4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
5 - access("SJ"."LRRQ">=TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss') AND
"ORG"."ORGCODE"="SJ"."BLDWID" AND "SJ"."LRRQ"<TO_DATE('2013-11-30','yyyy-mm-dd hh24:mi:ss'))
filter("ORG"."ORGCODE"="SJ"."BLDWID")
2- Original With Adjusted Cost
------------------------------
Plan hash value: 2027379502
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 316 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 93 | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 25658 | 2330K| 316 (0)| 00:00:04 |
|* 4 | INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE | 157 | 10990 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW | 442 | 10166 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2013-11-30','yyyy-mm-dd
hh24:mi:ss'))
4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
5 - access("SJ"."LRRQ">=TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss') AND
"ORG"."ORGCODE"="SJ"."BLDWID" AND "SJ"."LRRQ"<TO_DATE('2013-11-30','yyyy-mm-dd hh24:mi:ss'))
filter("ORG"."ORGCODE"="SJ"."BLDWID")
3- Using SQL Profile
--------------------
Plan hash value: 2716384415
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 8 (38)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 93 | | |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN | | 25658 | 2330K| 8 (38)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE | 157 | 10990 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW | 258K| 5804K| 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2013-11-30','yyyy-mm-dd
hh24:mi:ss'))
3 - access("ORG"."ORGCODE"="SJ"."BLDWID")
4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
5 - access("SJ"."LRRQ">=TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss') AND
"SJ"."LRRQ"<TO_DATE('2013-11-30','yyyy-mm-dd hh24:mi:ss'))
-------------------------------------------------------------------------------
Elapsed: 00:00:02.47
Elapsed: 00:00:00.00

复制代码

回复只看该作者道具举报

SKYLINE.LIU

10^#

发表于 2013-12-16 17:48:41

本帖最后由 SKYLINE.LIU 于 2013-12-16 17:53 编辑

Liu Maclean(刘相兵发表于 2013-12-15 19:25
---------------------------------------------------------------------------------------------------- ...

在1楼的列统计信息

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY
T_SJ_SJJBXX BLDWID 575 88460 1 0.00173913043478261
T_SJ_SJJBXX LRRQ 792962 2020 1 1.26109447867615E-6
T_SYS_ORGANIZATION ORGCODE 3517 0 1 0.000284333238555587
T_SYS_ORGANIZATION TREEPATH 3517 0 1 0.000284333238555587

复制代码

作为条件的四个列 TREEPATH,ORGCODE,BLDWID,LRRQ 均没有直方图，所以直方图的影响可以排除。

非常感谢各位专家在百忙之中的解答，这个问题能否解决并不重要，最重要的是向各位专家学习了解决问题的思路~

回复只看该作者道具举报

Maclean Liu(刘相兵

11^#

发表于 2013-12-16 18:11:09

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_59486', task_owner => 'SYS', replace => TRUE);

接受即可

回复只看该作者道具举报

wzhihua

12^#

发表于 2014-8-29 10:42:50

刘大，工作中遇到了类似的问题，通过搜索找到了这个帖子，特来请教。
本例中，当条件 org.treepath like '11000000000000.11010600000000' || '%'换了输入参数值，返回的记录数非常少，比方说只有1条；而sj.lrrq 的时间跨度较大，此时是不是走NESTED LOOPS 较好？？采用了accept_sql_profile固定执行计划能否做到准确的选择NESTED LOOPS 与hash join??

回复只看该作者道具举报

wzhihua

13^#

发表于 2014-8-29 10:50:32

针对本例，我的理解是 org.treepath为长字符串，此时，不论是否通过直方图oracle均不能准确的估算org.treepath like '11000000000000.11010600000000' || '%的返回记录数，但是这个记录数对于选择nested loops,hash join是至关重要的。这样的情况该如处理？ dbms_sqltune.accept_sql_profile能够解决这类问题吗？

回复只看该作者道具举报