collaborate08 peopletools 8.48/9© peopletools 8.48/8.49 new database features david kurtz...

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 1

PeopleTools 8.48/8.49 New Database Features

David KurtzGo-Faster Consultancy Ltd.

david.kurtz@go-faster.co.uk

www.go-faster.co.uk

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 2

• Oracle Database Specialist– Independent consultant

• System Performance tuning– PeopleSoft ERP

– Oracle RDBMS

• Book– www.psftdba.com

Who Am I?

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 3

UK Oracle User Group

• UKOUG– PeopleSoft Director

• Annual Conference– Birmingham UK– 1-5 December 2008

• Call for Papers is open– http://conference.ukoug.org

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 4

Introduction

• The book deals with PeopleTools 8.44.– The presentation is the addenda and errata

• PeopleTools 8.44 was a major release– Instrumentation

• PeopleTools 8.48– ‘Fusion readiness’, integration and messaging,

– some significant database features

• PeopleTools 8.49– New third party versions

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 5

Agenda

• DDL Models & %UpdateStats

• NOLOGGING

• Column Default Values

• Long Columns, Unicode & CLOBs

• Descending Indexes

• Dirty Read (PT8.49, SQLServer)

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 6

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

– in the conference library– www.go-faster.co.uk

• Notes pages include references to my blog– blog.psftdba.com

• Errata and Addenda for the book can be found at www.psftdba.com

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 7

%UpdateStats

• Genuine challenge in any RDBMS with a Cost-base optimizer– What do I do about statistics on a working

storage table?

• Platform generic solution:– Collect statistics in the program after you

populate the table.

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 8

DDL Models

• From PT8.x, on Oracle, %UpdateStats uses two new DDL models – Up to PT8.47

ANALYZE TABLE [TBNAME] COMPUTE STATISTICS;

ANALYZE TABLE [TBNAME] ESTIMATE STATISTICS;• Probably due to support for Oracle 8i up to PT8.44.

– From PT8.48Uses DBMS_STATSSilly Defaults

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 9

Delivered DDL Model

• Estimate– Model 4

DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=>1,method_opt=> 'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

• Compute– Model 5

