Pag-optimize ng mga query sa ORACLE gamit ang isang execution plan. Pag-unawa sa Mga Resulta ng Ipatupad ang Explain Plan sa Oracle SQL Developer

Hayaan akong linawin kaagad na ilalarawan ko gamit ang halimbawa ng paggamit ng libreng utility na OraDeveloper Studio. bakit naman Dahil hindi posible na gawin ito sa mga ordinaryong query, at walang oras o pagnanais na malaman ito, dahil may mas madaling paraan. 😉

Kaya, para saan ito? Ilalarawan ko ito sa iyo kongkretong halimbawa, dahil dito napilitan akong magsagawa ng pag-optimize.

Ang gawain ay mag-load ng libu-libong mga hilera ng data sa database. Para sa bawat hilera, kailangan mo munang maghanap ng karagdagang data sa database na may isang medyo masalimuot na query (4 na talahanayan sa pamamagitan ng pagsali).
Ang problema ay ang pag-load ng 15 libong mga hilera ay tumatagal ng 8-9 na oras. Dahil, ayon sa mga tuntunin ng gawain, kinakailangan na i-load ito nang madalas, at hindi isang beses bawat limang taon... Sa pangkalahatan, kailangan nating dalhin ang oras sa isang katanggap-tanggap na antas.

Ano bang nagawa ko?
1. Nalaman ko na ang pili ay bumabagal (ang data ay ipinasok at na-update sa mga talahanayan kung saan mayroong isang bungkos ng mga hilera at ang ilan sa mga talahanayan ay walang mga index o mga susi - kaya ang mga pagdududa tungkol sa kasalanan ng pinili) .
2. Sinuri ang pagkakaroon ng mga index sa mga field na ginamit ng kahilingan. Nagdagdag ng mga nawawala.
3. Humingi ng tulong sa mga nakakaalam. 🙂

Pinayuhan kami ng mga nakakaalam na suriin ang plano sa pagpapatupad ng query at ipinaliwanag kung paano ito gagawin sa OraDev.
Gumawa ng bagong query window (Ctrl+N). Kinokopya namin ang aming kahilingan dito. Pindutin ang Alt+G. Pumili ng isang umiiral na o lumikha ng isa bagong mesa plano.
Pagkatapos ng execution, lalabas ang execution plan tree. Ito ay hindi napakadali upang malaman ito sa iyong sarili at walang kalahating litro. 😉

Ano ang interes sa amin tungkol sa punong ito? Interesado kami sa mga node (mga hakbang) kung saan ipinahiwatig ang malaking Gastos ng hakbang. Maaari mong makita ang presyo ng hakbang sa mga katangian ng hakbang (Mayroon akong palaging bukas na window ng mga katangian at samakatuwid kailangan ko lamang pumili ang tamang hakbang; maaaring kailanganin mo ring pumili ng mga katangian sa pamamagitan ng pag-right click sa hakbang). Hinahanap namin ang mabagal na hakbang (hindi namin isinasaalang-alang ang pinakamataas na node, ang ugat ng plan tree - ang kabuuang presyo ng kahilingan ay ipapakita doon, at alam na namin na ang problema ay nasa kahilingang ito). Nahanap na? Ngayon ay tinitingnan natin kung aling talahanayan, aling mga patlang at kung gaano karaming mga hilera ang gumagana sa hakbang - ito ay nasa mga katangian at pangalan ng hakbang. Tinitingnan namin at iniisip, bakit ang bagal dito?
Halimbawa, ang isa sa mga hakbang ay gumana sa 4000 na tala sa halip na isa o tatlong talaan (hindi libu-libo). Hindi ito dapat nangyari sa prinsipyo - Nililimitahan ko ang sample nang tumpak upang pumili mula sa kinakailangang hanay, at hindi mula sa isang tambak ng hindi kinakailangang basura. Sa maingat na pagtingin sa kondisyon ng pagsali, napansin ko na napalampas ko ang isa sa mga field. Nagdagdag ako ng isang patlang sa kahilingan at lahat ay nahulog sa lugar. Bumaba ang presyo ng kahilingan (buo) mula 531 hanggang 6. :)

Salamat sa mga kasama pugad at detect para sa kanilang tulong.

P.S. Paumanhin sa hindi pagsama ng mga screenshot. Ito ay magiging mas malinaw sa kanila, ngunit... Dahil sa pagiging kompidensiyal ng ilang impormasyon, 80% ay kailangang i-gloss at pagkatapos ay muli itong hindi malinaw.
P.P.S. Ang kabuuang oras ng paglo-load ay makabuluhang nabawasan. Tumagal ng 12 minuto upang mai-load ang 17.5 libong mga hilera ng data sa database. Kumpara sa 8-9 na oras... Well, naiintindihan mo na ang lahat sa iyong sarili. 😉

5 sagot

Ipaliwanag ang PLAN output ay optimizer debug output Mga tanong sa Oracle. Ang COST ay ang resulta ng isang cost optimizer (CBO) na ang layunin ay piliin kung alin sa maraming posibleng plano ang dapat gamitin upang magpatakbo ng query. Kinakalkula ng CBO ang kaugnay na gastos para sa bawat plano, pagkatapos ay pipiliin ang plano na may pinakamababang halaga.

(Tandaan: Sa ilang mga kaso ang CBO ay walang sapat na oras upang suriin ang lahat ng posibleng mga plano, sa mga kasong ito ay pinipili lamang nito ang plano na may pinakamababang halaga na natagpuan sa ngayon)

Sa pangkalahatan, ang isa sa mga pinakamalaking kontribusyon sa isang mabagal na query ay ang bilang ng mga row na binasa upang maserbisyuhan ang query (mas tiyak, mga block), kaya ang gastos ay bahagyang ibabatay sa bilang ng mga row na kakailanganing basahin ng mga pagtatantya ng optimizer. .

Halimbawa, sabihin nating mayroon kang sumusunod na query:

PUMILI ng emp_id MULA sa mga empleyado KUNG SAAN buwan_ng_serbisyo = 6;

(Ang column na months_of_service ay may NOT NULL constraint dito at isang regular na index dito.)

Mayroong dalawang pangunahing plano na maaaring piliin ng optimizer mula dito:

Plano 1: Basahin ang lahat ng mga hilera mula sa talahanayan ng "mga empleyado", para sa bawat pagsusuri kung ang panaguri ay totoo (buwan_ng_serbisyo=6).
Plano 2: Basahin ang index kung saan months_of_service=6 (nagreresulta ito sa isang hanay ng mga ROWID), pagkatapos ay i-access ang talahanayan batay sa mga ibinalik na ROWID.

Ipagpalagay natin na ang talahanayan na "mga empleyado" ay naglalaman ng 1,000,000 (1 milyon) na mga hilera. Isipin pa natin na ang mga halaga para sa mga buwan_ng_serbisyo ay mula 1 hanggang 12 at sa ilang kadahilanan ay pantay-pantay ang pagkakabahagi.

Presyo Plano 1, na kinabibilangan ng BUONG PAG-SCAN, ay gagastos upang basahin ang lahat ng mga hilera sa talahanayan ng mga empleyado, na humigit-kumulang 1,000,000; ngunit dahil madalas na nababasa ng Oracle ang mga block gamit ang multi-block reads, ang aktwal na gastos ay magiging mas mababa (depende sa kung paano naka-configure ang iyong database) - halimbawa, sabihin natin na ang bilang ng mga sample na may maraming block ay 10 - ang tinantyang halaga ng isang buong ang pag-scan ay magiging 1,000,000/10 ; Kabuuang gastos = 100,000.

Presyo Plano 2, na kinabibilangan ng INDEX RANGE SCAN at isang table lookup ng ROWID, ay gagastos sa index scan pati na rin ang halaga ng pag-access sa talahanayan gamit ang ROWID. Hindi ko tatalakayin ang halaga ng isang range index scan, ngunit isipin natin na ang halaga ng isang range index scan ay 1 bawat hilera; inaasahan naming makahanap ng tugma 1 sa 12 beses, kaya ang halaga ng pag-scan sa index ay 1,000,000/12 = 83,333; kasama ang gastos sa pag-access sa talahanayan (ipagpalagay na 1 block read sa bawat access, hindi namin magagamit ang multi-block reads dito) = 83.333; Kabuuang gastos = 166,666.

Gaya ng nakikita mo, ang halaga ng plan 1 (full scan) ay mas mababa kaysa sa halaga ng plan 2 (index check + rowid access) - nangangahulugan ito na pipiliin ng CBO ang BUONG pag-scan.

