slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
PeopleSoft Adoption of Oracle Database Technology CON8588 PowerPoint Presentation
Download Presentation
PeopleSoft Adoption of Oracle Database Technology CON8588

Loading in 2 Seconds...

play fullscreen
1 / 83

PeopleSoft Adoption of Oracle Database Technology CON8588 - PowerPoint PPT Presentation


  • 172 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'PeopleSoft Adoption of Oracle Database Technology CON8588' - lydia


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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

slide3

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.

agenda
Agenda

PeopleSoft and Maximum Availability Architecture

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

PeopleSoft Oracle Feature Integration

  • New MetaSqls
  • Global Temp Table
  • Partitioning
  • Materialized Views
  • Multitenant Database
maximum availability architecture overview1
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
maximum availability architecture overview2
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
maximum availability architecture overview3
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
maximum availability architecture
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
peoplesoft maa1
PeopleSoft MAA

High Availability

peoplesoft maa2
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
peoplesoft maa3
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
peoplesoft maa4
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
peoplesoft maa5
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
peoplesoft maa6
PeopleSoft MAA

Failover Behavior – Application Server

peoplesoft maa7
PeopleSoft MAA

Failover Behavior – Batch Server

peoplesoft maa8
PeopleSoft MAA

Disaster Recovery

peoplesoft maa9
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
peoplesoft maa10
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
peoplesoft and active data guard1
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

peoplesoft and active data guard2
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
peoplesoft and active data guard3
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
peoplesoft and active data guard4
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

results failover and switchover1
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
results failover and switchover2
Results: Failover and Switchover

Test Results: 1,000 Online HR Users

Full DR site startup after primary site failure took 91 seconds

results failover and switchover3
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
results planned maintenance
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

peoplesoft maa take aways
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

maa resources
MAA Resources
  • www.oracle.com/goto/maa

@OracleMAA

new metasql selectdummytable
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.
new metasql selectdummytable1
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 (....)
new metasql selectdummytable2
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‘;
new metasql selectdummytable3
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
new metasql selectdummytable4
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'
new metasql sqlhint
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. 
new metasql sqlhint1
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.
new metasql sqlhint2
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.
new metasql sqlhint3
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.
new metasql sqlhint4
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>)
new metasql sqlhint5
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>)
new metasql sqlhint6
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>)
new metasql sqlhint7
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>)
new metasql sqlhint8
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)
using peoplesoft metasql sqlhint
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.
new metasql sqlhint9
New MetaSql %SQLHINT
  • Example: Multiple hints specified in a PeopleCode %INSERTSELECT construct
  • Original SQL + SQLHINT(s):
    • %SQLHINT(‘INSERT’, 1, ’/*+ APPEND*/’,ORACLE, ENABLE ),%SQLHINT(‘SELECT’, 1, ‘/*+ LEADING(H) INDEX(L, PSFJRNL_LN) */’ , ORACLE , ENABLE)
    • %InsertSelect(JRNL_LIU_TAO, JRNL_LN L,
    • BUSINESS_UNIT_IU=H.BUSINESS_UNIT_IU, LEDGER_GROUP=H.LEDGER_GROUP, IU_SYS_TRAN_CD=H.IU_SYS_TRAN_CD,
    • IU_TRAN_CD=H.IU_TRAN_CD, PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE))
    • FROM %Table(JRNL_HIU_TAO) H, PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L
    • WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE)
    • AND H.BUSINESS_UNIT=L.BUSINESS_UNIT
    • AND H.JOURNAL_ID=L.JOURNAL_ID
    • AND H.JOURNAL_DATE=L.JOURNAL_DATE
    • AND H.UNPOST_SEQ=L.UNPOST_SEQ; 
  • The %SQLHINT will be expanded and applied after all other meta-sql expansion has occurred . In this example, the ‘APPEND’ hint will be applied to the first INSERT found in this SQL Block. expansion. The ‘LEADING’ hint will be applied to the first SELECT found in this SQL Block.
