使用 SQL Developer 查找查询的执行时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22198853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding Execution time of query using SQL Developer
提问by java_enthu
I am beginner with Oracle DB. I want to know execution time for a query. This query returns around 20,000 records. When I see the SQL Developer, it shows only 50 rows, maximum tweakable to 500. And using F5 upto 5000.
我是 Oracle DB 的初学者。我想知道查询的执行时间。此查询返回大约 20,000 条记录。当我看到 SQL Developer 时，它只显示 50 行，最大可调整到 500。使用 F5 最多可显示 5000。
I would have done by making changes in the application, but application redeployment is not possible as it is running on production. So, I am limited to using only SQL Developer. I am not sure how to get the seconds spent for execution of the query ? Any ideas on this will help me. Thank you.
我本来可以通过在应用程序中进行更改来完成的，但是由于它正在生产中运行，因此无法重新部署应用程序。所以，我只能使用 SQL Developer。我不确定如何获得执行查询所花费的秒数？对此的任何想法都会对我有所帮助。谢谢你。
Regards, JE
问候， 乙脑
回答by
If you scroll down past the 50 rows initially returned, it fetches more. When I want all of them, I just click on the first of the 50, then press CtrlEndto scroll all the way to the bottom.
如果向下滚动超过最初返回的 50 行，它会获取更多。当我想要所有这些时，我只需单击 50 个中的第一个，然后按CtrlEnd一直滚动到底部。
This will update the display of the time that was used (just above the results it will say something like "All Rows Fetched: 20000 in 3.606 seconds") giving you an accurate time for the complete query.
这将更新所用时间的显示（就在结果上方，它会显示“所有行获取：3.606 秒内的 20000”），为您提供完整查询的准确时间。
回答by Trinimon
If your statement is part of an already deployed application and if you have rights to access the view V$SQLAREA, you could check for number of EXECUTIONSand CPU_TIME. You can search for the statement using SQL_TEXT:
如果你的语句是已部署应用程序的一部分，如果你有权限访问该视图V$SQLAREA，你可以检查的数量EXECUTIONS和CPU_TIME。您可以使用SQL_TEXT以下命令搜索语句：
SELECT CPU_TIME, EXECUTIONS
FROM V$SQLAREA
WHERE UPPER (SQL_TEXT) LIKE 'SELECT ... FROM ... %';
This is the most precise way to determine the actual run time. The view V$SESSION_LONGOPSmight also be interesting for you.
这是确定实际运行时间的最精确方法。该视图V$SESSION_LONGOPS对您来说也可能很有趣。
If you don't have access to those views you could also use a cursor loop for running through all records, e.g.
如果您无权访问这些视图，您还可以使用游标循环来遍历所有记录，例如
CREATE OR REPLACE PROCEDURE speedtest AS
count number;
cursor c_cursor is
SELECT ...;
BEGIN
-- fetch start time stamp here
count := 0;
FOR rec in c_cursor
LOOP
count := count +1;
END LOOP;
-- fetch end time stamp here
END;
Depending on the architecture this might be more or less accurate, because data might need to be transmitted to the system where your SQL is running on.
根据架构，这可能或多或少准确，因为数据可能需要传输到运行 SQL 的系统。
回答by Alex Poole
You can change those limits; but you'll be using some time in the data transfer between the DB and the client, and possibly for the display; and that in turn would be affected by the number of rows pulled by each fetch. Those things affect your application as well though, so looking at the raw execution time might not tell you the whole story anyway.
您可以更改这些限制；但是您将在 DB 和客户端之间的数据传输中使用一些时间，并且可能用于显示；而这反过来又会受到每次提取所拉出的行数的影响。但是，这些事情也会影响您的应用程序，因此查看原始执行时间可能并不能告诉您整个故事。
To change the worksheet (F5) limit, go to Tools->Preferences->Database->Worksheet, and increase the 'Max rows to print in a script' value (and maybe 'Max lines in Script output'). To change the fetch size go to the Database->Advanced panel in the preferences; maybe to match your application's value.
要更改工作表 (F5) 限制，请转到“工具”->“首选项”->“数据库”->“工作表”，并增加“在脚本中打印的最大行数”值（也可能是“脚本输出中的最大行数”）。要更改提取大小，请转到首选项中的数据库-> 高级面板；也许是为了匹配您的应用程序的价值。
This isn't perfect but if you don't want to see the actual data, just get the time it takes to run in the DB, you can wrap the query to get a single row:
这并不完美，但如果您不想查看实际数据，只需获取在数据库中运行所需的时间，您可以将查询包装起来以获取一行：
select count(*) from (
<your original query
);
It will normally execute the entire original query and then count the results, which won't add anything significant to the time. (It's feasible it might rewrite the query internally I suppose, but I think that's unlikely, and you could use hints to avoid it if needed).
它通常会执行整个原始查询，然后计算结果，这不会增加任何重要的时间。（我认为它可能在内部重写查询是可行的，但我认为这不太可能，如果需要，您可以使用提示来避免它）。
