Hi All,
We are having an issue with following query. I have edited the original query as i can't preset the query in original form. The issue is oracle unable to take advantage of the Distinct Displacement.
In below query all the tables are very small (more or less than 50k rows) except the table called "BIG_TABLE" which has nearly 41 million rows. However, we have only few distinct values for column "OP_ID" . When we run this query oracle not using the Distinct Displacement, i.e. it's doing joining and eliminating the duplicates. This query is taking more than 1 hr to complete.
SELECT DISTINCT
E2.KEY_ID,
Table__135.Key,
Table__135.create_ts,
Table__135.id
FROM
BIG_TABLE E2,
SMALL_TABLE1 E22,
SMALL_TABLE12 E,
SMALL_TABLE3 GE2,
( select A1.key,
A1.create_ts,
A1.id
from SMALL_TABLE4 A1
where A1.create_ts = ( select max(A2.create_ts)
from SMALL_TABLE4 A2
where A2.key = A1.key
)
) Table__135
WHERE E.cd=E22.cd
AND E.key=Table__135.key(+)
AND E.CL_ID= E2.OP_ID
AND E.key= GE2.key ;
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2423M| 489G| | 114M (1)|380:58:02 |
| 1 | HASH UNIQUE | | 2423M| 489G| 528G| 114M (1)|380:58:02 |
|* 2 | HASH JOIN | | 2423M| 489G| 5080K| 85713 (18)| 00:17:09 |
|* 3 | HASH JOIN RIGHT OUTER | | 23855 | 4798K| | 7105 (3)| 00:01:26 |
| 4 | VIEW | | 13 | 1300 | | 6452 (3)| 00:01:18 |
|* 5 | FILTER | | | | | | |
| 6 | HASH GROUP BY | | 13 | 1664 | | 6452 (3)| 00:01:18 |
|* 7 | HASH JOIN | | 1998K| 243M| 9136K| 6340 (1)| 00:01:17 |
| 8 | TABLE ACCESS FULL| SMALL_TABLE4 | 334K| 5220K| | 1961 (1)| 00:00:24 |
| 9 | TABLE ACCESS FULL| SMALL_TABLE4 | 334K| 35M| | 1961 (1)| 00:00:24 |
|* 10 | HASH JOIN | | 23855 | 2469K| | 652 (1)| 00:00:08 |
|* 11 | HASH JOIN | | 14862 | 1393K| | 525 (1)| 00:00:07 |
| 12 | TABLE ACCESS FULL | SMALL_TABLE1 | 7 | 147 | | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SMALL_TABLE12 | 14862 | 1088K| | 522 (1)| 00:00:07 |
| 14 | INDEX FAST FULL SCAN| PK_SMALL_TABLE3 | 96242 | 939K| | 127 (1)| 00:00:02 |
| 15 | INDEX FAST FULL SCAN | BIG_TABLE_IDXX03 | 41M| 435M| | 18698 (2)| 00:03:45 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."CL_ID"="E2"."OP_ID")
3 - access("E"."key"="TABLE__135"."key"(+))
5 - filter("A1"."CREATE_TS"=MAX("A2"."CREATE_TS"))
7 - access("A2"."key"="A1"."key")
10 - access("E"."key"="GE2"."key")
11 - access("E"."cd"="E22"."cd")
When I re-write the query to as follows, still it's merging the inline query and using the same above plan.
SELECT
DISTINCT
E2.KEY_ID,
Table__135.Key,
Table__135.create_ts,
Table__135.id
FROM
(select distinct OP_ID,KEY_ID from BIG_TABLE) E2,
SMALL_TABLE1 E22,
SMALL_TABLE12 E,
SMALL_TABLE3 GE2,
( select A1.key,
A1.create_ts,
A1.id
from SMALL_TABLE4 A1
where A1.create_ts = ( select max(A2.create_ts)
from SMALL_TABLE4 A2
where A2.key = A1.key
)
) Table__135
WHERE E.cd=E22.cd
AND E.key=Table__135.key(+)
AND E.CL_ID= E2.OP_ID
AND E.key= GE2.key ;
When i added a hint to not to merge the inline query then oracle doing the Distinct displacement for E2 and query completing in less than a minute.
SELECT /*+ no_merge(E2) */
DISTINCT
E2.KEY_ID,
Table__135.Key,
Table__135.create_ts,
Table__135.id
FROM
(select distinct OP_ID,KEY_ID from BIG_TABLE) E2,
SMALL_TABLE1 E22,
SMALL_TABLE12 E,
SMALL_TABLE3 GE2,
( select A1.key,
A1.create_ts,
A1.id
from SMALL_TABLE4 A1
where A1.create_ts = ( select max(A2.create_ts)
from SMALL_TABLE4 A2
where A2.key = A1.key
)
) Table__135
WHERE E.cd=E22.cd
AND E.key=Table__135.key(+)
AND E.CL_ID= E2.OP_ID
AND E.key= GE2.key ;
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 699K| 144M| | 61529 (6)| 00:12:19 |
| 1 | HASH UNIQUE | | 699K| 144M| 156M| 61529 (6)| 00:12:19 |
|* 2 | HASH JOIN | | 699K| 144M| | 28590 (12)| 00:05:44 |
| 3 | VIEW | | 11992 | 128K| | 21481 (15)| 00:04:18 |
| 4 | HASH UNIQUE | | 11992 | 128K| | 21481 (15)| 00:04:18 |
| 5 | INDEX FAST FULL SCAN| BIG_TABLE_IDXX03 | 41M| 435M| | 18698 (2)| 00:03:45 |
|* 6 | HASH JOIN RIGHT OUTER | | 23855 | 4798K| | 7105 (3)| 00:01:26 |
| 7 | VIEW | | 13 | 1300 | | 6452 (3)| 00:01:18 |
|* 8 | FILTER | | | | | | |
| 9 | HASH GROUP BY | | 13 | 1664 | | 6452 (3)| 00:01:18 |
|* 10 | HASH JOIN | | 1998K| 243M| 9136K| 6340 (1)| 00:01:17 |
| 11 | TABLE ACCESS FULL| SMALL_TABLE4 | 334K| 5220K| | 1961 (1)| 00:00:24 |
| 12 | TABLE ACCESS FULL| SMALL_TABLE4 | 334K| 35M| | 1961 (1)| 00:00:24 |
|* 13 | HASH JOIN | | 23855 | 2469K| | 652 (1)| 00:00:08 |
|* 14 | HASH JOIN | | 14862 | 1393K| | 525 (1)| 00:00:07 |
| 15 | TABLE ACCESS FULL | SMALL_TABLE1 | 7 | 147 | | 3 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | SMALL_TABLE12 | 14862 | 1088K| | 522 (1)| 00:00:07 |
| 17 | INDEX FAST FULL SCAN| PK_SMALL_TABLE3 | 96242 | 939K| | 127 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."CL_ID"="E2"."OP_ID")
6 - access("E"."key"="TABLE__135"."key"(+))
8 - filter("A1"."CREATE_TS"=MAX("CREATE_TS"))
10 - access("E"."key"="GE2"."key")
13 - access("E"."key"="GE2"."key")
14 - access("E"."cd"="E22"."cd")
Stats looks good and it even indicates that it has only few distinct keys. However oracle is unable to take advantage of it without using hints and modifying the query.
SQL> select NUM_ROWS, DISTINCT_KEYS, LEAF_BLOCKS, BLEVEL from dba_indexes where INDEX_NAME='BIG_TABLE_IDXX03' ;
NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS BLEVEL
---------- ------------- ----------- ----------
41555156 11992 67847 2
SQL> select COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, NUM_NULLS, HISTOGRAM, LAST_ANALYZED from dba_tab_col_statistics where owner='CGSOWNER2' and table_name='CLIENT_BILLING_HIERARCHY_E' and column_name in ('OP_ID','KEY_ID') ;
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS HISTOGRAM LAST_ANALZED
---------------------------------------------------------------- ---------- --------------- ---------
OP_ID 11744 30303131 5A5A5555 0 HEIGHT BALANCED 08-SEP-16
KEY_ID 6190 3130303131 4631344F47 0 HEIGHT BALANCED 08-SEP-16
However it's not possible to add hint to the sql in the application. We can re-write the query but without hint. For some reason app doesn't allow hints.
Can any one please advise how to solve this issue without adding hint ?