Extending Tablespace takes long time

[Former Member]

Hello Experts,

We are facing a strange situation on our Oracle Production Database while extending tablespace (adding data files) via BR Space.

Details of the database are as below:

Version : Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

DB Size:  275GB

If we add a data file of size 20GB to a tablespace like PSAPES702I, it takes about 1 hour to perform the extension. However the same operation on the non production systems like QA or pre-prod  take about 5-10 minutes.

We are trying to analyze why the about issue is happening. There are no disk issues as we have had disk experts check the stats while this table extension is going. The disk was hardly having any read/write or contention load.

Requesting your suggestions regarding this, if you have faced similar issues in the past.

Thanks a ton in advance.

Srikishan

[Former Member · Accepted solution]

Hi Srikishan,

I believe that this problem is caused by I/O hardware. This shouldn't be a performance issue at the Oracle level. Try to create a temporary file with the same size (~20Gb) at the same mount point. Then monitor the I/O performance as same as the data file create process.

Best regards,

Orkun Gedik

[Reagan]

Hello

What OS is this ? If this is an Unix system then I recommed you to check the system performance during the time.

If I were you I would look into the Disk activity using the top (topas on AIX) command.

On top of that I would check whether the file systems are created according to the recommendations.

There are recommendations for Oracle file systems for datafiles and online redologs to be created with the cio option.

Please check them.

Cheers

RB

[stefan_koehler]

Hi Srikishan,

to be honest - i have to be sorry for such suggestions. Guesses, wrong assumptions and answers.

1. Temporary data files are sparse files and you will see nearly no I/O by creating them. If you should create a temp file with external tools (like dd) you still have the issue with filesystem cache and I/O options, which can lead to wrong results.

2. It can be an oracle issue as you have various actions on the Oracle dictionary and control files. Just think about contention (like CF enqueue).

Now how to drill down that issue:

Add another data file to the tablespace and monitor the particular session by sampling the wait interface. The easiest and comfortable way to do this is Snapper instead of sampling the data by V$SESSION (or the AWR later on) manually.

Regards

Stefan

[Former Member]

Hi Stefan,

>> be honest - i have to be sorry for such suggestions. Guesses, wrong assumptions and answers.

I don't think that I/O performance problem is a wrong assumption. This can be occured and need to be investigated in detail. If it is not, then need to check Oracle events and more...

Best regards,

Orkun Gedik

[stefan_koehler]

Hi Orkun,

but that is the wrong approach (in my opinion). You make an assumption / guess without any indication. I can also find just as much possible oracle issues for such a behavior, but it would not lead to a solution for that problem.

You also would not go for a surgery without an exact diagnosis by a doctor, right?

Why not using the wait interface first before walking into the wrong direction (one or two layer below)?

Regards

Stefan

[Former Member]

Stefan,

Please don't misunderstand me, but this is your point of view, as you noted which does not mean that this is wrong. This is not a surgery work.

"On this case", I prefer to be sure that all the underlying components are healthy, before I check the top layer components, such as OS or database.

Best regards,

Orkun Gedik

[Former Member]

Hi,

   As well as please check whether this issue is occurring often or rarely..

As suggested earlier, it can not be an oracle issue. Might be I/O issue.

However, in some case, if the oracle is very busy, this issue might occur.

Thanks and Regards,

Vimal

[former_member188883]

Hi Srikishan,

As Orkun mentioned , it is not an oracle issue. It seems to be a problem with your disk configuration and storage subsystem. Could you share the Disk configuration on Production and what is the RAID level defined for disks used for production storage.

Regards,

Deepak Kori