We’re using TOAD 12 to manage our Oracle 11g database which is used for static analysis -load data, analyze, load data, analyze, repeat (vs Transactional)…
We would like to create a “mini Warehouse” and agggregate our data for comparative analysis. We will be using the same hardware, howeever we have a separate drive array available that I would like to utilize.
Since they will be on the same machine, I dont see any advantage to creating anothe Oracle instance, nor do I know how. Please correct me if there is an advantage to two Oracle instances on the same machine (sounds like a mess, and with licensing as well!)
In Toad, I can create a new tablespace and click on the tab to add a datafile. If I click on the browse button there, it brings up a grid of all my exisitng datafiles. My current datafiles are on the E:\drive and I wish the new ones to be on the F:\drive. How can this be done?
When you create a new tablespace, you can put in whatever path you want for the files (as long as it’s a path that Oracle has privileges to use). We just bring up the existing files in the ‘browse’ dialog because typically they are all in the same place, and we’re just trying to save you some typing.
The only disadvantage I can think of off hand to a single instance is that you’re aggregate and non-aggregate tables will share the same SGA (buffer cache) and compete for it. So you might not be able to accurately measure the true value of the aggregates. I don’t mean it won’t work. But maybe the N% improvement might actually be greater than N% had there not been competition for resources. If you do want to stay with one instance then you should test your queries against the non-aggregates - time those. Flush the buffer cache or even bounce the instance and then test the aggregates. The idea being to eliminate competition for the purpose of getting a true difference reading.
So are you using materialized views for your aggregates and for keeping them up to date (i.e. refreshed)? Or are you doing it manually. Or are you just creating tables and doing everything with custom code? MV’s can be a huge advantage when done right, because you can “instruct” oracle (via db design) that when a query comes in use the base table unless the aggregate is a better match. That way you can use the same old queries and let the database decide what tables to hit to get the info in the quickest way possible …
Thanks -
Bert - can you elaborate on the MV advantage you mentioned? I’ve heard about MV but could never understand the advantage - seems like a hard Table would do the same.
if you have table_a which is the huge base table, and table_b which is your manually created aggregate, then your user or BI tool must manually say which table to query. So you would have to rewrite all your reports to use the new table. Then if some users say they report one way and other users say they report another - you’d create different aggregates for each user, and now you’d need to create custom reports to hit the right aggregates for each user. Way, way too much qork - and too hard to keep straight which users and who’s reports hit which tables.
So now instead you have table_a again which is your base table. Now you create an MV which we’ll call table_c. Your reports do not need to have their code changed. You can instruct oracle as to how table_c is summarized along table_a - and all queries/reports still go against table_a in terms of the SQL code. But Oracle now knows when user says give me monthly totals against a daily table where there is an MV which has either weekly or motnly values, the Oracle optimizer will redirect the query against the MV. If later you add more aggregate MV’s, the optimizer will always auto choose for each query where to go. And if you later drop an MV, add more, or quit using them altogether - your reports and SQL code never changed so no coding ramifications.
basically if you need aggregates - MV’s are mana from heaven and should be used in 90% of the cases without question. There are times where they are not the best match - but one should start from the position of if I need an aggregate I use an MV unless I know for a fact that I have a special case need that falls into the 10% cases where MV’s are not good.