SQL teikuma izpildes plāna iegūšana – Oracle, MySQL, SQL Server

SQL teikumu izpildes plāns (query execution plan) – kurš gan nopietni interesējoties par datubāzu izstrādi tādu terminu nav dzirdējis? Vaicājums lēni strādā – paskaties izpildes plānu! Tiesa gan vairumā gadījumu tiek pašas par sevi pieņemtas divas (varbūt pat trīs) lietas:

• persona zin, kā izpildes plānu dabūt;
• persona zin, ko iegūtais izpildes plāns nozīmē;
• persona zin, kā panākt, lai izpildes plāns būtu optimālāks nekā iegūtais vai arī saprot, ka nekas labāks nevar sanākt (neobligātais solis).

Šai rakstā apspriedīšu pirmo soli jau parasti izvēlētajām DBVS – Oracle, MySQL, SQL Server. Kā jau Jūs noteikti zinat visas DBVS ir atšķirīgas, attiecīgi atšķirīgas ir arī metodes, kā izpildes plānu iegūt. Tātad pēc kārtas.

Oracle

Oraclē ir vairākas metodes, kā iegūt SQL teikuma izpildes plānu, ar dažādu sarežģītību un ticamības pakāpi. Ja izmantojat rīku SQL*Plus, tad visvienkāršāk ir izmantot šī rīka komandu autotrace. Es šeit gari nekāvēšos pie šī rīka un komandas apraksta, jo tie abi ir augšminētajās saitēs, šeit tikai īss piemērs. Visiem turpmākajiem piemēriem tiks izmantotas tabulas no raksta par Dekarta reizinājumu.

Piemērs 1. Izpildes plāna iegūšana izmantojot autotrace.

SQL> set autot traceonly explain
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
  2  FROM personas
  3  INNER JOIN adreses ON prs_adr_id = adr_id
  4  /
Execution Plan
----------------------------------------------------------
Plan hash value: 4160699136
----------------------------------------------------------------
|Id|Operation          |Name    |Rows|Bytes|Cost(%CPU)|Time    |
----------------------------------------------------------------
| 0|SELECT STATEMENT   |        |  3 | 231 |   7  (15)|00:00:01|
|*1| HASH JOIN         |        |  3 | 231 |   7  (15)|00:00:01|
| 2|  TABLE ACCESS FULL|ADRESES |  3 | 105 |   3   (0)|00:00:01|
| 3|  TABLE ACCESS FULL|PERSONAS|  4 | 168 |   3   (0)|00:00:01|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("PRS_ADR_ID"="ADR_ID")
Note
-----
   - dynamic sampling used for this statement

Jāatgādina, ka autotrace mēdz melot – izpildes plāns ir tas ko Oracle domā, ka izpildīs zinot pašreizējo informāciju, aktuālajā izpildē situācija noteiktos apstākļos var mainīties. Tiesa gan meli ir relatīvi reti, bet ir.

Nākošā metode, kas ir sākot no 9 versijas, bet kopš 10 versijas tā jau ir vienkārši nenormāli ērta, ir izmantot pakotni dbms_xplan. Tātad, lai iegūtu izpildes plānu tabulā plan_table (noklusētā tabula), mēs varam izmantot komandu EXPLAIN PLAN, kas rezultātu ieraksta jau augšminētajā tabulā un tad no tās iegūt formatētā veidā izmantojot pakotnes dbms_xplan funkciju display. Arī šis veids mēdz melot, jo pēc būtības autotrace dara to pašu tikai klusiņām fonā.

Piemērs 2. Izpildes plāna saglabāšana tabulā plan_table un iegūšana no tās izmantojot dbms_xplan. Šis strādā jau no 9 versijas.

SQL> set autot off
SQL> EXPLAIN PLAN FOR
  2  SELECT prs_personas_kods, prs_vards, adr_pilseta
  3  FROM personas
  4  INNER JOIN adreses ON prs_adr_id = adr_id
  5  /
Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Plan hash value: 4160699136
----------------------------------------------------------------
|Id|Operation          |Name    |Rows|Bytes|Cost(%CPU)|Time    |
----------------------------------------------------------------
| 0|SELECT STATEMENT   |        |  3 | 231 |   7  (15)|00:00:01|
|*1| HASH JOIN         |        |  3 | 231 |   7  (15)|00:00:01|
| 2|  TABLE ACCESS FULL|ADRESES |  3 | 105 |   3   (0)|00:00:01|
| 3|  TABLE ACCESS FULL|PERSONAS|  4 | 168 |   3   (0)|00:00:01|
----------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("PRS_ADR_ID"="ADR_ID")
Note
-----
   - dynamic sampling used for this statement