Kung ang mga pagpapalagay na ginawa dito ng optimizer ay tama, kung gayon sa katunayan ang Plano 1 ay magiging mas kanais-nais at mas mahusay kaysa sa Plano 2 - na pinabulaanan ang mito na ang BUONG pag-scan ay "palaging masama".

Magiging ibang-iba ang mga resulta kung ang target ng optimizer ay FIRST_ROWS(n) sa halip na ALL_ROWS - kung saan mas gusto ng optimizer ang plan 2 dahil madalas nitong ibabalik ang unang ilang row nang mas mabilis, sa halaga ng pagiging hindi gaanong mahusay para sa buong query .

Bumubuo ang CBO ng decision tree sa pamamagitan ng pagtantya sa mga gastos ng bawat posibleng execution path na magagamit para sa bawat kahilingan. Ang mga gastos ay itinakda ng CPU_cost o I/O_cost parameter na itinakda sa instance. At tinatantya ng CBO ang gastos sa abot ng makakaya nito sa umiiral na mga istatistika ng mga talahanayan at index na gagamitin sa query. Hindi mo dapat i-customize ang iyong kahilingan batay lamang sa gastos. Nagbibigay-daan sa iyo ang gastos na maunawaan kung BAKIT ginagawa ng optimizer ang ginagawa nito. Kung wala ang gastos, mauunawaan mo kung bakit pinili ng optimizer ang plano na ginawa nito. Higit pa mababang gastos ay hindi nangangahulugan ng isang mas mabilis na kahilingan. May mga pagkakataon na ito ay totoo at may mga pagkakataon na ito ay mali. Ang gastos ay batay sa iyong talahanayan ng mga istatistika at kung mali ang mga ito ay magiging mali ang gastos.

Kapag nagse-set up ng query, dapat mong tingnan ang cardinality at bilang ng mga row ng bawat hakbang. May sense ba sila? Itinuturing bang tama ang optimizer? Ibinalik ba nang tama ang mga string? Kung ang impormasyon ay hindi naroroon nang tama, malamang na ang optimizer ay walang tamang impormasyon na kailangan upang makagawa ng tamang desisyon. Ito ay maaaring dahil sa luma o nawawalang mga istatistika sa talahanayan at index, pati na rin sa cpu-stats. Pinakamainam na i-update ang mga istatistika kapag kino-configure ang iyong query upang masulit ang optimizer. Ang pag-alam sa iyong circuit ay nakakatulong din ng malaki sa pag-setup. Alam kung kailan talaga napili ang optimizer masamang desisyon at itinuro ito ang tamang paraan na may kaunting pahiwatig, makakatipid ka ng oras.

Ang iyong pagbanggit ng "FULL" ay nagpapahiwatig sa akin na ang query ay gumagawa ng isang full screen scan upang mahanap ang iyong data. Sa ilang mga kaso ito ay maayos, kung hindi, ito ay isang tagapagpahiwatig ng isang masamang pag-index/query entry.

Bilang pangkalahatang tuntunin ng thumb, gusto mong gumamit ng mga key ang iyong query upang mahanap ng Oracle ang data na kailangan mo sa pamamagitan ng pag-access sa pinakamaliit na bilang ng mga row. Sa huli, maa-access mo balang araw ang arkitektura ng iyong mga talahanayan. Kung mananatiling masyadong mataas ang mga gastos, maaaring kailanganin mong pag-isipan kung paano ibagay ang disenyo ng iyong circuit para sa mas mahusay na pagganap.

Kamakailan lamang Mga bersyon ng Oracle Ang COST ay kumakatawan sa oras na naghihintay ang optimizer para sa isang query, na ipinahayag sa mga yunit ng oras na kinakailangan upang mabasa ang isang bloke.

Kaya, kung ang pagbabasa ng isang block ay tumatagal ng 2ms at ang gastos ay ipinahayag bilang "250", ang kahilingan ay maaaring tumagal ng 500ms upang makumpleto.

Kinakalkula ng optimizer ang isang gastos batay sa tinantyang bilang ng single-block at multi-block na pagbabasa, pati na rin ang pagkonsumo ng CPU ng plano. ang huli ay maaaring maging lubhang kapaki-pakinabang para sa pagliit ng mga gastos sa pamamagitan ng pagsasagawa ng ilang partikular na operasyon bago ang iba upang subukang maiwasan ang mataas na gastos sa CPU.

Itinaas nito ang tanong kung paano alam ng optimizer kung gaano katagal ang mga operasyon upang makumpleto. pinakabagong bersyon Pinapayagan ka ng Oracle na lumikha ng mga koleksyon ng "mga istatistika ng system", na tiyak na hindi dapat malito sa mga istatistika ng talahanayan o index. Ang mga istatistika ng system ay mga sukat ng pagganap hardware, higit sa lahat ay mahalaga:

Gaano katagal bago basahin ang isang bloke.
Gaano katagal ang multitasking reading?
Gaano kalaki ang multiblock read (kadalasang iba sa maximum na posible dahil sa laki ng talahanayan na mas mababa sa maximum na halaga at iba pang mga dahilan).
Pagganap ng CPU

Ang mga numerong ito ay maaaring mag-iba nang malaki depende sa operating environment ng system, at ang iba't ibang mga istatistika ay maaaring maimbak para sa "daytime OLTP" na mga operasyon at "nightly periodic reporting" operations, at para sa "month-end reporting" kung gusto mo.

Dahil sa mga set na ito ng mga istatistika, ang planong ito Maaaring matantya ang pagpapatupad ng query para sa gastos sa iba't ibang mga operating environment, na maaaring hikayatin ang paggamit ng mga full table scan sa ilang mga kaso o index scan sa iba.

Ang gastos ay hindi perpekto, ngunit ang optimizer ay nagiging mas mahusay sa self-monitoring sa bawat release at maaaring tumugon sa aktwal na gastos kumpara sa tinantyang gastos upang tanggapin pinakamahusay na solusyon sa hinaharap. nagpapahirap din ito sa pagtataya.

Pakitandaan na hindi kailangan ang gastos oras sa dingding, dahil ang mga pagpapatakbo ng parallel na query ay kumokonsumo ng kabuuang tagal ng oras sa maraming mga thread.

Sa mas lumang mga bersyon ng Oracle, ang gastos ng mga pagpapatakbo ng CPU ay hindi pinansin, at ang kamag-anak na halaga ng mga single at multi-block na pagbabasa ay epektibong naitama ayon sa mga parameter ng init.

Maaari ka ring mag-query sa v $sql at v $session upang makakuha ng mga istatistika tungkol sa mga SQL statement, at magkakaroon ito ng mga detalyadong sukatan para sa lahat ng uri ng mapagkukunan, timing at pagpapatupad.

Para itong pako sa talampakan ng paborito mong sapatos. Maaari kang maglakad, ngunit mas madalas mong mahuli ang iyong sarili na gustong manatili sa lugar o italaga ang gawain sa iba. Ang mga maliliit na abala ay hindi lamang nagpapabagal sa ating trabaho, ngunit nakakabawas din ng motibasyon, nakakasagabal sa proseso, at nakakabawas sa kalidad ng resulta. At kung nakatagpo ka ng isang kaibigan na nagturo sa iyo na kumuha ng martilyo at martilyo sa pako na iyon, hindi ka lamang magpapasalamat sa kanya para sa kanyang tulong, ngunit tutulungan mo rin ang iba sa iyong sarili, na iligtas sila mula sa isang menor de edad ngunit napaka-nakakainis na balakid. Ito ang dahilan kung bakit kailangan mong makipag-usap, magbahagi hindi lamang ng malalim at matalik na kaalaman sa mga forum at sa mga site tulad ng Habr, kundi pati na rin ang iyong mga simpleng trick at "maliit na trick"

Tulad ng anumang teksto, ang mga query at program sa SQL ay maaaring malikha sa alinman text editor. Ngunit kung ikaw ay isang propesyonal, marami kang trabaho at madalas gamit ang SQL, kung gayon ang pagkakaroon ng syntax highlighting at awtomatikong pag-reformat ng code ay hindi na sapat para sa iyo, lalo na kung kailangan mong lumipat sa pagitan ng iba't ibang bersyon ng parehong DBMS o iba't ibang mga platform DBMS.

Kamakailan ay nakipag-usap ako sa isa sa mga nangungunang propesyonal Oracle DBMS. Sinabi niya ang maraming kawili-wiling bagay tungkol sa pagtatrabaho sa mga plano sa pagpapatupad ng query iba't ibang bersyon ang DBMS na ito at hindi nag-atubiling sabihin sa lahat ang tungkol sa mga tool at teknik na ginagamit niya at magbigay ng ilang kapaki-pakinabang na maliliit na tip. Isinalin ko ang isa sa mga artikulo sa kanyang blog at nais kong ipaalam ito kay Khabravchan. Sa kabila ng katotohanan na ang inilarawang pamamaraan ay ginamit upang gumana sa Oracle, matagumpay kong ginagamit ang parehong diskarte para sa MS SQL at Sybase.

Magpatakbo ng query sa pagpapatupad at lalabas ang tab na Plano ng Query, na puno ng plano sa pagpapatupad.

Ilagay ang cursor ng mouse sa alinman sa mga node sa diagram at karagdagang kapaki-pakinabang na impormasyon, na nauugnay sa hakbang na ito ng pagpapatupad mula sa plano ng query!
Bilang default, ipinapakita ng Rapid SQL ang execution plan nang grapiko. Lumabas ako sa lumang mundo ng pag-optimize…. Mas gusto ko ang bersyon ng teksto, kaya nag-click ako kanang pindutan mouse sa window na may plano at piliin ang "Tingnan bilang Teksto".
Mas gusto kong makita ang text ng kahilingan at ang plano nang sabay.

Madaling gawin. Nakikita mo ba ang mga tab ng ISQL window sa ibaba ng pangunahing window? Una, kailangan nating i-configure ang Rapid SQL upang maipakita nito ang plano sa isang hiwalay na window.

I-click ang Options button (kaliwang pulang bilog) at pagkatapos ay itakda ang 'Unattached' na opsyon para sa Resulta window. Gagawa ito ng dalawang magkahiwalay na tab sa ibaba ng Rapid SQL sa sandaling tumakbo ang query. I-drag lang ang window na ito nang kaunti sa tab at may lalabas na parihaba kung saan mo maaaring ilipat ang window na ito.
O maaari mong gamitin ang item na Tile windows mula sa pangunahing menu ng programa

At isa pang bagay: lahat ng ito ay gumagana din sa DBArtisan - isang solusyon para sa mga administrator ng database.

Tulad ng anumang text, ang mga query at program sa SQL ay maaaring malikha sa anumang text editor. Ngunit kung ikaw ay isang propesyonal, marami kang nagtatrabaho at madalas gamit ang SQL, kung gayon ang pagkakaroon ng syntax highlight at awtomatikong pag-reformat ng code ay hindi na sapat para sa iyo, lalo na kung kailangan mong lumipat sa pagitan ng iba't ibang bersyon ng parehong DBMS o iba't ibang mga platform ng DBMS.

Kamakailan ay nakipag-usap ako sa isa sa mga nangungunang propesyonal sa Oracle DBMS. Sinabi niya ang maraming kawili-wiling bagay tungkol sa pagtatrabaho sa mga plano sa pagpapatupad ng query sa iba't ibang bersyon ng DBMS na ito at hindi nag-atubiling sabihin sa lahat ang tungkol sa mga tool at diskarte na ginagamit niya at magbigay ng ilang kapaki-pakinabang na maliliit na tip. Isinalin ko ang isa sa mga artikulo sa kanyang blog at nais kong ipaalam ito kay Khabravchan. Sa kabila ng katotohanan na ang inilarawang pamamaraan ay ginamit upang gumana sa Oracle, matagumpay kong ginagamit ang parehong diskarte para sa MS SQL at Sybase.

Magpatakbo ng query sa pagpapatupad at lalabas ang tab na Plano ng Query, na puno ng plano sa pagpapatupad.

I-hover ang iyong mouse sa alinman sa mga node sa diagram at lalabas ang karagdagang kapaki-pakinabang na impormasyong nauugnay sa hakbang na iyon sa pagpapatupad mula sa query plan!
Bilang default, ipinapakita ng Rapid SQL ang execution plan nang grapiko. Lumabas ako sa lumang mundo ng pag-optimize…. Mas gusto ko ang bersyon ng teksto, kaya nag-right-click ako sa window ng plano at piliin ang "Tingnan bilang Teksto".
Mas gusto kong makita ang text ng kahilingan at ang plano nang sabay.

Madaling gawin. Nakikita mo ba ang mga tab ng ISQL window sa ibaba ng pangunahing window? Una, kailangan nating i-configure ang Rapid SQL upang maipakita nito ang plano sa isang hiwalay na window.

I-click ang Options button (kaliwang pulang bilog) at pagkatapos ay itakda ang 'Unattached' na opsyon para sa Resulta window. Gagawa ito ng dalawang magkahiwalay na tab sa ibaba ng Rapid SQL sa sandaling tumakbo ang query. I-drag lang ang window na ito nang kaunti sa tab at may lalabas na parihaba kung saan mo maaaring ilipat ang window na ito.
O maaari mong gamitin ang item na Tile windows mula sa pangunahing menu ng programa

At isa pang bagay: lahat ng ito ay gumagana din sa DBArtisan - isang solusyon para sa mga administrator ng database.

Oracle Database 11g Performance Tuning.

Paano basahin ang mga query plan

Ano ang isang plano sa pagpapatupad?

Ang execution plan ay ang output ng optimizer na binibigyang kahulugan sa wika ng execution engine. Ito ay nagtuturo sa execution engine tungkol sa mga operasyon na dapat gawin upang makuha ang data na hiniling ng expression nang mabilis at mahusay hangga't maaari.

Kinukuha ng expression na EXPLAIN PLAN ang execution plan na pinili ng optimizer para sa pag-execute ng mga expression gaya ng SELECT, UPDATE, DELETE, at INSERT. Ang mga hakbang sa plano ng pagpapatupad ay hindi isinasagawa sa pagkakasunud-sunod na tinukoy sa plano. Mayroong relasyon ng magulang-anak sa pagitan ng mga hakbang. ang source string tree ay ang batayan ng execution plan. Naglalaman ito ng sumusunod na impormasyon:

Pag-uuri ng mga talahanayan na tinutukoy ng pahayag
Paraan ng pag-access para sa bawat talahanayan na tinukoy sa pahayag
Paraan ng pagsali para sa mga talahanayan, na ginagamit ng mga operator ng pagsali sa isang expression
Mga pagpapatakbo ng data gaya ng filter, pag-uuri, o pagsasama-sama

Bilang karagdagan sa source string tree (o data flow tree in parallel operations) ang talahanayan ng plano ay naglalaman ng sumusunod na data:

Data ng pag-optimize tulad ng gastos at cardinality ng bawat operasyon
Data ng partisyon, gaya ng set ng mga partisyon na na-access
Concurrency data tulad ng paraan ng pamamahagi ng operasyon ng pagsali

Ang mga resulta ng EXPLAIN PLAN ay makakatulong sa iyo na matukoy kung ang optimizer ay pumipili ng isang partikular na plano sa pagpapatupad, gaya ng paggamit ng mga nested loop.

Saan ako makakahanap ng mga plano sa pagpapatupad?

Mayroong maraming mga paraan upang makakuha ng mga plano sa pagpapatupad para sa mga expression sa database, ang pinakasikat ay nakalista sa ibaba:

Binibigyang-daan ka ng command na EXPLAIN PLAN na tingnan ang execution plan na magagamit ng optimizer para magsagawa ng expression. Ang utos na ito ay lubhang kapaki-pakinabang dahil ito ay bumubuo ng isang plano sa pagpapatupad at isinusulat ito sa isang talahanayan na tinatawag na PLAN_TABLE nang hindi iniimbak ang SQL statement.
Ang V$SQL_PLAN ay nagbibigay ng kakayahang tingnan ang mga plano sa pagpapatupad para sa mga cursor na kamakailang naisakatuparan. Ang impormasyong nakaimbak sa V$SQL_LAN ay halos kapareho sa impormasyong ginawa ng command na EXPLAIN PLAN. Gayunpaman, ang Explain Plan ay nagpapakita ng isang potensyal na plano sa pagpapatupad, at ang V$SQL_PLAN ay nag-iimbak ng mga plano ng naisagawa na mga query.
Ang V$SQL_PLAN_MONITOR ay naglalaman ng mga istatistika ng pagsubaybay sa antas ng plano para sa bawat SQL statement na makikita sa V$SQL_MONITOR. Ang bawat linyang nakapaloob sa V$SQL_PLAN_MONITOR ay tumutugma sa isang partikular na operasyon ng plano sa pagpapatupad.
Ang AWR framework at Statspack store execution plans para sa pinakamadalas na tinatawag na SQL. Ang mga plano ay inilalagay sa dBA_HIST_SQL_PLAN o STATS$SQL_PLAN view.
Ang mga execution plan at row source ay isinulat din upang masubaybayan ang mga file na nabuo ng DBMS_MONITOR.
Ang SQL Management Base ay bahagi ng diksyunaryo ng data na nakaimbak sa SYSAUX tablespace. Ang impormasyon ng log tungkol sa mga operasyon, kasaysayan ng mga plano sa pagpapatupad at mga linya ng sanggunian ay naka-imbak dito pati na rin ang mga profile para sa mga pahayag ng SQL.
Ang diagnostic na kaganapan 10053, na ginagamit upang itala ang mga kalkulasyon ng cost optimizer, ay maaari ding bumuo ng mga plano sa pagpapatupad ng query.
Simula sa bersyon 10.2, kapag nag-dump ka ng isang estado ng proseso, ang execution plan ay kasama rin sa nabuong trace file.

