Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: есть ли шанс оптимировать SQL?  [new]
-2-
Member

Откуда:
Сообщений: 15330
group by having или count(*) over(partition by)
28 дек 10, 18:54    [10013064]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
это очень херовый план, запрос был:

Select --+ parallel(a 16)
       *
  from abwe a
 where saktiv = 1
   and ifaktor = 0
   and exists (select 1
          from abwe b
         where b.smand = a.smand
           and b.swerk = a.swerk
           and b.spers = a.spers
           and b.ldatum = a.ldatum
           and b.saktiv = 1
           and b.izaehler != a.izaehler)

?
28 дек 10, 18:55    [10013070]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
примерно так можно попробовать:
select *
from 
(
  select a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2,
  from abwe a
  where saktiv = 1
)
where ifaktor = 0 and (izaehler <> m1 or izaehler <> m2)
28 дек 10, 18:57    [10013083]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
-2-
Member

Откуда:
Сообщений: 15330
_Nikotin,

m1<>m2
28 дек 10, 18:59    [10013089]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
Help!!
Guest
брадобрей
это очень херовый план, запрос был:

Select --+ parallel(a 16)
       *
  from abwe a
 where saktiv = 1
   and ifaktor = 0
   and exists (select 1
          from abwe b
         where b.smand = a.smand
           and b.swerk = a.swerk
           and b.spers = a.spers
           and b.ldatum = a.ldatum
           and b.saktiv = 1
           and b.izaehler != a.izaehler)

?


да. Только литералы подправил: saktiv = '1'
28 дек 10, 19:02    [10013099]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
не уверен, но всё же покажи план такого запроса
Select --+ parallel(a 16)
       *
  from abwe a
 where saktiv = 1
   and ifaktor = 0
   and exists (select --+ parallel(b 16)
               null
          from abwe b
         where b.smand = a.smand
           and b.swerk = a.swerk
           and b.spers = a.spers
           and b.ldatum = a.ldatum
           and b.saktiv = 1
           and b.izaehler != a.izaehler)
28 дек 10, 19:03    [10013100]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18370
брадобрей
не уверен, но всё же покажи план такого запроса

Раз уж полезли хинтовать parallel - то пишите уж до кучи и PQ_DISTRIBUTE...
28 дек 10, 19:07    [10013113]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
andrey_anonymous
брадобрей
не уверен, но всё же покажи план такого запроса

Раз уж полезли хинтовать parallel - то пишите уж до кучи и PQ_DISTRIBUTE...

это еще зачем?
28 дек 10, 19:08    [10013116]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
andrey_anonymous
Раз уж полезли хинтовать parallel - то пишите уж до кучи и PQ_DISTRIBUTE...

при чем здесь PQ_DISTRIBUTE если у него один FTS не параллелится
28 дек 10, 19:10    [10013120]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
Help!!
Guest
_Nikotin
примерно так можно попробовать:
select *
from 
(
  select a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2,
  from abwe a
  where saktiv = 1
)
where ifaktor = 0 and (izaehler <> m1 or izaehler <> m2)


select *
from 
(
  select a.*,
         max(izaehler) over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler) over(partition by smand, swerk, spers, ldatum) m2
  from abwe a
  where saktiv = '1'
)
where ifaktor = 0 and m1 != m2

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    37M|  9491M|       |   871K  (2)| 02:54:19 |
|*  1 |  VIEW               |      |    37M|  9491M|       |   871K  (2)| 02:54:19 |
|   2 |   WINDOW SORT       |      |    37M|  2825M|    11G|   871K  (2)| 02:54:19 |
|*  3 |    TABLE ACCESS FULL| ABWE |    37M|  2825M|       |   178K  (3)| 00:35:39 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("IFAKTOR"=0 AND "M1"<>"M2")
   3 - filter("SAKTIV"='1')
28 дек 10, 19:10    [10013122]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18370
Help!!
select *
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   2 |   WINDOW SORT       |      |    37M|  2825M|    11G|   871K  (2)| 02:54:19 |
------------------------------------------------------------------------------------

Аппетиты на TEMP выросли в 10 по отношению к оригинальному запросу.
Я бы даже не пробовал запускать :)
28 дек 10, 19:13    [10013130]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
А так?
select *
from 
(
  select /*+ parallel(a 16) */ a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2
  from abwe a
  where saktiv = 1
)
where ifaktor = 0 and m1 <> m2)
28 дек 10, 19:16    [10013140]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
можно pga_aggregate_target = 100G выставить :)
28 дек 10, 19:18    [10013146]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
_Nikotin
можно pga_aggregate_target = 100G выставить :)


