Eric Grancher CERN IT department eric granchercern ch

Eric Grancher, CERN IT department, eric. grancher@cern. ch Consolidation Without Tears! Oracle Database 12 c and Database Replay (documents available at https: //indico. cern. ch/conference. Display. py? conf. Id=276758)

Agenda • • A few words on CERN and the computing challenges, Oracle at CERN Consolidation challenge Oracle multitenant database Real Application Testing / capture and replay Conclusions (your turn!) (demos, experience and tips) 3

CERN 27 km circumference Staff members: about 2500 Research community: 10, 000 scientists

Large Hadron Collider - LHC The most complex machine on earth • • • The world biggest particle accelerator 600 million collisions / second Fundamental physics • • • Why do fundamental particles weigh the amount they do? What is 96% of the Universe made of? Where did the antimatter go to? What was the universe like just after the « Big Bang » ? Are there extra dimensions of space? 5

ATLAS/CMS, March 1 st 2013 • • • “Having analysed two and a half times more data than was available for the discovery announcement in July, they find that the new particle is looking more and more like a Higgs boson, the particle linked to the mechanism that gives mass to elementary particles. It remains an open question, however, whether this is the Higgs boson of the Standard Model of particle physics, or possibly the lightest of several bosons predicted in some theories that go beyond the Standard Model. Finding the answer to this question will take time. Whether or not it is a Higgs boson is demonstrated by how it interacts with other particles, and its quantum properties. For example, a Higgs boson is postulated to have no spin, and in the Standard Model its parity – a measure of how its mirror image behaves – should be positive. “ http: //home. web. cern. ch/fr/about/updates/2013/03/new-results-indicate-new-particle-higgs-boson 6

Computing and storage needs • Data volume • • • Computing and storage capacity, world-wide distributed • • • 25 PB per year (in files) > 5. 25 * 1012 rows in an Oracle table (IOT, compression, partition) in one of the databases > 150 sites (grid computing) > 260 000 CPU cores > 269 Po disk capacity > 210 Po tape capacity Distributed analysis with costs spread in the different sites ( « LHC Computing Grid » ) 7

Oracle at CERN • 1982: start with Oracle at CERN (accelerator control) Credit: N. Segura Chinchilla 9

Credit: M. Piorkowski 10

Consolidation, not easy! (a priori) • Change version, parameters, statistics gathering, hardware! • Errors (ORA-600, 745), different execution plans, different results? • Does it fit on the one system? (average / peak!) Does one workload impact the others, take all resources at some point? • Multi instance, schema, virtualisation consolidation, etc. • 11

Oracle Multitenant Database • • Introduced in Oracle DB 12. 1 Ideal for consolidation, like virtualisation for database … but also additional features (cloning, rapid provisioning, regression testing, faster upgrades, move from one database to another -same storage-) SQL level compatibility, tablespace, users, PL/SQL, application unchanged • • Any difference can be reported as a bug But is the case for your application? 12

Oracle Multitenant Database and instance parameters SYS PL/SQL sofware Oracle background processes List of users / roles User PL/SQL software User tables / indexes Oracle foreground processes Non CDB Database and instance parameters SYS PL/SQL sofware Oracle background processes List of users / roles User PL/SQL software User tables / indexes Oracle foreground processes CDB - 1 PDB Database and instance parameters SYS PL/SQL sofware Oracle background processes List of users / roles User PL/SQL software User tables / indexes Oracle foreground processes CDB - 2 PDBs 13

Demo 1 Create a pluggable database • Create a tablespace, one user, two tables • Clone a pluggable database • 14

Real Application Testing Capture and Replay What if you could capture the workload, all workload at the database level (better than client level): select, insert, delete, update, PL/SQL calls, all? • Real Application Testing Capture and Replay • Used at CERN for capture as of 10. 2, replay on 11. 1, 11. 2 and 12. 1 • Was a key component for our successful migration from 10. 2 to 11. 2 • 15

Your objective for the testing • • • New hardware -> time matters New version -> execution plans, LIO matters Difference in results Resource management or parameters impact… All lead to different tests and observations 16

Capture and Replay Capture Upgrade to 12. 1 and non. CDB to PDB Replay Copy of the database: RMAN, DG, expdp flashback_scn= nnn … … t… SQL ate r te e e / d l s L p in u P de … … t… SQL ate r e ete / d l s L p e in u P d 17

Open sessions In principle can create errors/issues at replay • In our experience, little of an issue, marginal differences • 18

In flight transaction • • • Recommendation is to stop the database instances, then start the instance(s) in restricted mode and then enable capture. Not possible in most cases It means that it can incur errors for dependent transactions Not an issue if errors are negligible percentage of the workload Transaction A Transaction B Possible cascading effect on some other transactions … tr … SQL ate ete … e L/ upd del P i ns 19

Capture files (1/2) One file created per server process (each session for dedicated server process) • Sequential, buffered writing per session • access("/…/wcr_7 jya 5 h 0000009. rec", F_OK) = -1 ENOENT (No such file or directory) open("/…/wcr_7 jya 5 h 0000009. rec", O_RDWR|O_CREAT|O_TRUNC, 0666) = 10 20 20

Capture files (2/2) $ ls -lrt /proc/7576/fd/ lrwx------ 1 oracle ci 64 Sep 20 22: 06 9 -> […]wcr_7 jwjrh 0000002. rec $ strace -tt -T -p 7937 2>&1 | grep "write(9" 22: 24: 30. 745968 write(9, “…". . . , 4096) = 4096 <0. 000028> 22: 24: 30. 746050 write(9, “…". . . , 45056) = 45056 <0. 000044> 22: 24: 30. 746149 write(9, “…". . . , 684) = 684 <0. 000016> 22: 24: 31. 111495 write(9, “…". . . , 4096) = 4096 <0. 000026> 22: 24: 31. 111584 write(9, “…". . . , 45056) = 45056 <0. 000038> 22: 24: 31. 111675 write(9, ". . . , 713) = 713 <0. 000017> 22: 24: 31. 474120 write(9, “…". . . , 4096) = 4096 <0. 000027> 22: 24: 31. 474193 write(9, “…". . . , 45056) = 45056 <0. 000040> 22: 24: 31. 474282 write(9, “…”…, 712) = 712 <0. 000019> 21 21

Synchronisation SCN: the COMMIT order in the captured workload will be preserved during replay and all replay actions will be executed only after all dependent COMMIT actions have completed • OBJECT_ID: all replay actions will be executed only after all relevant COMMIT actions have completed • OFF: no dependency (if independent transaction? ) • 22

sysdate and sequence • Latest replay patch bundle (16086826, see reference) captures sysdate and sequence calls so that they can be used at replay 23

Divergence DBA_WORKLOAD_REPLAY_DIVERGENCE • GET_DIVERGING_STATEMENT procedure in DBMS_WORKLOAD_REPLAY • • Replay report provides a summary 24

SQL tuning set capture_sts => TRUE is not supported in RAC • Tuning set can be used to compare SQL executions • 25

Demo 2 • Capture on one database • • Check the capture files Replay • Check the replay report 26

Methodology matters! (1/2) Reproducible tests (scripted): reload database, upgrade, set parameters, disable some of jobs and resource manager time based settings • Gather statistics, logs and reports • 27

Methodology matters! (2/2: caching) • Buffer cache and shared pool (globally less impact for long replay) Warming cache Measure Multiple strategies: 1. Take the AWR reports after a first period of replay 2. Do not compare execution time but LIO, execution plans, etc. 3. Pre-warm (hint: use Capture and Replay!) 28

Several replays • Advise to compare between replays when doing multiple changes and replay versus the capture (same version, measure only the differences not the capture/replay differences) Capture New platform, new version, capture->replay, changes Replay 1 Replay 2 … t… SQL ate ete … r l d se PL/ in de up … t… SQL ate ete … r d l se PL/ in up de … t… SQL ate ete … r l d se PL/ de in up 29

Demo 3 • Methodology, example CASTORNS 30

Multitenant Database – Capture/Replay Non CDB, SQL ordered by Gets 31

Consolidated replay In 11. 2. 0. 3 apply patch 16086826 • In 11. 2. 0. 4 and 12. 1, no patch required • My Oracle Support note 1453789. 1, “Real Application Testing: Consolidated Database Replay Feature” • 32

Consolidated replay 1. Copy data (plug) 33

Consolidated replay 2. Copy and process capture files 34

Consolidated replay EXEC DBMS_WORKLOAD_REPLAY. BEGIN_REPLAY_SCHEDULE ('CONS_SCHEDULE'); SELECT DBMS_WORKLOAD_REPLAY. ADD_CAPTURE ('DBA') FROM dual; SELECT DBMS_WORKLOAD_REPLAY. ADD_CAPTURE ('DBB') FROM dual; EXEC DBMS_WORKLOAD_REPLAY. END_REPLAY_SCHEDULE; EXEC DBMS_WORKLOAD_REPLAY. INITIALIZE_CONSOLIDATED_REPLAY ('CONS_REPLAY', 'CONS_SCHEDULE'); 3. Configure and initialize replay 35

Consolidated replay EXEC DBMS_WORKLOAD_REPLAY. REMAP_CONNECTION (schedule_cap_id => 1, CONNECTION_ID => 1, replay_connection => 'db 121 ol 5/pdba'); 4. Remap connections 36

Consolidated replay EXEC DBMS_WORKLOAD_REPLAY. PREPARE_CONSOLIDATED_REPLAY (synchronization => 'OBJECT_ID'); wrc EXEC DBMS_WORKLOAD_REPLAY. START_CONSOLIDATED_REPLAY; 5. prepare, launch wrc, start replay 37

Demo 4 • Replay multiple workloads into a pluggable database 38

Resource manager 16 • 4. 496583 select /*+ parallel(3) */ 7. 048278 select /*+ parallel(3) */ 3. 281641 select /*+ parallel(3) */ 39

Resource management • Resource management is critical for consolidation • • • Example: BEGIN DBMS_RESOURCE_MANAGER. CREATE_CDB_PLAN_DIRECTIVE( plan => 'newcdb_plan', pluggable_database => 'salespdb', shares => 3, utilization_limit => 100, parallel_server_limit => 100); END; / 40

If start_capture hangs • From Szymon Skorupinski: dbms_workload_capture. start_capture can hang, “ADDM Jobs are in Status Executing or Running for a Long Time” (Doc ID 1557550. 1). Workaround to disable automatic ADDM runs after snapshot taking works. alter system set "_addm_auto_enable"=false scope=both sid='*’; 41

Library We have built of library of {source DB, captured workload} • Very useful for testing new version, new OS, new platform • 42

Conclusion • • Oracle Database 12 c Multitenant database for consolidation Replay with your applications is the only way to prepare Use Real Application Testing not only for major upgrades, patching and/or parameter changes. It is integrated with Multitenant Capture and Replay to measure the differences if any Methodology, reproducibility Your turn! 43

References • • Oracle 12 c testing guide, http: //docs. oracle. com/cd/E 24628_01/server. 121/e 20852/part 2. htm#CHDGFGCC Oracle Database Replay http: //www. vldb. org/pvldb/2/vldb 09 -588. pdf Consistent Synchronization Schemes for Workload Replayhttp: //www. vldb. org/pvldb/vol 4/p 1225 -morfonios. pdf Master Note for Real Application Testing Option (MOS Doc ID 1464274. 1) Real Application Testing: Consolidated Database Replay Feature (MOS Doc ID 1453789. 1) Pre and Post Installation Readme for Patch 16086826 DBREPLAY Patch Bundle 2 and Database Replay Workload Consolidation Feature (MOS Doc ID 1565663. 1) Scripts To Debug Slow Replay (MOS Doc ID 760402. 1) Julian Dyke presentations on Database Replay http: //www. juliandyke. com/Presentations. html 44