Hi , i am using Hi I am using version 11.2.0.4.0 of oracle. we have a search query which does FTS on two tables, below is the runtime execution for same. Currently its taking ~3 minutes and we need its to be executed in few seconds. i am seeing the line number 7 and 8 consumes much time.
So what are the options we should try to optimize this? should we think of caching the results fr A and B as those seems to take considerable amount of time? or is it that user IO needs to be improved?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 39190 |00:02:30.82 | 1012K| 652K| | | |
| 1 | NESTED LOOPS OUTER | | 1 | 1 | 39190 |00:02:30.82 | 1012K| 652K| | | |
| 2 | NESTED LOOPS OUTER | | 1 | 1 | 39190 |00:02:30.32 | 955K| 652K| | | |
|* 3 | HASH JOIN RIGHT SEMI | | 1 | 1 | 34766 |00:01:43.74 | 872K| 623K| 3091K| 2748K| 2497K (0)|
| 4 | VIEW | VW_NSO_1 | 1 | 7364 | 34587 |00:01:28.64 | 618K| 612K| | | |
| 5 | SORT GROUP BY | | 1 | 7364 | 34587 |00:01:28.62 | 618K| 612K| 3313K| 858K| 2944K (0)|
|* 6 | HASH JOIN | | 1 | 7364 | 42284 |00:01:28.46 | 618K| 612K| 224M| 11M| 255M (0)|
| 7 | TABLE ACCESS FULL | B | 1 | 2945K| 2946K|00:00:12.52 | 87631 | 81721 | | | |
| 8 | TABLE ACCESS FULL | A | 1 | 15M| 15M|00:00:51.90 | 531K| 531K| | | |
| 9 | NESTED LOOPS OUTER | | 1 | 420K| 54446 |00:00:14.89 | 253K| 10465 | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 420K| 47875 |00:00:01.11 | 52255 | 15 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | B | 1 | 420K| 47875 |00:00:00.39 | 24897 | 0 | | | |
|* 12 | INDEX RANGE SCAN | B_IX4 | 1 | 420K| 47875 |00:00:00.08 | 2723 | 0 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | C | 47875 | 1 | 38765 |00:00:00.64 | 27358 | 15 | | | |
|* 14 | INDEX RANGE SCAN | C_IX1 | 47875 | 1 | 39375 |00:00:00.25 | 17675 | 5 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | A | 47875 | 1 | 42223 |00:00:13.69 | 201K| 10450 | | | |
|* 16 | INDEX RANGE SCAN | A_IX1 | 47875 | 1 | 63413 |00:00:10.97 | 141K| 5870 | | | |
| 17 | PARTITION RANGE ITERATOR | | 34766 | 1 | 20001 |00:00:46.51 | 83144 | 28962 | | | |
|* 18 | TABLE ACCESS BY LOCAL INDEX ROWID| D | 34766 | 1 | 20001 |00:00:46.39 | 83144 | 28962 | | | |
|* 19 | INDEX RANGE SCAN | D_IX2 | 34766 | 53 | 50283 |00:00:12.27 | 37407 | 7423 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | E | 39190 | 1 | 39190 |00:00:00.44 | 57666 | 19 | | | |
|* 21 | INDEX UNIQUE SCAN | E_PK | 39190 | 1 | 39190 |00:00:00.24 | 18476 | 5 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
below is the section of code for which the table A and B went for FTS
AND ID IN ( SELECT MAX (A_ID)
FROM A
where exists(select 1 from B
where B.rate_id = A.rate_id
AND B.AMT =
A.AMT
AND B.m_code =
A.m_code
AND B.cre_dt BETWEEN A.EVENT_DATE
AND A.EVENT_DATE
+ 10)
GROUP BY A.rate_id, A.AMT, A.m_code);