1 / 48

Performance Testing Progress and Oracle

Performance Testing Progress and Oracle. Environment Variables and Installation. inventory.xml. Physical Structure (data files). Physical Structure. .srt, .DBI. ORACLE_HOME. Logical Structure. Background Processes. Connecting to a database. $ORACLE_HOME/bin. Connecting to a database.

lorna
Download Presentation

Performance Testing Progress and Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Performance Testing Progress and Oracle

  2. Environment Variables and Installation inventory.xml PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  3. Physical Structure (data files) PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  4. Physical Structure .srt, .DBI ORACLE_HOME PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  5. Logical Structure PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  6. Background Processes PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  7. Connecting to a database $ORACLE_HOME/bin PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  8. Connecting to a database S -H host –S service1 SID Host:port:SID Host:port:SID2 -H host –S service2 SID2 Listener Port 1521 PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  9. Meta-schema and VST Progress Meta-schema tables -80 < _file-number < 0 Progress VST _file-number < -16384 PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  10. Meta-schema and VST PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  11. Data Types 10 -5 PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  12. Create Database PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  13. PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  14. PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  15. Create Database • prodb mydb • procopy empty8 mydb • Min size 5 MB • Min memory 160KB • Time to create 1 second • A set of .sql scripts • create database mydb; • Min size 512 MB • Min memory 512MB • Time from 20 min to 1.5 hr PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  16. Shutdown • Unconditional Shutdown • Emergency Shutdown • Shutdown Normal • Shutdown Transactional • Shutdown Immediate • Shutdown Abort Progress 10.2B -shutdownTimeout [ immed, 10m, 1h, maximum ] PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  17. Startup Shared Memory Background Proc Prorest init*.ora Restore .ctl PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  18. Backup PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  19. Compressed Backup 700 GB Progress DB makes about 600 GB probkup 1.4 TB Oracle DB makes either 1.2 TB regular backup or 250 GB RMAN compressed backup (BZIP2 is default). PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  20. Dump and Load PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  21. ATM Database Tests Hardware: IBM POWER 6 p570, RAM 24GB, 2 CPUs 4704 MHz Software: AIX 6.1, OpenEdge 11.0, Oracle 11gR2 Database: ATM database = 7GB PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  22. Database Size Progress DB/area size 7,442,137,088 Oracle DB/tablespace size 7,688,953,856 Oracle db appear to be 3% more in bytes PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  23. Backup Size and Speed Progress Oracle Oracle ( compressed ) time 94 sec 140 sec 240 seconds size 7GB 7.8GB 735MB PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  24. ASCII Dump (in seconds) # records Progress Oracle 10,000 0.31 0.43 100,000 1.56 3.32 1,000,000 15 32 8,000,000 115 259 for each account where account.id < "condition“ no-lock: export account. select * from account where account.id < "condition" PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  25. ASCII Load (in seconds) # records Progress Oracle 100,000 3.75 0.8 1,000,000 53 12 10,000,000 413 139 repeat: create account. import account. insert into account select * from stg_account; -- External table PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  26. Binary Dump (in seconds) # records Progress Oracle 50,000,000 80.38 55.46 proutil atm -C dump PUB.account /dir -index 0 –RO expdp user/passwd TABLES=ACCOUNT directory=DATA_DIR dumpfile=expdp.dmp parallel=2 -- Data Pump PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  27. Binary Load with integrity # records Progress Oracle 50,000,000 920.38 60.43 proutil atm -C load account.bd -B 50000 -TB 31 -TM 32 -T /tmp impdp user/passwd directory=DATA_DIR dumpfile=expdp.dmp -- Data Pump PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  28. Binary Load no integrity (-i) # records Progress Oracle 50,000,000 216.28 60.43 proutil atm -C load account.bd -i -B 50000 -TB 31 -TM 32 -T /tmp impdp user/passwd directory=DATA_DIR dumpfile=expdp.dmp -- Data Pump PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  29. Binary Load HWM PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  30. Binary Load HWM PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  31. Binary Load HWM PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  32. Oracle Load HWM PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  33. Oracle Load HWM PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  34. Oracle Load HWM PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  35. Build a brand new index # records Progress Oracle 50,000,000 148.96 118.31 proutil atm -C idxbuild area "Account Index" -thread 1 -threadnum 2 -TF 80 -TB 64 -TM 32 -B 256 -T /tmp -SG 64 CREATE UNIQUE INDEX account_pk ON account (id); PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  36. Rebuild existing index # records Progress Oracle 50,000,000 153.95 81.88 proutil atm -C idxbuild area "Account Index" -thread 1 -threadnum 2 -TF 80 -TB 64 -TM 32 -B 256 -T /tmp -SG 64 ALTER INDEX account_pk REBUILD PARALLEL 2; PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  37. ATM Test PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  38. atm1.sql PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  39. Progress ATM Results All tests run for 300 seconds PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  40. Oracle ATM Results All tests run for 300 seconds PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  41. PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  42. Big box Test IBM Power 7 Model p750 16 3.4 GHz CPUs x 4 SMTs RAM 124 GB SAN V7000 PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  43. PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  44. Startup parameters PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  45. PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  46. OEM parameters PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  47. Startup parameters PUG Challenge Americas 2012 Performance Testing Progress and Oracle

  48. Questions PUG Challenge Americas 2012 Performance Testing Progress and Oracle

More Related