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<\/i>.<\/p>\n
Connect to the OWB_RUNTIME<\/i> schema, or use select access from another use such as OWB_USER.
\n
\nselect
\n OBJECT_NAME name,
\n trunc(min(elapse_time)) min,
\n trunc(max(elapse_time)) max,
\n trunc(sum(elapse_time)) sum,
\n trunc(avg(elapse_time)) avg,
\n count(elapse_time) count
\nfrom all_rt_audit_executions
\nwhere 1=1
\nAND task_type = ‘PLSQL’
\nAND created_on >= to_date(’12\/10\/2004′, ‘MM\/DD\/YYYY’)
\ngroup by OBJECT_NAME
\norder by avg desc;<\/i><\/p>\n
Expressed in Seconds… Yields the following results (maps changed to protect the innocent)<\/i><\/p>\n 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 <\/b>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).<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/46"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=46"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/46\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\nNAME\tMIN\tMAX\tSUM\tAVG\tCOUNT\nmap1\t0\t31988\t165372\t20671\t8\nmap2\t5494\t68905\t135111\t19301\t7\nmap3\t1672\t3509\t20542\t2567\t8\nmap4\t316\t3511\t14502\t1812\t8\nmap5\t1018\t2170\t13089\t1636\t8\nmap6\t436\t1353\t6784\t848\t8\nmap7\t478\t1272\t6476\t809\t8\nmap8\t309\t2243\t5351\t668\t8\n ..................\n<\/pre>\n