Utilizarea excesivă a clauzei Oracle With? (Administrarea bazelor de date, Oracle, Cele Mai Bune Practici)

Cimplicity a intrebat.

Scriu multe interogări de raportare pentru actualul meu angajator, utilizând clauza With din Oracle pentru a-mi permite să creez pași simpli, fiecare dintre aceștia fiind o transformare orientată pe date, care se bazează unul pe celălalt pentru a executa o sarcină complexă.

Mi s-a atras atenția astăzi că utilizarea excesivă a clauzei With ar putea avea efecte secundare negative asupra resurselor serverului Oracle.

Poate cineva să explice de ce utilizarea excesivă a clauzei oracle with ar putea cauza blocarea unui server? Sau să-mi indice niște articole unde pot cerceta cazurile de utilizare corespunzătoare? Am început să folosesc foarte mult clauza With pentru a adăuga structură codului meu și pentru a-l face mai ușor de înțeles, sper că, cu ajutorul unor răspunsuri informative de aici, voi putea continua să o folosesc eficient.

Vă mulțumesc!

Comentarii

  • Aveți grijă cu interpretarea dumneavoastră. Efecte secundare negative asupra resurselor nu echivalează neapărat cu provoacă blocarea unui server. –  > Por Leigh Riffel.
  • Pentru a fi mai precis, persoana care mi-a spus acest lucru era un dezvoltator SQL certificat OCP cu un master în informatică. El lucrează la un spital mare din sudul Californiei. El a spus că serverul lor principal de raportare s-a blocat din cauza utilizării excesive a clauzei with de către dezvoltatorii de rapoarte care consumau resursele serverului. Ceea ce a generat această întrebare. El a remarcat că DBA-ul spitalului a restricționat de atunci utilizarea clauzei with de către dezvoltatorii de rapoarte. –  > Por Cimplicity.
  • Ați întrebat dacă au încercat aceeași interogare prin mutarea clauzei WITH clauzele în linie ca subinterogări pentru a dovedi că WITH sintaxa a fost cauza problemei? Dacă ar putea dovedi că problema provine de la clauză, atunci ar trebui să solicite Oracle să creeze un bug pentru aceasta. Dacă problema este mai degrabă o interogare cu costuri extrem de ridicate, atunci tot nu ar trebui să se blocheze, dar este de înțeles că ar avea un consum anormal de mare de resurse. –  > Por Leigh Riffel.
  • Sunt de acord cu Rob Farley (+1). Restricționarea WITH a fost probabil benefică, deoarece i-a determinat pe dezvoltatori să se gândească mai mult la problema în ansamblu și, prin urmare, să elimine necesitatea unor interogări secundare. –  > Por Leigh Riffel.
  • Ca o notă, cele mai mari probleme de performanță pe care le-am văzut din cauza gardurilor de optimizare în PostgreSQL au implicat grupuri de îmbinări imbricate fără subinterogări. Am văzut cum o interogare a scăzut de la 3 ore la 7 secunde după eliminarea unora dintre acestea. Vizualizările în linie/CTE sunt un exemplu de lucru care poate cauza acest lucru, dar nu este singurul. –  > Por Chris Travers.
5 răspunsuri
Rob Farley

Amintiți-vă că clauza WITH este utilizată pentru a denumi subinterogări, iar logica subinterogărilor poate fi oarecum abuzată. Cheltuielile generale pentru WITH nu ar trebui să ajungă să fie mult mai mari decât utilizarea unei subinterogări obișnuite, dar, totuși, nu încercați să aveți o cantitate ridicolă de anvelopare în desfășurare. Atâta timp cât aveți o imagine bună a profunzimii pe care o acordați lucrurilor, ar trebui să vă descurcați.

Chris Travers

Pentru început, să remarcăm faptul că nu cunosc toate elementele interne ale implementării Oracle, dar cunosc destul de bine declarațiile WITH.

