Долго отрабатывают запросы с сортировкой (Order by) : Oracle

Vlad · █ 01.03.2009 16:02

Была у меня тестовая база на Oracle 8.1.6 экспортнул данные через exp.exe.
Установил по инструкции Oracle 9.2.0.1, установил патч 9.2.0.8
Создал БД также по инструкции
Импортнул дамп БД.
Выдал гранты, создал вьюху на Jobs, перкомпилировал все
База запускаетс нормально, Супермаг с ней работает, товародвижение считается, правда перенос шел долго около 5 часов.
Проблемма вот в чем например вполняю запрос

Код:

 
select * from smcard

запрос выполняется за 312 мс
если же делаю вот такой запрос

Код:

select * from smcard order by article

то запрос выполняется за 3!!! сек
посмотрел параметры sort_area_size = 4m
поднял до 8m результат тот же
остальные параметры как в инсрукции.
Намекните куда копать?

OlegON · █ 01.03.2009 20:17

План дай?

Код:

SQL> select * from supermag.smcard;

Execution Plan
----------------------------------------------------------
Plan hash value: 2401187839

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 40020 |    12M|   211   (6)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| SMCARD | 40020 |    12M|   211   (6)| 00:00:01 |
----------------------------------------------------------------------------

SQL> select * from supermag.smcard order by article;

Execution Plan
----------------------------------------------------------
Plan hash value: 2415359591

-------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        | 40020 |    12M|       |  4434   (1)| 00:00:11 |
|   1 |  SORT ORDER BY     |        | 40020 |    12M|    31M|  4434   (1)| 00:00:11 |
|   2 |   TABLE ACCESS FULL| SMCARD | 40020 |    12M|       |   211   (6)| 00:00:01 |
-------------------------------------------------------------------------------------

Vlad · █ 02.03.2009 10:03

Для select * from smcard:

Код:

 0      SELECT STATEMENT optimizer=CHOOSE (cost=124 card=72362 bytes
        =13965866)

 1    0   TABLE ACCESS(FULL) of 'SMCARD' (cost=124 card=72362 bytes=
        13965866)

Для select * from smcard order by article:

Код:

0      SELECT STATEMENT optimizer=CHOOSE (cost=2171 card=72362 bytes
       =13965866)

1    0   SORT(ORDER BY) (cost=2171 card=72362 bytes=13965866)
2    1     TABLE ACCESS(FULL) of 'SMCARD' (cost=124 card=72362 bytes
       =13965866)

Оно?

deucel · █ 02.03.2009 11:22

Цитата:

Vlad ➤ поднял до 8m результат тот же

Код:

1    0   SORT(ORDER BY) (cost=2171 card=72362 bytes=13`965`866)

оно?!
просто у тебя дисковая сортировка
:)

Vlad · █ 02.03.2009 13:23

Вот этот запрос на Oracle 8i выполняется за 57сек на 9ке за 20мин:

Код:

SELECT x.article, (SELECT c.datatype
            FROM supermag.svcardname c
            WHERE c.article = x.article)
    FROM (SELECT DISTINCT article
              FROM supermag.ffspec) x
    ORDER BY x.article

План для 9-ки:

Код:

   i    p PLAN_PLUS_EXP                                                OBJECT_N
   0      SELECT STATEMENT optimizer=CHOOSE (cost=91086 card=1 bytes=27)
   1    0   NESTED LOOPS (cost=2 card=1 bytes=12)
   2    1     TABLE ACCESS(BY INDEX ROWID) of 'SMCARD' (cost=1 card=1 bytes=10)
   3    2       INDEX(UNIQUE SCAN) of 'SMCARD_PK' UNIQUE) (cost=1 card=1)
   4    1     INDEX(UNIQUE SCAN) of 'SACMEASUREMENT_PK' UNIQUE)
   5    0   SORT(ORDER BY) (cost=91086 card=1 bytes=27)
   6    5     VIEW (cost=91084 card=1 bytes=27)
   7    6       SORT(UNIQUE NOSORT) (cost=91084 card=1 bytes=27)
   8    7         INDEX(FULL SCAN) of 'FFSPEC_ART' NON-UNIQUE)

А вот для 8-ки

Код:

  i    p PLAN_PLUS_EXP                                                OBJECT_N
--- ---- ------------------------------------------------------------ --------
  0      SELECT STATEMENToptimizer=CHOOSE (cost=78414 card=63909 byte
         s=1725543)

  1    0   SORT(ORDER BY) (cost=78414 card=63909 bytes=1725543)
  2    1     VIEW (cost=78087 card=63909 bytes=1725543)
  3    2       SORT(UNIQUE) (cost=78087 card=63909 bytes=319545)
  4    3         INDEX(FAST FULL SCAN) of 'FFSPEC_ART' NON-UNIQUE) (c
         ost=6706 card=35339324 bytes=176696620)

OlegON · █ 02.03.2009 17:07

У меня так (10ка, но не очень корректное сравнение, долго листался список по ssh)

Код:

