This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 25, 2013 5:06 AM by 772199 RSS

Oracle OLAP and APEX

772199 Newbie
Currently Being Moderated
Hi,

We have built reports in APEX using Oracle OLAP views. But these reports are very very slow as they query the views which in turn use TABLE and CUBE_TABLE functions to convert cube into two dimensional format of a table.
The cube contains 10 dimensions and 6 measures. Can you help us in answering the below question

Is using OLAP views the only way of accessing Oracle OLAP in APEX ? If yes can you suggest ways to speed up the access ?

We can access the cube very fast using data viewer in AWM.

Thanks,
RK
  • 1. Re: Oracle OLAP and APEX
    tony.g Journeyer
    Currently Being Moderated
    Have you tried using cube orientated materialised views?

    Tony
  • 2. Re: Oracle OLAP and APEX
    Nasar Journeyer
    Currently Being Moderated
    RK,

    Often times there are two reasons for slow query:

    (1). Are you providing WHERE conditions for all dimensions in your APEX query? If any dimension is not needed in the report then set it to top node in the WHERE clause.

    (2). LOOP_VAR and/or LOOP_DENSE settings for calculated measures are not correctly set.


    The best way to debug this issue is to check the CUBE_OPERATIONS_LOG table.

    Take your sql query from APEX and execute it manually in SQL*Developer (or TOAD or sql*plus).
    Then check the records in CUBE_OPERATIONS_LOG table and see which step is taking the most amount of time.


    Take a look at my 04-Feb and 05-Feb postings here on this topic:

    Delay when querying from CUBE_TABLE object, what is it?



    .
  • 3. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    Hi Nasar,

    This is the query I'm executing SQL developer

    SELECT
    SUM(TS.SUBMITTED)
    FROM SUBMITTED_DS_VIEW TS ,
    CUBE_DIM_CALENDAR_HIE_CAL_VIEW CA
    WHERE TS.CUBE_DIM_CALENDAR = CA.CALENDAR_DATE
    AND CA.LEVEL_NAME = 'CALENDAR_DATE'
    AND TS.CUBE_DIM_SOURCE <> 'ALL VENDORS'
    AND TS.CUBE_DIM_CAMPAIGN <> 'ALL CAMPAIGNS'
    AND TS.CUBE_DIM_CLIENT_CONTRACT <> 'ALL CONTRACTS'
    AND CA.END_DATE >= '18-FEB-2013'
    AND CA.END_DATE < '19-FEB-2013'
    ;

    And it just keeps on running without any result.

    Edited by: svramakris on Mar 15, 2013 10:47 PM
  • 4. Re: Oracle OLAP and APEX
    Nasar Journeyer
    Currently Being Moderated
    look at cube operations log table and see where it is taking time
  • 5. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    In the cube I changed Aggregation method from Level-based to Cost-based and the above query executed in 95 seconds. But this is very slow.
  • 6. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    I re-executed the same query and it took 99 seconds; next I checked cube_operations_log but there are no entries for the SQL I executed.
  • 7. Re: Oracle OLAP and APEX
    Nasar Journeyer
    Currently Being Moderated
    Take a look at David Greenfield's response
    Cube Refresh Performance Issue

    CUBE_OPERATIONS_LOG table should be in the same schema where you are executing the query.

    If it is enabled (as I mentioned in my previous reply), then information about all OLAP_TABLE and CUBE_TABLE queries will be logged.
  • 8. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    Hi Nasar,

    Table CUBE_OPERATIONS_LOG is enabled and I can see entries in it for operations like HIERARCHY,AGGREGATE,COMMIT,ROWSOURCE,KERNEL,SQLIMPORT,PAGING every time I process the cube; but i do not see any entries for SQL queries I run againt OLAP views.


    Also I executed the command
    dbms_cube_log.enable(dbms_cube_log.TYPE_OPERATIONS, dbms_cube_log.TARGET_TABLE, dbms_cube_log.LEVEL_MEDIUM);

    Is there anything which I need to enable ? Please help.


    Thanks,
    RK
  • 9. Re: Oracle OLAP and APEX
    DavidGreenfield Expert
    Currently Being Moderated
    Your query appears to be truncated in the post. Can you supply the entire query? I ask because you said your cube had 10 dimensions, but only 4 of them are qualified in the query.

    How is your cube partitioned and how many leaf rows are loaded per partition?

    What percent precompute do you have on your cube?

    Here is my favourite diagnostic query on the cube_operations_log. You run it after the executing the query. It assumes that cube_operations_log is empty before the query is run. Can you please copy the results to the forum?
    set linesize 200
    column NAME_VALUE_DETAILS format a200
    set long 20000
    
    SELECT 
       RPAD(NAME, MAX(LENGTH(NAME)) OVER()) 
       || '      ' 
       || RPAD(VALUE, MAX(LENGTH(VALUE)) OVER()) 
       || '      '
       || DETAILS NAME_VALUE_DETAILS 
    FROM CUBE_OPERATIONS_LOG 
    WHERE 
      NAME LIKE '%DIM%' 
      OR NAME LIKE '%ROW%' 
      OR NAME LIKE '%LOOP%' 
      OR NAME LIKE '%LIMIT%' 
      OR NAME LIKE '%SUPPRE%' 
      OR NAME LIKE '%TIME%' 
      OR NAME LIKE '%PAG%' 
    ORDER BY TIME
    /
  • 10. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    This is the complete query


    SELECT
    SUM(TS.TOTAL_SUBMITTED)
    FROM EDW2.TOTAL_SUBMITTED_DS_VIEW TS ,
    EDW2.CUBE_DIM_CALENDAR_HIE_CAL_VIEW CA
    WHERE TS.CUBE_DIM_CALENDAR = CA.CALENDAR_DATE
    AND CA.LEVEL_NAME ='CALENDAR_DATE'
    AND TS.CUBE_DIM_SOURCE<>'ALL VENDORS'
    AND TS.CUBE_DIM_CAMPAIGN<>'ALL CAMPAIGNS'
    AND TS.CUBE_DIM_CLIENT_CONTRACT <> 'ALL CONTRACTS'
    AND TS.CUBE_DIM_VERTICAL <> 'ALL VERTICALS'
    AND TS.CUBE_DIM_PRODUCT <> 'ALL PRODUCTS'
    AND TS.CUBE_TIME_HOURS <> 'ALL HOURS'
    AND TS.CUBE_DIM_CHANNEL <> 'ALL CHANNELS'
    AND TS.CUBE_DIM_CLIENT <> 'ALL CLIENTS'
    AND TS.CUBE_DIM_LEAD_TEMPLATES <> 'ALL LEAD TEMPLATES'
    AND CA.END_DATE >= '18-FEB-2013'
    AND CA.END_DATE < '19-FEB-2013'
    ;

    After I included all dimensions in the query, the execution time reduced to 2.7 seconds. In the earlier query I included only 4 dimensions.

    The cube is partitioned by Month, there is a partition for each month.

    I did 70% precompute on the cube.

    I also enabled the Materialized view refresh of the cube. Now the same query using the materialized executes in 0.2 seconds. Below is the query.

    SELECT
    SUM(TS.TOTAL_SUBMITTED)
    FROM EDW2.CB$TOTAL_SUBMITTED_DS TS
    WHERE CUBE_DIM_CALENDAR = 780
    ;

    Now in APEX we will use the materialized view instead of cube view and check the performance.

    I will paste the contents of CUBE_OPERATIONS_LOG table shortly.
  • 11. Re: Oracle OLAP and APEX
    DavidGreenfield Expert
    Currently Being Moderated
    A response time of 2.7 seconds sounds much better. When you use the cube views it is absolutely vital that you specify conditions on all the dimensions of the cube. If you don't, then the code will attempt to return all rows for all members of the unspecified dimension. Here is a simple example. Suppose you have a cube with 4 dimensions, each of which has 10 members. We will assume the cube has data everywhere, which means there are 10000 (= 10^4) cell in the cube.

    Query 1, below, will fetch just one cell from the cube.
    SELECT SUM(meas)
    FROM cube_view
    WHERE dim1 = 'A' AND dim2 = 'B' AND dim3 = 'C' AND dim4 = 'D'
    Query 2, by contrast, will fetch 1000 (= 10^3) cells since three of the dimensions are unqualified.
    SELECT SUM(meas)
    FROM cube_view
    WHERE dim1 = 'A'
    When you use query rewrite, on the other hand, you are writing queries against the base fact table. In this case any dimension not specified in the WHERE clause will be (effectively) pinned to the 'ALL' value in the cube.
  • 12. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    Thank you for the detailed explanation. Now that I have enabled materialized views, the queries are fast. But I'm facing strange problem.

    When I run the below query in SQL Developer the connection is getting closed (message is "No more data to read from socket")

    SELECT
    SUM(TS.TOTAL_SUBMITTED)
    FROM
    EDW2.CB$TOTAL_SUBMITTED_DS TS ,EDW2.CB$CUBE_DIM_CALENDAR_HIE_CALEN C
    WHERE TS.CUBE_DIM_CALENDAR = C.CUBE_DIM_CALCALENDAR_DAT
    AND C.END_DATE='01-MAR-2013'
    ;


    If the re-write the same query without direct join between the views, it works

    SELECT
    SUM(TS.TOTAL_SUBMITTED)
    FROM EDW2.CB$TOTAL_SUBMITTED_DS TS
    WHERE CUBE_DIM_CALENDAR = (SELECT CUBE_DIM_CALCALENDAR_DAT FROM EDW2.CB$CUBE_DIM_CALENDAR_HIE_CALEN WHERE END_DATE = '01-MAR-2013')
    ;

    Can you help us in understanding the issue ?
  • 13. Re: Oracle OLAP and APEX
    Nasar Journeyer
    Currently Being Moderated
    Can you provide the EXPLAIN PLAN for both queries?

    Anything you see in CUBE_OPERATIONS_LOG that could be helpful?

    AWR reports are also useful.
  • 14. Re: Oracle OLAP and APEX
    772199 Newbie
    Currently Being Moderated
    Explain Plan too gives the same message(No more data to read from socket). I checked alert log and there is an exception logged


    Tue Mar 19 08:24:37 2013
    Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x314FCC9, xsjpshValidPush()+3591] [flags: 0x0, count: 1]
    Errors in file /data/oracle/app/oracle/diag/rdbms/oraprd1/oraprd1/trace/oraprd1_ora_19411.trc (incident=101307):
    ORA-07445: exception encountered: core dump [xsjpshValidPush()+3591] [SIGSEGV] [ADDR:0x0] [PC:0x314FCC9] [Address not mapped to object] []
    Incident details in: /data/oracle/app/oracle/diag/rdbms/oraprd1/oraprd1/incident/incdir_101307/oraprd1_ora_19411_i101307.trc
    Use ADRCI or Support Workbench to package the incident.
    See Note 411.1 at My Oracle Support for error and packaging details.
    Tue Mar 19 08:24:39 2013
    Dumping diagnostic data in directory=[cdmp_20130319082439], requested by (instance=1, osid=19411), summary=[incident=101307].
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points