Un lucru de reținut este că declarațiile WITH returnează seturi de rezultate care sunt menite să rămână stabile pe parcursul unei interogări. Cu alte cuvinte, din punct de vedere logic, acestea creează un fel de relații sintetice la care puteți face referire în altă parte în interogare. Înțeleg că Oracle încearcă să le plieze ca subinterogări, dar pot exista cazuri în care acest lucru nu funcționează corect. În aceste cazuri, optimizarea execuției codului nu va fi posibilă dincolo de granița declarației WITH. Există și alte cazuri în care am văzut că optimizarea execuției este întreruptă în PostgreSQL din motive matematice valabile și aș presupune că Oracle este la fel, astfel încât declarațiile WITH nu sunt unice, iar problema are întotdeauna legătură cu stabilitatea rezultatelor într-o parte a interogării.

Din acest motiv, chiar dacă Oracle este mai bun decât PostgreSQL la planificarea WITH-urilor, m-aș aștepta să existe și acolo probleme.

Acest lucru nu ar trebui să provoace blocarea serverului, dar ar putea însemna o utilizare mai mare a memoriei, o utilizare mai mare a procesorului și, în general, planuri de interogare mai puțin eficiente. Așadar, rețineți că declarațiile WITH pot cauza probleme de performanță. Păstrați-le acolo unde au sens. Oracle are și o mulțime de alte modalități de codificare a transformărilor de date, dar fără exemple concrete, este greu de știut ce să recomandăm.

APC

Oracle numește clauza WITH „sub-query factoring”. Principala sa utilizare este de a îmbunătăți performanța interogărilor care utilizează aceeași subinterogare de mai multe ori.

O putem folosi și pentru a face codul nostru mai ușor de înțeles, dar simplificarea excesivă poate cauza performanțe slabe. Ar fi mai ușor să înțelegem scenariul dvs. dacă ați posta niște cod, dar se pare că descrieți o abordare procedurală. Acest lucru miroase a RBAR și este posibil să aibă performanțe mai slabe decât o abordare bazată pe seturi.

Folosirea multor clauze WITH nu ar trebui să îngenuncheze un server de baze de date. Cu toate acestea, va avea ca rezultat probabil o recuperare inutilă a datelor și un consum suplimentar de procesor. Acest lucru va solicita mai mult baza de date decât logica echivalentă implementată cu JOIN-uri decente.

Comentarii

  • De acord. Este important să se utilizeze interogări pentru a defini ce informațiile pe care doriți să le primiți, nu cum doriți să fie returnate. –  > Por Chris Travers.
catalina

Nu sunt sigur că declarația WITH poate cauza probleme de performanță, dar cu siguranță poate cauza probleme de validare.

Am avut un caz (în Oracle) în care compilarea unui pachet care citește o vizualizare cu „with option” eșuează cu „ERROR at line 1:ORA-00600: internal error code, arguments: [kqludp2], [], [], [], [], [], [], [], [],[]’ eroare.

Se compilează bine după ce vizualizarea este recreată, iar când vizualizarea este rescrisă definitiv pentru a fi fără „with option”, pachetul a început să se comporte bine.Așadar, eu personal încerc să evit „with option”, până când înțeleg care a fost problema aici.

Comentarii

  • Ați depus un raport de eroare la Oracle? Ce au spus despre acest lucru? –  > Por Mat.
  • nu, nu încă… Voi scrie aici când/ dacă voi găsi o explicație –  > Por catalina.
user3807115

Clauza With ar trebui să fie utilizată în general dacă setul de date rezultat este sub 999 de rânduri. Pe baza unor factori nedocumentați, Oracle decide dacă dorește să stocheze setul de date în memorie sau într-un GTT gestionat de Oracle. Există o modalitate nedocumentată de a forța ORacle să stocheze setul de rezultate într-un GTT definit de Oracle.Setul de rezultate creat dacă este stocat în memoria de execuție poate deveni un consumator de resurse și penalizează resursele din PGA ȘI UGA. Am văzut situații în care utilizarea memoriei PGA și UGA a unei sesiuni a crescut cu 400% cu un With() executat, iar rezerva așteptată a crescut cu 1800%.Dacă setul de rezultate este stocat într-un GTT, necesarul de resurse nu ar mai fi atât de mare. 2c