new metasql sqlhint10
New MetaSql %SQLHINT
  • The post expanded SQL would look like this:
  •  INSERT /*+ APPEND */ INTO PS_JRNL_LIU_TAO5 (BUSINESS_UNIT
  • , JOURNAL_ID
  • , JOURNAL_DATE
  • <For the purposes of clarity, many columns in this column list have been omitted from this example.>
  • , DEPTID
  • , SCENARIO
  • , BUSINESS_UNIT_IU)
  • SELECT /*+ LEADING(H) INDEX(L, PSFJRNL_LN) */ L.BUSINESS_UNIT
  • , L.JOURNAL_ID
  • , L.JOURNAL_DATE
  • <For the purposes of clarity, many columns in this column list have been omitted from this example.>
  • ,L.DEPTID
  • ,L.SCENARIO
  • , H.BUSINESS_UNIT_IU
  • FROM PS_JRNL_HIU_TAO5 H , PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L
  • <For the purposes of clarity, the rest of this SQL statement beginning with the WHERE clause has been omitted from this example.>
oracle global temp table support
Oracle Global Temp Table Support
  • Recent benchmark POCs on Exadata have shown considerable performance gains when Oracle GTT’s are used in AE programs in lieu of regular Oracle tables utilized as PeopleSoft temporary tables.
  • Today, PeopleSoft defined temporary tables are generated as ‘regular’ RDBMS tables regardless of RDBMS platform.
  • For the Oracle platform this means the following:
    •  A PS record designated as a PS Temporary table must be pre-created/pre-exist in the database prior to use.
    • Since PS Temporary tables are created as ‘regular’ Oracle tables, they will incur all normal overhead associated with ‘regular’ tables: (e.g. Undo, Redo Logging, etc…with any DML operations.)
oracle global temp table support1
Oracle Global Temp Table Support

Benefits

  • Less overhead
    • Oracle GTT’s do not incur some of the same overhead (eg. Redo Log writing) thus providing performance gains over non-GTT tables
    • Decreased Redo generation, as by definition, GTT’s are non-logging
  • Session specific data
    • Easy disposal of data used for calculations. Data in GTT’s are only available for the duration of the session
    • No rollback or deletion of session specific data required
    • Explicit truncation
oracle global temp table support2
Oracle Global Temp Table Support

Proposed Syntax for GTT support:

  • CREATE GLOBAL TEMPORARY TABLE PS_abcdefghijk_nnnn
  • (  column1  VARCHAR2(8),  column2  NUMBER)
  • ON COMMIT PRESERVE ROWS;
  • The syntax we want to use is ON COMMIT PRESERVE ROWS. Since Apps can commit multiple times in an App Engine Program we need to preserve rows when it commits.  Apps will take care of deleting if it's needed within the program.
oracle global temp table support4
Oracle Global Temp Table Support

Possible restrictions:

  • GTT usage is restricted to Batch AE:
    • Tools will restrict GTT usage for online AE's. If an online AE is attempting to use a GTT, then the AE runtime will throw an error and terminate the process.
  • GTT's cannot be used in AE 's which require restart capability.
    • Tools will restrict GTT usage if the AE is marked restartable and its using GTT's.
    • If an AE is attempting to use a GTT and the AE is marked restart able, then the AE definition infrastructure in PSIDE will throw an error during the save of the AE. 
oracle global temp table support5
Oracle Global Temp Table Support

Possible restrictions:

  • If the Global Temporary Table attribute is set for the record type of 'Temporary Table', the record name is limited to only 11 characters.
    • If the record name which you have specified exceeds 11 characters, you will get an error message during 'Save'.
      • 'You will need to provide a new name not exceeding 11 characters to successfully save the record with the GTT attribute.‘
    • This applies to new Temp tables attributes as GTT as well as current temp tables which are attributed as GTT.
partitioning support
Partitioning Support
  • Simply stated, Partitioning allows tables and/or indexes to be subdivided into smaller pieces, enabling these database objects to be accessed and managed at a finer level of granularity by the database.
  • The two main reasons to use partitioning are related to management and performance improvement. 
    • Improved administrative manageability - Partitioning can greatly simplify common administration tasks. Partitioning achieves this by allowing query manipulations of smaller chunks of data.
    • Increased query performance by selecting only from the relevant partitions. This weeding out process eliminates the partitions that do not contain the data needed by the query through a technique called partition pruning.
partitioning support1
Partitioning Support
  • Improved administrative manageability - Partitioning can greatly simplify common administration tasks. Partitioning achieves this by allowing query manipulations of smaller chunks of data which results in:
    • Faster backups - A DBA can back-up a single Oracle partition of a table, rather than backing up the entire table, thereby reducing backup time.
    • Easier administration – Management of Oracle partitioned tables is improved because maintenance can be focused on particular portions of tables. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more easily manageable chunks.
