7 lietas, ko nevajag darīt

Arī mani ir sasniegusi sērga par 7 lietām, kas neesot par mani zināmas. Bet nu nekā nebija, es šeit netaisos pagaidām apspriest savas privātās lietas vai kaut ko tādu, kas pārāk tālu attālinās no datubāzēm un ar to saistīto. Ja vien mani nenovedīs līdz tam briestošo priekšvēlēšanu laikā 😉 Bet tagad nē. Tagad Jums būs 7 lietas saistītas ar datubāzes ātrdarbību (faktiski lēndarbību), ar kurām visām  es pašlaik cīnos vai man ir nācies cīnīties un par kurām varu izteikties tikai [cenzēts], [cenzēts], [cenzēts].

1. Nelietojiet SQL vaicājumos lietotāja definētas (user-defined) funkcijas. Nelietojiet tās lieliem datu apjomiem. Vēl vairāk nelietojiet tās, ja tajās atkal kaut kas tiek lasīts no datubāzes. Un tas attiecas tikpat labi uz Oracle, kā uz SQL serveri. Izbaudu to uz savas ādas gandrīz katru reizi, kad nākas cīnīties ar bremzīgiem Select vaicājumiem. Kāpēc? Oracle tas ir tāpēc, ka vaicājums tiek nosacīti izpildīts zem SQL dziņa (SQL engine), savukārt jebkurš lietotāja definēts funkcijas izsaukums prasa PL/SQL dzini (PL/SQL engine) un mētāšanās no viena uz otru ir relatīvi dārgs process. To pašu esmu novērojis SQL Server, nezinu precīzu tehnisku skaidrojumu, bet tā vien izskatās, ka tur ir kaut kas ļoti līdzīgs.
2. Nelasiet no datubāzes liekus datus. Nerakstiet SELECT * no 50 tabulām (jā 50!), rakstiet tikai tās kolonas no tām tabulām, ko Jums vajag. Tas ir pilnīgs ārprāts, ja Jūs atlasat 80 kolonas, jā 80! un pēc tam attēlojat 10. Un tas viss tikai tāpēc, ka šis vaicājums tagad derēs visās 17 vietās, kur Jums kaut ko no tā vajag attēlot. Autors par laimi (viņam) bija jau atlaists, citādi tas noteikti beigtos ar asinsizliešanu 😉
3. Nekārtojiet liekus datus. Ja nu ir jākārto (ORDER BY) kādi dati, tad vienmēr raugieties, lai kārtojamo kolonu skaits nebūtu vairāk nekā nepieciešams. Nerakstiet SELECT * ORDER BY <1, 2, 3 kolonas>, ja izrādās, ka * nozīmē 20 kolonas no kurām tālāk Jūs lietojat tikai 10. Katra lieka kolona ir lieki dati, kas datubāzes serverim jātur atmiņā un jāvazā līdz kārtošanas laikā. Tā ir papildus atmiņa, papildus apstrādes laiks un bezjēdzīgi iztērēti resursi.
4. Neatlasiet unikālās kolonas (DISTINCT jeb UNIQUE) tikai tāpat vien, drošības pēc. DBVS nezin, ka tas ir tikai tāpat, aiz nekā darīt. Ja tas patiesi ir pārāk bieži nepieciešams, tad diezgan droši tā ir kļūda datubāzes modelī. Kāpēc to nevajag? Tāpēc, ka katrs DISTINCT prasa unikālo ierakstu atlasi un jo vairāk kolonas tiks atlasītas, jo vairāk atmiņas un resursu tam būs nepieciešami. Ja tas ir izdarīts uz ierakstu kopu, kas jau tāpat ir ar unikāliem elementiem, tad tie ir burtiski zemē nomesti CPU cikli, atmiņas operācijas un iespējams pat diska apgriezieni.
5. Nerakstiet dinamisko SQLu ar iešūtām mainīgo vērtībām. Datu bāzu vadības sistēmās (Oracle, MS SQL Server), kuru izstrādātāji ir ilgi pūlējušies, lai izveidotu kopīgu atmiņas apgabalu visiem SQL teikumiem (attiecīgi shared pool un procedure cache), maz kas var būt paradoksālāk kā nezinoši vai piedodiet par izteicienu stulbi izstrādātāji, kas katru savu SQL teikumu uzģenerē ar iešūtām mainīgo vērtībām, līdz ar to visus sākotnējos pūliņus izslaukot miskastē. Iešūtas mainīgo vērtības nozīmē praktiski nulles varbūtību, ka izveidotais SQL teikuma izpildes plāns būs lietojams arī nākošajam lietotājam, jo viņa SQL teikums būs gandrīz tāds pats, bet tikai gandrīz. Tajā atšķirsies iešūtie identifikatori vai arī kādi citi mainīgie un hopsā – tas vairs nav tas pats SQL teikums, kuram izpildes plāns jau bija zināms. Tā ir viena no tūkstoš hidras galvām, kam atkal jāpārbauda tiesības uz objektiem, jāģenerē savienojumu iespējamās kombinācijas un viss SQL teikuma izpildes plāns. Sīkums vienam lietotājam, kas reizi pusstundā palaiž SQL teikumu uz 15 minūtēm, bet nežēlīga sāpe kaut vai 10 lietotājiem, kuri katrs izpildītu desmitiem vai pat simtiem SQL teikumu sekundē, ja vien katram SQL teikumam nebūtu analīzes (parse) fāze, kas konstanti aizņem sekundi…
6. Nelietojiet procedurālo valodu ciklus, lai apstrādātu ierakstus pa vienam, jo sevišķi, ja iterāciju skaits ir proporcionāls datu apjomam. Visizplatītākā lieta – kursori. Mans mazais skaistais kursoriņš, kurš izpildās zibenīgi uz dažiem ierakstiem, pārvēršas par milzīgu nekustīgu monstru, ja tas tikpat akli dodas caur reālajiem produkcijas miljons ierakstiem. Cikli, kuru iterāciju skaits ir tieši proporcionāls datu daudzumam nav savienojami ar vārdu ātrdarbība. Tie var būt tikai un vienīgi atbilstoši vārdam lēndarbība.
7. Testējiet uz reālu datu apjomu, kādu Jūsu sistēma sasniegs pēc gada, diviem. Tas, ka Jūsu vaicājums izstrādes vidē uz 10 ierakstiem atgriež funkcionāli pareizu rezultātu zibenīgi ir nekas. Burtiski nekas. Sliktākajā gadījumā saģenerējiet datus pats, ja esošus reālus daudz nevar dabūt. Jo tad, kad Jūsu vaicājums sastapsies ar miljons ierakstiem, kam nāksies katram izsaukt lietotāja definētu funkciju, nolasīt visu ierakstu 50 kolonas, visu miljonu sakārtot dēļ liekā Distinct, un to darīs vismaz 10 lietotāji vienlaicīgi palaižot jūsu dinamiski ģenerēto SQL teikumu, lūk tad iestāsies brīdis, kad klients pacels cepuri un dosies pie Jūsu konkurentiem. Jo diemžēl lielāks dzelzis nederēs. Neviens dzelzis pie kaut cik ievērojama datu apjoma nespēj pārciest šeit uzskaitīto, tā lai lietotāji nesāktu dusmās vārīties un klusiņām lādēt izstrādātāju.

