This content has been marked as final. Show 19 replies
Yep, my thoughts too, and I've asked already, but was told they "probably" need real time data.
But, we will find out for sure, and then I like your idea.
Re bitmap indexes you said this
Oh really? I see. That would be great. I like that idea. Thanks.
Fuggetaboutit! Get the thought of using a bitmap index out of your head. Unless you have a query that allows to Oracle to combine SEVERAL bitmap indexes (or at least two really good ones) a regular index will get the job done. Bitmap indexes are appropriate when that COMBINE process allows Oracle to identify rows of interest by ONLY using those bitmap indexes.
I'm also considering Jonathan's idea of using virtual columns, but since we can't modify the queries themselves, I'm not sure how helpful that would be. But we can modify the main view in this case. The main table they are selecting from is actually a view which is a query of the exact table (including all 560 columns including the CLOBS).
And unless REMEDY has changed their tune you can't add VIRTUAL columns, real columns or even indexes because you would have to add them to THEIR tables: all you (the client) has are views.
Now you are finally revealing what you should have told everyone up front (no fault of yours; that's just the way things are sometimes).
A little background for others:
The REMEDY application (for sure the version I worked with several years ago) only provides VIEWS to the client. Those views have the joins to the underlying tables pre-built. And those underlying tables have names like: T134, T83, T42, etc. The name-prefix might be 'T' for one type of table and 'B' for another type (e.g. the one with LOBs).
Care to guess what the column names are in each of those tables? You'd be correct if you guessed names using numbers: C1, C2, C3, etc.
So a view winds up with a definition something like:
The client doesn't know what the each table actually contains or what the different columns represent. That information IS stored in the master 'metadata' tables if you know where to look and how to join the tables properly.
SELECT T134.C3, C4, C5, C78345 FROM T123,B123 WHERE (T123.C1 = B123.C1 (+));
In short, a client cannot effectively query the actual data tables or even create their own views without risk that everything will break when the next REMEDY update is deployed.
Those tables and columns are generated dynamically when a user designs application screens and forms using the GUI designer. So a data table will get a table name of 'T' suffixed with a sequence number of the next available value. You know what I mean? Just like people in the forums want to do construct their own 'gap-free' surrogate keys that have their own prefix and a sequence value suffix.
Which means the client cannot effectively add indexes since the terrible, generic view definitions may prevent Oracle from using them anyway.
Back to OP
Unless REMEDY has changed their tune since the version I worked with you will either need to contract with them (the vendor) to write some custom views or you will need to create your own views that eliminate those awful joins to tables with LOB data that you don't even need.
At our site (a large national telecommunications company) they licensed Remedy's DSO option which replicated the ticket data to another database. Then we did the reporting from the backup database. Side note: yup! Remedy had their OWN replication option, which they of course licensed to their clients: they did not use Oracle replication.
Be cause of the problem you stated we had some Crystal reports that pulled two hundred thousand records just to produce a one-page summary report showing service-level violations for the trouble tickets. The report actuall took all the raw data and performed ALL filtering, grouping and sorting that couldn't be done at the database. Some of those reports were run every 5-10 minutes using real-time data to report on imminent violations of the SLA for high-visibility tickets.
I sympathize with your problem!
1. no bitmap indexes
2. use DSO or Oracle replication to create a backup/reporting database and use that
3. create a small number of custom views (using official app views as a guide) that eliminate the tables and columns you don't need.
Creating your own views isn't that hard to do if you base them on the official ones since those 'official' ones have useable column names that tell you what you are getting.
Oh my gosh! You are so right on it.
Okay, my comment regarding bitmap indexes was only to be used on the MV and used only for those queries joining multiple low-cardinality columns, as per Jonathan's comments above. You more or less said the same thing.
Yes, you are exactly correct about Remedy, and it is hard to explain that in a forum when asking for help. But thanks, because you pretty well nailed it.
I am also with a very large telecommunications company using Remedy, but we do not have the DSO.
Instead, I have been considering the recommendation of Active Data Guard.
We are already using Data Guard, but just not the "active" data guard option.
That's mainly because of the costs involved for the extra licensing.
Active Data Guard is still cheaper than Golden Gate (replication) which uses Active Data Guard anyway.
Okay, so basically what you are telling me is that there isn't a whole lot I can do. :-(
The metadata table you were referring to is called ARSCHEMA.
Yes, it can be used to figure out what columns in a table are used for.
It is a bit convoluted. Maybe that is an understatement. :-)
Okay, so basically what you are telling me is that there isn't a whole lot I can do.
The simplest, low-impact solution is to create your own views that eliminate the joins to tables that you don't need and eliminate the columns that you don't need. You should be able to tell from the 'official' view definition what columns you need and then use the ARSCHEMA table and SELECT statement from the view to determine which tables correspond to those columns.
My question now would be that with using the MV is what would be the performance impact of have an MV that is updated every time a change is made on the main table since the main table is so active?
Using an MV log will be the next lowest-impact solution and I would not expect the performance impact to be of any concern for your use case.
But that won't avoid the problem I mentioned earlier. For an MV to be fast refreshable The MV log has to be on the base table(s) which means on the 'Tnnn' table - not on the view. And that means that the actual MV table will have the same obtuse column names that the orginal table has.
So while an MV can efficiently capture the table changes you still have the same issue of how to efficiently query the columns that have the odd names; back to the views again except now they would be on your MV.
Based on my own experience dealing with Remedy DB issues I suggest you test first using your own modified views on the base tables that eliminate the tables and columns that you don't need.
Then if you can't get the requisite performance explore the MV option further.
The third option might be to use MVs to replicate the lookup tables (with the group info) and put indexes on them that help you eliminate those LIKE predicates. The DML activity on the lookup tables should be minimal so even if you used bitmap indexes on the columns it should have relatively minor impact. Anything you can do to reduce or eliminate the LIKE predicates is likely to give you the most bang for the buck.
Thank you again for all your input.
Okay, I agree. So, I was thinking to create a view on the T2179 (the main application table for all tickets), but obviously without the CLOB columns and other columns we don't need. This is the main (15G) table that keeps getting fully scanned and reloaded into buffer_cache so many times a day by these reports. And most of them do not join any other tables, just pulling from this table alone. Some reports, of course, join other tables too, but most of them all include the T2179 table and manage to do a FTS on it every time.
Yep, using the arschema table is a bit tricky to get the right columns, but we have some developers here that should be able to do that.
I'm not sure what you mean by how to eliminate the LIKE predicate. We can't modify the GUI interface that generates the SQL that uses the LIKE predicates, even if I create MVs that would be efficient without it.
Thanks again for your suggestions and of your knowledge of Remedy.
It is very helpful in my case.