Have you trace the session ?
How's CPU utilisation ?
How's disk utilisation ?
If better performance is obtain with all rows over
choose it means the optimiser favors hash joins over
nested loops.
Instead of bouncing db (and flushing all the caches)
you can set the proper behavior for the session only.
You can set the optimiser_mode and boost
hash_area_size and sort_area_size to high value for
the session running the loads.
Trace the session and identify where you are loosing
time.
HTH
- Cherie_Machler_at_gelco.com a écrit : > We are
running 8.0.4 in a Sun environment. We have
> a very large datamart that
> has
> data loaded on it from another database warehouse
> every night. The data is
> loading
> using PL/SQL packages and procedures. The loads had
> been taking around six
> hours and then suddenly they started not finishing
> after 24 hours seemingly
> overnight.
> This was just before I started working here.
>
> Supposedly, nothing has changed in the code and the
> developer is reluctant to
> change the
> code as it is in production now. We have tried
> adding some indexes and
> refreshing the
> statistics and removing the statistics with a little
> improvement but not enough.
>
> Two nights ago we switched from optimization mode of
> CHOOSE to ALL_ROWS in order
> to maximize throughput for this batch job. We had
> marked improvement going from
> more
> than a 24-hour load to 4 hours. We bounced the
> database in the morning to
> switch back
> to choose during the day. We then bounced the
> database again in the evening and
> ran
> the same load overnight but it had not finished in
> nine hours. Theoretically,
> nothing has
> changed on the system except the bouncing of the
> database to change optimizer
> mode
> and a different set of data (no massive change in
> amount of data).
>
> Any ideas of why this load is so erratic in the
> length of time it takes? We
> can't seem to
> get our arms around it. What should we be checking
>
> Thanks in advance,
>
> Cherie
>
>
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Received on Wed Jul 12 2000 - 09:23:33 CDT
