Upload File

sql tuning advisor

25
2/23/2015 SQL Tuning Advisor http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 1/25 SQL Tuning Advisor in Oracle SQL Developer 3.0 This tutorial contains the following sections: Purpose Time to Complete Overview Software and Hardware Requirements Prerequisites Creating a Database Connection Providing Privileges and Removing the existing Statistics on the Scott User Running the SQL Tuning Advisor on a SQL statement Implementing SQL Tuning Advisor recommendations Summary Purpose This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.0. Time to Complete Approximately 20 minutes. Overview The SQL Tuning Advisor analyzes highvolume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits. The recommendation or advice provided relates to the collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements. Oracle Database can automatically tune SQL statements by identifying problematic SQL statements and implementing tuning recommendations using the SQL Tuning Advisor. You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic. In this tutorial, you learn how to run and review the recommendations of the SQL Tuning Advisor. Note: Tuning Advisor is part of the Tuning Pack, one of the Oracle management packs and is available for purchase with Enterprise Edition. For more information see The Oracle Technology Network or the online documentation. Software and Hardware Requirements The following is a list of software requirements: Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed. Oracle SQL Developer 3.0. Prerequisites Before starting this tutorial, you should: 1. Install Oracle SQL Developer 3.0 from OTN . Follow the readme instructions here . 2. Install Oracle Database 11g with the Sample schema.

Upload: prashanth-reddy-burri

Post on 20-Nov-2015

58 views

Category:

Documents


2 download

Report

Tags:

DESCRIPTION

sql

