SQL tuning / How to Tune the SQL Query

[OraMaster]

Hi Experts,

I have one select query which having some one function call and taking very long time to give the result.

Please find the code below:

SELECT a.ID, a.idtype, a.username, a.firstname, a.lastname, a.status,
       a.languageid, a.languagename, getrolesforuser (a.username) ROLES,
       0 clubid, a.isdisabled, 0 region
  FROM (SELECT w.ID, w.idtype, w.username, p.firstname, p.lastname,
               w.acct_statcd status, w.preferredlanguage languageid,
               lt.langname languagename, p.sortfirstname, p.sortlastname,
               w.isdisabled, TRUNC (w.createdt) createdt, registration_type
          FROM webuser w JOIN person p ON w.ID = p.ID AND w.idtype = p.idtype
               LEFT JOIN langtype lt ON w.preferredlanguage = lt.langty
               ) a
 WHERE 1 = 1 AND TRUNC (a.createdt) BETWEEN '01-Jun-2009' AND '30-Oct-2009'

Please note below function "getrolesforuse r" is being called from above SQL.

FUNCTION getrolesforuser (pusername IN webuser.username%TYPE)
   RETURN VARCHAR2
IS
   vroles   VARCHAR (1000);

   CURSOR cursorout (pusernames VARCHAR2)
   IS
      SELECT /*+INDEX(PH) INDEX(WU)INDEX(PN)*/
             NVL2 (c.commdesc,
                   pn.posdesc || '-' || c.commdesc,
                   pn.posdesc
                  ) rolename
        FROM positionholder ph,
             webuser wu,
             positionname pn,
             (SELECT /*+INDEX(CD)INDEX(CN)*/
                     cd.commid, cn.commdesc
                FROM committee cd, commname cn
               WHERE cn.commid = cd.commid
                 AND cd.effdt <= SYSDATE
                 AND (cd.enddt >= SYSDATE OR cd.enddt IS NULL)
                 AND cn.langty = 0) c
       WHERE ph.ID = wu.ID
         AND ph.idtype = wu.idtype
         AND ph.positionid = pn.positionid
         AND ph.commid = c.commid(+)
         AND ph.effdt <= SYSDATE
         AND (ph.enddt >= SYSDATE OR ph.enddt IS NULL)
         AND UPPER (wu.username) = UPPER (pusernames)
         AND pn.langty = 0
         AND pn.gender = 'M'
      UNION
      SELECT /*+INDEX(PN)*/
             pn.posdesc
        FROM positionname pn
       WHERE pn.posdesc = 'All Constituents';
-- Declare program variables as shown above
BEGIN
   FOR rec_cur IN cursorout (pusername)
   LOOP
      IF vroles IS NULL
      THEN
         LOOP
            vroles := rec_cur.rolename;
         END LOOP;
      ELSE
         vroles := vroles || ', ' || rec_cur.rolename;
      END IF;
   END LOOP;

   RETURN vroles;
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put ('error');
      END;
END getrolesforuser;

I want select query posted above should fetch the records within half minute.
Pls provide some solution on this asap.

Thanks in Advance
Bhushan

[kiranrajenimbalkar]

How to tune SQL...

I have a problem with one SQL.
Its not fetching the records its took very long time for execution.

Please see the below SQL

SELECT A.ID,
A.IDTYPE,
A.USERNAME,
A.FIRSTNAME,
A.LASTNAME,
A.STATUS,
A.LANGUAGEID,
A.LANGUAGENAME,
GETROLESFORUSER (A.USERNAME) ROLES,
0 CLUBID,
A.ISDISABLED,
0 REGION
FROM (SELECT W.ID,
W.IDTYPE,
W.USERNAME,
P.FIRSTNAME,
P.LASTNAME,
W.ACCT_STATCD STATUS,
W.PREFERREDLANG UAGE LANGUAGEID,
LT.LANGNAME LANGUAGENAME,
P.SORTFIRSTNAME ,
P.SORTLASTNAME,
W.ISDISABLED,
TRUNC(W.CREATED T) CREATEDT,
REGISTRATION_TY PE
FROM WEBUSER W
JOIN PERSON P ON W.ID = P.ID
AND W.IDTYPE = P.IDTYPE
LEFT JOIN LANGTYPE LT ON W.PREFERREDLANG UAGE = LT.LANGTY) A
WHERE 1 = 1
AND TRUNC(A.CREATED T) BETWEEN '01-JUN-2009' AND '30-Oct-2009';

this SQL contains one function GETROLESFORUSER

