[Oracle] Materialize View - Refresh faalt door staleness

[JaFFoG]

Ik zit met een vervelend probleem en na veel Google'en en lezen van lappen tekst stel ik mijn vraag nu maar hier.

Hieronder een illustratie van het model in kwestie waar het probleem optreedt.


In Schema A heb ik een Materialized View die periodiek, middels een Complete Refresh gegevens ophaalt uit Schema B en heeft hier ook de rechten voor. Dit gebeurt allemaal op Oracle-niveau.

Het probleem wat optreedt is dat de Refresh mislukt doordat de Staleness de waarde "Needs Compile" krijgt.

Mijn vermoeden is dat dit te maken heeft met het feit dat de gegevens uit een ander schema komen, maar ik kan dit nog niet bevestigen. Schema B is het schema waar alle brongegevens in staan en daar kan niks in het model gewijzigd worden (dus ook geen Tabellen, Views, Functions, etc. aan worden toegevoegd).

Kan iemand mijn verder helpen?

[JaQ]

Gebruik je partities in je materialized view?

(en welke versie hebben we het over?)

[ Voor 31% gewijzigd door JaQ op 20-09-2011 22:22 ]

[JaFFoG]

Hoi JaQ, bedankt voor je reactie.

Versie: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Ik gebruik geen partities in deze Materialized View. Verder heb ik de details nog even bekeken (zie dit tekstbestand) en het volgende viel mij op:

"UNKNOWN_PLSQL_FUNC" "Y"

Dit is mij niet eerder opgevallen, maar in de onderliggende query worden inderdaad een aantal schema-specifieke functies aangeroepen. Zou hier het probleem in kunnen liggen?

[JaQ]

JaFFoG schreef op donderdag 22 september 2011 @ 12:51:
Zou hier het probleem in kunnen liggen?

mmm.. Schema specifieke functies zeg je. Vertel je nu dat de mview over een view in het remote schema gaat?

edit:

Ik moet ook eerst in de docs kijken

Indicates whether the materialized view contains PL/SQL functions (Y) or not (N)

Oftewel: je gebruikt een pl/sql functie in je mview. Normaliter is dat een recept voor performance issues (maar vandaar dat je een mview gebruikt?). Anyway, op zich is het gebruik van een pl/sql functie geen reden voor het invalideren van de mview.

NEEDS_COMPILE kan overigens geen waarde van staleness zijn, dat kan wel de waarde van compile_state zijn. Zie hier.

Some object upon which the materialized view depends has changed. An ALTER MATERIALIZED VIEW...COMPILE statement is required to validate this materialized view.

Dat betekent dat er blijkbaar in 1 van de onderliggende objecten (bijvoorbeeld de pl/sql functie(s), of het object in het andere schema) iets gewijzigd wordt aan de structuur (DDL dus). Ook de locatie van een external table valt hier onder.

[ Voor 66% gewijzigd door JaQ op 22-09-2011 13:46 ]

[mkleinman]

JaFFoG schreef op donderdag 15 september 2011 @ 11:16:
Ik zit met een vervelend probleem en na veel Google'en en lezen van lappen tekst stel ik mijn vraag nu maar hier.

Hieronder een illustratie van het model in kwestie waar het probleem optreedt.
[afbeelding]

In Schema A heb ik een Materialized View die periodiek, middels een Complete Refresh gegevens ophaalt uit Schema B en heeft hier ook de rechten voor. Dit gebeurt allemaal op Oracle-niveau.

Het probleem wat optreedt is dat de Refresh mislukt doordat de Staleness de waarde "Needs Compile" krijgt.

Mijn vermoeden is dat dit te maken heeft met het feit dat de gegevens uit een ander schema komen, maar ik kan dit nog niet bevestigen. Schema B is het schema waar alle brongegevens in staan en daar kan niks in het model gewijzigd worden (dus ook geen Tabellen, Views, Functions, etc. aan worden toegevoegd).

Kan iemand mijn verder helpen?

Edit:

Ik zie net dat je function calls doet waar hij problemen mee geeft. Mogelijkerwijs zijn deze objecten invalid doordat er andere objecten opnieuw zijn gecompileerd. Je kan dit oplossen door de invalid objecten te recompilen. Volgens mij kan je dit ook doen met een ALTER nogwat RECOMPILE ALL te doen op Schema B.

