db.dobo.sk

Explain sa používa ako prvá inšpekcia dotazu – ukáže, aký exekčný plán vymyslí CBO, aké indexy budú použité a aké sú náklady (memory, I/O).

V zásade sa dá používať 2 spôsobmi: samotný EXPLAIN a AUTOTRACE (v sqlplus).

1. EXPLAIN

Syntax – EXPLAIN PLAN FOR dotaz….

Samotný plán sa nevypisuje, ale ukladá do tabuľky PLAN_TABLE (pod SYSom, ale od 11g sú automaticky vytvárané public synonyms). Je možné sa dotazovať tabuľky priamo, na výpis plánu, ale nikto to nepoužíva. Je pre to vhodnejší predpripravený package dbms_xplan a jeho metoda display:

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Samozrejme, administratívni klienti ako SQL Developer a Toad ai. majú na to klikacie záložky, kde sa explain objaví. Detailnejší popis package dbms_xplan je tu.

2. AUTOTRACE

Utilitka v sqlplus, ktorá vracia explain dotazu. Nutné zapnúť (ON/OFF) a zvoliť mód, v ktorom to bude pracovať:

• explain – spustí dotaz a zobrazí aj jeho exekučný plán
• statistics – spustí dotaz a zobrazí štatistiku využitia zdrojov
• traceonly – nespustí dotaz, len ukáže explain

SQL> SET AUTOTRACE ON TRACEONLY EXPLAIN – ukáže exekučný plán, ale dotaz samotný nespustí. Pekná tabuľka tu na Oracle wiki.

3. Jak interpretovať exekučný plán a štatistiku

Jak z toho vytiahnuť nejaké info? Príklad – časť mojej fotodatabázy.

Tabuľka fotiek, DDL je i s definíciou šématu, constraintov a indexov (mimochodom, je tu vidieť nesmierne chujovú fičúru Oraclu, konvertovať INTEGER na NUMBER pri exporte z Data Modeleru do SQL Developeru):

———————————————————–

CREATE TABLE “DOBO”.”TAB_FOTO”
( “ID_PHOTO” NUMBER(*,0),
“DESCRIPTION_PHOTO” VARCHAR2(50 CHAR)
);
CREATE UNIQUE INDEX “DOBO”.”PK_TAB_PHOTO” ON “DOBO”.”TAB_FOTO” (“ID_PHOTO”);
ALTER TABLE “DOBO”.”TAB_FOTO” ADD CONSTRAINT “PK_TAB_PHOTO” PRIMARY KEY (“ID_PHOTO”);

—————————————————————————————————————————————-

Tabuľka exifovských dát, DDL tiež so šématom, constraintami, indexmi:

————————————————————

CREATE TABLE “DOBO”.”TAB_EXIF_DATA”
( “ID_PHOTO” NUMBER(*,0),
“EXIF_MANUFACTURER” CHAR(12 BYTE),
“EXIF_VERSION” FLOAT(126),
“TAB_PHOTO_ID_PHOTO” NUMBER(*,0)
);
CREATE UNIQUE INDEX “DOBO”.”IDX_ID_FOTO” ON “DOBO”.”TAB_EXIF_DATA” (“TAB_PHOTO_ID_PHOTO”);
CREATE UNIQUE INDEX “DOBO”.”PK_TAB_EXIF” ON “DOBO”.”TAB_EXIF_DATA” (“ID_PHOTO”);
ALTER TABLE “DOBO”.”TAB_EXIF_DATA” ADD CONSTRAINT “PK_TAB_EXIF” PRIMARY KEY (“ID_PHOTO”);
ALTER TABLE “DOBO”.”TAB_EXIF_DATA” MODIFY (“TAB_PHOTO_ID_PHOTO” NOT NULL ENABLE);
ALTER TABLE “DOBO”.”TAB_EXIF_DATA” MODIFY (“ID_PHOTO” NOT NULL ENABLE);
ALTER TABLE “DOBO”.”TAB_EXIF_DATA” ADD CONSTRAINT “FK_TAB_EXIF_REF_TAB_PHOTO” FOREIGN KEY (“TAB_PHOTO_ID_PHOTO”)
REFERENCES “DOBO”.”TAB_FOTO” (“ID_PHOTO”) ON DELETE CASCADE ENABLE;

—————————————————————————————————————————

Tak, naplnil som to datami a dotaz so subquery:

SELECT id_photo, description_photo FROM tab_foto
WHERE id_photo IN
(SELECT id_photo FROM tab_exif_data
WHERE exif_manufacturer = ‘Nikon’ AND exif_version > 1)
;

Tak, EXPLAIN tohoto dotazu vypadá takto:

Parser tohoto plánu postupuje tak, že:

• ako prvé spracuje listy stromu  v poradí za sebou (t.j. predikáty EXIF_VERSION>1 a potom EXIF_MANUFACTURER=’Nikon’)
• potom loopy “zvnútra” (zanorený NESTED LOOP)
• zopakuje postup pre druhú tabuľku
• a potom spracuje tú “vonkajšiu” NESTED LOOP

V tabuľke sú jasné kardinality vyfiltrovaných hodnôt a náročnosť operácií (costy). Jediné skutočné náklady sú tie nested loops. Pre exifovskú tabuľku by to chcelo indexy, prevádza sa fullscan.