Home » RDBMS Server » Performance Tuning » Unable to update newly added column in existing table (Oracle 9.2.0.6, OS - AIX 6.1)
Unable to update newly added column in existing table [message #585050] Tue, 21 May 2013 09:51 Go to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Hi,

I am facing some challenge while running update query on newly added column in existing table.

Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1

No of records in table : 12572770

Below are the step i followed.

1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.

SQL> desc testtablename
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMNNAME1                                        VARCHAR2(8)
 COLUMNNAME2                                        CHAR(1)
 COLUMNNAME3                                        CHAR(1)
 COLUMNNAME4                                        VARCHAR2(8)
 COLUMNNAME5                                        VARCHAR2(11)
 COLUMNNAME6                                        NUMBER(9)
 COLUMNNAME7                                        VARCHAR2(10)
 COLUMNNAME8                                        VARCHAR2(10)
 COLUMNNAME9                                        VARCHAR2(9)
 COLUMNNAME10                                       VARCHAR2(6)
 COLUMNNAME11                                       VARCHAR2(80)
 COLUMNNAME12                                       VARCHAR2(80)
 COLUMNNAME13                                       VARCHAR2(80)
 COLUMNNAME14                                       LONG
 COLUMNNAME15                                       NUMBER(5)
 COLUMNNAME16                                       VARCHAR2(2)
 COLUMNNAME17                                       VARCHAR2(15)
 COLUMNNAME18                                       VARCHAR2(15)
 COLUMNNAME19                                       DATE
 COLUMNNAME20                                       NUMBER(5)
 COLUMNNAME21                                       VARCHAR2(15)
 COLUMNNAME22                                       VARCHAR2(15)
 COLUMNNAME23                                       DATE
 COLUMNNAME24                                       DATE
 COLUMNNAME25                                       VARCHAR2(2)
 COLUMNNAME26                                       NUMBER(5)
 COLUMNNAME27                                       DATE
 COLUMNNAME28                                       VARCHAR2(8 CHAR)

SQL> Alter table testtablename add ( COLUMNNAME29 varchar2(8));

Table altered.

SQL> select index_name, column_position,  column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;

INDEX_NAME                     COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME                            1 COLUMNNAME24
 



Problem faced & My analysis

1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.

Kindly let me know what is the way forward to identify the issue.

Below are the queries completed successfully


Update  Testtablename
Set     Columnname29       = '1'
Where   Columnname24       >=  To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And     Columnname24       < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')


Update  Testtablename
Set     Columnname1        = '1111'
Where   Columnname24       >=  To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And     Columnname24       < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')



Below are the queries hanging in database


Update  Testtablename
Set     Columnname29        = Columnname1
Where   Columnname24       >=  To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And     Columnname24       < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Update  Testtablename
Set     Columnname29        = '1111'
Where   Columnname24       >=  To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And     Columnname24       < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')



Below is character set in database

SQL> select * from v$nls_parameters;
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MM-YYYY
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 US7ASCII
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE



Please let me know for further inputs/clarification if required.

Thanks & Regards
Ramachandran S M
Re: Unable to update newly added column in existing table [message #585051 is a reply to message #585050] Tue, 21 May 2013 09:57 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Upgrade. Not only is 9i unsupported but 9.2.0.6 wasn't even its terminal release. That was 9.2.0.8, if I remember correctly.
Re: Unable to update newly added column in existing table [message #585052 is a reply to message #585051] Tue, 21 May 2013 10:03 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Thanks gazzag, but in my scenario i could not upgrade my oracle database.
Re: Unable to update newly added column in existing table [message #585057 is a reply to message #585050] Tue, 21 May 2013 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What was the previous value in "Columnname1"? A single 1?
You can follow the statement querying the size it uses in rollback/undo segments: v$transaction.USED_UBLK.

Regards
Michel
Re: Unable to update newly added column in existing table [message #585060 is a reply to message #585057] Tue, 21 May 2013 11:17 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Hi Michel,

Thanks.

The value vary from 1 to 99999999 in Columnname1.

Quote:
What was the previous value in "Columnname1"? A single 1?


When i checked v$transaction.USED_UBLK is not moving beyond 3 or 4 during execution of update statement.

Quote:
You can follow the statement querying the size it uses in rollback/undo segments: v$transaction.USED_UBLK.


Thanks & Regards
Ramachandran S M
Re: Unable to update newly added column in existing table [message #585062 is a reply to message #585060] Tue, 21 May 2013 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$lock to see if it is not waiting for someone else.

Regards
Michel
Re: Unable to update newly added column in existing table [message #585064 is a reply to message #585062] Tue, 21 May 2013 11:50 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Thanks Michel

No, the table is not waiting

Quote:
Query v$lock to see if it is not waiting for someone else.


Also in above provided details, i mentioned existing column is updated success.

Quote:
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.


I have checked alert log, lock for table, event for the query and v$transaction.USED_UBLK, not able to get any clue that why query is hanging in database.

Thanks & Regards
Ramachandran S M
Re: Unable to update newly added column in existing table [message #585065 is a reply to message #585064] Tue, 21 May 2013 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;

content of trace file will reveal what is occurring within the SQL statement

tail -f <trace_file>
Re: Unable to update newly added column in existing table [message #585066 is a reply to message #585065] Tue, 21 May 2013 12:07 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Hi,

I have enabled session level trace and executed my update statement.

when i checked the trace file, below is the content.



ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

WAIT #1: nam='db file sequential read' ela= 9929 p1=3114 p2=41560 p3=1
WAIT #1: nam='db file sequential read' ela= 7747 p1=3114 p2=41592 p3=1
WAIT #1: nam='db file sequential read' ela= 13767 p1=3114 p2=42799 p3=1
WAIT #1: nam='db file sequential read' ela= 7638 p1=3112 p2=100292 p3=1
WAIT #1: nam='db file sequential read' ela= 9991 p1=3113 p2=5325 p3=1
WAIT #1: nam='db file sequential read' ela= 8202 p1=3113 p2=5241 p3=1
WAIT #1: nam='db file sequential read' ela= 453 p1=3113 p2=5246 p3=1
WAIT #1: nam='db file sequential read' ela= 6824 p1=3112 p2=83738 p3=1
WAIT #1: nam='db file sequential read' ela= 6973 p1=3112 p2=100283 p3=1
WAIT #1: nam='db file sequential read' ela= 18841 p1=3114 p2=80326 p3=1
Re: Unable to update newly added column in existing table [message #585068 is a reply to message #585066] Tue, 21 May 2013 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Example (Index Scan):
WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1

WAITing under CURSOR no 1
for "db file sequential read"
We waited 0.04 seconds for a single block read (p3=1)
from file 4, block 1224
Re: Unable to update newly added column in existing table [message #585118 is a reply to message #585068] Wed, 22 May 2013 05:42 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Thanks Swan

I have identified the issue, while executing update query on newly added column row migration is happening. i.e. chained_rows

Thanks & Regards
Ramachandran S M
Re: Unable to update newly added column in existing table [message #585161 is a reply to message #585118] Wed, 22 May 2013 09:20 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Hi Swan,

I want to know is there anyway to speedup the update query in chained_rows, or at least subsequent update query can be speedup?

Thanks & Regards
Ramachandran S M
Re: Unable to update newly added column in existing table [message #585162 is a reply to message #585161] Wed, 22 May 2013 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Consider to "rebuild" the table so that chained rows no longer exist.
Re: Unable to update newly added column in existing table [message #585164 is a reply to message #585162] Wed, 22 May 2013 09:29 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Thanks Swan
Re: Unable to update newly added column in existing table [message #586067 is a reply to message #585118] Mon, 03 June 2013 04:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
s.m.ramachandran wrote on Wed, 22 May 2013 16:12

I have identified the issue, while executing update query on newly added column row migration is happening. i.e. chained_rows


I wonder how it is chained row issue? With just 29 columns and all columns except (COLUMNNAME14 LONG) have size not more than 80 bytes.

Can you provide the output of the following query:-
1. SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0 and table_name='testtablename';
2. ANALYZE TABLE tablename LIST CHAINED ROWS;
3. SELECT * FROM chained_rows;

I guess, COLUMNNAME14 should be the culprit.
Re: Unable to update newly added column in existing table [message #586117 is a reply to message #586067] Mon, 03 June 2013 12:52 Go to previous message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Hi,

The environment is no more, so i could not get the output.

Quote:
Can you provide the output of the following query:-
1. SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0 and table_name='testtablename';
2. ANALYZE TABLE tablename LIST CHAINED ROWS;
3. SELECT * FROM chained_rows;


Yes, COLUMNNAME14 caused the issue.

Quote:
I guess, COLUMNNAME14 should be the culprit.


Also I have identified chained rows by executing ANALYZE LIST for the table.

Thanks & Regards
Ramachandran S M
Previous Topic: LogMiner Doubt
Next Topic: How to avoid repeat where clause in oracle sql
Goto Forum:
  


Current Time: Fri Mar 29 04:57:13 CDT 2024