Posted by: michaelverzijl | 05/10/2011

Review Session Day 3: Oracle Open World

Today was day three of Oracle OpenWorld.
The day started with a keynote of Michael Dell (from Dell). In this keynote he announced some new hardware from Dell.

Following Michael was John Fowler from Oracle. He started with a recap of the Exa* family (again).
After this John talked about a new release  of the Unbreakable Linux kernel.



The first real session was: Aggregation: BI Server versus Oracle Optimizer
Presented by Stewart Bryson

There are 3 ways you can optimize a query:

  1. Manual
  2. BIServer
  3. Oracle Optimizer

Manual optimize queries is something you shouldn’t do. You make the RPD far to complex and don’t use the power provided to you from Oracle.

BIServer optimizations are seen more often and are a good way of optimizing your query. You can use the aggregate persistence utility inside the RPD.

With this utility you can add the metadata to the repository and make an initial load for the aggregate tables in the DWH. If you want to use this more often you have to write some ETL to fill this aggregate table.

The best way to optimize a query is to user the Oracle Optimizer. You can create materialized views and with query rewrite on you can let the database rewrite those queries.
A best practise of Stewart is to create the dimensions inside the database through CREATE DIMENSION. On this way to optimizer knows of the existence of these objects.

To make the materialized view work follow these steps:

  • Partition your fact table
  • Partition your materializes view
  • Create the materialized view only on the fact table (so that this can easily be reused)
  • Include the surrogate keys for the dimensions you want to have available inside the materialized view
  • Add your measures and don’t forget to add an extra COUNT(MEASURE) inside the materialized view (so that the optimizer can see this)

Furthermore DBMS_ADVISOR provided by Oracle is a good tool to notify on missing materialized views in your database.



After this session I did the Oracle GoldenGate 10g Essentials exam and passed. Therefore I’m now a Certified GoldenGate implementation consultant.



The last two session were on Exadata with a DWH solution on it.

Four customers did a showcase of what they have made on Exadata.

The following advice was given:

  • Take note of your DB size and make a good planning
  • Endian format conversion is important to notice
  • Version difference can be a problem (if you have the time migrate first to 11gR2 before migrating to Exadata
  • Do a “lift-and-shift” instead of a real migration
  • Don’t tune and use the Exadata features before successfully migrated
  • Backup is something that needs to be addressed and can take some time to arrange
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories

%d bloggers like this: