Home » RDBMS Server » Performance Tuning » Package Tuning (merged 4)
Package Tuning (merged 4) [message #388524] Wed, 25 February 2009 03:35 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,
If I have to tune the given packge code.What are the basic steps to tune any PL/SQL objects.For example I am sending you my code.
Re: Package Tunning [message #388525 is a reply to message #388524] Wed, 25 February 2009 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Package Tunning [message #388573 is a reply to message #388524] Wed, 25 February 2009 08:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Some general rules of thumb:

1) Tune all the SQL in the package individually
2) Re-write SQL to replace as many Cursor loops as possible with single Sql statements
3) Minimise the number of SELECT statements used to populate pl/sql variables. Rather than executing a piece of SQL ionce per loop, try to move it outside the loop, and select it's values into a collection.
4) Minimise the number of calls from within SQL to functions that perform SQL
Re: Package Tunning [message #388870 is a reply to message #388524] Thu, 26 February 2009 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
5. Don't use SELECT COUNT(*) if you aren't interested in how many rows match your criteria. If you just want to see if any rows match use SELECT 1

e.g.

This:
	FUNCTION employeehasrole
	(
		pempcode     VARCHAR2,
		prolecode    VARCHAR2,
		pproductcode VARCHAR2,
		pcompanyid   NUMBER
	) RETURN BOOLEAN IS
		vcount NUMBER;
	BEGIN
		--see if the employee that is passed to the function has the rolecode
		SELECT COUNT(*)
		INTO   vcount
		FROM   company_employee_role a
		WHERE  cg_rolecode = prolecode
		AND    productcode = pproductcode
		AND    companyid = pcompanyid
		AND    employeecode = pempcode
		AND    casehandle > 0;
		IF vcount > 0
		THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	END;



Becomes:


FUNCTION employeehasrole
(
  pempcode     VARCHAR2,
  prolecode    VARCHAR2,
  pproductcode VARCHAR2,
  pcompanyid   NUMBER
) RETURN BOOLEAN IS

  v_found NUMBER;

BEGIN

  --see if the employee that is passed to the function has the rolecode
  SELECT 1
  INTO   v_found
  FROM   company_employee_role a
  WHERE  cg_rolecode = prolecode
  AND    productcode = pproductcode
  AND    companyid = pcompanyid
  AND    employeecode = pempcode
  AND    casehandle > 0;

  RETURN TRUE;

EXCEPTION 
  WHEN no_data_found THEN
    RETURN FALSE;

  WHEN too_many_rows THEN --(assuming this is possible here)
    RETURN FALSE;

END;



If your query can only return 0 or 1 it won't matter that much but if it can return a higher count it can make a big difference.
Re: Package Tunning [message #388905 is a reply to message #388870] Thu, 26 February 2009 10:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your queries will return counts > 1 you might want to rewrite them like this:
  SELECT 1
  INTO   v_found
  WHERE EXISTS (SELECT null
                FROM   company_employee_role a
                WHERE  cg_rolecode = prolecode
                AND    productcode = pproductcode
                AND    companyid = pcompanyid
                AND    employeecode = pempcode
                AND    casehandle > 0);

That'll stop searching after the first matching row it finds.
Package Tuning [message #389135 is a reply to message #388524] Fri, 27 February 2009 06:31 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi To ALL,

HOw to reduce total excecution time in the given package.

Sample table structure and data count is given below,



-- Create table Total Record in this table is --695
create table LCT_WORKFLOWSTAGE_DTL
(
  LOANSTAGEID       NUMBER(8) not null,
  COMPANYID         NUMBER(8),
  LOANID            NUMBER(8),
  COLLCIFID         NUMBER(8) not null,
  APPLID            NUMBER(8),
  WORKFLOWID        NUMBER(8),
  WORKFLOWSTAGEID   NUMBER(8),
  STAGE             VARCHAR2(8 CHAR) not null,
  DISBURSALSTAGEFLG VARCHAR2(1 CHAR),
  STAGESTATUS       VARCHAR2(1 CHAR),
  STATUSDATE        DATE,
  FINANCIERID       NUMBER(8),
  PRIORITY          NUMBER(3),
  MANDATORY         VARCHAR2(1 CHAR) not null,
  REMARKS           VARCHAR2(200 CHAR),
  MAKERID           VARCHAR2(8 CHAR) not null,
  MAKEDATE          DATE not null,
  STATUS            VARCHAR2(1 CHAR) not null,
  STATUSBUSIDATE    DATE,
  CUSNOTEID         NUMBER(8),
  CG_STATUSREASON   NUMBER(8),
  SEQNO             NUMBER(8),
  STATUSENDDATE     DATE,
  EMPLOYEECODE      VARCHAR2(8 CHAR),
  SUPERVISORCODE    VARCHAR2(8 CHAR),
  APPLICATIONFLAG   VARCHAR2(2 CHAR),
  SETNO             NUMBER(8),
  ISROUTED          VARCHAR2(1 CHAR) default 'N',
  DECISION          VARCHAR2(2 CHAR),
  ACCESSSTATUS      VARCHAR2(1 CHAR),
  SERVICESTATUS     VARCHAR2(1 CHAR),
  REALLOCTYPE       VARCHAR2(1 CHAR),
  REALLOCEMPID      NUMBER(8),
  REALLOCATEDBY     NUMBER,
  REALLOCATEDATE    DATE,
  PARENTLOANSTAGEID NUMBER(8)
)
partition by hash (COMPANYID)
(
  partition SYS_P445
    tablespace ULS_TBS,
  partition SYS_P446
    tablespace ULS_TBS,
  partition SYS_P447
    tablespace ULS_TBS,
  partition SYS_P448
    tablespace ULS_TBS
);
-- Add comments to the table 


  
alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD1_LOANID foreign key (LOANID)
  references LCT_LOAN_DTL (LOANID);
alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD_CG_STATUSREASON foreign key (CG_STATUSREASON)
  references COMPANY_GENERIC (GENERICID);
  alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD_COLLCIFID foreign key (COLLCIFID)
  references LCT_CIF_CUSTOMER (COLLCIFID);
alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD_COMPANYID foreign key (COMPANYID)
  references COMPANY (COMPANYID);
-- Create/Recreate indexes 
create index CLWSD_NIDX1 on LCT_WORKFLOWSTAGE_DTL (WORKFLOWID, STAGESTATUS, COLLCIFID, EMPLOYEECODE, STATUS, LOANID);
 
create index CLWSD_NIDX2 on LCT_WORKFLOWSTAGE_DTL (COLLCIFID);

create index CLWSD_NIDX3 on LCT_WORKFLOWSTAGE_DTL (COLLCIFID, EMPLOYEECODE, COMPANYID, STAGESTATUS, APPLICATIONFLAG, STATUS, LOANID, STAGE)
;
create index INDX_1 on LCT_WORKFLOWSTAGE_DTL (LOANID, EMPLOYEECODE, STAGE, APPLICATIONFLAG);

create index INDX_LCWKSTDT_001 on LCT_WORKFLOWSTAGE_DTL (COMPANYID, EMPLOYEECODE, STAGESTATUS, STATUS);
 
create index INDX_LWD_EMPLINK on LCT_WORKFLOWSTAGE_DTL (COLLCIFID, STAGESTATUS, APPLICATIONFLAG, PARENTLOANSTAGEID);
create index NDX_LCTWD1 on LCT_WORKFLOWSTAGE_DTL (EMPLOYEECODE);

create index NDX_LCTWD2 on LCT_WORKFLOWSTAGE_DTL (LOANID);


select count(*) from lct_worklist_employee ---nera about records =31;

total record in flt_logic table :--103;



[mod-edit: added code tags and disabled smilies]
  • Attachment: test_pkg.sql
    (Size: 10.88KB, Downloaded 150 times)

[Updated on: Sat, 28 February 2009 22:21] by Moderator

Report message to a moderator

Re: Package Tuning [message #389141 is a reply to message #389135] Fri, 27 February 2009 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't seriously expect us to rewrite your package for you do you?

Have a look at the answers to your previous topic, most of them are just as valid here.
Package Tuning (merged) [message #389486 is a reply to message #388524] Mon, 02 March 2009 05:37 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi

CREATE OR REPLACE PACKAGE BODY pkg_collworklist_tune IS

-- li_count NUMBER(3);

gl_outmsg VARCHAR2(2000);

-----FUNCTION TO CHECK THE CASE ALREADY EXIST IN LCT_WORKLIST_EMPLOYEE
FUNCTION f_chkcase(pn_loanid IN NUMBER) RETURN BOOLEAN AS
BEGIN
SELECT COUNT(ROWID)
INTO li_count
FROM lct_worklist_employee a
WHERE a.loanid = pn_loanid
AND a.status <> 'X';

CASE when li_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END case;

EXCEPTION
WHEN OTHERS THEN
lv_sqlcode := SQLCODE;
gl_outmsg := fn_errconcat('-20508', fn_errconcat(lv_sqlcode), NULL);
RETURN FALSE;

END;

-------FUNCTION TO DELETE FROM LCT_WORKLIST_EMPLOYEE
FUNCTION f_dlt_worklist(pn_loanid IN NUMBER) RETURN BOOLEAN AS
BEGIN
DELETE FROM lct_worklist_employee nologging WHERE loanid = pn_loanid;
RETURN f_chkcase(pn_loanid);

EXCEPTION
WHEN OTHERS THEN
lv_sqlcode := SQLCODE;
gl_outmsg := fn_errconcat('-20509', fn_errconcat(lv_sqlcode), NULL);
RETURN FALSE;
END;

----FUNCTION TO INSERT INTO LCT_WORKLIST_EMPLOYEE
FUNCTION f_ins_worklist
(
pn_worklistid IN NUMBER,
pn_empcode IN NUMBER,
pn_loanid IN NUMBER,
pv_status IN VARCHAR2,
pv_maker IN VARCHAR2,
pn_companyid IN NUMBER
) RETURN BOOLEAN AS
ln_serial lct_worklist_employee.worklistempid%TYPE;

BEGIN






ln_serial := pkg_sequence_generation.set_sequence('LCT_WORKLIST_EMPLOYEE');
INSERT INTO lct_worklist_employee
(worklistempid, worklistid, employeeid, loanid, status, makerid,
makedate, companyid)
VALUES
(ln_serial, pn_worklistid, pn_empcode, pn_loanid, pv_status, pv_maker,
SYSDATE, pn_companyid);

RETURN f_chkcase(pn_loanid);
EXCEPTION
WHEN OTHERS THEN
--added by ankur on 20/10/2007
lv_sqlcode := SQLCODE;
gl_outmsg := fn_errconcat('-20510', fn_errconcat(lv_sqlcode), NULL);
-- gl_outmsg := fn_errconcat('-20510', gl_outmsg);
RETURN FALSE;
END;

-------------2.FUNCTON TO EVALUDATE CASE FOR PERTICULAR LOAN STAGE
--a.it opens cursor containing logic string for all rule id
--b.execute each logicstring
--b.1. if it returns 'P' then return corresponding worklistid
--b.2. if it returns multiple 'P' then return worklistid based on priority
--b.3. if it doesnt return 'F' then return -1
FUNCTION f_evalcase
(
pn_loanid VARCHAR2,
pn_empid NUMBER
) RETURN NUMBER AS

TYPE tab_companyid IS TABLE OF lct_worklist.companyid%type ;
TYPE tab_worklistid IS TABLE OF lct_worklist.worklistid%type ;
TYPE tab_logicstring IS TABLE OF flt_logic.logicstring%type ;
TYPE tab_ruleid IS TABLE OF lct_worklist.ruleid%type ;
TYPE tab_priority IS TABLE OF lct_worklist.priority%type ;

tv_companyid tab_companyid;
tv_worklistid tab_worklistid;
tv_logicstring tab_logicstring;
tv_ruleid tab_ruleid;
tv_priority tab_priority ;


ln_worklistid lct_worklist.worklistid%TYPE;
lv_return VARCHAR2(1);
BEGIN

--------BULK COLLECT__
SELECT lw.companyid,lw.worklistid,fl.logicstring,
lw.ruleid, lw.priority BULK COLLECT
INTO tv_companyid,tv_worklistid, tv_logicstring,
tv_ruleid , tv_priority

FROM lct_worklist lw, flt_logic fl
WHERE fl.logicid = lw.ruleid
AND lw.status <> 'X'
AND fl.logicstring IS NOT NULL
AND lw.employeeid = pn_empid
AND fl.status <> 'X'
AND lw.companyid = gl_companyid
ORDER BY lw.priority;
-------BULK COLECT

ln_worklistid := NULL;
FOR x IN tv_worklistid.first..tv_worklistid.count LOOP
------------EXECUTE LOGICSTRING FOR LOAN STAGE ID .It will return either P or F
BEGIN
lv_return := pkgexecstatement.sp_execute_statement(tv_companyid(x)/*x.companyid*/,
pn_loanid,
NULL,
'CO',
tv_logicstring(x) /*x.logicstring*/,
NULL,
'P');
IF lv_return = 'P' THEN
ln_worklistid :=tv_worklistid(x) /*x.worklistid*/;
END IF;
EXCEPTION
WHEN OTHERS THEN
lv_return := 'F';
ln_worklistid := NULL;
END;
END LOOP;
RETURN ln_worklistid;
EXCEPTION
WHEN OTHERS THEN
--added by ankur on 20/10/2007
lv_sqlcode := SQLCODE;
gl_outmsg := fn_errconcat('-20511', fn_errconcat(lv_sqlcode), NULL);
RETURN ln_worklistid;
END;

--------1. Procedure for worklist prioritisation for an employee
---a.it will call evaluate function
--b.after evaluation it will check for existing record in lct_worklist_employee table
--c.if cheaks false then insert new record into lct_worklist_employee
--d.if cheak turns true then delete existing stage and insert into history table
PROCEDURE p_worklist_definition
(
pn_empcode IN NUMBER,
pv_maker IN VARCHAR2,
pn_companyid IN NUMBER,
pv_out_msg OUT VARCHAR2
) IS


ln_worklistid lct_worklist.worklistid%TYPE;

TYPE tab_lwd_loanid IS TABLE OF lct_workflowstage_dtl.loanid%type;
tv_lwd_loanid tab_lwd_loanid;

TYPE tab_lwd_stagestaus IS TABLE OF lct_workflowstage_dtl.stagestatus%type;
tv_lwd_stagestaus tab_lwd_stagestaus;

e_parameter EXCEPTION;


BEGIN
IF pn_empcode IS NULL OR pn_companyid IS NULL THEN
RAISE e_parameter;
END IF;
gl_outmsg := NULL;

---New Changes----
SELECT lwd.loanid ,lwd.stagestatus BULK COLLECT
INTO tv_lwd_loanid ,tv_lwd_stagestaus
FROM lct_workflowstage_dtl lwd
WHERE lwd.Stagestatus in ('P', 'D')
AND status <> 'X'
AND loanid IS NOT NULL
AND companyid=pn_companyid
AND lwd.employeecode=pn_empcode;
----New Changes---

FOR i in 1..tv_lwd_loanid.count loop
ln_worklistid := f_evalcase(tv_lwd_loanid(i), pn_empcode);

IF f_chkcase(tv_lwd_loanid(i)) = FALSE THEN
IF f_ins_worklist( ln_worklistid,
pn_empcode,
tv_lwd_loanid(i),
tv_lwd_stagestaus(i),
pv_maker,
pn_companyid) = TRUE THEN
pv_out_msg := 'Success';
END IF;
ELSE
IF f_dlt_worklist(tv_lwd_loanid(i)) = FALSE THEN
IF f_ins_worklist(ln_worklistid,
pn_empcode,
tv_lwd_loanid(i),
tv_lwd_stagestaus(i),
pv_maker,
pn_companyid) = TRUE THEN
pv_out_msg := 'Success';
END IF;
END IF;
END IF;
END LOOP;

COMMIT;

EXCEPTION
WHEN e_parameter THEN
pv_out_msg := fn_errconcat('-20822', NULL, NULL);
WHEN OTHERS THEN
lv_sqlcode := SQLCODE;
pv_out_msg := fn_errconcat('-20823', fn_errconcat(lv_sqlcode), NULL);
END p_worklist_definition;

END pkg_collworklist_tune;


Above is my Package code.My PM told me also remove f_chkcase function from package.I dont able to remove ot .Can you tell how can i Remove without affecting my logic.
Re: Package Tuning (merged) [message #389489 is a reply to message #389486] Mon, 02 March 2009 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

And don't multipost your question.

Regards
Michel
Re: Package Tuning (merged 4) [message #389533 is a reply to message #388524] Mon, 02 March 2009 08:50 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>HOw to reduce total excecution time in the given package.
MAXIMUM performance gain results from NOT calling procedure; especially not within any LOOP.
Previous Topic: Oracle 9i sga advisory view
Next Topic: Package Tuning using Bulk Excpetion
Goto Forum:
  


Current Time: Fri Dec 09 21:09:25 CST 2016

Total time taken to generate the page: 0.10631 seconds