Slow cube request
Hi there again,
well I eventually managed to get my OLAP cube done, I can now play with it in Answers, so things are moving forward. However I now seem to have a performance problem when building views in Answers. A view typically requires about one minute to compute, this seems very much to me, knowing that the initial fact table upon which the cube is built has only 200k rows.
Here is for instance the text of an SQL query submitted to the Oracle 11g server by Answers, which returns ten rows in about one minute:
=================================================
SELECT t1936.sh_long_description AS c5, t1936.cd_long_description AS c6,
t1936.bl_long_description AS c7
FROM exitcs_view t1992,
dttm_view t1968,
bnumber_view t1936,
anumber_view t1904,
traffic_view t2001
WHERE ( t1936.dim_key = t2001.bnumber
AND t1904.dim_key = t2001.anumber
AND t1968.dim_key = t2001.dttm
AND t1904.level_name = N'SH'
AND t1936.cd_long_description = N'212'
AND t1936.level_name = N'BL'
AND t1936.sh_long_description = N'SHORT'
AND t1968.level_name = N'ALLDT'
AND t1992.dim_key = t2001.exitcs
AND t1992.level_name = N'ALLCS'
);
=================================================
and the resulting autotrace info :
=================================================
----------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
----------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 29 (0)|00:00:01 |
| 1 | JOINED CUBE SCAN PARTIAL OUTER| | | | | |
| 2 | CUBE ACCESS | TRAFFIC | | | | |
| 3 | CUBE ACCESS | ANUMBER | | | | |
| 4 | CUBE ACCESS | BNUMBER | | | | |
| 5 | CUBE ACCESS | DTTM | | | | |
|* 6 | CUBE ACCESS | EXITCS | 1 | 100 | 29 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SYS_OP_C2C(SYS_OP_ATG(VALUE(KOKBF$),31,32,2))=U'SH' AND
SYS_OP_C2C(SYS_OP_ATG(VALUE(KOKBF$),24,25,2))=U'212' AND
SYS_OP_C2C(SYS_OP_ATG(VALUE(KOKBF$),15,16,2))=U'BL' AND
SYS_OP_C2C(SYS_OP_ATG(VALUE(KOKBF$),23,24,2))=U'SHORT' AND
SYS_OP_C2C(SYS_OP_ATG(VALUE(KOKBF$),55,56,2))=U'ALLDT' AND
SYS_OP_C2C(SYS_OP_ATG(VALUE(KOKBF$),5,6,2))=U'ALLCS')
Statistics
----------------------------------------------------------
8176 recursive calls
1580 db block gets
5195 consistent gets
856 physical reads
258396 redo size
542 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
79 sorts (memory)
0 sorts (disk)
10 rows processed
=================================================
and trace :
=================================================
call count cpu elapsed disk query current rows
----------------------------------------------
Parse 1 0.08 0.11 3 766 298 0
Execute 1 0.00 0.00 2 15 0 0
Fetch 2 61.84 61.96 1 795 0 10
----------------------------------------------
total 4 61.93 62.08 6 1576 298 10
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Rows Row Source Operation
----------------------------------------------
10 JOINED CUBE SCAN (cr=965 pr=3 pw=0 time=0 us cost=29 size=100 card=1)
=================================================
Can somebody spot a problem here ? My understanding is that these kind of requests against the dimensions of a cube should be almost instantaneous, however it feels like here the server is doing a "full table scan" of some sort.
Thanks again,
Christian