Oracle materialized views and partitioning benchmarking

Oracle Tips by Burleson Consulting
 

See my related notes on partitioned Materialized Views here:

• Materialized Views Tuning

• Materialized Views Refreshing Performance

Materialized views are an Oracle Silver Bullet when pre-joining tables together for super-fast response time. 

One issue with highly-normalized, non-redundant Oracle table designs (e.g. third normal form) is that Oracle experiences a high degree of overhead (especially CPU consumption) when joining dozens of tables together, over-and-over again, and partitioning may help.

Using materialized views we pre-join the tables together, resulting in a single, fat, wide and highly-redundant table.

This can reduce logical I/O from tens of thousands to a single row fetch, resulting in blisteringly fast response time, but careful attention must be paid to choosing the proper materialized view partition keys and the best refresh interval.

The Oracle 10g SQL*Access advisor utility advises on recommended materialized views, but it does not advise us about the proper method for partitioning the materialization.  Here are directions for installing basic materialized views.  Dr. Arun Kumar, author of the bestselling book “Easy Oracle Automation: Oracle10g Automatic Storage, Memory and Diagnostic Features” also has some excellent advice for using the Oracle 10g SQLAccess advisor for generating materialized view suggestions.

The problem with materialized view for pre-joined tables is keeping them refreshed.  Because the materialized view is built from many tables, and changes to the base tables require an update to the materialized view.

Remember, If someone has a row locked for update then you get a consistent read block from the rollback segment when you read. You aren’t blocked. You just can’t see the change until it is committed.

The following research by Mike Ault illustrates this important technique and shows how proper partition analyze can allow the Oracle DBA to de-normalize a schema for high performance without affecting high volumes of concurrent users.

Oracle Partitioned, Refresh on Commit, Materialized View Testing

Mike Ault
Burleson Consulting

One of the suggested architectures to allow for rapid reporting without stressing the base tables is to use partitioned, refresh on commit, materialized views. While this has been tested in a single user setup, true stress testing has not been accomplished. It is hoped this test will help show the affects of user load on such an architecture.

In order to test this architecture the Quest Benchmark Factory was utilized with two-main GUI installations; one to use to perform the INSERT into the base tables, the other to perform the SELECT activity against the refresh on commit materialized view. The testing was performed in three phases:

Phase 1:

In phase one both the INSERT and SELECT potions of the test were cycled from 1-60 users in 5 user increments on the INSERT side and 1-30 users in 5 user increments on the SELECT side simultaneously.

Phase 2:

In phase two the INSERT side was cycled from 1-60 users in 5 user increments until the response time exceeded 6 seconds while the SELECT side was run at a constant user level during the INSERT runs. The SELECT side was run at constant user levels of 5, 10 and 20 users.

Phase 3:

In phase 3 the materialized view was recreated as a single table and the constant user level of 20 for SELECTs was used to test the difference between use of partitions and single tables.

All Phases:

In all phases the SALES table was used for the update target with ON COMMIT processing for the materialized views causing selects from all the base tables in the PUBS schema (SALES, AUTHOR, BOOK, AUTHOR_BOOK, PUBLISHER, STORE) to publish data into the MV_AUTHOR_SALES materialized view.

Prior to each test the MV_AUTHOR_SALES materialized view and the SALES table were both truncated.

System Information:

The test system consisted of two laptops each configured with the Benchmark Factory utility. Laptop 1, a VIO PCG-GRT250P with 1 gigabyte of memory and a 2.8 Ghz Pentium IV processor and a 1 GBit Enet card was used for running the INSERT processing. Laptop 2, a Gateway 9300 with a 400 Mhz PII processor and 700 Megabytes of memory with a 100 Mbit Enet card was used for the SELECT processing.

The database server is a Redhat Linux based 3.0 Ghz Pentium IV with hyperthreading and 2 Gigabytes of memory running Oracle Enterprise 10gR2 10.1.0.3 release. The server is direct attached through Fast-Wide SCSI to a 8 disk NStore disk array using all 8-19 Gigabyte 10K rpm drives in a RAID5 array. This architecture is shown in Figure 1.

Figure 1: Test Architecture

Details of the Architecture are as follows:

OS Version:

[oracle@aultlinux3 ~]$ uname -a

Linux aultlinux3 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

CPU (One 3 Ghz, hyperthreading)
 

[oracle@aultlinux3 proc]$ more cpuinfo

processor       : 0

vendor_id       : GenuineIntel

cpu family      : 15

model           : 2

model name      : Intel(R) Pentium(R) 4 CPU 3.00GHz

stepping        : 9

cpu MHz         : 2998.822

