-
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
-
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?
-
Thanks a Lot
Anuy body for Timing in Access Query, Please
Thanks
Regards
Shruti
-
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
-
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
-
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
-
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
-
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|