Прошу помощи по настройке 10g : Oracle

kadr · █ 27.01.2009 13:59

Цитата:

mighty ➤

Код:

select sum(decode(f.saletype,'CR',-f.salesum,f.salesum)) realiz from supermag.ffmaprep f where f.rectype=1 and f.saletype in ('CR','CS') 
and f.saledate>=to_date('20.01.2005','DD.MM.YYYY')
and f.saledate<=to_date('19.01.2009','DD.MM.YYYY')
and f.article in ( select c.article from supermag.smcard c where  c.accepted<>-1  )

Запрос при вышеописанном плане отрабатывал 631 секунда - плохо, на таких объемах данных еще 2 месяца назад этот запрос отрабатывался за минуту или даже меньше но там была 9.2.0.8 и 32 разрядная.

Kard, а после изменения optimizer_index_cost_adj статистику надо пересобирать? или план запроса сразу меняется?

сразу меняется, по докам этот параметр показывает стоимость индексного доступа, т.е. по умолчанию 100%, если ставить 50%, то оптимизатору указывается что стоимость индексного пути доступа будет выглядеть наполовину меньше.

Цитата:

Arsen ➤ Советую не менять этот мараметр - optimizer_index_cost_adj.

Я тоже читал, что при таких значениях план меняется на индексы, но когда поменял его, то случилось вот что: когда редактируешь карточки товаров, сохраниние длится около 3 минуты.

Любые параметры нужно менять осознанно. План меняется на индексный доступ, но он не всегда самый быстрый, иногда быстрее просканить всю таблицу, чем сначала искать в индексе, потом считывать блок данных, а потом опять искать по индексу и опять считывать блок данных.

isi · █ 28.01.2009 02:43

при твоем условии

Код:

 f.saledate>=to_date('20.01.2005','DD.MM.YYYY')
and f.saledate<=to_date('19.01.2009','DD.MM.YYYY')

Oracle в любом случае сделает фулскан, ты же всю таблицу отбираешь, нафига ему индексный доступ

mighty · █ 28.01.2009 12:16

Цитата:

isi ➤ при твоем условии

Код:

 f.saledate>=to_date('20.01.2005','DD.MM.YYYY')
and f.saledate<=to_date('19.01.2009','DD.MM.YYYY')

Oracle в любом случае сделает фулскан, ты же всю таблицу отбираешь, нафига ему индексный доступ

да нет даже если дату изменить с '20.01.2005' на '20.01.2008' все равно фулскан..

Так что сделано и что получилось:
Перепартиционировал таблицу FFMAPREP было по RANGE(saledate) по месяцам, сделал как у Олега по LIST и RANGE http://www.olegon.ru/showthread.php?...ight=PARTITION
только подразбил еще 2007 год по кварталам, индексы от FFMAPREP выкинул в отдельный таблспейс. Индексы создавал обычные, имхо BITMAP
только для поля INCOMECLIENTINDEX подходит, в остальных используется saledate или saleid -битмап по ним будет очень долго создаваться, насчет выигрыша не знаю, если кто знает скажите.

Код:

--переименовываем таблицу FFMAPREP
ALTER TABLE SUPERMAG.FFMAPREP RENAME TO FFMAPREP_OLD;

--создаем таблспейс для FFMAPREP
CREATE TABLESPACE SM_ANAL_FFMAPREP
    DATAFILE 'D:\ORACLE\ORADATA\VLIGCO\FFMAPREP01.DBF' SIZE 2G AUTOEXTEND OFF,
             'D:\ORACLE\ORADATA\VLIGCO\FFMAPREP02.DBF' SIZE 2G AUTOEXTEND OFF,
             'D:\ORACLE\ORADATA\VLIGCO\FFMAPREP03.DBF' SIZE 2G AUTOEXTEND OFF,
             'D:\ORACLE\ORADATA\VLIGCO\FFMAPREP04.DBF' SIZE 2G AUTOEXTEND OFF
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    NOLOGGING
    ONLINE
    SEGMENT SPACE MANAGEMENT MANUAL;

--создаем таблспейс для индексов FFMAPREP
CREATE TABLESPACE FF_INDEX_MAPREP
    DATAFILE 'F:\ORACLE\ORADATA\VLIGCO\FFINDX01.DBF' SIZE 2G AUTOEXTEND OFF,
             'F:\ORACLE\ORADATA\VLIGCO\FFINDX02.DBF' SIZE 2G AUTOEXTEND OFF,
             'F:\ORACLE\ORADATA\VLIGCO\FFINDX03.DBF' SIZE 2G AUTOEXTEND OFF,
             'F:\ORACLE\ORADATA\VLIGCO\FFINDX04.DBF' SIZE 2G AUTOEXTEND OFF             
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    NOLOGGING
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO;

