sql - Oracle SQL 提示和性能

Question

如果我执行这个

EXPLAIN PLAN
    FOR
WITH locks_02vw AS (
    SELECT DISTINCT
        loobj1,
        lockr
    FROM
        cdc.uap_dfkklocks
    WHERE
        lotyp = '02'
        AND proid = '01'
        AND lockr IN (
            'V',
            'W',
            '@',
            '&'
        )
        AND tdate >= (
            SELECT
                to_char(sysdate, 'YYYYMMDD')
            FROM
                dual
        )
),

-- Fetch the open debt
 aux_dfkkop AS (
    SELECT
        opbel
        || opupw
        || opupk
        || opupz AS doc_no,
        substr(vtref, 11, 10) AS contract,
        vkont   AS ca,
        gpart   AS bp,
        betrw   AS amount,
        CASE
            WHEN kofiz IN (
                '01',
                '03'
            ) THEN
                'DOM'
            WHEN kofiz IN (
                '02',
                '04'
            ) THEN
                'SME'
            ELSE
                'Other'
        END AS type,
        CASE
            WHEN spart = '01' THEN
                'Elec'
            WHEN spart = '02' THEN
                'Gas'
            ELSE
                'Other'
        END AS division,
        hvorg   AS main,
        tvorg   AS sub,
        bldat   AS doc_date,
        faedn   AS due_date,
        stakz   AS statistical
    FROM
        cdc.uap_dfkkop
    WHERE
        augst IS NULL
        AND faedn <= (
            SELECT
                to_char(sysdate, 'YYYYMMDD')
            FROM
                dual
        )
)
SELECT
    b.*,
    a.lockr
FROM
    locks_02vw   a
    JOIN aux_dfkkop   b ON b.doc_no = a.loobj1;

SELECT
    *
FROM
    TABLE ( dbms_xplan.display );

它给出了以下解释计划。如果我真的执行查询，它会永远运行，我需要阻止它运行。

---

| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |   374 | 83776 |    13M  (1)| 00:08:57 |
|*  1 |  HASH JOIN                             |                       |   374 | 83776 |    13M  (1)| 00:08:57 |
|   2 |   VIEW                                 |                       |   113 |  4181 |   840   (1)| 00:00:01 |
|   3 |    HASH UNIQUE                         |                       |   113 |  8475 |   840   (1)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| UAP_DFKKLOCKS         |   621 | 46575 |   839   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | UAP_DFKKLOCKS_TDATE_1 |  3144 |       |    18   (0)| 00:00:01 |
|   6 |       FAST DUAL                        |                       |     1 |       |     2   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID BATCHED  | UAP_DFKKOP            |    51M|  9240M|    13M  (1)| 00:08:57 |
|   8 |    BITMAP CONVERSION TO ROWIDS         |                       |       |       |            |          |
|*  9 |     BITMAP INDEX SINGLE VALUE          | UAP_DFKKOP_AUGST_2    |       |       |            |          |
|  10 |    FAST DUAL                           |                       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

但是，如果我在最后一次选择中提供提示 /* FULL(a) FULL(b) */，它会提供以下执行计划。如果我真的执行，查询会非常顺利（7-10 分钟）。

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   374 | 83776 |    34M  (1)| 00:22:25 |
|*  1 |  HASH JOIN                   |               |   374 | 83776 |    34M  (1)| 00:22:25 |
|   2 |   VIEW                       |               |   113 |  4181 |   242K  (1)| 00:00:10 |
|   3 |    HASH UNIQUE               |               |   113 |  8475 |   242K  (1)| 00:00:10 |
|*  4 |     TABLE ACCESS STORAGE FULL| UAP_DFKKLOCKS |   621 | 46575 |   242K  (1)| 00:00:10 |
|   5 |      FAST DUAL               |               |     1 |       |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS STORAGE FULL  | UAP_DFKKOP    |    51M|  9240M|    34M  (1)| 00:22:15 |
|   7 |    FAST DUAL                 |               |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

在这种情况下，提示在做什么？为什么解释计划存在如此大的差异，为什么它们如此错误？有什么方法可以让我获得有关它们实际运行情况的更多信息。

标签： sqloracle

Answer 1

您的表或索引统计信息可能不是最新的，这反过来又会生成次优的执行计划。

尝试使用以下方法更新统计信息：

EXEC DBMS_STATS.gather_table_stats('cdc', 'uap_dfkklocks');
EXEC DBMS_STATS.gather_table_stats('cdc', 'uap_dfkkop');