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

Откуда: Москва
Сообщений: 18388
MGR
     195823  consistent gets
     183784  physical reads
       2825  rows processed
SQL> 
В табличке RLT_A_SALFLDG около 7 000 000 записей.
В табличке RLT_ACNT 2825 записей (удовлетворяющих ACNT_TYPE <> 5)
Выполняется около 7-8 мин.

Что-то тут не так...
План не самый бедовый, но 100 логических чтений на строку - ИМХО многовато будет.
Но 100 ФИЗИЧЕСКИХ чтений на строку - перебор по-любому :)
Не знаю что там у Вас за ноут и для чего, но производительность oracle сильно зависит от такой штуки, как buffer cache. А он перед началом теста либо пуст, либо имеет совершенно недостаточный размер.
Что касается оптимизации плана - я бы попробовал уйти с NL. Сначала в сторону MERGE (если индексы позволяют, требуется аккуратность), потом - в сторону HASH (все одно сплошные физические чтения - почему бы не обрабатывать RLT_A_SALFLDG за один FTS?)
24 май 06, 11:41    [2699670]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
MGR
Alexey Polovinkin

Скажите пожалуйста - как обстоит дело со сбором статистики у вас в базе и покажите пожалуйста индексы .... какой то у вас нехороший INDEX FULL SCAN
хотя может оптимизатору виднее....


Эх-ма... да кабы я умел! :)
индексов-то толпа как на LDG, так и на ACNT. Я могу посмотреть их в PL/SQL, но как скопировать описания сюда не знаю :(


Выделяете мышкой в девелопере и копируете (ctrl+c/ctrl+v)
скопируйте только те, в которых присутствуют поля из запроса
24 май 06, 11:41    [2699672]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
SYS_C007425	Unique	ACCNT_CODE,PERIOD,TRANS_DATETIME,JRNAL_NO,JRNAL_LINE
XIE1RLT_A_SALFLDG	Normal	JRNAL_NO,JRNAL_LINE
XIE2RLT_A_SALFLDG	Normal	ASSET_CODE,PERIOD,TRANS_DATETIME,JRNAL_NO,JRNAL_LINE
XIE3RLT_A_SALFLDG	Normal	TREFERENCE,JRNAL_NO,JRNAL_LINE
XIE4RLT_A_SALFLDG	Normal	JRNAL_TYPE
XIE5RLT_A_SALFLDG	Normal	ALLOCATION
XIE6RLT_A_SALFLDG	Normal	ALLOC_REF,JRNAL_TYPE
XIF98617RLT_A_SALFLDG	Normal	SAVED_SET_NUM
XIF98649RLT_A_SALFLDG	Normal	AUTHORISTN_SET_REF
XIF98650RLT_A_SALFLDG	Normal	PYMT_AUTHORISTN_SET_REF
24 май 06, 11:42    [2699677]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
Alexey Polovinkin
версия оракла?

9.2.0.6
24 май 06, 11:43    [2699680]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
guest___guest
Guest
MGR
Alexey Polovinkin

Скажите пожалуйста - как обстоит дело со сбором статистики у вас в базе и покажите пожалуйста индексы .... какой то у вас нехороший INDEX FULL SCAN
хотя может оптимизатору виднее....


Эх-ма... да кабы я умел! :)
индексов-то толпа как на LDG, так и на ACNT. Я могу посмотреть их в PL/SQL, но как скопировать описания сюда не знаю :(



что значит толпа?!
главное не скока их,а используются ли они...
посмотри если ли индексы на те столбцы,которые указаны в условии WHERE
не надо ничего копировать, можно выгрузить всю таблицу(синтаксис составляющих) с помощью Extract DDL...я правда SQL NAvigator'om пользуюсь
24 май 06, 11:43    [2699685]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
MGR
Alexey Polovinkin
версия оракла?

9.2.0.6


вторую табличку еще....
24 май 06, 11:44    [2699687]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
andrey_anonymous
Что-то тут не так...


Андрей, я бы попросил прокомментировать, а то я ни разу не понял :(
1. Как посмотреть размер buffer cache (я не админ) и как его изменить (могу потрясти DBA)
2. Что такое NL?
3. Как я понял MERGE, HASH это хинты? Достаточно ли будет их указать?
4. FTS - этож full table scan, так есть такой хинт?

К моему большому сожалению, изучить оптимизацию Оракла за короткие сроки я не смогу :(
24 май 06, 11:48    [2699718]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
Alexey Polovinkin
MGR
Alexey Polovinkin
версия оракла?

9.2.0.6


вторую табличку еще....

SYS_C007629	Unique	ACNT_CODE
XIE1RLT_ACNT	Normal	LOOKUP
XIE2RLT_ACNT	Normal	ACNT_TYPE
XIF88932RLT_ACNT	Normal	DAG_CODE
XIF91538RLT_ACNT	Normal	DFLT_CURR_CODE
XIF98332RLT_ACNT	Normal	CV4_DFLT_CURR_CODE
XIF98333RLT_ACNT	Normal	CV5_DFLT_CURR_CODE
24 май 06, 11:50    [2699735]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
Не так то, что у вас сплошные физические чтения из файла, а судя по тому что у вы выплняли уже запрос, то оракл должен был найти эти блоки данных в буферном кеше....
чего у вас не наблюдается - вывод у вас маленький db_cache_size
24 май 06, 11:52    [2699746]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
Сделайте план запроса с (+) и в студию :)
24 май 06, 11:55    [2699757]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
andrey_anonymous
MGR
     195823  consistent gets
     183784  physical reads
       2825  rows processed
SQL> 
В табличке RLT_A_SALFLDG около 7 000 000 записей.
В табличке RLT_ACNT 2825 записей (удовлетворяющих ACNT_TYPE <> 5)
Выполняется около 7-8 мин.

Что-то тут не так...
План не самый бедовый, но 100 логических чтений на строку - ИМХО многовато будет.
Но 100 ФИЗИЧЕСКИХ чтений на строку - перебор по-любому :)


Кстати, 183 тыщи чтений на 7 миллионов записей (из них отрезаются условием 3 млн) это разве много?
24 май 06, 11:56    [2699763]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
Alexey Polovinkin
Не так то, что у вас сплошные физические чтения из файла, а судя по тому что у вы выплняли уже запрос, то оракл должен был найти эти блоки данных в буферном кеше....
чего у вас не наблюдается - вывод у вас маленький db_cache_size


Посмотрел в v$parameter
Значение = 33554432
Это много или мало?
24 май 06, 12:05    [2699805]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
MGR
Alexey Polovinkin
Не так то, что у вас сплошные физические чтения из файла, а судя по тому что у вы выплняли уже запрос, то оракл должен был найти эти блоки данных в буферном кеше....
чего у вас не наблюдается - вывод у вас маленький db_cache_size


Посмотрел в v$parameter
Значение = 33554432
Это много или мало?


Это почти ничего :)
24 май 06, 12:07    [2699821]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
Alexey Polovinkin
Сделайте план запроса с (+) и в студию :)


