title: How to optimize query with PL/SQL Developer?
body: <p>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?</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>Query, that require optimization:</p>
  
  <pre><code>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'))
  </code></pre>