Pagtingin sa mga plano sa pagpapatupad

Kung pinapatakbo mo ang command na EXPLAIN PLAN sa SQL*Plus, maaari kang pumili ng data mula sa talahanayan ng PLAN_TABLE at tingnan ang nabuong mga plano sa pagpapatupad. Karamihan sa simpleng paraan tingnan ang execution plan ay ang paggamit ng DBMS_XPLAIN package. Ang DBMS_XPLAIN package ay naglalaman ng limang magagamit na function:

DISPLAY: Ginagamit para sa na-format na output ng execution plan.
DISPLAY_AWR: Ginagamit para sa na-format na output ng plano Pagpapatupad ng SQL mga expression na nakaimbak sa AWR repository.
DISPLAY_CURSOR: Ginagamit upang i-format ang execution plan na output mula sa anumang na-load na cursor
DISPLAY_SQL_PLAN_BASELINE: Ginagamit para sa na-format na output ng isa o higit pang mga plano sa pagpapatupad para sa mga SQL statement na natukoy ng mga header.
DISPLAY_SQLSET: Ginagamit para sa na-format na output ng execution plan na naka-store sa SQL Tuning set.

Ang pakinabang ng paggamit ng DBMS_XPLAIN na pakete ay nagbibigay-daan ito sa iyong tingnan ang na-format na plano sa pagpapatupad ng isang SQL statement, anuman ang pinagmulan.

Ipaliwanag ang utos ng PLAN

Ang utos na EXPLAIN PLAN ay ginagamit upang bumuo ng isang query execution plan.
Kapag nabuo na ang plano, maaari itong matingnan sa pamamagitan ng pagtatanong sa impormasyon mula sa talahanayan ng PLAN_TABLE

Ang PLAN TABLE ay awtomatikong nilikha bilang isang pandaigdigang pansamantalang talahanayan, na pagkatapos ay ginagamit ng lahat ng mga gumagamit upang mag-imbak ng mga plano sa pagpapatupad. Maaari kang lumikha ng iyong sariling PLAN TABLE gamit ang isang script $ORACLE_HOME/rdbms/admin/utlxplan.sql kung kailangan ng pangmatagalang imbakan ng mga plano sa pagpapatupad.

Ipaliwanag ang PLANo Istraktura ng Utos

Ang EXPLAIN PLAN command ay naglalagay ng isang row sa PLAN TABLE para sa bawat hakbang ng execution plan.

Ipaliwanag ang halimbawa ng utos ng PLAN

Ang utos na ito ipinapasok ang execution plan para sa expression sa PLAN TABLE at idinaragdag ang demo01 tag para sa sanggunian sa ibang pagkakataon.

Mayroong maraming mga paraan upang makakuha ng isang plano sa pagpapatupad. Ang pamamaraan sa itaas ay gumagamit ng EXPLAIN PLAN command. Ang utos na ito ay bumubuo ng isang execution plan para sa isang SQL statement nang hindi ito isinasagawa, at inilalagay ang resulta sa PLAN TABLE. Ang PLAN TABLE ay kumakatawan sa isang istraktura ng puno kung saan maaari mong ibalik ang execution plan para sa isang expression gamit ang ID at PARENT_ID na mga column at ang CONNECT BY clause sa PUMILI ng expression.

Pag-output ng mga nilalaman ng PLAN TABLE

Ang halimbawa sa itaas ay gumagamit ng ALL key para sa DBMS_XPLAIN.DISPLAY function, na nagbibigay-daan sa iyong tingnan ang lahat magagamit na impormasyon tungkol sa execution plan, na nakaimbak sa PLAN TABLE. Ang konklusyong ito bilang karagdagan sa karaniwang impormasyon, naglalaman ng karagdagang impormasyon tulad ng PROJECTION, ALIAS at REMOTE SQL na impormasyon kung ang operasyon ay ibinahagi.

Para sa mas tumpak na kontrol sa impormasyon ng output, maaaring gamitin ang sumusunod: pangunahing mga parameter. Ang bawat isa keyword nagdaragdag ng hiwalay na bloke sa output ng impormasyon mula sa PLAN TABLE. Ang mga keyword ay dapat na pinaghihiwalay ng kuwit o espasyo:

Ang ROWS, kung naaangkop, ay nagpapakita ng bilang ng mga row na inaasahang mabibilang ng optimizer.
Ang ROWS, kung naaangkop, ay nagpapakita ng bilang ng mga byte na inaasahang mabibilang ng optimizer.
HALAGA kung naaangkop, ipinapakita ang gastos,siguro kalkulado ng optimizer
PARTITION kung naaangkop, nagpapakita ng patrician na itinapon ng optimizer
PARALLEL o angkop ba ito, ay nagpapakita ng impormasyon ng PX (pamamaraanpamamahagi ng impormasyon at impormasyon tungkol sa mga queue sa pag-access sa talahanayan)
PREDICATEo angkop ba ito, nagpapakita ng impormasyon tungkol sa panaguri
PROJECTION o ito ay angkop, ay nagpapakita ng seksyonmga projection

Paggamit ng Explain Plan sa SQL Developer

AUTOTRACE

Kapag ang expressiontumatakbo sa SQL*Plus o SQL Developer, maaari mong awtomatikong makuha ang execution plan at execution statistics ng isang expression. Awtomatikong nabuo ang ulat pagkatapos magsagawa ng anumang uri ng mga operasyon gaya ng SELECT, INSERT, UPDATE at DELETE. Maaaring gamitin ang impormasyong ito upang masuri at ibagay ang pagganap ng mga SQL statement.

Upang magamit ang AUTOTRACE, dapat gumawa ng PLAN TABLE sa database at ang user na nagsasagawa ng AUTOTRACE ay dapat bigyan ng tungkuling PLUSTRACE. TungkulinAng PLUSTRACE ay nilikha at ibinigay sa tungkulin ng DBA gamit ang isang script$ORACLE_HOME/sqlplus/admin/plustrce.sql

Syntax AUTOTRACE

Maaari kang magsagawa ng Autotrace gamit ang syntax na ipinapakita sa figure sa itaas. Ang mga sumusunod na opsyon ay magagamit din:

OFF Hindi pinapagana ang paggamit ng pagsubaybay
ON Pinapagana ang paggamit ng awtomatikong pagruruta
TRACE Pinapagana ang awtomatikong pagsubaybay at pinipigilan ang output ng SQL
Ipaliwanag Ipinapakita ang plano ng pagpapatupad ngunit hindi nagpapakita ng mga istatistika
STATISTICS Nagpapakita ng mga istatistika na walang plano sa pagpapatupad

Mga halimbawa ng paggamit ng AUTOTRACE

AUTOTRACE: STATISTICS

Ang mga istatistika na naitala ng server kapag isinagawa ang expression ay nagpapakita ng lakas ng tunog mapagkukunan ng system, na ginugol sa pagpapatupad ng expression at kasama ang mga sumusunod na pangunahing tagapagpahiwatig:

