This discussion is archived
14 Replies Latest reply: Nov 27, 2012 1:34 AM by user258571 RSS

Script to check response time

user258571 Newbie
Currently Being Moderated
Hi,
customer have a monitoring system that execute a script to check response time db (9i database).
The script do the following steps:
- Date
- select count(*) from all_tables
- select count(*) from v$lock
- Date

And then calculate the gap from end and start date, this is the response time.
So, response time change from db to db depending on number of tables.
Is there any different method or different object to use?

Thanks.
  • 1. Re: Script to check response time
    Purvesh K Guru
    Currently Being Moderated
    Use
    alter session set timing on;
    and then Run the script. It will display the time of execution in Milliseconds.
  • 2. Re: Script to check response time
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    set timing on
    
    Or use 
    v$sqlarea
  • 3. Re: Script to check response time
    John Stegeman Oracle ACE
    Currently Being Moderated
    a script to check response time db
    That script is useless to check the "response time" for anything other than those specific queries. V$LOCK is a dynamic view and would have varying numbers of rows depending upon what is going on in the DB. ALL_TABLES - that would vary depending upon the DB and under which user you have logged in.

    What exactly are you trying to measure (and don't say "response time," because that's too vague).

    John
  • 4. Re: Script to check response time
    user258571 Newbie
    Currently Being Moderated
    Purvesh K wrote:
    Use
    alter session set timing on;
    and then Run the script. It will display the time of execution in Milliseconds.
    I need to know the cumulative elapsed of the 2 queries.
    But i need to know if there is another object to query instead all_tables.
  • 5. Re: Script to check response time
    John Stegeman Oracle ACE
    Currently Being Moderated
    What are you trying to accomplish? If you need to know the total elapsed time of those 2 queries, you've done that. How can we know if there is another "better" table or tables for you to use, since we have no idea of what you are trying to measure
  • 6. Re: Script to check response time
    user258571 Newbie
    Currently Being Moderated
    John Stegeman wrote:
    a script to check response time db
    That script is useless to check the "response time" for anything other than those specific queries. V$LOCK is a dynamic view and would have varying numbers of rows depending upon what is going on in the DB. ALL_TABLES - that would vary depending upon the DB and under which user you have logged in.

    What exactly are you trying to measure (and don't say "response time," because that's too vague).

    John
    Thank you John.
    Customer wants check if database is heavy loaded, and use this method to verify the load of the database.
    If the elapsed of this script is 0.2s, the database is not heavy loaded, but if the elapsed is 2s there is a workload to check.
    So, as you write, it depends on the numbers of tables and others.

    I want to change these queries.
  • 7. Re: Script to check response time
    user258571 Newbie
    Currently Being Moderated
    Osama_mustafa wrote:
    set timing on
    
    Or use 
    v$sqlarea
    Hi Osama,
    Count(*) of v$sqlarea changes if the shared pool is small or large?
  • 8. Re: Script to check response time
    Purvesh K Guru
    Currently Being Moderated
    842366 wrote:
    Purvesh K wrote:
    Use
    alter session set timing on;
    and then Run the script. It will display the time of execution in Milliseconds.
    I need to know the cumulative elapsed of the 2 queries.
    But i need to know if there is another object to query instead all_tables.
    Doesn't your Script contain only these two queries? If they do, then the execution time of Script is same as the Cumulative execution time of the two queries. Unless you are doing something that you have not demonstrated.

    Another way of doing it is:
    declare
      v_start_time  date;
      v_end_time   date;
    begin
      v_start_time := sysdate;
      dbms_output.put_line('Start Time :: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
      Your Query 1;
      Your Query 2;
      v_end_time := sysdate;
      dbms_output.put_line('End Time :: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
      dbms_output.put_line('Total Execution Time :: ' || v_end_time - v_start_time);
    end;
    PS:- You may also try using SYS_TIMESTAMP for a very small script (small in sense of activity of queries); as the execution time may be fractional seconds which are not considered in SYSDATE.

    Also, I haven't heard anywhere of "Response Time" changing from DB to DB due to number of tables. It does change from DB to DB due to different configurations viz. Block Size, Database Load etc.
  • 9. Re: Script to check response time
    user258571 Newbie
    Currently Being Moderated
    Purvesh K wrote:

    Another way of doing it is:
    declare
    v_start_time  date;
    v_end_time   date;
    begin
    v_start_time := sysdate;
    dbms_output.put_line('Start Time :: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
    Your Query 1;
    Your Query 2;
    v_end_time := sysdate;
    dbms_output.put_line('End Time :: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
    dbms_output.put_line('Total Execution Time :: ' || v_end_time - v_start_time);
    end;
    PS:- You may also try using SYS_TIMESTAMP for a very small script (small in sense of activity of queries); as the execution time may be fractional seconds which are not considered in SYSDATE.

    Also, I haven't heard anywhere of "Response Time" changing from DB to DB due to number of tables. It does change from DB to DB due to different configurations viz. Block Size, Database Load etc.
    Thank you,
    which query i can use instead "Your Query 1" and "Your Query 2" to create a standard procedure?

    Edited by: 842366 on 27-nov-2012 0.51
  • 10. Re: Script to check response time
    Purvesh K Guru
    Currently Being Moderated
    842366 wrote:
    which query i can use instead "Your Query 1" and "Your Query 2" to create a standard procedure?

    Edited by: 842366 on 27-nov-2012 0.51
    Obviously, your Queries; Not Mine. ;)
    select count(*) from all_tables
    select count(*) from v$lock
  • 11. Re: Script to check response time
    user258571 Newbie
    Currently Being Moderated
    But i don't like these queries. It depends on number tables and others.
    I want to change them but i don't know which.
  • 12. Re: Script to check response time
    Purvesh K Guru
    Currently Being Moderated
    842366 wrote:
    But i don't like these queries. It depends on number tables and others.
    I want to change them but i don't know which.
    Your actual question was regarding the Execution time and that has been answered, hence, I suggest you to Mark this Thread as Answered by awarding suitable helpful/correct points, and post a new thread with all the relevant details
    1. Your Oracle Version
    2. Your Existing Queries.
    3. What do you wish to change?
    4. What output do you wish from the queries? Any logic involved to attain the output?
    5. Most Importantly, your Table structures and some sample data.
    6. Your Best attempt to the change requested.

    If you post all these details, you will almost certainly get a very good help.
  • 13. Re: Script to check response time
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi
    842366 wrote:
    >
    Thank you John.
    Customer wants check if database is heavy loaded, and use this method to verify the load of the database.
    If the elapsed of this script is 0.2s, the database is not heavy loaded, but if the elapsed is 2s there is a workload to check.
    the approach is wrong. The time it takes to select rows from two arbitrary data dictionary views gives almost 0 information about how long it would take to run other queries.

    You should be monitoring response times of important application queries and important application jobs.

    Best regards,
    Nikolay
  • 14. Re: Script to check response time
    user258571 Newbie
    Currently Being Moderated
    Nikolay Savvinov wrote:

    the approach is wrong. The time it takes to select rows from two arbitrary data dictionary views gives almost 0 information about how long it would take to run other queries.

    You should be monitoring response times of important application queries and important application jobs.

    Best regards,
    Nikolay
    Hi Nikolay,
    i kwow that the approach is wrong.
    But customer wants to create a standard procedure and use it in different database.

    thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points