[ Voor 12% gewijzigd door mkleinman op 22-09-2011 13:47 ]

[JaQ]

flitspaal.nl schreef op donderdag 22 september 2011 @ 13:46:
Ik zie net dat je function calls doet waar hij problemen mee geeft. Mogelijkerwijs zijn deze objecten invalid doordat er andere objecten opnieuw zijn gecompileerd. Je kan dit oplossen door de invalid objecten te recompilen. Volgens mij kan je dit ook doen met een ALTER nogwat RECOMPILE ALL te doen op Schema B.

Vind je dit niet te veel symptoom bestrijden? De vraag is toch waarom het object invalid wordt, niet hoe je een invalid object compileert?

[mkleinman]

JaQ schreef op donderdag 22 september 2011 @ 13:53:
[...]

Vind je dit niet te veel symptoom bestrijden? De vraag is toch waarom het object invalid wordt, niet hoe je een invalid object compileert?

Meestal worden objecten invalid zodra je nieuwe objecten compileert die een afhankelijkheid hebben met een ander object. Zelf ook vaak genoeg ruzie mee gehad. Andere optie is om bij een nieuwe release alle invalid objects opnieuw te compileren, vermoedelijk dus ook de materialized view.

[JaFFoG]

Allereerst excuses voor de late reactie. De prioriteiten zijn tijdelijk iets verschoven, maar inmiddels is dit probleem weer geklommen op de ladder met openstaande werkzaamheden.

Bedankt voor de hulp zover!

JaQ schreef op donderdag 22 september 2011 @ 13:39:
[...]

Oftewel: je gebruikt een pl/sql functie in je mview. Normaliter is dat een recept voor performance issues (maar vandaar dat je een mview gebruikt?). Anyway, op zich is het gebruik van een pl/sql functie geen reden voor het invalideren van de mview.

Dat is inderdaad de reden dat ik een mview gebruik.

(...)

Dat betekent dat er blijkbaar in 1 van de onderliggende objecten (bijvoorbeeld de pl/sql functie(s), of het object in het andere schema) iets gewijzigd wordt aan de structuur (DDL dus). Ook de locatie van een external table valt hier onder.

In de onderliggende objecten wijzigt alleen de inhoud van de tabellen. Maar die inhoud is vrij uitgebreid (zeer complex schema met een behoorlijke hoeveelheid data). Ik kan me voorstellen dat dit gevolgen heeft op de schijven wat opslag betreft en dat dit gevolgen heeft voor de status die Oracle aan die objecten hangt. Ik heb alleen té weinig technische kennis om hier iets zinnigs over te zeggen. Kan hier een oorzaak gezocht worden?

flitspaal.nl schreef op donderdag 22 september 2011 @ 13:46:
Edit:

Ik zie net dat je function calls doet waar hij problemen mee geeft. Mogelijkerwijs zijn deze objecten invalid doordat er andere objecten opnieuw zijn gecompileerd. Je kan dit oplossen door de invalid objecten te recompilen. Volgens mij kan je dit ook doen met een ALTER nogwat RECOMPILE ALL te doen op Schema B.

Een ALTER heeft niet mijn voorkeur. Hiervoor moeten bepaalde gebruikers dit recht krijgen welke ik in verband met veiligheid liever niet uitdeel.

Is er een manier om te achterhalen wanneer een object voor het laatst een compile heeft gehad? Ik weet van de DBA_OBJECTS en USER_OBJECTS af, maar daar staat die informatie bij mijn weten niet in.

Edit:
Ik begrijp uit diverse andere bronnen dat de laatste compile timestamp overeenkomt met de LAST_DDL_TIME (al interpreteer ik de term "last ddl time" zelf anders). Als dat zo is, dan hebben geen van de onderliggende objecten een compile gekregen die mijn probleem verklaren.

[ Voor 6% gewijzigd door JaFFoG op 13-10-2011 10:39 . Reden: LAST_DDL_TIME ]

[JaFFoG]

JaQ schreef op donderdag 22 september 2011 @ 13:39:
NEEDS_COMPILE kan overigens geen waarde van staleness zijn, dat kan wel de waarde van compile_state zijn. Zie hier.

Ik zie het, maar dit is toch echt zo. Screenshot:

[JaQ]