cache size      : 512 KB

physical id     : 0

siblings        : 2

fdiv_bug        : no

hlt_bug         : no

f00f_bug        : no

coma_bug        : no

fpu             : yes

fpu_exception   : yes

cpuid level     : 2

wp              : yes

flags           : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pb

e cid xtpr

bogomips        : 5914.62

 

processor       : 1

vendor_id       : GenuineIntel

cpu family      : 15

model           : 2

model name      : Intel(R) Pentium(R) 4 CPU 3.00GHz

stepping        : 9

cpu MHz         : 2998.822

cache size      : 512 KB

physical id     : 0

siblings        : 2

fdiv_bug        : no

hlt_bug         : no

f00f_bug        : no

coma_bug        : no

fpu             : yes

fpu_exception   : yes

cpuid level     : 2

wp              : yes

flags           : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pb

e cid xtpr

bogomips        : 5980.16

[oracle@aultlinux3 proc]$ more meminfo

MemTotal:      2074236 kB

MemFree:         11492 kB

Buffers:         98872 kB

Cached:        1605096 kB

SwapCached:         72 kB

Active:        1532024 kB

Inactive:       397312 kB

HighTotal:     1178816 kB

HighFree:         1536 kB

LowTotal:       895420 kB

LowFree:          9956 kB

SwapTotal:     2031608 kB

SwapFree:      2031456 kB

Dirty:             132 kB

Writeback:           0 kB

Mapped:         968348 kB

Slab:            67264 kB

Committed_AS:  2212852 kB

PageTables:      45360 kB

VmallocTotal:   106488 kB

VmallocUsed:      4040 kB

VmallocChunk:   102360 kB

HugePages_Total:     0

HugePages_Free:      0

Hugepagesize:     2048 kB

RAID:

 

This is a NStore SCSI attached 8 Disk Array

 

[root@aultlinux3 ~]# mdadm -Q --detail /dev/md0

/dev/md0:

        Version : 00.90.01

  Creation Time : Mon May  9 14:32:32 2005

     Raid Level : raid5

     Array Size : 124479488 (118.71 GiB 127.47 GB)

    Device Size : 17782784 (16.96 GiB 18.21 GB)

   Raid Devices : 8

  Total Devices : 8

Preferred Minor : 0

    Persistence : Superblock is persistent

 

    Update Time : Sun Mar 12 11:01:19 2006

          State : clean

 Active Devices : 8

Working Devices : 8

 Failed Devices : 0

  Spare Devices : 0

 

         Layout : left-symmetric

     Chunk Size : 1024K

 

    Number   Major   Minor   RaidDevice State

       0       8       16        0      active sync   /dev/sdb

       1       8       32        1      active sync   /dev/sdc

       2       8       48        2      active sync   /dev/sdd

       3       8       64        3      active sync   /dev/sde

       4       8       80        4      active sync   /dev/sdf

       5       8       96        5      active sync   /dev/sdg

       6       8      112        6      active sync   /dev/sdh

       7       8      128        7      active sync   /dev/sdi

           UUID : cadfa665:da15b8cc:95ee2807:a4dd69b3

         Events : 0.260263

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod

PL/SQL Release 10.1.0.3.0 - Production

CORE    10.1.0.3.0      Production

TNS for Linux: Version 10.1.0.3.0 - Production

NLSRTL Version 10.1.0.3.0 - Production

Network: Ethernet through Hubs and Nics

All networks 1 GHZ or minimum of 100 Mghz

Database Objects

The database utilizes a base set of tables, SALES, AUTHOR, BOOK, AUTHOR_BOOK, STORE and PUBLISHER. These tables are used to create a REFRESH-ON-COMMIT materialized view constructed on top of an existing partitioned table.

Details of Materialized View:

The script used to create the materialized view base table is shown in Figure 2.

drop materialized view mv_author_sales;

drop table mv_author_sales;

truncate table sales;

CREATE TABLE mv_author_sales

      PARTITION BY RANGE (order_date)

         (PARTITION p1  VALUES LESS THAN (to_date('012002','mmyyyy')),

          PARTITION p2  VALUES LESS THAN (to_date('022002','mmyyyy')),

          PARTITION p3  VALUES LESS THAN (to_date('032002','mmyyyy')),

          PARTITION p4  VALUES LESS THAN (to_date('042002','mmyyyy')),

          PARTITION p5  VALUES LESS THAN (to_date('052002','mmyyyy')),

          PARTITION p6  VALUES LESS THAN (to_date('062002','mmyyyy')),

          PARTITION p7  VALUES LESS THAN (to_date('072002','mmyyyy')),

          PARTITION p8  VALUES LESS THAN (to_date('082002','mmyyyy')),

          PARTITION p9  VALUES LESS THAN (to_date('092002','mmyyyy')),

          PARTITION p10 VALUES LESS THAN (to_date('102002','mmyyyy')),

          PARTITION p11 VALUES LESS THAN (to_date('112002','mmyyyy')),

          PARTITION p12 VALUES LESS THAN (to_date('122002','mmyyyy')),

          PARTITION p13 VALUES LESS THAN (MAXVALUE))

