Reputation: 824
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.
Upvotes: 1
Views: 171
Reputation: 1000
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');
Upvotes: 4