| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Materialized Views performance
Sounds like the problem I have - I've logged a TAR with Oracle - see
attachment.
If I update 1 record on 2 tables (set field equal to itself), commit is instant. I then execute:
exec dbms_snapshot.refresh('v_matched_orders, v_cross_orders')
which takes 90secs (should be 1/10th of a second by my reckoning). On a
dataload where say 1000 rows are added the MV refresh takes 10-30 minutes
(to create the MVs takes 10mins). I would suggest generating a trace file
and running tkprof on it to see what is going on (and opening a TAR with
Oracle - feel free to reference mine if you wish).
1. open sql
2. alter session set events '10046 trace name context forever, level 12';
This will create a trace file in the user_dump_dest folder (init.ora).
3. run commands to update/insert and refresh MV. 4. Exit SQL (closes trace file 5. Ms-dos. cd <user_dump_dest> 6. tkprof <trace filename> tkprof.out explain=user/pass_at_sid
I have the error ORA-10980 at the top of the file. I'll be upgrading my test server to 8.1.6 or 8.1.7 next week and going from there.
Steve
"mike streeton" <mikes_at_crazydiamondcorp.demon.co.uk> wrote in message
news:999008759.25405.0.nnrp-07.c3ad5001_at_news.demon.co.uk...
> We are running 8.1.7 with 3 aggregate materialized views on a single fact
> table. The views are created with query rewrite and on commit update. The
> query rewrite works great and queries are very good. Update performance of
> the base fact table is very poor with a 1000 row insertion taking 1.5
hours,
> to do this in my own pl/sql code takes 2 seconds on the same machine.
>
> Has anybody else seen this problem or similar and no the reason. We could
> maintain the materialised views ourselves but getting Oracle to do it
would
> be the best option.
>
> Many Thanks
>
> Mike
>
>
begin 666 TAR text.txt
M06QL(&1E=&%I;',@87,@5$%2(#$S-#0R,#(N.3DV("T@:70@=V%S(&YE=F5R
M(')E<V]L=F5D(&)U="!C;&]S960_at_87,@22!W87,@#0IG;VEN9R!O;B!A(%!E
M<F9O<FUA;F-E("L_at_5'5N:6YG(&-O=7)S92X_at_22!N;W<@;F5E9"!T;R!G970@
M=&AI<R!F:7AE9"X-"DD@:&%V92 R('-N87!S:&]T<RP_at_8F%S960@;VX@=6YD
M97)L>6EN9R!T86)L97,@:&%V:6YG(#PU,# L,# P(')E8V]R9',N($EF($D@
M#0IU<&1A=&4_at_82!S:6YG;&4@<F5C;W)D(&%N9"!C;VUM:70@=&AE(&-H86YG
M92!I="!T86ME<R Y,"!S96-O;F1S(&9O<B!T:&4@#0IM871E<FEA;&EZ960@
M=FEE=W,@=&\@=7!D871E+B!7:71H;W5T(&EN9&5X97,@=&AE(')E9G)E<V@@
M=&%K97,@,3_at_P('-E8V]N9',N($D@#0IC86YN;W0_at_8F5L:65V92!I;F1E>&5S
M('=I;&P@<F5D=6-E('1H:7,@<F5F<F5S:"!T:6UE('1O(&$@<W!L:70@<V5C
M;VYD('-O('1H97)E( T*;75S="!B92!S;VUE=&AI;F<@9G5N9&%M96YT86QL
M>2!W<F]N9RX-"D]B=FEO=7-L>2P@=VAE;B!U<&1A=&EN9R!H=6YD<F5D<R]T M:&]U<V%N9',@;V8@<F5C;W)D<R!T:&4@<F5F<F5S:"!T:6UE(&)E8V]M97,@ M#0II;G1R=7-I=F4_at_870@,3 M-# @;6EN=71E<RX_at_3&]A9&EN9R!H=6YD<F5DM<R!O<B!T:&]U<V%N9',@;V8@<F5C;W)D<R!I;G1O('1H92 -"G5N9&5R;'EI M;F<@=&%B;&5S('1A:V5S(&QE<W,@=&AA;B U(&UI;G5T97,@*'5S:6YG(%!, M+U-13"!S8W)I<'1S('=I=&@@#0IV86QI9&%T:6]N+"!D871A(&QO;VMU<',@ M971C*2X-"DD_at_8V%N('!R;V1U8V4_at_86YY('1R86-E(&9I;&5S+"!M871E<FEA M;&EZ960@=FEE=R!S8W)I<'1S+"!D871A8F%S92!D=6UP<R!T:&%T( T*>6]U M('=O=6QD(&QI:V4N(%)E9V%R9',L#0I3=&5V92X-"@T*(R,C(%=H870@=V5R M92!Y;W4@=')Y:6YG('1O(&%C8V]M<&QI<V@@=VAE;B!T:&4@<')O8FQE;2!O
M8V-U<G)E9#\@(R,C#0I-5B!2969R97-H#0H-"B,C(R!(87,@=&AI<R!E=F5R
M('=O<FME9#\@(R,C#0I997,-"@T*(R,C($-A;B!Y;W4@<F5P<F]D=6-E('1H
M92!P<F]B;&5M(&%T('=I;&P_(",C(PT*665S#0H-"B,C(R!297!R;V1U8VEB
M;&4@;VX@;W1H97(@<&QA=&9O<FUS+"!S>7-T96US+"!E;G9I<F]N;65N=',_
M(",C(PT*665S#0H-"B,C(R!+;F]W;B!R96-E;G0_at_8VAA;F=E<R!T;R!Y;W5R
M(&-O;7!U=&5R(&5N=FER;VYM96YT.B C(R,-"DYO;F4-"@T*0V]N=&%C="!M
M92!V:6$@.B!%+6UA:6P@+3X_at_4W1E=F4N2&]L;&EN9W- 16-R;W-S;F5T+F-O
M;0T*#0H-"@T*#0HR.2U!54<M,#$@,3$Z,C,Z,C4-"@T*5&AI<R!405(@:&%S
M('1H92!W<F]N9R!P<F]D=6-T(&-O9&4_at_86YD('-O('=A<R!N;W0_at_87-S:6=N
M960@=&\@=&AE(&-O<G)E8W0_at_9W)O=7 N#0I/<F%C;&4_at_4F1B(&ES(&YO="!T
M:&4@<V%M92!D871A8F%S92!A<R!/<F%C;&4X#0I)('=I;&P_at_9V5T('1H:7,@
M5$%2(')E87-S:6=N960N#0H-"@T*,CDM055'+3 Q(#$R.C4T.C S#0H-"E1H
M92!C=7-T;VUE<B!H87,@=7!L;V%D960@=&AE(&9O;&QO=VEN9R!F:6QE('9I
M82!-971A3&EN:SH-"G9?8W)O<W-?;W)D97)S7TU6+G-Q; T*#0H-"C(Y+4%5
M1RTP,2 Q,CHU-3HP- T*#0I4:&4_at_8W5S=&]M97(@:&%S('5P;&]A9&5D('1H
M92!F;VQL;W=I;F<@9FEL92!V:6$@365T84QI;FLZ#0IV7VUA=&-H961?;W)D
M97)S7TU6+G-Q; T*#0H-"C(Y+4%51RTP,2 Q,CHU-CHP,0T*#0I4:&4_at_8W5S
M=&]M97(@:&%S('5P;&]A9&5D('1H92!F;VQL;W=I;F<@9FEL92!V:6$@365T
M84QI;FLZ#0I/<F$P,#(V."YT<F,-"@T*#0HR.2U!54<M,#$@,3(Z-3_at_Z,C<-
M"@T*#0I,;V]K:6YG(&EN=&\@=&AI<RXN+BX-"@T*#0HR.2U!54<M,#$@,3,Z
M,# Z,3_at_-"@T*3F5W(&EN9F\@.B!4:&%T(&QI<W0@;V8@<')O9'5C=',@:7,@
M9F%R('1O;R!L;VYG+"!A;F0@:70_at_9&]E<VXG="!I;F-L=61E($]R86-L92 -
M"C@@16YT97)P<FES92!S;R!)(&QE9G0@:70@;VX@=&AA="!D969A=6QT+@T*
M#0H-"C(Y+4%51RTP,2 Q,SHU,SHS.0T*#0H-"DAI(%-T979E+ T*#0I0;&5A
M<V4@=')Y('1H92!F;VQL;W=I;F<@87,@=V%S('-U9V=E<W1E9"!I;B!T:&4@
M<')E=FEO=7,@=&%R.@T*#0I5<V4@=&MP<F]F(&]N('1H92!E=F5N=" Q,# T
M-B!T<F%C92!F:6QE('1O(&=E="!T:&4_at_97AP;&%I;B!P;&%N+@T*4&EC:R!T
M:&4@=V]R<W0@<75E<GDN#0I#:&5C:R!I9B!A;GD_at_1G5L;"!486)L92!38V%N
M(&ES(&)E:6YG(&1O;F4N#0I!9&0@:6YD97@@;VX@=&AE('1A8FQE<R!A<R!R
M97%U:7)E9"X-"E1H96X@=')Y('1H92!R969R97-H+@T*#0I4<GD@=&AE(&%B
M;W9E('1O(&-H96-K(&EF('1H92!&=6QL(%1A8FQE(%-C86X@:&%S(&)E96X@
M879O:61E9"X-"@T*1&\@>6]U(&AA=F4_at_86YY('9E<G-I;VYS(&]F(#@N,2XW
M(&1A=&%B87-E('=H97)E('EO=2!C86X@=')Y('1H92!T97-T(&-A<V4_#0H-
M"@T*07II>@T*#0H-"C(Y+4%51RTP,2 Q,SHU,SHT-0T*#0I%;6%I;"!5<&1A
M=&4_at_8G5T=&]N(&AA<R!B965N('!R97-S960@+2T_at_4V5N9&EN9R!E;6%I;"X@
M#0H-"C(Y+4%51RTP,2 Q-#HR,SHP,@T*#0I.97<@:6YF;R Z($D_at_9&]N)W0@
M8F5L:65V92!T:&%T('!L87EI;F<@87)O=6YD('=I=&@@:6YD97AE<R!C86X@
M9V5T(&$@.3 @<V5C;VYD( T*<75E<GD_at_9&]W;B!T;R Q+S$P(&]F(&$@<V5C
M;VYD+"!E<W!E8VEA;&QY('=H96X@=&AA="!Q=65R>2!I<R!B87-E9"!O;B Q
M(')O=R!O9B -"F1A=&$N#0I-871E<FEA;&EZ960@=FEE=R!L;V=S(')E8V]R
M9"!T:&4_at_9F%C="!T:&%T(')E8V]R9" S,# S,# @:&%S(&)E96X@=7!D871E
M9"!O;B R( T*=&%B;&5S+B!3=7)E;'D@=VET:"!A;B!I;F1E>"!O;B!E86-H
M(')O=VED(&-O;'5M;B H;VX@=&AE($U6*2!A;F0@=&AE(')O=VED( T*8V]L
M=6UN(&)E:6YG(&]N(&5A8V@@=FEE=R!L;V<@:70@<VAO=6QD(&)E(&EN<W1A
M;G1A;F5O=7,@<F5G87)D;&5S<R!O9B!A;GD@#0II;F1E>&5S(&]N('1H92!U
M;F1E<FQY:6YG('1A8FQE<RX_at_02 G1F%S="<@<F5F<F5S:"!O9B Y,"!S96-O
M;F1S(&]N(#$@<F]W(&]F( T*9&%T82!O=70@;V8_at_-3 P+# P,"!I;F1E>&5D
M(&)Y(')O=VED(&ES;B=T(')I9VAT+@T*22!W86YT('1O(&9O8W5S(&]N('1H
M:7,@<F%T:&5R('1H86X@=&AE(&-R96%T:6]N('1I;64N($%R92!T:&5R92!A
M;GD@:VYO=VX@#0IP<F]B;&5M<S\@1$\@=&AE($U6(&-R96%T:6]N('-C<FEP
M=',@;&]O:R!O:S\-"DEF(&ET('1A:V5S(#$P(&UI;G,@87!P<F]X('1O(&-R
M96%T92!B;W1H($U6<R!H;W<@8V%N(&ET('1A:V4@,3 M-# @;6EN=71E<R!T
M;R -"G)E9G)E<V@@:70@=VAE;B Q,# P(&YE=R!R96-O<F1S(&%R92!A9&1E
M9#\@4V]M971H:6YG('-E96US(&9U;F1A;65N=&%L;'D@=W)O;F<@#0IH97)E
M+@T*22!W:6QL(&=E;F5R871E(&$@5%)#(&9I;&4_at_9F]R('1H92!S:6YG;&4@
M<F]W('5P9&%T92!A;F0@=&AE('-U8G-E<75E;G0_at_358@#0IR969R97-H+B -
M"@T*#0HR.2U!54<M,#$@,30Z-30Z,#(-"@T*5&AE(&-U<W1O;65R(&AA<R!U
M<&QO861E9"!T:&4_at_9F]L;&]W:6YG(&9I;&4@=FEA($UE=&%,:6YK.@T*3W)A
M,# R,S_at_N=')C#0H-"@T*,CDM055'+3 Q(#$T.C4U.C T#0H-"E1H92!C=7-T
M;VUE<B!H87,@=7!L;V%D960@=&AE(&9O;&QO=VEN9R!F:6QE('9I82!-971A
M3&EN:SH-"G)E9G(Q+F]U= T*#0H-"C(Y+4%51RTP,2 Q-#HU.#HP,@T*#0I.
M97<@:6YF;R Z($IU<W0@=7!L;V%D960@=&AE(%1R86-E(&9I;&4_at_86YD('1H
M92!C;W)R97-P;VYD:6YG(%1+4%)/1B!O=71P=70N( T*5&AE<F4_at_87)E(#(@
M97)R;W)S(&%T('1H92!T;W @;V8@=&AI<R!F:6QE(')E9V%R9',@97)R;W(@
M3U)!+3$P.3_at_P+@T*22!C;W5L9&XG="!F:6YD(&%N>71H:6YG(&]N($]43BP@
M86YD(&]N;'D_at_82!C;W5P;&4@;V8_at_9&ES8W5S<VEO;B!T:')E861S(&]N( T*
M365T86QI;FLN($-O=6QD('1H:7,@<')O8FQE;2!B92!H879E('-E<FEO=7,@
M:VYO8VLM;VX_at_969F96-T<S\-"DDG;2!O=70@;V8@=&AE(&]F9FEC92!T:'5R
M+V9R:2!T:&ES('=E96L_at_86YD(&)A8VL@;VX_at_36]N9&%Y+@T*#0H-"C(Y+4%5
M1RTP,2 Q-3HQ,#HS, T*#0H-"E1H86YK<R!F;W(@=&AE(&EN9F\@86)O=F4N
M#0H-"E=I;&P@;&]O:R!I;G1O('1H92!I;F9O<FUA=&EO;B!P<F]V:61E9"!A
M8F]V92X-"@T*37D_at_8V]L;&5G=64@=VAO('1R:65D('1H92!S86UE('1H:6YG
M(&AE<F4@;VX_at_82!S=7!P;W)T960@=F5R<VEO;B!O9B!/<F%C;&4@+2 X,38@
M+R X,3<@86YD(&-O=6QD(&YO="!R97!R;V1U8V4@=&AE('!R;V)L96TN#0HM"
D%R92!Y;W4_at_86)L92!T;R!R97!R;V1U8V4@=&AI<R!O;B X+C$N-B!O<B X
M+C$N-RX-"@T*66]U(&%R92!O;B X+C$N-2XP+C L('=O=6QD(&%D=FES92!Y
M;W4@=&\@9V\@=&\@=&AE(&QA=&5S="!P871C:"!S970_at_9F]R(#@N,2XU('=H
M:6-H(&ES(#@N,2XU+C$N,2X-"@T*4&QE87-E(&%L<V\@:&%V92!A(&QO;VL@
M870@=&AE(&9O;&QO=VEN9SH-"CQ"=6<Z,3(V,#8W,#X_at_86YD(#Q"=6<Z,3(Y
1,#@S,#X-"@T*07II>@T*#0H`
 |
 |