performance impact of large "X.dbf" file in oracle database ?

[Stuart Abramson_2]

One of our DBAs wants to create a LARGE file on the order of 6 GB on one of our filesystems.

(We have the "largefiles" bit set. We already have several "xxx.dbf" files bigger than 2 GB..)

They need a "Temp" table with a LOT of "extents", and he wants it in one big file.

My question is, is there any significant performance impact to creating a file that big? Either way - faster or slower?

[James A. Donovan · Accepted solution]

The thing with Oracle is tablespaces not files. Since your DBA wants only a temp table, then a temporary tablespace should be created. Ideally, on a seperate filesystem from your other datafiles so you can tune the fs differently.

One large file, or multiple smaller files, shouldn't make a big difference. But if you have the room you might as well make it one big file.

The create statement will look something like this...

CREATE TEMPORARY TABLESPACE TEMPX
TEMPFILE '/path/tempx.dbf' SIZE 6217728K AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM

/

[Steven E. Protter]

This file may seem large, but its really not that large.

How the database peforms depends more on the parameters in the init.ora file and how much memory there is on the OS.

Tuning issues such as shmseg and shmmax will also have an impact.

The biggest impact on performance is how the underlying disk is set up. If there are going to be a lot of transactions and the underlying disk is raid 5, its going to stress disk i/o and bandwith on your fiber channel if thats what kind of disk you are using.

If the underlying disk is raid 1 or raid 10, the impact will be less.

The best advice I can give you is to accommodate the dba and make the underlying disk as fast as practical for this operation.

SEP