Tad tagad par to nenormāli ērto piegājienu, lai iegūtu patieso izpildes plānu un piedevām vēl gan sagaidīto kardinalitāti, gan patieso – to, kas tika iegūta izpildes laikā. Un tātad jāizmanto dbms_xplan.display_cursor. Ar piemērā redzamajiem parametriem izpildes plāns tiek paņemts pēdējam izpildītajam SQL teikuma no datu vārdnīcas dinamiskajiem (v$) skatījumiem. SQL teikumā pierakstot hintu gather_plan_statistics, iegūstam arī patieso izpildes laika kardinalitāti un izpildes laiku katram plāna solim.

Piemērs 3. Pēdējā izpildītā SQL teikuma izpildes plāna iegūšana no sistēmas dinamiskajiem skatījumiem.

SQL> set pages 50000
SQL> set serveroutput off
SQL> SELECT /*+ gather_plan_statistics */
  2  prs_personas_kods, prs_vards, adr_pilseta
  3  FROM personas
  4  INNER JOIN adreses ON prs_adr_id = adr_id;
PRS_PERSONA PRS_VARDS      ADR_PILSETA
----------- -------------- ------------
23456789012 PĒTERIS        TALSI
34567890123 ANNA           TALSI
45678901234 KĀRLIS         TALSI
SQL> select * from
  2  table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID  6u4ahhavg90cy, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */
prs_personas_kods, prs_vards, adr_pilseta
FROM personas INNER JOIN
adreses ON prs_adr_id = adr_id
Plan hash value: 4160699136
----------------------------------------------------------
|Id|Operation         |Name    |E-Rows|A-Rows|   A-Time  |
----------------------------------------------------------
|*1|HASH JOIN         |        |    3 |    3 |00:00:00.01|
| 2| TABLE ACCESS FULL|ADRESES |    3 |    3 |00:00:00.01|
| 3| TABLE ACCESS FULL|PERSONAS|    4 |    4 |00:00:00.01|
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("PRS_ADR_ID"="ADR_ID")
Note
-----
   - dynamic sampling used for this statement
 

Augšminētajā  piemērā es esmu izvācis ārā dažas kolonas, jo tās diemžēl bojā izskatu visai vietnei :), bet interesantākās esmu atstājis – E-Rows ir paredzētais ierakstu skaits (estimated rows), bet A-Rows ir aktuālāis ierakstu skaits (actual rows). A-Time savukārt ir laiks, cik paterēts katram solim. Kā redzam šai gadījumā plānotais un reālais ierakstu skaits sakrīt, bet laiks ir 0.01 sekunde (tā kā norādītajam laikam ir noapaļošanas kļūdas, tad uz tik maziem lielumiem precizitāte, protams, klibo).

Vēl divas iespējas, lai iegūtu  reālos izpildes plānus citām sesijām un/vai vairākiem SQL teikumiem, ir izmantot vai nu to pašu dbms_xplan.display_cursor noskaidrojot sql_id vērtību, piemēram, no v$sql, vai arī palaist trasēšanu (trace), kas ģenerē trasēšanas datni uz servera, kurā var iegūt arī visus precīzos izpildes plānus u.c. informāciju.

Protams, ja izmantojat kādu no vizuālajiem rīkiem, tad lielākajā daļā no tiem ir iebūvēta plāna izpildes apskate. PL/SQL Developer tas tiek ņemts no plan_table (attiecīgi nav pilnīgas drošības par tā patiesumu) un izskatās šādi:

Piemērs 4. Vizuāls SQL teikuma izpildes plāns rīkā PL/SQL Developer.

Savukārt, ja gadās izmantot Enterprise Manager konsoli, tad tas ir reālais izpildes plāns un izskatās šādi:

Piemērs 5. Vizuāls SQL teikuma izpildes plāns Enterprise Manager Console.

MySQL

MySQLā ir komanda EXPLAIN, ko vienkārši piekabina sākumā SQL Select teikumam un iegūst izpildes plānu.

Piemērs 6. Izpildes plāna iegūšana MySQLā.

mysql> EXPLAIN
    -> SELECT prs_personas_kods, prs_vards, adr_pilseta
    -> FROM personas
    -> INNER JOIN adreses ON prs_adr_id = adr_id;
