DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to time a query

  1. #1
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    Hi all!!

    Is there any utility which can tell the time
    it takes to execute the query?
    By setting "timing on" on SQL prompt I found it showed total time it took both to execute and display the results, where as I want only the Execution time.
    Actually I want to compare the execution time for the
    same query on Access Database and Oracle.
    I'm not able to get time in any of these.


    Thanks

    Shruti

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you simply want to eliminate the time it tooks to display all the fetched records in SQL*Plus, the easiest way is to do the following in SQL*Plus prompt:

    SQL> SET TIMING ON
    SQL> SET AUTOTRACE TRACEONLY STATISTICS

    This will suppress the display of the fetched records, but will show you the time the query needed to complete. When you're done and want again to see the resultsets of your queries, you simply isue:

    SQL> SET AUTOTRACE OFF

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    Thanks a Lot

    Anuy body for Timing in Access Query, Please

    Thanks

    Regards
    Shruti

  4. #4
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Unhappy Error enabling STATISTICS report

    I tried to do this butt it returned the following error

    SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
    SP2-0611: Error enabling STATISTICS report

    Please help

    Regards
    Shruti

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ShrutiM
    Thanks a Lot

    Anuy body for Timing in Access Query, Please

    Thanks

    Regards
    Shruti
    In ACCESS, you'll have to write some code. Take a timepoint before your query, create a recordset, execute it, and then take another timepoint.

    You'll have to be careful when analyzing the results. Access will return pre-maturely after it presents the first few rows. In order to get an accurate result, you will have to make sure you move to the end of the recordset and THEN take a timepoint.
    Jeff Hunter

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Error enabling STATISTICS report

    Originally posted by ShrutiM
    I tried to do this butt it returned the following error

    SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
    SP2-0611: Error enabling STATISTICS report

    Please help

    Regards
    Shruti
    Grant the PLUSTRACE role to yourself.
    Jeff Hunter

  7. #7
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Unhappy PLUSTRACE role

    Thanks Marist,

    But what is this PLUSTRACE role ?
    I didn't have any such role defined by default in my database. Can you please suggest which System Privileges to grant to this role so that I can creat this one myself. Or is there any script to create this Role.

    Regards
    Shruti

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you don't have PLUSTRACE role created, simply run the script %ORACLE_HOME%\sqlplus\admin\plustrce.sql logged on as SYS or INTERNAL.

    If you can't find this script, run the following commands as SYS or INTERNAL:

    drop role plustrace;
    create role plustrace;

    grant select on v_$sesstat to plustrace;
    grant select on v_$statname to plustrace;
    grant select on v_$session to plustrace;
    grant plustrace to dba with admin option;

    After you're done you have to grant this PLUSTRACE role to the user that will execute SET AUTOTRACE commands in SQL*Plus.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jan 2001
    Posts
    91

    HOW TO TIME A SQL QUERY

    THANKS Jmodic I realy needed this info.. to test between to databases.

    how do you interpret the results??? this is what I have


    DATABASE #1` QUERY RESULTS

    SQL> SELECT MEETING FROM MEETING WHERE MEETING = 'HL017';

    real: 1763

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    2 consistent gets
    0 physical reads
    0 redo size
    127 bytes sent via SQL*Net to client
    198 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL> SET AUTOTRACE OFF

    --------------------------------------------------------------
    DATABASE #2 QUERY RESULTS

    SQL> SELECT MEETING FROM MEETING WHERE MEETING = 'HL017';

    real: 321

    Statistics
    ----------------------------------------------------------
    621 recursive calls
    0 db block gets
    247 consistent gets
    16 physical reads
    0 redo size
    181 bytes sent via SQL*Net to client
    289 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    5 sorts (memory)
    0 sorts (disk)
    1 rows processed




    Tim

  10. #10
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Smile AutoTrace Output Explanation ??

    Hi!!

    Thanks a lot, I really got the job done with all your support.
    But it seems the information returned from autotrace is not just time elapsed to execute the query but also some vital statistics about the query. I'm trying to understand these.

    Can you provide link to some site which explains these.

    Thanks

    Regards
    Shruti

Posting Permissions

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


Click Here to Expand Forum to Full Width