Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Unanswered: procedure to time an sql

    Hello
    is there a way to create a procedure that will take
    sql statement from another table and run the timing on that sql statement in order to output a runtime ?

    I have a table with qid, title, and sql
    can i pass a qid to a proc to run that kind of job??

    CREATE OR REPLACE PROCEDURE TEST (Q_ID in INTEGER )
    v_temp
    IS
    CURSOR c1 IS
    Select sql from temp where qid = Q_ID ;

    BEGIN
    open c1;
    loop
    FETCH c1 into v_temp ;
    EXIT WHEN c1%NOTFOUND;

    run the timing for the sql and output it to a screen
    end loop;

    close c1;

    End TEST ;
    /

    Thank you

    Al

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://download-west.oracle.com/docs...13.htm#1014062
    Why are you trying to re-invent the what SQL*Plus already does?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    dbms_utility.get_time

    take a look at the docs
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by anacedent
    http://download-west.oracle.com/docs...13.htm#1014062
    Why are you trying to re-invent the what SQL*Plus already does?
    yup, set timing on will work too
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Question

    Wouldn't tkprof be the most reliable method of determining how long a query takes?

    http://asktom.oracle.com/pls/ask/f?p...:4030201115642,
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    as it states in your link, "set timing on" will show the
    overall time the procedure or package took to complete.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    i just want to do all from a procdure, since i would like to stick that output to a table with query run time column and q_id column, i need to run it every week. and since my table with sql has some of the complex query i do not want to copy it to a script. just call a proc, pass a (q_id) and table done ...

    Is there a way to see the output from a procedure to a table as well as screen ?



    big thank you

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    dbms_utility.get_time
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •