12.04.2012 10:14
Mr_Vito
 
Есть запрос:

Код:
 insert into vit_pp_tab 
 select  d.saledate, d.article, decode(d.saleop, 1,  d.salelocationfrom,  d.salelocationto) loc, NVL(d5.price_pp,0) price_pp, 
 NVL(d5.cliind_pp,0) cliind_pp, 
           decode(d.incomeclientindex,100150,100150, 
                                      100137,100137, 
                                      100182,100182, 
                                      100191,100191, 
                                      100250,100250, 
                                      100795,100795, 
                                      100885,100885, 
                                      100823,100823, 
                                      100772,100772, 
                                      100978,100978, 
                                      101032,101032, 
                                      100657,100657, 
                                      100648,100648, 
                                      100674,100674, 
                                      100784,100784, 
                                      100926,100926, 
                                      100019,100019, 
                                      100321,100321, 
                     decode(d4.idclass,2,0,decode(d3.idclass,1,0,decode(d1.opcode,0,d.incomeclientindex,0)))) 
              clientindex,  
              decode(d.saleop, 1,  sum(d.saleq),0) saleq, 
              decode(d.saleop, 3,  sum(d.saleq),0) w_in_q,  
              decode(d.saleop, 1, sum(d.salesum),0) salesum,  
              decode(d.saleop, 3, sum(d.salesum),0) w_in_sum,   
              decode(d.saleop, 1,  sum(decode(d.saleuserop,-2,d.primecost,0)),0)  primecost, 
              decode(d.saleop, 3,  sum(decode(d.saleuserop,-2,d.primecost,0)),0) w_in_primecost 
 from ffmaprep_ d, smdocuments d1, smclients d3, smsuppliers d4 
     , (select d.article, q2.locationto, max(d.itemprice) keep (dense_rank last order by q2.createdat) price_pp, 
                                   max(q2.clientindex) keep (dense_rank last order by q2.createdat) cliind_pp 
     from smspec d, smdocuments q2, smclients q3, smsuppliers q4 
     where q2.id=d.docid 
     and q2.doctype=d.doctype 
     and q2.clientindex = q3.id(+) 
     and q2.clientindex = q4.id(+) 
     and decode(q2.clientindex,100150,1,  
                                      100137,1, 
                                      100182,1, 
                                      100191,1, 
                                      100250,1, 
                                      100795,1, 
                                      100885,1, 
                                      100823,1, 
                                      100772,1, 
                                      100978,1, 
                                      101032,1, 
                                      100657,1, 
                                      100648,1, 
                                      100674,1, 
                                      100784,1, 
                                      100926,1, 
                                      100019,1, 
                                      100321,1, 
                decode(q4.idclass,2,0,decode(q3.idclass,1,0,1)))=1 
     and q2.opcode in(0,13) 
     and q2.docstate=3 
     group by d.article, q2.locationto) d5 
 where d.rectype=1 
  and d.saleop in (1,3) 
   and d.incomeclientindex = d3.id(+) 
   and d.incomeclientindex = d4.id(+) 
   and d.incomeid=d1.id(+) 
   and d.incometype=d1.doctype(+) 
   and d.article=d5.article(+) 
   and  decode(d.saleop, 1,  d.salelocationfrom,  d.salelocationto)=d5.locationto(+) 
 group by d.saledate, d.article, d.saleop, d.salelocationfrom, d.salelocationto, d5.price_pp, d5.cliind_pp, 
           decode(d.incomeclientindex,100150,100150, 
                                      100137,100137, 
                                      100182,100182, 
                                      100191,100191, 
                                      100250,100250, 
                                      100795,100795, 
                                      100885,100885, 
                                      100823,100823, 
                                      100772,100772, 
                                      100978,100978, 
                                      101032,101032, 
                                      100657,100657, 
                                      100648,100648, 
                                      100674,100674, 
                                      100784,100784, 
                                      100926,100926, 
                                      100019,100019, 
                                      100321,100321, 
            decode(d4.idclass,2,0,decode(d3.idclass,1,0,decode(d1.opcode,0,d.incomeclientindex,0)))) 
 order by sum(decode(d.saleuserop,-2,d.primecost,0))
всё работает, всё здорово за исключением одной проблемки
запрос сжирает 40 гигов UNDO
после выполнения count(*) по vit_pp_tab возвращает 46862944 записей
соответственно вопросы:
почему и как избежать?
12.04.2012 10:29
OlegON
 
1. у тебя есть четкое понимание разницы между undo и redo?
2. каким запросом ты прикрутил 40Гб именно к этой транзакции?
3. почему ты объем меряешь в строках, а не Мб? и сколько там Мб?
4. какие параметры у этой таблички (DDL)?

я тут забеган сильно, но это направление куда копать
12.04.2012 11:36
Mr_Vito
 
1) но я знаю, что именно табличное пространство undo занимает 90 гигов
два выполненных запроса приведённых выше, забивают его на 80 гигов. запросы делаются ночью, а в течении дня это 80 гигов не вычищаются :(

Код:
SELECT STATUS,TABLESPACE_NAME, SUM(BYTES)/1024/1024, COUNT(*) 
FROM DBA_UNDO_EXTENTS 
GROUP BY STATUS, TABLESPACE_NAME

STATUS    TABLESPACE_NAME                SUM(BYTES)/1024/1024                   COUNT(*)                               
--------- ------------------------------ -------------------------------------- -------------------------------------- 
EXPIRED   UNDOTBS1                                                         0,25                                      4
UNEXPIRED UNDOTBS1                                                     94234,25                                   3487
ACTIVE    UNDOTBS1                                                           65                                      2
2) во время выполнения этого запроса, когда пользователей не было: сделал запрос:

Select * from v$sqltext where sql_id in
(select maxqueryid from
(select maxqueryid from v$undostat where BEGIN_TIME >=to_date('12.04.2012','dd.mm.yyyy') and END_TIME <=to_date('09.09.2012','dd.mm.yyyy'))
) order by sql_id,piece;

он и показал

3)

Код:
SELECT   owner, segment_name, segment_type,tablespace_name, 
         ROUND(SUM (BYTES) /  1024 / 1024) "SIZE (MB)",
         SUM (blocks) blocks, COUNT (*) extents
    FROM dba_extents
   WHERE owner = 'SUPERMAG'  AND segment_name = 'VIT_PP_TAB' 
     and segment_type = 'TABLE' and tablespace_name='VITO_TAB'
GROUP BY owner, segment_name, segment_type, tablespace_name

OWNER                          SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME                SIZE (MB)                              BLOCKS                                 EXTENTS                                
------------------------------ --------------------------------------------------------------------------------- ------------------ ------------------------------ -------------------------------------- -------------------------------------- -------------------------------------- 
SUPERMAG                       VIT_PP_TAB                                                                        TABLE              VITO_TAB                                                         2752                                 352256                                    155
4)

Код:
-- Start of DDL Script for Table SUPERMAG.VIT_PP_TAB
-- Generated 12-àïð-2012 13:03:16 from SUPERMAG@ILISHCO

CREATE TABLE vit_pp_tab
    (saledate                       DATE NOT NULL,
    article                        VARCHAR2(50 BYTE) NOT NULL,
    loc                            NUMBER(10,0) NOT NULL,
    price_pp                       NUMBER(19,4) NOT NULL,
    cliind_pp                      NUMBER(10,0) NOT NULL,
    clientindex                    NUMBER(10,0) NOT NULL,
    saleq                          NUMBER(19,4),
    w_in_q                         NUMBER(19,4),
    salesum                        NUMBER(19,4),
    w_in_sum                       NUMBER(19,4),
    primecost                      NUMBER(19,4),
    w_in_primecost                 NUMBER(19,4))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  vito_tab
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
  NOPARALLEL
  LOGGING
/





-- Indexes for VIT_PP_TAB

CREATE INDEX vit_pp_tab_datlocart_idx ON vit_pp_tab
  (
    saledate                        ASC,
    loc                             ASC,
    article                         ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  vito_idx
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
NOLOGGING
/

CREATE INDEX vit_pp_tab_cliind_pp_idx ON vit_pp_tab
  (
    cliind_pp                       ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  vito_idx
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
NOLOGGING
/

CREATE INDEX vit_pp_tab_saledate_idx ON vit_pp_tab
  (
    saledate                        ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  vito_idx
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
NOLOGGING
/

CREATE INDEX vit_pp_tab_artloc_idx ON vit_pp_tab
  (
    article                         ASC,
    loc                             ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  vito_idx
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
NOLOGGING
/

CREATE INDEX vit_pp_tab_clientindex_idx ON vit_pp_tab
  (
    clientindex                     ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  vito_idx
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
NOLOGGING
/

CREATE INDEX vit_pp_tab_artdat_idx ON vit_pp_tab
  (
    article                         ASC,
    saledate                        ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  vito_idx
  STORAGE   (
    INITIAL     1073741824
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
NOLOGGING
/



-- End of DDL Script for Table SUPERMAG.VIT_PP_TAB
12.04.2012 12:24
vdm
 
Ну пишется боольшой объем в одной непрерывной транзакции, ну хранится все это в undo для отката.
Почему потом не освобождается - не моя квалификация.
Где то в стороне UNDO_RETENTION можно поискать.

Разбить insert на части, курсор+commit в цикле через каждые n строк - не пойдет ?

LOGGING на табличке тоже можно отключить, если она с нуля заполняется и потеря изменений не критична.
12.04.2012 13:02
OlegON
 
Мда, превращается в лечение по фотографии...
Зачем такая куча индексов? Какой UNDO_RETENTION?
предлагаю
1. индексы на время вставки убивать.
2. nologging
3. insert /*+ append */ into vit_pp_tab ...
12.04.2012 15:41
Mr_Vito
 
1,2,3) попробую

Код:
SQL> show parameter %undo%;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     28800
undo_tablespace                      string      UNDOTBS1
12.04.2012 15:45
akonev
 
сильно. 8 часов получается. вот оно и держит старательно, раз попросили.
12.04.2012 17:09
OlegON
 
вот именно... кстати, еще раз, раз используешь оптимизатор, там есть секция по транзакциям в логе, если у тебя он пускается раз в полчаса, то данные о росте должны быть в логе, история.
13.04.2012 09:26
Mr_Vito
 
в итоге undo не очистился, даже по прошествии 18 часов после окончания выполнения запроса :(
правда очистился по прошествии 24 часов
может уменьшить undo_retention? если уменьшить до скольки? и почему, даже после того как содержимое перешло в EXPIRED, UNDO не вычищается так долго?
13.04.2012 09:52
OlegON
 
Выше же писали про undo_retention, сколько у тебя длится этот insert? append не помог? выставь в 3600 попробуй?
Часовой пояс GMT +3, время: 09:22.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.