| Compare Query Performance [message #451140] |
Mon, 12 April 2010 11:28  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I want to check performance of 2 queries
Though as per standard practice (as per my understanding) we shall compare Logical I/O as the first criterion, I would like to compare its timings
However, I want suppress the output since the queries will return large amount of data
As of now, I know following methods to suppress the output
A)
Write following in script file and execute
variable n number
set serveroutput on
exec :n := dbms_utility.get_time;
set termout off
<sql query>
set termout on
exec dbms_output.put_line( (dbms_utility.get_time-:n)/100 || ' seconds' );
B)
set timing on
set autotrace traceonly
<sql query>
C)
sqlplus -s user/password@connectString @c:\query.sql >c:\output.log
As per my understanding when database server returns data to client which when displayed on the screen, lot of time is consumed and we can not get actual query execution time since in practice we do not use the same method to fetch the data
I have 2 queries regarding this
1) Is above method to compare timing of queries by suppressing its output will give realistic figure.
Apart from comparison, I need absolute timing as well for benchmarking (without actually executing it in PL/SQL as in live system)
2) Will method C) while writing the query output to file increase the execution time of the query?
I assume it would be similar to 'create table as select' method where space alllocation may add to query execution timing
Please suggest
Thanks and Regards,
prataps
|
|
|
|
|
|
|
|
| Re: Compare Query Performance [message #451663 is a reply to message #451140] |
Fri, 16 April 2010 01:04  |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Methods A) and B) eliminate Terminal I/O. However, for method B), use SET AUTOTRACE TRACEONLY STATISTICS so that network round trips to fetch the data are still executed.
Whether you want Terminal I/O or not depends on how the query is actually used in your application. For example, if it is a query executed by an AppServer which "buffers" or "stores" or "processes" the results, the query does not do Terminal I/O. A user running the query from his desktop without SET TERMOUT OFF does suffer (significant) Terminal I/O overheads.
See http://forums.oracle.com/forums/thread.jspa?messageID=4234348#4234348 for a similar discussion.
Method C redirects the output to disk. -- again Disk performance on the client can cause an impact if you are fetching a very large resultset.
Hemant K Chitale
[Updated on: Fri, 16 April 2010 01:07] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