А еще если вместо a.* поставить что-то более значимое и осмысленное, то ...
28 дек 10, 19:20    [10013156]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
-2-
Member

Откуда:
Сообщений: 15330
andrey_anonymous
Аппетиты на TEMP выросли в 10 по отношению к оригинальному запросу.
Я бы даже не пробовал запускать :)
В плане к оригинальному запросу на выходе hash join одна строка, а здесь 37M после всего.
28 дек 10, 19:24    [10013167]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
-2-
В плане к оригинальному запросу на выходе hash join одна строка, а здесь 37M после всего.

в любом случае m <> m после сортировки будет
28 дек 10, 19:26    [10013173]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
Help!!
Guest
_Nikotin
А так?
select *
from 
(
  select /*+ parallel(a 16) */ a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2
  from abwe a
  where saktiv = 1
)
where ifaktor = 0 and m1 <> m2)


select *
from 
(
  select /*+ parallel(a 16) */ a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2
  from abwe a
  where saktiv = '1'
)
where ifaktor = 0 and m1 <> m2;

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    37M|  9491M|       | 77275   (1)| 00:15:28 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    37M|  9491M|       | 77275   (1)| 00:15:28 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    VIEW                  |          |    37M|  9491M|       | 77275   (1)| 00:15:28 |  Q1,01 | PCWP |            |
|   4 |     WINDOW SORT          |          |    37M|  2825M|    11G| 77275   (1)| 00:15:28 |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |    37M|  2825M|       | 12340   (2)| 00:02:29 |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |    37M|  2825M|       | 12340   (2)| 00:02:29 |  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |    37M|  2825M|       | 12340   (2)| 00:02:29 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| ABWE     |    37M|  2825M|       | 12340   (2)| 00:02:29 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("IFAKTOR"=0 AND "M1"<>"M2")
   8 - filter("SAKTIV"='1')
28 дек 10, 19:28    [10013181]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
Этот план лучше. Вместо а.* ставь то что тебе действительно надо.
28 дек 10, 19:29    [10013184]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
С аналитикой на таких объемах могут быть проблемы (по крайней мере во всех 10g что я видел они есть).
Analytic Agony.
Не мешало бы помониторить v$sql_workarea_active, сколько PGA он использует под WINDOW SORT.
29 дек 10, 01:38    [10014031]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18370
wurdu
сколько PGA он использует под WINDOW SORT.

С учётом PK, диктующего достаточно скромные размеры partitions, решение на parallel аналитике действительно работоспособно - я сделал тесткейс на 10 миллионов записей. В serial результатов не дождался (боле часа), зато в parallel - достаточно приемлемый результат в 30 секунд (для сравнения - варианты на HJ давали в тех же условиях результат от 220 секунд до "не дождешься" в зависимости от PQ_DISTRIBUTE).
Но всё-таки мне кажется, что использование имеющегося индекса также может дать неплохой результат, если таблица относительно "широкая".
29 дек 10, 02:07    [10014053]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18370
Вот, дома кое-что воспроизвёл:
+

create table ABWE(SMAND number,SWERK number,SPERS number,LDATUM number,IZAEHLER number
, saktiv number,ifaktor number
, primary key(SMAND,SWERK,SPERS,LDATUM,IZAEHLER)
) NOLOGGING PARALLEL 8;

insert /*+ append nologging */
into ABWE(SMAND,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR)
select rownum,rownum,rownum,rownum,0,1,0 from dual connect by level < 1e6
union all
select rownum,rownum,rownum,rownum,1,1,0 from dual connect by level < 2;
commit;

insert /*+ append nologging */
into ABWE(SMAND,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR)
select smand+1e6,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR from abwe;
commit;

insert /*+ append nologging */
into ABWE(SMAND,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR)
select smand+2e6,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR from abwe;
commit;

insert /*+ append nologging */
into ABWE(SMAND,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR)
select smand+4e6,SWERK,SPERS,LDATUM,IZAEHLER,saktiv,IFAKTOR from abwe;
commit;

exec dbms_stats.gather_table_stats(user,'abwe',estimate_percent => null,cascade => true);



собственно, забеги:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