Man principā diez ko nepatīk visādas ķēdes un piramīdas, galu galā Medofs arī slikti beidza 🙂 , taču gribu pievērst lasītāju uzmanību vienam visnotaļ interesantam un (cerams, ka arī turpmāk) daudzsološam blogam, kurš raksta arī par lietām, kas saistās ar tīmekli un ātrdarbību.

Lasīt arī:

• Efektīva SQL un PLSQL vadlīnijas;
• Paradigmas maiņa – SQL ātrdarbības atslēga.

Efektīva (vairāk vai mazāk) SQL un PL/SQL vadlīnijas

1. Iespēja kaut ko izdarīt visātrāk ir to nedarīt vispār, tāpēc par katru SQL teikumu būtu jāpārliecinās:

• vai tas vispār ir vajadzīgs, varbūt to var nemaz nepildīt,
• vai to tiešām ir nepieciešams pildīt tik daudz reižu (ciklā), varbūt to var pildīt tikai vienreiz (piemēram iegūt kodu no db, aizpildīt mainīgo ar sistēmas datumu utml),
• varbūt to var pildīt tikai kādos speciālos gadījumos (if, case).

2. Pēc iespējas jādomā kopas operācijās. Jāmēģina izvairīties no procedurālas SQL teikumu sasaistes. Ja problēmu ir iespējams atrisināt tikai ar SQL līdzekļiem, tad vispārējā gadījumā tā arī jādara. Jo sevišķi jāmēģina izvairīties no SQL konstrukcijām, kas ir ievietotas PL/SQL ciklos.

