SEO

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

On Wed, 14 May 2008 08:02:03 -0700, Tom wrote:

> 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

First of all, if you are to employ "parallel" option, there will be
no caching. You will have quite a few segment checkpoints, though.
My advice would be to clench your teeth and take the 2 scans like a
man. A DBA got to do what a DBA got to do. Increase PGA_AGGREGATE_TARGET,
create indexes with "nologging" and byte the bullet.




--
http://mgogala.freehostia.com

On May 14, 8:02*am, Tom <tzebli...@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.

I think you might have lock issues doing this. See
http://richardfoote.wordpress.com/20...ch-ch-changes/

Remember, DDL is not transaction oriented, so issues commits before
and after it runs, and so has to rely on locking to make sense. Also,
full table scans put the blocks read on the tail end of the LRU list,
so they age out first (see memory use section of performance tuning
guide).

>
> 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.

This sounds good, though the docs say it _may_ use the index to build
the others. I would expect it to, but... test.

>
> I'm hoping that someone else has better ideas or that there is just a
> simpler way.

Listen to Mladen.

jg
--
@home.com is bogus.
Half-a-doc: http://www.oracle.com/technology/sof...acle10g/htdocs

On May 14, 11:27*am, Mladen Gogala <mgog...@yahoo.com> wrote:
> On Wed, 14 May 2008 08:02:03 -0700, Tom wrote:
> > 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
>
> First of all, if you are to employ "parallel" option, there will be
> no caching. You will have quite a few segment checkpoints, though.
> My advice would be to clench your teeth and take the 2 scans like a
> man. A DBA got to do what a DBA got to do. Increase PGA_AGGREGATE_TARGET,
> create indexes with "nologging" and byte the bullet.
>
> --http://mgogala.freehostia.com

Do not forget that If you run two index creates on the same table at
one time you will need to have enough temporary tablespace allocated
to handle both index builds and the two tasks will compete for IO
capacity for both the reading of the table and the use of sort plus
potentially the index target tablespace.

I would set work area management policy to manual, set the largest
practical sort_area_size possible and just run one index build at a
time.

I do not have experience using the parallel parameter on the index
build but I would hesitate to let Oracle use its calculated value for
the reasons Mladen mentioned. I might try 2 or 4 depending on how
many cpu the server has and how many physical disks temp and the table
are spread over. Sometimes less is better.

HTH -- Mark D Powell --

On May 14, 11:02 am, Tom <tzebli...@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

On May 14, 1:53 pm, bdbafh <bdb...@gmail.com> wrote:
> On May 14, 11:02 am, Tom <tzebli...@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.

"Tom" <tzeblisky@autooneins.com> wrote in message
news:889e1efa-7a65-4df5-b043-590a1c1e690b@k13g2000hse.googlegroups.com...
>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


Which version ?

If it's older than 10.2 then you'll be using the old sort option,
which is very CPU intensive and CPU may (silly though it sounds)
be more important than the simple cost of a large disc read.

You've got an 8Gb SGA, so I assume this means a 64bit machine.
If you allow about 36 bytes overhead per row plus the sum of
the average columns sizes in the index plus 2 x number of columns.
That will give you an indication of the size of the pga memory
you will need to complete the sort in memory.

If you can't do it in memory, the amount of I/O involved will be
the same regardless if you can do a one-pass sort - and the smaller
the memory usage is, the less CPU you use ... and that can make a
big difference to elapsed time.


--
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

On May 14, 2:43*pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "Tom" <tzebli...@autooneins.com> wrote in message
>
> news:889e1efa-7a65-4df5-b043-590a1c1e690b@k13g2000hse.googlegroups.com...
>
>
>
>
>
> >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
>
> Which version ?
>
> If it's older than 10.2 then you'll be using the old sort option,
> which is very CPU intensive and CPU may (silly though it sounds)
> be more important than the simple cost of a large disc read.
>
> You've got an 8Gb SGA, so I assume this means a 64bit machine.
> If you allow about 36 bytes overhead per row plus the sum of
> the average columns sizes in the index plus 2 x number of columns.
> That will give you an indication of the size of the pga memory
> you will need to complete the sort in memory.
>
> If you can't do it in memory, the amount of I/O involved will be
> the same regardless if you can do a one-pass sort - and the smaller
> the memory usage is, the less CPU you use ... and that can make a
> big difference to elapsed time.
>
> --
> 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- Hide quoted text -
>
> - Show quoted text -


Thank you all!! It is always humbling to realize how little I know.

To answer a few of your questions:

Is the table partitioned? - No
What degree of parallelism is set for the table? - none
What version of the Oracle database server software? - 10.2
What is the maximum OS IO size that is supported? (e.g. 1 MB) - I
don't know
What is the db_file_multiblock_read_count set to? (e.g. 16) - it is 16
Have you gathered system stats under workload? - I don't think so but
the workload should be minimal there will be no end-users.
The db_block_size would also be of interest, as would the tablespace
block size if it differs from the db_block_size. - db block size is 8k
I'm not sure what the tablespace block size is.

To give a little more background information - we are moving a DB from
Sun to Linux. In the process, we are dropping and re-creating the
indexes. Most of them are fast - this particular one is not. I do
not own the DB but was putting in my two cents to try to help speed
things up. I now have a good amount of research to do - but that's OK!

Thank you all again!

. . . Tom

On Wed, 14 May 2008 10:33:24 -0700, Mark D Powell wrote:

> I would set work area management policy to manual, set the largest
> practical sort_area_size possible and just run one index build at a
> time.

This no longer applies to 10.2 and later. There is a new sort algorithm
in 10.2, specifically rewritten to make use of larger memories, available
through PGA_AGGREGATE_TARGET. There is a paper about this here:
http://tinyurl.com/5hc3l



--
http://mgogala.freehostia.com

On Wed, 14 May 2008 10:33:24 -0700, Mark D Powell wrote:


> I would set work area management policy to manual, set the largest
> practical sort_area_size possible and just run one index build at a
> time.

That wouldn't be advisable in case of Oracle 10.2. It has a new and
improved sorting algorithm, specifically improved to use large memory
areas and dynamic PGA memory.



--
http://mgogala.freehostia.com