 PL/SQL datawarehouse process: stability issues [message #408286] |
Mon, 15 June 2009 10:38 |
ees_db
Messages: 1
Registered: June 2009
|
Junior Member |
|
|
Hi, we're developing a person data consolidation process with pl/sql, comparable to a small data warehouse.
- >1 run per day
- target runtime ~ 1h
- ~ 1,5 million records to process each time and consolidate with base data
- ~ 5000 LOC within a package
- ~ 5 tables read+write involved, ~ 15 read only (configuration, core data, ...)
the process consists of multiple process steps like
- loading of multiple files in the same format to a table with sqlloader
- data normalizing, enrichment, referencing to core data, archiving, versioning,...
- consolidating with base data
the most steps are all executed on one single table and affects the most data of it - but all in sequence.
now we have big stability issues on the production system at our customers system (10.2.0.3.0), the development system inhouse (10.2.0.4.0) works just fine.
- some single update statements won't complete or take nearly forever to complete (longops estimates dayS), the process has to be terminated. the same statements take a few seconds on the development server
- the production server has much more resources: more cpus, more ram, more hdd, bigger tablespaces, ...
- we can't reduce the problems to specific statements, but only updates that reach a certain level of complexity (like subselects)
- the continuous gathering of statistics between each process step reduced the problems (in many cases the optimizer has just choosen a horrible execution plan),
but of course gathering stats so often greatly extends process time and i wouldn't always solve the problem!
- outlines are not a solution either because there are more or less dynamic statements (execute immediates) generated by user configuration
looks like the optimizer can't handle multiple updates on a single table, gather stats helped but wouldn't solve the problem completely.
someone has an idea what could cause these problems? maybe a oracle parameter set wrong or something.
is there a bug in 10.2.0.3 that could cause such problems fixed by 10.2.0.4?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