mga recursive na tawag- dami mga recursive na tawag, nabuo sa panig ng kliyente at server. Oracle Database sumusuporta sa mga talahanayan na ginagamit para sa panloob na pagproseso. Kapag ang Oracle Database ay kailangang gumawa ng mga pagbabago sa mga talahanayang ito, ito ay bumubuo panloob na operator SQL, na bumubuo naman ng recursive na tawag.
nakukuha ng db block- ang dami ng beses na hiniling ang CURRENT block
consistent na nakukuha- ang dami ng beses na hinihiling ang operasyon ng integral reading ng mga bloke ng data.
pisikal na pagbabasa- bilang ng mga bloke ng data na nabasa mula sa disk. Ang numerong ito ay kumakatawan sa kabuuan ng mga halaga pisikal na pagbabasa nang direkta at lahat ng nabasa mula sa buffer cache.
gawing muli ang laki- kabuuang bilang ng nabuong redo sa mga bloke
byte na ipinadala sa pamamagitan ng SQL*Net sa kliyente- ang kabuuang bilang ng mga byte na inilipat sa kliyente mula sa proseso sa background.
byte na natanggap sa pamamagitan ng SQL*Net mula sa kliyente- kabuuang bilang ng mga byte na natanggap mula sa Oracle*Net client
SQL*Net roundtrips papunta/mula sa kliyente- Ang kabuuang bilang ng mga mensahe ng Oracle NET na ipinadala at natanggap mula sa kliyente.
uri (memorya)- ang bilang ng mga pagpapatakbo ng pag-uuri na matagumpay na nakumpleto sa memorya at hindi nangangailangan ng pagsulat sa disk.
uri (disk)- ang bilang ng mga pagpapatakbo ng pag-uuri na nangangailangan ng hindi bababa sa isang operasyon sa disk.
naproseso ang hilera- ang bilang ng mga row na naproseso sa panahon ng operasyon.

db_block_gets nagpapakita ng mga read operation ng kasalukuyang block mula sa database. consistent na nakukuha- ito ay mga block reading operation na dapat matugunan ang isang partikular na numero ng SCN. pisikal na pagbabasa nagpapakita ng mga bloke ng pagbabasa mula sa disk. db_block_gets At consistent na nakukuha- mga istatistikal na tagapagpahiwatig na patuloy na sinusubaybayan. Dapat ay mababa ang mga ito kumpara sa bilang ng mga row na nakuha. Ang pag-uuri ay isinasagawa sa memorya, hindi sa disk.

AUTO TRACE gamit ang SQL*Developer

Tingnan ang V$SQL_PLAN

Ipinapakita ng view na ito ang mga plano sa pagpapatupad para sa mga cursor na nasa cache ng library. Ang impormasyong nakaimbak sa view na ito ay sa maraming paraan katulad ng impormasyon mula sa PLAN TABLE. Gayunpaman, ang V$SQL_PLAN ay naglalaman ng mga plano sa pagpapatupad para sa mga expression na naisakatuparan na. Ang execution plan na ginawa ng EXPLAIN PLAN ay maaaring iba sa aktwal na execution plan na naka-store sa cursor. Nangyayari ito dahil ang mga parameter ng session at mga halaga ng variable ng BIND ay maaaring mag-iba mula sa mga kasalukuyang.

Isa pang kapaki-pakinabang na view: V$SQL_PLAN_STATISTICS na nagbibigay ng mga istatistika ng pagpapatupad para sa bawat operasyon sa plano ng pagpapatupad ng bawat naka-cache na cursor. Isa pang kapaki-pakinabang na ideya V$SQL_PLAN_STATISTIC_ALL pinagsasama ang impormasyon ng pagpapatupad mula sa V$SQL_PLAN_STATISTICS At V$SQL_WORKAREA na may nakaimbak na plano sa pagpapatupad V$SQL_PLAN.

Paglalarawan ng mga pangunahing column ng V$SQL_PLAN view

Ang view ay naglalaman ng parehong mga column gaya ng PLAN TABLE at ilang karagdagang column. Mga column na nasa PLAN TABLE at may parehong mga value:

ADDRESS
HASH_VALUE

Ang PLAN_HASH_VALUE ay isang column na naglalaman ng numeric na representasyon ng SQL expression plan para sa cursor. Sa pamamagitan ng paghahambing ng mga halaga ng column na PLAN_HASH_VALUE, mabilis mong matutukoy kung nagbabago ang plano ng pagpapatupad para sa parehong expression nang hindi inihahambing ang mga ito sa bawat hilera.

Tingnan ang V$SQL_PLAN_STATISTICS

Nagbibigay ang V$SQL_PLAN_STATISTICS view kasalukuyang mga istatistika sa pamamagitan ng pagpapatupad para sa bawat operasyon sa plano ng pagpapatupad, tulad ng bilang ng mga row na naproseso o ang oras ng pagpapatupad. Ang lahat ng mga istatistika, maliban sa bilang ng mga hilera, ay naipon. Halimbawa, ang mga istatistika sa mga pagsali sa talahanayan ay maaaring magsama ng 3 pagpapatakbo ng pagsasama sa talahanayan. Ang mga istatistika na naka-imbak sa V$SQL_PLAN_STATISTICS ay available para sa mga cursor na pinagsama-sama sa initialization parameter STATISTICS_LEVEL = ALL o gamit ang GATHER_PLAN_STATISTICS optimizer hint.

Ang V$SQL_STATISTICS_ALL view ay naglalaman ng mga istatistika ng paggamit ng memorya para sa lahat ng source row na gumamit ng SQL memory (pag-uri-uriin o pagsali sa HASH na ito ay pinagsasama-sama ang impormasyong nakaimbak sa V$SQL_PLAN view kasama ang mga istatistika ng pagpapatupad mula sa V$SQL_PLAN_STATISTICS at V$SQL_WORKAREA na view). .

Mga ugnayan sa pagitan ng mahahalagang dynamic na view ng performance

V$SQLAREA ay nagpapakita ng mga istatistika para sa mga shared SQL na lugar at naglalaman ng isang linya para sa bawat isa Mga string ng SQL mga ekspresyon. Ang view na ito ay nagbibigay ng mga istatistika sa mga SQL statement na na-parse na, nasa memorya, at handa nang isagawa:

SQL_ID- SQL identifier ng parent cursor sa cache ng library
VERSION_COUNT bilang ng mga child cursor na nasa cache para sa isang partikular na parent cursor

V$SQL nag-iimbak ng mga istatistika sa mga shared SQL na lugar at naglalaman ng isang row para sa bawat SQL expression na nagmula sa parent na SQL expression:

ADDRESS kumakatawan sa parent cursor header address para sa isang naibigay na cursor
HASH_VALUE-ang halaga ng parent expression sa library cache
SQL_ID- SQL identifier ng parent cursor sa cache ng library
PLAN_HASH_VALUE- numeric view ng SQL plano para sa cursor na ito
CHILD_NUMBER- numero ng cursor ng bata

Ang mga istatistika na nakaimbak sa V$SQL ay ina-update habang isinasagawa ang mga SQL statement. Gayunpaman, para sa mga matagal nang expression, ang impormasyon sa view ay ina-update bawat limang segundo. Ginagawa nitong posible na suriin ang epekto ng pagganap ng mga matagal nang query habang tumatakbo ang mga ito.

V$SQL_PLAN naglalaman ng impormasyon ng plano ng pagpapatupad para sa bawat cursor ng bata na na-load sa cache ng library. Mga hanay ADDRESS, HASH_VALUE At CHILD_NUMBER maaaring gamitin upang kumonekta sa V$SQL para sa susunod na kahulugan ng mga child cursor.

V$SQL_PLAN_ISTATISTIKA nagbibigay ng source-row-level execution statistics para sa bawat child cursor.Mga hanay ADDRESS, HASH_VALUE maaaring gamitin upang pagsamahin sa isang view V$SQLAREA upang matukoy ang magulangcursor. Mga hanay ADDRESS, HASH_VALUE At CHILD_NUMBERmaaaring gamitin upang kumonekta saV$SQLpara tukuyin ang mga child cursor.

V$SQL_PLAN_STATISTICS_ALL naglalaman ng mga istatistika ng paggamit ng memorya para sa lahat ng source row na gumamit ng SQL memory (sort or HASH join) Pinagsasama-sama ng view na ito ang impormasyong nakaimbak sa view V$SQL_PLAN na may mga istatistika ng pagpapatupad mula sa mga view V$SQL_PLAN_STATISTICS At V$SQL_WORKAREA.

V$SQL_WORKAREAnagbibigay ng mga istatistika sa mga lugar ng trabaho na kasangkot sa proseso gawaing SQL mga ekspresyon. Ang bawat SQL statement na nakaimbak sa isang shared pool ay may isa o higit pang mga child cursor, ang impormasyon kung saan naka-store V$SQL. V$SQL_WORKAREA naglalaman ng impormasyon tungkol sa lahat ng workspace na kailangan ng mga child cursor na ito.

