Hi all,
I have this issue i have been trying to fix for over a week but need some help from you guys.
I have this spatial query:
SELECT brh.nitg_nr,
sdo_util.to_wktgeometry(loc.geometry) geometry
FROM
DINO_DBA.BRH_BOREHOLE_TBL brh
JOIN DINO_DBA.LOC_SURFACE_LOCATION_RD_G loc
ON
brh.SURFACE_LOCATION_DBK = loc.SURFACE_LOCATION_DBK
WHERE
sdo_inside(loc.geometry,
sdo_geom.sdo_arc_densify(sdo_geom.sdo_buffer(sdo_cs.transform(sdo_geometry('POINT (141893.43775605165865272 478160.01169336074963212)',
28992),
28992),
1.0,
0.001,
'arc_tolerance=0.05 unit=km'),
0.001,
'arc_tolerance=0.05 unit=km')) = 'TRUE'
;
This query works fine for 1 user and finishes within 1 sec.
explain plan for that user shows indexes are being used without a problem:
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 894 | 41124 | 2660 (1)| 00:00:32 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 894 | 41124 | 2660 (1)| 00:00:32 |
| 3 | TABLE ACCESS BY INDEX ROWID | LOC_SURFACE_LOCATION_RD_G | 894 | 23244 | 184 (1)| 00:00:03 |
|* 4 | DOMAIN INDEX (SEL: 0.100000 %)| LOC_SLC_RD_IDX | | | 14 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | BRH_BOREHOLE_TBL | 1 | 20 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | BRH_BOREHOLE_TBL_IDX1 | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
for the other users the query finishes after 15 minutes with this explain plan. Index are not used this time:
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 685K| 64M| | 6416 (5)| 00:01:17 |
|* 1 | VIEW | | 685K| 64M| | 6416 (5)| 00:01:17 |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 685K| 30M| 20M| 6416 (5)| 00:01:17 |
|* 4 | TABLE ACCESS FULL| BRH_BOREHOLE_TBL | 686K| 13M| | 2799 (9)| 00:00:34 |
| 5 | TABLE ACCESS FULL| LOC_SURFACE_LOCATION_RD_G | 893K| 22M| | 927 (5)| 00:00:12 |
What have i done so far:
I refreshed the statistics → but if this would be a problem i would expect this to be a problem for all users. But that's not the case.
checked if both users have same priviliges → i assume if it's a priviliges issue that query will fail but it works fine for both users only one is using indexes and other one is not. as far as i know you can't grant specific priviliges on index so if they both have select privs on the table then index privs are the same. Even if grant the problem user DBA role still query wont use indexes. note: 1 of the indexes is a domein index, does it need additional priviliges?
checked optimizer mode: ALL_ROWS and optimizer_index_cost_adj = 100.
I tried using hints but they are being ignored by the optimizer:
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
5 - SEL$F5BB74E1 / LOC@SEL$1
U - INDEX(loc, LOC_SLC_RD_IDX)
So same database, schema, query and still different plans per user.
I hope you guys have any idea.
Thanks!
Mina