Categories
Need help improving Usage Tracking performance
We use Usage Tracking quite heavily, but the performance is very poor, sometimes taking many minutes for analysis to complete.
The dashboards we've created show things such as number of users per day, number of queries per day, etc. Just basic calculations.
The only Oracle database we use is the one used for Usage tracking. The rest of our data bases are SQL Server, and
compared to the Oracle database, they are lightning fast. We routinely do complex business calculations, number conversions, etc on tables
containing eighty million records, using views, complex CASE statements, etc, and the wait times are at most, a couple of seconds, yet the Usage Tracking screens are extremely slow.
Since I dont have much experience with Oracle databases, I need some suggestions as to what to look for. We are using the
"Out of the Box" usage tracking, and our tables contain around two million records. The only tools I have are SQL Developer, the
OBIEE log files, and Usage Tracking information itself.
How can I begin to figure out what is going on here and why Usage Tracking response time is so slow?
Answers
-
The tables themselves aren't really optimized for querying but for inserts.
Best you offload the data into othee, quer-optimiued tables with nice indices and maybe partitionig...best talk to you DBA since this all heavil depends on your local setup.
0 -
I think 'Chpater 16: Usage Tracking' of 'Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial' book covers the topic and will be useful to you. Hope you get a chance to go through it.
Regards,
Manoj.
0 -
What Christian said for sure will work.
Just one question: it sounds like your organization is good with SQL Server and know how to best tune it, so why did you use an Oracle database for the OBIEE RCU (so the usage tracking as well) ?
Pay an Oracle DB license just for that and lacking the DBA-side knowledge to best manage it sounds like there is wasted money and ressources there.
The way I read your message I would say that before to try to tune a table you probably better try to understand what your DB is doing ...
Is it really the Oracle DB taking time when running queries? (so if you make a time profile of your analysis it's really the DB spending time?)
What happen when your DB is taking time?
Oracle DBA is a full time job, can't you just move the usage tracking on your SQL Server and compare performances ?
0 -
Heh the joys of speed-reading. Didnt fully take into account the SQL Server bit.
+1 to Gianni: why not just move to SQL Server.
0 -
Usage tracking was originally on SQL Server. At some point, a management decision (above my pay grade) was made to move it to the "Out of the box" configuration using an Oracle database. We already use Oracle data bases, so licensing/cost was not an issue. Performance on the Oracle database is worse, so I have been tasked with trying to figure out what the problems may be. Unfortunately, I am a metadata developer, not a DBA. Our Oracle DBA's seem to have a very narrow focus, and other than running some statistics, they have offered little help. Basically, they want to be told what the problems are, and then they will fix them. They want specifics and I cant give them specifics because there is no one query or dashboard that stands out as being "slow". It's just an overall type situation. That's why I am reaching out to determine if others have had this issue, and to find out what they did to address it. I also realize that all environments are different, I was just hoping to establish a list of "You need to do this" items that are best practice, along with a list of "Dont do this" items.
0 -
To make it short the usage tracking table is setup for insert and not for analysis, so nothing is done on the table to make it fast for analytical needs.
So going back to what Christian posted you can move data out of it and create table (one or many) optimized for your analysis needs.
It can be real tables or also materialized views.
0 -
I'd partition the table, probably on date. Get your DBAs doing that, and then take it from there.
0