DBMS_STATS.GATHER_TABLE_STATS (ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=> dbms_stats.auto_sample_size,method_opt=> 'FOR ALL INDEXED COLUMNS

SIZE 1',cascade=>TRUE);

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 10

Delivered Models

Estimate and Compute Confused Saved by 1% Sample Size for Compute

1% Sample Size for Compute– I think that’s a typo!

DBMS_STATS.AUTO_SAMPLE_SIZE– Default in 10g. Good idea. Most of the time most people use an

unnecessarily large sample size. SIZE 1

– Oracle 9i default, removes histograms.– 10g default is FOR ALL COLUMNS SIZE AUTO

ALL INDEXED COLUMNS SIZE 1– Removes histograms from indexed columns– Leaves old histograms on unindexed columns

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 11

My suggested DDL Model

• Estimate– Model 4

DBMS_STATS.GATHER_TABLE_STATS (ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=> dbms_stats.auto_sample_size,method_opt=> 'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

• Compute– Model 5

DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=>100,method_opt=> 'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 12

Optimizer Dynamic Sampling

• Oracle’s approach to same problem of statistics working storage tables– Delete the statistics– Have the database sample a few blocks at parse

time.• JIT ANALYZE• Also effective with Global Temporary Tables

– From 10g: Lock the statistics• OPTIMIZER_DYNAMIC_SAMPLING = 4

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 13

PeopleSoft workaround to handle locked statistics

• DBMS_STATS.GATHER_TABLE_STATS raises ORA-20005 on table with locked statistics.

• %UpdateStats in Application Engine

• Also in COBOL

– Need a PL/SQL procedure to encapsulate DBMS_STATS

– Handle error in exception.

– Call PL/SQL procedure in DDL model.• See

http://blog.psftdba.com/2007/05/updatestats-v-optimizer-dynamic.html.

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 14

NOLOGGING operations

• Not written to the redo stream.– Operations do not appear in a Data Guard standby

database• You will get corrupt blocks

ORA-01578: ORACLE data block corrupted

(file # 1, block # 134419)

ORA-01110: data file 1: '/u01/oradata/hcm89/system01.dbf‘

ORA-26040: Data block was loaded using the NOLOGGING option

– Cannot recover objects on restore• reapply archive logs after restoring backup

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 15

NOLOGGING in PeopleSoft

• From PeopleTools 8.48:• Application Designer

– builds all indexes PARALLEL NOLOGGING– Then alters them NOPARALLEL LOGGING

Can remove NOLOGGING from DDL model

Alter statement is hard coded,not in the DDL model

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 16

NOLOGGING in PeopleSoft

Good idea in non-production environments.– Probably not in archive log mode anyway– Better index build performance

Bad in Production– Corrupt Blocks

Simple WorkaroundALTER DATABASE FORCE LOGGING;

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 17

Column Default Values

• Most columns in PeopleSoft are NOT NULLORA-01400: cannot insert NULL into

("SYSADM"."table name"."field name")

• When a new column is added to a table– Need to visit all insert statements.

• Unless I could do this:DESCR VARCHAR2(30) DEFAULT ' ' NOT NULL,SEX VARCHAR2(1) DEFAULT 'U' NOT NULL,

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 18

• Record Properties

• And then fields defaults appear in create table DDL.– Some of them.

Mobile Synchronisation Framework

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 19

CREATE TABLE PS_DMK (EMPLID VARCHAR2(11) DEFAULT ' ' NOT NULL,EFFDT DATE,SEX VARCHAR2(1) DEFAULT 'U' NOT NULL,ACTION_DT DATE NOT NULL,ARRIVAL_TIME DATE NOT NULL,ACTION_DTTM DATE,AA_PLAN_YR SMALLINT DEFAULT 42 NOT NULL,ACCRUED_AMOUNT DECIMAL(15, 2) DEFAULT 0 NOT NULL) …

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 20

Column Default Values

Only character and numeric literal defaults used as columns defaults.Dates ignored.

• What I was hoping for was…

ACTION_DT DATE DEFAULT TRUNC(SYSDATE) NOT NULL,

ARRIVAL_TIME DATE DEFAULT SYSDATE NOT NULL,

…

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 21

Long Columns & CLOBs

• Previously– Long Characters Columns become LONG

columns

• Difficult to handle– Can’t use character functions– Trouble using across database links– Deprecated by Oracle RDBMS

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 22

Long Columns and CLOBs

• From PeopleTools 8.48:If PSSTATUS.DATABASE_OPTION = 2

– Long characters become CLOBs– Raw fields become BLOBs

Supported from Apps 9.0 only– Why not legacy apps on PT8.48?

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 23

Unicode

• Uses Character Semantics

• Reduces parse problem

… EMPLID VARCHAR2(11 CHAR)…– Except that the ‘char’ is assumed

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 24

How did PeopleSoft used to create character columns?

• Byte semantics– 3 bytes / character

CREATE TABLE PS_JOB

(EMPLID VARCHAR2(33)CHECK(LENGTH(EMPLID)<=11)

…

);

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 25

So...

• Length checking constraint on EVERY character column in the database!

• >500,000 user constraints in a Financials database

• What effect does this have on performance?

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 26

Experiment

create table test_nocons(id number,field_01 varchar2(30)…,field_20 varchar2(30));

create table test_nocons_char(id number,field_01 varchar2(30 char)…,field_20 varchar2(30 char));

create table test_cons(id number,field_01 varchar2(90)

CHECK(LENGTH(field_01)<=30)

…,field_20 varchar2(90)

CHECK(LENGTH(field_01)<=30)

);

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 27

Experiment 1: Populate tables only one hard parse

• Only one hard parse

BEGIN FOR i IN 1..10000 LOOP INSERT INTO test_nocons VALUES (i

,RPAD(TO_CHAR(i),11,'.')…

,RPAD(TO_CHAR(i),30,'.'));COMMIT;

END LOOP;END;/

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 28

Results of Experiment 1

• Insert 10000 rows

• Elapsed time:– No constraints: 5.36s– With constraints: 5.79s– Character semantics: 5.11s

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 29

Experiment 2

• Now deliberately generate different SQL statements, forcing parse every time.

BEGIN

FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE 'INSERT INTO test_nocons VALUES ('||i||',RPAD(TO_CHAR('||i||'),11,''.''))';

END LOOP;

COMMIT;

END;

/

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 30

Results of Experiment 2

• >90% parse time

• Elapsed Time:– Without Constraints: 14.71s– With Constraints: 37.01s– Character Semantics: 11.57s

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 32

Conclusion

• Execution of constraints adds overhead, but not a huge amount.

• Parse overhead of constraints is very severe.– Tracing shows that they keep scanning cons$

Character semantics are a good thingThey even seem to work better than byte

semantics without constraints!

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 33

But there is a catch!

• Like CLOBs this feature is controlled by– PSSTATUS.DATABASE_OPTION = 2

Only supported from Applications 9.

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 34

Descending Indexes

SELECT EMPLID,...FROM PS_JOBWHERE EMPLID=:1AND EMPL_RCD=:2ORDER BY EMPLID, EMPL_RCD, EFFDT DESC, EFFSEQ DESC

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 35

Descending Indexes

• Removed in PeopleTools 8.14/8.15– Bug 869177 in Oracle 8i– ORA-03113

• Reintroduced in PeopleTools 8.48

• Increase in performance of effective date/sequence queues. – Usually ask for max or current (max <= today)– Search from latest record back instead of earliest record forwards– Can be lots of history and not much future

• Often current is latest

– Don’t have to scan all of history

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 36

Descending Index

• Form of Function Based Index– Hidden Column– System Generated Name

• Eg. SYS_NC00164$

• appears in USER_IND_COLUMNS

– USER_IND_EXPRESSIONS• Translation of column to expression

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 37

Dirty Read PT8.49, PSQRYSRV on SQLServer

• Oracle doesn’t do dirty reads because it has multi-versioning supported by Undo (Rollback) Segments– SQL Server 2000 provided read committed

mode by locking queried data– SQL Server 2005 has multi-versioning option

(similar effect to Oracle)

• If you have upgraded to PT8.49 why haven’t you upgraded to SQL Server 2005?

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 38

Questions?

Collaborate08 PeopleTools 8.48/9

© www.go-faster.co.uk 39

PeopleTools 8.48/8.49 New Database Features

David KurtzGo-Faster Consultancy Ltd.

david.kurtz@go-faster.co.uk

www.go-faster.co.uk