Re: SQL Loader Performance issue after Oracle8i

Hi Fraser,

Thanks for the reply.

Yes we did statistics for the object referenced by trigger still the performance is same. Also the object referenced by trigger is a partition table. All indexes are present in the new database. Still the execution plan shows the cost after enforcing rule.

1. Before enforcing the rule

select * from summary_usage_logs
where pmonth=1
and to_char(last_day_month,'DD/MM/YYYY')='31/01/2002' AND ROWNUM=1; Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=327 Bytes=313
          92)

   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SUMMARY_USAGE_LOGS' (C
          ost=3 Card=327 Bytes=31392)

   3    2       INDEX (RANGE SCAN) OF 'SUMMARY_USAGE_LOGS_U1' (UNIQUE)
           (Cost=2 Card=327)

2. After enforcing the rule

select /*+ rule */ * from summary_usage_logs  where pmonth=1
 and to_char(last_day_month,'DD/MM/YYYY')='31/01/2002'  AND ROWNUM=1; Execution Plan

   0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=3 Card=327 Bytes
          =31392)

   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SUMMARY_USAGE_LOGS' (C
          ost=3 Card=327 Bytes=31392)

   3    2       INDEX (RANGE SCAN) OF 'SUMMARY_USAGE_LOGS_U1' (UNIQUE)
           (Cost=2 Card=327)

Thanks,
Guru

"Fraser McCallum" <fmcc_at_removetoemail_odbaguru.com> wrote in message news:KSrt8.36706$uR5.85572_at_newsfeeds.bigpond.com...
> Prior to your load did you computed statistics for all the objects
> referenced by the trigger? If computing statistics does not improve things
> and you were using the rule based optimizer in your pre-migration Oracle
> system then you may need to add hints to your trigger code to force a rule
> based optimizer plan. One last thought, you did check that all your
indexes
> are still present and valid in the new system?
>
> Kind Regards,
>
> Fraser McCallum
> MVP Oracle Administration
> www.brainbench.com
>
> "Rajendran Kumaraguru" <rajendran.k_at_pacific.net.sg> wrote in message
> news:a95gtg$4ip$1_at_newton.pacific.net.sg...
> > Hi,
> >
> > I have a performance issue while loading the data thru sql loader. SQL
> > Loader loads the data 30000 records per hour to a global partitioned
table
> > (it has about 120 million records).
> > There is a database trigger attached to the loading table and which is
> > inserting/updating some records in another table which has 10 million
> > records. We have migrated our database to Oracle8i, after the migration
> it
> > is taking more than one hour to complete the loading, before the
oracle8i
> > migration it took only less than 20 mins time, also there is no changes
in
> > the control file and the loading table so far.
> >
> > Can you please help me any tuning parameter that I have to add.
> >
> >
> > Thanks in advance.
> > Guru
> >
> >
> >
> >
> >
>
>
Received on Tue Apr 16 2002 - 22:01:18 CDT