1 / 43

Patty Charlebois Green Shield Canada

Patty Charlebois Green Shield Canada. Successful Upgrades Start with a Good (Execution) Plan. Speaker Qualifications. Senior DBA and Supervisor of DBA Team at Green Shield Canada 15 Years DBA experience Published articles in ODTUG Technical Journal, IOUG Best Practices Booklet

dusty
Télécharger la présentation

Patty Charlebois Green Shield Canada

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. Patty CharleboisGreen Shield Canada Successful Upgrades Start with a Good (Execution) Plan

  2. Speaker Qualifications • Senior DBA and Supervisor of DBA Team at Green Shield Canada • 15 Years DBA experience • Published articles in ODTUG Technical Journal, IOUG Best Practices Booklet • Presented at Collaborate, SEMOP, UKOUG, and RMOUG

  3. Introduction

  4. Introduction • Upgrading an Oracle database is a stressful job but somebody has to do it! • One of the biggest unknowns these days is whether or not your SQL statements will still perform well after the upgrade. • The goal of this presentation is to show you a proactive method that we at Green Shield used to upgrade a database WITHOUT SQL statement performance degradation.

  5. Introduction • The title of this presentation should have been “Successful Upgrades Start With a Good (EXECUTION) Plan”. • This presentation is not about how to upgrade an Oracle database and it is not about how to optimize your SQL statements to perform better.

  6. General Upgrade Guidelines

  7. Be Prepared • Read the documentation, especially the Upgrade Guide • Make sure you have all of the prerequisites before you start • Be sure to check for Compatibility and Interoperability • Don’t leave unresolved installation issues, make sure you have a successful installation BEFORE you attempt an upgrade of an existing database

  8. Start With a Baseline • Understand your current system • Know what the current performance levels are, otherwise how would you know things have changed? • Measure actual user-valued response times • Measure actual batch processing times • These are the things that you want to be the same or better after the upgrade

  9. Review Optimizer Changes • Check out the Oracle blog “Inside the Oracle Optimizer – Removing the Black Magic” • Look for and read the experiences of others who have already upgraded • Find out what changes have been made to the Optimizer and think about which of your SQL statements or applications the changes may affect

  10. Know Tolerance Levels • Before you begin you should understand what your Business Community will tolerate • You may find that you have many performance issues and you can’t fix them all right away • Know which SQL Statements or applications are most critical from a performance point of view • Ask for documented performance levels from your users

  11. Create a Test Plan • Use a spreadsheet or complete Project Plan • Ensure all areas of testing are covered • Should have a deployment strategy and a SQL collection strategy • Functional tests ensure compatibility but you should also test for performance

  12. Create a Test Plan • Collect SQL statements and Optimizer Statistics from Production • Make sure you have the right people for the testing jobs • This presentation will focus on the Performance Testing aspect

  13. A Trip to the SPA

  14. SQL Performance Analyzer (SPA) • A key feature of Oracle’s new Real Application Testing Option • Predicts and helps prevent SQL execution performance issues • Allows you to execute and/or gather explain plans for SQL statements before and after a change

  15. SQL Performance Analyzer (SPA) • Validates how a system change will affect SQL statement performance • Can be used for changes including upgrades, patches, parameter changes, schema changes, Optimizer statistic refreshes, application SQL statement change • Requires the Real Application Testing Option license in Test and the EM Tuning Pack in Production

  16. Availability in Oracle 10g • First release of SPA created for 11.1.0.6 • Functionality added to 11.1.0.7 (or 11.1.0.6 + a patch) to allow use on earlier releases (9i & 10g) • Uses a small Oracle 11g database as the ‘SPA System’ • Use in earlier releases accomplished through a public database link from ‘SPA System’ to 9i and/or 10g databases • This presentation will use SPA to upgrade a database from 10.2.0.4 to 11.1.0.6.

  17. Basic SPA Functionality • Five Primary Steps • Capture SQL Statements from Production • Create a SQL Tuning Set (STS) • Transport the STS to Test and SPA System • Execute SQL Pre-Change • Statements in the STS executed in Test database • Creates pre-change performance data • Make the Change

  18. Basic SPA Functionality • Execute SQL Post-Change • Statements in the STS executed in Test database • Creates post-change performance data • Compare Performance data • SPA identifies changes in execution plans and/or explain plans • SQL statements are ‘weighted’ by SPA using # of executions as a metric • Uses elapsed time by default as a comparison metric but you can use others

  19. Other Options Available • SQL statement performance issues started with our upgrade to Oracle 10g • SPA did not exist but we found an alternative product • We successfully used the Hotsos Laredo tool for the same functionality and purpose • Our upgrade from 9i to 10g did not include any SQL statement performance issues, we proactively fixed the SQL ahead of time • www.hotsos.com

  20. Testing an Upgrade Using SPA Are you Ready for this?

  21. Prepare for the Test • Install Oracle 11.1.0.6 and patch 6865809 on a test server. • The patch is necessary to enable your 11g ‘SPA system’ to consume SQL trace files from previous releases • Another (untested by me) option is to install 11.1.0.7 with no patch • Create an Oracle 11g database on the test server. • This is a small ‘SPA System’ database, no schema or data necessary. • Run all scripts outlined in the README for patch 6865809 to generate the Oracle-supplied packages properly

  22. Prepare for the Test • Configure EM Database Control • If you want to execute SPA from EM, you will need to configure it in you SPA system database • You don’t need this if you want to just use the Oracle supplied packages to run SPA. • Create a 10g test database on the test server • Clone prod if possible, if not, make it similar and import prod Optimizer Stats (dbms_stats.export_stats) • Make init.ora Optimizer parameters the same as prod

  23. Prepare for the Test • Create a PUBLIC database link. • Create the link in the SPA System database to your test database • Create public database link link_name connect to userid identified by password using ‘testdb.name’; • Grant privileges to the SPA user • The userid in the test database that the database link connects to must have execute on dbms_sqlpa and ADVISOR privilege.

  24. Prepare for the Test • Disable gathering of Optimizer stats • Do this in the test database so that it does not interfere with your test • Dbms_scheduler.disable (‘GATHER_STATS_JOB’);

  25. Capture Production Workload • Capture SQL statements and create a SQL Tuning Set (STS) • Can be done through OEM or Manually • Manually done using DBMS_SQLPA package • Instructions in Oracle 11g Documentation

  26. Capture Production Workload • OEM capture done from Performance page

  27. Transport the STS • SQL Tuning Set is moved to the SPA System and the Test database • First, create table to store the STS BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => ‘PRD_SPA_TAB’); END; /

  28. Transport the STS • Now, pack the STS into the table BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET( Sqlset_name => ‘PRD_DAILY_STS’, Staging_table_name => ‘PRD_SPA_TAB’); END; / • Export the table and FTP it to the SPA System and test database server(s)

  29. Transport the STS • Unpack the STS from the table into both the SPA System and Test databases BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( Sqlset_name => ‘%’, Replace => TRUE, Staging_table_name => ‘PRD_SPA_TAB’); END; /

  30. Execute the Test • Can be done using OEM or manually using the Oracle supplied packages • SQL Performance Analyzer is located on the Performance page of OEM

  31. Execute the Test • Manual execution of the test involves four (4) steps: • Create the SPA Task. • Database object called an Analysis Task is the SPA task container that manages the state of the test variable prd_sts_task VARCHAR2(100); EXEC :prd_sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( - sqlset_name => 'PRD_DAILY_STS', - task_name => 'PRD_UPG_TEST', - description => 'process prod workload via scripts');

  32. Execute the Test • Execute the SQL Before the Change • Generate Explain plans or execution plans or both • Time limits for execution can be defined individually or for the complete set EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'PRD_UPG_TEST', - execution_type => 'TEST EXECUTE', - execution_name => 'PRD_STSEXEC_BEFORE_UPGRADE', - execution_params => dbms_advisor.arglist('DATABASE_LINK','TESTHCMP.WORLD'));

  33. Execute the Test • Upgrade the test database to 11g • Can be done manually or using Database Upgrade Assistant (DBUA) • Refer to the Oracle 11g Upgrade Guide

  34. Execute the Test • Execute the SQL After the Change EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'PRD_UPG_TEST', - execution_type => 'TEST EXECUTE', - execution_name => 'PRD_STSEXEC_AFTER_UPGRADE', - execution_params => dbms_advisor.arglist('DATABASE_LINK','TESTHCMP.WORLD'));

  35. Generate the Comparison Report • SQL Performance Analyzer looks for differences in things like performance, execution plans, and # of rows returned • Also shows errors encountered in either trial • Analysis based on Elapsed Time metric by default • You can choose a different metric such as CPU time

  36. Generate the Comparison Report • Report can be generated as HTML or Text, detailed or summary • Can be done through OEM or manually using DBMS_SQLPA • Eg: Set pages 50000 lines 200 long 50000 SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘PRD_UPG_TEST’, ‘TEXT’, ‘TYPICAL’, ‘SUMMARY’) from dual;

  37. Next Steps • Use the comparison report to decide which SQL statements need to be tuned, fix them, and run your test again • Poor performance can be caused by several factors • Ensure Optimizer init.ora parameters are the same, Optimizer stats are the same • You may need to use Hints for better SQL statement performance

  38. Next Steps • As a temporary fix, you may need to continue to use the previous version Optimizer for some SQL statements • The parameter OPTIMIZER_FEATURES_ENABLE allows you to do this and can be set at the session level. • Be sure to track and document all of the changes you make in test so that they can be done in production before you upgrade.

  39. Questions?

  40. Thank You Patty Charlebois Successful Upgrades Start With a Good (Execution) Plan • Feel free to contact me: Patty.Charlebois@greenshield.ca

More Related