Transcript Materialized Views: Simple Replication?

PeopleTools 8.48/8.49
New Database Features
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
1
Who Am I?
• Oracle Database Specialist
– Independent consultant
• System Performance
tuning
• Book
– www.psftdba.com
– PeopleSoft ERP
– Oracle RDBMS
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
2
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
3
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
4
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
5
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
6
%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
7
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.48
Uses DBMS_STATS
Silly Defaults
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
8
Delivered DDL Model
• Estimate
• Compute
– Model 4
– Model 5
DBMS_STATS.GATHER_TABLE_STATS
(ownname=>[DBNAME]
,tabname=>[TBNAME]
,estimate_percent=>1
,method_opt=>
'FOR ALL COLUMNS SIZE 1'
,cascade=>TRUE);
Collaborate08 PeopleTools 8.48/9
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);
© www.go-faster.co.uk
9
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
10
My suggested DDL Model
• Estimate
• Compute
– Model 4
– Model 5
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);
Collaborate08 PeopleTools 8.48/9
DBMS_STATS.GATHER_TABLE_STATS
(ownname=>[DBNAME]
,tabname=>[TBNAME]
,estimate_percent=>100
,method_opt=>
'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
© www.go-faster.co.uk
11
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
12
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-optimizerdynamic.html.
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
13
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
14
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
15
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 Workaround
ALTER DATABASE FORCE LOGGING;
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
16
Column Default Values
• Most columns in PeopleSoft are NOT NULL
ORA-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
17
Mobile Synchronisation Framework
• Record Properties
• And then fields defaults appear in create
table DDL.
– Some of them.
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
18
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
19
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
20
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
21
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
22
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
23
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
24
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
25
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)
);
Collaborate08 PeopleTools 8.48/9
create table test_cons
(id
number
,field_01 varchar2(90)
CHECK(LENGTH(field_01)<=30
)
…
,field_20 varchar2(90)
CHECK(LENGTH(field_01)<=30
)
);
© www.go-faster.co.uk
26
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
27
Results of Experiment 1
• Insert 10000 rows
• Elapsed time:
– No constraints:
– With constraints:
– Character semantics:
Collaborate08 PeopleTools 8.48/9
5.36s
5.79s
5.11s
© www.go-faster.co.uk
28
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
29
Results of Experiment 2
• >90% parse time
• Elapsed Time:
– Without Constraints:
– With Constraints:
– Character Semantics:
Collaborate08 PeopleTools 8.48/9
14.71s
37.01s
11.57s
© www.go-faster.co.uk
30
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 thing
They even seem to work better than byte
semantics without constraints!
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
32
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
33
Descending Indexes
SELECT EMPLID,...
FROM PS_JOB
WHERE EMPLID=:1
AND EMPL_RCD=:2
ORDER BY EMPLID, EMPL_RCD, EFFDT DESC, EFFSEQ DESC
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
34
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
35
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
36
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
37
Questions?
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
38
PeopleTools 8.48/8.49
New Database Features
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk
39