V$SQL_WORKAREAmaaaring kumonekta sa V$SQLAREA (ADDRESS, HASH_VALUE) at kasama V$SQL ( ADDRESS, HASH_VALUE,CHILD_NUMBER).

Gamit ang representasyong ito, makakakuha ka ng mga sagot sa mga sumusunod na tanong:

Nangungunang 10 lugar ng trabaho na higit na nangangailangandamimemorya para sa cache
Para sa mga workspace na tumatakbo sa AUTO mode, ilang porsyento ng mga workspace ang ginagamit maximum na dami alaala?

V$SQLSTATS ay nagpapakita ng mga pangunahing istatistika ng pagganap para sa mga SQL cursor, na ang bawat hilera ay kumakatawan sa data na pinagsasama ang SQL expression text at SQL execution plan (kumbinasyon SQL_ID At PLAN_HASH_VALUE). Mga column sa V$SQLSTATS magkapareho, V$SQL At V$SQLAREA. Gayunpaman, ang pagtatanghal V$SQLSTATS iba sa V$SQL At V$SQLAREA bilis ng pagpoproseso, scalability, mahabang panahon ng pag-iimbak ng data (maaaring iimbak ang data ng istatistika sa view, kahit na matapos na maalis ang cursor mula sa nakabahaging pool).

Halimbawa ng pag-query ng data mula sa V$SQL_PLAN view

Maaari kang mag-query ng data mula sa isang view V$SQL_PLAN gamit ang function DBMS_XPLAIN.DISPLAY_CURSOR() upang ipakita ang kasalukuyan o huling naisakatuparan na expression (tulad ng ipinapakita sa halimbawa). Maaari mong ipasa ang halaga SQL_ID bilang parameter para makuha ang execution plan para sa ibinigay na pagpapahayag. SQL_ID - SQL_ID mga expression na nakaimbak sa cache ng cursor. Makukuha mo ang katumbas na halaga sa pamamagitan ng pagtatanong sa impormasyon ng column SQL_ID V V$SQL At V$SQLAREA. Bilang kahalili, maaari kang pumili PREV_SQL_ID para sa isang partikular na sesyon mula sa V$SESSION. Bilang default, ang pagpipiliang ito ay hindi tinukoy, kung saan ang plano na nakaimbak sa huling naisakatuparan na cursor ay ipinapakita.

IOSTATS: Ipagpalagay na ang proseso ng pagpapatupad ng SQL ay nangongolekta ng mga pangunahing istatistika para sa mga plano sa pagpapatupad, ang STATISTICS_LEVEL ay nakatakda sa LAHAT, o ang HINT GATHER_PLAN_STATISTICS ay ginagamit), ang format na ito ay nagpapakita ng mga istatistika ng I/O para sa lahat, kapag tinukoy ang LAHAT (o ang huli lamang, kapag tinukoy ang LAST) mga pagpapatupad ng cursor.
MEMSTATS: Ipagpalagay na ginamit awtomatikong kontrol PGA (pga_aggregate_target parameter ay nakatakda sa isang hindi-zero na halaga) ang format na ito ay nagbibigay-daan sa iyo upang ipakita ang mga istatistika ng paggamit ng memorya ganitong uri ang mga istatistika ay naaangkop lamang sa mga pagpapatakbong masinsinang memorya, tulad ng HASH Join, pag-uuri, o ilan sa mga operator ng bitmap.
ALLSTATS: kasingkahulugan ng "IOSTATS MEMSTATS"
HULING: Bilang default, ipinapakita ang mga istatistika ng plano ng pagpapatupad para sa lahat ng mga pagpapatupad ng cursor. Gamit ang LAST na keyword, maaari mong tingnan ang mga istatistika ng plano na nabuo mula noong huling beses itong naisakatuparan.

Mahahalagang AWR Views

Maaari mong tingnan ang data ng AWR sa Enterprise Manager o sa pamamagitan ng pagbuo ng ulat ng AWR, at maaari mo ring ma-access ang mga sumusunod na dynamic na view ng pagganap na nag-iimbak ng data ng AWR:

V$ACTIVE_SESSION_HISTORY- Ipinapakita ng view na ito ang impormasyon tungkol sa pinakabagong aktibidad ng session, na ina-update bawat segundo.
Ang V$ metric view ay nagpapakita ng metric data para subaybayan ang performance ng system. Ang listahan ng mga view ng sukatan ay maaaring tingnan sa pamamagitan ng pag-access sa V$METRICGROUP view.
Ang mga view ng DBA_HIST ay naglalaman ng makasaysayang data na nakaimbak sa database. Kasama sa grupong ito ng mga view ang:

DBA_HIST_ACTIVE_SESS_HISTORY naglalaman ng mga nilalaman ng kasaysayan ng aktibong session na pinili mula sa memorya batay sa kamakailang aktibidad ng system
DBA_HIST_BASELINE naglalaman ng impormasyon tungkol sa mga linya ng sanggunian na nakaimbak sa database.
DBA_HIST_DATABASE_INSTANCE naglalaman ng impormasyon tungkol sa kapaligiran ng database
DBA_HIST_SNAPSHOT naglalaman ng impormasyon tungkol sa mga snapshot na nakaimbak sa system
DBA_HIST_SQL_PLAN naglalaman ng impormasyon tungkol sa mga plano sa pagpapatupad
DBA_HIST_WR_CONTROL naglalaman ng impormasyon tungkol sa mga setting ng AWR

Paghiling ng data mula sa AWR

Pagbuo ng mga ulat para sa isang tiyakSQLmula sa imbakan ng AWR

Pagsubaybay sa SQL

Tool Pagsubaybay sa SQL magagamit bilang default kapag ang opsyon STATISTICS_LEVEL nakatakda sa halaga TYPICAL o LAHAT. Upang gamitin ng instrumentong ito dapat mo ring itakda ang halaga ng parameter CONTROL_MANAGEMENT_PACK_ACCESS sa kahulugan DIAGNOSTIC+TUNING para paganahin ang diagnostic at configuration package.

Bilang default, awtomatikong pinapagana ang pagsubaybay sa SQL kapag ang isang SQL statement ay naisakatuparan o kapag gumagamit ito ng higit sa limang segundo ng oras ng CPU o I/O sa isang solong pagpapatupad.

Mayroong dalawang pahiwatig ng optimizer upang tahasang paganahin o huwag paganahin ang pagsubaybay sa SQL para sa isang expression - MONITOR At NO_MONITOR.

Maaari mong subaybayan ang mga istatistika ng pagpapatupad ng SQL statement gamit ang mga view V$SQL_MONITOR At V$SQL_PLAN_MONITOR.

Pagkatapos i-activate ang SQL Statement Monitoring sa Dynamic Performance View V$SQL_MONITOR Nagdaragdag ng impormasyong kailangan upang subaybayan ang mga pangunahing sukatan ng pagganap gaya ng oras ng pagpapatupad, oras ng CPU, bilang ng mga reads at writes, I/O latency, at iba pang mga sukatan ng latency. Ang istatistikang ito na-update sa real time sa panahon ng SQL execution, bilang default - bawat segundo. Matapos makumpleto ang pagpapatupad, ang impormasyon tungkol sa pagpapatupad ay naka-imbak sa view V$SQL_MONITOR isa pang minuto, pagkatapos nito ay tinanggal.

Halimbawang ulat sa Pagsubaybay sa SQL

SA sa halimbawang ito Ipinapalagay nito na pumipili ka ng data mula sa talahanayan ng SALES at nagpapatakbo ng SQL Monitoring sa isa pang session.

Ang DBMS_SQLTUNE.REPORT_SQL_MONITOR function ay maaaring tumanggap ng ilang mga parameter na naglilimita o nagpapalawak sa antas ng detalye ng ulat, gamit ang mga karagdagang parameter na maaari mo ring tukuyin ang output format ng ulat (TEXT, HTML o XML), bilang default ang ulat ay nabuo sa teksto pormat.

Upang natatanging makilala ang dalawang execution ng parehong SQL statement, isang composite key na tinatawag na execution key ay nabuo. Itong susi ay binubuo ng tatlong katangian, ang bawat isa ay tumutukoy sa isang column sa V$SQL_MONITOR:

SQL_ID
Isang panloob na nabuong identifier upang matiyak na ang ibinigay pangunahing susi ay talagang natatangi (SQL_EXEC_ID)
timestamp ng pagsisimula ng expression execution (SQL_EXEC_START)

