Turpinot tēmu par ātrdarbības meklējumumiem Oracle datubāzēs šoreiz paskatīsimies uz SQL*Plus rīka komandu autotrace. Tā jau dod vairāk informācijas nekā vienkārši patērētais laiks, ko var noskaidrot vairākos veidos.
Jāpiebilst gan, ka izpildes plānu un statistiskos rādītājus izmantojot autotrace ir iespējams dabūt tikai DML teikumiem – SELECT, INSERT, UPDATE, MERGE, DELETE.
Tātad par visu pēc kārtas:
Autotrace priekšnosacījumi
Lai varētu pilnvērtīgi izmantot šo SQL*Plus komandu, ir divi priekšnosacījumi:
-
Lietotājam jābūt iedotai PLUSTRACE lomai. Lomas izveides skripts atrodas $oracle_home\sqlplus\admin\plustrce.sql, kur $oracle_home ir direktorija, kur uzinstalēta Oracle datubāze.
-
Lietotājam jābūt pieejamai tabulai PLAN_TABLE. Tā var būt uzinstalēta kādā datubāzes lietotāja shēmā un iedotas tiesības visiem pārējiem lietotājiem, vai arī katra paša lietotāja shēmā, tas nav svarīgi. Tabulas izveides skripts atrodas $oracle_home\rdbms\admin\utlxplan.sql . Vajadzētu lietot tieši konkrētajai Oracle versijai paredzēto tabulu tāpēc, ka katrā nākošajā versijā parasti nāk klāt jaunas kolonas.
Autotrace iespējošana/atspējošana
-
SET AUTOTRACE OFF – autotrace atskaite netiek ģenerēta. Normālā situācija.
- SET AUTOTRACE ON EXPLAIN – autotrace atskaite parāda tikai izpildes plānu.
- SET AUTOTRACE ON STATISTICS – autotrace atskaite parāda tikai SQL teikuma izpildes statistiku.
- SET AUTOTRACE ON – autotrace atskaite attēlo gan izpildes plānu, gan SQL teikuma izpildes statistiku.
- SET AUTOTRACE TRACEONLY – autotrace atskaite attēlo gan izpildes plānu, gan SQL teikuma izpildes statistiku, bet netiek attēlots SELECT vaicājuma izpildes rezultāts. Ērti izmantot tad, ja vaicājuma izpildes rezultāts ir liels un mūs neinteresē, bet interesē tikai izpildes plāns un statistika.
- SET AUTOTRACE TRACEONLY EXPLAIN – autotrace atskaite attēlo izpildes plānu, bet netiek attēlots vaicājuma izpildes rezultāts. Ja tas ir SELECT teikums, tad šajā gadījumā patiesībā SQL SELECT teikums NETIEK izpildīts.
- SET AUTOTRACE TRACEONLY STATISTICS – autotrace atskaite attēlo SQL teikuma izpildes statistiku, bet netiek attēlots vaicājuma izpildes rezultāts.
Dažas lietas, ko vērts atcerēties:
-
INSERT, UPDATE, DELETE, MERGE teikumi tiek reāli izpildīti vienmēr, bet SELECT teikums patiesībā netiek izpildīts, ja ir SET AUTOTRACE TRACEONLY EXPLAIN. Šis variants reizēm ir noderīgs, ja select teikums izpildās stundu, bet mēs gribam redzēt tikai izpildes plānu. Kā arī reizēm rodas nesapratne – kāpēc, ja ir SET AUTOTRACE TRACEONLY EXPLAIN, tad select teikums izpildās zibenīgi, bet citādi iet stundu? Atbilde tātad – select pieprasījums patiesībā netiek izpildīts, tiek uzģenerēts tikai izpildes plāns.
-
Nelietot autotrace SYS lietotājam. Šis lietotājs ir īpašs un šim lietotājam autotrace atskaite nav izmantojama.
-
Autotraces rezultātā iegūtais SQL pieprasījuma izpildes plāns var nebūt īstais, kas patiesībā ir jūsu programmā. Vairumā gadījumu izpildes plāni sakrīt un ir vienādi, bet var būt gadījumi, kad tas tā nav, piemēram, ja atšķiras vides uzstādījumi sesijām (izdalītā atmiņa utml). Otrs iemesls, kad izpildes plāni var atšķirties ir tad, ja jūs lietojat BIND mainīgos un Oracle datubāze reālā vaicājuma izpildes plāna ģenerēšanas laikā pirmajā reizē skatās uz doto mainīgo vērtībām. Autotrace ģenerējot SQL vaicājuma izpildes plānu to nedara. Piemērs – jums ir tabula ar kolonu, kurā ir viena “A” vērtība un 1000 “B” vērtības. Ja atlasa ierakstu ar “A” vērtību, tad tabulu ir vērts lasīt pēc indeksa, ja atlasa ierakstus ar “B” vērtību, tad tabulu ir izdevīgāk lasīt visu neizmantojot indeksu. Šajā gadījumā autotrace var rādīt vienu izpildes plānu, bet reālais var būt atšķirīgs.
Izmantošanas piemērs
Izmantotā tabula ir šāda:
CREATE TABLE personas (
prs_personas_kods VARCHAR2(11) NOT NULL PRIMARY KEY,
prs_vards VARCHAR2(40),
prs_uzvards VARCHAR2(40));
INSERT INTO personas VALUES ('11111112345', 'JĀNIS', 'BĒRZIŅŠ');
INSERT INTO personas VALUES ('12121212345', 'PĒTERIS', 'SŪNIŅŠ');
INSERT INTO personas VALUES ('13131312345', 'RŪDIS', 'BĒRZIŅŠ');
COMMIT;
SQL> set autotrace on
SQL> SELECT * FROM personas WHERE prs_personas_kods = '11111112345';
PRS_PERSONA PRS_VARDS PRS_UZVARDS
----------- ---------------------------------------- --------------
11111112345 JĀNIS BĒRZIŅŠ
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1
Bytes=51)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PERSONAS' (TABLE)
(Cost=1 Card=1 Bytes=51)
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C00112708' (INDEX (UNIQUE))
(Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
459 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Tātad mēs šeit redzam izpildes plānu, kurā tiek parādīts:
- kādas darbības (SQL vaicājuma izpildes plāns) Oracle DB veic, lai iegūtu rezultātu,
- kāds ir izpildes plāna un tās soļu izmaksu novērtējums (Cost);
- kāds ir sagaidāmo ierakstu skaits ko Oracle DB cer iegūt katra soļa rezultātā (Card, no cardinality);
- kāds ir rezultāta apjoms baitos (Bytes).
Ja Cost, Crad un Bytes nav, tad tas nozīmē, ka jūs izmantojat RBO (rule based optimizer) un šeit ir rakstīts, kāpēc to nevajag darīt.
Par katru no statistikām vairāk nākošajā sadaļā.
Lai paskatītos, cik grūti ir iegūt visus mums pieejamos objektus no datu vārdnīcas, varam pašus rezultātus neskatīties, bet tikai statistiku.
SQL> set autotrace traceonly statistics SQL> select * from all_objects; 61295 rows selected. Statistics ------------------------------------------------------ 5467 recursive calls 0 db block gets 113664 consistent gets 0 physical reads 0 redo size 3032446 bytes sent via SQL*Net to client 45458 bytes received via SQL*Net from client 4088 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 61295 rows processed
Tātad kā redzam, ieraksti rādīti netika, bet statistikas atskaite ir redzama.
Statistiku nozīme
- recursive calls – rekursīvo izsaukumu skaits (SQL teikumu skaits), ko veic sistēmas vai lietotāja līmenī. Daži no izplatītākajiem iemesliem varētu būt lietotāja definēto funkciju izsaukumi, kas kaut ko dara datu bāzē un sistēmas izsaukumi, lai varētu izveidot SQL Pieprasījuma izpildes plānu.
- db block gets – bloka nolasīšana current mode. Pārsvarā tas parādās datu koriģēšanā, kad jāmaina tikai bloka pēdējā versija.
- consistent gets – bloka lasīšana no Oracle DB bufera keša read consistent mode. Pārvarā tas parādās vaicājumos un iekļauj arī bloka iegūšanu no UNDO segmentiem (iepriekšējām bloka versijām atmiņā).
- physical reads – bloka lasīšana no diska gan izmantojot tiešo lasīšanu, gan bufera kešu. Protams, arī šeit jāatceras, ka ne vienmēr tā patiesi ir lasīšana no diska – jo pastāv vēl gan operētājsistēmas kešs, gan iespējams arī katram diskam ir kaut kāds kešs. Bet no Oracle DB viedokļa tā ir lasīšana no diska.
- redo size – norāda cik daudz tiek ģenerēts REDO. Pārsvarā attiecas tikai uz datu koriģēšanas teikumiem.
- bytes sent through SQL*Net to client – no servera uz klienta pārsūtīto baitu skaits.
- bytes received via SQL*Net from client – no klienta uz servera pārsūtīto baitu skaits.
- SQL*Net roundtrips to/from client – SQL*Net ziņojumu skaits, kas sūtīts starp datu bāzi un klientu.
- sorts (memory) – veikto kārtošanu skaits atmiņā.
- sorts (disk) – kārtošanu skaits, kam ir bijusi nepieciešamība pēc pagaidu vietas uz diska.
- rows processed – atlasīto (select), pievienoto (insert, merge), koriģēto (update, merge), dzēsto (delete, merge), ierakstu skaits.
Kam pievērst uzmanību (nosacītā svarīguma dilšanas kārtībā):
- Pats svarīgākais ir censties samazināt consistent gets un db block gets. Jo tas nozīmē, ka jums mazāk datu būs jālasa no atmiņas, mazāk datu būs jāapstrādā, mazāk būs jāveic atmiņas struktūru skanēšana, kas protams ir ļoti ātra, bet tai pašā laikā prasa nozīmīgus CPU resursus. Nevajadzētu iedomāties, ka “ieraujot visu datu bāzi” atmiņā tiks atrisinātas visas ātrdarbības problēmas. Pie tam samazinot šo statistiku automātiski parasti samazinās arī physical reads. Consistent reads ir atkarīgs arī no tā kāds ir vienā reizē pārsūtīto ierakstu skaits starp datubāzi un klientu un līdz ar to pārsūtīto ziņojumu skaits (SQL*Net roundtrips to/from client) vienam un tam pašam SQL vaicājumam. Jo šis skaits ir lielāks, jo vienam un tam pašam SQL vaicājumam var būt vairāk consistent gets. Katrā vidē to nosaka savādāk, bet SQL*Plusā to nosaka izmantojot komandu SET ARRAYSIZE <skaitlis>.
- recursive calls visu laiku (ne tikai pirmajā izsaukuma reizē) saglabājas liels skaitlis (simti, tūkstoši). Tas var norādīt uz to, ka vai nu nepārtraukti SQL teikums tiek parsēts atkal un atkal (kas autotraces un vienkārša DML teikuma gadījumā ir neparasti), vai arī tiek izpildītas lietotāja definētas funkcijas, kas var būt paslēptas skatījumā un vispārīgā gadījumā var būt pamatīgs ātrdarbības grāvējs.
- Liels redo size. Jo lielāks redo size, jo vairāk datu Oracle ir spiesta rakstīt uz diska. Jāatceras, ka katrs indekss, kas datu modificēšanas laikā ir papildus jāuztur, palielina šo metriku.
- Liels physical reads. Jānoskaidro kāpēc tas tāds ir. Iespējams, ka tas ir neizbēgami – ja jums piemēram ir jānolasa liela tabula un tā ir jānolasa visa, lai iegūtu kādu atskaiti, tad parasti visefektīvākais veids ir to visu arī vienkārši nolasīt.
- Sorts – mērķis ir samazināt metriku sorts (disk), jo tas nozīmē, ka kārtošana nesatilpst atmiņā. Ir 2 iespējas kā to paveikt – palielināt atmiņu vai samazināt kārtošanu skaitu vispār 🙂
Turpmākā lasāmviela
-
Why You Should Focus on LIOs Instead of PIOs by Cary Millsap – apraksts par to, kāpēc loģiskās lasīšanas no atmiņas nav nemaz tik lētas, pieejams tikai reģistrētiem lietotājiem, bet reģistrēties var bez maksas.
Ierakstīja Gints Plivna 
Datubāzes 