E N D
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?