[oracle] Zoeken in database

[Thyzz]

Ik zit met het volgende probleem.
Ik heb een oracle 10g database die behoorlijk ver genormaliseerd is. Dat is in heel veel gevallen heel handig maar om op te selecteren, waarbij je van veel verschillende tabellen afhankelijk bent om de juiste data er weer uit te halen, een ramp. Zeker als je die tabellen ook weer een aantal keer met andere moet joinen om daar ook op te filteren. Met andere woorden we komen op monster query's uit die niet uitermate snel zijn.
Om dit op te lossen is er ooit een design beslissing gemaakt om de select (zoek) query op te bouwen uit verschillende brokken strings in PL/SQL en deze dan met een Execute immediate uit te voeren. Dit had als voordeel dat men dynamisch kon bepalen welke tabellen aan elkaar gejoined werden aan de hand van de ingevoerde zoekwaarden. Tot nu toe werkt dit en door af en toe aan deze constructie te sleutelen hebben we de resultaten altijd binnen toelaatbare tijd op het scherm weten te krijgen.

Echter moet deze data straks ontsloten worden via een website. Het idee is dat je begint met alle resultaten en dan steeds verder door filtert (waarbij je ook te zien krijgt hoeveel resultaten er nog over zijn) tot je uiteindelijke gewenste resultaat overblijft. Dat gaat met de huidige query niet lukken. Ten eerste is hij al te traag, maar door steeds meer criteria toe te voegen wordt hij alleen maar trager.

Nu hadden we zelf al een tweetal mogelijke oplossingen bedacht.
De eerste optie. Je doet eerst een globale zoek opdracht, stopt dit in een datastructure en gaat er dan in pl/sql overheen lopen om alle overige criteria er uit te filteren. Is een relatief snelle oplossing (we gebruiken het ergens anders ook al), maar niet als je 100-1000 concurrent gebruikers hebt, omdat je memory usage veel te groot wordt.

De andere optie die we hadden was. We joinen de altijd benodigde en meest gebruikte data aan elkaar en slaan dit plat op in een soort van cache tabel. Het probleem hierbij is dat die tabel exponentieel groeit bij elke join.
VB: De tabel waar we uit selecteren bestaat uit iets van 50.000 rijen. Gaan we echter wat belangrijke tabellen daar aan joinen dan zitten we na het toevoegen van 4 andere tabellen op een rijen count van 218miljoen en dan heb ik nog maar een klein gedeelte van de benodigde tabellen gejoined. De vraag is dan of we ons doel hiermee niet voorbij schieten. Helemaal omdat die cache ook nog eens relatief vaak/bijna realtime moet worden bijgewerkt.

Zijn hier standaard oplossingen voor? Of is dit een kwestie van trial en error tot je een gegevens set hebt die niet te groot is, maar wel de meest belangrijke informatie bevat waar je achteraf nog op gaat filteren?

[Confusion]

Crash_neo schreef op woensdag 12 september 2007 @ 08:35:
Met andere woorden we komen op monster query's uit die niet uitermate snel zijn.

Wat noem je 'monster'? Volgens mij zit er een minimaal verschil tussen de tijd die het kost om 25 tables te joinen in een genormaliseerde database versus 5 in een gedenormaliseerde versie van die database. Het probleem zit dan ergens anders.

[Exocet]

Ik weeet niet hoe actueel de gegevens moeten zijn uit je dataset, maar anders zou ik toch aan materialized views gaan denken. Hiermee kun je als het ware een resultaatset opslaan, die je op gezette tijden update.

[reskobon]

Materialized views hebben ook genoeg nadelen. Zo ben je nooit real-time up to date en bij resultaten van meer dan 200 miljoen rijen is hij ook wel een tijdje bezig met verversen.

Genormaliseerd of niet, een grote dataset ophalen met een query die bijv 10 joins en nog wat subqueries bevat is niet snel. Ga er maar van uit dat dat al snel 3 seconden duurt. Dat is in dit geval niet werkbaar.

[Janoz]

