Oracle 10g versus 9i major I/O problem

We have a query that is run once a year to move all accounting data to the next fiscal year:

INSERT INTO COST_CD_ACCT
( COST_CD_ACCT.nr_id,
  COST_CD_ACCT.suffix_nr_id,
  COST_CD_ACCT.gl_cost_cd,
  COST_CD_ACCT.gl_acct_nr_id,
  COST_CD_ACCT.cc_acct_yr_tm,
  COST_CD_ACCT.cc_acct_mo_tm,
  COST_CD_ACCT.cc_acct_last_act_usr_id,
  COST_CD_ACCT.cc_acct_last_act_dt,
  COST_CD_ACCT.cc_acct_stat_cd,
  COST_CD_ACCT.acct_am )
SELECT
  COST_CD_ACCT.nr_id,
  COST_CD_ACCT.suffix_nr_id,
  COST_CD_ACCT.gl_cost_cd,
  COST_CD_ACCT.gl_acct_nr_id,
  :Incc_acct_yr_tm_0,
  0,
  'BATCH',
  SYSDATE,
  'A',
  NVL( SUM( COST_CD_ACCT.acct_am ), 0 )
  FROM
  COST_CD_ACCT
WHERE
  ( COST_CD_ACCT.cc_acct_mo_tm IN( 1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9 ) AND
  COST_CD_ACCT.cc_acct_yr_tm = :Incc_acct_yr_tm_1 ) OR ( COST_CD_ACCT.cc_acct_mo_tm IN( 0,
  10,
  11,
  12 ) AND
  COST_CD_ACCT.cc_acct_yr_tm = :Incc_acct_yr_tm_2 )
GROUP BY
  COST_CD_ACCT.nr_id,
  COST_CD_ACCT.suffix_nr_id,
  COST_CD_ACCT.gl_cost_cd,
  COST_CD_ACCT.gl_acct_nr_id

Binding <:Incc_acct_yr_tm_0> with <2007>
Binding <:Incc_acct_yr_tm_1> with <2007>
Binding <:Incc_acct_yr_tm_2> with <2006>

The query result is 13,748,977 rows inserted.

Here is the table:

desc COST_CD_ACCT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NR_ID                                     NOT NULL VARCHAR2(14)
 SUFFIX_NR_ID                              NOT NULL VARCHAR2(1)
 GL_COST_CD                                NOT NULL VARCHAR2(12)
 GL_ACCT_NR_ID                             NOT NULL VARCHAR2(7)
 CC_ACCT_YR_TM                             NOT NULL NUMBER(4)
 CC_ACCT_MO_TM                             NOT NULL NUMBER(2)
 CC_ACCT_LAST_ACT_USR_ID                            VARCHAR2(8)
 CC_ACCT_LAST_ACT_DT                                DATE
 CC_ACCT_STAT_CD                                    VARCHAR2(1)
 ACCT_AM                                            NUMBER(14,2)

Under orcale 9i this query runs in 27:27.

Under oracle 10g this query runs in 11:37:37 -- over 11 hours longer!!

The machine is an hp9000 runing 11.11.  It has four 875 mhz processors and 16 GB  ram.  The Oracle database files are on a SAN.  There were no hardware changes.  The only change was to upgrade to 10g.  There appears to be no memory or cpu contention.  The SAN reports very high I/O as would be expected.

During the run of this query we turn off all triggers on the table and drop all but the primary key indexes.  We also turn off archving because we do a backup before and after the query so archiving is unnecessary.

I think the problem lies in the amount of I/O that Oracle 10g overhead requires versus 9i.  Using GlancePlus we see the following I/O for the processes that seem to be involved:

Under 9i:
oracleret, user process, 6.56 GB I/O
ora_dbw0_ret, oracle process, 8.66 GB I/O
ora_lgwr_ret, oracle process, 7.16 GB I/O

Under 10g
oracleret, user process, 154 GB I/O, 11,518,878 phisical reads, 52,231 phisical writes
ora_dbw0_ret, oracle process, 158 GB I/O, 2,159,586 phisical reads, 14,001,896 phisical writes
ora_lgwr_ret, oracle process, 41.8 GB I/O, 60,531 phisical reads, 372,950 phisical writes

Here is the init.ora file:

O7_DICTIONARY_ACCESSIBILITY=FALSE
audit_sys_operations=TRUE
audit_trail=TRUE
background_dump_dest=/usr01/dba/admin/ret/bdump
compatible=10.2.0
control_files=/usr10/ORACLE/ret/retcntrl1.ctl, /usr10/ORACLE/ret/retcntrl2.ctl, /usr10/ORACLE/ret/retcntrl3.ctl
core_dump_dest=/usr01/dba/admin/ret/cdump
db_block_size=8192
db_cache_size=400M  #872415232
db_file_multiblock_read_count=32
db_files=250
db_name=ret
db_writer_processes=1
disk_asynch_io=FALSE
dml_locks=500
fast_start_mttr_target=300
global_names=TRUE
job_queue_processes=2
log_archive_dest='/usr08/ORACLE/ret/arch'
log_archive_format=ret_%s%t%r.arc
log_buffer=245760
max_dump_file_size=10240
nls_date_format=MMDDYYYY
open_cursors=400
os_roles=FALSE
processes=450
remote_login_passwordfile=EXCLUSIVE
resource_limit=TRUE
sga_max_size=4000000000
sga_target=2000000000
shared_pool_size=268435456
sort_area_retained_size=65536
sort_area_size=1024000
sql92_security=TRUE
timed_statistics=TRUE
undo_management=AUTO
undo_retention=900
user_dump_dest=/usr01/dba/admin/ret/udump
utl_file_dir=*
java_pool_size=67108864
streams_pool_size=50331648
large_pool_size=8388608
pga_aggregate_target=25165824
session_max_open_files=20

I am not the DBA but am the programmer in charge of making the application work.  The DBAs so far have not been able to help and Oracle support has provided no insight.  The DBAs have not been able to get Enterprise Manager configured because they forgot some password ...

Is there an automatic oralce feature that is causing this overhead?  Is there something we can tune, change or turn off?Start Free Trial