OraFAQ Forum: Performance Tuning » Check My Query Plz

Home » RDBMS Server » Performance Tuning » Check My Query Plz

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Check My Query Plz [message #169315]

Wed, 26 April 2006 04:26

sohailnawaz
Messages: 40
Registered: April 2005
Location: Saudi Arabia

Member

Sir,
Below is my function and Query. when i m running my query it is taking more than 4 Hours, I have created Indices on the source table, STG.Badge_History_Table on "LogicalReaderType", "ReaderID" and "MicroDate" , please tell me why it is taking so much time, when i comments my function in my Query then the query taking 2 minutes, what is the reason please tell me the solution.

Thanks

CREATE OR REPLACE FUNCTION Get_Next_In (
P_Employee_Number VARCHAR2, P_DATE DATE, P_T_ID NUMBER
)
RETURN DATE
AS
IN_DATE DATE;
BEGIN
SELECT
MIN(TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate") INTO IN_DATE
FROM
STG.BADGE_HISTORY_TABLE
WHERE
"EmployeeNumber"=P_Employee_Number
AND
TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate">P_DATE
AND
"LogicalReaderType" = 3
AND
"ReaderID" IN (60,61,62,63,64,65,66,67,68,69,70,71,72,73)
AND
"ID">P_T_ID;
RETURN IN_DATE;
END;
/

DECLARE
TYPE T_Tid IS TABLE OF OUT_VS_IN_DETAIL.TRANSACTION_ID%TYPE INDEX BY BINARY_INTEGER;
TYPE T_Empno IS TABLE OF OUT_VS_IN_DETAIL.EMPLOYEE_NUMBER%TYPE INDEX BY BINARY_INTEGER;
TYPE T_Dat IS TABLE OF OUT_VS_IN_DETAIL.TRANSACTION_DATE%TYPE INDEX BY BINARY_INTEGER;
TYPE T_OUT IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE T_NEXTIN IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE T_MINOBRK IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_tid T_Tid;
v_empno T_Empno;
v_dat T_Dat;
v_out T_OUT;
v_nextin T_NEXTIN;
v_MinObrk T_MINOBRK;
CURSOR C1 IS
SELECT "ID" TRANSACTION_ID,
"EmployeeNumber" EMPLOYEE_NUMBER,
TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate" TRANSACTION_DATE,
TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate" OUT_TIME,
Get_Next_In( "EmployeeNumber" ,TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate" , "ID") NEXT_IN,
(( Get_Next_In( "EmployeeNumber" ,TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') +
"MicroDate" , "ID"))-(TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate" ))*24*60 MINUTES_OF_BREAK
FROM
STG.BADGE_HISTORY_TABLE
WHERE
"LogicalReaderType" = 4
AND "ReaderID" IN (60,61,62,63,64,65,66,67,68,69,70,71,72,73)
AND
TO_DATE('30/12/1899','dd/mm/yyyy hh24:mi') + "MicroDate" BETWEEN '01-JAN-2006' AND '30-APR-2006';
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO
v_tid ,
V_empno,
V_dat,
v_out,
v_nextin,
v_MinObrk
LIMIT 1000;
FORALL I IN 1 .. v_tid.COUNT
INSERT INTO OUT_VS_IN_DETAIL(TRANSACTION_ID,EMPLOYEE_NUMBER,TRANSACTION_DATE,OUT,NEXT_IN,MINUTESOFBREAK)
VALUES (v_tid(i), v_empno(i), v_dat(i),v_out(i),v_nextin(i),v_MinObrk(i));
DBMS_OUTPUT.PUT_LINE('Array count = '||v_tid.COUNT);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
COMMIT;
END;

Report message to a moderator

Re: Check My Query Plz [message #169336 is a reply to message #169315]

Wed, 26 April 2006 05:43

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Did you also collect statistics on tables/indexes?
Read sticky

Report message to a moderator

Re: Check My Query Plz [message #169417 is a reply to message #169315]

Wed, 26 April 2006 13:26

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

You should

Try to encapsulate your function's logic into pure SQL, to prevent the overhead that context-switching between SQL and PL/SQL introduces;
Experiment with INSERT INTO...SELECT syntax.

Does the following SQL produce the same results as your code?

INSERT INTO out_vs_in_detail (
    transaction_id
,   employee_number
,   transaction_date
,   out
,   next_in
,   minutesofbreak
)
SELECT a.id
,      a.employeenumber
,      a.four_date
,      a.four_date
,      a.next_in_date
,     (a.next_in_date - a.four_date) * 24 * 60
FROM  (SELECT   bht4.id
       ,        bht4.employeenumber
       ,        TO_DATE('18991230','YYYYMMDD') + bht4.microdate      four_date
       ,        MIN(TO_DATE('18991230','YYYYMMDD') + bht3.microdate) next_in_date
       FROM     stg.badge_history_table      bht3
       ,        stg.badge_history_table      bht4
       WHERE    bht3.employeenumber = bht4.employeenumber
       AND      bht3.logicalreadertype = 3
       AND      bht4.logicalreadertype = 4
       AND      bht3.readerid IN (60,61,62,63,64,65,66,67,68,69,70,71,72,73)
       AND      bht4.readerid IN (60,61,62,63,64,65,66,67,68,69,70,71,72,73)
       AND      TO_DATE('18991230','YYYYMMDD') + bht4.microdate BETWEEN TO_DATE('20060101','YYYYMMDD')
                                                                    AND TO_DATE('20060430','YYYYMMDD')
       AND      bht3.microdate > bht4.microdate
       AND      bht3.id > bht4.id
       GROUP BY bht4.id
       ,        bht4.employeenumber
       ,        TO_DATE('18991230','YYYYMMDD') + bht4.microdate) a
/

Report message to a moderator

Previous Topic:	Bulk delete
Next Topic:	dbms_job package

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 15:19:02 CST 2024