Partition, Compress, Archive and Purge Keep Your System on the Road David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk.

Partition, Compress, Archive and Purge Keep Your System on the Road David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk.

Transcript Partition, Compress, Archive and Purge Keep Your System on the Road David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk.

Partition, Compress, Archive and Purge Keep Your System on the Road

David Kurtz Go-Faster Consultancy Ltd.

www.go-faster.co.uk

Who Am I?

• • • Oracle Database Specialist – Independent consultant Performance tuning – – PeopleSoft ERP Oracle RDBMS Book – www.psftdba.com

This happens to be a story about...

• • An ERP System – But it could be about any on-line transaction processing system.

• A PeopleSoft system – But it could be about any ERP PeopleSoft HR/Time & Labor/Payroll – But it could be about any product UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 4

PeopleSoft HR, T&L, GP @ Morrisons

• • 130,000 employees – 100,000 in stores Scheduled hours – – Clock in/out T&L matches reported hours with schedules to determine payable time – Legacy T&L feed from Manufacturing and Distribution.

– Payable time passed to Global Payroll.

Two Challenges

• Performance – Big Tables • • (for OLTP) Eg payroll accumulator table 2.4 billion rows!

– Poor/Variable Performance • Various processes and reports suffering • Critical Processes • Database Size – – – >4TB Growth ≈ 1TB/year Backup • Size/Contention – Cloning • Dev/Test environments UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 6

0.91Tb/yr

Throw Away

• When we say the ‘database is slow’, often: – It is fetching data from disk, reading it, and deciding it isn’t something it wants and throwing it away.

– It would be better if we had not spent time and resource fetching it in the first place • The fastest way to do anything is not to do it at all.

4 Techniques

• • • • Partition – Breaking tables and indexes into manageable pieces Compress – Squeezing more data into the same space Archive – Get the data out of the live application tables and put it somewhere else.

PARTITIONING

Partitioning

Can I Partition Application tables?

• • Support implication?

We could only use partitioning because the application and the business data was suitable.

Partitioning decisions driven by Application

• • Global Payroll – – ‘streamed’ processing • RANGE on EMPLID to match concurrent processing no good for archiving ‘Calendar Group ID’ (effectively a payroll period ID) corresponds to a period of time • LIST sub-partition on payroll period - archive by partition Scheduling, Time & Labor – Weekly range partitions on ‘Date under reporting’ UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 13

Partitioning in Other PeopleSoft Financials Modules?

• • General Ledger – FISCAL_YEAR & ACCOUNTING_PERIOD – Monthly/Quarterly/Annual partitions. Many tables keyed by – Set ID (legal entity/legislature) – Business Unit • No use for archiving, but good for separating concurrent processes.

• I have another customer with 99% of the business in one BUSINESS_UNIT.

– Partitioning is not an option for them.

Partitioning helps archiving

• • • Links logical data value to physical location of data in the database.

Time-based partitioning – Future / Current / Historical Partition-wise operations – Compress historical partitions – – – – Archive by partition exchange Purge by dropping whole partitions.

Less Redo. Faster.

Delete rows –v- Drop Partition?

• • • Free space is left in data block. High Water Mark unchanged – Data block remain part of table/index – Still scanned by queries.

Still backed up by RMAN • • • No longer in a database object. So not referenced.

Free space is available in tablespace.

– Still backed up If the space is at end of the datafile it can be trimmed off by resize.

Partitioning PeopleSoft at Morrisons

• • • • Global Payroll – 46 tables range on EMPLID, matching 32 streams – List sub-partition largest tables by pay period (CAL_RUN_ID).

– 24 archive tables, range partitioned on pay period into tax-year partitions.

– 2 custom GP reporting tables Schedules & Time and Labor – 10 tables in weekly range partitions – of which Reported Time table hash sub-partitioned Audit Tables – 28 monthly range partitions on audit timestamp Security Tables – Separate partitions for different business units UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 17

Managing Partitioning and Partition DDL

• How are you going to generate partition DDL?

– Manual scripting?

– Who is going to write them?

– On going maintenance?

• Add new partitions over time?

Managing Partitioning in PeopleSoft

• • Partitioning strategies – Described own meta-data tables PL/SQL Utility package – generate DDL scripts.

– Driven by PeopleTools tables and our own meta-data • Application Designer from PT8.51 will preserve some existing partitioning – See •

Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART Package

http://www.go-faster.co.uk/gfc_pspart.manual.pdf

Licencing of Partitioning

• Oracle RDBMS Enterprise Edition only – And then separately licenced • Application Support Policies Vary – Partitioning does not invalidate any aspect of PeopleSoft support.

– My understanding is that the policy for E Business suite is not the same.

COMPRESSION

Oracle RDBMS Compression

• Basic Compression – Not separately licenced – Only option in 10g • Advanced Compression • Out of scope of this presentation – – Licenced option Available in 11g • Hybrid Columnar Compression (HCC) • Also out of scope of this presentation.

– Exadata and ZFS filesystem only.

Simple Compression

• Block Dictionary Encoding – Data values stored only stored once in array in data block.

– Pointer from row of data to array of values.

• Shorter Rows, more rows per block, fewer Blocks, less I/O • More CPU to read row.

Simple Table Compression

• • • Specified at table or partition level with DDL – Sub-partition level inherits from partition level Compression only occurs – On creation of the object • ALTER TABLE … COMPRESS – – Direct path insert into object CPU overhead during compression and read Row decompressed on update – Likely to be migrated UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 26

Advanced Table Compression

• 11g Licenced Option – Same Compression Algorithm – Same Performance Benefits/Overheads – Data compressed after DML – Tables remain on-line during compress operations.

When can I compress my data?

• When it is no longer updated by application.

– Because DML uncompresses rows – But that may be well before you can archive/purge.

– Needs knowledge of business/application.

What tables do I compress?

• Depends on application, business, and partitioning strategy.

– Audit data partitions: as soon as month ends – Payroll result data: when pay period is closed for 1 month – T&L: after 6 months UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 29

Compression Gotcha!

• • • During – ALTER TABLE PARTITION … MOVE … COMPRESS.

Local index partitions momentarily become unusable before they go back to being usable.

This can cause application errors – Choose carefully when to compress, – – – or ignore unusable indexes, or do it in an outage or use on-line reredefinition UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 30

Custom PL/SQL Utility Package

• • DBA run management utility Meta-data rules to determine when partition no longer updated.

– – Moves partitions to correct tablespace Incrementally compress static partitions.

– Dynamically generates DDL statements and executes them.

– Added some logging which appears trace files and alert log.

How much table compression?

• • It depends on your data!

We have see a factor 2x – 5x on tables – (50%-80% reduction in size) • Indexes are less compressible – Up to 50% reduction UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 33

Index Compression

• Applies to whole of index – Can choose to enable/disable for each partition • Useful for DEFAULT or MAXVALUE partition • Index remains compressed – DML does not decompress – CPU Performance overhead • Table compression does not affect index size.

Index Compression Prefix Length

• Choose how many columns of index to compress –

Too much compression can be worse than no compression at all

ANALYZE INDEX VALIDATE

• Examines physical index segments – OPT_CMPR_COUNT = optimal compression – OPT_CMPR_PCTSAVE = size saving relative to current index size – 1 result row in INDEX_STATS • • Result is for last partition if partitioned index MAXVALUE / DEFAULT partition may produce unusual results.

Index Compression

• • Not limited to static data Increased CPU overhead on DML – Offset by lower I/O overhead • We found that it has been worth compressing largest indexes on live tables.

ARCHIVE & PURGE

Archive & Purge

• Archive: – Move data from live application table to another table.

– In this case another to table in the same database.

– Read-only access provided to archive tables.

UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge • Purge: – Expunge data from the database.

Archive/Purge

• For each table or set of tables – Define a policy that specifies when data is archived or purged.

• Compression too, but that has no functional impact.

‘But my data will disappear’

• • • That may be a good thing – If you keep data you have to look after it responsibly.

– Pay for the disk to store it/back it up.

Read-only access via PS/Query tool to archive data.

– No access via SQL Developer A very few components and reports access archive tables – Development cost of customisation UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 42

Example Archive/Purge Policies

– GP: archive 2 rolling years, purge after 8 tax years – Schedules, purge at 56 weeks – T&L, purge at 108 weeks – Audit: various: • • Archive after 3 months, 1, 3 year Purge 6 months, 1 year, 2 years, never UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 43

Archive Method

• Partition Management – Exchange/Drop whole Partitions • Time based partitioning – Faster • • Avoid rewriting data Avoid redo – Global Indexes maintenance overhead • Don’t build on archive table – Compression must match • Archive table indexes not compressed.

• Row-by-Row – Non-time based partitioning • Some GP tables – Can be done on-line – Can compress data during rewrite – Different partitioning strategy in archive and live.

– Move archive to lower storage tier.

PL/SQL Archive/Purge Utility

• Custom PL/SQL Package – Driven by same meta-data as partitioning utility – Archive by partition exchange – Purge by dropping partition.

– Called from custom Application Engine • So archiving can be done via application.

Custom Archive Utility

• • • • Based on delivered PeopleSoft Archive Utility We only use it for GP archiving Copies data in direct path – Faster – Can compress during copy Calls PL/SQL utility to drop partitions to save deleting data. UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 48

‘PSARCH’ Schema Design

• • • 2 nd – – – schema to hold archive tables Only tables archived by partition exchange Same name as live tables Same local indexes as live Benefits – Application cannot update/delete from these tables.

Drawbacks – Views to make them accessible to PS/Query.

– – Can’t manage indexes with Application Designer.

Tablespace Design

• • • • No impact on performance – Because all on same tier of disk Do not keep objects with dissimilar purge policies in same tablespaces – You can drop empty tablespaces and recover space.

Periodic tablespaces make sense without purge policy With a purge policy might just have one tablespace.

– Unless you have tiered storage available.

Wrong Tablespace Design

• • • Before archive policy – Weekly T&L and Schedule partitions in same monthly tablespaces Archive Policy – T&L: 108 weeks – Schedules: 56 weeks Schedules populated first, T&L later.

– Schedules tend to be nearer start of tablespace – Schedules purged first leaving holes than cannot be trimmed off.

Free Space Fragmentation

• Free Space left in middle of tablespace – Dropping objects – Compressing objects can leave holes • Less Severe since Oracle 9i – Uniform extent sizes – Bitmap space map UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 52

Oracle RMAN Backup

• • Backs up block with SCN – If an object is dropped, blocks still have SCN – These empty blocks are still backed up.

Can only reduce full backup size by trimming free space from end of data files.

Defragmentation of Free Space

• • PL/SQL utility to rebuild objects nearest end of data file into spaces in the middle of the tables – Needs to be done during an application outage.

Frees space at end of data file that can be trimmed off.

– Thus reducing size of database and backup.

Oracle Flashback Database

• • • SQL Rollback – We are used to the idea of rolling back transaction that have not been committed.

Flashback database – – Generates additional flashback logging Rollback committed transaction and DDL •

but not through a drop/resize data file (smaller)

Future: Oracle >= 11g

• • • • Automatic Partition Creation Range-Range partitioning Advanced Compression – Licenced Option – Supports DML • Possible row-migration threats.

PROGRESS

0.91Tb/yr 0.56Tb/yr

Achievements

• • Database Size – 4.5TB => <2TB – No Growth (apart from GP archive tablespace) Performance Improvements – GP calculation and other processing – T&L processing – Financial Reports UKOUG RAC CIA & DB Combined SIG Partition, Compress, Archive, Purge ©2014 www.go-faster.co.uk 63

Caveat

• I/O has reduced, but not as much as the size of the database • IOPs/Gb of data file has gone up!

– Cheaper to use SSD rather than stripe over more spindles but leaving empty disk space to supply more IOPs?

• It depends…

Conclusion

Nullius in verba

Partition, Compress, Archive and Purge Keep Your System on the Road David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk.

Transcript Partition, Compress, Archive and Purge Keep Your System on the Road David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk.