Oracle SQL tuning stappen

Oracle SQL tuning stappen In sommige gevallen, een onnodigeOracle SQL tuning – Tune individuele SQL-statements

Oracle Tips door Burleson Consulting

Voor een volledig begrip van SQL tuning stappen, probeer het Advanced Oracle SQL Tuning. Een 3-daagse of 5-daagse on-site Oracle cursus. Wij bieden ook een geweldige ter plaatse Oracle tuning training

Tuning individuele Oracle SQL-statements

De afkorting SQL staat voor Structured Query Language. SQL is een industriestandaard database query taal die in het midden van de jaren 1980 werd goedgekeurd. Het moet niet worden verward met commerciële producten zoals Microsoft SQL Server of open source zoals MySQL, die beide gebruikt het acroniem kader van de titel van hun producten.


Er zijn drie belangrijke beslissingen die de SQL-optimizer moet maken tijdens het optimaliseren van een SQL-statement:

Toegang methode: Oracle heeft een aantal keuzes van de "beste" manier om toegang te krijgen tot gegevens.

Join methode: Oracle moet decoderen tussen geneste lussen joins, hash te sluiten, enz.

Laten we eens een kijkje op het hoge niveau proces van SQL tuning:

Doe dit voordat u afzonderlijke SQL-instructie afstemmen te starten

Deze brede-borstel aanpak kan duizenden besparen uren van het vervelende SQL tuning want je kunt honderden vragen tegelijk. Vergeet niet dat je moet doen deze eerste, anders later wijzigingen aan de optimizer parameters of statistiek kan un-tunen van uw SQL.

Vergeet niet, je moet altijd beginnen met een system-level SQL tuning, anders later veranderingen kunnen ongedaan maken uw afgestemd uitvoeringsplannen:

  • Optimaliseer de server kernel – Je moet altijd afstemmen van uw schijf en netwerk I / O-subsysteem (RAID, DASD bandbreedte, netwerk) op de I / O tijd, netwerk packet grootte en de dispatching frequentie te optimaliseren.
  • Het aanpassen van uw optimizer statistieken – Je moet altijd verzamelen en opslaan optimizer statistieken zodat de optimizer voor meer informatie over de verdeling van uw gegevens te leren om intelligenter uitvoeringsplannen te nemen. Ook kunnen histogrammen SQL hyperlading in het geval van het bepalen van de optimale tafel zitten orde, en bij het maken van de toegang beslissingen over scheve WHERE predikaten.
  • Pas optimizer parameters – Optimizer OPTIMIZER_MODE, optimizer_index_caching. Optimizer_index_cost_adj .
  • Optimaliseer uw exemplaar – Uw keuze van db_block_size,db_cache_size. en OS parameters (db_file_multiblock_read_count, cpu_count, &c) kan SQL prestaties beïnvloeden.
  • Tune uw SQL Access werklast met fysieke indexen en gematerialiseerd uitzicht – Net zoals de SQLAccess adviseur adviseert ontbrekende indexen en ontbrekende gematerialiseerd uitzicht, moet u altijd uw SQL werklast met indexen, met name-functie op basis van indexen, een uitkomst voor SQL tuning te optimaliseren.

12c Opmerking: De Oracle 12c SQL Prestatie-analyse (SPA), is vooral bedoeld om de snelheid van de holistische SQL stemproces.

Zodra u een werkbelasting (een zogenaamde SQL Tuning Set, of STS) te creëren, zal Oracle herhaaldelijk de werklast te voeren, met behulp van geavanceerde voorspellende modellen (met behulp van een regressie testaanpak) in het oog springende veranderingen in SQL uitvoeringsplannen nauwkeurig te identificeren, op basis van uw veranderingen in het milieu . Met behulp van SPA, kunnen we de impact van veranderingen in het systeem te voorspellen op een werkbelasting, en kunnen we na het maken van eventuele wijzigingen, zoals het wijzigen van parameters, schema veranderingen, wijzigingen in de hardware, OS veranderingen, of Oracle upgrades veranderingen in de responstijden voor SQL voorspeld.

Oracle SQL tuning doelen

Oracle SQL tuning is een fenomenaal complex onderwerp. Hele boeken zijn geschreven over de nuances van Oracle SQL tuning; Er zijn echter enkele algemene richtlijnen die elke Oracle DBA volgt teneinde de prestatie van de systemen te verbeteren. Nogmaals, zie het boek "Oracle Tuning: The Definitive Reference "Voor volledige details.

De doelstellingen van SQL tuning focus op verbetering van de uitvoering van plan om de rijen met het kleinste aantal van de database te halen "aanrakingen" (LIO buffer krijgt en PIO fysieke gelezen).

  • Verwijder onnodige grote tafel vol-table scans -Onnodige full-table scans leiden tot een enorme hoeveelheid onnodige I / O en kan sleep-down een hele database. De tuning expert beoordeelt eerst de SQL op basis van het aantal rijen geretourneerd door de query. De meest voorkomende tuning remedie voor onnodige full-table scans is het toevoegen van indexen. Standard b-tree indexen kunnen worden toegevoegd aan tabellen en bitmap en functie-gebaseerde indexen kunnen tevens een einde aan full-table scans. In sommige gevallen, kan een onnodige volledige tabel scan gedwongen een index gebruiken door toevoeging van een index hint naar SQL statement.
  • Cache small-table full-table scans -In gevallen waarin een volledige tabel te scannen is de snelste toegang methode, moet de beheerder ervoor zorgen dat de gepresenteerde gegevens buffer is beschikbaar voor de rijen. In Oracle8 en daarbuiten, kan een kleine tafel worden gecached door het forceren van het in de KEEP zwembad.
  • Controleer optimaal index gebruik -Oracle heeft soms een keuze van de indexen, en de tuning professional moet elke index te onderzoeken en ervoor te zorgen dat Oracle is met behulp van de juiste index.
  • Concretiseren uw aggregaten en samenvattingen voor statische tabellen – Een kenmerken van de Oracle SQLAccess adviseur aanbevelingen voor nieuwe indexen en suggesties voor gematerialiseerde uitzicht. Gematerialiseerd uitzicht pre-join tafels en pre-vatten data, een echte zilveren kogel voor data mart rapportage databases waarin de gegevens alleen dagelijks wordt bijgewerkt. Nogmaals, zie het boek "Oracle Tuning: The Definitive Reference "Voor volledige informatie over de SQL tuning met gematerialiseerde uitzicht.

Dat zijn de doelstellingen van SQL tuning in een notendop. Ze zijn echter bedrieglijk eenvoudig, en hen effectief te voldoen, moeten we een hebben door middel van inzicht in het inwendige van Oracle SQL. Laten we beginnen met een overzicht van de Oracle SQL optimizers.

Oracle SQL optimizers

Een van de eerste dingen die de Oracle DBA kijkt is de standaard optimizer modus voor de database. De Oracle initialisatie parameters bieden veel kosten gebaseerde optimizer modes evenals de deprecated maar nuttige rule-based hint:

De kosten gebaseerde optimizer gebruik ‘statistieken’ die zijn verzameld uit de tabel met de opdracht ‘table analyseren’. Oracle maakt gebruik van deze statistieken over de tafels met het oog op de meest efficiënte manier van onderhoud van de SQL-query op intelligente wijze te bepalen. Het is belangrijk te erkennen dat in veel gevallen de kosten gebaseerde optimizer mogen de goede beslissing wat betreft de snelheid van de nieuwe zoekopdracht. De kosten gebaseerde optimizer wordt voortdurend verbeterd, maar er zijn nog steeds veel gevallen waarin de rule-based optimizer zal resulteren in een snellere Oracle queries.

Voorafgaand aan de Oracle 10g, is Oracle’s standaard optimizer mode genaamd ‘te kiezen.’ In het kiezen optimizer modus zal Oracle de rule-based optimizer uit te voeren als er geen statistieken aanwezig zijn voor de slacht; het zal de kosten gebaseerde optimizer uitvoeren als statistieken aanwezig zijn. Het gevaar van het gebruik van de optimizer kiest modus is dat er problemen kunnen optreden in gevallen waarin een lijst van Oracle in een complexe query statistieken en de andere tafels niet.

Vanaf Oracle 10g, de standaard optimizer modus is all_rows, ten gunste van full-table scans over de toegang index. De all_rows optimizer modus is ontworpen om IT-middelen te minimaliseren en het bevordert full-table scans. Index toegang (first_rows_n) voegt extra I / O-overhead, maar ze terugkeren rijen sneller, terug naar de query uit:

Volle tafel scans raken alle datablokken

Daarom zullen veel OLTP winkels first_rows kiezen. first_rows_100 of first_rows_10, vragen Oracle indexen gebruiken om block aanrakingen te verminderen:

Index scans terug rijen snel door het doen van extra I / O

Notitie: Staring in Oracle9i Release 2, de Oracle performance tuning gids zegt dat de first_rows optimizer modus is verouderd en te gebruiken first_rows_n plaats.

Wanneer slechts enkele tafels CBO statistieken bevatten, zal Oracle de kosten gebaseerde optimalisatie en de raming van de statistieken voor de andere tabellen in de query tijdens de uitvoering. Dit kan aanzienlijke vertraging in de uitvoering van de individuele vraag veroorzaken.

Natuurlijk, er zijn momenten waarop een full-tabel te scannen geschikt is voor een query is, zoals wanneer je aan het doen bent gezamenlijke activiteiten zoals een som of een gemiddelde, en de meerderheid van de rijen binnen de Oracle tabel moet worden gelezen om het te krijgen query resultaten. De taak van de SQL tuning deskundigen moeten elke full-tabel te scannen en te evalueren of het kan worden verbeterd door toevoeging van een index.

Voor het volledige verhaal, zie mijn boek "Oracle Tuning: The Definitive Reference " voor meer informatie over het kiezen van de juiste optimizer mode.

Een strategisch plan voor Oracle SQL tuning

Veel mensen vragen waar ze moeten beginnen wanneer tuning Oracle SQL. Tuning Oracle SQL is net als vissen. U moet eerst vis in de Oracle library cache om SQL-statements uit te pakken en te rangschikken de verklaringen van de omvang van hun activiteit.

Stap 1: Identificeer high-impact SQL

De SQL-statements zal worden gerangschikt op basis van het aantal executies en wordt afgestemd in deze volgorde. De executies kolom van de v $ sqlarea uitzicht en de stats $ sql_summary of de dba_hist_sql_summary tabel kan worden gebruikt om de meest gebruikte SQL lokaliseren. Merk op dat we SQL-statements door kan weergeven:

  • rijen verwerkt. Zoekopdrachten die een groot aantal rijen proces hoge I / O en kan ook invloed hebben op de TEMP tablespace.
  • buffer krijgt. High buffer krijgt kan een resource-intensieve vraag te geven.
  • Disk leest. Hoge schijf leest duiden op een query die wordt veroorzaakt door buitensporige I / O.
  • Memory KB. Het geheugen toekenning van een SQL-instructie is nuttig voor het identificeren van verklaringen die aan het doen zijn in het geheugen tabel joins.
  • CPU secs. Dit identificeert de SQL-statements die de meeste processor middelen te gebruiken.
  • soorten. Soorten kan een enorme vertraging, vooral als ze worden gedaan op een schijf in de TEMP tablespace.
  • executies. Hoe vaker uitgevoerde SQL-statements moet eerst worden afgestemd, omdat zij de grootste invloed op de algehele prestaties zal hebben.

Stap 2: Bepaal het uitvoeringsplan voor SQL

Aangezien elke SQL-instructie wordt geïdentificeerd, zal het ‘legde’ om de bestaande uitvoeringsplan te bepalen. Er zijn tal van hulpprogramma’s van derden op de markt die het uitvoeringsplan voor SQL verklaringen blijkt. De meest voorkomende manier van het bepalen van het uitvoeringsplan voor een SQL-instructie is om Oracle’s plannen uit te leggen programma te kunnen gebruiken. Door het gebruik van plannen uit te leggen, kan de Oracle DBA Oracle vragen om de verklaring te ontleden en de uitvoering klasse pad weer te geven zonder daadwerkelijk uitvoeren van de SQL-instructie.

sqlplus gt; @utlxplan
Tabel gemaakt.

sqlplus gt; het creëren van publieke synoniem PLAN_TABLE voor sys.plan_table;
Synoniem gemaakt.

