Home » SQL & PL/SQL » SQL & PL/SQL » Tuning PLSQL PROCEDURE
Tuning PLSQL PROCEDURE [message #188908] Tue, 22 August 2006 06:20 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
Below is the PLSQL Package which needs to be tuned. When I run this package it takes a lot of time to run this.
Can someone suggest how to go about it?
Can someone suggest how to get an explain plan for this?

Thanks in advance
Anand
CREATE OR REPLACE PACKAGE BODY PCK_YIELD_REPORT
AS

PROCEDURE GEN_YIELD_REPORT(o_EmpCursor OUT empcur)
IS
BEGIN
OPEN o_EmpCursor FOR
SELECT E.FULL_NAME, E.EMPLOYEE_NUMBER, E.EMPLOYEE_TYPE, E.SUPERVISOR_NAME, E.OBU_NAME, E.CENTER_NAME,
E.PROGRAM_NAME, E.LOCATION, E.MODE_OF_DELIVERY, E.ROLE, E.BAND, E.ATTRIBUTE7,
(SELECT NVL(SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100,0)
FROM APPS.YIELD_CALC
WHERE E.PERSON_ID=YIELD_CALC.PERSON_ID
AND START_DATE=
CASE TO_CHAR(SYSDATE, 'D')
WHEN '1' THEN TRUNC(SYSDATE)-13
WHEN '2' THEN TRUNC(SYSDATE)-7
WHEN '3' THEN TRUNC(SYSDATE)-8
WHEN '4' THEN TRUNC(SYSDATE)-9
WHEN '5' THEN TRUNC(SYSDATE)-10
WHEN '6' THEN TRUNC(SYSDATE)-11
WHEN '7' THEN TRUNC(SYSDATE)-12
END
) AS PREV_YIELD,
(SELECT NVL(SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100,0)
FROM APPS.YIELD_CALC
WHERE E.PERSON_ID=YIELD_CALC.PERSON_ID
AND START_DATE BETWEEN
CASE TO_CHAR(SYSDATE, 'D')
WHEN '1' THEN TRUNC(SYSDATE)-34
WHEN '2' THEN TRUNC(SYSDATE)-28
WHEN '3' THEN TRUNC(SYSDATE)-29
WHEN '4' THEN TRUNC(SYSDATE)-30
WHEN '5' THEN TRUNC(SYSDATE)-31
WHEN '6' THEN TRUNC(SYSDATE)-32
WHEN '7' THEN TRUNC(SYSDATE)-33
END
AND
CASE TO_CHAR(SYSDATE, 'D')
WHEN '1' THEN TRUNC(SYSDATE)-13
WHEN '2' THEN TRUNC(SYSDATE)-7
WHEN '3' THEN TRUNC(SYSDATE)-8
WHEN '4' THEN TRUNC(SYSDATE)-9
WHEN '5' THEN TRUNC(SYSDATE)-10
WHEN '6' THEN TRUNC(SYSDATE)-11
WHEN '7' THEN TRUNC(SYSDATE)-12
END
) AS PREV_3WEEK_YIELD,
(SELECT NVL(SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100,0)
FROM APPS.YIELD_CALC
WHERE E.PERSON_ID=YIELD_CALC.PERSON_ID
AND START_DATE BETWEEN
CASE TO_CHAR(SYSDATE, 'D')
WHEN '1' THEN TRUNC(SYSDATE)-97
WHEN '2' THEN TRUNC(SYSDATE)-91
WHEN '3' THEN TRUNC(SYSDATE)-92
WHEN '4' THEN TRUNC(SYSDATE)-93
WHEN '5' THEN TRUNC(SYSDATE)-94
WHEN '6' THEN TRUNC(SYSDATE)-95
WHEN '7' THEN TRUNC(SYSDATE)-96
END
AND
CASE TO_CHAR(SYSDATE, 'D')
WHEN '1' THEN TRUNC(SYSDATE)-13
WHEN '2' THEN TRUNC(SYSDATE)-7
WHEN '3' THEN TRUNC(SYSDATE)-8
WHEN '4' THEN TRUNC(SYSDATE)-9
WHEN '5' THEN TRUNC(SYSDATE)-10
WHEN '6' THEN TRUNC(SYSDATE)-11
WHEN '7' THEN TRUNC(SYSDATE)-12
END
) AS PREV_13WEEK_YIELD
FROM
DEV_EMP_MASTER E;

END GEN_YIELD_REPORT;


END PCK_YIELD_REPORT ;
/
Re: Tuning PLSQL PROCEDURE [message #189010 is a reply to message #188908] Tue, 22 August 2006 15:00 Go to previous messageGo to next message
tongucy
Messages: 8
Registered: August 2006
Junior Member
If what you need is to get every explain plan of the sql statements in your plsql source you may use sql trace and tkprof with explain option. TKPROF is run from the operating system prompta and the syntax is:

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

Sample output from TKPROF is as follows:

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;

call count cpu elapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Parse 1 0.16 0.29 3 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.26 2 2 4 14

Misses in library cache during parse: 1
Parsing user id: (8) SCOTT

Rows Execution Plan
------- ---------------------------------------------------

14 MERGE JOIN
4 SORT JOIN
4 TABLE ACCESS (FULL) OF 'DEPT'
14 SORT JOIN
14 TABLE ACCESS (FULL) OF 'EMP'

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#PFGRF01010
Re: Tuning PLSQL PROCEDURE [message #189016 is a reply to message #188908] Tue, 22 August 2006 15:21 Go to previous message
tongucy
Messages: 8
Registered: August 2006
Junior Member
SQL Trace and TKPROF; http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#PFGRF01010

[Updated on: Tue, 22 August 2006 15:21]

Report message to a moderator

Previous Topic: to find out % of total
Next Topic: how to debug pl/sql code
Goto Forum:
  


Current Time: Wed Dec 07 10:47:19 CST 2016

Total time taken to generate the page: 0.13928 seconds