11.2.0.3
Solaris 10
I need some help with our access-restriction policy implementation. Based on our execution plans and trace files, we see our policy function execute when querying access restricted tables. This, of course, is expected and is done as a hash join against the results returned by the query. The function populates a set of id's based on what roles the user has and pushes those predicates against object_1.
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5331 | 786K| 1358 (21)| 00:00:03 |
|* 1 | HASH JOIN RIGHT SEMI | | 5331 | 786K| 1358 (21)| 00:00:03 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| GET_PERMISSIONS | 16360 | 32720 | 20 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | OBJECT_1 | 634K| 90M| 1302 (19)| 00:00:03 |
-------------------------------------------------------------------------------------------------------
The problem we're having is with reports where the optimizer finds it is more efficient to perform a NESTED LOOP between one object and the access restricted object. The function is then called as many times as the nested loop operation runs. Where a report might take a couple seconds, it will take minutes to complete because the function is called thousands of times. These reports are very long and complex but below is a sample where I forced the nested loop (query takes too long without additional predicate).
SELECT /*+ USE_NL(obj1 obj2)*/
ob1.id
FROM object_1 ob1, object_2 ob2
WHERE ob1.obj2_id = ob2.id(+)AND obj1.obj_name IN ('SOME_VALUE');
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3620 | 190K| 137 (2)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3620 | 190K| 137 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | OBJECT_1 | 3620 | 123K| 135 (1)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | OBJECT1_FK_I | 3620 | | 22 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | OBJECT_2 | 1 | 19 | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS SEMI | | 1 | 19 | 21 (10)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | OBJECT_2 | 1 | 17 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | OBJECT_2_PK | 1 | | 1 (0)| 00:00:01 |
|* 8 | COLLECTION ITERATOR PICKLER FETCH| GET_PERMISSIONS | 137 | 274 | 20 (10)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
We have some reports where the function is called over 100,000 times and can take an additional 12 minutes to complete. I am not the developer of these reports or our policies but I am doing my best to help them tune it. I can force them to use hash joins, where possible, and the query time is excellent. There are, unfortunately, thousands of these reports with varying degrees of performance degradation because of this function and developers are wondering if there are other alternatives. Is there a way to tell the optimizer to use hash joins when dealing with this funciton? I've looked at using different policy types (currently "SHARED_CONTEXT_SENSITIVE") but neither have helped improve performance. I've been all over the internet and haven't been able to find a similar situation. I tried to be thorough, but I'm sure I missed some vital information so please let me know if I can elaborate. Thoughts greatly appreciated.
Thanks.