SQL语句调优 + 列直方图的理解&对执行计划的影响

最新推荐文章于 2024-03-11 15:24:16 发布

richieruan

最新推荐文章于 2024-03-11 15:24:16 发布

阅读量500

点赞数

分类专栏：技术总结个人经验总结数据库

本文链接：https://blog.csdn.net/richieruan/article/details/91938492

版权

本文介绍了SQL语句调优的一个实际案例，强调了执行计划和统计信息准确性的重要性。直方图在Oracle数据库中用于记录数据分布，帮助优化器做出更好的查询规划。直方图分为HEIGHT BALANCED和FREQUENCY两种类型，适用于数据偏斜的情况，以改善优化器的决策。文章还探讨了何时应该使用直方图以及如何通过DBMS_STATS包收集直方图统计信息。

摘要由CSDN通过智能技术生成

Part 1：SQL语句调优

背景说明:2019年5月申请Risk岗位，部门manager及技术manger提出的优化问题以考察技术知识。

代码：

--LOXON/MUCH - buffer for assignment engine
SELECT dass.skp_client,
       dass.skp_contract,
       dass.skp_credit_case,
       dass.skp_credit_type,
       dass.date_decision,
       CASE
           WHEN cmp.skp_milestone IN (31, 47)
                AND dass.skp_late_collection_type = 3 THEN
            'SF01' -- settlement for field
           WHEN cmp.skp_milestone IN (31, 47)
                AND dass.skp_late_collection_type = 4 THEN
            'SEXA' -- settlement for exa
           ELSE
            decode(dass.skp_late_collection_type, 2, 'EF', 3, 'LF', 4, 'EXA', 'XX') || CASE
                WHEN dass.skp_late_collection_type = 4 THEN
                 ''
                WHEN cmp.skp_camp_task_type IN (365, 366)
                     AND dass.num_dpd_assign <= 30 THEN
                 '00' -- super early cases
                WHEN cmp.skp_camp_task_type = 366 THEN
                 '01' -- early 1st round
                WHEN cmp.skp_camp_task_type = 365 THEN
                 '02' -- early 2nd or more round
                WHEN cmp.skp_camp_task_type = 253 THEN
                 '01' || decode(dass.num_visits_prev_assign, 0, '1', '2') -- late 1st round after termination
                WHEN cmp.skp_camp_task_type = 252 THEN
                 '02' -- late 2nd or more round
                WHEN cmp.skp_camp_task_type = 133 THEN
                 'PR' -- prolongation
                ELSE
                 'XX' -- something is wrong
            END
       END AS code_recovery_method,
       ctt.name_camp_task_type,
       cmp.skp_camp_task_type,
       cmp.date_registration,
       dass.date_creation,
       dass.num_visits_prev_assign,
       dass.num_dpd_assign,
       dass.text_collector_username_prev,
       trunc(SYSDATE - cmp.dtime_registration) AS days_in_status,
       dass.code_assignment_status,
       cmp.skp_milestone,
       dass.skp_late_collection_type,
       dass.id_source,
       dcc.id_cuid
FROM   owner_dwh.dc_collection_assignment dass
JOIN   owner_dwh.dc_client dcc ON dcc.skp_client = dass.skp_client
JOIN   owner_dwh.f_camp_task_ad cmp ON cmp.skf_camp_task = dass.skf_camp_task
                                AND    cmp.date_registration BETWEEN dass.date_creation - 1 AND dass.date_creation
JOIN   owner_dwh.cl_camp_task_type ctt ON ctt.skp_camp_task_type = cmp.skp_camp_task_type
--join owner_dwh.dc_milestone ms on ms.skp_milestone = cmp.skp_milestone
WHERE  1 = 1
AND    dass.date_creation >= trunc(SYSDATE) - 20
AND    dass.date_creation < SYSDATE
AND    dass.date_assigned = DATE '1000-01-01'
AND    dass.num_assignment IS NULL
      --and dass.skp_late_collection_type in (2,3,4)
AND    dass.code_source_system = 'LCS'
AND    dass.flag_deleted = 'N'
AND    dass.code_status = 'XNA'
AND    dass.code_assignment_status = 'exported'
      --
AND    cmp.flag_deleted = 'N';

第一次修改结果

1. 关联字段没有关联索引键 skp_client, 因此，在条件中加入该条件

2. 字段未使用分区键，因此加入分区键以做或调整分区键

Back to the query performance, there are two apparent point impacting the performance:
1.	After checking the exec plan, I found the table full scan of owner_dwh.f_camp_task_ad should be the major bottleneck for the script which took too much temp space to query the useless data from fact table.
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |     1 |   185 |  8572K  (5)| 05:07:38 |       |       |
|*  1 |  FILTER                                 |                           |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                          |                           |     1 |   185 |  8572K  (5)| 05:07:38 |       |       |
|   3 |    NESTED LOOPS                         |                           |     1 |   185 |  8572K  (5)| 05:07:38 |       |       |
|*  4 |     HASH JOIN                           |                           |     1 |   150 |  8572K  (5)| 05:07:38 |       |       |
|   5 |      NESTED LOOPS                       |                           |     1 |   117 |   170K  (2)| 00:06:07 |       |       |
|   6 |       NESTED LOOPS                      |                           |     1 |   117 |   170K  (2)| 00:06:07 |       |       |
|   7 |        PARTITION RANGE ALL              |                           |     1 |   104 |   170K  (2)| 00:06:07 |     1 |   365 |
|*  8 |         TABLE ACCESS FULL               | DCT_COLLECTION_ASSIGNMENT |     1 |   104 |   170K  (2)| 00:06:07 |     1 |   365 |
|*  9 |        INDEX UNIQUE SCAN                | PK_CLIENT                 |     1 |       |     1   (0)| 00:00:01 |       |       |
|  10 |       TABLE ACCESS BY GLOBAL INDEX ROWID| DCT_CLIENT                |     1 |    13 |     2   (0)| 00:00:01 | ROWID | ROWID |
|  11 |      PARTITION RANGE ITERATOR           |                           |   646M|    19G|  8390K  (5)| 05:01:05 |     1 |   KEY |
|* 12 |       TABLE ACCESS FULL                 | FT_CAMP_TASK_AD           |   646M|    19G|  8390K  (5)| 05:01:05 |     1 |   KEY |
|* 13 |     INDEX UNIQUE SCAN                   | PK_CCAMPTSKT              |     1 |       |     0   (0)| 00:00:01 |       |       |
|  14 |    TABLE ACCESS BY INDEX ROWID          | CLT_CAMP_TASK_TYPE        |     1 |    35 |     1   (0)| 00:00:01 |       |       |
So when I back to the script, I found the 
„join owner_dwh.dc_client dcc on dcc.skp_client = dass.skp_client “ is needless except one column from the dc_client was in the result 
But the skp_client also existed in the owner_dwh.f_camp_task_ad. Thus 

I change the script to 
“JOIN   owner_dwh.f_camp_task_ad cmp ON cmp.skf_camp_task = dass.skf_camp_task
                                AND    cmp.skp_client = dass.skp_client
                                AND    cmp.date_registration BETWEEN dass.date_creation - 1 AND dass.date_creation”

And the exec time shor to 0.03s and exec plan change to:
-------------------------------------------------------------