Transcript Materialized Views: Simple Replication? - Go
Materialized Views: Simple Replication?
David Kurtz Go-Faster Consultancy Ltd.
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
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