Я только не уверен - правильно ли я у Вас подсмотрел расстановку этих вот плюсиков. Судя по количеству строк - не правильно :(

SQL>  set autot traceonly
SQL> SELECT 
  2  SUM(CASE WHEN PERIOD < 2005001 OR LDG.JRNAL_TYPE = 'XXX' THEN LDG.AMOUNT ELSE 0 END) BEGBAL,
  3  SUM(CASE WHEN D_C = 'D' AND PERIOD >= 2005001 AND LDG.JRNAL_TYPE <> 'XXX' THEN LDG.AMOUNT ELSE 
0 END) DR_AMOUNT,
  4  SUM(CASE WHEN D_C = 'C' AND PERIOD >= 2005001 AND LDG.JRNAL_TYPE <> 'XXX' THEN LDG.AMOUNT ELSE 
0 END) CR_AMOUNT,
  5  ACNT.ACNT_CODE, MAX(ACNT.DESCR) ACNT_DESCR
  6  FROM RLT_ACNT ACNT,
  7        RLT_A_SALFLDG LDG
  8  WHERE LDG.ACCNT_CODE = ACNT.ACNT_CODE(+)
  9     AND LDG.PERIOD <= 2005012
 10     AND ACNT.ACNT_TYPE(+) <> 5
 11   GROUP BY ACNT.ACNT_CODE;

2205 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'RLT_A_SALFLDG'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'RLT_ACNT'
   5    4         INDEX (UNIQUE SCAN) OF 'SYS_C007629' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   10420157  consistent gets
     439749  physical reads
          0  redo size
     187301  bytes sent via SQL*Net to client
       1292  bytes received via SQL*Net from client
        148  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2205  rows processed

SQL> 
24 май 06, 12:09    [2699829]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
при 2 Гигах памяти дать ораклу 33 Метра под буфера....
24 май 06, 12:10    [2699836]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
MGR
andrey_anonymous
MGR
     195823  consistent gets
     183784  physical reads
       2825  rows processed
SQL> 
В табличке RLT_A_SALFLDG около 7 000 000 записей.
В табличке RLT_ACNT 2825 записей (удовлетворяющих ACNT_TYPE <> 5)
Выполняется около 7-8 мин.

Что-то тут не так...
План не самый бедовый, но 100 логических чтений на строку - ИМХО многовато будет.
Но 100 ФИЗИЧЕСКИХ чтений на строку - перебор по-любому :)


