Materialized Views: Simple Replication?

1. Materialized Views: Simple Replication? David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk www.go-faster.co.uk

2. Book www.psftdba.com Who Am I? • Oracle Database Specialist • Independent consultant • System Performance tuning • PeopleSoft ERP • Oracle RDBMS • UK Oracle User Group • PeopleSoft Director www.go-faster.co.uk

3. Agenda • Simple Replication using Materialized Views • Database Links • Limitations • Aspects of the application (PeopleSoft) • Workarounds www.go-faster.co.uk

4. 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 www.go-faster.co.uk

5. Initial Scenario HRMS Assential Data Stage CRM EPM www.go-faster.co.uk

6. 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 www.go-faster.co.uk

7. 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 Materialized Views – v- Streams www.go-faster.co.uk

8. The Plan • Eliminate Assential (as far as possible) • Some complex transitions remain • Implement incremental refresh for all MVs • Incremental refresh every midnight www.go-faster.co.uk

9. 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. www.go-faster.co.uk

10. 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 www.go-faster.co.uk

11. MVs MV Refresh Source Table Target MV MVL www.go-faster.co.uk

12. 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? MV Replication Choice www.go-faster.co.uk

13. 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 <table> younger than last refresh Need to do complete refresh. Demo mv2.sql Effect of TRUNCATE www.go-faster.co.uk

14. MVs with Long Columns MV Refresh Trigger Source Table Target MV Long Table MVL View www.go-faster.co.uk

15. 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 www.go-faster.co.uk

16. 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_DATA" "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 Distributed Transaction Lock www.go-faster.co.uk

17. 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 www.go-faster.co.uk

18. 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 www.go-faster.co.uk

19. 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. www.go-faster.co.uk

20. Questions? www.go-faster.co.uk