3. Dinamisko SQLu ir atļauts lietot tad un tikai tad, ja ar statisko SQLu uzdevumu nav iespējams izpildīt.

4. Ja tomēr nepieciešams izmantot dinamisko SQLu un jo sevišķi, ja ir paredzams, ka tas notiks bieži, tad ir jāizmanto EXECUTE IMMEDIATE kopā ar USING klauzu, lai tiktu lietoti bind variables.

5. Dinamiski ģenerējot SELECT teikumus ar iepriekš nezināmu skaitu parametru (tipiski izvērstām meklēšanas iespējām) ir jāizmanto konteksta iespējas (CREATE OR REPLACE CONTEXT), lai sistēma netiktu pārpludināta ar tikai vienreizlietojamiem unikāliem SQL teikumiem, kas nelieto BIND VARIABLES.

6. Ir jāmēģina izvairīties no lietotāja definētu funkciju izsaukšanas SQLā, jo sevišķi, ja attiecīgais SQLs tiek izpildīts bieži vai arī lietotāja definētā funkcija tiek izsaukta daudz reižu viena SQL teikuma ietvaros, jo tiek apstradāts liels datu apjoms. Tā vietā lietotāja definētā funkcija jāmēģina iekļaut pamata SQL teikumā.

7. Ja PL/SQL kodā no datubāzes jāiegūst mainīgie, kas pēc tam nemainās un tiek vairākkārt izmantoti, tad tos vēlams inicializēt vai nu pakotnes ķermenī (bet tikai tad, ja vērtība datubāzē NEVAR mainīties starp vairākiem pakotnes izsaukumiem) vai arī procedūras sākumā (ja vērtība datubāzē var mainīties starp vairākiem pakotnes izsaukumiem).

8. UPDATE operācijām pēc iespējas jāveic koriģēšana tikai tām kolonām, kas patiesi ir mainījušās.

9. Visiem SELECT, UPDATE, INSERT INTO..SELECT FROM, MERGE un DELETE, kas nav triviāli (nenotiek uz vienu tabulu pēc primārās atslēgas) vajadzētu noskaidrot potenciālo izpildes plānu lietojot tādus rīkus kā EXPLAIN PLAN vai AUTOTRACE SQL*Plusā vai izmantojot atbilstošu funkcionalitāti PL/SQL Developer vai SQL Navigator vai līdzīgos vizuālajos rīkos.

Vērtējot izpildes plānu jāņem vērā šādi faktori:

• Ja darbība tiks izpildīta ļoti bieži un atgriezīs mazu rezultātu kopumu, tad iesaistītajām operācijām būtu jānotiek pēc indeksa(-iem) un ir jāizvairās no tabulu full scan.
• Ja darbības rezultātam ir nepieciešams pārskanēt visu tabulu, tad ir jāveic full scan pa visu tabulu un jāizvairās no tabulu kombionēšanas (join) izmantojot nested loops uz šo tabulu.
• Izpildes plānam darbu būtu jāsāk no tabulas, kura ir visselektīvākā, t.i., kur attiecība <Paredzamais atgriezto ierakstu skaits>/<Viss tabulas ierakstu skaits> ir vismazākais, lai kopsummā veicot pieprasījumu tiktu parbaudīta iespējami mazākā ierakstu kombinācija datubāzē.

