im using below query using hint rsult_cache but its not using.there is view in this query.
SELECT * FROM (SELECT /*+ result_cache */ ObjectId_194 ObjectId, TypeId_194 TypeId, field_356, field_388, TypeId_388, field_388_SD, field_622, TypeId_622, field_622_SD, field_824, field_1230, field_352, field_169, field_1136, field_205, field_2678, field_1430, field_3086, TypeId_3086, field_3086_SD, field_852, field_2679, TypeId_2679, field_2679_SD, field_697, field_919, field_580, field_2932, field_1538, field_1609,row_number() over (order by DateModified desc) rn FROM lecorpio.Raa_tt_194
WHERE ( UPPER(field_356) LIKE '%P12342US1%'
OR UPPER(field_388_SD) LIKE '%P12342US1%'
OR UPPER(field_622_SD) LIKE '%P12342US1%'
OR UPPER(field_824) LIKE '%P12342US1%'
OR UPPER(field_1230) LIKE '%P12342US1%'
OR UPPER(field_352) LIKE '%P12342US1%'
OR UPPER(field_205) LIKE '%P12342US1%'
OR UPPER(field_3086_SD) LIKE '%P12342US1%'
OR UPPER(field_852) LIKE '%P12342US1%'
OR UPPER(field_2679_SD) LIKE '%P12342US1%'
OR UPPER(field_697) LIKE '%P12342US1%'
OR UPPER(field_919) LIKE '%P12342US1%'
OR UPPER(field_2932) LIKE '%P12342US1%'
OR UPPER(field_1609) LIKE '%P12342US1%' ) )WHERE rn >= 1 and rn <= 100;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 1258304K
result_cache_mode string AUTO
result_cache_remote_expiration integer 0
Execution Plan
----------------------------------------------------------
Plan hash value: 1562701590
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67696 | 66M| | 30811 (1)| 00:06:10 | | |
|* 1 | VIEW | | 67696 | 66M| | 30811 (1)| 00:06:10 | | |
|* 2 | WINDOW SORT PUSHED RANK | | 67696 | 51M| 52M| 30811 (1)| 00:06:10 | | |
|* 3 | HASH JOIN RIGHT OUTER | | 67696 | 51M| | 19542 (1)| 00:03:55 | | |
| 4 | VIEW | RGO_ET_26 | 4985 | 99700 | | 14 (0)| 00:00:01 | | |
| 5 | UNION-ALL | | | | | | | | |
| 6 | INDEX FAST FULL SCAN | RGO_TT_103_PK | 507 | 18759 | | 3 (0)| 00:00:01 | | |
| 7 | INDEX FULL SCAN | RGO_TT_226_PK | 1 | 20 | | 0 (0)| 00:00:01 | | |
| 8 | INDEX FAST FULL SCAN | RGO_TT_8_PK | 3330 | 120K| | 7 (0)| 00:00:01 | | |
| 9 | INDEX FAST FULL SCAN | RGO_TT_34_PK | 1146 | 42402 | | 3 (0)| 00:00:01 | | |
| 10 | INDEX FULL SCAN | RGO_TT_49_PK | 1 | 37 | | 1 (0)| 00:00:01 | | |
|* 11 | FILTER | | | | | | | | |
|* 12 | HASH JOIN RIGHT OUTER | | 67696 | 49M| | 19528 (1)| 00:03:55 | | |
| 13 | TABLE ACCESS FULL | RGO_TT_103 | 507 | 50700 | | 8 (0)| 00:00:01 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 67696 | 43M| | 19520 (1)| 00:03:55 | | |
| 15 | TABLE ACCESS FULL | RGO_TT_256 | 54 | 5238 | | 3 (0)| 00:00:01 | | |
|* 16 | HASH JOIN OUTER | | 67696 | 37M| 31M| 19516 (1)| 00:03:55 | | |
|* 17 | HASH JOIN RIGHT OUTER| | 67696 | 30M| 6416K| 8602 (1)| 00:01:44 | | |
| 18 | PARTITION RANGE ALL | | 67696 | 5619K| | 3495 (1)| 00:00:42 | 1 | 43 |
| 19 | TABLE ACCESS FULL | RGO_TT_194 | 67696 | 5619K| | 3495 (1)| 00:00:42 | 1 | 43 |
| 20 | PARTITION RANGE ALL | | 67696 | 25M| | 3504 (1)| 00:00:43 | 1 | 43 |
| 21 | TABLE ACCESS FULL | RGO_TT_194 | 67696 | 25M| | 3504 (1)| 00:00:43 | 1 | 43 |
| 22 | TABLE ACCESS FULL | RGO_TT_38 | 399K| 37M| | 7270 (1)| 00:01:28 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1 AND "RN"<=100)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("RGO_TT_194"."DATEMODIFIED") DESC )<=100)
3 - access("ALIASFIELD_807"."OBJECTID_26"(+)="RGO_TT_194"."FIELD_807")
11 - filter(UPPER("RGO_TT_194"."FIELD_356") LIKE '%P12342US1%' OR UPPER("ALIASFIELD_388"."FIELD_1288") LIKE
'%P12342US1%' OR UPPER("ALIASFIELD_622"."FIELD_905") LIKE '%P12342US1%' OR UPPER("RGO_TT_194"."FIELD_824")
LIKE '%P12342US1%' OR UPPER("RGO_TT_194"."FIELD_1230") LIKE '%P12342US1%' OR UPPER("RGO_TT_194"."FIELD_352")
LIKE '%P12342US1%' OR UPPER("RGO_TT_194"."FIELD_205") LIKE '%P12342US1%' OR
UPPER("ALIASFIELD_3086"."FIELD_178") LIKE '%P12342US1%' OR UPPER("RGO_TT_194"."FIELD_852") LIKE '%P12342US1%'
OR UPPER("ALIASFIELD_2679"."FIELD_356") LIKE '%P12342US1%' OR UPPER("FIELD_697") LIKE '%P12342US1%' OR
UPPER("RGO_TT_194"."FIELD_919") LIKE '%P12342US1%' OR UPPER("RGO_TT_194"."FIELD_2932") LIKE '%P12342US1%' OR
UPPER("FIELD_1609") LIKE '%P12342US1%')
12 - access("ALIASFIELD_388"."OBJECTID_103"(+)="RGO_TT_194"."FIELD_388")
14 - access("ALIASFIELD_3086"."OBJECTID_256"(+)="RGO_TT_194"."FIELD_3086")
16 - access("ALIASFIELD_622"."OBJECTID_38"(+)="RGO_TT_194"."FIELD_622")
17 - access("ALIASFIELD_2679"."OBJECTID_194"(+)="RGO_TT_194"."FIELD_2679")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
Statistics
----------------------------------------------------------
145 recursive calls
4 db block gets
63300 consistent gets
0 physical reads
0 redo size
3186 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed