Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Timer function/procedure

    Does anyone know where I can get some kind of timer function or procedure to time the execution of my queries/statements (in SQL*Plus). I need something simple that outputs in seconds and/or minutes. I've been trying to find something like this on the internet to no avail. I came across PLVtmr functions but they do not work with Oracle 9i as stated by the author. So now im in a problem trying to find something simple that just times execution of my statements/queries. Any help is greatful. Thanks.

    By the way, i'm using it to time how long it takes to like find certain records out of thousands of them.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Timer function/procedure

    Originally posted by nickbn
    Does anyone know where I can get some kind of timer function or procedure to time the execution of my queries/statements (in SQL*Plus). I need something simple that outputs in seconds and/or minutes. I've been trying to find something like this on the internet to no avail. I came across PLVtmr functions but they do not work with Oracle 9i as stated by the author. So now im in a problem trying to find something simple that just times execution of my statements/queries. Any help is greatful. Thanks.

    By the way, i'm using it to time how long it takes to like find certain records out of thousands of them.
    I am using on Unix crond and shellscripts, on Win at and batch files, on DB jobs and everthing without any problem.

    Here is example of shell script:

    ORACLE_BASE=/opt/oracle
    ORACLE_HOME=/opt/oracle/920
    TNS_ADMIN=$ORACLE_HOME/network/admin
    NLS_LANG=AMERICAN_AMERICA.EE8ISO8859P2
    ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
    PATH=$PATH:$ORACLE_HOME/bin
    CONNECT_STRING=scott/tiger@test.world

    export ORACLE_BASE ORACLE_HOME TNS_ADMIN NLS_LANG ORACLE_SID PATH ORA_NLS33

    echo "start"
    /bin/date

    # calling sqlpus whith some sql file
    sqlplus $CONNECT_STRING /path/to/sql/update.sql

    echo "end"
    /bin/date
    echo
    echo

    At crontab is following entry:

    15 7 * * * /path/to/shellscript/test.sh > /tmp/update.log 2>&1

    Script is executed everyday at 7:15AM

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    You might want to use dbms_utility.get_time

    Example (set serveroutput on)

    THE FOLLOWING BLOCK

    declare
    v_time_now number(12);
    v_time_later number(12);
    begin
    v_time_now := dbms_utility.get_time;
    dbms_lock.sleep(1);
    v_time_later := dbms_utility.get_time;
    dbms_output.put_line('difference : '||to_char((v_time_later - v_time_now)/100)||' in seconds...');
    end;
    /

    GIVES THE FOLLOWING OUTPUT

    SQL>difference : 1.03 in seconds...

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Come to think of it.

    You can also just do this in SQL*Plus :

    SQL>SET TIMING ON
    SQL>SELECT COUNT(*) FROM LARGE_TABLE;

    6199995

    Elapsed 00:04:45.00

    SQL>

  5. #5
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    SQL> set timing on;
    SQL> select count(*) from large_table;
    select count(*) from large_table
    *
    FOUT in regel 1:
    .ORA-00942: table or view does not exist

    Verstreken: 00:00:00.00


    That isn't correct cvandermaele :s Or am I doing it wrong?
    A good programmer is a LAZY programmer!

  6. #6
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    Ok, it does work I didn't made an action. Thats why I got that error.
    A good programmer is a LAZY programmer!

  7. #7
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by Lazy
    SQL> set timing on;
    SQL> select count(*) from large_table;
    select count(*) from large_table
    *
    FOUT in regel 1:
    .ORA-00942: table or view does not exist

    Verstreken: 00:00:00.00


    That isn't correct cvandermaele :s Or am I doing it wrong?
    I assume your post was meant to be a joke. If it is not, than just keep in mind that LARGE_TABLE is just a name I invented while keying in the reply. I'm not surprised to see that it doesn't really exists in your schema.

    So, to correct my own syntax :

    SELECT COUNT(*) FROM LARGE_TABLE
    becomes
    SELECT COUNT(*) FROM <YOUR_TABLE>, where <YOUR_TABLE> is a valid tablename/view in your schema.

    Anyway, the whole point was the "Verstreken: 00:00:00.00", which - for non-dutch speaking boys and girls - means "Elapsed : 00:00:00.00".

  8. #8
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    I thought the only way to do that timing was a pl/sql block like you used before. I had never heard of the timing function.

    Btw large_table could be something like dual. Could I know? I'm still a non graduated student. What you know, I've got to learn and wasn't it a smart men who said: 'You've got to learn from your mistakes.'?
    A good programmer is a LAZY programmer!

  9. #9
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    No problemo.

  10. #10
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by cvandemaele
    Come to think of it.

    You can also just do this in SQL*Plus :

    SQL>SET TIMING ON
    SQL>SELECT COUNT(*) FROM LARGE_TABLE;

    6199995

    Elapsed 00:04:45.00

    SQL>
    omg, thank you so much.. i knew it would be something as simple as this. I had some really complicated 2 page timer code and i was like ... no it can't be THAT complicated. C++ timer functions are just as similar. That's why I asked this question. But anyhow... do u think the SET TIMING ON is more accurate than doing the first method?? I'm going to try both and see how much difference it is.

  11. #11
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Not sure what you mean by "more accurate".

    Both are accrurate. To my best knowledge, get_time gives you more precision.

    And SET TIMING ON only has its purpose in SQL*Plus, and is of no use in e.g. a pl/sql block.

  12. #12
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    Originally posted by cvandemaele
    Come to think of it.

    You can also just do this in SQL*Plus :

    SQL>SET TIMING ON
    SQL>SELECT COUNT(*) FROM LARGE_TABLE;

    6199995

    Elapsed 00:04:45.00

    SQL>
    Can you set timing on and have it active every time you start a new session?
    I would like to use this feature day in and day out without the set timing on SQL to start every query.
    Call me lazy, but the less typing I do the better!

  13. #13
    Join Date
    May 2003
    Posts
    87
    Add the line "set timing on" to your glogin.sql. This sql file gets executed everytime you start a new session.

    I too have all my session settings in glogin.sql -- makes me feel at home and be more lazy ;-)

    Originally posted by tlael
    Can you set timing on and have it active every time you start a new session?
    I would like to use this feature day in and day out without the set timing on SQL to start every query.
    Call me lazy, but the less typing I do the better!

  14. #14
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    DUHHH!

    Thanks,
    Somtimes it is the simply obvious solutions that escape me.

  15. #15
    Join Date
    Jan 2003
    Posts
    8
    Hi all

    Have been using the timing object in my script which works great. Now I am trying to log the timing into audit table. However, I am stuck trying to figure out how I can put the timing object into my sql insert statement.

    insert into audit_log(duration)
    select ? (<- I need the timing show value to be filed here)

    Any help is appreciated.

    Cheers
    -WK-

Posting Permissions

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