Ikzelf zou eerst eens gaan kijken waar de knelpunten in die queries zitten. Een kwestie van profilen. Misschien kun je, door het toevoegen van een paar key's en het aanpassen van de join volgorde de efficientie van de query een stuk vergroten.

Als het puur is dat je het te veel werk vindt om die lange queries in te tikken, dan zou je een paar views kunnen maken van gejoinde tabellen.

[Thyzz]

Het probleem is niet het tikken van die lange query's, het probleem zit hem in de onvoorspelbaarheid van de uit te voeren query. Door de onvoorspelbaarheid van de combinatie van ingevoerde zoekcriteria.

Simple sql voorbeeld van hoe het er nu uit ziet:

SQL:

IF variabele1 IS NOT NULL
THEN
  m_join := m_join || ' join (select * from TABELY where column = ' || variable1|| ') y on ( y.id = x.y_id)';
END IF;

IF variable2 IS NOT NULL
THEN
  m_where := m_where || 'AND (x.colomz = '|| variable2 ||' OR x.colom in (Select z.waarde from TABELZ z where z.colomz <> waardez)';

etc...

Deze code wordt aan de 'simpele' query toegevoegd

SQL:

m_query := 'select * from TABELX x ' ||
             m_join ||
             ' where colomx <> waarde ' || m_where;

daarna wordt er een execute imidiate aangeroepen.

SQL:

OPEN m_cursor FOR
(
  m_query
);
FETCH m_cursor BULK collect INTO m_collection;
FOR i in 1 ..m_collection.COUNT
LOOP
  nog een heleboel controles die we vanwege de complexiteit niet in een query konden zetten zonder 
  dat het allemaal veel te traag werd.

Dit is dus eigenlijk niet te onderhouden. Maar omdat we dus voor het zoeken afhankelijk zijn van veel van dit soort condities is het toen der tijd zo opgelost.

Daarnaast komen resultaten er voor een swing client er op deze manier wel redelijk snel uit, 2-5sec afhankelijk van de opgegeven parameters
Maar voor een webbased oplossing is dit niet snel genoeg. Zeker omdat we verwachten dat het aantal concurrent users met een factor x zal stijgen.
We zijn dus op zoek naar een manier om dit anders te doen.

[Thyzz]

Even een kick. Ik had eigenlijk verwacht dat dit een vaak voorkomend probleem is binnen database land. Maar ook op google kan ik hier weinig over vinden. Al komt dat misschien ook omdat ik er geen goede term aan kan geven. Iets als 'Conditional Select' ofzo geeft niet de resultaten die ik verwacht.

[Verwijderd]

Mijn eerste idee is ook Material Views, maar zoals reeds aangehaald in een real time omgeving misschien te traag.
Het tweede waar ik aan dacht zijn indexen, Staan er voldoende indexen op de verschillende tabellen. Dit kan je nagaan door je opgebouwde select statements te analyseren met de EXPLAIN PLAN functie.
Ten derde moet je misschien je queries herschijven.

where columnX <> waardeY

is een criteria dat zeker geen index gaat gebruiken, je zal je conditie anders moeten aanbrengen.

vb

where GEFACTUREERD <> 'Y'

wordt

where GEFACTUREERD = 'N'

of

where STATUS <> 'CLOSED'

wordt

where STATUS in ('OPEN', 'ASSIGNED', 'REJECTED', '....')

[Sepio]

Een tabel van 50.000 rijen valt nog wel mee om te doorzoeken. De joins die je maakt maken er een probleem van.

De strategie die ik meestal hanteer is de volgende:

• Blijf in SQL werken tenzij het echt niet anders kan en ga dan pas naar PLSQL
• Zorg ervoor dat je je hoofdtabel eerst beperkt qua records en voer daarna pas de join uit.
• Misschien is DBMS_SQL een optie voor jullie. Hier kun je dynamisch sql statements mee opbouwen.

SQL:

--Normale join
SELECT *
FROM TABEL_A A, TABEL_B B
WHERE A.ID = B.ID 
AND A.STATUS = 'NIEUW'

SQL:

--Eerst beperken dan pas joinen.
SELECT * 
FROM TABEL_B  B, (
    SELECT *
    FROM TABEL_A
    WHERE STATUS = 'NIEUW'
) A
WHERE A.ID = B.ID

