Hotsos Symposium Speaker – Alex Haralampiev

Mr. Haralampiev is a new Hotsos Symposium presenter.

Biography

Alex HaralampievAlex Haralampiev, M.Eng., Ph.D., has over twenty years of experience as a research scientist, software developer, systems administrator and data warehouse architect. He designed and implemented data marts and ETL data flows for number of Fortune 500 companies in the financial, hospitality and wholesale distribution industries. Alex is proficient working with broad range of operating systems and database platforms and he is a certified Oracle and Teradata professional.

Presentation Title

Session: When a Good Design Goes Bad

Abstract

An enormous boost in BI report performance results when Oracle join bitmap indexes (JBIX) are positioned like a bridge between the dimensions and facts for star schemas. Implementations that use materialized views (MV) capable of FAST MV refreshes avoid the "dangers of mixing bitmap indexes and data updates". (Lewis, 2006). Dropping and rebuilding the indexes is unnecessary, because refreshing a materialized view automatically updates them. This design pattern was successfully implemented during a data mart migration from Teradata to Oracle (50 million transaction records, 16 dimensions and 34 indexes of which 8 JBIX). Refresh times for the MV based BI layer were 2 to 2 1/2 hours; however, on random exceptions the refresh wouldn't complete in 12+ hours. Root cause determination was difficult, as superficially it was a few simple calls to DBMS_MVIEW.REFRESH.

This presentation summarizes the results from the analysis of extensive event 10046/10053 trace data and describes how judiciously applying Method R (Millsap and Holt, 2003), traces the root causes for performance problems in two categories:

  • Random (or maybe not so random) performance issues — e.g., related to BITMAP JOIN INDEX UPDATE (UPD_JOININDEX) process misbehaving when the base table stats are being collected simultaneously; and
  • "The usual suspects" — e.g., init.ora settings, missing indexes on materialized view log tables, frequent statistics collection, and unnecessary updating of records.

Eliminating these problems resulted in stability of the nightly MV refresh: 40-50% (1-1.5 hrs) improvements over the past "good" runs.

Presentation Materials

Presentation materials are available online to attendees only. Alex's presentation slides and white paper will be available upon receipt.

Schedule

The speaker schedule is as follows: