deep dive: more oracle data pump performance tips and tricks

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |

Deep Dive: More Oracle Data Pump Performance Tips and Tricks

Dean Gagne ConsulIng Member of Technical Staff Database UIliIes Oracle CorporaIon

Deep Dive: Data Pump Performance Tips & Tricks

Roy F. Swonger Sr. Director & Product Manager Database Upgrade & UIliIes Oracle CorporaIon


Safe Harbor Statement The following is intended to outline our general product direcIon. It is intended for informaIon purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or funcIonality, and should not be relied upon in making purchasing decisions. The development, release, and Iming of any features or funcIonality described for Oracle’s products remains at the sole discreIon of Oracle.



More Oracle Data Pump Performance Tips & Tricks


IntroducIon

Recap: Performance Best PracIce

Important Performance Fixes

Futures


$> whoami


16 years w/Oracle Previously with DEC OpenVMS – 10 years

-‐  Exp/imp Jan 2000 -‐  Metadata May 2001 -‐  Data Pump Mar 2002

-‐  Currently responsible for -‐  Worker process – Mar 2002 – Day 1 -‐  Transportable Tablespace – Apr 2005 -‐  MCP process – May 2007 -‐  Export/Import staIsIcs – Mar 2000

Dean Gagne


$> whoami

6

20 years w/Oracle Previously with DEC Rdb

11+ years managing Data Pump Database Upgrade SQL*Loader Transportable Tablespaces

Upgrade and Migrate to Oracle 12c -‐ Live and Uncensored!

Roy Swonger


IntroducIon: Test Hardware •  System: Sun Fire X4170 M3 • CPU – Intel x86-‐64 – 16 total cores x 2.9 Ghz – Two-‐way hyperthreaded: CPU_COUNT=32 •  Storage: – 4.7 TB in RAID 0 (striped across two volumes)

• RAM: – 256 GB • OS: Oracle Linux 6.4


Good amount of CPU for parallel processing

Decent Storage Space but limited performance with all files on one device

Seems like a lot un>l you try running 4 databases with lots of parallelism…


IntroducIon: Test Database • Oracle E-‐Business Suite database – Lots of metadata •  297 users •  4213 system grants •  12455 sequences •  51064 synonyms •  4364 types •  33164 tables •  375,000+ object grants •  53,196 indexes •  53,217 packages •  7082 triggers

– Not a huge amount of data… •  Started with ~50GB of data •  Built larger tables for some cases, up to ~500GB

– …but lots of metadata •  Heavy stress on dicIonary performance

– Export was from 11.1.0.7 into 16 dumpfiles •  Natural limit to parallelism based on # of files



Baseline Performance •  Import into 11.2.0.3 – Connected as SYS – Simple Parfile: directory=EBSIMP dumpfile=aexp%U.dmp logfile=full_4_11203.log FULL=Y EXCLUDE=TABLESPACE parallel=4 metrics=y

• Can you spot any problems?


Import time: 7h 53m 52s




IntroducIon



Futures


Data Pump Best PracIces Performance Tips

• Use PARALLEL=n – Typically n = 2x <number of CPU cores>

• EXCLUDE=STATISTICS on export • Enable parallel import of indexes: apply patch for bug 21539301 – Available for 11.2.0.4 and 12.1.0.2 • New feature in 12c: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y – Apply patch for bug 20778442



Speed up the EsImate Phase • What is the EsImate Phase? •  ESTIMATE=BLOCKS vs ESTIMATE=STATISTICS

• Conclusion: the more data you have, the bigger the benefit received from ESTIMATE=STATISTICS – (But make sure you have accurate staIsIcs!)


0

20

40

60

80

100

120

140

160

180

200

0 50 100 150 200 250 300 350 400

Es>m

ate Ph

ase (secon

ds)

Data Size (GB)

Comparison of Es>mate Phase Methods

Blocks

Stats


Exclude StaIsIcs from Export

•  Is it faster to – EXCLUDE=STATISTICS and build them awer import, or – Export and import staIsIcs?

•  Test Results: – expdp of staIsIcs: 19 minutes – impdp of staIsIcs: 83 minutes – exec dbms_stats.gather_database_stats(): 82 minutes

• Conclusion: – EXCLUDE=STATISTICS saves ~25% of total Ime (plus 150 MB of dumpfile space)


Save Ime and disk space


Do NOT Connect as SYS or SYSDBA

• We do not export SYS-‐owned objects anyway, so SYS is really not necessary for data pump operaIons • ConnecIng as SYS (or SYSDBA) also gives worse performance! – Import of table using PARALLEL=8 from 8-‐dumpfile export… – Connected as schema user GL: imported "GL"."GL_BALANCES” 13.82 GB 147526320 rows in 83 seconds

– Same user connected as SYSDBA: imported "GL"."GL_BALANCES” 13.82 GB 147526320 rows in 206 seconds

• Conclusion: – Use roles and privileges (e.g. DATAPUMP_IMP_FULL_DATABASE) not super users!


Use roles and privileges to do what you need




IntroducIon



Futures



• Patch for bug number 21539301 – Old behavior: build one index at a Ime with PARALLEL n keyword – New behavior: build n indexes at a Ime, each without parallelism

•  Test results for 53190 indexes – 12.1.0.2.0 without patch: •  PARALLEL=4: 5681 seconds (total import 4:14:23) •  PARALLEL=8: 5504 seconds (total import 3:30:14)

– 12.1.0.2.3 with patch: •  PARALLEL=4: 1308 seconds (total import 2:38:29) •  PARALLEL=8: 719 seconds (total import 2:03:11) •  PARALLEL=32: 347 seconds (total import 1:44:41)


Parallel Import for Indexes in 11.2.0.4/12.1.0.2



• Background: package bodies have always been loaded in parallel – But, loading lots of large packages (e.g. XLA packages in EBS) was sIll really slow • Bug 16018315 filed, fixed in 2012 (included in 11.2.0.4, 12.1.0.2) • How much improvement can a single bug fix make, really? – For 52093 Package Bodies – 11.2.0.3: 22179 seconds – 11.2.0.4/12.1.0.2: 2810 seconds

• Remark: you aren’t sIll running 11.2.0.3 or 12.1.0.1…are you?


Package Body Import Improvements


Let’s Put It All Together


0:00:00

1:12:00

2:24:00

3:36:00

4:48:00

6:00:00

7:12:00

8:24:00

Import Time Improves, Step by Step

11.2.0.3 parallel=4 baseline

11.2.0.4 parallel=4 with package body fix 11.2.0.4 parallel=4 excluding sta>s>cs 12.1.0.2 parallel=8

12.1.0.2 parallel=8 w/index patch

12.1.0.2 parallel=32 w/index patch




IntroducIon



Futures


Safe Harbor Statement The preceding is intended to outline our general product direcIon. It is intended for informaIon purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or funcIonality, and should not be relied upon in making purchasing decisions. The development, release, and Iming of any features or funcIonality described for Oracle’s products remains at the sole discreIon of Oracle.


deep dive: more oracle data pump performance tips and tricks

Technology