Home » RDBMS Server » Performance Tuning » Index creation- Performance impact (Oracle 10g)
Index creation- Performance impact [message #352134] Mon, 06 October 2008 05:17 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
We can create index with the PARRALAL clause.
CREATE INDEX TEST_idx 
ON SCOTT.TEST(NAME)

CREATE INDEX TEST_idx 
ON SCOTT.TEST(NAME)
PARALLEL 4 NOLOGGING;

PARALLEL (NOPARALLEL is the default): Multiple processes can work together simultaneously to create an index. 
By dividing the work necessary to create an index among multiple server processes, the Oracle server can 
create the index more quickly than if a single server process 
created the index sequentially. 

NOLOGGING: Using this keyword makes index creation faster because it creates a very minimal amount 
of redo log entries as a result of the creation process. 



So it is nice to have the faster index creation with parallel option.

But does it affects the PERFORMANCE in the database if yes then how much if any idea?

Thanks,

[Updated on: Mon, 06 October 2008 05:20]

Report message to a moderator

Re: Index creation- Performance impact [message #352142 is a reply to message #352134] Mon, 06 October 2008 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, index creation implies locking table and so other waits.
In addition, index creation implies CPU and IO and so other waits.

Regards
Michel
Re: Index creation- Performance impact [message #352147 is a reply to message #352134] Mon, 06 October 2008 05:41 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I am agree with you.

But i would like to know that creating index with PARALLEL cluase has more petrformance impact than creating index normally???

Thanks,
Re: Index creation- Performance impact [message #352150 is a reply to message #352147] Mon, 06 October 2008 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it will.
Having 4 workers do (I hope) more work than 1 worker.

Regards
Michel
Re: Index creation- Performance impact [message #352781 is a reply to message #352134] Thu, 09 October 2008 09:47 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Think of it like this.

You got one guy making 100 chairs. It takes 40 hours. You pay 1 dollar/chair, it costs 100 dollars.

You got 10 guys making 100 chairs. It takes 4 hours. You pay 1 dollar/chair, it costs 110 dollars.

Wait, how does 100 chairs@1dollar/chair make 110 dollars?

Because you need a boss to manage 10 guys, but you don't need a boss to manage 1 guy. You have to pay the boss too.

And so it goes with parallel operations. If nothing else, you need a boss to manage the overall process so it costs more. How much more? depends but usually not that much more.

Now consider this:

1) you got 10 guys in total
2) you got 100 chairs to make and 20 tables to make
3) you put all 10 guys making chairs which takes them four hours

? in the first four hours how many tables did you make? A: none.

Because everybody was making chairs, nobody was left to make tables. Is this good or bad? maybe good maybe bad, depends.

And so it goes with parallel operations. You have a limited amount of resources on your box. Parallel operations in oracle are very good at consuming as much resources as they can. You must decide how fast you want a single task done vs. how many tasks you want worked on concurrently.

If a big parallel job gets started and takes four hours consuming all available resources, then how many 1/2 second queries can you get done in these four hours? A: none.

Is this good or bad? Depends, it is good for the one task that gets done in 1/10th the time. It is bad for the hundreds or possibly thousands of users who have to wait four hours to get their screens painted.

Hope this helps, Kevin
Previous Topic: How to tune UNION
Next Topic: segment tuning
Goto Forum:
  


Current Time: Thu Dec 08 16:30:56 CST 2016

Total time taken to generate the page: 0.28291 seconds