Re: Improve perfromance of my package

C Chang wrote:

> Hi Daniel and Other significants:
> I post the Package that I use the view of a union of 6 tables -
> VP$list_data_all_view. This is actually a synonym based on the real
> view(list_data_all_view) on another schema using a remote link to
> connect. Actually all name prefixed with vp$.. here are remote
> objects. I put my best to put these together. The return of few up to
> few tens of records is quick by this package called from a ASP, that use
> the MS ODBC for Oracle, DNS-less connection. However when it return
> 1000 records and above, it took about 2 minutes upto 4 mniutes for 3625
> records back to a HTML format, and even worse when return back to an
> excel format. By watching the asp responses, it seems that the delay is
> due to this package. Can you suggest any improvement? I am thinking
> combining the c_getPoInfo and c_qty_rcvd into one cursor, probably
> combining the three table (po_heads, po_lines, receipts). Welcome
> anyone's suggestion. My system is on IIS 4 (separate web server), NT 4
> with ORacle 8.1.6 , 1024M ram 700Mhz single PIII CPU.
> I am learning the use of trace of explain for this, meantime.
>
> CREATE OR REPLACE PACKAGE vd2_Report AS
>
> -- Vendor Consumption Report Arrays
> TYPE tbl_counter IS TABLE OF NUMBER
> INDEX BY BINARY_INTEGER;
> TYPE tbl_vendor IS TABLE OF vp$contract_status.vendor_id%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_nsn IS TABLE OF VARCHAR2(13)
> INDEX BY BINARY_INTEGER;
> TYPE tbl_contract_qty IS TABLE OF vp$contract_status.qty%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_um IS TABLE OF vp$contract_status.um%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_qty_ordered IS TABLE OF po_lines.qty%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_source_list IS TABLE OF vp$contract_status.source_list%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_source_lin IS TABLE OF vp$contract_status.source_lin%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_qty_period IS TABLE OF vp$contract_status.qty_period%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE tbl_ref_date IS TABLE OF VARCHAR2(10)
> INDEX BY BINARY_INTEGER;
> TYPE tbl_all_sites IS TABLE OF sites.dflt_contract_id%TYPE
> INDEX BY BINARY_INTEGER;
>
> PROCEDURE getVendorConsumption
> (
> p_site_id IN VARCHAR2,
> p_vendor IN VARCHAR2,
> p_niin IN VARCHAR2,
> o_count IN OUT NOCOPY tbl_counter,
> o_vendor IN OUT NOCOPY tbl_vendor,
> o_nsn IN OUT NOCOPY tbl_nsn,
> o_contract_qty IN OUT NOCOPY tbl_contract_qty,
> o_um IN OUT NOCOPY tbl_um,
> o_qty_ordered IN OUT NOCOPY tbl_qty_ordered,
> o_percentage IN OUT NOCOPY tbl_contract_qty,
> o_source_list IN OUT NOCOPY tbl_source_list,
> o_source_lin IN OUT NOCOPY tbl_source_lin,
> o_qty_period IN OUT NOCOPY tbl_qty_period,
> o_ref_date IN OUT NOCOPY tbl_ref_date,
> o_contract_id IN OUT NOCOPY tbl_all_sites,
> o_qty_rcvd IN OUT NOCOPY tbl_qty_ordered
> );
>
> END vd2_Report;
> /
> CREATE OR REPLACE PACKAGE BODY vd2_Report AS
>
> PROCEDURE getVendorConsumption
> (
> p_site_id IN VARCHAR2,
> p_vendor IN VARCHAR2,
> p_niin IN VARCHAR2,
> o_count IN OUT NOCOPY tbl_counter,
> o_vendor IN OUT NOCOPY tbl_vendor,
> o_nsn IN OUT NOCOPY tbl_nsn,
> o_contract_qty IN OUT NOCOPY tbl_contract_qty,
> o_um IN OUT NOCOPY tbl_um,
> o_qty_ordered IN OUT NOCOPY tbl_qty_ordered,
> o_percentage IN OUT NOCOPY tbl_contract_qty,
> o_source_list IN OUT NOCOPY tbl_source_list,
> o_source_lin IN OUT NOCOPY tbl_source_lin,
> o_qty_period IN OUT NOCOPY tbl_qty_period,
> o_ref_date IN OUT NOCOPY tbl_ref_date,
> o_contract_id IN OUT NOCOPY tbl_all_sites,
> o_qty_rcvd IN OUT NOCOPY tbl_qty_ordered
> )
> IS
>
> v_count NUMBER DEFAULT 0;
> v_sqlstmt VARCHAR2(2000);
> v_counter NUMBER DEFAULT 0;
> v_index NUMBER DEFAULT 0;
> v_length NUMBER DEFAULT 0;
>
> v_vendor vp$contract_status.vendor_id%TYPE;
> v_nsn VARCHAR2(13);
> v_contract_qty vp$contract_status.qty%TYPE;
> v_um vp$contract_status.um%TYPE;
> v_qty_ordered po_lines.qty%TYPE;
> v_contractID sites.dflt_contract_id%TYPE;
> v_percentage vp$contract_status.qty%TYPE;
> v_source_list vp$contract_status.source_list%TYPE;
> v_source_lin vp$contract_status.source_lin%TYPE;
> v_qty_period vp$contract_status.qty_period%TYPE;
> v_niin po_lines.niin%TYPE;
> v_qty_rcvd receipts.qty%TYPE;
> v_ref_date VARCHAR2(14);
> v_sites VARCHAR2(70);
> v_siteContract VARCHAR2(70);
>
> TYPE cursor_T IS REF CURSOR;
> v_cursor cursor_T;
> v_siteList tbl_all_sites;
>
> -- dummy variables for fixing Oracle Uninitializing errors
> t_count tbl_counter;
> t_vendor tbl_vendor;
> t_nsn tbl_nsn;
> t_contract_qty tbl_contract_qty;
> t_um tbl_um;
> t_qty_ordered tbl_qty_ordered;
> t_percentage tbl_contract_qty;
> t_source_list tbl_source_list;
> t_source_lin tbl_source_lin;
> t_qty_period tbl_qty_period;
> t_ref_date tbl_ref_date;
> t_contract_id tbl_all_sites;
> t_qty_rcvd tbl_qty_ordered;
>
>
> CURSOR c_getContractID(p_site IN sites.site_id%TYPE) IS
> SELECT dflt_contract_id
> FROM sites
> WHERE site_id = p_site;
>
> -- This cursor is used to get the sum of the quantity ordered for niin
> CURSOR c_getPoInfo(p_niin IN po_lines.NIIN%TYPE,
> p_date IN VARCHAR2,
> p_vendor IN catalog_items.vendor_id%TYPE,
> p_site IN sites.site_id%TYPE) IS
>
> SELECT SUM(p.qty)
> FROM po_heads ph,po_lines p
> WHERE ph.site_id= p_site
> AND TRUNC(ph.po_date) > TRUNC(TO_DATE(p_date,'MM/DD/YYYY'))
> AND ph.vendor_id = p_vendor
> AND ph.po_id=p.po_id
> AND niin = p_niin;
>
> -- This cursor is used to get the sum of the quantity received for niin
> at specified site
> CURSOR c_qty_rcvd ( i_niin IN receipts.niin%TYPE,
> i_rcvd_date IN VARCHAR2,
> i_site IN receipts.site_id%TYPE ) IS
> SELECT SUM(qty)
> FROM receipts
> WHERE site_id= i_site
> AND TRUNC(rcvd_date) > TRUNC(TO_DATE(i_rcvd_date,'MM/DD/YYYY'))
> AND SAMMS_FLAG = 'N'
> AND niin = i_niin;
>
> -- This cursor gets the reference date which is the original contract
> date
> CURSOR c_getRefDate (p_source_list IN vp$source_lists.list%TYPE) IS
> SELECT DECODE(reference_date,NULL, TO_CHAR
> (source_file_date,'MM/DD/YYYY'),TO_CHAR(reference_date,'MM/DD/YYYY'))
> FROM vp$source_lists
> WHERE list = p_source_list
> AND price_type = 'S';
>
> -- This cursor gets the contracted qty and um from list_data_all_view of
> other schema
> CURSOR c_list_data_all ( i_niin IN VP$list_data_all_view.niin%TYPE,
> i_source_list IN
> VP$list_data_all_view.list%TYPE) IS
> SELECT qty, um
> FROM VP$list_data_all_view
> WHERE niin = i_niin
> AND list = i_source_list;
>
> BEGIN
> v_sites:= p_site_id;
> v_length:= LENGTH(p_site_id);
> IF v_length > 3 THEN
>
> FOR v_counter IN 0..(v_length/3) LOOP
> v_index:= INSTR(v_sites,','); -- find first occurence
> v_siteList(v_counter):=SUBSTR(v_sites,1,v_index-1);
> v_sites:=SUBSTR(v_sites,v_index+1);
> END LOOP;
>
> v_sites:=NULL;
> v_counter:=0;
> v_siteContract:=NULL;
>
>
> FOR v_counter IN 0..(v_length/3) LOOP
> OPEN c_getContractID(v_siteList(v_counter));
> FETCH c_getContractID
> INTO v_sites;
> EXIT WHEN c_getContractID%NOTFOUND;
> v_siteContract:= v_siteCOntract || ',' ||'''' || v_sites
> ||'''' ;
> v_sites:=NULL;
> CLOSE c_getContractID;
> END LOOP;
>
> v_siteContract:= SUBSTR(v_siteContract,2);
>
> ELSE
> OPEN c_getContractID(p_site_id);
> FETCH c_getContractID
> INTO v_sites;
> v_siteContract:= v_sites;
>
> END IF;
>
> v_sqlstmt:= 'SELECT ci.vendor_id,ci.fsc||ci.niin,';
> v_sqlstmt:= v_sqlstmt
> ||ci.vendor_list,c.vendor_lin,c.qty_period,ci.contract_id';
> v_sqlstmt:= v_sqlstmt || ' FROM vp$contract_status c, catalog_items
> ci';
>
> IF LENGTH(p_site_id) > 3 THEN
> v_sqlstmt:= v_sqlstmt || ' WHERE ci.contract_id IN (' ||
> v_siteContract || ')';
> ELSE
> v_sqlstmt:= v_sqlstmt || ' WHERE ci.contract_id =' ||''''||
> v_siteContract ||'''';
> END IF;
>
> IF p_vendor IS NOT NULL THEN
> v_sqlstmt:= v_sqlstmt || ' AND ci.vendor_id=' ||'''' || p_vendor ||
> '''' ;
> END IF;
>
> IF p_niin IS NOT NULL THEN
> v_sqlstmt:= v_sqlstmt || ' AND ci.niin=' ||'''' || p_niin || '''' ;
> END IF;
>
> v_sqlstmt:= v_sqlstmt || ' AND ci.vendor_id <> ';
> v_sqlstmt:= v_sqlstmt || '''' || vd2_util.SAMMS_ORDER || '''' ;
> v_sqlstmt:= v_sqlstmt || ' AND ci.contract_id=c.contract ';
> v_sqlstmt:= v_sqlstmt || ' AND ci.niin=c.niin ';
>
> -- initialize table index
> o_vendor:=t_vendor;
> o_nsn:=t_nsn;
> o_contract_qty:=t_contract_qty;
> o_um:=t_um;
> o_qty_ordered:=t_qty_ordered;
> o_qty_rcvd:=t_qty_rcvd;
> o_percentage:=t_percentage;
> o_source_list:=t_source_list;
> o_source_lin:=t_source_lin;
> o_qty_period:=t_qty_period;
> o_ref_date:=t_ref_date;
> o_contract_id:=t_contract_id;
>
> OPEN v_cursor FOR v_sqlstmt;
> LOOP
>
> FETCH v_cursor
> INTO v_vendor,v_nsn,v_source_list,
> v_source_lin,v_qty_period,v_contractId;
> EXIT WHEN v_cursor%NOTFOUND;
>
> v_count:= v_count + 1;
>
> -- not all of the NSN have an FSC so check on the length first
> IF length(v_nsn) < 13 THEN
> v_niin:= v_nsn;
> ELSE
> v_niin:=substr(v_nsn,5,9);
> END IF;
>
> OPEN c_list_data_all(v_niin, v_source_list);
> FETCH c_list_data_all
> INTO v_contract_qty, v_um;
> CLOSE c_list_data_all;
>
> o_vendor(v_count):= v_vendor;
> o_nsn(v_count):= v_nsn;
> o_contract_qty(v_count):=v_contract_qty;
> o_um(v_count):=v_um;
> o_source_list(v_count):=v_source_list;
> o_source_lin(v_count):=v_source_lin;
> o_qty_period(v_count):=v_qty_period;
> o_contract_id(v_count):=v_contractid;
>
> OPEN c_getRefDate(v_source_list);
> FETCH c_getRefDate
> INTO v_ref_date;
> CLOSE c_getRefDate;
>
> o_ref_date(v_count):=v_ref_date;
>
> OPEN
> c_getPoInfo(v_niin,v_ref_date,v_vendor,SUBSTR(v_contractid,1,2));
> FETCH c_getPOInfo
> INTO v_qty_ordered;
> CLOSE c_getPoInfo;
>
> o_qty_ordered(v_count):=v_qty_ordered;
>
> OPEN c_qty_rcvd(v_niin,v_ref_date,SUBSTR(v_contractid,1,2));
> FETCH c_qty_rcvd
> INTO v_qty_rcvd;
> CLOSE c_qty_rcvd;
>
> IF (v_qty_rcvd IS NOT NULL) THEN
> o_qty_rcvd(v_count):=v_qty_rcvd;
> IF v_contract_qty <> 0 THEN
> o_percentage(v_count):=((v_qty_rcvd/v_contract_qty)*100);
> ELSE
> o_percentage(v_count):= 0;
> END IF;
> ELSE
> o_qty_rcvd(v_count):= 0;
> o_percentage(v_count):= 0;
> END IF;
>
> v_vendor:=NULL;
> v_nsn:=NULL;
> v_contract_qty:=NULL;
> v_um:=NULL;
> v_qty_ordered:=NULL;
> v_qty_rcvd:=NULL;
> v_contractID:=NULL;
> v_percentage:=NULL;
> v_source_list:=NULL;
> v_source_lin:=NULL;
> v_qty_period:=NULL;
> v_ref_date:=NULL;
> END LOOP;
>
> CLOSE v_cursor;
> o_count(1):= v_count;
>
> END getVendorConsumption;
> END vd2_Report;
> /

1, Pull every SQL statement out and run it independently using EXPLAIN PLAN. If the tables are large enough ... Are indexes being used?

2. Use DBMS_PROFILER to profile the package while it is running and look toward tuning those areas that are consuming the most resources/time. (excellent instructions on using DBMS_PROFILER in Tom Kyte's book "Expert one-on-one Oracle"

What are the out parameters being returned to?

Daniel Morgan Received on Thu Jun 27 2002 - 10:07:23 CDT