[SQL] Oracle SQL developer en VIEWs

[Martinusz]

Mijn vraag:
Ik draai regelmatig rapporten in Oracle SQL developer.
Ik merk op dat zodra er een VIEW (...._V table) in de SQL query zit, dat de performance echt dramatisch verslechterd. Lees: van 30 seconden naar 30 minuten bijvoorbeeld.
Ik begrijp dat VIEW meestal langzamer zijn dan de base tables, maar waarom is het verschil zo drastisch groot? Kan dat toch aan settings liggen?

Het rare is, is dat een collega dezelfde query met VIEW in een paar seconden kan draaien (zelfde software op zelfde moment op zelfde database). Andere collega heeft hetzelfde als ik, zodra er een VIEW is, wordt performance erg slecht.

Iemand wellicht tips om iets van settings te checken die dit kunnen veroorzaken? Of is het gewoon een feit dat VIEWs heel veel langzamer zijn? (waarom dan die ene collega geen last ervan?)
Zou het zelfs hardware gerelateerd kunnen zijn?

Relevante software en hardware die ik gebruik:
SQL Developer: version: 20.2.0.175 build: 175.1842

Processor: Intel Core i5-8350 CPU @ 1.7GHz 1.9GHz
RAM: 16 GB

Wat ik al gevonden of geprobeerd heb:
- checks met collega's gedaan
- SQL Developer ge-update naar laatste versie

[ Voor 4% gewijzigd door Martinusz op 10-12-2020 15:46 ]

[Down]

Wat staat er in het explain plan/execution plan?

[CurlyMo]

Dat hangt er heel erg vanaf wat je probeert te bereiken.

Neem deze view:

SQL:

create view foo_v as
select * from foo;

Je roept de view vervolgens aan als:

SQL:

select * from foo_v where bar = 1;

Dan is SQL server slim genoeg om die filter direct op je foo tabel te draaien.

---

Dan deze view:

SQL:

create view foo_v as
select count(aap) as cnt, noot from foo group by noot;

SQL:

select * from foo_v where noot = 5;

Dan is nog maar de vraag of SQL server snapt dat hij die enkel maar noot = 5 hoeft te groeperen. Het kan ook voorkomen dat hij eerst alle groeperingen doet en daarna pas de filter. Wat dus veel langzamer is.

---

De slimheid van databases houdt vrijwel altijd op bij dit soort views:

SQL:

create view foo_v as
select
   *
from
   (select
       row_number() over (partition by aap order by noot) as rij,
       *
    from
       foo
   ) as a
left join
   bar as b
on
   a.id = b.id
and
   a.rij = 1;

Hierbij is de kans vrijwel 100% dat hij eerst de hele selectie van de view doet voordat hij de view resultaten teruggeeft. Ook als je dingen zoals dit doet:

SQL:

select * from foo_v where aap = 1;

Dat zal vrijwel altijd veel langzamer zijn dan:

SQL:

select
   *
from
   (select
       row_number() over (partition by aap order by noot) as rij,
       *
    from
       foo
    where
       aap = 1
   ) as a
left join
   bar as b
on
   a.id = b.id
and
   a.rij = 1;

[Martinusz]

Down schreef op donderdag 10 december 2020 @ 15:54:
Wat staat er in het explain plan/execution plan?

Ga ik zo even draaien.

