Cost Based Optimizer vs. nedokumentovani parametri
Monday, 01.09.2008 – DejanDok sam u više navrata čitao kako pojedini administratori Oracle baze podataka bacaju drvlje i kamenje na CBO (Cost Based Optimizer), mislio sam da oni nisu pravilno konfigurisali bazu ili da imaju neprikladan hardware (premalo RAM-a i slabi CPU resursi), jer zaboga – kako CBO, kojeg Oracle diže u nebesa i sa garancijom stoji iza svog tehnološkog čeda, može POGREŠNO da “naredi” bazi kako da pristupa podacima!? E, pa može! I nisu ti administratori bez razloga kukali na pogrešno funkcionisanje CBO-a …
To tvrdim iz ličnog iskustva, jer sam se iznenadio i iskreno začudio, kako CBO mijenja svoju ćud.
Instalirali mi zadnji 10.2.0.4 patch (zakrpa), u kojem su bili ispravljeni neki bugovi iz prethodnih verzija, ali se ispostavilo da i ova zakrpa ima rupu – i to više njih. Najočiglednije je bilo drastično povećanje vremena potrebnog za izvršavanje pojedinih aplikacija. Npr. jedan batch job, koji normalno bude gotov za pola sata, odjednom treba 6 sati da završi posao. Mnogi SQL upiti su u odnosu na prije trošili nenormalno puno CPU resursa ili su bjesomučno radili Full Table Scan umjesto Index Range Scan.
Provjerili smo hardware, da nije nešto crklo – sve je bilo u redu. System administratori nisu ništa mijenjali na nivou operativnog sistema. Jedino na šta smo sumnjali je bio taj zadnji patch 10.2.0.4, a kako se ispostavilo – tu je i ležao “problem”.
Nakon DETALJNOG čitanja pratećih informacija vezanih za 10.2.0.4 patch, pronašao sam par zanimljivih stvari vezanih za CBO i nedokumentovane parametre. Slijedi copy-paste sa objašnjenjem:
“To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter:
_optimizer_native_full_outer_join =force
You can set this parameter for the system or for a specific session.
Besides dramatically improving the performance of a full outer join, the new implementation fixes a variety of issues, for examples a variety of ORA-942 (table or view doesn’t exists) and ORA-4331 (unable to allocate string bytes of shared memory) errors.
This issue is tracked with Oracle bug 6322672.”
Aha – znači CBO sad drugačije posmatra full outer join. I ne samo to, nego i ostale kompleksne join kombinacije. Npr. za bug 7189447 pod naslovom “Wrong results when view merging applied to query” predlažu “zavaravanje” CBO-a korištenjem Optimizer Hinta “NO_MERGE“, kako bi Cost Based Optimizeru rekli da ne radi to onako kako on misli da treba, nego malo drugačije:
“Wrong results are possible when the connecting condition of a subquery refers to an aggregating column of a group-by view. The problem is that the column is not recognized as a correlating column and consequently the view is merged when it should not be.
Workaround:
Add a NO_MERGE hint to the subquery block to prevent it from being merged or set “_complex_view_merging”=FALSE
at the system level.”
I tako postavim ja te nedokumentovane parametre na nivou baze:
alter system set “_complex_view_merging”=FALSE scope=spfile sid=’*’;
alter system set “_optimizer_native_full_outer_join”=FORCE scope=spfile sid=’*’;
kad gle čuda!!!!
Onaj batch job odjednom ne treba više 6 sati, nego 20-30 minuta, a killer-SQL upiti se ponašaju kao mirne ovce dok pasu na livadi.
Dakle, postavlja se pitanje – da li uvijek slijepo vjerovati Cost Based Optimizeru ili ga pokušati “prevariti” nedokumentovanim parametrima?
Ko zna kako će se CBO ponašati nakon nove zakrpe i kako će ti parametri uticati na CBO…
3 Responses to “Cost Based Optimizer vs. nedokumentovani parametri”
Što se tiče nedokumentiranih parametara i oni nisu uvijek baš najbolje rješenje – specijalno ako ih mijenjamo bez konzultacija sa Oracle podrškom ili bez detaljne analize na Metalinku.
Po meni bi bilo najbolje koristiti hintove za operacije koje znamo da jako dugo traju i za koje znamo koji je najbolji execution plan.
Ako znam da će mi neki query brže odradit posao preko nekog indexa ili FTS-om, hintom ću natjerati Oracle da koristi putanju koju ja želim.
Znači moj odgovor na pitanje bi bio da ne treba slijepo vjerovati CBO-u nego koristiti hintove kad je to moguće – nedokumentirane parametre mijenjati kad baš nemam drugog izbora.
By Marko on Sep 1, 2008
Lako je igrati se hintovima, ako sve funkcionise kako treba, a ti želiš još za nijansu poboljšati performanse, ali kad neke stvari ne rade nikako (izbacuju ORA- greške) ili djelomično (traju nedopustivo dugo), onda moraš iskoristiti sva raspoloživa “sredstva” (čitaj nedokumentovane parametre, hintove i td.), kako bi obezbijedio funkcionalnost aplikacija i da ti šef ne skoči za vrat. 🙂
Meni sad preostaje da čekam da se ovi bugovi isprave i da pratim uticaj dotičnih parametara na rad baze…
By Dejan on Sep 1, 2008
Ako baš dođe do toga da moram dirati nedokumentirane parametre to bi uvijek radio sa povećanom dozom opreza isključivo nakon konzultacije sa Metalinkom ili Oracle supportom. Konkretno za tvoj slučaj vjerojatno bih isto postupio s obzirom da se ovdje radi o bug-u i na Metalinku postoji dokument o tome što napraviti, tj. kakve parametre promijeniti.
A što se tiče CBO-a, zna se ponekad dogodit da statistika nije redovito ažurirana zbog skupoće same operacije pa da CBO na temelju “krivih” podataka izračuna skuplji execution path, a i pored toga se zna dogodit da CBO na osnovu nečeg izabere skuplje rješenje. Iz tog razloga ne mislim da bi se trebalo slijepo vjerovati CBO-u.
Osobno bih monitorirao skupe operacije na bazi i uvjerio se da je CBO izabrao najbolje rješenje, a ako nije – Hintovi uskaču 🙂
By Marko on Sep 1, 2008