1	OraclePerformance Cube Using Microsoft OLAP technology to manage and monitor performance of an Oracle 10g RAC database Richard Lees November 2006 Richard@EasternMining.com.au EasternMining.com.au
2	Summary This is a tool that supports ad hoc and predefined analysis of Oracle 10g databases. It is particularly useful where the database has a high performance requirement. Essentially it takes Oracle ADW tables, adds some metadata and continually puts this information into an OLAP cube for easy, flexible and powerful analysis. This tool does not add any performance overhead to your production database It only uses data already collected by Oracle, but not easily accessible and not well documented.
3	Why was the OraclePerformance Cube created? Richard Lees was working in an Oracle 6 node RAC environment where performance and continuous availability was paramount. It became obvious to Richard that there was a wealth of information within Oracle that was not being used, and occasionally being misinterpreted. For example, IO performance was an often discussed issue. How much IO was the database demanding and what service time was it receiving. All this information is available from within Oracle, but it was difficult to extract. IO data was the first data to go into the cube. It was easy to show vendors, management and architects the IO demands throughout the day, trends over time, and performance experience. Next off the rank was the SQL statement data, which enabled easy access to the high consumers, poor performers and sudden changes in SQL performance. The OraclePerformance cube was then extended to add system stats, parameters, scheduled jobs, memory pools, enqueues, host metrics, buffer pool, service stats, session wait and system events. Essentially every performance metric available to database administrators was now available from within an Excel PivotTable for slice and dice analysis, drill down, pivoting, charting, trend analysis etc. As icing on the cake, several key reports were put on Reporting Services so that the masses could see information relevant to them.
4	Oracle Enterprise Manager
5	AWR tables (used in cube)
6	AWR Sample Data
7	Incremental Load Package
8	OLAP Cube Picture
9	OLAP Dimensions and Measures
10	Ad hoc querying – Write IO
11	Ad hoc querying - Top 20 SQL
12	Ad hoc querying - Read IO
13	Ad hoc querying - Library Cache Size
14	Ad hoc querying – CPU Utilisation
15	Ad hoc querying – Shared Pool Free Memory
16	Dynamic Reporting with HTML
17	Dynamic Reporting – Write IO
18	Dynamic Reporting – Read IO
19	Dynamic Reporting - IO
20	Dynamic Reporting – Write IO
21	Dynamic Reporting – Read IO Daily Cycle
22	Dynamic Reporting – File System Free Space
23	Dynamic Reporting – UNIX Free Memory
24	Dynamic Reporting – Top 20 Queries by Buffer Gets
25	Dynamic Reporting – SQL Statement Analysis
26	Dynamic Reporting – Storage by Table
27	Dynamic Reporting – Storage by Tablespace
28	Dynamic Reporting – Bind Variable Peaking
29	Dynamic Reporting – SQL Hash Plans
30	Dynamic Reporting – SQL Query Analysis
31	Dynamic Reporting – Concurrent Connections
32	Dynamic Reporting – Connection Rate
33	Dynamic Reporting – Connection Reuse
34	Dynamic Reporting – System Metrics
35	Dynamic Reporting – Library Cache Size
36	Dynamic Reporting – Memory Headlines
37	Dynamic Reporting – Job Scheduler
38	Dynamic Reporting – Query Search
39	OraclePerformance Wrap There are an infinite number of reports that can be created, these are just a sample. Using OLAP technology with the multiple measure groups and dozens of hierarchies there is a huge amount of information that is easy to access via html reports or a rich cube browser like Excel PivotTable