Home » SQL & PL/SQL » SQL & PL/SQL » Ned to capture timing statistics in a stored procedure (Oracle 9.2.0.8 on Windows)
Ned to capture timing statistics in a stored procedure [message #350979] Sun, 28 September 2008 13:25 Go to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Hello, I have a stored procedure like the following in which I need to capture start, end and elapsed timings and write them to a table. The timings need to include the ref cursor fetch. Is this possible to do with in a stored procedure like this one for example?



CREATE OR REPLACE PROCEDURE SP_SEARCH_CUSTOMERS_DATA (
              lastName IN NVARCHAR2,
              cur_OUT IN OUT SYS_REFCURSOR,
              filterAltAccountOnly IN INTEGER := 0
)

  IS
    lastNameValue NVARCHAR2(45);
    limitRows NUMBER := 100;
    preferredAltIDFormat VARCHAR2(30) := '7_____________';

  BEGIN

    -- Initialize variables!
    lastNameValue   := NULL;


    IF lastName IS NOT NULL THEN
      lastNameValue := UPPER(lastName);
    END IF;

   IF lastNameValue IS NOT NULL THEN
     OPEN cur_OUT FOR
        SELECT DISTINCT
        MV_CUSTOMER.CUSTOMER_ID AS PREFERRED_CUST_KEY,
        COALESCE(
          (
            SELECT
              MIN(Alt_Acct_Num)
            FROM
              Additional_Account
            WHERE
              Additional_Account.Customer_ID = mv_Customer.Customer_ID
            AND
              Alt_Acct_Num <> mv_Customer.Customer_Acct_Num
            AND
              Alt_Acct_Num LIKE preferredAltIDFormat
          ),
          NULL -- MV_CUSTOMER.CUSTOMER_ACCT_NUM
        ) AS CUST_ID,
        MV_CUSTOMER.LAST_NAME AS CUST_LST_NM,
        MV_CUSTOMER.FIRST_NAME AS CUST_FRST_NM,
        CUSTOMER_HOME_PHONE.PHONE_NUM AS HOME_PHONE,
        CUSTOMER_WORK_PHONE.PHONE_NUM AS WORK_PHONE,
        MV_CUSTOMER.POSTAL_CD AS CUST_ADDR_ZIP
       FROM
        MV_CUSTOMER
        LEFT OUTER JOIN PHONE CUSTOMER_HOME_PHONE
          ON CUSTOMER_HOME_PHONE.CUSTOMER_ID = MV_CUSTOMER.CUSTOMER_ID
          AND CUSTOMER_HOME_PHONE.PHONE_TYPE_ID = 1
        LEFT OUTER JOIN PHONE CUSTOMER_WORK_PHONE
          ON CUSTOMER_WORK_PHONE.CUSTOMER_ID = MV_CUSTOMER.CUSTOMER_ID
          AND CUSTOMER_WORK_PHONE.PHONE_TYPE_ID = 2
        WHERE (MV_CUSTOMER.LAST_NAME = UPPER(lastName) OR MV_CUSTOMER.LAST_NAME = lastNameValue)
        AND (filterAltAccountOnly = 0 OR EXISTS (
            SELECT
              *
            FROM
              Additional_Account
            WHERE
              Additional_Account.Customer_ID = mv_Customer.Customer_ID
            AND
              Alt_Acct_Num <> mv_Customer.Customer_Acct_Num
            AND
              Alt_Acct_Num LIKE preferredAltIDFormat
          )
        )
        AND Is_Deleted = 0
        AND ROWNUM <= limitRows;
   END IF;
END SP_SEARCH_CUSTOMERS_DATA;
/




Thank you,

David
Re: Ned to capture timing statistics in a stored procedure [message #350985 is a reply to message #350979] Sun, 28 September 2008 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To get time you can use dbms_utility.get_time but your procedure does not fetch anything so you can't get any information about fetches.

Regards
Michel
Re: Ned to capture timing statistics in a stored procedure [message #350987 is a reply to message #350985] Sun, 28 September 2008 16:12 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Michel, what do you mean by it does not fetch any thing? I do fetch a result set and pass it out as a ref cursor. Thank you for your reply.

David
Re: Ned to capture timing statistics in a stored procedure [message #351021 is a reply to message #350987] Mon, 29 September 2008 00:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You open a ref cursor based on in-parameters. You do not fetch from that cursor.
Re: Ned to capture timing statistics in a stored procedure [message #351087 is a reply to message #350979] Mon, 29 September 2008 04:42 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Michel, the ref cursor is declared as IN and OUT:

cur_OUT IN OUT SYS_REFCURSOR

This returns a result set when executed. Should I be doing something different here?

If so, can you kindly illustrate and also include how I could successfully record start and elapsed times in a stand alone debug table.

The table is defined as:


CREATE TABLE capture_cust_sp_search_params
(
 startTime     TIMESTAMP,
 elapTime      NUMBER,
 paramString   VARCHAR2(4000)
) TABLESPACE USERS;



I tried using

BegTime := dbms_utility.get_time;
EndTime := dbms_utility.get_time;

Then calculating elapsed time as follows:
ElapsedTime := round((EndTime - BegTime)/100, 2);

This results are negative values some times.

It also never seems to calculate the time it takes to return any result set.

Here is my insert statement:

INSERT INTO capture_cust_sp_search_params
    (startTime, elapTime, paramString)
    VALUES
    (systimestamp, ElapsedTime, ' lName: ' || lastName);


Your assistance is greatly appreciated here. Thank you,

David
Re: Ned to capture timing statistics in a stored procedure [message #351098 is a reply to message #351087] Mon, 29 September 2008 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the code it is hard to say something.
But as Frank said, you only open the cursor, so it is very fast.

please read OraFAQ Forum Guide, especially "How to format your post?" section and use code tags.

Regards
Michel
Re: Ned to capture timing statistics in a stored procedure [message #351107 is a reply to message #350979] Mon, 29 September 2008 05:17 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
My appologies I use code tags from another forum. The stored procedure I am trying to capture timing statistics on is here, formatted correctly I hope. Is this what you mean by you need the code? What do I need to do to get accurate timings on the cursor fetch?

CREATE OR REPLACE PROCEDURE SP_SEARCH_CUSTOMERS_DATA (
              lastName IN NVARCHAR2,
              cur_OUT IN OUT SYS_REFCURSOR,
              filterAltAccountOnly IN INTEGER := 0
)

  IS
    lastNameValue NVARCHAR2(45);
    limitRows NUMBER := 100;
    preferredAltIDFormat VARCHAR2(30) := '7_____________';

    -- Debug variables
    stTime timestamp;
    BegTime NUMBER := 0;
    EndTime NUMBER := 0;
    ElapsedTime NUMBER := 0; 

  BEGIN

    -- Populate the start time
    stTime := systimestamp;
    BegTime := dbms_utility.get_time+power(2,32)/100;


    -- Initialize variables!
    lastNameValue   := NULL;


    IF lastName IS NOT NULL THEN
      lastNameValue := UPPER(lastName);
    END IF;

   IF lastNameValue IS NOT NULL THEN
     OPEN cur_OUT FOR
        SELECT DISTINCT
        MV_CUSTOMER.CUSTOMER_ID AS PREFERRED_CUST_KEY,
        COALESCE(
          (
            SELECT
              MIN(Alt_Acct_Num)
            FROM
              Additional_Account
            WHERE
              Additional_Account.Customer_ID = mv_Customer.Customer_ID
            AND
              Alt_Acct_Num <> mv_Customer.Customer_Acct_Num
            AND
              Alt_Acct_Num LIKE preferredAltIDFormat
          ),
          NULL -- MV_CUSTOMER.CUSTOMER_ACCT_NUM
        ) AS CUST_ID,
        MV_CUSTOMER.LAST_NAME AS CUST_LST_NM,
        MV_CUSTOMER.FIRST_NAME AS CUST_FRST_NM,
        CUSTOMER_HOME_PHONE.PHONE_NUM AS HOME_PHONE,
        CUSTOMER_WORK_PHONE.PHONE_NUM AS WORK_PHONE,
        MV_CUSTOMER.POSTAL_CD AS CUST_ADDR_ZIP
       FROM
        MV_CUSTOMER
        LEFT OUTER JOIN PHONE CUSTOMER_HOME_PHONE
          ON CUSTOMER_HOME_PHONE.CUSTOMER_ID = MV_CUSTOMER.CUSTOMER_ID
          AND CUSTOMER_HOME_PHONE.PHONE_TYPE_ID = 1
        LEFT OUTER JOIN PHONE CUSTOMER_WORK_PHONE
          ON CUSTOMER_WORK_PHONE.CUSTOMER_ID = MV_CUSTOMER.CUSTOMER_ID
          AND CUSTOMER_WORK_PHONE.PHONE_TYPE_ID = 2
        WHERE (MV_CUSTOMER.LAST_NAME = UPPER(lastName) OR MV_CUSTOMER.LAST_NAME = lastNameValue)
        AND (filterAltAccountOnly = 0 OR EXISTS (
            SELECT
              *
            FROM
              Additional_Account
            WHERE
              Additional_Account.Customer_ID = mv_Customer.Customer_ID
            AND
              Alt_Acct_Num <> mv_Customer.Customer_Acct_Num
            AND
              Alt_Acct_Num LIKE preferredAltIDFormat
          )
        )
        AND Is_Deleted = 0
        AND ROWNUM <= limitRows;
   END IF;

    EndTime := dbms_utility.get_time;
    ElapsedTime := round((EndTime - BegTime)/100, 2);

    -- Populate elapsed time and parameters
    INSERT INTO capture_cust_sp_search_params
    (startTime, elapTime, paramString)
    VALUES
    (stTime, ElapsedTime, ' lName: ' || lastName);

END SP_SEARCH_CUSTOMERS_DATA;
/



I hope this is better. Thank you for your time.

David
Re: Ned to capture timing statistics in a stored procedure [message #351110 is a reply to message #350979] Mon, 29 September 2008 05:40 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
I think I realize what is going on here. I am only opening the ref cursor and passing it out. The fetch never actually takes place in the procedure so I will not get accurate timings. I need to issue an explicit FETCH to get them. Thank you for responding.

David
Re: Ned to capture timing statistics in a stored procedure [message #351122 is a reply to message #351110] Mon, 29 September 2008 06:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
./fa/1964/0/
Previous Topic: :NEW trigger issue
Next Topic: how to remove distinct key word from the table
Goto Forum:
  


Current Time: Sun Dec 11 04:23:56 CST 2016

Total time taken to generate the page: 0.15346 seconds