There are literally millions of bits of data and pages in books on how to tune Oracle and PL/SQL. Most important to BI professionals is the need to beat the clock, and have their data ready by start of business(8am) at a minimum. Of course if you’re a right time environment you have even more stringent requirements. It’s likely your data processing takes a while and OWB can give you some great information on where it’s spending time. This is useful if you’re trying to pick a few mappings to “tune” and get the most bang for the buck.
Connect to the OWB_RUNTIME schema, or use select access from another use such as OWB_USER.
AND task_type = ‘PLSQL’
AND created_on >= to_date(’12/10/2004′, ‘MM/DD/YYYY’)
group by OBJECT_NAME
order by avg desc;
Expressed in Seconds… Yields the following results (maps changed to protect the innocent)
NAME MIN MAX SUM AVG COUNT map1 0 31988 165372 20671 8 map2 5494 68905 135111 19301 7 map3 1672 3509 20542 2567 8 map4 316 3511 14502 1812 8 map5 1018 2170 13089 1636 8 map6 436 1353 6784 848 8 map7 478 1272 6476 809 8 map8 309 2243 5351 668 8 ..................
You’ll see this identifies several candidates for tuning. You also have an idea of what kind of performance gain you can hope to expect. If you improve the avg time of map1 by 10% you’ll save yourself approximatley 20671 * .9 / 60 = approx 35min per execution. This is not hard and fast, seeing as though some tasks run in parallel (if map 4 is always waiting for map3 in a process flow, you won’t cut down your time).