SQL语句执行计划问题请教
本帖最后由 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,orgcodeINDEX_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 呀 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 于 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 补充信息：
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方式，开发一般不会同意。
期待各位专家支招~
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 这个是统计信息时间吧，会不会太旧？ T_SYS_ORGANIZATION 这个表是机构表，95%以上数据是2011年底系统上线时初始化进去的，一年变动的机构不超过20个，而且这个表的数据量很小，数据才3000多 -----------------------------------------------------------------------------------------------------
| 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
并给出结果 假如把该列直方图的信息删掉，执行计划会是什么样子呢? 会走HASH_JOIN??
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 于 2013-12-16 17:53 编辑
Liu Maclean(刘相兵 发表于 2013-12-15 19:25 static/image/common/back.gif
---------------------------------------------------------------------------------------------------- ...
在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 均没有直方图，所以直方图的影响可以排除。
非常感谢各位专家在百忙之中的解答，这个问题能否解决并不重要，最重要的是向各位专家学习了解决问题的思路~ execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_59486', task_owner => 'SYS', replace => TRUE);
接受即可
刘大，工作中遇到了类似的问题，通过搜索找到了这个帖子，特来请教。
本例中，当条件 org.treepath like '11000000000000.11010600000000' || '%'换了输入参数值，返回的记录数非常少，比方说只有1条；而sj.lrrq 的时间跨度较大，此时是不是走NESTED LOOPS 较好？？采用了accept_sql_profile固定执行计划能否做到准确的选择NESTED LOOPS 与hash join??
针对本例，我的理解是 org.treepath为长字符串，此时，不论是否通过直方图oracle均不能准确的估算org.treepath like '11000000000000.11010600000000' || '%的返回记录数，但是这个记录数对于选择nested loops,hash join是至关重要的。这样的情况该如处理？ dbms_sqltune.accept_sql_profile能够解决这类问题吗？ dbms_sqltune.accept_sql_profile 这个是固定执行计划的，不能解决
'11000000000000.11010600000000' || '% ---这种只关心前面的，是否可以考虑函数索引 函数索引？建立什么样的函数索引能较准确的估算返回的记录数？
页:
[1]