Re: tunning an index build

if workarea_size_policy is manual then pga_aggregate_target is not used.

• Original Message ---- From: "genegurevich_at_discover.com" <genegurevich_at_discover.com> To: Alexander Fatkulin <afatkulin_at_gmail.com> Cc: oracle-l_at_freelists.org Sent: Wednesday, July 18, 2007 10:32:11 AM Subject: Re: tunning an index build

Alex,

Yes, I have set pga_aggregate_target to 1.5G and workarea_size_policy is manual. I thought that this will make
the sort_area_size (and all other area_size parameters) unused. IS that not the case?
I am also working with the UNIX team to address the I/O contention that I am seeing.

thank you for your input

Gene Gurevich

                                                                           
             "Alexander                                                    
             Fatkulin"                                                     
             <afatkulin_at_gmail.                                          To 
             com>                      genegurevich_at_discover.com           
                                                                        cc 
             07/17/2007 11:10          oracle-l_at_freelists.org              
             PM                                                    Subject 
                                       Re: tunning an index build          
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

Do you use those 1.5G through pga_aggregate_traget?

If so, try to use more memory by switching your session to workarea_size_policy=manual and dedicating as much to sort_area_size as you can.

Make sure you spent enough time on temp I/O before doing that.

On 7/17/07, genegurevich_at_discover.com <genegurevich_at_discover.com> wrote:
> Hi all:
>
> I think I have posted on this issue before, but I can't find my old
Emails
> so - sorry for the repeated question.
> Here is the situation. I am running oracle 9208 and have a 40G table
with
> a lot of indices - mostly bitmap
> Their sizes are under 1G. These indices take about 20 to 30 minutes to
> build. That seems to be too long.
> I have just waited for 24 minutes to build a 82M index. The index is
> parallelized 4 way, the table is
> parallelized 6 ways. I have 2G sga and 1.5G pga. I do see a lot of IO
waits
> (via vmstat) and I have UNIX
> team looking into that. I think I have plenty of memory and the
parallelizm
> should be enough for a small index.
>
> Any thoughts on what else I should look at?
>
> thank you
>
> Gene Gurevich
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Alex Fatkulin,
The Pythian Group,
http://www.pythian.com/blogs/author/alexf/




--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l