OBIEE Ad Hoc performance

Hesh

Hi,

12.2.1.0.0 version

My client is experiencing performance issues with OBIEE, I know that it sounds monotonous but I have to understand how to get there.

What I understand as of now is not all the time this is happening with my OBIEE application, but during the peak times when most of the users are actively pulling the reports its causing delays for everyone .... delays starts with 5 min then 10 min then.. goes on to 20..30 min!

I have tried S_NQ_ACCT table to see what actually happening with the user request, I don not think I am reading that table properly!

When I took the difference between 'end_ts' & 'start_ts' values... that value is different from 'resp_time_sec' column. And more to say, few queries which got more than 30 min difference between 'end_ts' & 'start_ts'  were actually run very fast as per user!

One common pattern what I have observed is when the performance issue reported, that time and few min before a big queue of user requests are in '[nQSError: 46066] Operation cancelled.' in the column 'error_text'... so may be users cancelling the requests once they are experienced with performance.

What would be a better way to analyze things here? sometimes I believe it might not be a DB issue, somewhere in Weblogic server is hanging when many users are pulling reports... but I don not want to 'assume', I want to know if that true!

Thanks

Hesh

Sherry George

Hi Hesh,

Can't advice much without being actually there.

Take a look at these links, if you haven't already. They will give you a good idea of how to better understand the issue, troubleshoot and then possibly a fix.

http://www.clearpeaks.com/blog/oracle-bi-ee-11g/obiee-11g-tuning-and-performance-monitoring

https://speakerdeck.com/rmoff/still-no-silver-bullets-obiee-12c-performance-in-the-real-world

Robert Angel

I agree no magic bullets, but; -

Have you looked into caching strategies, particularly that preseed cache for most used reports?

Do you run standard reports on a timed basis out of hours?

Do you write your reports to ensure underlying indexes can be used?

Do you have pre-aggregated content for reports that aggregate a lot of data?

On your problems have you tried ramping your diagnostics to max (which will make performance worse) and running the worst offenders.

Also worth getting your DBA to have a look from his side of the server.

Gianni Ceresa

Sherry George gave you some good resources on how to start having a clear picture of what happen.

As a side note you maybe want to suggest your client to upgrade to a still supported version as 12.2.1.0.0 as Error correction support ended last June (https://support.oracle.com/rs?type=doc&id=1664916.1)

Hesh

Thank you all.

I will read the resources provided by Sherry George.... I have one more question about  S_NQ_ACCT table , can I trust its stats? Do the column TOTAL_TIME_SEC  represents the total time taken by the user query? i would like to use this to get the queries which are taking more time and try to do more analysis on those...

Regards

Hesh

Gianni Ceresa

I would say you can globally trust it, as you aren't looking for factual execution times but more to define priorities of what object / analysis to look at first.

Of course you must not work on the MAX only, keep in mind you also need to consider the number of occurrences, the average and the median to define your priorities (improve the performance of a single analysis taking long but which is called only once per year versus another one being a bit faster but used 123422 times per day).