--даем права на таблспейсы пользователю SUPERMAG
ALTER USER SUPERMAG QUOTA UNLIMITED ON FF_INDEX_MAPREP;
ALTER USER SUPERMAG QUOTA UNLIMITED ON SM_ANAL_FFMAPREP;

--создаем партиционированную таблицу FFMAPREP
create table SUPERMAG.FFMAPREP
  PCTFREE     0
  INITRANS    30
  MAXTRANS    255
  TABLESPACE  SM_ANAL_FFMAPREP
  NOLOGGING
  PARTITION BY RANGE (saledate)
  SUBPARTITION BY LIST (saletype)
  (
  PARTITION ff_2006 VALUES LESS THAN (TO_DATE('01012007','DDMMYYYY'))
         (SUBPARTITION ff_2006_wi VALUES ('WI'),
          SUBPARTITION ff_2006_wo VALUES ('WO'),
          SUBPARTITION ff_2006_cs VALUES ('CS'),
          SUBPARTITION ff_2006_cr VALUES ('CR')
         ),
  PARTITION ff1_2007 VALUES LESS THAN (TO_DATE('01042007','DDMMYYYY'))
         (SUBPARTITION ff1_2007_wi VALUES ('WI'),
          SUBPARTITION ff1_2007_wo VALUES ('WO'),
          SUBPARTITION ff1_2007_cs VALUES ('CS'),
          SUBPARTITION ff1_2007_cr VALUES ('CR')
         ),
  PARTITION ff2_2007 VALUES LESS THAN (TO_DATE('01072007','DDMMYYYY'))
         (SUBPARTITION ff2_2007_wi VALUES ('WI'),
          SUBPARTITION ff2_2007_wo VALUES ('WO'),
          SUBPARTITION ff2_2007_cs VALUES ('CS'),
          SUBPARTITION ff2_2007_cr VALUES ('CR')
         ),
  PARTITION ff3_2007 VALUES LESS THAN (TO_DATE('01102007','DDMMYYYY'))
         (SUBPARTITION ff3_2007_wi VALUES ('WI'),
          SUBPARTITION ff3_2007_wo VALUES ('WO'),
          SUBPARTITION ff3_2007_cs VALUES ('CS'),
          SUBPARTITION ff3_2007_cr VALUES ('CR')
         ),
  PARTITION ff4_2007 VALUES LESS THAN (TO_DATE('01012008','DDMMYYYY'))
         (SUBPARTITION ff4_2007_wi VALUES ('WI'),
          SUBPARTITION ff4_2007_wo VALUES ('WO'),
          SUBPARTITION ff4_2007_cs VALUES ('CS'),
          SUBPARTITION ff4_2007_cr VALUES ('CR')
         ),
  PARTITION ff1_2008 VALUES LESS THAN (TO_DATE('01042008','DDMMYYYY'))
         (SUBPARTITION ff1_2008_wi VALUES ('WI'),
          SUBPARTITION ff1_2008_wo VALUES ('WO'),
          SUBPARTITION ff1_2008_cs VALUES ('CS'),
          SUBPARTITION ff1_2008_cr VALUES ('CR')
         ),
  PARTITION ff2_2008 VALUES LESS THAN (TO_DATE('01072008','DDMMYYYY'))
         (SUBPARTITION ff2_2008_wi VALUES ('WI'),
          SUBPARTITION ff2_2008_wo VALUES ('WO'),
          SUBPARTITION ff2_2008_cs VALUES ('CS'),
          SUBPARTITION ff2_2008_cr VALUES ('CR')
         ),
  PARTITION ff3_2008 VALUES LESS THAN (TO_DATE('01102008','DDMMYYYY'))
         (SUBPARTITION ff3_2008_wi VALUES ('WI'),
          SUBPARTITION ff3_2008_wo VALUES ('WO'),
          SUBPARTITION ff3_2008_cs VALUES ('CS'),
          SUBPARTITION ff3_2008_cr VALUES ('CR')
         ),
  PARTITION ff4_2008 VALUES LESS THAN (TO_DATE('01012009','DDMMYYYY'))
         (SUBPARTITION ff4_2008_wi VALUES ('WI'),
          SUBPARTITION ff4_2008_wo VALUES ('WO'),
          SUBPARTITION ff4_2008_cs VALUES ('CS'),
          SUBPARTITION ff4_2008_cr VALUES ('CR')
         ),

  PARTITION ff1_2009 VALUES LESS THAN (TO_DATE('01042009','DDMMYYYY'))
         (SUBPARTITION ff1_2009_wi VALUES ('WI'),
          SUBPARTITION ff1_2009_wo VALUES ('WO'),
          SUBPARTITION ff1_2009_cs VALUES ('CS'),
          SUBPARTITION ff1_2009_cr VALUES ('CR')
         ),
  PARTITION ff2_2009 VALUES LESS THAN (TO_DATE('01072009','DDMMYYYY'))
         (SUBPARTITION ff2_2009_wi VALUES ('WI'),
          SUBPARTITION ff2_2009_wo VALUES ('WO'),
          SUBPARTITION ff2_2009_cs VALUES ('CS'),
          SUBPARTITION ff2_2009_cr VALUES ('CR')
         ),
  PARTITION ff3_2009 VALUES LESS THAN (TO_DATE('01102009','DDMMYYYY'))
         (SUBPARTITION ff3_2009_wi VALUES ('WI'),
          SUBPARTITION ff3_2009_wo VALUES ('WO'),
          SUBPARTITION ff3_2009_cs VALUES ('CS'),
          SUBPARTITION ff3_2009_cr VALUES ('CR')
         ),
  PARTITION ff4_2009 VALUES LESS THAN (TO_DATE('01012010','DDMMYYYY'))
         (SUBPARTITION ff4_2009_wi VALUES ('WI'),
          SUBPARTITION ff4_2009_wo VALUES ('WO'),
          SUBPARTITION ff4_2009_cs VALUES ('CS'),
          SUBPARTITION ff4_2009_cr VALUES ('CR')
         )
)
AS SELECT * FROM SUPERMAG.FFMAPREP_OLD;

