Hello,
After reading thru the posts, looks like this topic has already been discussed several times. But if someone has any different suggestion to try that will really help… So now the issue…
We keep 45 or so worth of data in the main set of TN tables. For every transaction, we extract and save a bunch of attributes. Our bizdoc table has about 20 millions rows and attribute table has about 300 million. When we query on the transaction analysis page on MWS, it usually times out because those queries are taking about 15-20 mins to run. Sometimes even more.
What have we tried so far?
- Indexing. We had our DBAs look at the query explain plans and the indexes were added appropriately. We have the index on stringvalue and doctimestamp in bizdoc as example (There are several others).
- we are running stats pretty much every day.
What answer am I looking for?
- Does anyone have experience with this kind of data volume?
- Can I override the query generated by the MWS Transaction Analysis page. There are certain predicates that are slowing us down like upper(corporationname) (We do have a FBI on upper(corporationname) !!). I think if we write our own query it can return the results faster.
- We are on 8.2 (oracle 11.2g) soon upgrading to 9.x (oracle 12c). Does 9.x have any better features for query performance?
Appreciate your help.
Thanks,
Vikas.