返回小栈
《微课sql优化》第四期、帮助“表姐”优化报表
orastar2020-03-23 14:19:07

1、"表姐"的报表SQL问题



今天"表姐"有个比较紧急的工作,有一张历史费用报表下班前必须查询出来,已经倒腾2个小时了,查询并发数已经加到40个,报表还是查询不出来,而且这个报表每月都会折磨表姐,眼看就要下班了,时间紧近,紧急求助。
经常被单位各位"表姐"骚扰,而且还特别紧急。。。。DBA的苦,好吧,放下手头工作,先帮"表姐"处理问题。。。。

2、问题分析



DBA: 
你的SQL发我看看?

表姐
select  /*+ parallel(T 40)*/* FROM ht.ht_tab_fy T
WHERE EXISTS (SELECT 1 FROM ht2.ht_DEPT M WHERE T.DEPT_ID = M.DEPT_ID AND M.DEPT_TREE LIKE '%611002%')
AND T.DATA_MONTH = '201709' ;
DBA
这个查询结果发我看看?

explain plan for 
select * FROM ht.ht_tab_fy T
WHERE EXISTS (SELECT 1 FROM ht2.ht_DEPT M WHERE T.DEPT_ID = M.DEPT_ID AND M.DEPT_TREE LIKE '%611002%')
AND T.DATA_MONTH = '201709' ;
select * from table(dbms_xplan.display());
表姐: 
-------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   | 25466 |  3531K| 80186   (2)|
|   1 |  HASH JOIN RIGHT SEMI|                   | 25466 |  3531K| 80186   (2)|
|   2 |   TABLE ACCESS FULL  | ht_DEPT          |    58 |  2494 |     8   (0)|
|   3 |   TABLE ACCESS FULL  | ht_tab_fy |   118K|    11M| 80176   (2)|
-------------------------------------------------------------------------------
DBA
这两个查询结果给我

select version from v$instance;
select name,value from v$parameter where name like '%multiblock%';

SQL> select name,value from v$parameter where name like '%multiblock%';
SQL> select version from v$instance;

VERSION
-----------------
10.2.0.5.0

NAME    VALUE
-------------------------------------------------- --------------------------------------------------
db_file_multiblock_read_count    16


3、优化方案




DBA
从新收集下统计信息:
exec dbms_stats.gather_table_stats(ownname=>'ht2',tabname=>'ht_DEPT',estimate_percent=>100,method_opt=>'for all columns size repeat',no_invalidate=>false,degree=>8,cascade=>true);
exec dbms_stats.gather_table_stats(ownname=>'ht',tabname=>'ht_tab_fy',estimate_percent=>100,method_opt=>'for all columns size repeat',no_invalidate=>false,degree=>8,cascade=>true);


然后再执行你的语句:
alter session set db_file_multiblock_read_count=128;  --前面加一条
select * FROM ht.ht_tab_fy T
WHERE EXISTS (SELECT 1 FROM ht2.ht_DEPT M WHERE T.DEPT_ID = M.DEPT_ID AND M.DEPT_TREE LIKE '%611002%')
AND T.DATA_MONTH = '201709' ;





0
0