De meeste relationele databases maken gebruik van een uit te leggen hulpprogramma dat de SQL-instructie de informatie toegangspad in een PLAN_TABLE, die vervolgens kunnen worden ondervraagd om de toegang methoden zien als input neemt, loopt de SQL-optimizer en uitgangen. Listing 1 loopt een complexe query tegen een database.

Deze syntaxis wordt doorgesluisd naar de SQL-optimizer, die zal het analyseren van de query en het plan informatie op te slaan in een rij in het plan tafel geïdentificeerd door RUN1. Houdt u er rekening mee dat de vraag niet zal uitvoeren; het zal alleen maar leiden tot de interne toegang tot informatie in het plan tafel. Het plan tabellen bevat de volgende velden:

  • operatie. Het type toegang wordt uitgevoerd. Meestal tafel toegang, tafel merge, sort of index operatie
  • opties. Modifiers om de operatie, het opgeven van een volledige tabel, een reeks tafel, of een join
  • OBJECT_NAME. De naam van de tabel wordt gebruikt door de querycomponent
  • proces-ID. De identificatie voor de query component
  • Ouder ID. De ouder van de query component. Merk op dat verschillende vraag componenten dezelfde ouder kan hebben.

Nu de PLAN_TABLE is gemaakt en gevuld, kunt u het ondervragen om de output te zien door het uitvoeren van de volgende query in listing 2.

plan.sql – toont de inhoud van het uit te leggen in de tabel SET PAGINA’S 9999; SELECT LPAD ( ”, 2 * (niveau-1)) || operatie operatie, opties, object_name, positie FROM PLAN_TABLE START MET id = 0 en statement_id = ‘RUN1’ CONNECT BY voorafgaand id = parent_id AND statement_id = ‘RUN1’;

Listing 3 toont de uitvoer van de in de tabel weergegeven in listing 1. Dit is het uitvoeringsplan voor de verklaring en toont de stappen en de volgorde waarin ze zullen worden uitgevoerd.

SQLgt; @list_explain_plan WERKING ———————————————– ————————————– OPTIES OBJECT_NAME POSITIE ——— ——————— —————————– ————————– uitgezochte verklaring SORTEREN GROUP BY 1 aaneenschakeling 1 geneste lussen 1 TAFEL volledige toegang tot het PLANSNET 1 TAFEL MET DE ROWID DETPLAN 2 INDEX RANGE SCAN DETPLAN_INDEX5 1 geneste lussen

Vanuit deze uitgang, kunnen we zien de gevreesde TAFEL volledige toegang tot het op het PLANSNET tafel. Om de reden voor deze full-table scan te diagnosticeren, keren we terug naar de SQL en zoeken naar eventuele plansnet kolommen in de WHERE-component. Daar zien we dat de plansnet kolom genaamd ‘mgc’ wordt gebruikt als een join kolom in de query, waaruit blijkt dat een index is nodig ter plansnet.mgc de volledige tabel scan verlichten.

Terwijl het plan tafel bruikbaar voor het bepalen van het toegangspad tot de gegevens, is het niet het hele verhaal vertellen. De configuratie van de data is ook een overweging. De SQL optimizer kent het aantal rijen in elke tabel (de cardinaliteit) en de aanwezigheid van indexen op gebieden, maar het is niet bekend datadistributie factoren als het aantal verwachte rijen terug van elke querycomponent.

Stap 3: Stem de SQL-instructie

