Home » SQL & PL/SQL » SQL & PL/SQL » Adding column to the table is taking long time (oracle 10g , solaris)
| Adding column to the table is taking long time [message #603864] |
Fri, 20 December 2013 01:32  |
prejib
Messages: 126
Registered: March 2009
Location: India
|
Senior Member |
|
|
Hi,
I am adding one new column to the existing table having 71 Millions of records. But the DDL is taking long time to add the column . I am not giving any default value for the column ..Please suggest how can we reduce the timing ? Is there any way to calculate the time taken to add a column to the table in advance?
Thanks ,
prejib
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Adding column to the table is taking long time [message #603872 is a reply to message #603870] |
Fri, 20 December 2013 02:33  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's a test case done in 8i:
SQL> create table t as select object_id from dba_objects;
Table created.
SQL> alter session set events '10046 trace name context forever, level 1';
Session altered.
SQL> alter table t add owner varchar2(30);
Table altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> @v
Version Oracle : 8.1.7.4.1
And the trace file is attached -> only dictionary accesses, all statements are (long lines split by me):
alter session set events '10046 trace name context forever, level 1'
alter table t add owner varchar2(30)
delete from idl_ub1$ where obj#=:1
delete from idl_char$ where obj#=:1
delete from idl_ub2$ where obj#=:1
delete from idl_sb4$ where obj#=:1
delete from error$ where obj#=:1
select o.owner#, u.name, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0)
from dependency$ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 or d.property=2)
and d.d_obj#=o.obj# and o.owner#=u.user# and decode(:3,0,0,o.type#)=:3
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,
clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,
blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,
degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,
spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare3=:35 where obj#=:1insert into
col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,
col#,property,charsetid,charsetform,spare1,spare2)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),
decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,
decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19)
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16
where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5
or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
alter session set events '10046 trace name context off'
-
Attachment: ORA06072.TRC
(Size: 7.05KB, Downloaded 1053 times)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Adding column to the table is taking long time [message #603920 is a reply to message #603919] |
Fri, 20 December 2013 05:37 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
prejib wrote on Fri, 20 December 2013 11:32Thanks for your replies.
Still I don't understand why it took around half an hour for this activity . I have droped the column and added the same again , it took very few seconds.
It sounds like there was something blocking the action rather than the action itself taking a long time.
|
|
|
|
| Re: Adding column to the table is taking long time [message #603946 is a reply to message #603919] |
Fri, 20 December 2013 10:35 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
In addition to pablolee's answer, when you experiment such waits there are few things to first do and enable you to diagnose most of the problems:
A/ query v$session to see the state of the session, the most important columns are (from Reference Manual):
Quote:BLOCKING_SESSION NUMBER Session identifier of the blocking session
WAIT_CLASS VARCHAR2(64) Name of the class of the wait event
WAIT_TIME NUMBER If the session is currently waiting, then the value is 0. If the session is not in a wait, then the value is as follows:
> 0 - Value is the duration of the last wait in hundredths of a second
-1 - Duration of the last wait was less than a hundredth of a second
-2 - Parameter TIMED_STATISTICS was set to false
This column has been deprecated in favor of the columns WAIT_TIME_MICRO and STATE.
SECONDS_IN_WAIT NUMBER If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.
This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO.
STATE VARCHAR2(19) Wait state:
WAITING - Session is currently waiting
WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false
WAITED SHORT TIME - Last wait was less than a hundredth of a second
WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column
B/ if the wait is a lock then query v$lock
C/ if the wait is not a lock then have a look et the following columns in v$session
Quote:SEQ# NUMBER A number that uniquely identifies the current or last wait (incremented for each wait)
EVENT# NUMBER Event number
EVENT VARCHAR2(64) Resource or event for which the session is waiting. See Also: Appendix C, "Oracle Wait Events"
Query it several times if event changes or event stays the same but seq# increase then you are waiting for an operation to complete with short waits for each action (read file, read temp...) of this operation; the event will tell you what are these actions (refer to the Appendix C for more information about the events).
D/ if you want more details about what happens, activate a trace on the session (see DBMS_MONITOR procedure).
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 19 20:03:08 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
