Re: Faster index creation

On May 14, 1:53 pm, bdbafh <bdb..._at_gmail.com> wrote:
> On May 14, 11:02 am, Tom <tzebli..._at_autooneins.com> wrote:
>
>
>
> > I need to create 2 indexes on a 107 GB table. The indexes have no
> > columns in common. I have a 8 Gig SGA.
>
> > I am not looking forward to 2 tablescans so I was thinking of running
> > both create index statements at the same time from different sqlplus
> > sessions and hoping that the data for the slower statement is in
> > cache for the faster statement.
>
> > Or as an alternative, I was thinking of first creating an index which
> > was a superset of the columns in the other 2 indexes and then creating
> > the other indexes. They will just use the first index instead of
> > doing table scans. When they are done, drop the first index.
>
> > I'm hoping that someone else has better ideas or that there is just a
> > simpler way.
>
> > . . . Tom
>
> Tom,
>
> Is the table partitioned? (enterprise edition + partitioning option
> required)
> What degree of parallelism is set for the table? (if any)
> What version of the Oracle database server software are you referring
> to?
> What is the maximum OS IO size that is supported? (e.g. 1 MB)
> What is the db_file_multiblock_read_count set to? (e.g. 16)
> Have you gathered system stats under workload? (check sys.aux_stats$)
> This would enable cpu costing.
> The db_block_size would also be of interest, as would the tablespace
> block size if it differs from the db_block_size.
> (assume that it is 8192 bytes, but being a DW it might be 16K)
>
> Perhaps the limiting factor is that the dbfmbrc is only say 8, but
> could be set to 128 and grab a full 1 MB of data in a single IO call
> (show parameter db_file)
> Perhaps the limiting factor is that the pga_area_size is smallish
> causing a small sort_area_size to be used during index creation.
>
> If you're looking to make table scan and sorting operations cheaper
> (and go faster) you might consider the following:
> alter session set workarea_size_policy='MANUAL';
> alter session set db_file_multiblock_read_count=128;
> alter session set sort_area_size=536870912;
>
> Larger values for sort_area_size would likely be helpful.
> You might be limited on available physical memory.
>
> If other user sessions are working with blocks from the heap table,
> the likelihood of obtaining a large number of blocks from an extent in
> a single read decreases.
> Although it seems that accessing the blocks of the table at the same
> time would reduce the runtime of the 2 index creations due to having
> more blocks of that segment in memory, its much more likely that it
> would cause contention and cause the effective dbfmbrc to be reduced
> and the overall runtime to increase.
>
> Hopefully you have a good test environment in which to test for
> yourself.
>
> -bdbafh

"pga_area_size"
meant pga_aggregate_target.
oops. Received on Wed May 14 2008 - 12:55:39 CDT