PL/SQL programátor už NEbloguje – vývoj aplikací pod databází Oracle

Kolega si kdys postěžoval, že blog moc často neaktualizuji a má pravdu Nicméně dneska si ten post doslova vynutil, když mi potrápil s jedním selectem. Prvně bych chtěl všem připomenout, že to v Oracle funguje (alespon v zapatchované 11.2g) tak, že pokud Vám select vrací něco, co vypadá jako nesmysl – máte to špatně vy, ne Oracle. V mé první práci mě tohle myšlení naučil kolega, který nebyl ochoten přemýšlet o bugu Oracle dříve než za několik hodin doumání nad selectem. A já jsem tohle posunul ještě dál – tak 14 dní. Pokud jste daným selectem neztrávili alespoň 14 dní, pak není racionální přemýšlet nad tím, že je to bug Oracle. Za svůj život jsem potkal přesně 3 selecty, které vrátily špatný výsledek a byl to bug (hashjoin u subpartitions , left join na dual a to poslední si nepamauji – všechno již dávno fixnuto Oraclem). Zatímco já jsem na svůj select koukal asitak přesně bžilionkrát s tím, že “to není možné” a vždycky jsem končil přiznáním vlastní chyby a chutí ukousknout si vlastní ucho, když jsem zjistil, že to celou dobu přehlížím..

Jedinou vyjímkou, kdy bych přemýšlel nad chybou Oracle jsou ještě chvíle, kdy jste si hráli s datovým slovníkem (updaty pod sysem apod.) či je to chyba, kterou by mohlo vyvolat poškození datového bloku či indexu/objektu na mediu. Ale to je jen tak mé doporučení.

A nyní čím mi potrápil kolega (vypadalo nechtěně ačkoliv v průběhu zjišťování, v čem je zakopaná ponorka jsem ho podezíral se spousty věcí včetně VPD policy). Nejprve když mi popisoval svůj problém mluvil o constrainech a o tom, že mu “nefunguje cizí klíč”, což neznělo moc zajímavě, upřímně Do chvíle, než mi ukázal výsledky těchto dvou selectů:

Jiný výsledek

Všimněte, že výsledek je pokaždý jiný (počet řádků – sloupce jsou vždy z jiné tabulky) ačkoliv jediné co jsem změnil jsou sloupce v projection – join a predikát je naprosto stejný. Takže kde, že je ta zakopaná ponorka? Většinou v takovýhle případech zkouším exekuční plány – minimálně pro svou představu a oživení nápadů:

(explain plan)

Tohle píšu doma, takže to nemá naprosto stejný exekuční plán jako to mělo u nás v práci (speciálně to dělám na 12.1c tudíš tam mám navíc krok COLLECTORU a mám naprosto jiné tabulky), nicméně jako v práci to není ničím zajímavým, zkusme to pro b.*:

Tohle již vypadá zajímavěji  – mám tam join na dvou tabulkách, ale exekuční plán jde jen do jedné tabulky. Krásná síla Oracle, který zjistí, že join na tabulku A vůbec přístup do ní není třeba. Důvodem je FK constrain. Ten zaručuje, že join by zafungoval všude, zároveň na a.id je primární klíč, který zamezuje “pronásobení řádků”.  Takže nakonec po optimaizaci zbyde jen čtení z tabulky na predikát dsk=1 a join Oracle ztransformoval na  b.album_id is not null.

Nicméně to je “co je v exekučním plánu” a úplně to nenasvěčuje, proč dva selecty vrací jiné výsledky. Takže jsem si řekl, co to udělá, když Oracle donutím si tu tabulku vzít a fyzicky je na sebe najoinovat – zkusil jsem hinty na full, use_hash. – a odnáším si z toho ponaučení: tohle se ohintovat nedá, jakmile to Oracle vykydlí hned na pravidlech, neni žádná šance na to šahat hintem. Což je věc nad kterou jsem nikdy nepřemýšlel – většinou člověk hintuje, to co drhne v exekučním plánu, nikoliv takhle od pohledu do selectu. Každopádně nejde. Co mi ale napadlo až doma je vypnout tuhle featuru kydlení pomocí hintu OPT_PARAM. Což má nevýhodu v tom, že já vlastně ani nevím, co přesně vypnout – je to nějaký ze bžilonu zkrytých parametrů. Takže jsem nahodil brutálnější hint OPTIMALIZER_FEATURE_ENABLE, který umožní zadat pouze verzi A tak se stačí jen modlit, aby to nebylo hned od první verze, ale nebylo:

Fajn zanfungovalo – hned na poprvé Taky jsem teda zbaběle střelil nízkou verzi Oracle. Nicméně případně hrubou silou by šlo zjistit od kdy to začne zase vracet dva řádky a pak případně udělat rozdíl hidden parametrů a tipnout si, co přesně kde je třeba vypnout. Ale to bylo jen tak pro zajímavost.

Každopádně to visí a padá na constrainu, mrknu jsem co kolega psal ve scriptech a s úsměvěm jsem na tváři objevil slovo “novalidate”. Řikám si ha, má tam bordel v datech nedodržuje FK/špinavá data a přidal constrain novalidate, takže zvalidovat:

ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;
table TRACK altered

Po té, co mi tenhle příkaz nevyfuckoval, mi to začalo postupně kazit pátek Každopádně po delší odmlce jsme přišli, na to, že je problém s attributem RELY na constrainu. A to je můj druhý nový poznatek pro dnešek – pokud je na constrainu RELY, tak vás následné VALIDATE nevyhodí a normálně proběhne a co horší, proběhne pak i zpětné NORELY, jinými slovy, udělá to tahle kombinace:

ALTER TABLE track MODIFY CONSTRAINT fk_track RELY;
ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;
ALTER TABLE track MODIFY CONSTRAINT fk_track NORELY;


Pekelné Takže je třebas to zpravit:

ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE NOVALIDATE;
ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;


SQL Error: ORA-02298: není možno zkontrolovat platnost (AZOR6.FK_TRACK) - nebyly nalezeny nadřízené klíče
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious

A od téhle chvíle již to není vůbec zajímavé, v  tabulce je nějaký bordel, který je třebas promazat nebo se rozloučit sconstrainem. Takže asi tak, hébičkám zdar a pěkný víkend.