+--+-----------+--------+------+-------------+-------+-------+----+
|id|select_type|table   |type  |possible_keys|key    |key_len|rows|
+--+-----------+--------+------+-------------+-------+-------+----+
| 1|SIMPLE     |personas|ALL   |NULL         |NULL   |NULL   |   1|
| 1|SIMPLE     |adreses |eq_ref|PRIMARY      |PRIMARY|4      |   1|
+--+-----------+--------+------+-------------+-------+-------+----+
2 rows in set (0.00 sec)

Ja nu gadījumā  jums šāds izskats nepatīk, tad varat mēģināt izmantot rīku Maatkit, kas sola izpildes plānu vizualizēt koka formā, tiesa gan pats neesmu lietojis.

Līdzīgi kā Oraclei arī šeit EXPLAIN komanda var melot un tai ir arī citi šādi tādi mīnusi, kā rakstīts šai MySQL Performance Blog rakstā, tiesa gan izskatās, ka MySQL nav citu alternatīvu veidu, kā 100% droši iegūt faktisko izpildes plānu.

SQL Server 

SQL Server kā zināms izceļas ar savu “pogaino” interfeisu un tur iegūt izpildes plānu var ļoti vienkārši piespiežot pareizo podziņu 🙂 SQL Server Mangement Studio ir divas pogas 1) parādīt paredzamo izpildes plānu (Display Estimated Execution Plan) un 2) parādīt reālo izpildes plānu (Display Actual Execution Plan). Pirmā podziņa speciāli uzģenerē plānu, kas izskatās šādi.

Piemērs 7. Vizuāls paredzamais SQL teikuma izpildes plāns SQL Serverī.

Lai dabūtu reālo izpildes plānu, ir jānospiež podziņa mazliet pa labi un pēc SQL teikums jāizpilda, normālā gadījumā tas jau neatšķiras no paredzamā, tikai ar kursoru uzbraucot uz operācijas var iegūt papildus ziņas (piemēram, reālais ierakstu skaits Actual Number of Rows).

Piemērs 8. Vizuāls reālais SQL teikuma izpildes plāns SQL Serverī.

Izmantojot šādus SET teikumus, augstāk redzamās bildītes – izpildes plānus – var iegūt arī teksta formā:

• SET SHOWPLAN_ALL { ON | OFF } – attēlo paredzamo izpildes plānu.
• SET STATISTICS PROFILE { ON | OFF } – attēlo reālo izpildes plānu un reālo ierakstu skaitu katrā solī.

Ilustrācijai piemērs ar SET SHOWPLAN_ALL ON:

Piemērs 9. Paredzamais SQL teikuma izpildes plāns SQL Serverī.

StmtText                                                          
------------------------------------------------------------------
SELECT prs_personas_kods, prs_vards, adr_pilseta
FROM personas
INNER JOIN adreses ON prs_adr_id = adr_id                         
|-Nested Loops(Inner Join, OUTER REFERENCES:(personas.prs_adr_id))
  |-Clustered Index Scan(OBJECT:(personas.PK__personas__4374BBA0))
  |-Clustered Index Seek(OBJECT:(adreses.PK__adreses__418C732E),..

Uzrakstot SET STATISTICS PROFILE ON, iegūst sql teikuma izpildes rezultātu, to pašu, ko iepriekšējā piemērā un vēl kolonas Rows un Executes, kas norāda cik ieraksti atgriezti un cik reizes operācija izpildīta:

Piemērs 10. Reālais SQL teikuma izpildes plāns SQL Serverī.

Rows Executes StmtText                                       
---- -------- ------------------------------------------------
3    1        SELECT prs_personas_kods, prs_vards, adr_pilseta
              FROM personas
              INNER JOIN adreses ON prs_adr_id = adr_id
3    1        |--Nested Loops(Inner Join, OUTER REFERENCES
4    1          |--Clustered Index Scan(OBJECT...
3    4          |--Clustered Index Seek(OBJECT...

Tālākā lasāmviela

• Šis raksts jau bija gandrīz pabeigts pirms es ķēros pie savas Oracle prezentācijas par SQL teikuma izpildes plānu. Tajā neapšaubāmi ir vairāk informācijas kā šeit, bet, protams, bez īpašiem paskaidrojumiem;
• dbms_xplan 10g versijā – Jonathan Lewis raksts par dbms_xplan.