Home » SQL & PL/SQL » SQL & PL/SQL » query response time takes more time when calling from package
query response time takes more time when calling from package [message #383247] Tue, 27 January 2009 23:31 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
when I run above query its coming in 1.2 seconds.but when comment subquery and call from package its taking 9 seconds.query returns more than 2000 records.i am not able to find the reason why it is taking more time when calling from package?


SELECT 
      /*    UTILITIES_PKG.GET_COUNTRY_CODE(E.EMP_ID,E.EMP_NO) COUNTRY_ID */
          
       (SELECT DISTINCT IE.COUNTRY_ID
    FROM DOCUMENT IE
    WHERE IE.EMP_ID =E.EMP_ID
          AND IE.EMP_NO = E.EMP_NO
          AND IE.STATUS = 'OPEN'  ) COUNTRY_ID
      
FROM EMPLOYEE E


CREATE OR REPLACE PACKAGE BODY UTILITIES_PKG AS
  FUNCTION GET_COUNTRY_CODE(P_EMP_ID IN VARCHAR2, P_EMP_NO IN VARCHAR2)
    RETURN VARCHAR2 IS
    L_COUNTRY_ID VARCHAR2(25) := '';

  BEGIN
    SELECT DISTINCT IE.COUNTRY_ID
    INTO L_COUNTRY_ID
    FROM DOCUMENT IE
    WHERE IE.EMP_ID = P_EMP_ID
          AND IE.EMP_NO = P_EMP_NO
         AND IE.STATUS = 'OPEN';
    RETURN L_COUNTRY_ID;

  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'CONT';
  END;
END UTILITIES_PKG;

Re: query response time takes more time when calling from package [message #383248 is a reply to message #383247] Tue, 27 January 2009 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: query response time takes more time when calling from package [message #383270 is a reply to message #383247] Wed, 28 January 2009 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove what you say: use SQL*Plus and copy and paste your session.

Regards
Michel
Re: query response time takes more time when calling from package [message #383286 is a reply to message #383247] Wed, 28 January 2009 02:06 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
you mean to say that you want me to execute above query in sqlplus.when i execute same query in sqlplus it shows only howmany records returned but not showing any time.
Re: query response time takes more time when calling from package [message #383298 is a reply to message #383286] Wed, 28 January 2009 02:33 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, query and procedure use "set time on".

Regards
Michel
Previous Topic: explain plan concepts
Next Topic: what are tk profile user and stausback in oracle
Goto Forum:
  


Current Time: Sat Dec 10 05:14:56 CST 2016

Total time taken to generate the page: 0.08588 seconds