as

(Select d.order_date,

a.rowid idrowa, b.rowid idrowb, c.rowid idrowc,

d.rowid idrowd, e.rowid idrowe, f.rowid idrowf,

a.author_last_name, a.author_first_name,f.pub_name,

a.author_contract_nbr,

e.store_state,d.quantity

From author a, book_author b, book c, sales d, store e, publisher f

Where a.author_key=b.author_key

And b.book_key=c.book_key And c.book_key=d.book_key

And e.store_key=d.store_key

and c.pub_key=f.pub_key)

/

create index mv_rida on mv_author_sales(idrowa);

create index mv_ridb on mv_author_sales(idrowb);

create index mv_ridc on mv_author_sales(idrowc);

create index mv_ridd on mv_author_sales(idrowd);

create index mv_ride on mv_author_sales(idrowe);

create index mv_ridf on mv_author_sales(idrowf);

Figure 2: Script to Create Partitioned Table

Once the base table is created, the materialized view is defined on the existing table. The script to create the materialized view is shown in Figure 3.

Create materialized view  mv_author_sales

on prebuilt table

Refresh on commit

as

Select d.order_date,a.rowid idrowa, b.rowid idrowb, c.rowid idrowc, d.rowid idrowd, e.rowid idrowe, f.rowid idrowf, a.author_last_name, a.author_first_name,f.pub_name,

a.author_contract_nbr,

e.store_state,d.quantity

From author a, book_author b, book c, sales d, store e, publisher f

Where a.author_key=b.author_key

And b.book_key=c.book_key And c.book_key=d.book_key

And e.store_key=d.store_key

and c.pub_key=f.pub_key

/

Figure 3: Materialized View Creation Script

After creation and refresh the MV_AUTHOR_SALES and SALES tables we analyzed using a command similar to:

dbms_stats.gather_table_stats('PUBS','MV_AUTHOR_SALES',cascade=>true);

The dynamic sampling feature of 10g was utilized to maintain statitistics for the test since the table and materialized view were growing during the entire test period for each test.

Transaction Details:

Two basic transactions were utilized to test the affect of locking on the INSERT and SELECT activities. The SALES table formed the base of the materialized view MV_AUTHOR_SALES so the INSERT transaction focused on inserts into the SALES table. The inserts into the SALES table force the materialized view refresh (REFRESH-ON-COMMT) to select records from all of the base tables. The following Benchmark Factory function scripts where used to populate random values into the INSERT statement:

• $BFRandList – Insert one of the provided list into the statement at this point with frequency based on the provided integer (“val”:f) if no integer is provided, use 1.
• $BFRandRange – Insert a random integer in the range specified.
• $BFDate – Insert a random date in the range specified.

Insert Transaction:

insert into sales values (

 '$BFRandList("S101","S103","S103","S104","S105","S106","S107","S108",

"S109","S110")',

'$BFRandList("B101","B102","B103","B104","B105","B106","B107","B108","B109","B110","B111","B112","B113","B114","B115","B116")',

 'O'||to_char(order_number.nextval),

 to_date('$BFDate("01/01/2002","12/31/2002")','mm/dd/yyyy'),

 $BFRandRange(1,100));

The SELECT transaction was designed to fully access the materialized view placing the most stress on the view as possible.

Select Transaction:

select
   to_number(to_char(order_date,'mmyyyy'))  
   month_of_sales,
   author_first_name,author_last_name,sum(quantity) from
   mv_author_sales
group by
   to_number(to_char(order_date,'mmyyyy')),author_first_name,author_last_name;

Using the INSERT with the Benchmark Factory script functions provided a distribution of values similar to the following example distribution in all the tests.

Figure 4: Lock Monitoring Procedure

The locking was monitored at 4 second intervals and the results for Phase 1, 1-30 User SELECT processes in 5 user increments versus INSERT processing at 1-60 users in 5 user increments.

Lock profile for MV_AUTHOR_SALES materialized view:

The INSERT side of the test results are shown in Figure 5.

Figure 5: Results for INSERT Test

The results for the SELECT test of Phase 1 are shown in Figure 6.