Full scan при наличии индексов : Oracle

█ 04.05.2010 12:24

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

Запрос выполняется от 3х минут без нагрузки и более
(ожидания sequental read)

Код:

select 
  sum(decode( d.doctype, 'CS', 1,-1)*s.totalprice), count( distinct d.rowid), count(s.rowid)
from 
   supermag.smdocuments d, supermag.smspec s
where d.doctype=s.doctype
  and d.id=s.docid
  and d.doctype in ('CS' ,'CR')
  and d.createdat between '01.12.2007' and '30.12.2007'

Код:

Execution Plan

 0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1486 Card=1 Bytes=66)
 1    0   SORT (GROUP BY)
 2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'SMSPEC' (TABLE) (Cost=2 Card=15 Bytes=480)
 3    2       NESTED LOOPS (Cost=1486 Card=14208 Bytes=937728)
 4    3         PARTITION LIST (INLIST) (Cost=28 Card=971 Bytes=33014)
 5    4           TABLE ACCESS (FULL) OF 'SMDOCUMENTS' (TABLE) (Cost=28 Card=971 Bytes=33014)
 6    3         INDEX (RANGE SCAN) OF 'SMCSPEC_DISPLAYPOS' (INDEX) (Cost=1 Card=35)

Почему TABLE ACCESS (FULL) OF 'SMDOCUMENTS ?

Железо: 1 проц Core 2, 2 гига оперативки, 3 SATA винчестера, по ним все раскидано.

Параметры:

Код:

*.audit_file_dest='d:\oracle\product\10.2.0\admin\\adump'
*.background_dump_dest='d:\oracle\product\10.2.0\admin\\bdump'
*.compatible='10.2.0.4.0'
*.control_files='d:\oracle\oradata\\control01.ctl','d:\oracle\oradata\\control02.ctl','d:\oracle\oradata\\control03.ctl'
*.core_dump_dest='d:\oracle\product\10.2.0\admin\\cdump'
*.db_block_size=8192
*.db_cache_size=37748736
*.db_file_multiblock_read_count=32
*.db_keep_cache_size=104857600
*.db_recovery_file_dest='d:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XDB)'
*.job_queue_processes=10
*.open_cursors=300
*.optimizer_index_caching=50
*.optimizer_index_cost_adj=50
*.pga_aggregate_target=549453824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=786432000
*.sga_target=734003200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\product\10.2.0\admin\\udump'

Код:

SMDOCUMENTS
PARTITION BY LIST(DOCTYPE)	
CREATE INDEX SUPERMAG.SMDOCUMENTS_CLIENT
    ON SUPERMAG.SMDOCUMENTS(CLIENTINDEX)
	CREATE INDEX SUPERMAG.SMDOCUMENTS_CREATEDAT
    ON SUPERMAG.SMDOCUMENTS(CREATEDAT)
	CREATE INDEX SUPERMAG.SMDOCUMENTS_LOC
    ON SUPERMAG.SMDOCUMENTS(LOCATION)
	CREATE INDEX SUPERMAG.SMDOCUMENTS_LOCFROM
    ON SUPERMAG.SMDOCUMENTS(LOCATIONFROM)
	CREATE INDEX SUPERMAG.SMDOCUMENTS_LOCTO
    ON SUPERMAG.SMDOCUMENTS(LOCATIONTO)
	SMCDOCUMENTS_PK Primary (DOCTYPE, ID)
	
SMSPEC
PARTITION BY LIST(DOCTYPE)	
CREATE Unique INDEX SUPERMAG.SMCSPEC_DISPLAYPOS
    ON SUPERMAG.SMSPEC(DOCTYPE,DOCID,DISPLAYITEM)
	CREATE INDEX SUPERMAG.SMSPEC_ART
    ON SUPERMAG.SMSPEC(ARTICLE,DOCTYPE,DOCID)
	CREATE INDEX SUPERMAG.SMSPEC_CAUSEIDX
    ON SUPERMAG.SMSPEC(CAUSETYPE,CAUSEID,CAUSESPECITEM)
	SMCSPEC_PK Primary (DOCTYPE, DOCID, SPECITEM)

█ 04.05.2010 13:00

Цитата:

*.db_file_multiblock_read_count=32
*.optimizer_index_caching=50
*.optimizer_index_cost_adj=50

Уверен? Первый поставь в 8, два вторых лучше бы выкинуть. И запусти оптимизатор на пару дней, посмотри, что у него в логе будет, особенно в момент тормозов.

█ 04.05.2010 13:37

Т.е. ты предлагаешь
*.db_file_multiblock_read_count=8
*.optimizer_index_caching=0
*.optimizer_index_cost_adj=100
?
Так будет однозначно ACCESS FULL в большинстве запросов.

Тут надо бы уточнить, что все отчеты у нас самописные. И дело не в тормозах, разобраться хочу. Есть на форуме тема про партиционирование больших таблиц, на основании этой темы таблицы партиционированы. Там приводится вышеуказанный запрос и его план. Так там идет выборка по индексам, а у меня в большинстве случаев по партиционированным таблицам идет ACCESS FULL

Вот та тема, только она закрыта.
https://olegon.ru/showthread.php?t=2...rtition&page=3

(Oracle 10.2.0.4 64bit на Винде)

█ 04.05.2010 13:58

Я предлагаю второй и третий вообще убрать, чтобы по дефолту было, да, по дефолту будет так, как написано и это в большинстве случаев правильно. Если full scan, то скорее всего ошибка в запросе, а не в том, что программеры в Oracle, придумавшие оптимизатор, козлы. Запустить местный optimizer4 я тебе уже рекомендовал, заметь. Он тебе db_file_multiblock_read_count правильный поставит. Cудя по описанию железа у тебя там ближе к 8, а не 32 ни разу.

█ 04.05.2010 14:51

Как определить оптимальное значение для db_file_multiblock_read_count, я в инете не нашел. По умолчанию 16.

А по поводу параметров CBO я положился на статью:
http://workstudio.narod.ru/books/search_intelligence_cbo.doc

█ 04.05.2010 17:13

Только если сделать
optimizer_index_cost_adj=10
оптимизатор при построении плана использует индексы.

Насколько критично optimizer_index_cost_adj=10 для аналитической базы?