TRANSCRIPT

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 1/25

    SQLTuningAdvisorinOracleSQLDeveloper3.0Thistutorialcontainsthefollowingsections:

    PurposeTimetoCompleteOverviewSoftwareandHardwareRequirementsPrerequisitesCreatingaDatabaseConnectionProvidingPrivilegesandRemovingtheexistingStatisticsontheScottUserRunningtheSQLTuningAdvisoronaSQLstatementImplementingSQLTuningAdvisorrecommendationsSummary

    PurposeThistutorialshowsyouhowtousetheSQLTuningAdvisorfeatureinOracleSQLDeveloper3.0.

    TimetoCompleteApproximately20minutes.

    OverviewTheSQLTuningAdvisoranalyzeshighvolumeSQLstatementsandofferstuningrecommendations.IttakesoneormoreSQLstatementsasaninputandinvokestheAutomaticTuningOptimizertoperformSQLtuningonthestatements.ItcanrunagainstanygivenSQLstatement.TheSQLTuningAdvisorprovidesadviceintheformofpreciseSQLactionsfortuningtheSQLstatementsalongwiththeirexpectedperformancebenefits.Therecommendationoradviceprovidedrelatestothecollectionofstatisticsonobjects,creationofnewindexes,restructuringoftheSQLstatement,orcreationofaSQLprofile.YoucanchoosetoaccepttherecommendationtocompletethetuningoftheSQLstatements.

    OracleDatabasecanautomaticallytuneSQLstatementsbyidentifyingproblematicSQLstatementsandimplementingtuningrecommendationsusingtheSQLTuningAdvisor.YoucanalsoruntheSQLTuningAdvisorselectivelyonasingleorasetofSQLstatementsthathavebeenidentifiedasproblematic.

    Inthistutorial,youlearnhowtorunandreviewtherecommendationsoftheSQLTuningAdvisor.

    Note:TuningAdvisorispartoftheTuningPack,oneoftheOraclemanagementpacksandisavailableforpurchasewithEnterpriseEdition.FormoreinformationseeTheOracleTechnologyNetworkortheonlinedocumentation.

    SoftwareandHardwareRequirementsThefollowingisalistofsoftwarerequirements:

    OracleDatabase11gEnterpriseEditionwithaccesstotheTuningandDiagnosticmanagementpacksandwiththesampleschemainstalled.OracleSQLDeveloper3.0.

    PrerequisitesBeforestartingthistutorial,youshould:

    1. InstallOracleSQLDeveloper3.0fromOTN.Followthereadmeinstructionshere.

    2. InstallOracleDatabase11gwiththeSampleschema.

    http://www.oracle.com/technetwork/developer-tools/sql-developer/rel3-relnotes-304121.htmlhttp://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.htmlhttp://download.oracle.com/docs/cd/B28359_01/license.111/b28287/options.htm#CIHFIHFG

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 2/25

    CreatingaDatabaseConnectionThefirststeptomanagingdatabaseobjectsusingOracleSQLDeveloper3.0istocreateadatabaseconnection.

    Performthefollowingstepstocreateadatabaseconnection:

    Note:IfyoualreadyhavedatabaseconnectionsforSCOTTandSYSTEM,youdonotneedtoperformthefollowingsteps.YoucanmovetoProvidingPrivilegestotheScottUsertopic.

    1. IfyouhaveinstalledtheSQLDevelopericononyourdesktop,clicktheicontostartyourSQLDeveloperandmovetoStep4.Ifyoudonothavetheiconlocatedonyourdesktop,performthefollowingstepstocreateashortcuttolaunchSQLDeveloper3.0fromyourdesktop.

    OpenthedirectorywheretheSQLDeveloper3.0islocated,rightclicksqldeveloper.exe(onWindows)orsqldeveloper.sh(onLinux)andselectSendto>Desktop(createshortcut).

    2. Onthedesktop,youwillfindaniconnamedShortcuttosqldeveloper.exe.DoubleclicktheicontoopenSQLDeveloper3.0.

    Note:Torenameit,selecttheiconandthenpressF2andenteranewname.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 3/25

    3. YourOracleSQLDeveloperopensup.

    4. IntheConnectionsnavigator,rightclickConnectionsandselectNewConnection.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 4/25

    5. TheNew/SelectDatabaseConnectiondialogopens.EntertheconnectiondetailsasfollowsandclickTest.

    ConnectionName:systemUsername:systemPassword:(SelectSavePassword)Hostname:localhostSID:

    6. Checkforthestatusoftheconnectionontheleftbottomside(abovetheHelpbutton).ItshouldreadSuccess.ClickSaveandthenclickConnect.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 5/25

    7. IntheConnectionsnavigator,tocreateanewconnectiontothescottschema,rightclickConnectionsandselectNewConnection.

    8. TheNew/SelectDatabaseConnectiondialogopens.EntertheconnectiondetailsasfollowsandclickTest.

    ConnectionName:scottUsername:scottPassword:(SelectSavePassword)Hostname:localhostSID:

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 6/25

    9. Checkforthestatusoftheconnectionontheleftbottomside(abovetheHelpbutton).ItshouldreadSuccess.ClickSaveandthenclickConnect.

    10. TheconnectionissavedandyoucanviewthetwonewlycreatedconnectionsintheConnectionslist.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 7/25

    ProvidingPrivilegesandRemovingtheexistingStatisticsontheScottUserAuserrequirescertainprivilegestoruntheSQLTuningAdvisor.Also,inordertocollectandmanagestatisticsontheSCOTTschema,theexistingstatisticshavetobecleared.BelowarethestepstograntSQLTuningAdvisorprivilegesandremovetheexistingstatisticsonthescottuser.

    1.ClickSQLWorksheet andselectsystemuser.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 8/25

    2. TograntprivilegestothescottusertoruntheSQLTuningAdvisor,enterthefollowinglinesofcode.ClickRun

    Statement .

    grantadvisortoscott

    grantadministersqltuningsettoscott

    3. Theoutputforthestatementsisdisplayed.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 9/25

    4. TheOracledatabaseallowsyoutocollectstatisticsofmanydifferentkindsinordertoimproveperformance.ToillustratesomeofthefeaturestheSQLTuningAdvisoroffers,cleartheexistingstatisticsfromtheSCOTTschema.

    Todeletetheschemastatistics,enterthefollowinglineofcode.

    execDBMS_STATS.DELETE_SCHEMA_STATS('scott')

    SelectthestatementandclickRunStatement

    .

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 10/25

    WiththeDBMS_STATSpackageyoucanviewandmodifyoptimizerstatisticsgatheredfordatabaseobjects.TheDELETE_SCHEMA_STATSproceduredeletesstatisticsforanentireschema.

    5. Theoutputforthestatementappears.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 11/25

    RunningtheSQLTuningAdvisoronaSQLstatementInthistopic,youruntheSQLTuningAdvisoronaSQLstatement.FourtypesofanalysisareperformedbytheSQLTuningAdvisorontheSQLstatement.

    AlltherecommendationsaredisplayedintheOverview.Youcanalsovieweachrecommendationindividually.

    1.OpentheSQLWorksheetforthescottuserbyclickingSQLWorksheet .

    2. EnterthefollowingSQLstatementintheworksheet.

    selectsum(e.sal),avg(e.sal),count(1),e.deptnofromdeptd,empegroupbye.deptnoorderbye.deptno

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 12/25

    NotethattheaboveSQLstatementhasanunusedreferencetothe"dept"table.

    3.SelecttheSQLstatementandclickSQLTuningAdvisor .

    4. TheSQLTuningAdvisoroutputappears.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 13/25

    5. Intheleftnavigator,clickStatistics.Inthisanalysis,objectswithstaleormissingstatisticsareidentifiedandappropriaterecommendationsaremadetoremedytheproblem.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 14/25

    6. Intheleftnavigator,clickSQLProfile.Here,theSQLTuningAdvisorrecommendstoimprovetheexecutionplanbythegenerationofaSQLProfile.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 15/25

    7. ClicktheDetailtabbedpagetoviewtheSQLProfileFinding.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 16/25

    8. Intheleftnavigator,clickIndexes.ThisrecommendswhethertheSQLstatementmightbenefitfromanindex.Ifnecessary,newindexesthatcansignificantlyenhancequeryperformancesareidentifiedandrecommended.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 17/25

    9. ClicktheOverviewtabbedpage.Inthiscase,therearenoindexrecommendations.

    10. Intheleftnavigator,clickRestructureSQL.Inthisanalysis,relevantsuggestionsaremadetorestructureselectedSQLstatementsforimprovedperformance.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 18/25

    ImplementingSQLTuningAdvisorrecommendationsYoucanimplementtheSQLTuningAdvisorrecommendationfeature.Thiswillenableyoutoupdatethestatisticsinscottschema.PerformthefollowingstepstoimplementtheSQLTuningAdvisorrecommendations:

    1. IntheConnectionsnavigator,rightclickscottandselectGatherSchemaStatistics....

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 19/25

    2. InGatherSchemaStatistics,selectEstimatePercentas100fromthedropdownlistsothatallrowsineachtableareread.Thisensuresthatthestatisticsareasaccurateaspossible.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 20/25

    3. ClickApply.

    4. Aconfirmationmessageappears.ClickOK.

    5. ToruntheSQLTuningAdvisorontheSQLstatementagain,selecttheSQLstatementandclickSQLTuning

    Advisor .

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 21/25

    6. TheSQLTuningAdvisoroutputappears.Bygatheringstatistics,theStatisticsandSQLProfileadviceisnowremoved.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 22/25

    7. Intheleftnavigator,clickeachoftheSQLTuningAdvisorImplementTypetocheckifalltherecommendationshavebeenimplemented.

    Notetheissuesreportedtoyou:

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 23/25

    Notetheissuesreportedtoyou:

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 24/25

    NotethattheRestructureSQLrecommendationtoremoveanunusedtableremains.

    8.Removethe"dept"tableintheSQLstatementandclickSQLAdvisor .

    9. Theoutputappears.Alloftheadvicerecommendationshavebeenremoved.

  • 2/23/2015 SQLTuningAdvisor

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm?print=preview&imgs=visible#s6 25/25

    SummaryInthistutorial,youhavelearnedhowto:

    CreateaDatabaseConnection.ProvidePrivilegestotheScottUser.RuntheSQLTuningAdvisoronaSQLstatement.ImplementSQLTuningAdvisorrecommendations.

    AboutOracle|OracleandSun| |Careers|ContactUs|SiteMaps|LegalNotices|TermsofUse|YourPrivacyRights

    http://www.oracle.com/corporate/index.htmlhttp://www.oracle.com/corporate/index.htmlhttp://www.oracle.com/sitemaps/sitemaps.htmlhttp://www.oracle.com/corporate/employment/index.htmlhttp://www.oracle.com/corporate/contact/index.htmlhttp://www.oracle.com/html/privacy.htmlhttp://www.oracle.com/rss/index.htmlhttp://www.oracle.com/html/terms.htmlhttp://www.oracle.com/corporate/index.htmlhttp://www.oracle.com/html/copyright.html


