Hi OLAP developers,
I have run into a performance issue when using the TABLE function in an OLAP query and was wondering if someone had any pointers on a possible solution.
[Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production]
Am trying to develop Oracle APEX Interactive reports as a front end - using Oracle OLAP cubes and dimensions as the data source.
I would like to pass an APEX Item string of user selected dimension values to the
TABLE function that returns a table of dimension values for use in the WHERE clause in an OLAP query. The format of the APEX Item string is 'DIMVAL1:DIMVAL2:DIMVAL3'.
APEX users basically choose what Organizations, Locations, Time values that they want to see and the APEX Item values are passed to the Table Function.
Testing this I have found:
1. Queries in SQL Developer using the TABLE function return with good performance only if I use the hint /*+ PRECOMPUTE_SUBQUERY */
(I found this hint via googling and OTN searches). I know that it is probably not a good idea to use this hint.
2. It seems that when the SQL is run within APEX the hint /*+ PRECOMPUTE_SUBQUERY */ causes the called function to receive a NULL value instead of the APEX Item value.
Perhaps this is due to the way session management is run in APEX?
I would be interested to know if there is a way to get good performance using a table function in OLAP queries or should I not use TABLE functions in OLAP queries in the "WHERE" clause?
Note: Using a hint as solution seems to cause issues when the SQL is run inside APEX Reports.
Note: Interactive reports in APEX cannot use dynamic SQL as a source, so I cannot hard code lists of values in the WHERE clause at run time by regenerating the SQL based on APEX user choices.
Note: Have found that using one or two calls to a TABLE function sometimes works, but using a TABLE function against all four dimensions causes the SQL to "hang"
TO further demonstrate in a simple test of hardcoding values in the WHERE clause, One record is returned with fast performance
===============================================================================================================
EXPLAIN PLAN FOR
SELECT
o.long_description organization
, l.long_description location
, t.long_description time_period
, s.long_description tier
, ROUND(f.nas_alloc, 0) nas_alloc
, ROUND(f.nas_util, 0) nas_util
, ROUND(f.nas_percu, 2) nas_percu
, ROUND(f.san_alloc, 0) san_alloc
, ROUND(f.san_util, 0) san_util
, ROUND(f.san_percu, 2) san_percu
, ROUND(f.tot_alloc, 0) tot_alloc
, ROUND(f.tot_util, 0) tot_util
, ROUND(f.tot_percu, 2) tot_percu
FROM sord.db_meas_cube_view f
INNER JOIN sord.db_aw_time_dim_standard_view t
ON (f.db_aw_time_dim = t.dim_key)
INNER JOIN sord.db_aw_org_dim_cio_view o
ON (f.db_aw_org_dim = o.dim_key)
INNER JOIN sord.db_aw_loc_dim_standard_view l
ON (f.db_aw_loc_dim = l.dim_key)
INNER JOIN sord.db_aw_svc_dim_standard_view s
ON (f.db_aw_svc_dim = s.dim_key)
WHERE 1=1
--TIME Dimension--
AND (t.dim_key = 'Y2015')
--TIER/Service Name Dimension--
AND s.dim_key IN ('TOT')
--ORG Dimension--
AND o.dim_key IN ('TOT')
--LOCATION Dimension--
AND l.dim_key IN ('TOT')
;
Plan hash value: 2133067731
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 1423 (100)| 00:00:18 |
| 1 | JOINED CUBE SCAN OUTER| | | | | |
| 2 | CUBE ACCESS | DB_AW_SVC_DIM | | | | |
| 3 | CUBE ACCESS | DB_MEAS_CUBE | | | | |
| 4 | CUBE ACCESS | DB_AW_LOC_DIM | | | | |
| 5 | CUBE ACCESS | DB_AW_ORG_DIM | | | | |
|* 6 | CUBE ACCESS | DB_AW_TIME_DIM | 1 | 103 | 1423 (100)| 00:00:18 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SYS_OP_ATG(VALUE(KOKBF$),140,141,2)='TOT' AND
SYS_OP_ATG(VALUE(KOKBF$),113,114,2)='TOT' AND
SYS_OP_ATG(VALUE(KOKBF$),87,88,2)='Y2015' AND
SYS_OP_ATG(VALUE(KOKBF$),1,2,2)='TOT')
================================================================================================================
Using the TABLE function below with the PRECOMPUTE_SUBQUERY hint, the performance is also good.
================================================================================================================
create or replace TYPE dashb_dim_val_rec as object
(
dim_val varchar2(400)
) ;
create or replace TYPE dashb_dim_val_tbl as table of dashb_dim_val_rec;
create or replace FUNCTION dashb_ret_dim_val_recs(p_string IN VARCHAR2)
RETURN dashb_dim_val_tbl PIPELINED
AS
dim_val_ret dashb_dim_val_rec;
l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
dim_val_ret := NEW dashb_dim_val_rec(null);
l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(p_string);
FOR i IN 1..l_vc_arr2.count
LOOP
dim_val_ret.dim_val := l_vc_arr2(i);
PIPE ROW (dim_val_ret);
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS
THEN
dim_val_ret.dim_val := SQLERRM;
PIPE ROW (dim_val_ret);
RETURN;
END;
==============================================================================================================
Note: within APEX, would pass the dimension value as ":some_item" rather than 'TOT' where ":some_item" might contain 'DIMVAL1:DIMVAL2:DIMVAL3'
SELECT
o.long_description organization
, l.long_description location
, t.long_description time_period
, s.long_description tier
, ROUND(f.nas_alloc, 0) nas_alloc
, ROUND(f.nas_util, 0) nas_util
, ROUND(f.nas_percu, 2) nas_percu
, ROUND(f.san_alloc, 0) san_alloc
, ROUND(f.san_util, 0) san_util
, ROUND(f.san_percu, 2) san_percu
, ROUND(f.tot_alloc, 0) tot_alloc
, ROUND(f.tot_util, 0) tot_util
, ROUND(f.tot_percu, 2) tot_percu
FROM sord.db_meas_cube_view f
INNER JOIN sord.db_aw_time_dim_standard_view t
ON (f.db_aw_time_dim = t.dim_key)
INNER JOIN sord.db_aw_org_dim_cio_view o
ON (f.db_aw_org_dim = o.dim_key)
INNER JOIN sord.db_aw_loc_dim_standard_view l
ON (f.db_aw_loc_dim = l.dim_key)
INNER JOIN sord.db_aw_svc_dim_standard_view s
ON (f.db_aw_svc_dim = s.dim_key)
WHERE 1=1
--TIME Dimension--
AND (t.dim_key = 'Y2015')
--TIER/Service Name Dimension--
AND s.dim_key IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ dim_val FROM TABLE(sord.dashb_ret_dim_val_recs('TOT')))
--ORG Dimension--
AND o.dim_key IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ dim_val FROM TABLE(sord.dashb_ret_dim_val_recs('TOT')))
--LOCATION Dimension--
AND l.dim_key IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ dim_val FROM TABLE(sord.dashb_ret_dim_val_recs('TOT')))
;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 1423 (100)| 00:00:18 |
| 1 | JOINED CUBE SCAN OUTER| | | | | |
| 2 | CUBE ACCESS | DB_AW_SVC_DIM | | | | |
| 3 | CUBE ACCESS | DB_MEAS_CUBE | | | | |
| 4 | CUBE ACCESS | DB_AW_LOC_DIM | | | | |
| 5 | CUBE ACCESS | DB_AW_ORG_DIM | | | | |
|* 6 | CUBE ACCESS | DB_AW_TIME_DIM | 1 | 103 | 1423 (100)| 00:00:18 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SYS_OP_ATG(VALUE(KOKBF$),140,141,2)='TOT' AND
SYS_OP_ATG(VALUE(KOKBF$),113,114,2)='TOT' AND
SYS_OP_ATG(VALUE(KOKBF$),87,88,2)='Y2015' AND
SYS_OP_ATG(VALUE(KOKBF$),1,2,2)='TOT')
Without the HINT, using the TABLE function, the performance is very poor.
=====================================================================================================================================================
EXPLAIN PLAN FOR
SELECT
o.long_description organization
, l.long_description location
, t.long_description time_period
, s.long_description tier
, ROUND(f.nas_alloc, 0) nas_alloc
, ROUND(f.nas_util, 0) nas_util
, ROUND(f.nas_percu, 2) nas_percu
, ROUND(f.san_alloc, 0) san_alloc
, ROUND(f.san_util, 0) san_util
, ROUND(f.san_percu, 2) san_percu
, ROUND(f.tot_alloc, 0) tot_alloc
, ROUND(f.tot_util, 0) tot_util
, ROUND(f.tot_percu, 2) tot_percu
FROM sord.db_meas_cube_view f
INNER JOIN sord.db_aw_time_dim_standard_view t
ON (f.db_aw_time_dim = t.dim_key)
INNER JOIN sord.db_aw_org_dim_cio_view o
ON (f.db_aw_org_dim = o.dim_key)
INNER JOIN sord.db_aw_loc_dim_standard_view l
ON (f.db_aw_loc_dim = l.dim_key)
INNER JOIN sord.db_aw_svc_dim_standard_view s
ON (f.db_aw_svc_dim = s.dim_key)
WHERE 1=1
--TIME Dimension--
AND (t.dim_key = 'Y2015')
--TIER/Service Name Dimension--
AND s.dim_key IN (SELECT dim_val FROM TABLE(sord.dashb_ret_dim_val_recs('TOT')))
--ORG Dimension--
AND o.dim_key IN (SELECT dim_val FROM TABLE(sord.dashb_ret_dim_val_recs('TOT')))
--LOCATION Dimension--
AND l.dim_key IN (SELECT dim_val FROM TABLE(sord.dashb_ret_dim_val_recs('TOT')))
;
Plan hash value: 4058885555
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 163G| 105T| 780K(100)| 02:36:02 |
|* 1 | HASH JOIN | | 163G| 105T| 780K(100)| 02:36:02 |
| 2 | VIEW | VW_NSO_1 | 8168 | 1611K| 30 (4)| 00:00:01 |
| 3 | HASH UNIQUE | | 8168 | 16336 | 30 (4)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH | DASHB_RET_DIM_VAL_RECS | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 180M| 85G| 2378 (98)| 00:00:29 |
| 6 | VIEW | VW_NSO_3 | 8168 | 1611K| 30 (4)| 00:00:01 |
| 7 | HASH UNIQUE | | 8168 | 16336 | 30 (4)| 00:00:01 |
| 8 | COLLECTION ITERATOR PICKLER FETCH | DASHB_RET_DIM_VAL_RECS | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 7983K| 2322M| 1491 (98)| 00:00:18 |
| 10 | VIEW | VW_NSO_2 | 8168 | 1611K| 30 (4)| 00:00:01 |
| 11 | HASH UNIQUE | | 8168 | 16336 | 30 (4)| 00:00:01 |
| 12 | COLLECTION ITERATOR PICKLER FETCH| DASHB_RET_DIM_VAL_RECS | 8168 | 16336 | 29 (0)| 00:00:01 |
| 13 | JOINED CUBE SCAN OUTER | | | | | |
| 14 | CUBE ACCESS | DB_AW_SVC_DIM | | | | |
| 15 | CUBE ACCESS | DB_MEAS_CUBE | | | | |
| 16 | CUBE ACCESS | DB_AW_LOC_DIM | | | | |
| 17 | CUBE ACCESS | DB_AW_ORG_DIM | | | | |
|* 18 | CUBE ACCESS | DB_AW_TIME_DIM | 1254K| 123M| 1422 (100)| 00:00:18 |
-----------------------------------------------------------------------------------------------------------------
Thanks for taking a look,
-Kevin L.