Кстати, 183 тыщи чтений на 7 миллионов записей (из них отрезаются условием 3 млн) это разве много?


Физических чтений? Много
Товарищ автор, предлагаю вынести на обсуждение в студию настройки init.ora. Вообще-то, сложно отвечать на такие вопросы, где вот прям явных глюков в запросе нет. Вариантов-то масса...

1. слишком много экстентов в таблице (итог - таблица дефрагментирована)
2. подтягиваются "плохие" индексы, отношение селективности которых к затратам на их сканирование оставляет желать лучшего
3. как уже замечали - отсутствие или неактуальность статистики. Если у вас данные в этой таблице практически не меняются - выполните ANALYZE TABLE ... COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES. Если меняются - тогда надо собирать примерную статистику: ANALYZE TABLE ESTIMATE STATISTICS SAMPLE 40 (кажется, так)
4. В настройках oracle указан слишком маленький размер области сортировки sort_area_size. В итоге, когда ораклу нужно выполнить сортировку данных, и не хватает для этого места в памяти - будет жесткий своп. Со всеми вытекающими...
5. Маленькое значение db_block_buffers в init.ora
6. Маленькое значение буферного кеша в db_cash_size в init.ora
7. Неоптимальное значение db_file_multiblock_read_count (кол-во блоков, которое оракл может считать за один цикл IO)

Это, скажем, начало, с чего надо начинать разбираться...
24 май 06, 12:10    [2699838]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
Alexey Polovinkin


Это почти ничего :)


А сколько надо? :)
24 май 06, 12:10    [2699842]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18388
MGR
andrey_anonymous
Что-то тут не так...


Андрей, я бы попросил прокомментировать, а то я ни разу не понял :(
1. Как посмотреть размер buffer cache (я не админ) и как его изменить (могу потрясти DBA)
Можно select * from v$sgastat where name='buffer_cache', но лучше потрясти DBA :)
MGR

2. Что такое NL?
Метод соединения данных "вложенными циклами". Nested Loops, для друзей - просто NL.
MGR

3. Как я понял MERGE, HASH это хинты? Достаточно ли будет их указать?
Это еще два метода соединения.
MGR

4. FTS - этож full table scan, так есть такой хинт?
Да, и хинт есть (FULL(<table_alias>))
MGR
К моему большому сожалению, изучить оптимизацию Оракла за короткие сроки я не смогу :(

Вот в этом-то и проблема.
Для оптимизации этого запроса требуются определенные усилия. "Вслепую" тыкать хинты - не дело.
Лучше потратьте хотя бы день-два на изучение concepts и немножечко performance tuning guide... Не то чтобы Вы успели за два дня хотя бы их прочесть, но представление о системе получить можно - это упростит процесс коммуникации в форуме :)
24 май 06, 12:12    [2699858]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Alexey Polovinkin
при 2 Гигах памяти дать ораклу 33 Метра под буфера....


