If you are using compression to reduce the size of your cube, you should experiment with leaving the topmost level in some dimension hierarchies as unsummarised, as this can decrease the time required to maintain the cube at the cost of slightly slower queries, due to the way that the compression algorithm works.
Of course, the cost of pre-aggregating data is the additional time, and space, that this part of the load process will take up, and therefore a test was carried out using the Global Widgets sample data to see how much additional time and space pre-summarisation took up.
For the purposes of this test, the three versions of the Global Widgets cube dense, sparse and compressed were loaded first using skip-level aggregation, and then using full pre-aggregation. The results of the test were as follows:.
Note that these measurements are specific to the Global Widgets cube, and other cubes with differing or additional dimensions, measures, hierarchies and levels will produce different timings and differentials. One way of speeding up data loads and pre-aggregation is to not load the data in the first place. In the same way that with relational data, you can use functions and views to derive results from table columns, you can create calculations and formulas to dynamically generate measures or dimension members based on data you have loaded into your cube.
Calculations are something that you should already be familiar with, and are defined using the Calculated Measures feature using Analytic Workspace Manager 10 g Figure 3. Figure 3: Creating a calculated measure using Analytic Workspace Manager 10g. The advantage of defining a measure as a calculated measure is that it does not take up any storage space except for the metadata associated with the calculation and it does not require loading or pre-aggregating.
The downside, of course, is that a calculated measure will usually be slightly slower to query than a measure that has been fully loaded and aggregated. Previous users of Oracle Express Server will recognise calculated measures as a type of Express construct known as a formula. Formulas are very flexible objects and are also fully supported in Oracle OLAP, but they are only exposed in a very limited way with Analytic Workspace Manager 10 g.
The first load of data will usually take all current and historic data and load it into your Analytic Workspace. After that, you will usually want to load just new data, and data that has changed, into your Analytic Workspace rather than delete the cube and rebuild it from scratch. To use this approach, you will need to carry out the following steps. Figure 4: Selecting the cube processing option using Analytic Workspace Manager 10g. As an alternative to remapping your dimension and cube loads to point to tables that only contain new and changed data, another technique that has been used successfully in the past is to do the following:.
Using the Global Widgets dataset, timings were taken using the sparse version of the cube, using the following scenarios:.
The obvious conclusion from these tests was surprising and shows that, regardless of whether you load just the new and changed data into the cube or load all data, or whether you choose to aggregate just the new values or all values, with Oracle Database 10g Release 1 there is no difference in the time taken to perform cube maintenance.
It is, however, my understanding that this issue is, in fact, being addressed in Oracle Database 10g Release 2, and incremental loads will be significantly faster with this release. To illustrate how this works, the template for the sparse version of the cube was loaded into Analytic Workspace Manager 10 g , and the option to maintain the cube was selected. Figure 5: Saving a maintenance task to script using Analytic Workspace Manager 10g.
This is the log that Analytic Workspace Manager displays after a load has completed, but by querying this table, you can check the progress of the load whilst it is running. Disable Logging during build to improve overall load performance. Once load is complete turn it on. Use ADDM to determine the ideal size. This could be crucial for poorly performing disk subsystems. No RAID5. RAID 5 can severely affect performance on highly updated databases. Once you have loaded and aggregated your cube, the next area that you will want to optimise is the performance of your queries.
What this means in practice is that you will need to look at the following two areas:. In practice, this means that with current 10 g Release 1 versions of Oracle OLAP, tools such as Discoverer for OLAP will be slower to produce query results than you would have expected, given the fact that you have pre-summarised data and you are working with an OLAP-aware query tool.
There is not much you can do about this at the moment, although Release 2 of Oracle Database 10 g promises optimisations to this interface layer. We will look in more detail at the use of the Model clause to boost performance later on in this section. The most important time model statistic is the DB time.
It shows the total time spent by the sessions in database calls. It is equivalent to the sum of CPU time and wait times of all sessions not waiting on events classified by the Idle wait class. However, it is timed separately. The following breakdown of the time model statistics shows which statistics are subsets :. If the session spends less time in database calls, it is performing better.
Your tuning goal should be to reduce the overall DB time for the session. By now, wait model statistics are nothing new to you. Oracle Database 10 g Release 1 tracks over wait events to report time spent by the session waiting on those events. These are classified in 12 wait classes. This classification allows easier high-level analysis of the wait events. The classification is based on the solution that normally applies to correcting a problem with the wait event. Operating systems statistics provide information about system resources utilization such as CPU, memory, and file systems.
In Oracle versions prior to Oracle Database 10 g , some of these statistics were not available from within the database.
You had to issue OS commands or use OS level tools to gather machine-level statistics to investigate hardware-related issues. As we discussed in Chapter 2, you have to take snapshots at various intervals to find the rate of change in the statistics values reported by these views.
If you're searching for the fastest solution to a t-sql problem add the word 'itzik' to your google search term. Itzik Ben-Gan has been mentioned over times here on stackoverflow , but I couldn't believe it to find not a single mention of him here on this performance tuning question. As an additional resource, you can also find some videos of Itzik talking about performance related stuff here on youtube. Use Stack Overflow for Teams at work to find answers in a private and secure environment. Get your first 10 users free. Sign up. Learn more.
First 10 Free. What resources exist for Database performance-tuning?
Asked 10 years, 5 months ago. Active 2 months ago. Viewed 14k times.
Neil Kodner. You're lucky if you can find one. For Oracle, that person is Tom Kyte. Apr 8 '16 at This guy's answer to a not-the-same-inquiry is probably a good start. Milen A. Quick PostgreSQL Optimization query optimizing Short read, explains a lot of things well and 'works' a real example which is nice for those of us that learn better that way.
Nathan Voxland. SQL Server Resources. John Sansom. Christopher Klein.