WIXLIB

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 AgendaWhy SQL statements regress?Identifying problematic SQLTuning SQLPreventing SQL problemsReal-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 AgendaWhy SQL statements regress?Identifying problematic SQLTuning SQLPreventing SQL problemsReal-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 memoryLong running SQL or significantly differentruntimesHigh I/O, CPU, memory, network waitsTX Enqueue Waits, Row Lock ContentionPlan regressionSQL 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 AgendaWhy SQL statements regress?Identifying problematic SQLTuning SQLPreventing SQL problemsReal-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 jobsGrouping of SQLs, sessionsfor the application jobsKey scenarios: ETLoperations, Quarter End ClosejobsDriven by applicationspecified taggingOracle Data Pump jobsautomatically monitoredTagging 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: SPASPATests and predicts impact of system changeson SQL query performanceAnalyzes overall performance impactincluding improvements or regressionsCommon plan change scenariosDatabase parameter, schema changesStatistics gathering refreshI/O subsystem changes, ExadataDatabase upgrades or patches

Program AgendaWhy SQL statements regress?Identifying problematic SQLTuning SQLPreventing SQL problemsReal-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 historyIdeal when cardinality estimates are wrong, collection ofstatistics on objects or creation of new indexes is required23Copyright 2012, Oracle and/or its affiliates. All rights reserved.Insert 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 timeExecute known plan baseline plan performance is “verify byhistory”2) Adaptive SPM, SPM Evolve Advisorruns daily in the scheduledmaintenance window.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 typeIdeal 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 settingsIdeal 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 DMLParallel Slaves busy for the entire duration!!!

Program AgendaWhy SQL statements regress?Identifying problematic SQLTuning SQLPreventing SQL problemsReal-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 TestDatabaseProductionDatabaseRequires separate HWProduction System: Easier but Could be resource intensive andimpact production performanceData in test system should besame as productionChanges needs to be manuallyscoped to private sessionLengthy, error-prone taskCould 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