set echo on
col plan_table_output format a300

explain plan for
Select * from abwe a where saktiv=1 and ifaktor =0 and exists (select 1 from abwe b where
b.smand=a.smand and b.swerk=a.swerk and b.spers=a.spers and b.ldatum=a.ldatum and  b.saktiv=1
and b.izaehler != a.izaehler);
 
Explained

select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2997714394
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |  8000K|   419M|       |  6545   (1)| 00:01:19 |        |      |            |
|   1 |  PX COORDINATOR                 |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10002 |  8000K|   419M|       |  6545   (1)| 00:01:19 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN RIGHT SEMI BUFFERED|          |  8000K|   419M|    37M|  6545   (1)| 00:01:19 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                  |          |  8000K|   205M|       |  1394   (2)| 00:00:17 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH               | :TQ10000 |  8000K|   205M|       |  1394   (2)| 00:00:17 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR         |          |  8000K|   205M|       |  1394   (2)| 00:00:17 |  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL        | ABWE     |  8000K|   205M|       |  1394   (2)| 00:00:17 |  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE                  |          |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH               | :TQ10001 |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR         |          |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,01 | PCWC |            |
|* 11 |        TABLE ACCESS FULL        | ABWE     |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
   3 - access("B"."SMAND"="A"."SMAND" AND "B"."SWERK"="A"."SWERK" AND "B"."SPERS"="A"."SPERS" AND
              "B"."LDATUM"="A"."LDATUM")
       filter("B"."IZAEHLER"<>"A"."IZAEHLER")
   7 - filter("B"."SAKTIV"=1)
  11 - filter("SAKTIV"=1 AND "IFAKTOR"=0)
 
27 rows selected

set timing on
Select * from abwe a where saktiv=1 and ifaktor =0 and exists (select 1 from abwe b where
b.smand=a.smand and b.swerk=a.swerk and b.spers=a.spers and b.ldatum=a.ldatum and  b.saktiv=1
and b.izaehler != a.izaehler);
 
     SMAND      SWERK      SPERS     LDATUM   IZAEHLER     SAKTIV    IFAKTOR
---------- ---------- ---------- ---------- ---------- ---------- ----------
   1000001          1          1          1          0          1          0
   1000001          1          1          1          1          1          0
...
   4000001          1          1          1          0          1          0
   4000001          1          1          1          1          1          0
 
16 rows selected
 
Executed in 61,125 seconds

set timing off

-- "Плохой" план
explain plan for
Select /*+ first_rows */ * from abwe a where saktiv=1 and ifaktor =0 and exists (select 1 from abwe b where
b.smand=a.smand and b.swerk=a.swerk and b.spers=a.spers and b.ldatum=a.ldatum and  b.saktiv=1
and b.izaehler != a.izaehler);
 
Explained
select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2093679088
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |  8000K|   419M|  3335K  (1)| 11:07:12 |        |      |            |
|   1 |  PX COORDINATOR                |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10000     |  8000K|   419M|  3335K  (1)| 11:07:12 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS SEMI           |              |  8000K|   419M|  3335K  (1)| 11:07:12 |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR          |              |       |       |            |          |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL         | ABWE         |  8000K|   213M|  1398   (2)| 00:00:17 |  Q1,00 | PCWP |            |
|*  6 |     TABLE ACCESS BY INDEX ROWID| ABWE         |  8000K|   205M|     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      INDEX RANGE SCAN          | SYS_C0015890 |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("SAKTIV"=1 AND "IFAKTOR"=0)
   6 - filter("B"."SAKTIV"=1)
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   7 - access("B"."SMAND"="A"."SMAND" AND "B"."SWERK"="A"."SWERK" AND "B"."SPERS"="A"."SPERS" AND
              "B"."LDATUM"="A"."LDATUM")
       filter("B"."IZAEHLER"<>"A"."IZAEHLER")
 
23 rows selected

set timing on
Select  /*+ first_rows */ * from abwe a where saktiv=1 and ifaktor =0 and exists (select 1 from abwe b where
b.smand=a.smand and b.swerk=a.swerk and b.spers=a.spers and b.ldatum=a.ldatum and  b.saktiv=1
and b.izaehler != a.izaehler);
 
     SMAND      SWERK      SPERS     LDATUM   IZAEHLER     SAKTIV    IFAKTOR
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          0
   1000001          1          1          1          0          1          0