Edit: @Down Lijkt erop dat ik dat niet mag draaien? (ik ben geen DBA'er dus heb niet veel rechten vrees ik)

CurlyMo schreef op donderdag 10 december 2020 @ 15:58:
Dat hangt er heel erg vanaf wat je probeert te bereiken.

Neem deze view:

SQL:

create view foo_v as
select * from foo;

Je roept de view vervolgens aan als:

SQL:

select * from foo_v where bar = 1;

Dan is SQL server slim genoeg om die filter direct op je foo tabel te draaien.

---

Dan deze view:

SQL:

create view foo_v as
select count(aap) as cnt, noot from foo group by noot;

SQL:

select * from foo_v where noot = 5;

Dan is nog maar de vraag of SQL server snapt dat hij die enkel maar noot = 5 hoeft te groeperen. Het kan ook voorkomen dat hij eerst alle groeperingen doet en daarna pas de filter. Wat dus veel langzamer is.


De slimheid van databases houdt vrijwel altijd op bij dit soort views:

SQL:

create view foo_v as
select
   *
from
   (select
       row_number() over (partition by aap order by noot) as rij,
       *
    from
       foo
   ) as a
left join
   bar as b
on
   a.id = b.id
and
   a.rij = 1;

Hierbij is de kans vrijwel 100% dat hij eerst de hele selectie van de view doet voordat hij de view resultaten teruggeeft. Ook als je dingen zoals dit doet:

SQL:

select * from foo_v where aap = 1;

Dat zal vrijwel altijd veel langzamer zijn dan:

SQL:

select
   *
from
   (select
       row_number() over (partition by aap order by noot) as rij,
       *
    from
       foo
    where
       aap = 1
   ) as a
left join
   bar as b
on
   a.id = b.id
and
   a.rij = 1;

Maar bovenstaande zou voor alle gebruikers hetzelfde resultaat geven toch?
De exact zelfde query geeft dus een resultaat binnen 1 seconden en voor de andere 30 minuten....

simpel voorbeeld:
select *
from APPS.FND_ATTACHED_DOCS_FORM_VL
;

Resultaat gebruiker 1: < 5 seconden
Resultaat gebruiker 2: 500 < seconden

[ Voor 5% gewijzigd door Martinusz op 10-12-2020 16:09 ]

[CurlyMo]

Martinusz schreef op donderdag 10 december 2020 @ 16:02:
[...]
Maar bovenstaande zou voor alle gebruikers hetzelfde resultaat geven toch?

Dan zou ook caching ook nog een factor zijn.

[Martinusz]

CurlyMo schreef op donderdag 10 december 2020 @ 16:12:
[...]

Dan zou ook caching ook nog een factor zijn.

Je bedoelt dat wanneer iemand dezelfde tabel al keer heeft geladen en mogelijk daarom sneller kan draaien tweede keer?

Onze test met simpele query zoals hierboven bij beide gebruikers is zeg maar 'vers' gedraaid, dus voor een eerste keer na openen applicatie.

[CurlyMo]

Martinusz schreef op donderdag 10 december 2020 @ 16:14:
[...]

Je bedoelt dat wanneer iemand dezelfde tabel al keer heeft geladen en mogelijk daarom sneller kan draaien tweede keer?

Onze test met simpele query zoals hierboven bij beide gebruikers is zeg maar 'vers' gedraaid, dus voor een eerste keer na openen applicatie.

De applicatie zegt niks, het gaat om de cache van je database.

[Martinusz]

CurlyMo schreef op donderdag 10 december 2020 @ 16:15:
[...]

De applicatie zegt niks, het gaat om de cache van je database.

Ik ben geen DBA'er dus ik heb hier niet super veel verstand van
Maar de cache van de database, die is voor iedereen gelijk op die database?
Of kan gebruiker 1 in een andere pool of iets dergelijks zitten dan gebruiker 2? (waar gebruiker 1 profeit van heeft qua snelheid)

[CurlyMo]

Martinusz schreef op donderdag 10 december 2020 @ 16:24:
[...]

Ik ben geen DBA'er dus ik heb hier niet super veel verstand van
Maar de cache van de database, die is voor iedereen gelijk op die database?
Of kan gebruiker 1 in een andere pool of iets dergelijks zitten dan gebruiker 2? (waar gebruiker 1 profeit van heeft qua snelheid)

Als een bepaalde selectie meerdere keren gedaan wordt, dan blijft die in het geheugen van de database staan. De volgende run zal dan direct uit het geheugen komen.

[Martinusz]

CurlyMo schreef op donderdag 10 december 2020 @ 16:26:
[...]

Als een bepaalde selectie meerdere keren gedaan wordt, dan blijft die in het geheugen van de database staan. De volgende run zal dan direct uit het geheugen komen.

Ja oke, maar bij gebruiker 2 blijft het net zo langzaam als eerste keer, ook al draait ie de SQL 10 keer achter elkaar zeg maar.
Gebruiker 1 heeft vanaf het begin binnen enkele seconden resultaat.

Kan het zijn dat er verschillende pools zijn tussen gebruikers die ook qua performance verschillend zijn?
Zeg maar DBA versus 'normale' gebruiker?

[CurlyMo]

Martinusz schreef op donderdag 10 december 2020 @ 16:30:
[...]

Ja oke, maar bij gebruiker 2 blijft het net zo langzaam als eerste keer, ook al draait ie de SQL 10 keer achter elkaar zeg maar.
Gebruiker 1 heeft vanaf het begin binnen enkele seconden resultaat.

Kan het zijn dat er verschillende pools zijn tussen gebruikers die ook qua performance verschillend zijn?
Zeg maar DBA versus 'normale' gebruiker?

Oracle SQL heeft volgens mij wel de mogelijkheid om op die manier resources te verdelen. Of dat hier is gebeurt kan ik niet zeggen en ik weet ook niet hoe je daar achter komt. Daar kan ik Oracle SQL niet goed genoeg voor.

[Monkeybrains]

Martinusz schreef op donderdag 10 december 2020 @ 16:02:

simpel voorbeeld:
select *
from APPS.FND_ATTACHED_DOCS_FORM_VL
;

Resultaat gebruiker 1: < 5 seconden
Resultaat gebruiker 2: 500 < seconden

Even een gokje, dit gaat om Oracle EBS (E-Business) afgaande op de schema/tabel structuur? Ik ben DBA en mij sinds kort aan het verdiepen in EBS omdat mijn nieuwe werkgever het gebruikt. 1 ding waar ik achter kwam is de soms bizarre structuur van EBS. APPS.FND_ATTACHED_DOCS_FORM_VL is een synonym voor (waarschijnlijk) FND.FND_ATTACHED_DOCS_FORM_VL, wat weer een view is op een FND_ATTACHED_DOCS_FORM_XX tabel waarschijnlijk. Ook hangt het sterk af van onder welke gebruiker je de query uitvoert is mijn ervaring. Ik moest laatst wat data export query doen en dat ging niet goed als sys user maar wel als apps user.

[Martinusz]

Monkeybrains schreef op donderdag 10 december 2020 @ 16:35:
[...]


Even een gokje, dit gaat om Oracle EBS (E-Business) afgaande op de schema/tabel structuur? Ik ben DBA en mij sinds kort aan het verdiepen in EBS omdat mijn nieuwe werkgever het gebruikt. 1 ding waar ik achter kwam is de soms bizarre structuur van EBS. APPS.FND_ATTACHED_DOCS_FORM_VL is een synonym voor (waarschijnlijk) FND.FND_ATTACHED_DOCS_FORM_VL, wat weer een view is op een FND_ATTACHED_DOCS_FORM_XX tabel waarschijnlijk. Ook hangt het sterk af van onder welke gebruiker je de query uitvoert is mijn ervaring. Ik moest laatst wat data export query doen en dat ging niet goed als sys user maar wel als apps user.

Klopt inderdaad Oracle R12 E-Business.
Ik probeer zoveel mogelijk VIEW te mijden, echter soms kom je er niet omheen (of niet makkelijk).
Het verbaasde mij gewoon zo enorm het verschil tussen 2 gebruikers wanneer er een VIEW wordt gebruikt, dat ik er dan het fijne van wil weten.

Ligt het aan mijn settings? Ligt het aan rechten? Of is het gewoon goed geluk...

[Monkeybrains]

Ik weet te weinig af van EBS om echt een hulp te kunnen zijn. Maar als ik zie dat APPS.FND_ATTACHED_DOCS_FORM_VL een view is die uit 8 synonyms queried die stuk voor stuk naar views verwijzen die op hun beurt ieder uit een paar tabellen hun data halen ga je al gauw ergens stuk als je niet goed naar het query plan kan kijken. Bij het troubleshooten van dit soort dingen is het essentieel dat je:
a. Heel goed snapt hoe de optimizer van Oracle werkt (geen idee wat je kennis hiervan is, no offense in ieder geval).
b. je toegang hebt tot de juiste info zoals query plans en dergelijke om te zien waar precies in de query de traagheid vandaan komt.

En goede tooling! Ik gebruikte vroeger veel Quest Toad for Oracle, prachtige tool toen die heel goed kan helpen met oa het troubleshooten van dit soort dingen.

[ Voor 11% gewijzigd door Monkeybrains op 10-12-2020 17:06 ]

[Martinusz]

Monkeybrains schreef op donderdag 10 december 2020 @ 16:58:
Ik weet te weinig af van EBS om echt een hulp te kunnen zijn. Maar als ik zie dat APPS.FND_ATTACHED_DOCS_FORM_VL een view is die uit 8 synonyms queried die stuk voor stuk naar views verwijzen die op hun beurt ieder uit een paar tabellen hun data halen ga je al gauw ergens stuk als je niet goed naar het query plan kan kijken. Bij het troubleshooten van dit soort dingen is het essentieel dat je:
a. Heel goed snapt hoe de optimizer van Oracle werkt (geen idee wat je kennis hiervan is, no offense in ieder geval).
b. je toegang hebt tot de juiste info zoals query plans en dergelijke om te zien waar precies in de query de traagheid vandaan komt.

En goede tooling! Ik gebruikte vroeger veel Quest Toad for Oracle, prachtige tool toen die heel goed kan helpen met oa het troubleshooten van dit soort dingen.

Ik begrijp je verhaal en zoals gezegd waar nodig werk ik absoluut om views heen!

Ik ga eens vissen bij DBA team om te achterhalen of er geen verschillende pools zijn ofzo, ik zou anders niet kunnen verklaren waarom gebruiker 1 een view in 1 seconden kan aanroepen en gebruiker 2 30 minuten zit te wachten (in zover wij zien gelijke omstandigheden).

Voor mij geldt, VIEWs uit de SQL's werken dan werkt het sowieso sneller!

[edeboeck]

Martinusz schreef op vrijdag 11 december 2020 @ 10:01:
[...]
Voor mij geldt, VIEWs uit de SQL's werken dan werkt het sowieso sneller!

Mijn ervaring als DBA'er gaat al heel wat jaren terug (lees: eind jaren 90), maar views waren toen net sneller dan SQL-statements op zich (omdat er betere optimalisatie/caching plaatsvond). Ik weet niet wat de huidige stand van zaken is, maar de traagheid zou weleens kunnen liggen aan een oorzaak zoals @Monkeybrains aanhaalt (de reden waarom ik dit schrijf: misschien ligt de oorzaak niet bij het gebruik van views an sich, maar wel aan alle gegevens die worden opgehaald).
Dat neemt natuurlijk niet weg dat je nog met een zwaar verschil zit tussen beide uitvoeringstijden.

[mr_derk]

Je moet volgens mij weten hoe de views bevraagd worden, anders kan je er weinig zinnigs over zeggen.

[80000]

Als je Oracle in een cluster draait en de ene gebruiker komt op een database instance die al gebukt gaat onder heavy load, zou dit gedrag kunnen verklaren.

Tijdens de query kan je (de views) v$session (welke gebruiker en noteer de sql_id) en v$sql gebruiken via de sql_id, dan zie je disk_reads, buffer_gets, user_io_wait_time, exec_time en nog veel meer om te analyzeren wat er aan de hand is.

[gmblom]

Dit heeft zeer waarschijnlijk met rechten te maken. EBS werkt op een zodanige manier dat, afhankelijk van welke gebruikerscontext actief is, de views bepaalde rijen wel of niet laten zien. Door direct op de onderliggende tabellen te gaan querien ga je om de security laag heenwerken en wordt het een stuk lastiger om een query te maken die het juiste resultaat teruggeeft: de views zijn in feite een soort api. Dat ze niet performen is wel erg irritant, maar is eigenlijk werk voor je DBA.

[JaQ]

Het heeft idd te maken met hoe de beveiliging van EBS werkt. Die view die je aanroept die krijgt allemaal additionele filters op basis van je credentials.

Je lokale DBA zou je moeten kunnen helpen om dit te fixen.