Вы сторонник "2 байта памяти, но 3 процессорных такта" вместо "наоборот"? :)
Это тоже не решение. Нет, я не говорю, что число 33 чем-то плохо, просто все эти цифры надо тщательно обдумывать при создании базы и при дальнейшем сопровождении. 33 метра под буфера - хорошо, больше поместится, но и процессорное время будет тратиться больше на сканирование большого буфера! Нужна золотая середина. А ее каждый ищет для себя сам... ну, в смысле, под свои задачи
24 май 06, 12:12    [2699860]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
Ваш запрос у меня....
(ничего не менял только (+) поставил)
SELECT SUM(CASE
             WHEN PERIOD < 2005001
                  OR LDG.JRNAL_TYPE = 'XXX' THEN
              LDG.AMOUNT
             ELSE
              0
           END) BEGBAL,
       SUM(CASE
             WHEN D_C = 'D'
                  AND PERIOD >= 2005001
                  AND LDG.JRNAL_TYPE <> 'XXX' THEN
              LDG.AMOUNT
             ELSE
             0
           END) DR_AMOUNT,
       SUM(CASE
             WHEN D_C = 'C'
                  AND PERIOD >= 2005001
                  AND LDG.JRNAL_TYPE <> 'XXX' THEN
              LDG.AMOUNT
             ELSE
             0
           END) CR_AMOUNT,
       ACNT.ACNT_CODE,
       MAX(ACNT.DESCR) ACNT_DESCR
  FROM RLT_ACNT ACNT,
       RLT_A_SALFLDG LDG
 WHERE LDG.ACCNT_CODE = ACNT.ACNT_CODE(+)
   AND LDG.PERIOD <= 2005012
   AND ACNT.ACNT_TYPE(+) <> 5
 GROUP BY ACNT.ACNT_CODE

SELECT STATEMENT, GOAL = CHOOSE					
 SORT GROUP BY					
  NESTED LOOPS OUTER					
   TABLE ACCESS BY INDEX ROWID		Object name=RLT_A_SALFLDG		
    INDEX RANGE SCAN	Object name=idx_AAA			
   TABLE ACCESS BY INDEX ROWID	Object name=RLT_ACNT			
    INDEX UNIQUE SCAN		Object name=SYS_C0010564	


Данных нет - так что в остальном... :)
24 май 06, 12:16    [2699881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18388
Вадиман
33 метра под буфера - хорошо, больше поместится, но и процессорное время будет тратиться больше на сканирование большого буфера!

Уважаемый Вадиман, не ставьте пожалуйста автора перед необходимостью разгадывать столь странные ребусы. Ему, похоже, и так есть чем заняться.
24 май 06, 12:18    [2699893]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
guest___guest
Guest
andrey_anonymous
Вадиман
33 метра под буфера - хорошо, больше поместится, но и процессорное время будет тратиться больше на сканирование большого буфера!

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


Господа,Господа!

Брейк!!!
24 май 06, 12:19    [2699901]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
Вадиман
Alexey Polovinkin
при 2 Гигах памяти дать ораклу 33 Метра под буфера....


Вы сторонник "2 байта памяти, но 3 процессорных такта" вместо "наоборот"? :)
Это тоже не решение. Нет, я не говорю, что число 33 чем-то плохо, просто все эти цифры надо тщательно обдумывать при создании базы и при дальнейшем сопровождении. 33 метра под буфера - хорошо, больше поместится, но и процессорное время будет тратиться больше на сканирование большого буфера! Нужна золотая середина. А ее каждый ищет для себя сам... ну, в смысле, под свои задачи


7 000 000 строк в таблице из которых надо 3 000 000 поместить в кэш....

поанализируйте статистику сервера и вы поймете что 33 мега - это даже слишком мало для выделеного сервера (а он ведь выделеный правда?)
24 май 06, 12:20    [2699908]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
andrey_anonymous
Вадиман
33 метра под буфера - хорошо, больше поместится, но и процессорное время будет тратиться больше на сканирование большого буфера!

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


присоединяюсь
24 май 06, 12:21    [2699914]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожайлуста оптимизировать запрос  [new]
MGR
Member

Откуда:
Сообщений: 536
Вадиман

1. слишком много экстентов в таблице (итог - таблица дефрагментирована)


Что с этим можно сделать?


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


А где это можно посмотреть?

3. как уже замечали - отсутствие или неактуальность статистики. Если у вас данные в этой таблице практически не меняются - выполните ANALYZE TABLE ... COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES. Если меняются - тогда надо собирать примерную статистику: ANALYZE TABLE ESTIMATE STATISTICS SAMPLE 40 (кажется, так)


А за это спасибо!
Сделал ANALYZE TABLE RLT_A_SALFLDG ESTIMATE STATISTICS и получил 70с вместо 350!


4. В настройках oracle указан слишком маленький размер области сортировки sort_area_size. В итоге, когда ораклу нужно выполнить сортировку данных, и не хватает для этого места в памяти - будет жесткий своп. Со всеми вытекающими...

524288
24 май 06, 12:33    [2699992]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
Все форумы / Oracle Ответить