JaFFoG schreef op donderdag 13 oktober 2011 @ 10:16:
In de onderliggende objecten wijzigt alleen de inhoud van de tabellen. Maar die inhoud is vrij uitgebreid (zeer complex schema met een behoorlijke hoeveelheid data). Ik kan me voorstellen dat dit gevolgen heeft op de schijven wat opslag betreft en dat dit gevolgen heeft voor de status die Oracle aan die objecten hangt. Ik heb alleen té weinig technische kennis om hier iets zinnigs over te zeggen. Kan hier een oorzaak gezocht worden?

Normaliter is dat geen probleem. Tenzij je mview baseert op een object waar DDL op plaats vindt. Bijvoorbeeld dus een external table (waar je de location van wijzigt). Misschien ook wel een refresh van een andere mview of zo. Of een object dat via een databaselink wordt opgehaald.

JaFFoG schreef op donderdag 13 oktober 2011 @ 10:16:
Een ALTER heeft niet mijn voorkeur. Hiervoor moeten bepaalde gebruikers dit recht krijgen welke ik in verband met veiligheid liever niet uitdeel.

Het is ook een zwaktebod. Een trucje om niet echt uit te zoeken wat er aan de hand is (vandaar mijn opmerking over symptoom bestrijden alsof ik niet weet dat compileren ook kan helpen

Anyway, metalink note 454552.1 zegt "This is expected behavior when an update is made to a table that the materialized view references. The dba_objects table will report the status as invalid. It is normal behavior for dba_objects to return INVALID status for materialized views after the base tables have been updated." (en nog wat meer, maar dat mag je zelf opzoeken). Ik gok dus zomaar dat je refresh niet failed vanwege de needs_compile waarde.

Welke error krijg je als je de refresh uitvoert?

[The Eagle]

Leuk issue

Wat ik me afvraag: is er toevallig aan de onderliggende objecten iets gewijzigd als het omzetten van een LONG naar een LOB? of toevallig recentelijk naar een andere DB versie gegaan, danwel gepatched?

[JaFFoG]

Heren,

Dank weer voor de reacties.

JaQ schreef op donderdag 13 oktober 2011 @ 22:36:
(...) Bijvoorbeeld dus een external table (waar je de location van wijzigt). Misschien ook wel een refresh van een andere mview of zo. Of een object dat via een databaselink wordt opgehaald.

Heb beide situaties voor alle zekerheid toch even gecontroleerd, maar van beide is in dit geval geen sprake.

[...]
Het is ook een zwaktebod. Een trucje om niet echt uit te zoeken wat er aan de hand is (vandaar mijn opmerking over symptoom bestrijden alsof ik niet weet dat compileren ook kan helpen

Eens. Ik heb liever een oplossing.

Anyway, metalink note 454552.1 zegt "This is expected behavior when an update is made to a table that the materialized view references. The dba_objects table will report the status as invalid. It is normal behavior for dba_objects to return INVALID status for materialized views after the base tables have been updated." (en nog wat meer, maar dat mag je zelf opzoeken). Ik gok dus zomaar dat je refresh niet failed vanwege de needs_compile waarde.

Welke error krijg je als je de refresh uitvoert?

Ik refresh a.d.h.v. de staleness. Omdat die de status "NEEDS_COMPILE" heeft, gaat de boel mis. Ik ben er zojuist achter dat een geforceerde refresh wel (soort van) succes heeft. Alleen stopt het proces halverwege.

Het probleem blijkt dus nèt iets anders dan geschetst in de titel: het échte probleem is hoe het kan dat de staleness een waarde "NEEDS_COMPILE" krijgt. Maar ik denk dat ik met de gegevens informatie daar wel een weg in weet te vinden.

The Eagle schreef op donderdag 13 oktober 2011 @ 22:48:
(...)
Wat ik me afvraag: is er toevallig aan de onderliggende objecten iets gewijzigd als het omzetten van een LONG naar een LOB? of toevallig recentelijk naar een andere DB versie gegaan, danwel gepatched?

Driemaal neen.

Vraag blijft hoe het kan dat de staleness een waarde "NEEDS_COMPILE" krijgt, waar dit volgens de documentatie niet zou kunnen. Het moet toch ergens vandaan komen.

[Killemov]

Wellicht een open deur: Hoeveel trager wordt het met een "normale" view?

Als je een normale/materialized view toch niet kunt gebruiken waarom er dan niet een table van maken? Die kun je vervolgens OF periodiek updaten OF met behulp van triggers.