1 / 34

Embracing Autonomous Database: ADW and ATP as Force Multipliers

Explore the future of database management as the Autonomous Database approaches. Learn about ADW and ATP, performance monitoring, workload distribution, and more. Discover the transition to a more automated database environment.

allenf
Download Presentation

Embracing Autonomous Database: ADW and ATP as Force Multipliers

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. Jim Czuprynski Senior Enterprise Data Architect Viscosity NA @JimTheWhyGuy An Autonomous Singularity Approaches:ADW and ATP As Force MultipliersAugust 1, 2019

  2. @JimTheWhyGuy Jim Czuprynski • Oracle DBA since 2001 • Oracle 9i - 12c OCP, ADWC Certified • Oracle ACE Director • 100+ articles on databasejournal.com and IOUG SELECT • Co-author of 4 Oracle books • Oracle-centric blog (Generally, It Depends) • Regular speaker at Oracle OpenWorld, IOUG COLLABORATE, KSCOPE, and Regional OUGs • E-mail me at jim.czuprynski@viscosityna.com • Follow me on Twitter (@JimTheWhyGuy) • Connect with me on LinkedIn (Jim Czuprynski)

  3. Our Agenda • Where Will Your Career Be When the Singularity Arrives? • Autonomous Database (AuDB): ADW, ATP, and the End of the Helicopter DBA • Creating, Controlling, and Monitoring AuDB Instances • Monitoring AuDB Performance in Multiple Dimensions • What Happens If My AuDB Instance Becomes [Over]whelmed? • Conclusions, Use Cases, and What This All Means For DBAs EDAs

  4. A DBA’s Day: Utopia vs. Reality

  5. Moving to Autonomous DB: A Suggested Business Process Flow As an evolving Oracle Enterprise Data Architect, it’s crucial to recognize and embrace the main thrust of Autonomous DB: No More Knobs!

  6. AuDB: Getting Started Creating a New AuDB Instance Monitoring Performance Monitoring and Controlling Statement Execution

  7. AuDB: Creating a New Instance (1) 1 2 3 4 5 6 Specify your cloud account … Access your Cloud Dashboard … … and get logged in … and choose what kind of instance to create Build a new compartment for your ADWC instance … … and check out the other compartments available

  8. AuDB: Creating a New Instance (2) 1 2 3 4 Specify a compartment and administrator credentials … ADW instance now shows up in chosen compartment … … and ADW instance creation begins! … and your first ADW instance is now ready to access

  9. AuDB: Creating a New Instance (3) … and save the new credentials in TNSNAMES home 1 2 3 5 4 Connect to the new instance using the ADMIN account … Request new credentials for access … Here’s your first look at the ADW Service Console! … supply a robust password …

  10. AuDB: Monitoring Instance and Statement Performance 4 1 2 5 6 7 How is this AuDB instance performing right now, and are there any evident “pushbacks” against a running workload? Performance can also be viewed for a particular narrower time period … the statement’s execution plan … … and the statement’s use of parallelism Terminating a “wayward” session: Viewing an individual SQL statement’s performance … 3 Viewing the performance of running as well as completed individual statements

  11. ADW: Automatic Monitoring of SQL Statement Performance … and the actual operations consuming the most resources are easy to identify while the statement’s still running 2 1 3 4 5 SQL statements are automatically monitored for performance when they exceed 5 seconds of CPU or I/O activity … … and even specific CPU, memory, and I/O metrics … including specific activity like time and wait statistics and I/O performance … The report shows how many degrees of parallelism the SQL statement is leveraging …

  12. AuDB: Automatically Provided Database Services • SQL> BEGIN • CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( • consumer_group => 'HIGH’ • ,io_megabytes_limit => 10 • ,elapsed_time_limit => 30 • ); • END; • / PL/SQL procedure successfully completed. See the detailed documentation for complete information on how these database services work. When you create an AuDB database, up to five database services are also automatically provisioned: Format: [instance name]_{TPURGENT | TP | HIGH | MEDIUM | LOW} Each service provides different levels of application resource provisioning Leverages Database Resource Manager (DBRM) and I/O Resource Manager (IORM)

  13. Examples of Automatically-Provided RCGs and Database Services See the detailed documentation for complete information on how these database services work.

  14. ADW: But What If I Run Out of Resources? Workload Distribution:3 – HIGH6 – MEDIUM8 - LOW Workload Exhaustion Demonstration using TPC-DS Top 15 “Ugly” Queries Start-out: 4 OCPUs … Ramp-up:6 OCPUs … Ramp-down: 2 OCPUs …

  15. ATP: Turning the “Big Red Dial” Workload Exhaustion Demonstration:Five different workloads simultaneously executed against SOE schema 1 2 3 Requesting CPU scale-up … … and successful CPU scale-up completed Scale-up in progress … After scale-up, TPURGENT performance improves … … and there’s a decrease in queued statements … the number of executing statements increases …

  16. Auto-Scaling

  17. OCPU Auto-Scaling: What, Me Worry? AuDBessentially gives an Enterprise Data Architect (EDA) only two choices to impact performance: • Which database service should this application workload use to obtain best performance? • How many OCPUs does my database require to sufficiently support its application workloads? What if we couldreduce the decisions we need to make … by 50%?

  18. Auto-Scaling: One-Button Activation. 1 2 3 Activating OCPU Auto-scaling … and successful Auto-Scaling established Auto-scaling activation in progress …

  19. Auto-Scaling: Automatically Turning the “Big Red Dial” Before the workload starts, OCPUs are stable at two (2) As the workload ramped down in intensity, Auto Scaling automatically compensates by reducing OCPUs to three (3) Once the workload completes, OCPUs are automatically ratcheted back down! As an intense TPC-E workload ramps up, Auto-Scaling detects this and ramps up OCPUsto four (4)

  20. Automatic Index Creation (AIC)

  21. Automatic Index Creation (AIC) Exadata technology - the same hardware that underlies all Autonomous Database services – has proven that in many cases, a full table scan is often simply the best mechanism to quickly return a small result set from huge datasets • Exadata uses Smart Scan technology and techniques to quickly filter only needed data from large datasets, often leveraging parallel processing, partitioning, and storage indexes • Smart Scan is aided by retaining data in Hybrid Columnar Compression (HCC) units, both on physical storage as well as within the Exadata Smart Flash Cache • However, a Smart Scan operation may be ignored in several known cases: • Parallelism is unwarranted or unapplied • A significant amount of data has been changed in a large table or partition • The table is just too darn small • Online Transaction Processing (OLTP) operations cannot always leverage Smart Scan due to transaction scope (which tends to be quite small) • Index range scans are still somewhat efficient in niche cases Therefore: Not all secondary indexes are evil!

  22. AIC: Basic Concepts

  23. Activating and Controlling AIC 1 Is AIC activated? And if so, at what levels? SQL> SELECT parameter_name, parameter_value FROM dba_auto_index_config; AIC is already activated and it’s running in fully automatic mode. It can also be set to only gather intelligence for later index implementation (REPORT ONLY) or even deactivated (OFF) AIC Parameter Setting -------------------------------- --------- AUTO_INDEX_COMPRESSION OFF AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEX_MODE IMPLEMENT AUTO_INDEX_REPORT_RETENTION 31 AUTO_INDEX_RETENTION_FOR_AUTO 373 AUTO_INDEX_RETENTION_FOR_MANUAL AUTO_INDEX_SCHEMA AUTO_INDEX_SPACE_BUDGET 50 AIC logging will be retained for 31 days; any AIC-created indexes will be retained for at least 373 days … … and up to 50% of the AIC default tablespace can be allocated for AIC –created indexes

  24. Are There Any AIC-Created Indexes Already? The SYS_AI prefix is proof that AIC has been hard at work … SQL> SELECT owner, table_name, index_name, visibility FROM dba_indexes WHERE auto = 'YES' ORDER BY 1,2,3; OWNER TABLE_NAME INDEX_NAME VISIBILITY ----- -------------------------------- -------------------------------- ------------ TPCE ADDRESS SYS_AI_c3s7r26pujqyw INVISIBLE TPCE COMPANY SYS_AI_5bz560x7mkmm5 INVISIBLE TPCE COMPANY SYS_AI_8m4x6f5kzagpk INVISIBLE TPCE COMPANY_COMPETITOR SYS_AI_g16pbfxkcvndg INVISIBLE TPCE CUSTOMER_ACCOUNT SYS_AI_2nq8gbwuzdb6j INVISIBLE TPCE CUSTOMER_ACCOUNT SYS_AI_d3kmzk12banrf VISIBLE TPCE DAILY_MARKET SYS_AI_804nrthjdbw65 INVISIBLE TPCE FINANCIAL SYS_AI_f48n5gfuab5xd INVISIBLE TPCE HOLDING_HISTORY SYS_AI_c2vqkp1cnaynt INVISIBLE TPCE HOLDING_SUMMARY SYS_AI_5unr1rda8j36j INVISIBLE TPCE NEWS_XREF SYS_AI_8v3ttavnf0607 INVISIBLE TPCE SECURITY SYS_AI_2gm9u3nyxkwcu INVISIBLE TPCE SECURITY SYS_AI_4qyqwxyvbn1ut INVISIBLE TPCE TRADE SYS_AI_1hjwdmk5q4mkz VISIBLE TPCE TRADE SYS_AI_385rjxyamg6nd VISIBLE TPCE TRADE SYS_AI_a8805thkfs7ua VISIBLE TPCE TRADE SYS_AI_f03t41rg8f49c INVISIBLE TPCE TRADE_HISTORY SYS_AI_cbcn2hcywmvcb INVISIBLE TPCE WATCH_ITEM SYS_AI_cz35y7a104y79 INVISIBLE TPCE WATCH_LIST SYS_AI_3h68gvvm1cmvr INVISIBLE … but even though AIC created these indexes automatically, some are still INVISIBLEbecause they’re not yet valuable enough to improve workload performance

  25. AIC: Leveraging the DBMS_AUTO_INDEX Package

  26. AIC: Results of Automatic Index Visibility 1 2 3 Note that the addition of just 4 VISIBLE indexes increased overall performance by almost 290X: ... and for another statement, an improvement of 125796X. Just one small example of how AIC improved a single statement’s performance by 53X …

  27. Autonomous DB: Wrap-Up Summary of Features ADW vs. ATP Use Cases and Workload Recommendations Where Is Your DBA Career Headed?

  28. DBaaS vs. Autonomous Database: Comparison of Capabilities *Unless you decide to implement OCPUAuto-Scaling!See this blog post for some great examples of these capabilities for Autonomous Databases. • In 12.1.0.2, the In-Memory Area was in essence a static cache • Resizing the cache required bouncing the database instance

  29. Remember, ATP and ADW are all about no more knobs … and that’s really advantageous! Service instance can be stopped and restarted as necessary Useful for conserving Cloud credits Easy to connect to Only a few entries in SQLNET.ORA file and TNSNAMES.ORA are required Regular RMAN backups taken automatically on regular nightly schedule Cloning is reduced to supplying a new database name and a few clicks No instance tuning required Memory pool sizes are already locked in Parallelism is automatically derived depending on number of OCPUs and service name selected Only appropriate licensing options are included No worries about accidently incurring potential additional licensing fees Direct-path loads are fully supported DataPump Export and Import provides for rapid provisioning from existing databases GoldenGatesupport has been added as well AuDB: Advantages of “No More Knobs“

  30. AuDB: A Force Multiplier, But Not FullyAutomagic (Yet) • Virtually noDBA resources required for database management • Noinstance tuning is necessary • Selection of appropriate database service for the workload is really the only choice required • Parallelism derived from database service selected and number of OCPUs available • Scale-up and scale-down requires just a single button push… or not at all, if you auto-scale! • Database migration and transformation only limited by desired / appropriate transferal methods • Fresh load: DBMS_CLOUD.COPY_DATA,SQL*Loader, or INSERT INTO … SELECT FROM an EXTERNAL Table • Existing schema(s): DataPump Import • Tight synchronization required:GoldenGate • Extremely large data transfers possible via Oracle Cloud Infrastructure Data Transfer Appliance

  31. ADW: Summary of Appropriate Use Cases ADW is most appropriate for the following application workload requirements and environments: • Read-only orread-mostlyapplication workloads focused on leveraging analytical processing • All features of Exadata storage cell software – Smart Scan, Smart Storage, storage indexes - brought to bear on processing workloads • Tables are also cached in columnar format automatically with Exadata Smart Flash Cache • All query results are automatically saved within SQL Query Result Caches, thus limiting future re-read of same data • Up to 128 OCPUs and 128 TB of storage can be requested per ADW instance (subject to availability within instance’s region) • Ideally, data warehousing application workload(s) should already be well-tuned to avoid surprises

  32. ATP: Summary of Appropriate Use Cases ATP is most appropriate for the following application workload requirements and environments: • Hybrid workloads, including OLTP and moderate reporting • Exadata storage software caches most frequently used database blocks in flash memory on storage cells • Up to 128 OCPUs and 128 TB of storage can be requested per ATP instance (subject to availability within instance’s region) • Key features of Exadata storage cell software beneficial to heavy OLTP workloads (especially Smart Storage) are brought to bear on processing workloads • Huge amount of memory in Exadata Smart Flash Cache means single-block I/O is often significantly reduced • Exadata’s write-through cache and Smart Flash Logging makes short work of committed transactions • Ideally, OLTP application workload(s) should already be well-tuned to avoid surprises • Automatic Index Creation (AIC) will be helpful for hands-free tuning of “hungrier” application workloads

  33. Either Drive a School Bus, or Program Its Replacement. Your Choice. You can be a school bus driver kind of DBA – oblivious to the elimination of your job in just a few years … It’s all up to you. … or become an Enterprise Data Architect, ahead of the curve, ready to take advantage of Autonomous Database and the future it portends

  34. ADW Documentation: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html OCPU Auto-Scaling: https://docs.cloud.oracle.com/iaas/Content/Database/Concepts/adboverview.htm#scaling Dominic Giles’s Blog on Setting Up SwingBench for ADW: http://www.dominicgiles.com/blog/files/7fd178b363b32b85ab889edfca6cadb2-170.html ATP Documentation: https://docs.oracle.com/en/cloud/paas/atp-cloud/index.html Dominic Giles’s Blog on Setting Up SwingBench for ATP: http://www.dominicgiles.com/blog/files/c84a63640d52961fc28f750570888cdc-169.html Maria Colgan on What to Expect From ATP Cloud: https://blogs.oracle.com/what-to-expect-from-oracle-autonomous-transaction-processing https://blogs.oracle.com/what-to-expect-from-oracle-autonomous-transaction-processing-cloud Oracle Cloud Infrastructure Data Transfer Appliance: https://blogs.oracle.com/cloud-infrastructure/introducing-oracle-cloud-infrastructure-data-transfer-appliance Using GoldenGate to Replicate Data to ADW Cloud: https://docs.oracle.com/goldengate/c1230/gg-winux/GGODB/replicating-data-oracle-autonomous-data-warehouse-cloud.htm#GGODB-GUID-660E754E-B9A6-48DD-AA66-0D6B66A022CD Useful Resources and Documentation

More Related