This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Apr 24, 2013 12:25 PM by AdamMartin RSS

How to test SQL query performance - realiably?

623917 Newbie
Currently Being Moderated
I have certain queries and I want to test which one is faster, and how big is the difference.

How can I do this reliably?

The problem is, when I execute the queries, Oracle does it's caching and execution planning and whatnot, and results of the queries are dependent on the order I execute them.

Example: query A and query B, supposed to return same data.

query A, run 1: 587 seconds
query A, run 2: 509 seconds
query B, run 1: 474 seconds
query B, run 2: 451 seconds

It would seem that A is somewhat faster than B, but if I change the order and execute B before A, results are different.

Also I'm running the queries in SQL Developer, and it only returns 100 first lines, how can I remove this effect and simulate real scenario where all lines are fetched?

I can also use EXPLAIN PLANs and look at the costs but I'm not sure how much I can trust those either. I understand they are only estimations and even if cost(a) = 1.5 * cost (b), b could still end up executing faster in practise due to inaccuracies in the cost calculation....right? EDIT: actually event if cost(a) = 5000 * cost(b), b can still execute faster.....seems like query A's cost is 15836 and B's cost is 3 while A seems to be faster in practise.

Edited by: user620914 on 19-Jan-2010 01:42
  • 1. Re: How to test SQL query performance - realiably?
    thtsang Journeyer
    Currently Being Moderated
    As you said, SQL performance depends on many factors, like disk caching (at various level: oracle, OS, controller, disk...), system load, the mood of the OS, etc, you can't reliabily test SQL performance.

    One way to get a rough result is to set autotrace on and run the report. Check the number of consistent gets. This shows the total amount of data needed by the SQL, regardless of whether it was cached or not. As a result, it will be fairer than simply timing the query. Most SQL queries are IO bound, so the number of consistent gets is approximately proportional to the speed.

    To make sure all rows a retrieved, you can enclose your SQL with 'select count(*) from ...'.

    EXPLAIN PLAN is just estimate of the cost, not the actual. And the cost is very dependent on the statistics. I don't think it can be used for infer SQL performance.
  • 2. Re: How to test SQL query performance - realiably?
    Dom Brooks Guru
    Currently Being Moderated
    Because there are so many layers beyond your control - i.e. not just the buffer cache which you can flush mnaually but also caching further down outside Oracle - I would say your better off comparing methods which are warm rather than cold.

    In other words, the most reliable method is to compare the second run of both queries.

    What should you be comparing?

    I would say a combination of plans, costs and timings.
    Don't just use explain plan as that may not be representative.
    Use autotrace as well maybe.
    Or better a sql trace via tkprof.

    Are the plans reasonable?
    Are the row estimates reasonable?
    What difference is there in timings?

    And certainly if your SQL uses collections or functions then you should consider doing a runstats comparison. After all the faster bit of code in a single user test might scale differently.

    You can increase the rows in SQL Developer via Tools>Preferences>Database>Worksheet>Max Rows to print in script.
    Personally, it's one of those things I always user SQL*Plus for.
  • 3. Re: How to test SQL query performance - realiably?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user620914 wrote:

    How can I do this reliably?
    To answer that question with a question... How do you predict global weather accurately?

    The problem is pretty much the same. Too many variables too consider.. some of them totally dynamic and only known at run-time.

    So don't. Elapsed time (in this context) is meaningless as a measurement base.

    What can be measured is resource utilisation. Specifically, how much I/O the query needs. Now this should be consistent across the same database design. Could vary with data volumes (not all full table scans are equal). But even so, it is measurable and meaningful - and as I/O is the most expensive operation, you now have a fairly accurate idea of how much that query will spend on it and how "+expensive+" that query is.

    So forget about momentum.. collapse the wave function and deal with position only. ;-)
  • 4. Re: How to test SQL query performance - realiably?
    riedelme Expert
    Currently Being Moderated
    user620914 wrote:
    I have certain queries and I want to test which one is faster, and how big is the difference.

    How can I do this reliably?
    Excellent queston, with some already posted excellent answers.

    I'm pretty much in line with DomBrooks and ThtSang except that I find cost generally unreliable. I look at it, but it is an estimate that may not be reliable; I have seen cases where a higher-cost version of a query ran constantly faster than a lower cost version of the same query. Using cost is a bit like listening to your favorite sportscaster - you want him to be right but it probably does not always happen 100% of the time.

    I generally use run time (from system tools, sql*plus set timing on or the times displayed on GUI tools), consistent gets and disk reads from AUTOTRACE or trace files, and cpu time (from V$SQL for rare cases when a query is cpu bound) to decide when one version of a query is better than another. As you pointed out it is best to experiement with queries, running them many times under different circumstances at different times and even in different orders to see what happens.
  • 5. Re: How to test SQL query performance - realiably?
    P.Forstmann Guru
    Currently Being Moderated
    When you say I/O, do you mean physical reads/writes and logical reads/writes ?
  • 6. Re: How to test SQL query performance - realiably?
    623917 Newbie
    Currently Being Moderated
    Thanks for all the valuable comments.

    So far I have adjusted my test strategy to contain:

    1) I'm still using wall-clock timing, but I changed it wrapping my query in select count(*) from (<my actual query>)
    2) Ditched EXPLAIN PLAN and am looking at autotrace report, and especially LAST_CR_BUFFER_GETS

    - I feel like adding select count(*) to my wall-clock tests might make them more reliable. My initial assumption was that the query performance should not change from A to B, as to my understanding the differences should not matter that much. I'm trying to get some confirmation for my assumptions before implementing the changes for real...

    Question: is it possible that given 2 queries A and B, both take 500s as a whole, but A is done so that most work is done in Oracle before returning the first row, while B only does small part of the work before returning 1st row, and continues to work while returning the remaining rows? So that with A time is divided say like 80% of total time spent before returning row 1, and with B 60% of time is spent before returning row 1, and 40% for the rest...

    What kind of queries would cause this? Doesn't Oracle more or less do all the major work (like joining tables, sorting, etc) before returning the 1st row, and rest is just reading from some internal cache....or what actually happens?

    Just trying to get some general understanding of this

    - "number of consistent gets" = LAST_CR_BUFFER_GETS, right? That's the one to watch and compare (seems to be quite identical with A and B) What about "physical reads"?
  • 7. Re: How to test SQL query performance - realiably?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    P. Forstmann wrote:
    When you say I/O, do you mean physical reads/writes and logical reads/writes ?
    Yes.. as I/O resource utilisation can and will vary between physical and logical over time. Thus a variances in execution time, but a consistent amount of I/O (assuming things like a stable/consistent execution plan). A better baseline to use for performance evaluation than to deal with elapsed time as the unit of measurement.
  • 8. Re: How to test SQL query performance - realiably?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    I do not really understand what your goal is with this and what you hope to gain by it.

    If there was any kind on consistency ito SQL performance, then database products of today would have heavily utilised this for performance management and optimisation. But there isn't..

    There's also the issue of scalability. Let's say we have application fooApp - which is well designed and written. A single client runs a copy of this app and has a resource utilisation of x on the database (ito memory, CPU, I/O, etc).

    We deploy another 10 clients running fooApp. Does this mean that the resource footprint has now increased from x to +10x+ ? No. Oracle uses shared resources.. which is designed to serve multiple application sessions. The SQLs used by appFoo should be hard parsed once and stored once in the shared pool, whether there are a single fooApp client or a 100....

    On the I/O soide, one client session stands a good chance of using faster logical I/O as another session has already paid the price for getting the data from phsyical disk and into the buffer cache. Etc.

    Therefore attempting to somehow "+cost+" an application in terms execution time is pretty much flawed.. as the resource utilisation of the application does not scale linearly with the number of application sessions added. That is not how server systems scale - except perhaps Java application servers... ;-)

    Thus my my lack of understanding for what you're hoping to achieve by seemingly wanting to "+cost+" your application SQL in Oracle...
  • 9. Re: How to test SQL query performance - realiably?
    thtsang Journeyer
    Currently Being Moderated
    user620914 wrote:
    Question: is it possible that given 2 queries A and B, both take 500s as a whole, but A is done so that most work is done in Oracle before returning the first row, while B only does small part of the work before returning 1st row, and continues to work while returning the remaining rows? So that with A time is divided say like 80% of total time spent before returning row 1, and with B 60% of time is spent before returning row 1, and 40% for the rest...

    What kind of queries would cause this? Doesn't Oracle more or less do all the major work (like joining tables, sorting, etc) before returning the 1st row, and rest is just reading from some internal cache....or what actually happens?
    This is definitely possible. For example, a nested loop join returns the first row while Oracle is still working. For a hash join, Oracle can return the first row only when it has almost completed the job. That's why oracle has the FIRST_ROWS and ALL_ROWS hints. Also, a query with order by clause must be (nearly) fully processed before returning anything
  • 10. Re: How to test SQL query performance - realiably?
    623917 Newbie
    Currently Being Moderated
    Billy  Verreynne  wrote:
    I do not really understand what your goal is with this and what you hope to gain by it.

    If there was any kind on consistency ito SQL performance, then database products of today would have heavily utilised this for performance management and optimisation. But there isn't..

    There's also the issue of scalability. Let's say we have application fooApp - which is well designed and written. A single client runs a copy of this app and has a resource utilisation of x on the database (ito memory, CPU, I/O, etc).

    We deploy another 10 clients running fooApp. Does this mean that the resource footprint has now increased from x to +10x+ ? No. Oracle uses shared resources.. which is designed to serve multiple application sessions. The SQLs used by appFoo should be hard parsed once and stored once in the shared pool, whether there are a single fooApp client or a 100....

    On the I/O soide, one client session stands a good chance of using faster logical I/O as another session has already paid the price for getting the data from phsyical disk and into the buffer cache. Etc.

    Therefore attempting to somehow "+cost+" an application in terms execution time is pretty much flawed.. as the resource utilisation of the application does not scale linearly with the number of application sessions added. That is not how server systems scale - except perhaps Java application servers... ;-)

    Thus my my lack of understanding for what you're hoping to achieve by seemingly wanting to "+cost+" your application SQL in Oracle...
    I have to say I don't understand your point either :)

    What are you saying, that people should not test their SQL performance? That tools such as autotrace are useless?

    You can form your SQL queries better or worse, or select your table structure / indexes better or worse. Some choices may end up executing orders of magnitude slower than others. Obviously you can't get exact measurements "this query executes in 43123 ns" and there are a lot of unpredictable variables that affect the end performance. Still, it's often better to test your querie's / table's performance before implementing them in the application than not.
  • 11. Re: How to test SQL query performance - realiably?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user620914 wrote:

    I have to say I don't understand your point either :)

    What are you saying, that people should not test their SQL performance? That tools such as autotrace are useless?
    No.. what I'm saying is that you need a baseline to make an informed decision about SQL performance.

    What does a 4 second SQL performance mean for query foo ? Nothing really.. wearing my dba cap I would point at that this is actually utterly useless for me to determine the impact of your query on production, or use it to determine how to scale it.

    If instead you tell me that is hits that table using an index range scan.. I know what it is doing and have a far better idea what it will do to the production instance.

    Thus my questioning this "+elapsed time+" measurement approach. I as a dba cannot use it... and I'm not sure what benefit (wearing my developer hat) you will find from it either.
    You can form your SQL queries better or worse, or select your table structure / indexes better or worse. Some choices may end up executing orders of magnitude slower than others. Obviously you can't get exact measurements "this query executes in 43123 ns" and there are a lot of unpredictable variables that affect the end performance. Still, it's often better to test your querie's / table's performance before implementing them in the application than not.
    Exactly. I'm not questioning the fact that optimising your code (and ALL your code, not just SQL) is a Good Thing (tm) - but how you go about that optimisation process.

    For example, your PL/SQL code fires off a query. It returns on average 10,000 rows, hits a single partition (SQL enables partitioning pruning) and then uses a local bitmap index to identify the rows.

    An optimal query by the sounds of it, and one that will perform and scale well.. even when the database instance needs to service a 100 clients using your code and running this query.

    Only, the code does a single bulk collect of all the rows and stuff it into dedicated process memory (PGA). Servicing a 100 clients means that dedicated server memory is now needed for 100x10000 rows - there's insufficient free memory, causing the kernel to start swapping pages in and out of memory heavily as all 100 client sessions are active and wanting to process the rows returned by the optimal query.

    What happens to scalability and performance now?

    Testing for performance is not simply measuring a query and then trying to use that or extrapolate that to determine application performance and the impact on production.

    It starts with the design of the tables, the design of the application, the writing of the code (application and SQL). It is not something that should be done after the fact as in "+okay, application all done, let's see how she performs!+".. and especially not using time as the baseline for performance measurement.
  • 12. Re: How to test SQL query performance - realiably?
    623917 Newbie
    Currently Being Moderated
    True, true.

    However, in real life most projects just don't have nowhere near good enough resources to do what you outlined. I would estimate that in a typical project (ok, a fairly loose term...) no project member is familiar with concepts such a s partition pruning, local bitmap index or PGA.

    Very large scale projects aside. Those may be blessed with a DBA / other resource who is qualified enough to know these issues and has enough time to devote to the project to look at things on this level. Most smaller development projects can't even try to plan performance on such detailed technical level.

    So there's no really a possibility to analyze the queries on the "partition pruning" level of detail actually. It's closer to like "lets see we're not creating cartesian products here" level. Well, maybe a little more advanced than that.

    4 second performance for a query foo is usable to me as a developer. It functions as a sanity check. If query foo takes 4 seconds it alone may already be too slow for my online application's nonfunctional requirements. Whereas if it was part of a batch process that gets run once per night it would be acceptable. My original question to "query A and query B" was about situation where I'm thinking about changing my table structure a little and would like a confirmation that this change is not likely to explode the running time from 10 minutes to 2 hours.

    So I do think measuring "wall clock timings" is useful. I more or less know what values are in the acceptable range. And in this case the query is only executed by one session. Of course there's much more to performance than this, but it's one part.
  • 13. Re: How to test SQL query performance - realiably?
    1005287 Newbie
    Currently Being Moderated
    Bull! Sounds like you don't know so don't contribute if you don't know.
  • 14. Re: How to test SQL query performance - realiably?
    SomeoneElse Guru
    Currently Being Moderated
    A response 3 years later kind of loses its punch.
1 2 Previous Next

Legend

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