...
   5000001          1          1          1          1          1          0
   7000001          1          1          1          1          1          0
 
16 rows selected
 
Executed in 44,079 seconds
set timing off

-- Аналитика, "хороший" план
explain plan for
select *
from
(
  select /*+ parallel(a 8) */ a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2
  from abwe a
  where saktiv = '1'
)
where ifaktor = 0 and m1 <> m2;
 
Explained
select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 263047607
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |  8000K|   892M|       | 10052   (1)| 00:02:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  8000K|   892M|       | 10052   (1)| 00:02:01 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    VIEW                  |          |  8000K|   892M|       | 10052   (1)| 00:02:01 |  Q1,01 | PCWP |            |
|   4 |     WINDOW SORT          |          |  8000K|   213M|   367M| 10052   (1)| 00:02:01 |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  8000K|   213M|       |  1395   (2)| 00:00:17 |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |  8000K|   213M|       |  1395   (2)| 00:00:17 |  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |  8000K|   213M|       |  1395   (2)| 00:00:17 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| ABWE     |  8000K|   213M|       |  1395   (2)| 00:00:17 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("IFAKTOR"=0 AND "M1"<>"M2")
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   8 - filter("SAKTIV"=1)
 
21 rows selected

set timing on
select *
from
(
  select /*+ parallel(a 8) */ a.*,
         max(izaehler)over(partition by smand, swerk, spers, ldatum) m1,
         min(izaehler)over(partition by smand, swerk, spers, ldatum) m2
  from abwe a
  where saktiv = '1'
)
where ifaktor = 0 and m1 <> m2;
 
     SMAND      SWERK      SPERS     LDATUM   IZAEHLER     SAKTIV    IFAKTOR         M1         M2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
   5000001          1          1          1          0          1          0          1          0
   5000001          1          1          1          1          1          0          1          0
...
   7000001          1          1          1          0          1          0          1          0
   7000001          1          1          1          1          1          0          1          0
 
16 rows selected
 
Executed in 52,031 seconds
set timing off
SQL> 
29 дек 10, 03:31    [10014073]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18370
Ну и свой вариант:
+

explain plan for
select * from abwe a
where (SMAND,SWERK,SPERS,LDATUM)
    in( Select SMAND,SWERK,SPERS,LDATUM
        from abwe b
        group by SMAND,SWERK,SPERS,LDATUM
        having count(IZAEHLER) > 1)
and saktiv = 1 and ifaktor=0;
 
Explained
select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1865662531
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |  8000K|   610M|       |  6625   (2)| 00:01:20 |        |      |            |
|   1 |  PX COORDINATOR                  |              |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10003     |  8000K|   610M|       |  6625   (2)| 00:01:20 |  Q1,03 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN RIGHT SEMI BUFFERED |              |  8000K|   610M|       |  6625   (2)| 00:01:20 |  Q1,03 | PCWP |            |
|   4 |     JOIN FILTER CREATE           | :BF0000      |   400K|    19M|       |  5222   (2)| 00:01:03 |  Q1,03 | PCWP |            |
|   5 |      PX RECEIVE                  |              |   400K|    19M|       |  5222   (2)| 00:01:03 |  Q1,03 | PCWP |            |
|   6 |       PX SEND HASH               | :TQ10001     |   400K|    19M|       |  5222   (2)| 00:01:03 |  Q1,01 | P->P | HASH       |
|   7 |        VIEW                      | VW_NSO_1     |   400K|    19M|       |  5222   (2)| 00:01:03 |  Q1,01 | PCWP |            |
|*  8 |         FILTER                   |              |       |       |       |            |          |  Q1,01 | PCWC |            |
|   9 |          HASH GROUP BY           |              | 20000 |  8203K|   276M|  5222   (2)| 00:01:03 |  Q1,01 | PCWP |            |
|  10 |           PX RECEIVE             |              |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,01 | PCWP |            |
|  11 |            PX SEND HASH          | :TQ10000     |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,00 | P->P | HASH       |
|  12 |             PX BLOCK ITERATOR    |              |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,00 | PCWC |            |
|  13 |              INDEX FAST FULL SCAN| SYS_C0015890 |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,00 | PCWP |            |
|  14 |     PX RECEIVE                   |              |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,03 | PCWP |            |
|  15 |      PX SEND HASH                | :TQ10002     |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,02 | P->P | HASH       |
|  16 |       JOIN FILTER USE            | :BF0000      |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,02 | PCWP |            |
|  17 |        PX BLOCK ITERATOR         |              |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,02 | PCWC |            |
|* 18 |         TABLE ACCESS FULL        | ABWE         |  8000K|   213M|       |  1398   (2)| 00:00:17 |  Q1,02 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SMAND"="SMAND" AND "SWERK"="SWERK" AND "SPERS"="SPERS" AND "LDATUM"="LDATUM")
   8 - filter(COUNT(*)>1)
  18 - filter("SAKTIV"=1 AND "IFAKTOR"=0 AND SYS_OP_BLOOM_FILTER(:BF0000,"SMAND","SWERK","SPERS","LDATUM"))
 
32 rows selected
set timing on
select * from abwe a
where (SMAND,SWERK,SPERS,LDATUM)
    in( Select SMAND,SWERK,SPERS,LDATUM
        from abwe b
        group by SMAND,SWERK,SPERS,LDATUM
        having count(IZAEHLER) > 1)
and saktiv = 1 and ifaktor=0;
 
     SMAND      SWERK      SPERS     LDATUM   IZAEHLER     SAKTIV    IFAKTOR
---------- ---------- ---------- ---------- ---------- ---------- ----------
   1000001          1          1          1          0          1          0
   1000001          1          1          1          1          1          0
...
   5000001          1          1          1          0          1          0
   5000001          1          1          1          1          1          0
 
16 rows selected
 
Executed in 23,906 seconds
set timing off
SQL> 

или лучше так:
+
explain plan for
select /*+ index(a)*/ * from abwe a
where (SMAND,SWERK,SPERS,LDATUM)
    in( Select SMAND,SWERK,SPERS,LDATUM
        from abwe b
        group by SMAND,SWERK,SPERS,LDATUM
        having count(IZAEHLER) > 1)
and saktiv = 1 and ifaktor=0;
 
Explained

select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3216852839
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              | 20000 |  1562K|       | 10795   (1)| 00:02:10 |        |      |            |
|   1 |  PX COORDINATOR                |              |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10001     |       |       |       |            |          |  Q1,01 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                |              |       |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     NESTED LOOPS               |              | 20000 |  1562K|       | 10795   (1)| 00:02:10 |  Q1,01 | PCWP |            |
|   5 |      VIEW                      | VW_NSO_1     |   400K|    19M|       |  5222   (2)| 00:01:03 |  Q1,01 | PCWP |            |
|*  6 |       FILTER                   |              |       |       |       |            |          |  Q1,01 | PCWC |            |
|   7 |        HASH GROUP BY           |              |  1000 |  8203K|   276M|  5222   (2)| 00:01:03 |  Q1,01 | PCWP |            |
|   8 |         PX RECEIVE             |              |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,01 | PCWP |            |
|   9 |          PX SEND HASH          | :TQ10000     |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,00 | P->P | HASH       |
|  10 |           PX BLOCK ITERATOR    |              |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,00 | PCWC |            |
|  11 |            INDEX FAST FULL SCAN| SYS_C0015890 |  8000K|   160M|       |  1246   (1)| 00:00:15 |  Q1,00 | PCWP |            |
|* 12 |      INDEX RANGE SCAN          | SYS_C0015890 |     1 |       |       |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 13 |     TABLE ACCESS BY INDEX ROWID| ABWE         |     1 |    28 |       |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(COUNT(*)>1)
  12 - access("SMAND"="SMAND" AND "SWERK"="SWERK" AND "SPERS"="SPERS" AND "LDATUM"="LDATUM")
  13 - filter("SAKTIV"=1 AND "IFAKTOR"=0)
 
27 rows selected
set timing on
select /*+ index(a)*/ * from abwe a
where (SMAND,SWERK,SPERS,LDATUM)
    in( Select SMAND,SWERK,SPERS,LDATUM
        from abwe b
        group by SMAND,SWERK,SPERS,LDATUM
        having count(IZAEHLER) > 1)
and saktiv = 1 and ifaktor=0;
 
     SMAND      SWERK      SPERS     LDATUM   IZAEHLER     SAKTIV    IFAKTOR
---------- ---------- ---------- ---------- ---------- ---------- ----------
   1000001          1          1          1          0          1          0
   1000001          1          1          1          1          1          0
...
   6000001          1          1          1          0          1          0
   2000001          1          1          1          1          1          0
 
16 rows selected
 
