Тормозит отчёт "Лидеры групп товаров" : Супермаг Плюс (Супермаг 2000)

█ 19.01.2010 17:19

Помогите решить проблему с медленным формированием отчёта «Лидеры групп товаров». Проблема начала наблюдаться несколько дней назад. Никаких изменений в базе перед этим не происходило.

Трассировка сессии показала, что основное время тратится на исполнение запроса:

UPDATE supermag.ttarticledateprofit t
SET forcmap = '1'
WHERE t.article IN (SELECT tt.article
FROM supermag.ttrealization3 tt
WHERE tt.ID BETWEEN :b1 AND :b2)

Его статистика такая:
Name Value
Logical reads 41 840 915,00
Elapsed time (ms) 2 827 818,10
CPU time (ms) 2 826 109,38
Total rows 1 294 817,00
Logical reads per execute 94 024,53
Executions 445,00
Buffer cache hit ratio 100,00
Logical reads per row 32,31
Physical reads 5,00
Library cache misses 1,00
Parse calls 1,00
parse to execute ratio 0,00
Fetch calls 0,00

С чем может быть связано такое значительное время CPU?
Какие меры предпринять для ускорения запроса?
Версия СУБД: 10.0.2.4
Версия СМ: 1.027.2

Штатные административные задания полный сбор статистики для 10g, оптимизация индексов исполняются регулярно без ошибок.

█ 19.01.2010 17:35

Рекомендую привести план запроса и запустить оптимайзер.

█ 19.01.2010 18:15

План запроса могу привести, но он ведь выполнялся над "пустыми" табличками TTREALIZATION3 и TTARTICLEDATEPROFIT, поэтому данные о времени и стоимости не говорящие.

Код:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 2591263487

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT               |                         |     1 |    92 |     0   (0)| 00:00:01 |
|   1 |  UPDATE                        | TTARTICLEDATEPROFIT     |       |       |            |          |
|*  2 |   FILTER                       |                         |       |       |            |          |
|   3 |    NESTED LOOPS SEMI           |                         |     1 |    92 |     0   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN            | TTCARTICLEDATEPROFIT_PK |     1 |    52 |     0   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| TTREALIZATION3          |     1 |    40 |     0   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
|*  6 |      INDEX RANGE SCAN          | TTCREALIZATION3_PK      |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
   5 - filter("T"."ARTICLE"="TT"."ARTICLE")
   6 - access("TT"."ID">=TO_NUMBER(:B1) AND "TT"."ID"<=TO_NUMBER(:B2))

Пытаюсь смотреть в spotlight'е SQL plan для данного запроса, а у меня вместо дерева написано "no data".
А вчера видел там, что INDEX RANGE SCAN по TTCREALIZATION3_PK занял практически всё время.

█ 20.01.2010 06:13

Есть возможность запустить оптимизатор?

█ 20.01.2010 10:50

Оптимизатором могу прогнать в лучшем случае на выходных.
Поэтому прошу помощи уже сегодня.
Сегодня ночью перезапустил экземпляр, чтобы прежняя накопленная статистика по запросу не вводила в заблуждение.
Вот что получил:
Общая статистика:

Код:

Name				Value
Parse calls			0
Executions			436
Fetch calls			0
Total rows			939396
Logical reads			25064918
Physical reads			0
Elapsed time (ms)		2879066,37
CPU time (ms)			2881406,25
Library cache misses		
Buffer cache hit ratio		100
Logical reads per execute	57488,34
Logical reads per row		26,68
parse to execute ratio		0

План запроса:

Код:

Operation							id	cost	Cardinality	execs	elapsed Time (ms)	Buffer gets	disk reads	Rows	
UPDATE STATEMENT  						0	1							
 "UPDATE  SUPERMAG.TTARTICLEDATEPROFIT"				1				436	2879001,622		25064857	0		0	
  "FILTER  "							2				436	2870215,655		24104369	0		939396	
   "NESTED LOOPS SEMI "						3	0	12		436	2869218,329		24104369	0		939396	
    "INDEX FULL SCAN SUPERMAG.TTCARTICLEDATEPROFIT_PK"		4	0	4743		436	4,223			21412		0		2067948	
    "TABLE ACCESS BY INDEX ROWID SUPERMAG.TTREALIZATION3"	5	0	1		436	2864902,662		24082957	0		939396 
     "INDEX RANGE SCAN SUPERMAG.TTCREALIZATION3_PK"		6	0	21		436	8813,891		7387395		0		2971583209

█ 20.01.2010 11:22

(c широко раскрытыми глазами и тыча рукой в хрустальный шар)
Вижуууу... Вижуууууу. Статистику собранную на временной таблице.
Если не попал - определитесь, проблема это и вы запускаете оптимизатор вечером, либо не горит - запускаете в выходные. Сейчас больше похоже на лечение по фотографии. Оптимизатор помимо исправления явных косяков, оставляет мне подробный отчет по состоянию базы, что позволит делать более конкретные предположения.

█ 20.01.2010 12:22

Есть объективные причины, по которым прикручивание оптимизатора к боевой базе сейчас невозможно. Вернее, возможно, но не той ценой.
По поводу статистики, собранной по временной таблице, согласен. Но на что ещё опираться, когда пытаешься понять узкое место в конкретном запросе? Моей квалификации явно недостаточно, чтобы самостоятельно определить это узкое место - потому и написал сюда на форум.
Если способ только один - оптимизатор, то ок, так и поступим, попытаюсь прикрутить его.
Если получится решить методом закручивания плана - отпишусь.