如何在Oracle中衡量查询性能 [英] How to measure performance of query in oracle

问题描述

我是Oracle数据库的新手.我有2个查询，它们返回相同的结果集.我想衡量它们各自的性能，然后选择更好的一种.如何使用Oracle SQL Developer做到这一点?我记得读过某些工具提供了统计信息.关于如何读取这些统计数据的任何指示?

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?

更新:如Rob Van所建议，我使用tkprof实用程序来查找查询的性能.我能理解的几个参数(计数，行，经过的时间，执行时间)，但是大多数我却不明白.有人可以帮助我了解这些参数的重要性吗?以下是结果.

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

我可以确定查询2优于查询1.对磁盘，查询和当前参数意味着什么的任何帮助?

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

推荐答案

SQL编辑器上方有一个名为解释计划"的按钮.该工具将告诉您每条路线的成本以及该语句将如何使用索引和分区.请注意，您可能会遇到错误，您的DBA需要为您的用户帐户启用一项功能，我相信这是跟踪"功能，但在那一点上可能是错误的.首先，读取execute语句的输出可能会很困难，但这是帮助编写好的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.

这篇关于如何在Oracle中衡量查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！