Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE - Report Performance Issue

Received Response
11
Views
10
Comments
3340831
3340831 Rank 1 - Community Starter
edited Aug 13, 2024 6:10PM in Oracle Analytics Cloud and Server

Hello Experts,

Need your valuable inputs on a performance issue we are having with a OBIEE report. The report is very simple with just one field in the criteria and 3 filters - one for Date (between clause), another on a Fact column and the last one on a attribute (just one value selected). The report takes around 5 minutes to show the data. However, when we execute the physical query in database (Oracle 11g) directly it just takes 2 seconds to fetch the results. The result set is also not that much with just around 10-100 records. The query has just 3 physical tables (no views involved) - a fact and couple of dimensions. The execution plan of the query doesn't show any abnormality and is absolutely fine. We even tried creating new indexes on the tables but of no help in improving the report performance. The query was executed in DB using the same credentials that is being used in RPD connection pool.

Any inputs on this issue is highly appreciated.

Thanks,

Vedavyas

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Have you pulled out the query generated by the BI Server and executed that directly in your Oracle DB?

    First thing would be to rule out any DB issue then next step would be to look at rpd.

  • 3340831
    3340831 Rank 1 - Community Starter

    Hi Joel,

    Yes, as I mentioned earlier, I took the physical query from the log file and executed in DB directly. There is no performance issue with the query at all.

    Thanks,

    Vedavyas

  • rmoff
    rmoff Rank 6 - Analytics Lead

    You've not said what version of OBIEE you're running, but regardless, the approach is the same : look at where the time is being spent. In the log file, how long does it show the query as executing on the database for? Does the start/finish time in the nqquery (or obis1-query) log match that which you're seeing in the front end?

    This should give you a good place to start :

    * Video: http://ritt.md/silver-bullets-video

    * Slides: http://ritt.md/silver-bullets-slides

  • 3340831
    3340831 Rank 1 - Community Starter

    This is for OBIEE 11g (11.1.1.7.150120) and Oracle DB is 11.2.0.3. The query log shows the below messages.

    Physical query response time 323 (seconds)

    Physical Query Summary Stats: Number of physical queries 1, Cumulative time 323, DB-connect time 0 (seconds)

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    Hello,

    What is the total of records in your tables. Did you try taking the total count .

    Also after executing the report , Immediately navigate to manage sessions and check if the query actually running and sent to DB

    Thanks

    Aj

  • rmoff
    rmoff Rank 6 - Analytics Lead 
    3340831 wrote:This is for OBIEE 11g (11.1.1.7.150120) and Oracle DB is 11.2.0.3. The query log shows the below messages.Physical query response time 323 (seconds)Physical Query Summary Stats: Number of physical queries 1, Cumulative time 323, DB-connect time 0 (seconds)

    So ... 11.2.0.3 is pretty old, very old in fact. Unlikely to impact performance necessarily, just an observation. Your OBIEE version is many patchsets behind too. Again, unlikely to impact performance but you ought to be at least keeping it patched.

    So performance - if OBIEE is taking 323 seconds to execute the query on the DB, you've started some of the way to identifying the issue. As @asim cholas asked, how many rows are being returned from the DB, and how many bytes?

    You say it takes 2 seconds to run the query on the database manually - is this running the same exact physical SQL? Against the same database? From the OBIEE server?

  • 3318159
    3318159 Rank 1 - Community Starter

    Hello,

    below are the points you have asked for.

    Number of rows returned - 19 to 25 rows

    is this running the same exact physical SQL? - Yes

    Against the same database? From the OBIEE server?  - Yes

    Row count from the Dimensions & facts involved are as below -

    D1-1819, D2 - 286, D3 - 127636, D4 - 2256479, F1 - 17178977

    Thanks,

    Sunil (on behalf of Vedavyas)

  • 3318159
    3318159 Rank 1 - Community Starter

    Yes Asim,

    We have done that as well, we have ran the report & immediately went to manage sessions. the entire time is being spent on the Database.

    the query keeps running on database, this we have checked at the database sessions as well through DBA's.

    Thanks,

    Sunil

  • rmoff
    rmoff Rank 6 - Analytics Lead

    OK, so you have a query that runs 2 seconds directly, or 323 seconds via OBIEE. You need to now identify what the difference is between the two executions.

    1) Get the DBAs to check the execution plan of the two queries is exactly the same

    2) Do a trace at the DB to see confirm if it is executing the query for 323 seconds, or if some of that time is lost elsewhere (e.g. network connections, SQL Net funniness, etc)

    3) If the query is indeed running for 323 seconds on the DB (with no time lost elsewhere) then take the Method-R approach to trace its execution and compare it to the manual-query execution to determine why the time difference is there.

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    can you filter out a single record and see how long it executes. Also run dimension and fact separately and find the run time.

    Thanks

    Asim