CREATE OR REPLACE FUNCTION GETROLESFORUSER (PUSERNAME IN WEBUSER.USERNAM E%TYPE)
RETURN VARCHAR2 IS

VROLES VARCHAR(1000);

CURSOR COURSEROUT(PUSE RNAMES VARCHAR2) IS
SELECT NVL2(C.COMMDESC , PN.POSDESC || '-' || C.COMMDESC, PN.POSDESC) ROLENAME
FROM POSITIONHOLDER PH,
WEBUSER WU,
POSITIONNAME PN,
(SELECT CD.COMMID, CN.COMMDESC
FROM COMMITTEE CD, COMMNAME CN
WHERE CN.COMMID = CD.COMMID
AND CD.EFFDT <= SYSDATE
AND (CD.ENDDT >= SYSDATE OR CD.ENDDT IS NULL)
AND CN.LANGTY = 0) C
WHERE PH.ID = WU.ID
AND PH.IDTYPE = WU.IDTYPE
AND PH.POSITIONID = PN.POSITIONID
AND PH.COMMID = C.COMMID(+)
AND PH.EFFDT <= SYSDATE
AND (PH.ENDDT >= SYSDATE OR PH.ENDDT IS NULL)
AND UPPER(WU.USERNA ME) = UPPER(PUSERNAME S)
AND PN.LANGTY = 0
AND PN.GENDER = 'M'
UNION
SELECT PN.POSDESC
FROM POSITIONNAME PN
WHERE PN.POSDESC = 'All Constituents';

BEGIN
FOR REC_CUR IN COURSEROUT(PUSE RNAME) LOOP
IF VROLES IS NULL THEN
LOOP
VROLES := REC_CUR.ROLENAM E;
END LOOP;
ELSE
VROLES := VROLES || ', ' || REC_CUR.ROLENAM E;
END IF;
END LOOP;

RETURN VROLES;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT ('error');
END;
END GETROLESFORUSER ;

due to this function SQL took very long time for fetching the records.

So please provide me the soluntion for this.

Thanks
Kiran

[amitpatel66]

firstly Can you please try testing the query without calling a function?..just comment that particular column and run the query and check how much time it takes?

Also check for any indexes on the table that you can use in your select query by including a HINT.

[amitpatel66]

Duplicate Threads merged for better management of the forum

Moderator

[amitpatel66]

Try using the below code for the function getrolesforuser and let us know in case it solved your problem:

[code=oracle]
FUNCTION getrolesforuser (pusername IN webuser.usernam e%TYPE)
RETURN VARCHAR2
IS
vroles VARCHAR2 (1000) := NULL;
TYPE my_roles IS TABLE OF VARCHAR2(1000);
myrole my_roles;

CURSOR cursorout (pusernames VARCHAR2)
IS
SELECT /*+INDEX(PH) INDEX(WU)INDEX( PN)*/
NVL2 (c.commdesc,
pn.posdesc || '-' || c.commdesc,
pn.posdesc
) rolename
FROM positionholder ph,
webuser wu,
positionname pn,
(SELECT /*+INDEX(CD)INDE X(CN)*/
cd.commid, cn.commdesc
FROM committee cd, commname cn
WHERE cn.commid = cd.commid
AND cd.effdt <= SYSDATE
AND (cd.enddt >= SYSDATE OR cd.enddt IS NULL)
AND cn.langty = 0) c
WHERE ph.ID = wu.ID
AND ph.idtype = wu.idtype
AND ph.positionid = pn.positionid
AND ph.commid = c.commid(+)
AND ph.effdt <= SYSDATE
AND (ph.enddt >= SYSDATE OR ph.enddt IS NULL)
AND UPPER (wu.username) = UPPER (pusernames)
AND pn.langty = 0
AND pn.gender = 'M'
UNION
SELECT /*+INDEX(PN)*/
pn.posdesc
FROM positionname pn
WHERE pn.posdesc = 'All Constituents';
-- Declare program variables as shown above
BEGIN
OPEN cursorout(puser names);
FETCH cursorout BULK COLLECT INTO myrole;

FOR rec_cur IN myrole.FIRST..m yrole.LAST LOOP
LOOP
vroles := vroles|| ', ' || myrole(rec_cur) ;
END LOOP;
vroles := SUBSTR(vroles,3 );
RETURN vroles;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put ('error');
END;
END getrolesforuser ;

[/code]

[kiranrajenimbalkar]

Thank you very much Amit for Quick Reply.

Thanks
Kiran