html5-img
1 / 19

Materialized Views: Simple Replication

step
Download Presentation

Materialized Views: Simple Replication

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

    2. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 2 Oracle Database Specialist Independent consultant System Performance tuning PeopleSoft ERP Oracle RDBMS UK Oracle User Group PeopleSoft Director Book www.psftdba.com Database specialist Ive come to the conclusion that to describe myself as a DBA is not particularly accurate, since I dont administer databases. What I do do is that I look at performance problems that people have with PeopleSoft systems, that are not always on Oracle databases, and Oracle databases that do not always support PeopleSoft systems. I am involved with UKOUG. I am chair of the Unix SIG. Which means that I have to find people to come and talk to the meetings and tell their story. And if an unpolished performer like me can get up and talk, then so can you real customers with real systems and real stories. I am also on technical sub-committee of the UKOUGs Fusion council. Database specialist Ive come to the conclusion that to describe myself as a DBA is not particularly accurate, since I dont administer databases. What I do do is that I look at performance problems that people have with PeopleSoft systems, that are not always on Oracle databases, and Oracle databases that do not always support PeopleSoft systems. I am involved with UKOUG. I am chair of the Unix SIG. Which means that I have to find people to come and talk to the meetings and tell their story. And if an unpolished performer like me can get up and talk, then so can you real customers with real systems and real stories. I am also on technical sub-committee of the UKOUGs Fusion council.

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

    4. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 4 Resources If you cant 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 The usual rules of engagement applyThe usual rules of engagement apply

    5. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 5 Initial Scenario

    6. UKOUG DBMS SIG 17.7.07 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

    7. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 7 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

    8. UKOUG DBMS SIG 17.7.07 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

    9. UKOUG DBMS SIG 17.7.07 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.

    10. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 10 Problem 2:Primary Keys PeopleSoft doesnt 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 Cant add a primary key constraint

    11. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 11 MVs

    12. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 12 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?

    13. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 13 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 <table> younger than last refresh Need to do complete refresh. Demo mv2.sql

    14. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 14 MVs with Long Columns

    15. UKOUG DBMS SIG 17.7.07 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

    16. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 16 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_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

    17. UKOUG DBMS SIG 17.7.07 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

    18. UKOUG DBMS SIG 17.7.07 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

    19. UKOUG DBMS SIG 17.7.07 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.

    20. UKOUG DBMS SIG 17.7.07 www.go-faster.co.uk 20 Questions?

More Related