--даем права должности SUPERMAG_FN_REPADMIN_CALC
grant select, insert, update on SUPERMAG.FFMAPREP to SUPERMAG_FN_REPADMIN_CALC;

--удаляем индексы старого FFMAPREP_OLD
DROP INDEX SUPERMAG.FFMAPREP_ARTICLE;
DROP INDEX SUPERMAG.FFMAPREP_DOC;
DROP INDEX SUPERMAG.FFMAPREP_LOCFROM;
DROP INDEX SUPERMAG.FFMAPREP_LOCTO;
DROP INDEX SUPERMAG.FFMAPREP_SALEDATE;
DROP INDEX SUPERMAG.FFMAPREP_SUPPLIER;

--создаем новые индексы
create index SUPERMAG.FFMAPREP_ARTICLE on SUPERMAG.FFMAPREP (ARTICLE ASC)
  tablespace FF_INDEX_MAPREP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
  NOLOGGING
  ;
create  index SUPERMAG.FFMAPREP_DOC on SUPERMAG.FFMAPREP (SALEID ASC, SALETYPE ASC, SALESPECITEM ASC)
  tablespace FF_INDEX_MAPREP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
  NOLOGGING LOCAL
  ;
create  index SUPERMAG.FFMAPREP_LOCFROM on SUPERMAG.FFMAPREP (SALELOCATIONFROM ASC, SALEDATE ASC)
  tablespace FF_INDEX_MAPREP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
  NOLOGGING LOCAL
  ;
create  index SUPERMAG.FFMAPREP_LOCTO on SUPERMAG.FFMAPREP (SALELOCATIONTO ASC, SALEDATE ASC)
  tablespace FF_INDEX_MAPREP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
  NOLOGGING LOCAL
  ;
create  index SUPERMAG.FFMAPREP_SALEDATE on SUPERMAG.FFMAPREP (SALEDATE ASC)
  tablespace FF_INDEX_MAPREP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
  NOLOGGING LOCAL
  ;
create  BITMAP index SUPERMAG.FFMAPREP_SUPPLIER on SUPERMAG.FFMAPREP (INCOMECLIENTINDEX ASC)
  tablespace FF_INDEX_MAPREP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

После этого откомпиллировал инвалидов и собрал статистику по всей схеме SUPERMAG.

Для

Код:

select sum(decode(f.saletype,'CR',-f.salesum,f.salesum)) realiz 
from supermag.ffmaprep f 
where f.rectype=1 and f.saletype in ('CR','CS') 
and f.saledate>=to_date('20.01.2005','DD.MM.YYYY')
and f.saledate<=to_date('19.01.2009','DD.MM.YYYY')
and f.article in (select c.article from supermag.smcard c where  c.accepted<>-1)

Время выполнения: 192 секунды (уже не 631, это радует)
План

Код:

-------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |    34 |   135K  (3)| 00:27:10 |       |       |
|   1 |  SORT AGGREGATE            |          |     1 |    34 |            |          |       |       |
|*  2 |   HASH JOIN                |          |    26M|   844M|   135K  (3)| 00:27:10 |       |       |
|*  3 |    TABLE ACCESS FULL       | SMCARD   | 52438 |   409K|   242   (2)| 00:00:03 |       |       |
|   4 |    PARTITION RANGE ITERATOR|          |    26M|   646M|   135K  (3)| 00:27:05 |     1 |    10 |
|   5 |     PARTITION LIST INLIST  |          |    26M|   646M|   135K  (3)| 00:27:05 |KEY(I) |KEY(I) |
|*  6 |      TABLE ACCESS FULL     | FFMAPREP |    26M|   646M|   135K  (3)| 00:27:05 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

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

   2 - access("F"."ARTICLE"="C"."ARTICLE")
   3 - filter("C"."ACCEPTED"<>(-1))
   6 - filter("F"."RECTYPE"=1 AND "F"."SALEDATE"<=TO_DATE(' 2009-01-19 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "F"."SALEDATE">=TO_DATE(' 2005-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Ну с SMCARD все понятно операция <>(!=) отменяет действие индекса если поставить accepted in (0,1,2) план по карточкам переходит из фулскана в индекс(при этом время выполения разпроса уже 154 сек.), но вот план по FFMAPREP странный...фулскан..

меняем дату

Код:

select sum(decode(f.saletype,'CR',-f.salesum,f.salesum)) realiz 
from supermag.ffmaprep f 
where f.rectype=1 and 
f.saletype in ('CR','CS') 
and f.saledate>=to_date('01.12.2008','DD.MM.YYYY')
and f.saledate<=to_date('19.01.2009','DD.MM.YYYY')
and f.article in (select c.article from supermag.smcard c where  c.accepted <>-1)

План

Код:

-------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |    34 | 23131   (2)| 00:04:38 |       |       |
|   1 |  SORT AGGREGATE            |          |     1 |    34 |            |          |       |       |
|*  2 |   HASH JOIN                |          |  1964K|    63M| 23131   (2)| 00:04:38 |       |       |
|*  3 |    TABLE ACCESS FULL       | SMCARD   | 52438 |   409K|   242   (2)| 00:00:03 |       |       |
|   4 |    PARTITION RANGE ITERATOR|          |  1964K|    48M| 22874   (2)| 00:04:35 |     9 |    10 |
|   5 |     PARTITION LIST INLIST  |          |  1964K|    48M| 22874   (2)| 00:04:35 |KEY(I) |KEY(I) |
|*  6 |      TABLE ACCESS FULL     | FFMAPREP |  1964K|    48M| 22874   (2)| 00:04:35 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

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

   2 - access("F"."ARTICLE"="C"."ARTICLE")
   3 - filter("C"."ACCEPTED"<>(-1))
   6 - filter("F"."SALEDATE">=TO_DATE(' 2008-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "F"."RECTYPE"=1 AND "F"."SALEDATE"<=TO_DATE(' 2009-01-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Только стоимость и расчетное время выборки поменялось, а операции те же и фулскан тот же..
Буду дальше копать..

kadr · █ 28.01.2009 14:17

у тебя ещё соединение HASH JOIN, а у Олега NESTED LOOP
И время уменьшилось из-за того что меньше данных фуллсканится, идёт ещё отсечение субпартиций по типам документов

deucel · █ 30.01.2009 20:00

Цитата:

mighty ➤ Версия СМ+ 1.026.3
Полный перенос ускорил с 15 часов до 1 ч 22 мин путем сверки таблиц аналитической базы с базой эталоном сгенерированной на тестовом компе.
Оказалось что СМ+ в процессе переноса отключает только свои ограничения и чеки, про которые знает, у меня видимо были многие поля NOT NULL или от старой базы наследие или кривизна в генераторе, не знаю.

Думаю они не 'NOT NULL' были, а 'NOVALIDATE' т.е. когда констрейнт 'not null' есть, а в свойствах таблицы поля 'null', но констрейнт все равно отрабатывает.

:))