Interpretasyon ng Plano ng Pagpapatupad

Konklusyon IPALIWANAG ANG PLANO ay isang tabular na representasyon ng istraktura ng puno ng plano ng pagpapatupad. Ang bawat hakbang (isang linya sa isang plano sa pagpapatupad o isang node sa isang puno) ay kumakatawan sa isang Row Source.
Ang pagkakasunud-sunod ng mga node sa ilalim ng magulang ay nagpapakita ng pagkakasunud-sunod ng pagpapatupad ng mga node sa antas na iyon. Kung ang dalawang hakbang ay nasa parehong antas, ang una sa pagkakasunud-sunod ay unang isasagawa.
Sa isang tree format, ang mga dahon sa kaliwa sa bawat antas ng puno ay tumutukoy sa punto kung saan magsisimula ang pagpapatupad.
Ang mga hakbang sa plano ng pagpapatupad ay hindi isinasagawa sa pagkakasunud-sunod kung saan binibilang ang mga ito. Mayroong relasyon ng magulang-anak sa pagitan ng mga hakbang.
Sa PLAN_TABLE at V$SQL_PLAN mahahalagang elemento para makuha ang istraktura ng puno ay ang mga column ng ID, PARRENT_ID at POSITION. Sa trace file, ang mga column na ito ay tumutugma sa id, pid at pos field ayon sa pagkakabanggit.
Ang isang paraan upang basahin ang isang plano sa pagpapatupad ay ang pag-convert nito sa isang graph na may istraktura ng puno. Maaari kang magsimula sa itaas, ang entry na may ID=1 ay ang tuktok na punto ng puno. Ito ay totoo para sa mga pagpapatakbo na may parrent_id o pid na halaga na 1.
Upang katawanin ang plano bilang isang puno, gawin ang sumusunod:

Kunin ang ID na may pinakamababang halaga at ilagay ito sa tuktok ng puno.
Tukuyin ang mga row na may PID (parrent id) na katumbas ng value na ito.
Ilagay ang mga ito sa puno sa ibaba tala ng magulang ayon sa kanilang mga halaga ng POS mula sa pinakamaliit hanggang sa pinakamalaki mula kaliwa hanggang kanan.
Kapag nahanap na ang lahat ng parent ID, lumipat sa isang level sa susunod na ID at ulitin ang proseso, sa paghahanap ng mga bagong row na may parehong PID.

Ang unang bagay na tutukuyin sa plano ng pagpapatupad ay kung aling node ang unang ipapatupad. Ang pamamaraan na ipinapakita sa figure ay nagpapaliwanag kung paano ito gagawin, ngunit kung minsan sa kumplikadong mga plano sa pagpapatupad ay mahirap gawin ito at mahirap ding sundin ang lahat ng mga hakbang hanggang sa wakas. Ang mga kumplikadong plano ay hindi naiiba sa komposisyon mula sa mga simple maliban sa bilang ng mga hakbang. Ang parehong naaangkop sa kanila mga simpleng tuntunin. Maaari mong palaging itago ang mga hakbang sa iyong plano na hindi kumukonsumo ng malaking halaga ng mga mapagkukunan.
Karaniwang pamamaraan para sa pagbibigay-kahulugan sa isang plano sa pagpapatupad:

Magsimula sa tuktok
Ibaba ang mga operasyon hanggang sa maabot mo ang gumagawa ng data ngunit hindi kumukonsumo ng anuman. Ito ang simula ng operasyon.
Tingnan ang mga operasyon ng bata na mayroon ang magulang na ito. Ang mga operasyon ng bata ay isasagawa tulad ng sumusunod
Itaas ang puno hanggang sa masuri ang lahat ng transaksyon.

Ang karaniwang paraan para sa pagbibigay-kahulugan sa puno ng plano ng pagpapatupad ay:

Magsimula sa tuktok
Ilipat pababa at pakaliwa sa kahabaan ng puno hanggang sa maabot mo ang kaliwang node, ito ay isasagawa muna
Tingnan ang mga bata ng node na ito. ang mga batang ito ay papatayin pa.
Matapos maisakatuparan ang mga bata, ang operasyon ng magulang ay magpapatuloy sa pagpapatupad.
Ngayon, pagkatapos ng operasyong ito at ang lahat ng mga inapo nito ay makumpleto, umakyat sa puno at tingnan ang mga inapo ng orihinal na serye ng mga operasyon at ang mga magulang nito. Isinasagawa ito ayon sa parehong prinsipyo.
Mag-navigate sa puno hanggang sa masuri ang lahat ng mga transaksyon

Pagpapakahulugan sa Plano ng Pagpapatupad: Halimbawa 1

Ang figure sa itaas ay nagpapakita ng interpretasyon ng execution plan para sa expression. Ang query na ipinapakita sa figure ay sumusubok na maghanap ng mga empleyado na ang mga suweldo ay iba sa salary grid. Pinipili ng query ang data mula sa dalawang talahanayan at may kasamang subquery batay sa isang seleksyon mula sa isa pang talahanayan upang suriin ang mga antas ng suweldo.
Tingnan natin ang pagkakasunud-sunod ng pagpapatupad para sa kahilingang ito. Batay dito at sa nakaraang figure, ang utos ng pagpapatupad ay ang mga sumusunod: 3-5-4-2-6-1:

3: magsisimula ang pagpapatupad ng plano sa isang buong pag-scan ng talahanayan ng EMP (ID=3)
5: ang mga row ay ipinapasa sa hakbang na kumokontrol sa nested loop (ID=2), na gumagamit ng mga ito upang hanapin ang mga row sa PK_DEPT index (ID=5)
4: Ang mga ROWID ng mga row na nakuha pagkatapos i-scan ang PK_DEPT ay ginagamit upang makuha ang iba pang impormasyon mula sa talahanayan ng DEPT (ID=4)
2: ID=2, ang proseso ng nested loop merging ay magpapatuloy hanggang sa ito ay makumpleto
6: Pagkatapos maproseso ng ID=2 ang lahat ng source row para sa pagsali, isasagawa ang buong pag-scan ng table na SALGRADE (ID=6).
1: Ang data na natanggap pagkatapos i-execute ang ID=6 ay gagamitin para salain ang mga row mula sa ID=2 at ID=6

Ang mga proseso ng bata ay isinasagawa bago ang mga proseso ng magulang, bagama't ang mga istruktura ng koneksyon ay dapat mabuo bago ang mga proseso ng bata ay isagawa. Marahil ang pinakasimpleng paraan upang ipaliwanag ang utos ng pagpapatupad ay upang magsagawa ng isang NESTED LOOPS na operasyon ng pagsasama na may ID=2, ang dalawang bata (ID=3 at ID=4 (kasama ang kanilang mga anak)) ay dapat kumpletuhin ang kanilang pagpapatupad bago ang ID=2 ay pinaandar.

Pagpapakahulugan sa Plano ng Pagpapatupad: Halimbawa 2

Ibinabalik ng query na ito ang mga pangalan, pangalan ng departamento, at address ng mga empleyado na ang mga departamento ay matatagpuan sa Seattle at may manager.

Para sa kadalian ng pag-format, isa pang pagnunumero ang idinagdag sa figure. Ang column sa kaliwa ay kumakatawan sa ID at ang column sa kanan ay kumakatawan sa PID. Ang plano ng pagpapatupad ng query ay nagpapakita ng dalawang NESTED LOOP JOIN na operasyon. Binibigyang-kahulugan namin ang plano ng pagpapatupad ayon sa pamamaraan na ipinakita sa itaas:

