Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ALTER TABLE MODIFY ... Redo-Logs, Will this statemtent generate Redo Logs or not?
Jens
post Jul 19 2010, 09:41 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 19-July 10
Member No.: 43,508



Hello,

i've got a Table with 13 million datasets. The table contains a column
YWERT NUMBER(6,3) NOT NULL. I need to change the Column to Number(10,5),
so my question is, will the statement
ALTER TABLE TBLSSERESAVBEINZEL MODIFY(YWERT NUMBER(10,5));
generate redo logs or will it not, since it is DDL?

Thanx!
Go to the top of the page
 
+Quote Post
SteveC
post Jul 19 2010, 10:12 AM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



What are datasets?

Are any of your "datasets" being updated?
Go to the top of the page
 
+Quote Post
burleson
post Jul 19 2010, 11:46 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,730
Joined: 26-January 04
Member No.: 13



Hi Jens,

>> i've got a Table with 13 million datasets.

No you don't . . .

A dataset is a name for an OS file!

For example, the files referenced in dba_data_files are dataset . . .

***********************************************
>> will the statement ALTER TABLE TBLSSERESAVBEINZEL MODIFY(YWERT NUMBER(10,5)); generate redo logs or will it not, since it is DDL?

Is this a homework question?

Why not just try it and see for yourself?

Trust me, you will know right away!

**********************************************
>> ALTER TABLE TBLSSERESAVBEINZEL MODIFY(YWERT NUMBER(10,5));

It may be faster to make a new table and copy-in the values with parallel CTAS . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Steve
post Jul 19 2010, 12:52 PM
Post #4


Advanced Member
***

Group: Members
Posts: 618
Joined: 22-January 06
From: Virginia Beach, VA
Member No.: 3,560



The DDL statement itself will go to redo for the purpose of recovery (otherwise a Dataguard or Streams target could have the wrong column size), but the resize operation should not log extra redo. The NUMBER datatype is not fixed width and so it should not generate extra redo for each row.

Notice the use of the word "should". I have not personally tested this, and would advise you to make a small copy of your table and test it. You can turn on autotrace with "set autotrace traceonly" and run your alter command. The statistics after the run will show the amount of redo generated.


--------------------
Steve Karam
Sr. Consultant - Burleson Consulting
Oracle 10g Certified Master / Oracle ACE
http://www.OracleAlchemist.com
steve@orcldba.com
Go to the top of the page
 
+Quote Post
Jens
post Jul 20 2010, 01:30 AM
Post #5


Newbie
*

Group: Members
Posts: 2
Joined: 19-July 10
Member No.: 43,508



Hi Burleson, Hi Steve,

"datasets" was probably a problem in my translation. I guess rows is the better word. In german these two words have the same meaning.
And no, it's not a homework, if the database will get in trouble the whole company i am working for will have a problem.

So i will probably test it with a small copy first and see what happens.

Does the oracle documentation contain an overview of the amount of redo logs generated by an operation? I couldn't find something like that.
Go to the top of the page
 
+Quote Post
burleson
post Jul 20 2010, 08:14 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,730
Joined: 26-January 04
Member No.: 13



Hi,

>> So i will probably test it with a small copy first and see what happens.

That's smart!

FYI, it's a Oracle best practice to have a full-sized test environment, and I would carefully plan this first.

Note:

Size your redo properly: http://www.dba-oracle.com/concepts/redo_log_files.htm

You can dedicate a large undo segment to the job: http://www.remote-dba.net/t_oracle_9i_admi...ck_segments.htm

****************************************************
>> Does the oracle documentation contain an overview of the amount of redo logs generated by an operation?

Yes! See these scripts for moinitoring redo log history:

http://www.dba-oracle.com/tips_oracle_v&am...log_history.htm

When testing for redo log usage, start the job by performing a redo log switch (alter system switch logfile), so you start with a fresh redo log. Then you simply measure the number of archived redo logs generated, plus the remianing space in the current redo log.

Also, remember that the salient measure is the number of redo "blocks" generated, and this depends on your blocksize.

You also need to ensure that your archived redo log doirectory will have enough space . . .

Obviously, the amount of redo is the accumulated "before image" of the number columns, plus the ROWID.

I would SWAG this at 20 bytes per row, and estimate the number of redo blocks.

Karl Reitschuster offers this script for minitoring redo usage:

http://www.orcasoracle.org/

CODE
SELECT Start_Date,
       Start_Time,
       Num_Logs,
       Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
             2) AS Mbytes,
       Vdb.NAME AS Dbname
  FROM (SELECT To_Char(Vlh.First_Time,
                       'YYYY-MM-DD') AS Start_Date,
               To_Char(Vlh.First_Time,
                       'HH24') || ':00' AS Start_Time,
               COUNT(Vlh.Thread#) Num_Logs
          FROM V$log_History Vlh
         GROUP BY To_Char(Vlh.First_Time,
                          'YYYY-MM-DD'),
                  To_Char(Vlh.First_Time,
                          'HH24') || ':00') Log_Hist,
       V$log Vl,
       V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
          Log_Hist.Start_Time;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 28th November 2014 - 12:35 AM