Executed in 20,734 seconds
 
SQL> 
29 дек 10, 03:51    [10014076]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
sendal
Member

Откуда: Азия
Сообщений: 29990
Help!!
.......
спасибо, но дополнительное условие задачи: новых индексов создавать нельзя (заказчик очень долго проверяет DDL прежде чем даст разрешение, а результаты нужны срочно). сорри, забыл сразу сказать!
.......

А перечнем имеющихся индексов можно поинтересоваться ?
29 дек 10, 08:50    [10014209]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
Help!!
Guest
sendal
Help!!
.......
спасибо, но дополнительное условие задачи: новых индексов создавать нельзя (заказчик очень долго проверяет DDL прежде чем даст разрешение, а результаты нужны срочно). сорри, забыл сразу сказать!
.......

А перечнем имеющихся индексов можно поинтересоваться ?


кроме ПК еще один на (SMAND,SWERK,SPERS). Я его не упомянул, т.к. судя по плану он не помогает и не мешает...
29 дек 10, 13:24    [10016138]     Ответить | Цитировать Сообщить модератору
 Re: есть ли шанс оптимировать SQL?  [new]
Help!!
Guest
andrey_anonymous
или лучше так:
explain plan for
select /*+ index(a)*/ * from abwe a
where (SMAND,SWERK,SPERS,LDATUM)
    in( Select SMAND,SWERK,SPERS,LDATUM
        from abwe b
        group by SMAND,SWERK,SPERS,LDATUM
        having count(IZAEHLER) > 1)
and saktiv = 1 and ifaktor=0;


у меня план получается такой:
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 94166 |  9747K|       |   621K  (2)| 02:04:17 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ABWE          |     1 |    78 |       |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |               | 94166 |  9747K|       |   621K  (2)| 02:04:17 |
|   3 |    VIEW                     | VW_NSO_1      |  3463K|    92M|       |   247K  (4)| 00:49:29 |
|*  4 |     FILTER                  |               |       |       |       |            |          |
|   5 |      HASH GROUP BY          |               |   173K|    59M|  2228M|   247K  (4)| 00:49:29 |
|   6 |       INDEX FAST FULL SCAN  | SYS_C00152073 |    69M|  1189M|       | 89060   (2)| 00:17:49 |
|*  7 |    INDEX RANGE SCAN         | SYS_C00152073 |     1 |       |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("SAKTIV")=1 AND "IFAKTOR"=0)
   4 - filter(COUNT(*)>1)
   7 - access("SMAND"="$nso_col_1" AND "SWERK"="$nso_col_2" AND "SPERS"="$nso_col_3" AND
              "LDATUM"="$nso_col_4")

и выполняется уже 12 минут :-(

но наверное это не полный эквивалент. Верно было бы:
select /*+ index(a)*/ * from abwe a
where (SMAND,SWERK,SPERS,LDATUM)
    in( Select SMAND,SWERK,SPERS,LDATUM
        from abwe b
        [color=yellow]where saktiv = '1'[/color]
        group by SMAND,SWERK,SPERS,LDATUM
        having count(IZAEHLER) > 1)
and saktiv = '1' and ifaktor=0

а тут план уже другой :-(
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 51644 |  5345K|       |   474K  (2)| 01:34:49 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ABWE          |     1 |    78 |       |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |               | 51644 |  5345K|       |   474K  (2)| 01:34:49 |
|   3 |    VIEW                     | VW_NSO_1      |  1899K|    50M|       |   268K  (3)| 00:53:48 |
|*  4 |     FILTER                  |               |       |       |       |            |          |
|   5 |      HASH GROUP BY          |               | 94969 |    36M|  1378M|   268K  (3)| 00:53:48 |
|*  6 |       TABLE ACCESS FULL     | ABWE          |    37M|   724M|       |   176K  (2)| 00:35:21 |
|*  7 |    INDEX RANGE SCAN         | SYS_C00152073 |     1 |       |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAKTIV"='1' AND "IFAKTOR"=0)
   4 - filter(COUNT(*)>1)
   6 - filter("SAKTIV"='1')
   7 - access("SMAND"="$nso_col_1" AND "SWERK"="$nso_col_2" AND "SPERS"="$nso_col_3" AND
              "LDATUM"="$nso_col_4")

Вариант с параллельностью убил на 27-й минуте...
29 дек 10, 13:51    [10016376]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Oracle Ответить