How to measure performance of query in oracle

Question

I'm new to Oracle db. I have 2 queries which return the same result set. I want to measure the performance of each of them and choose the better one. How do I do that using Oracle SQL developer? I remember reading that certain tools provide stats. Any pointers on how to read these stats?

Update: As suggested by Rob Van, I used the tkprof utility to find the performance of my queries. A few parameters I could understand (count,rows,elapsed time,execution time), but most I couldn't. Can anybody help me out with the significance of those parameters? Below are the results.

Query 1:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       49      0.26       0.32         45        494          0       23959
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       51      0.28       0.33         45        494          0       23959

Query2:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       33      0.25       0.24          0        904          0       15992
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       35      0.25       0.24          0        904          0       15992

I can decide that query 2 is better than query 1. Any help on what disk, query and current params mean??

Bifurcate

You mentioned that query 1 and query 2 return the same result set. However, the tkprof file shows that query 1 returns 23959 rows, and query 2 returns 15992 rows. Not exactly equal... – Sprue

Okie the query is part of a view creation. The number of the rows returned are different because the way the queries create the view are different. The 1st query uses unions and the 2nd query uses LOJ and since the 1st query uses unios, a few records are duplicated which doesnt happen in the case of second query – Bifurcate

Answer 1

There is button above the SQL editor called "Explain Plan". This tool will tell you what each route costs, and how the statement will use indexes and partitions. Note, you may get an error, your DBA will need to turn on a feature for your user account, I believe it is "trace", but could be wrong on that point. Reading the execute statement output can be challenging at first, but it is a good tool to helping write good SQL.

Oblast

Thx.Yep i did see the results under explain tab. A column called cost appears with numbers. I would have to dig into oracle docs to interpret what these numbers mean – Bifurcate

I highly recommend digging into those docs. Do some googling on optimization and Oracle tuning. (Burleson has some good stuff - google "Oracle Burleson" to see a consulting site that includes tips.) And check out this for an overview of what you see with "explain plan." akadia.com/services/ora_interpreting_explain_plan.html As noted in another comment, just checking speed doesn't mean much. Environment can change things - fast in one setting can be slow in another. – Bewhiskered

But learning the stuff can take time. Do you best, check for speed for now, and then absorb as much as you can and tune as you go. – Bewhiskered

Answer 2

I'm afraid EXPLAIN PLAN is the only way. Start off by assuming that a lower cost (if you look at the explain plan there should be a column called COST) is better, but you need to read up about it so that you learn more and more. Maybe you have a DBA there that you could chat stuff through with? Without having your data and the queries it is difficult to advise further

For anything to do with developing Oracle, a good place to start is anything by Tom Kyte (Google him) or search through the Ask Tom website. If you really want to get involved

Only running the query a few times is a pretty bad idea - equivalent to just accepting that the cost of the explain plan tells you the best query. You really need to take into account what resources your query is taking up and therefore how it could affect you production system.

Depending on how often the query gets called affects how well you need to track a query's performance (sorry to purists, but it does). If the query only runs once a week and takes a minute to run without affecting anything else then do you need to optimise that query? Is it easier for maintenance to use a query that is more easy to logically follow?

If the query is being called multiple times a second then you need to completely understand the explain plan, and have a further understanding on how you can optimise the query to its best performance

Tbar

Answer 3

Basic answer - execute each query a couple of times and see which one is faster. Best part - you can do this without learning about Oracle performance. The only thing you need to know is that you can not trust the first try because most of the time it will read data from disk while second try will use cached data from RAM. That is why you try each query a couple of times.

Beltane

What if the "faster" query uses a lot of temp space? Once moved to production, with multiple users using that temp space, the "faster" query could become slower because it could start paging. The problem with determining query speed is that the answer is always "it depends". it's best to use explain plan, making note of temp space used, too, and then evaluate in terms of the server environment. Though that's tough to do well - I'm still learning. – Bewhiskered

Explain plan is good but first you have to be able to read it. If you have to make a choice today between two queries and you have not seen explain plan tools in your whole life, those NESTED LOOP/HASH JOIN/ INDEX RANGE SCAN / INDEX FFS will not really help you. If you have weeks available then sure, go and learn Oracle performance tuning first and write queries later. – Beltane

Answer 4

On OTN, Randolf Geist and me have written two posts about how to measure performance. If you do as indicated in those threads, you will have gathered information to be able to choose the better one.

If you want the full version, visit Randolf's.

If you want a short version, visit mine:

Both threads explain how to use explain plan and tracing to see where time is spent. Then it is up to you to decide what you exactly qualify as "better". This can be the shortest elapsed time, the least amount of used resources, or the smallest number of latches, or maybe something else.

Hope this helps.

Regards, Rob.

Sprue

The links are dead – Puce