[ Voor 3% gewijzigd door Sepio op 12-10-2007 13:30 ]

[Thyzz]

Sepio schreef op maandag 08 oktober 2007 @ 13:27:
Een tabel van 50.000 rijen valt nog wel mee om te doorzoeken. De joins die je maakt maken er een probleem van.

De strategie die ik meestal hanteer is de volgende:

• Blijf in SQL werken tenzij het echt niet anders kan en ga dan pas naar PLSQL
• Zorg ervoor dat je je hoofdtabel eerst beperkt qua records en voer daarna pas de join uit.
• Misschien is DBMS_SQL een optie voor jullie. Hier kun je dynamisch sql statements mee opbouwen.

--Normale join
SELECT *
FROM TABEL_A A, TABEL_B B
WHERE A.ID = B.ID
AND A.STATUS = 'NIEUW'

--Eerst beperken dan pas joinen.
SELECT *
FROM TABEL_B B, (
SELECT *
FROM TABEL_A
WHERE STATUS = 'NIEUW'
) A
WHERE A.ID = B.ID

We zitten eigenlijk wel tegen het uiterste van de query optimalisatie aan. De queries zijn al tot in den treure getraced en geoptimaliseerd, indexen zijn allemaal gezet, er is naar partitionering etc gekeken. Daarnaast gebruiken we al de dbms_sql functionaliteit

Het ging mij nu meer om de opbouw van een dergelijke zoek funtionaliteit, waarbij je dus een behoorlijke hoeveelheid optionele 'zoek' velden hebt, die er dan voor zorgen dat er andere tabelen aan gejoind moeten worden om beperkingen te leggen.

[Sepio]

Dit komt in feite neer op een soort datawarehouse problematiek. Hier slaan ze alle gegevens plat en selecteren de gebruikers uit de platgeslagen tabellen. Misschien dat een aantal MATERIALIZED VIEWS iets kunnen uithalen.

[Thyzz]

Dat is inderdaad ook waar we nu naar zitten te kijken. Alleen omdat het wel om een systeem gaat waarbij realtime data erg relevant is, is dat niet echt makkelijk. Maar we zijn aan het kijken of we bijvoorbeeld data die niet heel erg real-time hoeft te zijn platter kunnen opslaan en dat dan te combineren met de data die wel real-time correct moet zijn.

[justmental]

Het dynamisch opbouwen van de query is in principe goed.
De performance problemen zullen een andere oorzaak hebben dan de joins, al zijn het er 50 dan moet het nog prima kunnen.
Heb je wel indexen op je join kolommen? En op de filter criteria? Zijn je statistics up to date? Voor alle kolommen? Met histogrammen waar nodig?
Of zit je te zoeken in grote char of lob kolommen naar woorden?

[Xiphalon]

kuch, daarnaast:

Oracle is niet een van de snelste databases met betrekking tot het parsen van de query en het maken van het execution plan. In een van mijn programma's maakte ik een query van pakweg 17000 karakters, welke ongeveer 30 sec nodig had om te parsen, 0,3 seconden om de data op te halen en 0,02 seconden om uitgevoerd te hebben.

Moraal van het verhaal:
Heb je (uberhaupt) al naar de execution plans gekeken, en al een keer een trace laten meelopen?

[T-8one]

Hoe ziet de data eruit die je op wilt halen?

Is die heel specifiek, het resultaat van je zoekopdracht is één enkele regel?
Of wil je bijna de helft van alle data terug krijgen?


Als je veel data (bijna alle) op wilt halen vraag ik mij af of een index nog wel zin heeft, er moet dan volgens mij (bijna) altijd een full table scan uitgevoerd worden. Misschien dat een bitmap index wel performance winst oplevert.

Wij zitten hier in een datawarehouse omgeving met een gelijkend probleem, enige voordeel is dat de data niet via het net beschikbaar hoeft te zijn.
Een van de oplossingen die wij hebben gekozen is een nieuwe table (vergelijkbaar met een materialized view) die dagelijks ververst word. In deze table staat een populatie die bij veel query's gebruikt wordt.

