nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE - Report Performance Issue

3340831

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

Find more posts tagged with

Accepted answers

All comments

Joel Acha

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

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

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

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

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

rmoff

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

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

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

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

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