Dear Tom,
I am trying to tune a query (12.1.0.1 version, 4 node RAC).
The database appears to use DBMS_RLS policies.
I see one context defined as:
CREATE OR REPLACE CONTEXT CRAMERSESSION
USING CRAMER.PKGSESSIONCONTEXT
/
CRAMER.PKGSESSIONCONTEXT code is obfuscated.
In the explain plan, I see this:
74 TABLE ACCESS FULL TABLE CRAMER.SERVICEOBJECT Object Instance: 90 Filter Predicates: "SERVICEOBJECT2DIMOBJECT"=3 AND "RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) Cost: 124,425 Bytes: 2,898 Cardinality: 138 Time: 5"RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) -- this repeats throughout the explain plan.
This evaluates to 0:
select TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) from dual;is there any way to index this expression?
"RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0'))
Thank you.
Yes, because the context just returns a value (which becomes a bind variable value) in your query.
So from the database perspective, it ultimately becomes no different to:
where RPPLANID = :b1
Thus the index is built on the column RPPLANID in the normal way.
SQL> create or replace package pkg is
2 procedure setctx;
3 end;
4 /
Package created.
SQL>
SQL> create or replace package body pkg is
2 procedure setctx is
3 begin
4 dbms_session.set_context('MYCTX','MYKEY','123');
5 end;
6 end;
7 /
Package body created.
SQL>
SQL> create context myctx using pkg;
Context created.
SQL> create table t as select * from dba_objects;
Table created.
SQL>
SQL> exec pkg.setctx;
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select * from t
2 where object_id = to_number(sys_context('MYCTX','MYKEY'));
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 140 | 503 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 140 | 503 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(SYS_CONTEXT('MYCTX','MYKEY')))
SQL>
SQL> create index ix on t ( object_id );
Index created.
SQL> select * from t
2 where object_id = to_number(sys_context('MYCTX','MYKEY'));
Execution Plan
----------------------------------------------------------
Plan hash value: 3947747388
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 140 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 140 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(SYS_CONTEXT('MYCTX','MYKEY')))