Magsimula tayo sa itaas. ID=0
Bumaba kami sa mga operasyon hanggang sa maabot namin ang isa na gumagawa ng data ngunit hindi kumukonsumo ng anuman. SA sa kasong ito Kumokonsumo ng data ang ,ID 0,1,2, at 3. Ang ID=4 ay ang unang operasyon mula sa itaas na hindi gumagamit ng mga mapagkukunan, ngunit gumagawa ng data. Ito ang unang data source. Ibabalik ng INDEX RANGE SCAN ang ROWID ng mga row na gagamitin para ibalik ang data mula sa LOCATIONS table (ID=3)
Tingnan natin ang magkakapatid nitong pinagmumulan ng hilera na kapareho ng antas nito sa puno. Ang mga kapatid na lalaki at babae sa parehong antas ay may ID=3 at ID=5. Ang ID = 5 ay may anak - ID = 6, na isasagawa bago ito. Ito ay isang INDEX RANGE SCAN na operasyon sa isa pang index, na nagbabalik ng isang ROWID na sa ibang pagkakataon ay gagamitin upang kunin ang data mula sa DEPARTMENTS table sa panahon ng execution ID=5.
Pagkatapos magsagawa ng operasyon ng bata, ang kontrol ay ililipat sa ninuno nito. Ang susunod na operasyon ay magiging NESTED LOOPS na may ID=2 upang pagsamahin ang dating natanggap na data.
Ngayong natapos na ang operasyon ng magulang at lahat ng mga inapo nito, umakyat kami sa puno at tingnan kung ang operasyon ng magulang ay may mga kapatid na kapantay nito. Ang ID=2 ay nasa parehong antas ng operation ID=7 na mayroong child ID=8. Papatayin muna ang batang ito. Isang INDEX UNIQUE SCAN ang isasagawa upang makuha ang mga ROWID ng mga row, na pagkatapos ay gagamitin upang kunin ang data mula sa EMPLOYEES table sa operation ID=7.
lumipat tayo sa mas mataas na antas pagkatapos maproseso ang lahat ng operasyon sa kasalukuyang antas at ang kanilang mga inapo. Ang huling operasyon na isasagawa ay ang pagsamahin ang NESTED LOOPS sa ID=1, pagkatapos nito ay ipapadala ang resulta ng ID=0.
Ang pagkakasunud-sunod ng operasyon ay ang mga sumusunod: 4-3-6-5-2-8-7-1-0

Nasa ibaba ang detalyadong paglalarawan plano ng pagpapatupad:

Una, ang isang panloob na nested loop ay isinasagawa gamit ang LOCATIONS bilang nangungunang talahanayan, gamit ang index na access sa column ng CITY. Gumagana ang operasyon dahil naghahanap ka lamang ng mga departamento sa Seattle.

Ang resulta ay pinagsama sa DEPARTMENTS table gamit ang index sa LOCATION_ID column para sa pagsali. Ang resulta ng unang operasyon ng pagsali ay ang nangungunang data source para sa pangalawang nested loop.

Sinusuri ng pangalawang operasyon ng pagsali ang index sa EMPLOYEE_ID column ng EMPLOYEES table. Maaaring isagawa ang operasyong ito dahil alam ng system (mula sa unang operasyon ng pagsali) ang mga ID ng lahat ng tagapamahala ng departamento sa Seattle. Sa kasong ito, ang NATATANGING PAG-SCAN ay isinasagawa dahil ang pag-scan ay isinasagawa sa pangunahing susi ng susi.

Una, hina-hash ng system ang talahanayan T3 sa memorya (ID=3)

Pagkatapos ay hina-hash ng system ang talahanayan T1 sa memorya (ID=5)

Pagkatapos ay magsisimula ang pag-scan ng talahanayan T2 (ID-6).

Ang system ay kumukuha ng isang hilera mula sa T2 at sinusuri ang T1 (T1.i=T2.i)

Kung mananatili ang linya, hahanapin ito ng system sa T3 (T1.i=T3.i)

Kung mananatili ang row, ipapasa ito ng system sa susunod na operasyon.

Mga isyu sa system pinakamataas na halaga mula sa nakaraang hanay ng mga resulta.

Ang pagkakasunud-sunod ng pagpapatupad ay ang mga sumusunod: 3-5-6-4-2-1

Pagsasama-sama: T1-T2-T3

Pagbasa ng Higit pang Mga Comprehensive na Plano sa Pagpapatupad

Ang plano na ipinapakita sa figure sa kaliwa ay binuo mula sa isang query mula sa diksyunaryo ng data. Napakahaba nito, kaya napakahirap ilapat ang mga naunang pamamaraan ng interpretasyon upang mahanap ang unang operasyon.

Maaari mong paikliin ang balangkas upang gawin itong mas nababasa. Ang figure sa kanan ay nagpapakita ng parehong plano sa pagpapatupad, pinaikli lang. Gaya ng ipinapakita sa figure, madali itong gawin gamit ang Enterprise manager o SQL*Developer. Tulad ng makikita sa figure, kasama sa plano ang pagpapatakbo ng pagsasama ng dalawang sangay. Ang kaalaman sa diksyunaryo ng data ay nagbibigay-daan sa amin na maunawaan na ang dalawang sangay ay tumutugma sa mga tablespace na pinamamahalaan ng diksyunaryo at pinamamahalaan ng lokal. Ang kaalaman tungkol sa database ay nagbibigay-daan sa iyo na maunawaan na ang database ay walang mga puwang ng talahanayan na pinamamahalaan ng diksyunaryo. Sa ganitong paraan, kung may problema, ito ay nasa pangalawang sangay. Upang kumpirmahin ang iyong mga pagpapalagay, kailangan mong tingnan ang impormasyon ng plano at mga istatistika ng pagpapatupad ng bawat row source upang matukoy ang bahagi ng plano na kumukonsumo ng pinakamaraming mapagkukunan. Pagkatapos ay kailangan mong palawakin ang sangay kung saan matatagpuan ang mga problema. Upang magamit ang paraang ito, kailangan mo ring gumamit ng mga istatistika ng pagpapatupad, na makikita sa V$SQL_PLAN_STATISTICS view o sa ulat ng tkprof na nabuo mula sa trace file. Halimbawa, binibilang ng tkprof ang oras na kailangan ng bawat operasyon ng magulang upang maisakatuparan kasama ang oras ng pagpapatupad ng lahat ng operasyon ng bata.

Pangkalahatang-ideya ng Plano ng Pagpapatupad

Kapag nag-set up ka ng SQL expression sa isang OLTP environment, ang layunin ay magsimula sa talahanayan na may pinakamataas na selectivity filter na inilapat. Nangangahulugan ito na mas kaunting mga row ang ipapasa sa susunod na operasyon. Kung ang susunod na hakbang ay isang pagsali, ipinapalagay na mas kaunting mga hilera ang sasalihan. Kailangan mo ring tiyakin na ang mga path ng pag-access ng data ay pinakamainam. Habang sinusuri mo ang plano ng pagpapatupad, tandaan ang sumusunod:

Idinisenyo ang plano upang mailapat ang pinakamahusay na filter sa nangungunang talahanayan
Ang pagkakasunud-sunod ng pagsali ay idinisenyo upang ang pinakamaliit na bilang ng mga hilera ay maipasa sa susunod na hakbang (iyon ay, ang pagkakasunud-sunod ng pagsali ay dapat pumunta sa pinakamahusay na mga filter na hindi pa ginagamit)
Ang paraan ng pagsali ay tumutugma sa bilang ng mga row na ipinapasa dito upang sumali. Halimbawa, ang isang NESTED LOOP na pagsali gamit ang isang index ay maaaring hindi pinakamainam kapag maraming row ang ibinalik.
Mabisang ginagamit ang mga view. tignan mo PUMILI ng listahan upang matukoy kung saan kailangang ma-access ang view.
Walang mga pagpapatakbo ng produkto ng Cartesian ng mga talahanayan (kahit na may maliliit na talahanayan).
Ang bawat talahanayan ay mahusay na nagbabasa: isaalang-alang SQL predicates mga expression na may kaugnayan sa bilang ng mga hilera sa talahanayan. Tingnan ang mga kahina-hinalang operasyon nang detalyado, halimbawa full table scan para sa mga table na may isang malaking bilang mga string na nasa panaguri.

Ang buong pag-scan ng talahanayan ay maaaring magamit nang epektibo para sa maliliit na talahanayan, o para sa mga aplikasyon tiyak na uri sumali (hal. HASH JOIN) para sa mga ibinalik na row.

Kung ang alinman sa mga kundisyon sa itaas ay hindi pinakamainam, kakailanganin mong muling isulat ang SQL o suriin ang mga index na magagamit sa talahanayan.

Ang execution plan mismo ay hindi makakapagbigay ng impormasyon tungkol sa kahusayan ng query execution. Halimbawa, ang output ng EXPLAIN PLAN ay maaaring magpakita ng paggamit ng index, ngunit hindi ito nangangahulugan na epektibo ang expression. Minsan ang paggamit ng isang index ay maaaring maging lubhang hindi epektibo.

Upang matukoy pinakamainam na plano execution, kailangan mong kumuha ng paunang plano, at pagkatapos ay i-optimize ito para sa mas mahusay na execution gamit ang pagsubok. Habang binabago ang plano sa pagpapatupad, kailangan mong subaybayan ang pagkonsumo ng mga mapagkukunan ng system.