Transcription
1Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Step-by-Step Cookbook forIdentifying and Tuning SQLProblemsAshish Agrawal - Consulting Product Manager, OracleBaki Şahin- Database Operation Supervisor, AveATurkey
Safe Harbor StatementThe following is intended to outline our general product direction. It is intended forinformation purposes only, and may not be incorporated into any contract. It is not acommitment to deliver any material, code, or functionality, and should not be reliedupon in making purchasing decisions. The development, release, and timing of anyfeatures or functionality described for Oracle’s products remains at the sole discretionof Oracle.This document in any form, software or printed matter, contains proprietary informationthat is the exclusive property of Oracle. Your access to and use of this confidentialmaterial is subject to the terms and conditions of your Oracle Software License andService Agreement, which has been executed and with which you agree to comply.This document and information contained herein may not be disclosed, copied,reproduced or distributed to anyone outside Oracle without prior written consent ofOracle. This document is not part of your license agreement nor can it beincorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.3Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Program Agenda Why SQL statements regress? Identifying problematic SQL Tuning SQL Preventing SQL problems Real-World Customer Experiences- AveA, Turkey- S. Corporation, Korea4Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Program Agenda Why SQL statements regress? Identifying problematic SQL Tuning SQL Preventing SQL problems Real-World Customer Experiences- AveA, Turkey- S. Corporation, Korea5Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Why SQL statements regress?3 Broad CategoriesOptimizerApplicationResource and contentionIssues6Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Why SQL statements regress?Optimizer-relatedStale/Missing statisticsOverly general statistics or incorrect histogramsImproper optimizer configurationUpgraded database: new optimizerChanging statistics (refresh)Changing data (plans do not scale with data)Bind-sensitive SQL with bind peekingNot parallelized (no scaling to large data)Improperly parallelized (skews, RAC, etc.)7Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Why SQL statements regress?Application-relatedMissing access structuresPoorly written SQLstatementsLiteral usage8Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12Bad execution plans- Full table scans- Cartesian join
Why SQL statements regress?Resource and Contention Issues-relatedHardware resource crunch- CPU, Memory, IO, NetworkData fragmentationLogical Contention- Row lock contention- Block update contention9Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12Example:enq: TX - allocate ITL entryenq: TX - contentionenq: TX - index contentionenq: US - contention
Sub-optimal SQL performance: SymptomsSymptomsConsumes high CPU, buffer gets, I/O, PGA memory Long running SQL or significantly differentruntimes High I/O, CPU, memory, network waits TX Enqueue Waits, Row Lock Contention Plan regression SQL appear in- Top Activity Page- ASH Analytics Page- ADDM Report, AWR Report, ASH reports10Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Program Agenda Why SQL statements regress? Identifying problematic SQL Tuning SQL Preventing SQL problems Real-World Customer Experiences- AveA, Turkey- S. Corporation, Korea11Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
How to identify these SQL performanceproblems?Identify SQL performance problems using:12SQLs consuming high DBtimeADDM and ASH AnalyticsLong running SQLs andoperationsReal-time SQL Monitoring &Database OperationsMonitoringSQLs with execution planchangesSQL Performance Analyzer(proactive)Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Identify expensive SQL (Excessive DB time):ADDM and ASH AnalyticsADDMASH Analytics13Copyright 2012, Oracle and/or its affiliates. All rights reserved. Analyze current database performancethrough ADDM runs Next generation Top Activity PageInsert Information Protection Policy Classification from Slide 12
Identify expensive SQL: ADDMSQL consuming too much DB timeADDMThroughput centric: Goal is toreduce ‘DB time’Identifies top SQLShows SQL impactFrequency of occurrencePinpoints root cause14Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Identify expensive SQL: ASH AnalyticsFlexible Time PickerFlexible Activity ChartFlexible Top Chart15 Copyright 2011, Oracle and/or its affiliates. All rights reserved. Flexible Top Chart
ASH AnalyticsActive Reports Demo16Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Identify expensive SQL: ASH AnalyticsActive Reports Demo17Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12Click on Save
Identify long running SQL:Real-Time SQL MonitoringAutomaticallymonitorsinstances oflong runningSQL, PL/SQLexecutions18Enabled outof-the-box withnoperformanceoverheadCopyright 2012, Oracle and/or its affiliates. All rights reserved.Obviates needto traceindividual SQLInsert Information Protection Policy Classification from Slide 12Shows globalPL/SQL andSQL levelstatisticsGuides tuningefforts
Identify long running Database operations:Real-Time Database Operations Monitoring New inDatabase monitoring ofapplication jobs Grouping of SQLs, sessionsfor the application jobs Key scenarios: ETLoperations, Quarter End ClosejobsDriven by applicationspecified tagging Oracle Data Pump jobsautomatically monitored Tagging ability in PL/SQL,OCI, JDBCVisibility into top sql statements19Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Identify SQL plan changes: SPASPA Tests and predicts impact of system changeson SQL query performance Analyzes overall performance impactincluding improvements or regressions Common plan change scenarios Database parameter, schema changes Statistics gathering refresh I/O subsystem changes, Exadata Database upgrades or patches
Program Agenda Why SQL statements regress? Identifying problematic SQL Tuning SQL Preventing SQL problems Real-World Customer Experiences- AveA, Turkey- S. Corporation, Korea21Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Tuning SQL: SQL Tuning AdvisorGather Missing or StaleStatisticsSQL ProfilingStatistics AnalysisCreate a SQL ProfileAccess Path AnalysisAdd Missing AccessStructuresSQL Restructure AnalysisModify SQL ConstructsAlternative Plan AnalysisParallel Query AnalysisSQL TuningAdvisorAdministratorAdopt AlternativeExecution Plan (11.2)Create Parallel SQLProfile (11.2)Automatic Tuning OptimizerComprehensive SQL TuningRecommendations Multitenant database-aware All SQL’s are tuned across all PDB’s where it has executed before. Empowers the CDBA to tune across PDBs in one click22Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12New in
Tuning SQL: SQL Tuning AdvisorSQL ProfilingAutomatic Tuning Optimizer verifies andadjusts its own cardinality estimatesOptimizer provides additional informationto generate execution planTest executes the recommendedexecution plan for performanceReviews execution history23Copyright 2012, Oracle and/or its affiliates. All rights reserved. Ideal when cardinality estimates are wrong, collection ofstatistics on objects or creation of new indexes is requiredInsert Information Protection Policy Classification from Slide 12
Tuning SQL: SQL Plan BaselinesSome optimizer related change inthe environment results in a newplan being generatedNew plan is not the same as thebaseline – new plan is notexecuted but marked forverificationVerifying the new plan1) DBA can verify plan at any time2) Adaptive SPM, SPM Evolve Advisorruns daily in the scheduledmaintenance window.Execute known plan baseline plan performance is “verify byhistory” Ideal when you need to preserve and use good, known and verifiedexecution plans
Tuning SQL: SQL Access AdvisorRecommendationsIndexesMaterializedviews andview logs B-tree indexes Bitmap indexes Function-based indexes Fast refreshable Full refreshable MVsPartitiontable Range, Interval, Hash, RangeHash, Range-List type, List New partitioning schemes onalready partitioned tablesPartitionindex Local, Range, Hash type Ideal when you need advice for creation of indexes, mviewsand partition for your entire workload
Tuning SQL: Real-time SQL Monitoring CaseStudyI enabled parallel query, yet this query is taking so long. What’s going on?Parallel server downgrades? Uncontrolled parallel executionParallel Server availabilityObject level settingsSession level settings Ideal when you need to tune long running SQLs, complex queries with big executionplans, parallel queries, DML and DDL statements, Exadata smart scans, cases of a poorindexing strategies26Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Tuning SQL: Real-time SQL MonitoringInsert executed with parallel hint
Tuning SQL: Real-time SQL MonitoringParallel Tab Parallel Coordinator busy for the entire duration!!
Tuning SQL: Real-Time SQL MonitoringSolution: Enabled Parallel DML Parallel Slaves busy for the entire duration!!!
Program Agenda Why SQL statements regress? Identifying problematic SQL Tuning SQL Preventing SQL problems Real-World Customer Experiences- AveA, Turkey- S. Corporation, Korea30Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Preventing SQL problems:Finding Regressed SQL statements proactively using SPACopyright 2011, Oracle and/or its affiliates. All rights reserved.
SPA ChallengesRunning SPA on:Test System: Safe But TestDatabaseProductionDatabase Requires separate HWProduction System: Easier but Could be resource intensive andimpact production performance Data in test system should besame as production Changes needs to be manuallyscoped to private session Lengthy, error-prone task Could take a long time to finishDBA32Copyright 2013, Oracle and/or its affiliates. All rights reserved. No resource control by defaultInsert Information Protection Policy Classification from Slide 12 of the corporate presentation template
SPA Quick CheckNew in EM 12c Database Plug-in 12.1.0.5Supports routineproductionchange usecases Optimizer Gather StatisticsInit.ora parameter changesIndex creationSupport for DB Release 11g and aboveSPA Quick CheckPer SQL Time Limits,Resource ConsumerGroupControlledSimplifies preciseidentification ofplan regressionsDesigned andoptimized forproduction use Uses pre-selected STS that representsworkload to be tested Default SPA settings Optimal Trial or Explain Plan Mode Per SQL Time Limit, Resource ConsumerGroup Limits testing scope to private sessionOptimal TrialModeOptimizedScopedLimits testing scopeto private sessionPre-selectedSTS anddefault SPAsettingsProductionDatabase
SPA Quick CheckOptimal Trial ModeIdentifies subset SQLworkload with planchanges firstTest-executes onlySQLs with planchangesMinimizes use ofproduction resourcesdramatically In general, resourceconsumption reduced in therange of 10x or more
SPA Quick Check: SetupMinimal use of production resourcesPer-SQL Time Limit - preventsany runaway SQLsDisables Multiple ExecutionsUses Resource Consumer GroupExecutes only the query part ofthe DML without modifying data
SPA Quick CheckLaunch SPA in-line with Gather Optimizer Statistics workflow
SPA Quick CheckWorkflow for validating Gather Optimizer StatisticsOptimizer statistics gathering option PUBLISH set to FALSE temporarilyduring the processFour trials automatically executed, compared and reports generated
SPA Quick CheckWorkflow for Validating Gather Optimizer StatisticsProvidesactionablerecommendationson the report38Publish thepending statisticsCopyright 2012, Oracle and/or its affiliates. All rights reserved.Create SQL TuningSet for regressedSQLsOffers two optionsto fix regressedSQL resulting fromplan changesInsert Information Protection Policy Classification from Slide 12Create SQL PlanBaselinesExplore alternateexecution plansusing SQL TuningAdvisor
SPA Quick CheckGather Optimizer Statistics – SPA ReportSPA Reportwithout anyregression
Preventing SQL problems: SPA Quick CheckInit.ora changes: Validate optimizer related init parameter changeFirst trial with currentparameter valuesParameter changeonly effective inprivate sessionSecond trial withchanged valuesGeneratescomparison reportProvides actionablerecommendations onthe report (buttons)Note: Applies to optimizer related changes and session parameters only
Preventing SQL problems: SPA Quick CheckNew index creation: Use SPA Quick Check to find any plan changesFirst trial withcurrent environmentCreate index ininvisible modeSecond trial withindex invisibleGeneratescomparison reportProvides actionablerecommendations onthe report (buttons)Note: Indexes are only visible in a scoped manner to the session, uses optimizer use invisible indexes true in private session
Program Agenda Why SQL statements regress? Identifying problematic SQL Tuning SQL Preventing SQL problems Real-World Customer Experiences- AveA, Turkey- S. Corporation, Korea42Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
11g Upgrade Project using OracleReal Application TestingBaki ŞahinDatabase OperationSupervisor
AVEAAvea is the youngest GSM operator of Turkey with its 13.6 million customers. The only GSM 1800 mobile operator of Turkey Founded in 2004 merged of 2 GSM Operators (Aria & Aycell) Nationwide customer base of 13.6 million Provide GSM service 98% of the population in Turkey AVEA Around 3000 people work for Avea Certified as an R&D company in 2010
Prepaid History Workload DescriptionApplication All transaction related to prepaid customers except bonus Store last 6 months traffic Business rules for post-sales processes. (Rule engine) Configuration of subscriber servicesDatabase Database size is 2.5Tb Workload can roughly be classified into 2 parts: OLTP transactions is done during business hours Batch processing is done after business hours
Overview of Configuration
10.2 11g Database Upgrade
Compare Performance
Regressed SQL StatementsPlans unchanged Majority of plans unchanged Unlock statistics collections for tables Collects optimizer statistics again using 11g database Run SPA againPlans changed – fixing regressions Create Baselines Work with developers Indexes created or dropped Run SPA again
ConclusionBenefits for us Risk reduction Replays All SQL Real Bind Values Test production SQL workload before upgrade Tune regressed SQL statements No surprises when upgraded to 11.2.0.3 Report what will happen before upgrade To be more safe Reusable process Reduced resource requirements 1 team against 4 teams Fully proven recommendations Easier to convince business
S. CorporationLarge Manufacturing Company inSouth Korea: Case Study
Agenda1. Project Overview2. Oracle Solution for Upgrade3. Results52Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12 Insert Picture Here
Project Overview Customer Information- S. Corporation: Manufacturing company in Korea. Target: ERP System- SAP ERP system.- ECC : Logistics, Finance, XI and other sub systems with RAC configuration- Production, Test, DR and etc. Necessity of 11g Upgrade- 11g upgrade for a stable service environment- 10g version support instability due to End of Support period- Database environment improvement through 11g new function utilization 11g Upgrade Schedule- Project Term : Feb, 2012 – Dec, 2012- Test Process: Procedure Test Stability Test Application Test (Function, Performance, Compatibility) Rehearsal Cut Over53
Solution for UpgradeOracle Real Application TestingFactorPain PointHowResultDescription Regression had to be checked before the upgrade due to business criticality Time and cost issue for test and verification of more than one million SQLswithin 4 months. Low efficiency for SQL verification, if done manually. PoC for more than 2 months to validate the real effectiveness of RAT andto assess potential upgrade risks, which can’t be found without RAT. Sort sequence changing target program test through module source check. Performance test & tuning for individual SQL through RAT SPA. (10g vs. 11g ) Contribution to stable 11g Upgrade through SQL Change Risk exclusion . 1 million SQLs Performance tuning through SQL Performance Analyzerutilization Reduced upgrade project time through auto verification.54
ResultsFactorDatabasePerformanceImprovementRAT SolutionUtilizationCollaborationContent Improved performance through the Optimizer enhancement:About 15 20% improvement RAC efficiency, CPU usage monitoring improvementthrough the EM function improvements : About 20% improvement Over 1 million SQLs Performance testing using SPA Stable 11g upgrade of the most mission critical system through SQL changerisk elimination Verification exercises through the detailed procedure definitionand sufficient tests. Successful collaboration between the customer TFT and Oracle team(Local and Global) through close communication.55
Database ManageabilityRecommended SessionsSessionGEN8792General SessionGeneral Session: Database Management Innovations - Oracle Database 12c Manageability HighlightsSessionSessionDayTimeWednesday 10:15 AMLocationMoscone South – 103DayTimeLocationCON9582Oracle Exadata Management Deep Dive with Oracle Enterprise Manager 12cMonday12:15 PMWestin - Metropolitan ICON9573Managing the Oracle Identity Management Platform with Oracle Enterprise ManagerMonday1:45 PMMoscone South - 130CON9578Automatic Workload Repository Soup to Nuts: Fundamentals of Database Performance AnalysisMonday3:15 PMMoscone South – 104CON8788Maximizing Database Performance with Database ReplayTuesday10:30 AMMoscone South - 308CON9583Harness the Power of Oracle Database 12c with Oracle Enterprise Manager Database as a ServiceTuesday3:45 PMMoscone South – 305CON9579Step-by-Step Cookbook for Identifying and Tuning SQL ProblemsWednesday1:15 PMMoscone South – 103CON4666Oracle Enterprise Manager 12c Database Lifecycle Management Automatic Provisioning and PatchingWednesday3:30 PMMoscone South – 131CON8768DBA Best Practices for Protecting Data Privacy with Oracle’s Data MaskingWednesday3:30 PMMoscone West – 2024CON9577Active Session History Deep Dive: Advanced Performance Analysis TipsWednesday3:30 PMMoscone South – 104CON3255Being Sure: Confident Consolidations with Oracle Real Application Testing 12cWednesday5:00 PMMoscone South – 306Thursday12:30 PMMoscone South - 104CON310356Real Oracle Real Application Testing: What to Expect and Prepare ForCopyright 2013, Oracle and/or its affiliates. All rights reserved.
57Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert Information Protection Policy Classification from Slide 12
Tuning SQL: SQL Access Advisor Recommendations B-tree indexes Bitmap indexes Function-based indexes Indexes Fast refreshable Full refreshable MVs Materialized views and view logs Local, Range, Hash type Partition index Ideal when you need advice for creation of indexes, mviews and partition for your entire workload