11.5 SQL Performance Tuning

Tuning SQL Queries - Oracle

SQL Tuning Tips

1 Copyright © 2012, Oracle and/or its affiliates. All rights reserved ... · Tuning SQL: SQL Tuning Advisor SQL Profiling Automatic Tuning Optimizer verifies and adjusts its own

SQL Tuning Revisited - SEGUS · Tuning SQL - Year 2004 Get an SQL, Package or list of Packages from development Propagate Production statistics down the ... SQL Tuning revisited

Write your questions marked with A , B , C , D , E...• Jagan Athreya, Director, Database Manageability Topics A. SQL Tuning Advisor B. SQL Monitoring C. SQL Plan Management 6 D

SQL Tuning Advisor in Oracle SQL Developer

Tuning SQL

Anatomy of a SQL Tuning Session - Wolfgang Breitling of a SQL Tuning Session.ppt.pdf · Anatomy of a SQL Tuning Session Wolfgang Breitling ... administration, and performance tuning

IBM Software GroupTuning Sql Using Oracle Enterprise Manager Console Enable Snapshot Data Collection Create a Tuning Set Create a Tuning Task Using Sql Tuning Advisor Look for Recommendations

Sql performance tuning

Tuning Oracle SQL

SQL Tuning

SQL Tuning Dot.net Perspective

