如何衡量oracle查询的性能
我是 Oracle 数据库新手。我有两个返回相同结果集的查询。我想衡量他们每个人的表现并选择更好的一个。如何使用 Oracle SQL Developer 来做到这一点？我记得读过某些工具提供统计数据。有关如何阅读这些统计数据的任何指示吗？
更新：按照 Rob Van 的建议，我使用 tkprof 实用程序来查找查询的性能。我可以理解一些参数（计数、行数、经过的时间、执行时间），但大多数我不能理解。谁能帮我解释一下这些参数的意义吗？以下是结果。
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
我可以确定查询 2 比查询 1 更好。有关磁盘、查询和当前参数含义的任何帮助吗？
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??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
SQL 编辑器上方有一个名为“解释计划”的按钮。该工具将告诉您每条路由的成本，以及语句将如何使用索引和分区。请注意，您可能会收到错误，您的 DBA 将需要为您的用户帐户打开一项功能，我相信它是“跟踪”，但在这一点上可能是错误的。读取执行语句输出一开始可能会很困难，但它是帮助编写良好 SQL 的好工具。
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.
恐怕解释计划是唯一的方法。首先假设成本较低（如果您查看解释计划，应该有一个名为“成本”的列）更好，但您需要阅读相关内容，以便了解越来越多的信息。也许您那里有一位可以与您聊天的 DBA？如果没有您的数据和查询，就很难提供进一步的建议
对于与开发 Oracle 相关的任何内容，一个好的起点是 Tom Kyte（Google 他）的任何内容或搜索 Ask Tom 网站。如果您确实想参与其中，
只运行几次查询是一个非常糟糕的主意 - 相当于只接受解释计划的成本告诉您最佳查询。您确实需要考虑您的查询占用了哪些资源，以及它如何影响您的生产系统。
查询调用的频率会影响您跟踪查询性能的程度（对纯粹主义者来说很抱歉，但事实确实如此）。如果查询每周只运行一次并且需要一分钟才能运行而不影响其他任何内容那么您是否需要优化该查询？使用逻辑上更容易遵循的查询是否更容易维护？
如果查询每秒被调用多次，那么您需要完全理解解释计划，并进一步了解如何优化查询以获得最佳性能
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
基本答案 - 执行每个查询几次，看看哪个更快。最好的部分 - 您无需了解 Oracle 性能即可完成此操作。您唯一需要知道的是，您不能信任第一次尝试，因为大多数时候它会从磁盘读取数据，而第二次尝试将使用 RAM 中的缓存数据。这就是为什么您要多次尝试每个查询。
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.
在 OTN 上，Randolf Geist 和我写了两篇关于如何衡量性能的文章。如果您按照这些线程中的指示进行操作，您将收集到能够选择更好的信息。
如果您想要完整版本，请访问 Randolf 的。
如果您想要简短版本，请访问 我的：
两个线程都解释了如何使用解释计划和跟踪来查看时间花在哪里。然后由您决定什么才是“更好”。这可以是最短的运行时间、最少的使用资源量、或者最少的锁存器数量，或者可能是其他东西。
希望这有帮助。
问候，
抢。
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.