Home » RDBMS Server » Server Administration » How to calcutate undo needed for operation (alter table add column) (Oracle 10g release 10.2.0.4 Windows )
How to calcutate undo needed for operation (alter table add column) [message #422397] Wed, 16 September 2009 01:51 Go to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member
Hello everyone!

There is a problem to calculate an approximate space needed in UNDO tablespace for certain operation. The operation is adding a new column to table with default value: " alter table SOME_TABLE ADD ("Test" NUMBER(1)); ". I tested that DML-statement on test table and it took over 3 hours to complete, and needed about 25Gb of UNDO tablespace for undo-data. Table size is ~6Gb and it has two indexes ~3Gb+4Gb. Table has ~270mln rows.

So the questions are:
1. Is it possible to calculate amount of free space needed in UNDO-tablespace FOR THAT SPECIFIC OPERATION(add column) based on table+indexes size/row count? (e.g. we need about 2x of table+indexes size for undo for that kind of operation)
2. Can we reduce the tablespace size needed in any way? (e.g. "create table as select" or in any other way)

P.S. I calculated recommended undo-tablespace size for that db, and it came somewhere about 25GB, but that's not what I need.

P.P.S And actualy, what is the best practice of operating with large tables? (alter add column)

DBMS is Oracle 10g release 10.2.0.4.

any thoughts?

Report message to a moderator

Re: How to calcutate undo needed for operation (alter table add column) [message #422412 is a reply to message #422397] Wed, 16 September 2009 04:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
I calculated recommended undo-tablespace size for that db, and it came somewhere about 25GB

How did you derive that number?
Did you consider UNDO_RETENTION?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#ADMIN013

Report message to a moderator

Re: How to calcutate undo needed for operation (alter table add column) [message #422419 is a reply to message #422412] Wed, 16 September 2009 04:53 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member
Yes, I did.

I used these queries:

1. For an optimal undo_retention:

SELECT
d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';

2. For optimal undo size:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';

// ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
// ------------------------ ------------------------- ------------------------
// 23188 8300 23773,5559895833333333333333333333333333

Report message to a moderator

Re: How to calcutate undo needed for operation (alter table add column) [message #422424 is a reply to message #422419] Wed, 16 September 2009 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: How to calcutate undo needed for operation (alter table add column) [message #422426 is a reply to message #422424] Wed, 16 September 2009 06:20 Go to previous message
JuicyFruit
Messages: 34
Registered: September 2009
Member
Yes, I did.

I used these queries:

1. For an optimal undo_retention:

SELECT
   d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
   SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", 
   ROUND((d.undo_size / (to_number(f.value) *
   g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM
(
   SELECT
      SUM(a.bytes) undo_size
   FROM
      v$datafile a,
      v$tablespace b,
      dba_tablespaces c
   WHERE
      c.contents = 'UNDO'
      AND c.status = 'ONLINE'
      AND b.name = c.tablespace_name
      AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
   SELECT
      MAX(undoblks/((end_time-begin_time)*3600*24))                  undo_block_per_sec
   FROM
      v$undostat
) g 
   WHERE
      e.name = 'undo_retention'
      AND f.name = 'db_block_size';


2. For optimal undo size:

SELECT
   d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
   SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
   (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
   g.undo_block_per_sec) / (1024*1024)
   "NEEDED UNDO SIZE [MByte]"
FROM
(
   SELECT
      SUM(a.bytes) undo_size
   FROM
      v$datafile a,
      v$tablespace b,
      dba_tablespaces c
   WHERE
      c.contents = 'UNDO'
      AND c.status = 'ONLINE'
      AND b.name = c.tablespace_name
      AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT
   MAX(undoblks/((end_time-begin_time)*3600*24))
   undo_block_per_sec
FROM
   v$undostat
) g
WHERE
   e.name = 'undo_retention'
   AND f.name = 'db_block_size';

// ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
// ------------------------ ------------------------- ------------------------
// 23188 8300 23773,5559895833333333333333333333333333

Report message to a moderator

Previous Topic: problem bringing ASM instance up
Next Topic: Oracle Installation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 01 16:48:52 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.