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 Go to next message](/forum/theme/orafaq/images/down.png) |
sr_orcl
Messages: 82 Registered: January 2009 Location: mumbai
|
Member |
![sonu_2407%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 #388573 is a reply to message #388524] |
Wed, 25 February 2009 08:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sr_orcl
Messages: 82 Registered: January 2009 Location: mumbai
|
Member |
![sonu_2407%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 1203 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sr_orcl
Messages: 82 Registered: January 2009 Location: mumbai
|
Member |
![sonu_2407%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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.
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 06 14:17:50 CDT 2024
|