OS - Hpux
Oracle DB - Oracle 10G
Tablespace - ASSM
I need to add columns to some of the Big tables in my Data warehouse with DEFAULT clause,
when I performed the "ALETR TABLE" in normal methods I observed that addition of columns although can occur quickly the updates which happen recursively take a lot of time. I wanted to know if there is a way to Boost performance.
{please see the below sample}
Although I am not sure but I am investigating on possibilities like :--
1. Table Reorg
2. Adding suitable Hints
3. Are there any special parameters which can be enabled for the time being and reverted to original settings at a later stage
Sample data for a Big7 table with 221955816 rows when I performed a ALTER TABLE on my DB. Please suggest me ways to improve performance.
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
617,658,999 0 N/A 23.3 8970.45 14634.86 6cxffg6g4vu8k
Module: MAGG
ALTER TABLE "Big7" ADD ("Watt" VARCHAR2 (000003) DEFAULT ' ' N
OT NULL , "Quant" NUMBER (000017, 000003) DEFAULT 0 NOT NULL , "BASE_UO
M" VARCHAR2 (000009) DEFAULT ' ' NOT NULL , "Jumps" VARCHAR2 (000054) DEFAULT
' ' NOT NULL )
617,658,469 0 N/A 23.3 8970.42 14634.84 atmt8vbdc4ftb
update "Big7" set "Watt"=' ' ,"Quant"=0 ,"BASE_UOM"='
' ,"Jumps"=' '
Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
15,671,211 0 N/A 15.4 8970.45 14634.86 6cxffg6g4vu8k
Module: MAGG
ALTER TABLE "Big7" ADD ("Watt" VARCHAR2 (000003) DEFAULT ' ' N
OT NULL , "Quant" NUMBER (000017, 000003) DEFAULT 0 NOT NULL , "BASE_UO
M" VARCHAR2 (000009) DEFAULT ' ' NOT NULL , "Jumps" VARCHAR2 (000054) DEFAULT
' ' NOT NULL )
15,671,207 0 N/A 15.4 8970.42 14634.84 atmt8vbdc4ftb
update "Big7" set "Watt"=' ' ,"Quant"=0 ,"BASE_UOM"='
' ,"Jumps"=' '
Regards,
Valli