partitioning support2
Partitioning Support
  • Increased query performance by selecting only from the relevant partitions. This weeding out process eliminates the partitions that do not contain the data needed by the query through a technique called partition pruning. This results in improved SQL performance in the following areas:  
      • Index range scans – Oracle partitioning physically sequences rows in index-order causing a dramatic improvement (over 10x faster) in the speed of partition-key scans.
      • Full-table scans – Partitioning decreases the number of rows to be processed through partition pruning. Oracle partition pruning only accesses those data blocks required by the query and partition-wise joins.
      • Table joins – Oracle partition-wise joins take only the specific sub-set of the query partitions, causing significant speed improvements on nested loop and hash joins.
      • Updates – Oracle parallel query for partitioned objects improves batch load speed.
  • The performance improvement of SQL involving partitioned TABLEs or INDICEs is substantial.
partitioning example
Partitioning Example
  • CREATE TABLE RTBL_LEDGER (
  • SCE_ID VARCHAR2(10),
  • RTBL_SCENARIO VARCHAR2(18),
  • BUSINESS_UNIT VARCHAR2(5),
  • < All other columns have been omitted in this example for brevity. >
  • PROCESS_INSTANCE VARCHAR2(15))
  • PARTITION BY LIST(SCE_ID)
  • SUBPARTITION BY RANGE (BUSINESS_UNIT)
  • SUBPARTITION TEMPLATE
  • (
  • SUBPARTITION CAN01THRUCAN03 VALUES LESS THAN ('CAN04') TABLESPACE part1,
  • SUBPARTITION EVG01THRUEVG06 VALUES LESS THAN ('EVG07') TABLESPACE part2,
  • SUBPARTITION US001 VALUES LESS THAN ('US002') TABLESPACE part3,
  • SUBPARTITION ALLOTHER_BU VALUES LESS THAN (MAXVALUE) TABLESPACE part4
  • )
  • (
  • PARTITION SCE_0000000001 VALUES ('0000000001'),
  • PARTITION SCE_0000000002 VALUES ('0000000002'),
  • PARTITION SCE_0000000077 VALUES ('0000000077'),
  • PARTITION ALLOTHER_SCE VALUES LESS THAN (MAXVALUE));
materialized view support
Materialized View Support
  • A Materialized View is effectively a database table that contains the results of a query.
  • The power of materialized views comes from the fact that, once created, data can be synchronized (automatically/manually) with its source information as required with little or no programming effort.
materialized view support1
Materialized View Support
  • You can use materialized views to increase the speed of queries on very large databases.
    • Queries to large databases often involve joins between tables, aggregations such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.
  • Materialized views improve query performance by pre-calculating expensive join and aggregation operations on the database prior to execution and storing the results in the database.
    • The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time.
materialized view support2
Materialized View Support
  • Views vs Materialized Views
  • A view has a logical existence but a materialized view has a physical existence. A materialized view can be indexed, analyzed etc, that is all the things that we can do with a table can also be done with a materialized view.
  • We envision that materialized view support will be added yo the Application Designer. This would allow applications developers and customers the ability to add a materialized view or convert an existing view to a materialized view.
oracle multitenant database1
Oracle Multitenant Database

Key Concepts

  • Pluggable Database (PDB)
    • A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.
    • All databases prior to 12c are considered non-CDB
  • Container Database (CDB)
    • New ‘super set’ architecture
    • Consolidate Pluggable Databases (PDB)
    • Fully compatible with Real Application Clusters (RAC) and Active Data Guard
oracle multitenant database2
Oracle Multitenant Database

PSFT Certification Planned for PT 8.54

PeopleSoft Prod Container Database (CDB)

PeopleSoft HCM

Container Database (CDB)

  • Pre 12c DBs are non-CDBs
  • Container DBs (CDB) consolidate Pluggable Databases (PDB)
  • PDBs share DB catalog and background processes
  • Provisioning and cloning is easy, via SQL
  • DB patching is simple
  • Configuration is flexible

HCM (Prod)

HCM (Test)

FSCM (Prod)

HCM (Dev)

ELM (Prod)

HCM (Prod)

for more information
For More Information

The PeopleSoft Information Portal

All your PeopleSoft information and training resources in one convenient location. www.peoplesoft92.com or www.peoplesoftinfo.com