OraFAQ Forum: Performance Tuning » Improve performance of create statements on a highly fragmented disk

Home » RDBMS Server » Performance Tuning » Improve performance of create statements on a highly fragmented disk

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Improve performance of create statements on a highly fragmented disk [message #483760]

Wed, 24 November 2010 07:39

steffeli
Messages: 112
Registered: July 2006

Senior Member

Hi,

On Oracle 10g, I create, delete and drop a lot of tables. Therefore, the disk is highly fragmented.

The execution of a very simple create statement takes more than a minute. If I execute the same statement but first truncate the table and insert the data, it takes less than a second!

I think this has to do with the high fragmentation of the disk. Obviously, I can defragment the disk, but I will always have a high fragmentation since I use a lot of create, delete and drops.

Any idea how I can improve the performance of create statements on highly fragmented disks?

Thanks, Stefan

[Updated on: Wed, 24 November 2010 07:41]

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483762 is a reply to message #483760]

Wed, 24 November 2010 07:48

Frank Naude
Messages: 4593
Registered: April 1998

Senior Member

Do you use locally managed tablespaces (LMT) with ASSM?
It will most likely solve your problem.

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483763 is a reply to message #483760]

Wed, 24 November 2010 07:51

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I think this has to do with the high fragmentation of the disk.

Don't think, prove it.
The rest is waste of time until you don't prove (at least for yourself).

Quote:

Any idea how I can improve the performance of create statements on highly fragmented disks?

If the reason, as you say, is disk fragmentation and don't fix the cause then you can't have improvement anyway.
If you can have improvement WITHOUT doing anything about the cause you suspect then this one is not the real cause.
Just logic.

Regards
Michel

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483769 is a reply to message #483763]

Wed, 24 November 2010 08:11

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I would have thought that disc fragmentation is unlikely to be the cause - each datafile is a single file on disk, and Oracle handles the allocation of space inside the datafiles. Creating and dropping tables will not increase disk fragmentation.

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483772 is a reply to message #483762]

Wed, 24 November 2010 08:18

steffeli
Messages: 112
Registered: July 2006

Senior Member

Dear Frank, thanks for your answer. Unfortunately, I'm just a user and not an administrator. Running the statement

SELECT tablespace_name, extent_management, segment_space_management  FROM dba_tablespaces

returns an error ORA-00942: table or view does not exists

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483774 is a reply to message #483772]

Wed, 24 November 2010 08:23

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Are you doing create table, waiting the time, then immediately truncating/inserting?

You might be experiencing "false" performance improvements if this is the case as most/all of the data for the insert will be in the buffer whereas this is less likley for the create table initially.

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483777 is a reply to message #483774]

Wed, 24 November 2010 08:35

steffeli
Messages: 112
Registered: July 2006

Senior Member

Hi Roachcoach, no, this is defenitely not the case.

Report message to a moderator

Re: Improve performance of create statements on a highly fragmented disk [message #483796 is a reply to message #483777]

Wed, 24 November 2010 09:33

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Actually, unless I'm mistaken, disk performance shouldnt (seriously) impact create table as/insert - Doesnt DBW write independently of the code executing? Unless you're experiencing poor read speed in the create, but from your last reply to me that would suggest its not the case.

I could be wrong, its been a while since I looked at this in depth, but I think that the CTAS and insert should only be disc write bottlenecked by the log buffer disc (and if you're not commiting the insert that might appear to remove that bottleneck).

Doubtless one of the other folks here can confirm/deny.

[Updated on: Wed, 24 November 2010 09:34]

Report message to a moderator

Previous Topic:	Sql Taking More CPU time (merged)
Next Topic:	Performance issue for distributed transaction (2PC)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 20 17:14:12 CST 2025