Elapsed: 00:00:19.68

Execution Plan
----------------------------------------------------------
Plan hash value: 595625094

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  5330 |   140K| 33054  (19)| 00:01:18 |        |      |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| SMCARD    |     1 |    12 |     2   (0)| 00:00:01 |        |      |            |
|*  2 |   INDEX UNIQUE SCAN         | SMCARD_PK |     1 |       |     1   (0)| 00:00:01 |        |      |            |
|   3 |  PX COORDINATOR             |           |       |       |            |          |        |      |            |
|   4 |   PX SEND QC (ORDER)        | :TQ10001  |  5330 |   140K| 33054  (19)| 00:01:18 |  Q1,01 | P->S | QC (ORDER) |
|   5 |    SORT ORDER BY            |           |  5330 |   140K| 33054  (19)| 00:01:18 |  Q1,01 | PCWP |            |
|   6 |     VIEW                    |           |  5330 |   140K| 33054  (19)| 00:01:18 |  Q1,01 | PCWP |            |
|   7 |      HASH UNIQUE            |           |  5330 | 31980 | 33054  (19)| 00:01:18 |  Q1,01 | PCWP |            |
|   8 |       PX RECEIVE            |           |    26M|   149M| 27777   (4)| 00:01:06 |  Q1,01 | PCWP |            |
|   9 |        PX SEND RANGE        | :TQ10000  |    26M|   149M| 27777   (4)| 00:01:06 |  Q1,00 | P->P | RANGE      |
|  10 |         PX BLOCK ITERATOR   |           |    26M|   149M| 27777   (4)| 00:01:06 |  Q1,00 | PCWC |            |
|  11 |          TABLE ACCESS FULL  | FFSPEC    |    26M|   149M| 27777   (4)| 00:01:06 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("C"."ARTICLE"=:B1)


Statistics
----------------------------------------------------------
        622  recursive calls
          3  db block gets
      95979  consistent gets
      31292  physical reads
        752  redo size
     748348  bytes sent via SQL*Net to client
      24120  bytes received via SQL*Net from client
       2150  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
      32234  rows processed

Vlad · █ 03.03.2009 07:38

Собрал статистику с помощью compute statistic, поменялся план для запроса

Код:

SELECT x.article, (SELECT c.datatype
            FROM supermag.svcardname c
            WHERE c.article = x.article)
    FROM (SELECT DISTINCT article
              FROM supermag.ffspec) x
    ORDER BY x.article

  i    p PLAN_PLUS_EXP                                                OBJECT_N
--- ---- ------------------------------------------------------------ --------
  0      SELECT STATEMENT optimizer=CHOOSE cost=88923card=63909         bytes=1725543)
  1    0   NESTED LOOPS (cost=2 card=1 bytes=12)
  2    1     TABLE ACCESS(BY INDEX ROWID) of 'SMCARD' (cost=1 card=1 bytes=10)
  3    2       INDEX(UNIQUE SCAN) of 'SMCARD_PK' UNIQUE) (cost=1 card=1)
  4    1     INDEX(UNIQUE SCAN) of 'SACMEASUREMENT_PK' UNIQUE)
  5    0   SORT(ORDER BY) (cost=88923 card=63909 bytes=1725543)
  6    5     VIEW (cost=88598 card=63909 bytes=1725543)
  7    6       SORT(UNIQUE) (cost=88598 card=63909 bytes=383454)
  8    7         INDEX(FAST FULL SCAN) of 'FFSPEC_ART' NON-UNIQUE) (c
         ost=11110 card=34717980 bytes=208307880)

Цитата:

deucel ➤ просто у тебя дисковая сортировка

Я так понял чтобы уменьшить дисковую сортировку, необходимо увеличить размер sort_area_size? Но как вычислить оптимальный размер,
ведь может и памяти не хватить?

P. s. после полного сбора статистики, оно собралась и по темповым таблицам, нужна она там?

OlegON · █ 03.03.2009 11:53

Цитата:

Vlad ➤ Я так понял чтобы уменьшить дисковую сортировку, необходимо увеличить размер sort_area_size? Но как вычислить оптимальный размер,
ведь может и памяти не хватить?
P. s. после полного сбора статистики, оно собралась и по темповым таблицам, нужна она там?

Ты бы покопал в сторону изменений, сделанных в 9ке по отношению к 8ке, их много... Например, про PGA_AGGREGATE_TARGET почитай, SORT_AREA_SIZE убрали... С темповых статистику снеси, она в большинстве случаев неправильная.
кстати, сделай еще

Цитата:

select name, isspecified from v$obsolete_parameter;

Vlad · █ 03.03.2009 13:42

Цитата:

OlegON ➤ кстати, сделай еще

Код:

select * from v$obsolete_parameter;

Результат сюда привести?

OlegON · █ 03.03.2009 14:12

Цитата:

Vlad ➤ Результат сюда привести?

Да как хочешь. Это для тебя информация. Какие устаревшие параметры ты использовал.
Собственно, еще остается этот вариант ;)