How to measure query performance in oracle

Question

I am new to Oracle db. I have 2 queries that return the same result set. I want to measure the performance of each of them and choose the best. How to do this using Oracle SQL developer? I remember reading that some tools provide statistics. Any pointers on how to read these statistics?

Update: as Rob Wang suggested, I used the tkprof utility to find the performance of my queries. A few parameters that I could understand (counting, lines, elapsed time, runtime), but most of them I could not. Can someone help me with the significance of these 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 regarding which drive, query, and current settings mean?

Answer 1

Above the SQL editor is called Explain Plan. This tool will tell you what each route is worth, and how the operator will use indexes and sections. Please note that you may receive an error message, your database administrator will have to enable the function for your user account, I believe that this is a "trace", but may be erroneous at this point. Reading the output of the execution statement may be problematic at first, but it is a good tool to help write good SQL.

Answer 2

At OTN, Randolph Geist and I wrote two notes on how to measure performance. If you do as indicated in these threads, you will receive information to choose the best.

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

If you want a short version, visit mine :

Both threads explain how to use an explanation plan and trace to find out where the time is spent. Then you decide what exactly you qualify as "better." This may be the shortest elapsed time, the least amount of resources used or the least number of latches, or maybe something else.

Hope this helps.

Regards, Rob.

Answer 3

The main answer is to complete each request a couple of times and see which one is faster. The best part is you can do this without learning about Oracle performance. The only thing you need to know is that you cannot trust the first attempt, because most of the time it will read data from the disk, and the second attempt will use cached data from RAM. This is why you try to execute each request a couple of times.

Answer 4

I am afraid that EXPLAIN PLAN is the only way. Start by assuming a lower cost (if you look at the explanation plan, there should be a column called COST), but you need to read about it so that you learn more and more. Maybe you have a DBA with whom you could communicate? Without your data and queries, it’s difficult to advise further

Regarding the development of Oracle, a good place to start is that Tom Keith (Google it) or search through the Ask Tom website . If you really want to get involved

Running a request multiple times — a pretty bad idea — is tantamount to simply recognizing that the cost of an explanation plan will tell you the best request. You really need to consider what resources your request takes, and therefore how it can affect your production system.

Depending on how often the request is called, it affects how well you need to track the performance of the request (sorry for purists, but it is). If the request is executed only once a week and takes a minute to work without affecting anything, then you need to optimize this request? Is it easier to maintain a query that is easier to follow logically?

If the request is called several times per second, you need to fully understand the explanation plan and get an additional idea of ​​how to optimize the request to maximum performance.