10. Hintu (ne)lietošana. Izpildes plāni pēc iespējas optimāli būtu jāpanāk nelietojot hintus, bet izmantojot datu bāzes statistikas. Tāpēc arī par izpildes plāna pareizību var ar zināmu drošību pārliecināties tikai līdzīga satura un apjoma (testa, produkcijas) datu bāzē, kurai ir veikta atbilstoša statistiku rēķināšana un ir līdzīga konfigurācija.

10.1. Ja tas nav iespējams, tad pēc iespējas jālieto hinti, kas dod optimizatoram (optimizer) papildus informāciju, bet tieši nenosaka plāna izpildes veidu:

• ALL_ROWS – mērķis ir iegūt visu rezultātu ar iespējami mazāko resursu patēriņu;
• FIRST_ROWS (n) – mērķis ir iegūt pirmās (n) rindiņas pēc iespējas ātrāk;
• DYNAMIC_SAMPLING (n) – dinamiski rēķina statistikas, atkarībā no dotā līmeņa n. Informācija par līmeņiem. Vērtīgs, ja statistikas netiek rēķinātas, kā arī priekš pagaidu tabulām (temporal tables).
• DRIVING_SITE (table) – ja vaicājuma izpilde notiek vairākās datubāzēs izmantojot datubāzes linku, tad norāda kurā datubāzē vaicājums tiks izpildīts.
• CARDINALITY (n) – norāda sagaidāmo ierakstu apjomu vaicājumam. Sevišķi noderīgs, ja nepieciešams izmantot tipus.

10.2. Hinti, kurus ieteicams izmantot tikai tad, ja ir 100% pārliecība par savu taisnību un kā pēdējo līdzekli:

• FULL (table) – ja ir pilnīga pārliecība, ka ir nepieciešama visas tabulas pārskanēšana, lai iegūtu rezultātu, tipiski varētu noderēt atskaitēm, pilnīgi nepieņemams tipiskai datu ievadei/attēlošanai.
• LEADING (table) – ja ir pilnīga pārliecība, ka vaicājuma izpilde jāsāk ar konkrētu tabulu un statistiku rēķinašana tabulām un indeksiem nedod vēlamo rezultātu.
• Speciālos gadījumos, ja ir jāveic liela datu pievienošana kādā tabulā, tad var izmantot APPEND – paturot prātā, ka šīs operācijas rezultātā izveidotos datus nav iespējams atjaunot bez pilnas rezerves kopijas, kā arī, ka pēc šis operācijas ir jābeidz transakcija (COMMIT, ROLLBACK), ja vēlas veikt kādas darbības ar izmainīto tabulu.

10.3. Jebkuru citu hintu vajadzētu lietot tad un tikai tad, ja tā lietotājs pilnībā apzinās šī hinta sekas, piemēram, INDEX, INDEX_ASC vai INDEX_FFS – ir pilnīga pārliecība, ka indekss vienmēr būs tāds pats kā sākumā (saturēs tās pašas kolonas), indekss netiks nomests (drop), indekss netiks pārsaukts, šis indekss jebkuros apstākļos būs patiesi īstais un vienīgais, kas derēs vislabāk, lai iegūtu nepieciešamo rezultātu.

10.4. RULE hintu vajadzētu lietot tad un tikai tad, ja cilvēks zin, saprot un spēj izskaidrot visus RBO likumus, kas to nosaka (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/rbo.htm#38864 un http://www.oreilly.de/catalog/orsqltunpr/chapter/).

Turpmākā lasāmviela:

• Oracle Database Performance Tuning Guide 10g Release 2 (10.2)
• Oracle SQL Tuning Guide
• Patērēta laika mērīšana Oraclē
• Autotrace