oracle 如何使用 PL/SQL Developer 优化查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13949660/
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
How to optimize query with PL/SQL Developer?
提问by user1852786
I have got query of ~53 strings of code and I need to optimize it. I have got PL/SQL Developer 7.0 tool and how should I use it for optimiztion?
我有大约 53 串代码的查询，我需要优化它。我有 PL/SQL Developer 7.0 工具，我应该如何使用它进行优化？
I tried to use Explain Plan, but It tells me nothing. I also added time and timestamp columns, but there is nothing interesting, first is empty and second always with same time.
我尝试使用解释计划，但它什么也没告诉我。我还添加了时间和时间戳列，但没有什么有趣的，第一个是空的，第二个总是相同的时间。
I tried to use Test Window, but there is procedure, that require variable insertion, and as my query select many rows I can't use it.
我尝试使用测试窗口，但是有一个过程需要变量插入，并且当我的查询选择了很多行时，我无法使用它。
So question is how can I optimize sql query using PL/SQL Developer? Where should I look for to get query execution time for each subquery? Maybe there are some guides, but for now I found onlyh documentation, but it wasn't usefull fore me? For my current knowledge it looks impossible to optimize such big query without any instruments.
所以问题是如何使用 PL/SQL Developer 优化 sql 查询？我应该在哪里寻找每个子查询的查询执行时间？也许有一些指南，但现在我只找到了文档，但对我来说没有用？就我目前的知识而言，没有任何工具就无法优化如此大的查询。
Query, that require optimization:
查询，需要优化：
select count(PRODUCT_NUMBER)
from
(select W.SUID,
I.SUID,
W.PRODUCT_NUMBER,
MI.ML_NUMBER,
I.TITLE_TRANSLIT,
I.COUNTRIES,
QTY.REMAINS,
QTY.ACQ_PRICE_USD,
QTY.REMAINS * QTY.ACQ_PRICE_USD as TOTAL
from (select UID_WARE,
QTY as REMAINS,
ACQ_PRICE_USD
from (select UID_WARE,
PRODUCT_NUMBER,
NVL(sum(QTY_ON_STOCK), 0) - NVL(sum(ADD_IN_QTY), 0) + NVL(sum(ADD_OUT_QTY), 0) as QTY,
ACQ_PRICE_USD as ACQ_PRICE_USD
from (select SI.UID_WARE,
W.PRODUCT_NUMBER,
count(distinct STK.SUID) as QTY_ON_STOCK,
sum(case
when 1 = 1 then
DECODE('' , SM.UID_SOURCE_LOCATION, SM.QTY, 0)
else 0
end) as ADD_OUT_QTY,
sum(case
when 1 = 1 then
DECODE('' , SM.UID_DEST_LOCATION, SM.QTY, 0)
else 0
end) as ADD_IN_QTY,
ROUND(ACQ.PRICE_USD, 2) as ACQ_PRICE_USD
from STOCK_MOVEMENTS SM
join STOCK_ITEMS SI on SM.UID_STOCK_ITEM = SI.SUID
left outer join (select PR.UID_STOCK_ITEM,
DECODE(NVL(CR.RATE, 0), 0, 0, PR.PRICE / CR.RATE) as PRICE_USD
from MV_STOCK_ACQ_PRICES PR,
CURRENCY_RATES CR
where PR.PRICE_DATE = CR.RATE_DATE
and PR.UID_CURRENCY = CR.UID_CURRENCY) ACQ on ACQ.UID_STOCK_ITEM = SI.SUID
join WARES W on W.SUID = SI.UID_WARE
left outer join (select distinct STK.SUID,
STK.QTY_REMAINS
from STOCK_ITEMS STK
where STK.UID_STOCK_LOCATION != 'MS-STL-SALED'
) STK on STK.SUID = SI.SUID
where 1 = 1
group by SI.UID_WARE,
W.PRODUCT_NUMBER,
ACQ.PRICE_USD
) T
group by T.UID_WARE,
T.PRODUCT_NUMBER,
ACQ_PRICE_USD)) QTY
join WARES W on W.SUID = QTY.UID_WARE
join INVENTORY I on I.SUID = W.UID_ISSUE
join MAP_INFO MI on MI.SUID = I.SUID
where REMAINS != 0
and w.UID_SECTION in ('MS-SEC-BOOKS', 'MS-SEC-MAPS'))
回答by David Aldridge
When you use a GUI to analyse and optimise a query you are locking yourself in to a much smaller community of potential assistance. If you run explain plan and use DBMS_XPlan.Display to fetch the formatted result then you are working with a standard method understood by tens of thousands of practitioners.
当您使用 GUI 来分析和优化查询时，您将自己锁定在一个小得多的潜在帮助社区中。如果您运行解释计划并使用 DBMS_XPlan.Display 来获取格式化的结果，那么您正在使用一种被数以万计的从业者理解的标准方法。
Also, I'm suspicious that you have wrapped the query in an outer query with just count(*). Although this may be to run the query without the hassle of returning all the rows you could well be getting a completely different execution plan, and although there are hints that might help promote the use of the usual plan there is no guarantee that it won't change. So, if this is a convenience for the sake of tuning, take it away.
另外，我怀疑您是否将查询包装在仅使用 count(*) 的外部查询中。尽管这可能是为了运行查询而无需返回所有行的麻烦，但您很可能会得到一个完全不同的执行计划，虽然有一些提示可能有助于促进通常计划的使用，但不能保证它会“改变。所以，如果这是为了调优的方便，就把它拿掉。
So my advice:
所以我的建议是：
- Run the explain plan the regular query-based way and do not rely on tools that "help".
- Run the actual query you need to tune.
- Post the execution plan.
- 以常规的基于查询的方式运行解释计划，不要依赖“帮助”工具。
- 运行您需要调整的实际查询。
- 发布执行计划。
回答by maialithar
For execution time, from here:
对于执行时间，从这里开始：
SQL> set timing on;
In PL/SQL Developer you can run each subquery independently - mark it with your mouse and press F8. You will see execution time in lower left corner of sql window (below the results).
在 PL/SQL Developer 中，您可以独立运行每个子查询 - 用鼠标标记并按F8。您将在 sql 窗口的左下角（结果下方）看到执行时间。
Optimalization is not an easy task, you have to think by yourself - according to my knowledge, there are no tools to do this for you. Your aproach may depend on many factors - db version, underlying os, table size, indexing, ...
优化不是一件容易的事，你必须自己思考——据我所知，没有工具可以为你做这件事。您的方法可能取决于许多因素 - 数据库版本、底层操作系统、表大小、索引……
You can read some guides (like this, thisor this)
UPDATE
更新
Depending on your db version, you can try using CTEor SQL modelling- they will improve performance for sure, but require some time to learn.
根据您的数据库版本，您可以尝试使用CTE或SQL 建模- 它们肯定会提高性能，但需要一些时间来学习。
Instead of group by, you can try analytic functions(e.g. partition by, see this linkfor examples).
