I'm using sys_context bases variables to filter views. I'm new to oracle and in sql server I will use table value function (prefered inline) or store procedure.
It is strange that executing sql from view without variables and real constant in its place is much more faster than when it is execute trough view with sys_context variables. Difference can be very significant. Best regards.
BEGIN
ZV_INA_set_parameters_ctx_pkg.setparam_str('FIELD_CODE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('FACILITY_CODE','FAC_DEL');
ZV_INA_set_parameters_ctx_pkg.setparam_str('EP_FIELD_CODE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('WELL_CODE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('WELL_TYPE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('INSTRUMENTATION_TYPE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('MOS_SLA',NULL);
ZV_INA_set_parameters_ctx_pkg.setparam_str('OP_PU_CODE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('OP_AREA_CODE',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('COUNTRY',Null);
ZV_INA_set_parameters_ctx_pkg.setparam_str('WH_CODE',Null);
ZV_INA_set_parameters_ctx_pkg.set_fromto(TO_DATE('01.10.2020','dd.MM.yyyy'),TO_DATE('31.10.2020','dd.MM.yyyy')); END;
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ECKERNEL_MOL_DEV"."ZV_INA_PAR_WELL_DAY_DISPOSITION"
("DAYTIME", "FACILITY_CODE", "WELL_ID", "WELL", "WELL_TYPE", "PROD_METHOD", "GEO_FIELD_ID", "FIELD_CODE", "FIELD", "EP_ID", "EP_FIELD",
"WH_CODE", "INSTRUMENTATION_TYPE", "DIM1_KEY", "DISPOSITION_ID", "DISPOSITION_CODE", "DISPOSITION_NAME", "SORT_ORDER",
"COMMENTS", "PRODUCT_ID", "PRODUCT_CODE", "PRODUCT", "DISP_START_DATE", "DISP_END_DATE", "SCM", "MASS", "NUM3", "MASSSCM", "BOE")
AS
select ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DAYTIME,
well_lab.FACILITY_CODE FACILITY_CODE,
well_lab.OBJECT_ID WELL_ID,
well_lab.WELL,
well_lab.WELL_TYPE,
well_lab.PROD_METHOD,
well_lab.GEO_FIELD_ID,
well_lab.FIELD_CODE,
well_lab.FIELD,
well_lab.EP_ID,
well_lab.EP_FIELD,
well_lab.WH_CODE,
well_lab.INSTRUMENTATION_TYPE,
ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DIM1_KEY,
ZV_INA_DISPOSITION.DISPOSITION_ID DISPOSITION_ID,
ZV_INA_DISPOSITION.DISPOSITION_CODE,
ZV_INA_DISPOSITION.DISPOSITION_NAME,
ZV_INA_DISPOSITION.SORT_ORDER,
ZV_INA_DISPOSITION.COMMENTS COMMENTS,
ZV_INA_DISPOSITION.PRODUCT_ID,
ZV_INA_PRODUCT.PRODUCT_CODE,
ZV_INA_PRODUCT.PRODUCT,
ZV_INA_DISPOSITION.START_DATE DISP_START_DATE,
ZV_INA_DISPOSITION.END_DATE DISP_END_DATE,
ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.NUM1 SCM,
ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.NUM2 MASS,
ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.NUM3,
(ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.NUM2/NULLIF(well_lab.DENSITY,0)) MASSSCM,
(CASE WHEN PRODUCT_CODE = 'GAS' THEN ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.NUM1*FACTOR_GAS WHEN PRODUCT_CODE='OIL' OR PRODUCT_CODE='COND' OR PRODUCT_CODE='VN' THEN ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.NUM2*FACTOR_OIL ELSE NULL END) BOE
FROM ECKERNEL_MOL_DEV.ZV_INA_PAR_WELL_GEO_WITH_LAB_DATA_BE well_lab
INNER JOIN ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC ON
ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.OBJECT_ID = well_lab.OBJECT_ID
AND ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DAYTIME Between well_lab.start_date And NVL(well_lab.end_date,TO_DATE('31.12.9999','dd.MM.yyyy'))
LEFT JOIN ECKERNEL_MOL_DEV.ZV_INA_DISPOSITION ON ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DIM1_KEY=ECKERNEL_MOL_DEV.ZV_INA_DISPOSITION.DISPOSITION_ID
AND ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DAYTIME BETWEEN ECKERNEL_MOL_DEV.ZV_INA_DISPOSITION.START_DATE AND ECKERNEL_MOL_DEV.ZV_INA_DISPOSITION.END_DATE
LEFT JOIN ECKERNEL_MOL_DEV.ZV_INA_PRODUCT ON ECKERNEL_MOL_DEV.ZV_INA_DISPOSITION.PRODUCT_ID=ECKERNEL_MOL_DEV.ZV_INA_PRODUCT.PRODUCT_ID AND
ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DAYTIME BETWEEN ECKERNEL_MOL_DEV.ZV_INA_PRODUCT.START_DATE AND ECKERNEL_MOL_DEV.ZV_INA_PRODUCT.END_DATE
WHERE ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.CLASS_NAME='WELL_DAY_DISP_ALLOC'
AND (ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DAYTIME >= TO_DATE(SYS_CONTEXT('ZV_INA_parameters_ctx', 'fromdate') ,'dd.MM.yyyy')
OR TO_DATE( SYS_CONTEXT('ZV_INA_parameters_ctx', 'fromdate') ,'dd.MM.yyyy') IS NULL)
AND
(ECKERNEL_MOL_DEV.OBJECT_DAY_DIM1_ALLOC.DAYTIME <= TO_DATE(SYS_CONTEXT('ZV_INA_parameters_ctx', 'todate') ,'dd.MM.yyyy')
OR TO_DATE( SYS_CONTEXT('ZV_INA_parameters_ctx', 'todate') ,'dd.MM.yyyy') IS NULL)
AND (well_lab.FACILITY_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'FACILITY_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'FACILITY_CODE') IS NULL)
AND (well_lab.FIELD_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'FIELD_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'FIELD_CODE') IS NULL)
AND (well_lab.EP_FIELD=SYS_CONTEXT('ZV_INA_parameters_ctx', 'EP_FIELD_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'EP_FIELD') IS NULL)
AND (well_lab.WELL_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'WELL_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'WELL') IS NULL)
AND (well_lab.WELL_TYPE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'WELL_TYPE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'WELL_TYPE') IS NULL)
AND (well_lab.INSTRUMENTATION_TYPE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'INSTRUMENTATION_TYPE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'INSTRUMENTATION_TYPE') IS NULL)
AND (well_lab.MOS_SLA=SYS_CONTEXT('ZV_INA_parameters_ctx', 'MOS_SLA') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'MOS_SLA') IS NULL)
AND (well_lab.OP_PU_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'OP_PU_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'OP_PU_CODE') IS NULL)
AND (well_lab.OP_AREA_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'OP_AREA_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'OP_AREA_CODE') IS NULL)
AND (well_lab.COUNTRY_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'COUNTRY_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'COUNTRY_CODE') IS NULL)
AND (well_lab.WH_CODE=SYS_CONTEXT('ZV_INA_parameters_ctx', 'WH_CODE') OR SYS_CONTEXT('ZV_INA_parameters_ctx', 'WH_CODE') IS NULL);
I think there is perhaps some mixed messaging here.
When you accessing a view with a common query lots and lots of times, then using a sys_context to pass parameters makes sense because you are
- lowering the parsing costs associated with a new query
- removing the chances of SQL injection.
But the key thing in that statement above if "common query". For example, if I had a view which would
select ...
from emp
then I don't need a sys_context at all, because a predicate would be just pushed into the view anyway. But if the view was (say)
select ...
from emp
connect by mgr = prior empno
start with empno = ...
then I might choose to have the sys_context so that I can nominate the starting position (because I can't do that with predicate pushdown). Then this query will be parsed once for all the different executions that will likely come in. Its a simple query that I could reasonably expect to be called lots of times for different employees
However, looking at your query, it doesn't look (to me) like a "common" query. It looks a lot more like an adhoc search facility. This is probably where the one thing you do NOT want to happen is for the query to be parsed once and share the plan with many executions, because I'm guessing you have all sorts of pemutations of incoming parameters (null, not null etc).
For adhoc search, you typically want to build up the query to specifically suit the parameters, and also use the true values to give the optimizer the best information.
In places where I've worked in the past, the best solutions typically were a hybrid, for example:
On day 1, we'd implement a search facility similar to what you have done, but every search request is logged. After a short while, you start to see patterns in what is being requested.
It might be the common request is users give you both EP_FIELD, OP_PU_CODE and nothing most frequently. I'd then adjust the code to be:
if "EP_FIELD, OP_PU_CODE only" then
open refcursor for select .... where ep_code = [passed] and op_pu_code = [passed]
else
[build dynamic SQL with passed parameters]
end if
and this evolves over time to best handle your most common queries and fall through to a "catch all" for the rest.
So my recommendations:
1) For adhoc search, build your SQL dynamically and use the literals, taking care to use DBMS_ASSERT to ensure you don't get sql injection issues
2) capture all searches, find which ones are most common
3) implement specific SQL's for those ones, and because they are now single-purpose SQL's, use sys_context to use binds for those ones.