Als je veel data uit een table op wilt halen kun je dmb de optimizer hint /* FULL(table name)*/ ervoor kiezen om geforceerd een full table scan uit te voeren, dit gaf bij ons goede resultaten. Zonder deze hint zaten er nested loops in het explain plan.

[Thyzz]

@T-8one
Nee ik heb altijd maar een fractie van de resultaten uit de tabel. Maar nogmaals het probleem zit hem niet meer in de indexen en vergelijkbare dingen. We zijn al 3 jaar bezig de query zelf te tunen(ook om met nieuwe wensen om te kunnen gaan) dus daar valt echt bijna geen winst meer uit te halen.

Het ging mij meer om de vraag wat is een andere mogelijk oplossing is.

[JaQ]

Crash_neo schreef op dinsdag 06 november 2007 @ 21:54:
@T-8one
Nee ik heb altijd maar een fractie van de resultaten uit de tabel. Maar nogmaals het probleem zit hem niet meer in de indexen en vergelijkbare dingen. We zijn al 3 jaar bezig de query zelf te tunen(ook om met nieuwe wensen om te kunnen gaan) dus daar valt echt bijna geen winst meer uit te halen.

Het ging mij meer om de vraag wat is een andere mogelijk oplossing is.

Vraag eens aan je DBA of hij veel "hard parses" ziet in een awr rapportage. Dikke kans van wel. De redenen hiervoor zijn:
a. Het niet gebruiken van bind variabelen in je queries --> iedere query met een andere conditie (dus = 'Y' ipv ='N') ziet Oracle als nieuw --> hard parse --> tijdverlies (en laat die cursor_sharing parameter maar uit je hoofd)
b. Het gebruik van execute immediate --> automatisch executieplan opnieuw bepalen = enorm tijdverlies.

Dit soort oplossingen in PL/SQL zijn niet geschikt om een flink performende applicatie te maken, enkel voor kleinschalige oplossingen. Wellicht valt er ook nog flink wat te winnen door een goede DBA die een flinke dosis SQL-tuningskennis heeft binnen te halen.

Mocht je zelf flink in deze materie willen duiken, raad ik je aan om Cost Based Fundamentals van Jonathan Lewis en Practical Oracle design (of zoiets) van T. Kyte te gaan bestuderen (eventueel ook nog dat SQL boek van Lex de Haan en je bent goed onderweg als ontwikkelaar ).

darkmage schreef op zaterdag 27 oktober 2007 @ 12:05:
Oracle is niet een van de snelste databases met betrekking tot het parsen van de query en het maken van het execution plan. In een van mijn programma's maakte ik een query van pakweg 17000 karakters, welke ongeveer 30 sec nodig had om te parsen, 0,3 seconden om de data op te halen en 0,02 seconden om uitgevoerd te hebben.

Moraal van het verhaal:
Heb je (uberhaupt) al naar de execution plans gekeken, en al een keer een trace laten meelopen?

Lang leve de soft parses Bij een gelijke query wordt de 2e keer (of Nde keer) enkel gekeken of er al een executieplan is --> die 30 secs worden zomaar een stuk minder...

[ Voor 19% gewijzigd door JaQ op 29-11-2007 00:53 ]

[JaQ]

T-8one schreef op maandag 05 november 2007 @ 15:37:
Hoe ziet de data eruit die je op wilt halen?

Is die heel specifiek, het resultaat van je zoekopdracht is één enkele regel?
Of wil je bijna de helft van alle data terug krijgen?

relevante vraag, maar enkel als je het executieplan van 1 query gaat bekijken. Wat je dus zegt is dat je het executieplan per querie moet gaan bekijken (net zoals Oracle data doet).

T-8one schreef op maandag 05 november 2007 @ 15:37:
Als je veel data (bijna alle) op wilt halen vraag ik mij af of een index nog wel zin heeft, er moet dan volgens mij (bijna) altijd een full table scan uitgevoerd worden.

Daar heb je de Cost Based Optimizer (CBO) dus voor, die maakt de keuze of je wel of geen index moet gebruiken. Soms heeft deze een hint nodig, maar in 90% van de gevallen kiest de CBO goed.

