Materialized Views: Simple Replication?

David Kurtz Go-Faster Consultancy Ltd.

[email protected]

www.go-faster.co.uk

UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

1

Who Am I?

• • • Oracle Database Specialist – Independent consultant System Performance tuning – PeopleSoft ERP – Oracle RDBMS UK Oracle User Group – PeopleSoft Director • Book – www.psftdba.com

2 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

Agenda

• Simple Replication using Materialized Views – Database Links – Limitations • Aspects of the application (PeopleSoft) • Workarounds UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

3

Resources

• • • If you can’t hear me say so now.

Please feel free to ask questions as we go along.

The presentation will be available from –

–

www.ukoug.org

in the library

www.go-faster.co.uk

4 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

Initial Scenario

HRMS Assentia l Data Stage CRM

UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

EPM

5

Initial Scenario

• Extract from HR and CRM to EPM – Via Assential Data Stage • • • Table by Table replication by SQL Capability to transform data Limited Capability to handle long columns – Performance Bottleneck • Taking too much of batch window which was needed for other batch processing UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

6

Materialized Views – v- Streams

• Materialized Views – Used to be called snapshots – – Old stable technology Database links between databases – Also used for query rewrite • Not discussed in this presentation • Streams – – Introduced 9i Supplemental logging shipped to target database.

– No support for Longs in Oracle 9i • PeopleTools 8.45

• Lots of LONG columns UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

7

The Plan

• Eliminate Assential (as far as possible) – Some complex transitions remain – Implement incremental refresh for all MVs • Incremental refresh every midnight 8 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

Problem 1: Long Columns

• Long Columns – Assential works in blocks of 2000 characters • We discovered truncated data in long columns – Replicate up to 32Kb with MVs • workaround to go across DB links.

– Oracle 10g Streams would provide a total solution.

9 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

Problem 2:Primary Keys

• PeopleSoft doesn’t use database enforced Referential Integrity – No primary keys, only unique constraints • Can usually add primary key constraints using existing unique indexes – Can get Nullable date columns in unique key • Can’t add a primary key constraint 10 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

MVs

Source Table MVL MV Refresh Target MV

UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

11

MV Replication Choice

• No Primary Key – ROWID based replication – No inherited indexes or keys – You may need to create unique indexes on MV – What happens if you reorganise the table?

• Primary Key – Replication by primary key – MVs and MV logs inherit primary keys – Effect of Truncate command?

UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

12

Effect of TRUNCATE

• Primary Key replication – Rows not removed from MV by fast refresh – – No error raised Need to do complete refresh • Demo mv1.sql

• ROWID replication – ORA-12034 during fast refresh – materialized view log on

younger than last refresh – Need to do complete refresh.

• Demo mv2.sql

13 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

MVs with Long Columns

MV Refresh Trigger Source Table MVL

UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

Target MV Long Table View

14

MVs with Long Columns

• This solution would occasionally lock up – Distributed Xaction Lock • Visible in DBA_WAITERS – Unrelated statements – Every 2 or 3 weeks – Never reproduced outside production system – Kill a session created by the MV refresh process UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

15

Distributed Transaction Lock

• MV Refresh Process SELECT /*+ */ "A2"."APPLID", "A2"."APP_DT", "A2"."APPLIC_PURGE_DT", ...

"A2".“XX_PRIOR_RECR", "A2"."JOB_CAT", "A2".“XX_GRAD_OR_STANDRD" FROM "SYSADM"."PS_APPLICANT_DATA" "A2", (SELECT /*+ */ DISTINCT "A3"."APPLID" "APPLID", "A3"."APP_DT" "APP_DT“ FROM "SYSADM"."MLOG$_PS_APPLICANT_D ATA" "A3" WHERE "A3"."SNAPTIME$$" > :1 AND "A3"."DMLTYPE$$" <> :"SYS_B_0") "A1" WHERE "A2"."APPLID" = "A1"."APPLID" AND "A2"."APP_DT" = "A1"."APP_DT" • Long Query in Trigger SELECT "A1"."COMMENTS" FROM "SYSADM"."PS_ABS_HIST_DET" "A1" WHERE "A1"."EMPLID" = :b5 AND "A1"."EMPL_RCD" = :b4 AND "A1"."BEGIN_DT" = :b3 AND "A1"."ABSENCE_TYPE" = :b2 AND "A1"."COMMENT_DT" = :b1 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

16

Distributed Transaction Lock

• • When Oracle performs a distributed SQL statement it reserves an entry in the rollback segment for the 2-phase commit processing. The entry is held until the statement is committed, even if the statement is a query.

www.jlcomp.demon.co.uk/faq/dblink_commit.sql

The commit in the MV refresh does not release it – So we put query of long into autonomous transaction in a PL/SQL packaged function UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

17

Irony

• We never tested the fix to the locking problem – Interim workaround was simply not to replicate long columns • Disabled trigger on MV – Change in customer personnel removed need to replicate long columns 18 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

More Irony

• Can replace LONGs with BLOBs and CLOBs in PeopleTools 8.48

– Default in HR and Financials 9.0

– Most people moving to this release on Oracle RDBMS are also moving to Oracle 10g – In Oracle 10g, I would probably have chosen to implement Streams.

19 UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

Questions?

UKOUG DBMS SIG 17.7.07

www.go-faster.co.uk

20