1 / 83

PeopleSoft Adoption of Oracle Database Technology CON8588

PeopleSoft Adoption of Oracle Database Technology CON8588. Darryl Presley Consulting Member of Technical Staff Jerry Zarate Sr. Principal Software Engineer.

lydia
Télécharger la présentation

PeopleSoft Adoption of Oracle Database Technology CON8588

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. PeopleSoft Adoption of Oracle Database Technology CON8588 Darryl Presley Consulting Member of Technical Staff Jerry Zarate Sr. Principal Software Engineer

  2. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

  3. Agenda PeopleSoft and Maximum Availability Architecture • Maximum Availability Architecture Overview • PeopleSoft MAA • PeopleSoft and Active Data Guard • Test Results: Failover, Switchover

  4. Agenda PeopleSoft Oracle Feature Integration • New MetaSqls • Global Temp Table • Partitioning • Materialized Views • Multitenant Database

  5. Maximum Availability Architecture Overview

  6. Maximum Availability Architecture Overview MAA Business Goals • Business SLA driven • Recovery point objective (RPO) • Recovery time objective (RTO) • Detection and fast repair for unplanned outages • No single point of failure • Zero data loss • Reduced downtime for planned maintenance • Tested, validated and proven best practices

  7. Maximum Availability Architecture Overview Low-Cost, Integrated, Fully Active, High ROI Active Replica Production Site • Active Data Guard • Data Protection, DR • Query Offload • RAC • Scalability • Server HA • GoldenGate • Active-passive • Flashback • Human error correction ASM • Volume Management • Online Redefinition, Edition Based Redefinition, Data Guard, GoldenGate • Minimal downtime maintenance, upgrades, and migrations • RMAN & Fast Recovery Area • On-disk backups Oracle Secure Backup • Backup to tape / cloud

  8. Maximum Availability Architecture Overview Oracle 11gR2 Database Server • Grid Infrastructure • Oracle Clusterware-managed resources • Automatic Storage Manager (ASM) • SCAN Listener for load balancing and failover • Real Application Clusters (RAC) database • FAN-enabled role-based database services • Active Data Guard • Snapshot Standby • Flashback Database

  9. Maximum Availability Architecture Oracle 11gR2 Client Software • Transparent Application Failover (TAF) • SCAN support in 11gR2 • Register for database FAN events – Fast Client Failover (FCF) • Connect using role-based database services • TNS connect strings connect to FAN-enabled services • Applications respond to FAN events and reconnect to surviving RAC instance after instance failure • Applications can reconnect to new primary database in Data Guard configuration

  10. PeopleSoft MAA

  11. PeopleSoft MAA High Availability

  12. PeopleSoft MAA High Availability – Mid Tier Architecture • Hardware load balancer for PIA web traffic • Redundant switch for fault tolerance • Two or more PIA web servers on separate nodes • Each should have a primary and backup application domain server • Two or more application domain servers on separate nodes • Two domains per node for scalability • Connects to database using role based, FAN enabled services • Two or more Process Schedulers • Two masters (active, idle) and multiple slaves

  13. PeopleSoft MAA High Availability – Middle Tier Storage • Fault tolerant, reliable mid tier storage • Where all shared application installs are located • Report repository accessed by all local nodes • Supports continuous replication of critical file systems • Example; Sun 7000 series ZFS Appliance • Backups of file systems are recommended to protect against logical corruptions

  14. PeopleSoft MAA High Availability – Software Install • PS_HOME and PS_APP_HOME installed as shared homes • All local servers access the same PS_HOME and PS_APP_HOME • Advantages of shared homes • Reduced maintenance downtime • Install once for all servers • Reduces time for out of place patching and upgrades • PS_CFG_HOME is local to each server • COBOL run-time must be installed on each server

  15. PeopleSoft MAA High Availability – PeopleSoft Application Server • Database support: • Connect to the database using FAN-enabled role based services • Application servers register for FAN events to react to failures • Support for Active Data Guard standby database • Support for 11gR2 database client software • Support for 11gR2 SCAN • Support for read-only SELECT statement failover • RAC instance or standby failover • Transparent to PeopleSoft Applications

  16. PeopleSoft MAA Failover Behavior – Application Server

  17. PeopleSoft MAA Failover Behavior – Batch Server

  18. PeopleSoft MAA Disaster Recovery

  19. PeopleSoft MAA Disaster Recovery – Reduce time to deploy • Provision hardware and network infrastructure for DR site • Install or clone Oracle database software at DR site • Instantiate physical standby using RMAN duplicate or from backups • Configure Active Data Guard using Data Guard Broker at primary site • Configure PeopleTools for Active Data Guard at primary site • Replicate PS_HOME, PS_APP_HOME to DR site • Deploy local application and PIA server domains at DR site • No need to install PeopleSoft software at DR site

  20. PeopleSoft MAA Disaster Recovery – DR Site Testing • Convert physical standby to snapshot standby using Data Guard Broker • Start application and PIA servers • Test and validate • Shut down application and PIA servers • Return snapshot standby back to Active Data Guard standby

  21. PeopleSoft and Active Data Guard

  22. PeopleSoft and Active Data Guard • Oracle 11gR2 Active Data Guard Concurrent Real-Time Query Continuous Redo Shipment and Apply OLTP Users Read-only queries on physical standby concurrent with redo apply • Supports RAC on primary / standby • Queries see transactionally consistent results • Handles all data types, but not as flexible as logical standby Writes redirected via Database Link Primary Database Physical Standby Database

  23. PeopleSoft and Active Data Guard Oracle 11gR2 Active Data Guard • Active Data Guard (ADG) standby database • A physical standby database • Applies changes from primary database continuously • Open for read-only queries • PeopleSoft configured to be ADG aware • Offload read-only work to ADG database • PSQUERY • Tree Viewer • BI Publisher • nVision

  24. PeopleSoft and Active Data Guard PeopleTools Active Data Guard Configuration • Create second database schema, e.g. “PSSB2” with setup scripts • Create a secondary PeopleSoft Access ID via PIA web UI • Run supplied scripts to create database link, local and remote synonyms • Make sure database link ALWAYS points to a service that ONLY runs on the primary database • Create a role based database service that starts on a physical standby database i.e., PSQUERY • Create a TNS connect string that connects to the service on the physical standby

  25. PeopleSoft and Active Data Guard PeopleTools Active Data Guard Configuration • Configure psappsrv.cfg file: DBName=HR91FP3  TNS connect string alias to Primary Database DBType=ORACLE UserId=PS UserPswd=PS ConnectId=people ConnectPswd=peop1e ServerName= StandbyDBName=PSFTADG2  TNS connect string alias to Active Data Guard Standby StandbyDBType=ORACLE StandbyUserId=PSSB2 StandbyUserPswd=PSSB2

  26. Results: Failover and Switchover

  27. Results: Failover and Switchover Test Environment • PeopleSoft HRMS 9.1 and PeopleTools 8.52.07 • Database servers • X3-2 quarter Rack Exadata • 2 RAC nodes, 3 storage servers • Mid tier servers • 2 X3-2 Exalogic compute nodes for PeopleSoft application servers • 2 X3-2 Exalogic compute nodes for PIA web server • ZFS 7320 Appliance for mid tier storage • DR site has same configuration

  28. Results: Failover and Switchover Test Results: 1,000 Online HR Users Full DR site startup after primary site failure took 91 seconds

  29. Results: Failover and Switchover Test Results: 1,000 Online HR Users Failover Observations • Loss of PIA web server instance resulted in 25% lost sessions • Loss of PIA web node resulted in 4.5% loss sessions • PIA contains session state and the percentage lost depends on type of failure • Loss of an application server or node results in zero user errors • Loss of a database RAC instance or node results in zero user errors • Time to bring DR site up post failover • 35 seconds for database failover completion • 56 seconds for application and PIA server startup

  30. Results: Planned Maintenance Test Results: 1,000 Online HR Users • Apply Exadata Bundle patch 20 with Active Data Guard (ADG) • Standby-First patching RAC rolling • Start ADG services on primary if non-RAC ADG database • Stop ADG services on ADG instance (srvctl stop service …) • Apply patch • Start ADG services and shutdown ADG service on primary if needed • Primary RAC Rolling • Stop services on instance to be patched • Apply patch • Start services DOWNTIME

  31. PeopleSoft MAA Take-Aways Section Summary • MAA Foundation • ASM • RAC • Flashback Database • Recovery Manager • Snapshot Standby • Technology • Integration • TAF • FAN • SCAN • Active Data • Guard PeopleSoft MAA • MAA Benefits • Higher DR • Utilization • Higher • Confidence • Higher ROI MAA Best Practices

  32. MAA Resources • www.oracle.com/goto/maa @OracleMAA

  33. PeopleSoft Oracle feature integration

  34. PeopleSoft Proposed MetaSQL Enhancements

  35. New MetaSql %SelectDummyTable • There are many places in PeopleSoft Apps where we reference a TABLE (none in particular) or specifically the PS_INSTALLATION table where the TABLE is utilized in a quick calculation or as a branching mechanism in AE program logic. • If a ‘real’ table is utilized, that SQL statement incurs the overhead of a roundtrip to the DB. If that SQL statement is executed millions of times in a process, then this overhead can be significant. • A new metasql%SelectDummyTableis intended to address this specific identified performance issue by providing a way for our Apps to replace the SQL references to a dummy or any table used in this manner with a platform specific ‘dummy’ table. On the Oracle platform, using the DUAL table avoids the round trip to the DB.

  36. New MetaSql %SelectDummyTable • Throughout several performance benchmarks the following constructs were identified as performance issues. • select 'x' from ps_installation where exists (....) • PeopleSoft Apps use this construct quite often for a DoWhen condition in an AE program. When a real table (eg. ps_installation) is used, this SQL statement incurs a trip to the DB. • For the preceding example PS_INSTALLATION could be replaced for the Oracle platform with the following thus avoiding the trip to the DB: • with select 'x' from dual where exists (....)

  37. New MetaSql %SelectDummyTable • Here is another example: • select 'x' from ps_installationwhere %bind(some state record variable) <> 'some value‘; • If this condition were true then the AE program will branch to a particular logic path. • On the Oracle platform, if the DUAL table were used, a trip to db would be bypassed. • select 'x' from dual where %bind(some state record variable) <> 'some value‘;

  38. New MetaSql %SelectDummyTable Proposed Syntax: • %SelectDummyTable • The following illustrates what the %SelectDummyTable will evaluate to on each platform. • After evaluation: • Oracle: DUAL • DB2: SYSIBM.SYSDUMMY1 • SQL Server: SELECT LIST • On SQL Server the FROM clause containing the "dummy" table will be removed. So input such as this: SELECT 'x' FROM %SelectDummyTable where exists (select 'x' from table …) will evaluate to: SELECT 'x' WHERE exists (select 'x' from table ...) • Sybase: SYS.DUMMY • Informix: systables WHERE tabid=1

  39. New MetaSql %SelectDummyTable • Original App SQL: • %Select(GL_JP_AET.PROCESS_FREQUENCY) SELECT 'N' FROM PS_INSTALLATION WHERE 'O' = 'A’ • Would be rewritten by the apps like so: • %Select(GL_JP_AET.PROCESS_FREQUENCY) SELECT 'N' FROM %SelectDummyTableWHERE 'O' = 'A’ • This would then get expanded to the following on Oracle: • %Select(GL_JP_AET.PROCESS_FREQUENCY) SELECT 'N' FROM DUAL WHERE 'O' = 'A'

  40. New MetaSql %SQLHINT • In recent POC benchmarks done on the Exadata platform, SQL hints were required in order to take full advantage of specific Exadata features/behavior. • The ability to utilize SQL hints has broader applicability. • One of the major areas we (Customers, GSC, Consulting/Performance and Benchmarks (P&B) / Center of Excellence(COE), and Development) spend time on is addressing performance issues. 

  41. New MetaSql %SQLHINT • Hints can address performance issues with existing SQL that has performed well in the past but may have regressed from a performance perspective due to a variety of factors:  • Data skew over time causes the optimizer produce poor execution plans. • New RDBMS versions (eg. From Oracle 10 to Oracle 11 or Oracle 12) where the optimizer handles the query less efficiently than it did in the previous release. • An RDBMS bug causing poor performance.

  42. New MetaSql %SQLHINT • Performance tuning efforts can be simplified with the ability to add SQL hints to our SQL. • Development can add hint(s) directly to the SQL, but since SQL Hint syntax tends to be RDBMs platform specific, developers would have to maintain different versions of SQL for Oracle as well as the other RDBMS platforms • Providing a hint metasql would address the multiple platform SQL maintenance issue wrt hints.

  43. New MetaSql %SQLHINT Proposed Syntax: • For implementing a single hint: • %SqlHint(<DML Tag>, <Positional Marker>, ‘<HINT SYNTAX>’, <DB Platform Identifier>, <ENABLE/DISABLE>) • <SQL BLOCK> • For implementing multiple hints in a sql block: • %SqlHint(<DML Tag>, <Positional Marker>, ‘<HINT SYNTAX>’, <DB Platform Identifier>, <ENABLE/DISABLE>) %SqlHint(<DML Tag>, <Positional Marker>, ‘<HINT SYNTAX>’, <DB Platform Identifier>, <ENABLE/DISABLE>) %SqlHint(<DML Tag>, <Positional Marker>, ‘<HINT SYNTAX>’, <DB Platform Identifier>, <ENABLE/DISABLE>) • <SQL BLOCK> • Note: The <SQL BLOCK> is not part of the syntax. It appears in this example only to distinguish where the %SqlHintmetasql should be placed in relation to a block of SQL it applies to.

  44. New MetaSql %SQLHINT Proposed Syntax: • %SqlHint(<DML Tag>, <Positional Marker>, ‘<HINT SYNTAX>’, <DB Platform Identifier>, <ENABLE/DISABLE>) • Where the first parameter is the DML statement for which this hint is applicable to. Valid values are: • INSERT • UPDATE • DELETE • SELECT • MERGE – Applicable to Oracle platform only • Example: • %SQLHINT(‘SELECT’, <Positional Marker>, ‘<HINT SYNTAX>’, <Platform Identifier>, <ENABLE/DISABLE>)

  45. New MetaSql %SQLHINT Proposed Syntax: • %SQLHINT(‘<DML Tag>’, <Positional Marker>, ‘<HINT SYNTAX>’, <Platform Identifier>, <ENABLE/DISABLE>) • Where the second parameter is the DML tag positional marker. This is a numeric value and its purpose is to identify which DML tag a SQLHINT metasql applies to in metasql and SQL constructs where multiple DML statements are present and we want to identify which DML Tag to associate the %SQLHINT with. (After all other metasql expansion) • In the following example, the %SQLHINT would apply to the first(1) SELECT found in the SQLBLOCK. • Example: • %SQLHINT(‘SELECT’, 1, ‘<HINT SYNTAX>’, <Platform Identifier>, <ENABLE/DISABLE>)

  46. New MetaSql %SQLHINT Proposed Syntax: • %SQLHINT(‘<DML Tag>’, <Positional Marker>, ‘<HINT SYNTAX>’, <Platform Identifier>, <ENABLE/DISABLE>) • Where the third parameter is the actual hint syntax for the platform. • Example: Oracle platform index hint forcing the use of the PSFJRNL_LN index • /*+ INDEX(PS_JRNL_LN,PSFJRNL_LN) */ • Example: • %SQLHINT(‘INSERT’, 1 ‘/*+ INDEX(PS_JRNL_LN,PSFJRNL_LN) */’ , <Platform Identifier>, <ENABLE/DISABLE>)

  47. New MetaSql %SQLHINT Proposed Syntax: • %SQLHINT(‘<DML Positional Marker>’, ‘<HINT SYNTAX>’, <Platform Identifier>, <ENABLE/DISABLE>) • Where the fourth parameter is one of the following DB platform values. • ORACLE • Support for the following platforms(where possible) could be added in a future release • SQLSERVER • DB2 • DB2LUW • SYBASE • INFORMIX • Example: • %SQLHINT(‘INSERT’, 1 ‘/*+ INDEX(PS_JRNL_LN,PSFJRNL_LN) */’ , ORACLE, <ENABLE/DISABLE>)

  48. New MetaSql %SQLHINT Proposed Syntax: • %SQLHINT(‘<DML Positional Marker>’, ‘<HINT SYNTAX>’, <Platform Identifier>, <ENABLE/DISABLE>) • Where the fifth parameter is one of the following keywords indicating that the hint is to be either ENABLED or DISABLED. • ENABLE – Enabled means this hint is applicable to the platform coded for the hint. • DISABLE – This is the default and means the hint will be ignored • Example: • %SQLHINT(‘INSERT’, 1 ‘/*+ INDEX(PS_JRNL_LN,PSFJRNL_LN) */’ , ORACLE, ENABLE)

  49. Using PeopleSoft MetaSql %SQLHINT • Use the %SqlHint function to embed SQL hint(s) into SQL or PeopleCode as necessary. • When you use the %SqlHint function, the function must be located at the beginning of a SQL block. • PeopleSoft recommends placing the SQLHINT metasql into a SQL Object, then referencing the SQL Object containing the hint in SQL or PeopleCode. • Note: %SqlHint expansion will happen after all other metasql expansion has occurred.

More Related