Home » Other » General » PL/SQL datawarehouse process: stability issues (Live: & Development:
icon5.gif  PL/SQL datawarehouse process: stability issues [message #408286] Mon, 15 June 2009 10:38
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 (, the development system inhouse ( 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 that could cause such problems fixed by
Previous Topic: Database History
Next Topic: Service and Instance Query
Goto Forum:

Current Time: Thu Aug 18 15:54:53 CDT 2022