T-8one schreef op maandag 05 november 2007 @ 15:37:
Misschien dat een bitmap index wel performance winst oplevert.

Weet je wel wat een bitmap index doet? Als je een DBA voorstelt om zo'n ding op een OLTP systeem los te laten, zou ik zorgen dat je in ieder geval niet binnen het bereik van zijn armen zit

T-8one schreef op maandag 05 november 2007 @ 15:37:
Wij zitten hier in een datawarehouse omgeving met een gelijkend probleem, enige voordeel is dat de data niet via het net beschikbaar hoeft te zijn.
Een van de oplossingen die wij hebben gekozen is een nieuwe table (vergelijkbaar met een materialized view) die dagelijks ververst word. In deze table staat een populatie die bij veel query's gebruikt wordt.

Waarom heb je in dit geval niet gekozen voor een materialized view?

T-8one schreef op maandag 05 november 2007 @ 15:37:
Als je veel data uit een table op wilt halen kun je dmb de optimizer hint /* FULL(table name)*/ ervoor kiezen om geforceerd een full table scan uit te voeren, dit gaf bij ons goede resultaten. Zonder deze hint zaten er nested loops in het explain plan.

nested loops zijn niet per definitie slechter dan een full table scan. Het hangt maar net van de situatie af. Het grote probleem met sql tuningsvraagstukken zoals deze is dat er geen "silver bullets" zijn, enkel richtlijnen die voor een goed resultaat horen te zorgen (helaas zijn er vele uitzonderingen).

[MacWebber]

Explain plan is je vriend in deze. Dynamisch opbouwen van queries kan heel vervelend zijn, maar hoeft niet perse langzaam te worden. De twee voorbeelden die ik in het begin van het topic zie zijn overigens allebei vervelend omdat ze slecht te optimaliseren zijn.

Wat belangrijk is, is vooral kijken naar het eindresultaat van je query opbouw. Het werd al eerder gezegd, maar veel joins hoeven in principe geen trage queries op te leveren. Sterker nog, dit kan voor snelheid zorgen doordat de optimizer makkelijker een eliminerend pad kan vinden.

Kijk wel naar standaard dingen; geen IN gebruiken als je subquery een hoop resultaten oplevert (dan ben je in 95% van de gevallen beter af met een EXISTS). Geen SELECT * joinen om te zien of iets bestaat, etc.

Gebruik een tool waarmee je kunt zien wat je programmatuur uiteindelijk aan SQL oplevert en op Oracle loslaat. (Niet beredeneren wat de query zou moeten zijn, gewoon kijken naar wat het echt is).

Ik heb redelijke databases gezien en geoptimaliseerd en kom slechts zelden zaken tegen die Oracle niet kan oplossen, mits je het maar fatsoenlijk vraagt.

[DaCoTa]

JaQ schreef op donderdag 29 november 2007 @ 00:32:
[...]
Lang leve de soft parses Bij een gelijke query wordt de 2e keer (of Nde keer) enkel gekeken of er al een executieplan is --> die 30 secs worden zomaar een stuk minder...

Wel oppassen dat de statistics kloppen en je data niet evenredig vertegenwoordigd is. Het kan zijn dat met andere binds het plan helemaal niet meer zo efficient is.

[JaQ]

DaCoTa schreef op vrijdag 30 november 2007 @ 12:23:
[...]

Wel oppassen dat de statistics kloppen en je data niet evenredig vertegenwoordigd is. Het kan zijn dat met andere binds het plan helemaal niet meer zo efficient is.

Oracle 10g kan automatisch statistieken voor je bijhouden (er wordt o.a. gekeken naar hoeveelheid mutaties, etc. zie dbms_stats). Verder bestaat er iets als een histogram, wat er voor zorgt dat ook bij een onevenredige verdeling van data toch een juiste keuze (al dan niet de index gebruiken) genomen wordt.

Lang niet gek hoor, die CBO

_{damn... ik klink als een onwijze oracle fan-boy...}

[ Voor 4% gewijzigd door JaQ op 03-12-2007 23:03 ]