Voor degenen SQL-statements die een sub-optimale uitvoeringsplan bezitten, zal de SQL worden afgestemd door een van de volgende manieren:

  • Het toevoegen van SQL ‘hints’ aan het uitvoeringsplan te wijzigen
  • Herschrijven van de SQL in PL / SQL. Voor bepaalde zoekopdrachten kan dit leiden tot meer dan 20x prestatieverbetering. De SQL worden vervangen door een aanroep van een PL / SQL-pakket dat een opgeslagen procedure voor de query uitvoeren bevatte.
  • Met behulp van tips om af te stemmen Oracle SQL

    Een van de meest voorkomende instrumenten voor het afstemmen van SQL-statements zijn hints. Een hint is een richtlijn die is toegevoegd aan de SQL-instructie om het toegangspad voor een SQL-query te wijzigen.

    Problemen oplossen Tip! Voor het testen, kunt u snel het effect van een andere optimizer parameter waarde op de query te testen zonder gebruik te maken van een commando ‘alter-sessie’, met behulp van de nieuwe opt_param SQL hint:

    select / * + opt_param ( ‘OPTIMIZER_MODE’, ‘first_rows_10’) * / col1, col2.

    select / * + opt_param ( ‘optimizer_index_cost_adj’, 20) * / col1, col2.

    Oracle publiceert vele tientallen SQL hints en tips worden steeds ingewikkelder door de verschillende releases van Oracle en op naar Oracle.

    Opmerking: Hints worden alleen gebruikt voor de-afluisteren en je moet je optimizer statistieken aan te passen om de CBO repliceren het doorschemeren SQL. Laten we eens kijken naar de meest voorkomende tips om tuning te verbeteren:

    • Mode hints: first_rows_10, first_rows_100
  • Dynamische sampling: dynamic_sampling
  • Oracle SQL ongedocumenteerde tuning hints – Guru’s alleen

    Self-order de tafel sluit zich aan – Als u vindt dat Oracle samen sluit zich aan bij de tafels in een sub-optimale bestelling kunt u de bestelde hint gebruiken om de tabellen te worden samengevoegd in de volgorde te dwingen dat ze in de FROM-component. Zien

  • Probeer een first_rows_n hint. Oracle heeft twee kosten gebaseerde optimizer modes, first_rows_n en all_rows. De first_rows mode zal uitvoeren om te beginnen terug te keren rijen zo spoedig mogelijk, terwijl de all_rows modus is ontworpen om de bronnen op de gehele vraag te optimaliseren alvorens terug te keren rijen.

    Een case study in SQL tuning

    Een van de historische problemen met SQL omvat het formuleren van SQL queries. Eenvoudige vragen kan geschreven worden op verschillende manieren, elke variant van de query hetzelfde resultaat, maar met sterk verschillende toegangsmethoden en vraag snelheden.

    Bijvoorbeeld kan een eenvoudige zoekopdracht als “Wat studenten gekregen Een laatste halfjaar ” geschreven op drie manieren, zie hieronder, Voor ieder identieke resultaten.

    Een standaard join:
    SELECT * FROM STUDENT, REGISTRATIE WHERE STUDENT.student_id = REGISTRATION.student_id AND REGISTRATION.grade = ‘A’;
    Een geneste vraag:
    SELECT * FROM STUDENT WHERE student_id = (SELECT student_id VAN REGISTRATIE WAAR klasse = ‘A’);
    Een gecorreleerde subquery:
    SELECT * FROM STUDENT WAAR 0 lt; (SELECT count (*) VAN REGISTRATIE WAAR klasse = ‘A’ en student_id = STUDENT.student_id);

    Laten we de wind met een herziening van de basiscomponenten van een SQL-query en zie hoe een query voor de uitvoering van externe optimaliseren.

    Tips voor het schrijven van een efficiëntere SQL

    Space me niet toe om elk detail van Oracle tuning te bespreken, maar ik kan een aantal algemene regels te delen voor het schrijven van efficiënte SQL in Oracle, ongeacht de optimizer die wordt gekozen. Deze regels kunnen simplistisch lijken, maar na hen op een zorgvuldige wijze zal in het algemeen te verlichten meer dan de helft van de SQL tuning problemen die worden ervaren:

    • Herschrijf complex subqueries met tijdelijke tabellen – Oracle creëerde de globale tijdelijke tabel (GTT) en de SQL MET operator om te helpen verdelen-and-veroveren complexe SQL sub-queries (vooral die met met WHERE subqueries, SELECT-component scalaire subqueries en FROM clausule in-line views). Tuning SQL met tijdelijke tabellen (en materialisaties in de MET-clausule) kan leiden tot verbazingwekkende prestatieverbeteringen.
    • Gebruik in plaats van minus BESTAAT subqueries – Sommigen zeggen dat met het min operator in plaats van GEEN niet bestaat en resulteert in een snellere uitvoeringsplan.
    • Gebruik SQL-analytische functies – De Oracle analytische functies kunt meerdere combinaties (bijv. Rollup door kubus) doen met een enkele passage door de tafels, waardoor ze zeer snel voor het melden van SQL.
    • Re-schrijven NOT EXISTS en NOT EXISTS subqueries als outer joins – In veel gevallen van niet queries (maar alleen wanneer een kolom is gedefinieerd als NULL), kunt u deze opnieuw schrijven van de niet-gecorreleerde subquery in outer joins met IS NULL testen. Merk op dat dit een niet-gecorreleerde subzoekvraag, maar het kan worden herschreven als outer join.
    • selecteer book_key uit het boek
      waar
      book_key NIET IN (selecteer book_key uit de verkoop);

      Hieronder geven we de buitenste combineren samen met een NULL test in de WHERE-component zonder gebruik van een sub-vraag, waardoor een snellere uitvoeringsplan.

      selecteer b.book_key uit het boek b, sales s
      waar
      b.book_key = s.book_key (+)
      en
      s.book_key IS NULL;

      • Index uw NULL waarden – Als u SQL die vaak test voor NULL, overwegen om een ​​index op NULL-waarden. Om rond het optimaliseren van SQL-query’s die NULL kolom waarden te kiezen (dat wil zeggen waarbij emp_name IS NULL ), Kunnen we een functie-gebaseerde index met de nulwaarde ingebouwde SQL functie index alleen NULL kolommen.
      • Laat kolomnamen alleen – Doe nooit een berekening op een geïndexeerde kolom, tenzij u een passende functie-gebaseerde index (alias FBI). Beter nog, re-design van het schema, zodat gemeenschappelijke where predikaten niet transformatie met een BIF nodig:

      waarbij het salaris * 5 gt; : MyValue
      waar de substr (SSN, 7,4) = "1234"
      waarbij TO_CHAR (myDate, mon) = "januari"

      • Vermijd het gebruik van NIET IN of HEBBEN. In plaats daarvan, een NIET BESTAAT subquery kan sneller draaien (indien van toepassing).
      • Vermijd het predikaat LIKE = Vervang altijd een "graag willen" met een gelijkheid, indien nodig.
      • Meng nooit datatypes – Als een WHERE column predikaat numeriek is, niet om aanhalingstekens te gebruiken. Voor char index kolommen, altijd gebruik maken van offertes. Er zijn gemengde data predikaten:

      waarbij cust_nbr = "123"
      waar de substr (SSN, 7,4) = 1234

      • Gebruik decoderen en case – Het uitvoeren van complexe aggregaties met de "decoderen" of "geval" functies kan het aantal keren dat een tabel moet worden geselecteerd minimaliseren.
      • Wees niet bang voor full-table scans – Niet alle OLTP queries optimaal zijn als ze gebruik maakt van indexen. Als uw vraag een groot percentage van de tabel rijen zal terugkeren, kan een volledige tabel te scannen sneller dan een index scan. Dit is afhankelijk van vele factoren, waaronder uw configuratie (waarden voor db_file_multiblock_read_count. db_block_size), -query parallellisme en het aantal tabel / index blokken in de buffer cache.
      • Gebruik deze aliassen – Gebruik altijd tabelaliassen bij verwijzingen naar kolommen.

      Zie ook deze verwante SQL tuning notes:

      Burleson is de Amerikaanse Team

      Notitie: Deze Oracle documentatie werd opgericht als een ondersteuning en Oracle training referentie voor gebruik door onze DBA performance tuning consulting professionals. Voel je vrij om vragen over onze Oracle forum vragen.

      Verifiërenervaring!Iedereen die overweegt het gebruik van de diensten van een Oracle support deskundige moet onafhankelijk onderzoeken hun geloofsbrieven en ervaring, en niet afhankelijk zijn van advertenties en zelfbenoemde deskundigheid. Alle legitieme Oracle deskundigen publiceren hun Oracle-kwalificaties.

      De Oracle Database Ondersteuning

      auteursrechten © 1996 – 2016

      Alle rechten voorbehouden aan Burleson

      Orakel ® is een geregistreerd handelsmerk van Oracle Corporation.

      Bron: www.dba-oracle.com

      Geef een reactie

      Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

      1 × 1 =