Oracle SQL hints and performance

Question

If I execute this

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 );

It gives the following explain plan. If I actually execute the query, it runs forever and I need to stop it from running.

---

| 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 |
----------------------------------------------------------------------------------------------------------------

However, if I provide a hint /* FULL(a) FULL(b) */ in the last select, it provides the below execution plan. If I actually execute, the query goes quite smooth (7-10 minutes).

----------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------

What is the hint doing in this case? Why is there such difference in the explain plans and why are they so wrong? Is there any way I could get more information about how are they actually running.

Answer 1

Your table or index stats are probably not up to date which in turn generates suboptimal execution plan.

Try updating the stats using this:

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