Re: Mat. Views and Indexes w/o Analyze

On May 29, 6:53 am, rpend..._at_gmail.com wrote:
> On May 27, 4:12 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
>
>
>
>
>
> > <rpend..._at_gmail.com> wrote in message
>
> >news:1180296074.623341.52900_at_p77g2000hsh.googlegroups.com...
>
> > > Being a non-dba can someone answer this question? We had 6
> > > materialized views created in production along with multiple indexes.
> > > An Analyze was not performed on the new indexes, could this have led
> > > to memory utilization issues on that server when queries were run
> > > against these materialized views?
>
> > Yes
>
> > --
> > Regards
>
> > Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> > Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> > The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Thanks Jonathan. Do you know of any references or documentation I can
> show someone explaining this

See http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i28375

Metalink Note:46234.1 is a good primer on explain plans.

Of course, Jonathan's book is the bible on this particular subject, and I think what he meant by "I can't think of a published specific example to demonstrate the point" was that he didn't have a reproducible demonstration handy having to do with materialized views. Such demonstrations are certainly the best way to learn and understand the subject, and convince others of the truth. When dealing with support, it generally helps to have reproducible test cases, too. The references I gave above are just documentation, and since documentation is often full of half-or-less truths, it needs to be scrutinized in a scientific manner. But they can be good enough to show a manager who wants a justification without understanding. If you have a DBA that needs to be shown this sort of thing, or no DBA at all, you have a serious problem. Plain old developers need to be familiar with explaining plans, at any rate.

What evidence did you see of "memory utilization issues?" ORA-40xx errors? Run out of swap space? Performance issues? Large PGA usage? Insufficient redo log buffer? Long refresh times? A tuning advisor told you so? It is important to state all basic information when asking these sorts of questions, see http://www.dbaoracle.net/readme-cdos.htm . For example, if you are using ASMM on 10.2.0.2 with lots of commits someone might point you right at a memory leak bug (like 5552515, demonstration in there, arbitrarily grabbed searching bug database for memory leak materialized view). And they might suggest you check the reasoning behind the code that does all those commits. Adding the lack of stats on top of such a thing can't help, as all that extra data needs to be tracked and sorted through using even more memory.

jg

--
@home.com is bogus.
Wal-Mart pressures another supplier to cut costs:
http://www.signonsandiego.com/uniontrib/20070601/news_1b1dell.html