Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Mat. Views and Indexes w/o Analyze

Re: Mat. Views and Indexes w/o Analyze

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 1 Jun 2007 20:49:41 +0100
Message-ID: <6eSdnYzwgPrO5f3bRVnyhwA@bt.com>


<rpendyck_at_gmail.com> wrote in message
news:1180446826.808016.271620_at_q75g2000hsh.googlegroups.com...
> 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?
>

The basic principle is quite straightforward, but I can't think of a published specific example to demonstrate the point.

In the absence of reasonable statistics, the optimizer may decide that a certain execution plan is a very good idea when in fact the volume of data involved means it is a very bad idea.

For example, a 6-way hash join in a certain order might seem to be a very good idea if 5 of the tables appear to return a small volume of data. But if some of them happen to return large data sets, and the join order encourages the appearance of long rows in intermediate results then you can easily use up a lot of memory.

Your next step should be to learn a bit about execution plans, how to generate them and how to read them.

set linesize 132
set pagesize 60
explain plan for
{your sql statement}

select * from table(dbms_xplan.display)
;

Post the result, and someone may have time to analyze the result and give you a few pointers on what to look at next.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Fri Jun 01 2007 - 14:49:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US