Oracle Sql Tuning

SQL Tuning via Toad

Sql tuning guideline

Visual SQL Tuning (VST) Kyle Hailey kylehailey.com … SQL Tuning (VST) Kyle Hailey kylehailey.com [email protected] SQL Tuning Methodology ! 1.Find: Problem SQL

SQL Server Performance Tuning

Mike Ault-The New Tuning Universe of Oracle11g - … New Tuning Universe of Oracle11g Mike Ault Oracle Domain ... Oracle 11g Tuning Improvements • Improved SQL Advisor ... • Is

SQL Tuning and VST

SQL Optimization and Tuning

sql tuning -Dharam

Performance Tuning using the SQL Access Advisor

SQL Performance and Tuning SQL Performance and Tuning

Performancetuning in Oracle 11g - doag.org · • pl/sql-prozedur dbms_spm.evolve_sql_plan_baseline • Analyse problematischer SQL mit dem SQL Tuning Advisor und Akzeptieren des

The Self-Managing Database: Guided Application & SQL ... · SQL Tuning Advisor takes one or more SQL statements, and produces well-tuned plans along with tuning advice. The SQL statements

SQL - Oracleotndnld.oracle.co.jp/products/database/oracle10g/... · 2005-12-01 · SQL #* 3 Oracle Corporation $" Performance Tuning using the SQL Access Advisor ()% SQL 29B@ 2JM4=0

Oracle and .NET: Best Practices for Performance...–Use ODP.NET best practices •SQL tuning –Use SQL Tuning Advisor in Visual Studio •Database tuning under real world conditions

Tuning SQL without the Tuning Pack

Performance Tuning using the SQL Access Advisor · Performance Tuning using the SQL Access Advisor1 EXECUTIVE OVERVIEW Probably every DBA or person responsible for managing a computer

SQL Statement Tuning

SQL Tuning#1

Top-Down SQL Tuning - NOCOUG · SQL Tuning Assumptions •Oracle’s Cost-based Optimizer (CBO) does a perfectly good job on most SQL, requiring no manual tuning for most SQL

SQL Tuning Briefing