Даешь индекс в запросе отчета : Oracle

OlegON · █ 14.11.2007 13:53

В отчете "Товарный отчет и налоги" крутится фулскан, сволочь такая! При подсказке нормально трескает индекс. index_cost_adj=1, какие еще варианты, кроме outlines, чтобы заставить его по индексу пройти?

Цитата:

SQL Statement from editor:

INSERT INTO supermag.tttovreport
(doctype, opcode, useropcode, paycash, taxgroupid, taxid, taxsum)
SELECT m.saletype, m.saleop, NVL (m.saleuserop, -:"SYS_B_00"),
m.salepaycash, a.taxgroupid, t.taxid,
SUM (DECODE (m.saleq,
:"SYS_B_01", :"SYS_B_02",
t.taxsum * m.quantity / m.saleq
)
)
FROM supermag.fvmaprep m, supermag.smcardtax a, supermag.smspectax t
WHERE TO_DATE (:"SYS_B_03", :"SYS_B_04") BETWEEN a.datefrom AND a.dateto
AND a.article = m.article
AND NVL (m.salelocationfrom, m.salelocationto) = :"SYS_B_05"
AND m.saledate BETWEEN TO_DATE (:"SYS_B_06", :"SYS_B_07")
AND TO_DATE (:"SYS_B_08", :"SYS_B_09")
AND m.saletype IN (:"SYS_B_10", :"SYS_B_11")
AND m.saleop IN (:"SYS_B_12", :"SYS_B_13")
AND t.doctype = m.saletype
AND t.docid = m.saleid
AND t.specitem = m.salespecitem
GROUP BY m.saletype,
m.saleop,
NVL (m.saleuserop, -:"SYS_B_00"),
m.salepaycash,
a.taxgroupid,
t.taxid

------------------------------------------------------------

Statement Id=4203172 Type=
Cost=2,64013623522001E-308 TimeStamp=14-11-07::13::51:13

(1) INSERT STATEMENT CHOOSE
Est. Rows: 1 Cost: 12
(16) SORT GROUP BY
Est. Rows: 1 Cost: 12
(15) FILTER
(14) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSPECTAX [Not Analyzed]
(14) Est. Rows: 1 Cost: 1
Tablespace: BIG_USERS
(13) NESTED LOOPS
Est. Rows: 1 Cost: 10
(11) NESTED LOOPS
Est. Rows: 1 Cost: 9
(2) TABLE ACCESS FULL SUPERMAG.SMCARDTAX [Analyzed]
(2) Blocks: 256 Est. Rows: 92 of 36 869 Cost: 8
Tablespace: USERS
(10) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 1
(9) UNION-ALL PARTITION
(5) FILTER
(4) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP [Analyzed]
(4) Blocks: 79 125 Est. Rows: 1 of 15 791 512 Cost: 60 973
Tablespace: BIG_USERS
(3) NON-UNIQUE INDEX FULL SCAN SUPERMAG.FFMAPREP_DOC [Analyzed]
Est. Rows: 6 316 605 Cost: 17 474
(8) FILTER
(7) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP_ [Analyzed]
(7) Est. Rows: 1 Cost: 1
Tablespace: USERS
(6) NON-UNIQUE INDEX RANGE SCAN SUPERMAG.FFMAPREP_ARTICLE_ [Analyzed]
Est. Rows: 1 Cost: 1
(12) UNIQUE INDEX RANGE SCAN SUPERMAG.SMCSPECTAX_PK [Analyzed]
Est. Rows: 1

OlegON · █ 14.11.2007 14:07

Короче, еще одна несовместимость с cursor_sharing=force, пришлось вернуть на exact

OlegON · █ 15.11.2007 08:13

Цитата:

SELECT a.taxgroupid, DECODE (o.expensetype, 1, 1, 2) oper_type, o.ID operid,
NVL (u.ID, -2) useroperid, o.NAME || ' ' || u.title opername,
m.saletype, m.salepaycash,
SUM (DECODE (m.saleq, 0, 0, m.salesum * m.quantity / m.saleq)
) sum_full,
SUM (DECODE (m.incomeq,
0, 0,
m.incomenovat * m.quantity / m.incomeq
)
) cp,
SUM (DECODE (m.incomeq, 0, 0, m.incomesum * m.quantity / m.incomeq)
) cp_full
FROM supermag.fvmaprep m,
supermag.smcardtax a,
supermag.saoperation o,
supermag.smuserop u
WHERE o.ID IN (1, 3)
AND m.saleop = o.ID
AND m.saleuserop = u.ID(+)
AND m.saletype IN ('CI', 'CS', 'WO', 'CI', 'CR', 'WI')
AND m.saleop IN (1, 3)
AND m.saledate BETWEEN TO_DATE ('01.01.2006', 'DD.MM.YYYY')
AND TO_DATE ('31.03.2006', 'DD.MM.YYYY')
AND NVL (m.salelocationfrom, m.salelocationto) = 12
AND a.article = m.article
AND TO_DATE ('31.03.2006', 'DD.MM.YYYY') BETWEEN a.datefrom AND a.dateto
GROUP BY a.taxgroupid,
DECODE (o.expensetype, 1, 1, 2),
o.ID,
NVL (u.ID, -2),
o.NAME || ' ' || u.title,
m.saletype,
m.salepaycash
ORDER BY 1 ASC, 5 ASC, 3 ASC, 2 ASC, 4 ASC, 7 ASC, 6 ASC

------------------------------------------------------------

Statement Id=1442592 Type=
Cost=2,64013623522001E-308 TimeStamp=15-11-07::08::08:37

(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 379
(16) SORT GROUP BY
Est. Rows: 1 Cost: 379
(15) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 377
(12) NESTED LOOPS
Est. Rows: 1 Cost: 376
(9) NESTED LOOPS
Est. Rows: 1 Cost: 375
(2) TABLE ACCESS FULL SUPERMAG.SMCARDTAX [Analyzed]
(2) Blocks: 180 Est. Rows: 36 878 of 36 878 Cost: 6
Tablespace: USERS
(8) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 1
(7) UNION-ALL PARTITION
(4) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP [Analyzed]
(4) Blocks: 76 498 Est. Rows: 1 of 15 805 517 Cost: 152 371
Tablespace: BIG_USERS
(3) NON-UNIQUE INDEX FULL SCAN SUPERMAG.FFMAPREP_DOC [Analyzed]
Est. Rows: 15 805 517 Cost: 16 831
(6) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP_ [Analyzed]
(6) Est. Rows: 1 Cost: 1
Tablespace: USERS
(5) NON-UNIQUE INDEX RANGE SCAN SUPERMAG.FFMAPREP_ARTICLE_ [Analyzed]
Est. Rows: 1 Cost: 1
(11) TABLE ACCESS BY INDEX ROWID SUPERMAG.SAOPERATION [Analyzed]
(11) Blocks: 4 Est. Rows: 1 of 32 Cost: 1
Tablespace: USERS
(10) UNIQUE INDEX UNIQUE SCAN SUPERMAG.SACOPERATION_PK [Analyzed]
Est. Rows: 1
(14) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMUSEROP [Analyzed]
(14) Blocks: 4 Est. Rows: 1 of 2 Cost: 1
Tablespace: USERS
(13) UNIQUE INDEX UNIQUE SCAN SUPERMAG.SMCUSEROP_PK [Analyzed]
Est. Rows: 1

Никак не могу избавиться от фулскана в отчете, дальше повесился... Есть идеи? Убился уже. С подсказкой использует индекс, index_cost_adj=1... Есть идеи? Доберусь до работы - буду трейс снимать... :(

Цитата:

SELECT /*+INDEX(a SMCCARDTAX_PK)*/a.taxgroupid, DECODE (o.expensetype, 1, 1, 2) oper_type, o.ID operid,
NVL (u.ID, -2) useroperid, o.NAME || ' ' || u.title opername,
m.saletype, m.salepaycash,
SUM (DECODE (m.saleq, 0, 0, m.salesum * m.quantity / m.saleq)
) sum_full,
SUM (DECODE (m.incomeq,
0, 0,
m.incomenovat * m.quantity / m.incomeq
)
) cp,
SUM (DECODE (m.incomeq, 0, 0, m.incomesum * m.quantity / m.incomeq)
) cp_full
FROM supermag.fvmaprep m,
supermag.smcardtax a,
supermag.saoperation o,
supermag.smuserop u
WHERE o.ID IN (1, 3)
AND m.saleop = o.ID
AND m.saleuserop = u.ID(+)
AND m.saletype IN ('CI', 'CS', 'WO', 'CI', 'CR', 'WI')
AND m.saleop IN (1, 3)
AND m.saledate BETWEEN TO_DATE ('01.01.2006', 'DD.MM.YYYY')
AND TO_DATE ('31.03.2006', 'DD.MM.YYYY')
AND NVL (m.salelocationfrom, m.salelocationto) = 12
AND a.article = m.article
AND TO_DATE ('31.03.2006', 'DD.MM.YYYY') BETWEEN a.datefrom AND a.dateto
GROUP BY a.taxgroupid,
DECODE (o.expensetype, 1, 1, 2),
o.ID,
NVL (u.ID, -2),
o.NAME || ' ' || u.title,
m.saletype,
m.salepaycash
ORDER BY 1 ASC, 5 ASC, 3 ASC, 2 ASC, 4 ASC, 7 ASC, 6 ASC

------------------------------------------------------------

Statement Id=1442592 Type=
Cost=2,64013623522001E-308 TimeStamp=15-11-07::08::12:51

(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 469
(17) SORT GROUP BY
Est. Rows: 1 Cost: 469
(16) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 467
(13) NESTED LOOPS
Est. Rows: 1 Cost: 466
(10) NESTED LOOPS
Est. Rows: 1 Cost: 465
(3) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARDTAX [Analyzed]
(3) Blocks: 180 Est. Rows: 36 878 of 36 878 Cost: 96
Tablespace: USERS
(2) UNIQUE INDEX FULL SCAN SUPERMAG.SMCCARDTAX_PK [Analyzed]
Est. Rows: 36 878 Cost: 302
(9) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 1
(8) UNION-ALL PARTITION
(5) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP [Analyzed]
(5) Blocks: 76 498 Est. Rows: 1 of 15 805 517 Cost: 152 371
Tablespace: BIG_USERS
(4) NON-UNIQUE INDEX FULL SCAN SUPERMAG.FFMAPREP_DOC [Analyzed]
Est. Rows: 15 805 517 Cost: 16 831
(7) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP_ [Analyzed]
(7) Est. Rows: 1 Cost: 1
Tablespace: USERS
(6) NON-UNIQUE INDEX RANGE SCAN SUPERMAG.FFMAPREP_ARTICLE_ [Analyzed]
Est. Rows: 1 Cost: 1
(12) TABLE ACCESS BY INDEX ROWID SUPERMAG.SAOPERATION [Analyzed]
(12) Blocks: 4 Est. Rows: 1 of 32 Cost: 1
Tablespace: USERS
(11) UNIQUE INDEX UNIQUE SCAN SUPERMAG.SACOPERATION_PK [Analyzed]
Est. Rows: 1
(15) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMUSEROP [Analyzed]
(15) Blocks: 4 Est. Rows: 1 of 2 Cost: 1
Tablespace: USERS
(14) UNIQUE INDEX UNIQUE SCAN SUPERMAG.SMCUSEROP_PK [Analyzed]
Est. Rows: 1

kadr · █ 15.11.2007 08:56

Вчера наблюдал такую же ситуацию как и у тебя
db_file_multiblock_read_count=128
после выставления параметра сбор статистики был,
Но оперативная работа из-за этого замедлилась
вернул обратно
db_file_multiblock_read_count=8
пересобрал статистику и вот сегодня имеем такой план:

Код:

SELECT STATEMENT, GOAL = CHOOSE            Cost=345578    Cardinality=1    Bytes=205
 SORT ORDER BY            Cost=345578    Cardinality=1    Bytes=205
  SORT GROUP BY            Cost=345578    Cardinality=1    Bytes=205
   TABLE ACCESS BY INDEX ROWID    Object owner=SUPERMAG    Object name=SMCARDTAX    Cost=2    Cardinality=1    Bytes=22
    NESTED LOOPS            Cost=345532    Cardinality=1    Bytes=205
     NESTED LOOPS OUTER            Cost=345530    Cardinality=1    Bytes=183
      NESTED LOOPS            Cost=345529    Cardinality=1    Bytes=164
       VIEW    Object owner=SUPERMAG        Cost=345528    Cardinality=1    Bytes=142
        UNION-ALL                    
         TABLE ACCESS FULL    Object owner=SUPERMAG    Object name=FFMAPREP    Cost=24439    Cardinality=10130    Bytes=476110
         TABLE ACCESS BY INDEX ROWID    Object owner=SUPERMAG    Object name=FFMAPREP_    Cost=6    Cardinality=1    Bytes=157
          INDEX RANGE SCAN    Object owner=SUPERMAG    Object name=FFMAPREP_SALEDATE_    Cost=2    Cardinality=23719    
       TABLE ACCESS BY INDEX ROWID    Object owner=SUPERMAG    Object name=SAOPERATION    Cost=1    Cardinality=1    Bytes=22
        INDEX UNIQUE SCAN    Object owner=SUPERMAG    Object name=SACOPERATION_PK        Cardinality=1    
      TABLE ACCESS BY INDEX ROWID    Object owner=SUPERMAG    Object name=SMUSEROP    Cost=1    Cardinality=1    Bytes=19
       INDEX UNIQUE SCAN    Object owner=SUPERMAG    Object name=SMCUSEROP_PK        Cardinality=1    
     INDEX RANGE SCAN    Object owner=SUPERMAG    Object name=SMCCARDTAX_PK    Cost=1    Cardinality=1

мы кстати обсуждали недавно с тобой этот параметр

OlegON · █ 15.11.2007 09:59

Да вот делал уже :( Первым делом выкрутил его на 16, flush shared_pool, собрал статистику... И ничего не изменилось :( Что очень озадачивает, SQL Tuning квестовый почему-то упрямо настаивает, что таблица у меня больше 2Гб, поэтому будет фулскан. Хотя таблица на самом деле:

Цитата:

SQL> select bytes from dba_segments where segment_name='SMCARDTAX';

BYTES
----------
2097152

kadr · █ 15.11.2007 10:11

у меня

Цитата:

BYTES
----------
5242880

но я говорю именно о 8-ми, IMHO т.к. в этом районе и идёт перелом поведения оптимизатора (индекс юзать или фуллскан) на моих данных.

OlegON · █ 15.11.2007 10:16

Цитата:

kadr ➤ у меня
но я говорю именно о 8-ми, IMHO т.к. в этом районе и идёт перелом поведения оптимизатора (индекс юзать или фуллскан) на моих данных.

Да я и 8 пробовал, что учитывая cost_adj=1 вообще ни в какие ворота не лезет...

avl2007 · █ 27.11.2007 12:14

optimizer_max_permutations ? можно попробовать поиграться с ним, сделать как на 8.1.7.
Убить статистику на табличке.

analyze table <> delete statistics;

Это так, на вскидку

paul · █ 03.12.2007 20:06

разработчикам супербага привет ;)

Не вдаваясь в смысл запроса, как по Вашему оракл должен разобрать это - "AND TO_DATE ('31.03.2006', 'DD.MM.YYYY') BETWEEN a.datefrom AND a.dateto". Только фулскан по таблице a!

А вот к этому уже можно прикрутить индекс "and a.datefrom <= TO_DATE ('31.03.2006', 'DD.MM.YYYY') and a.dateto >=TO_DATE ('31.03.2006', 'DD.MM.YYYY')"

OlegON · █ 03.12.2007 21:34

Цитата:

paul ➤ разработчикам супербага привет ;)

Если это мне, то мимо ;) Я, конечно, писал ТЗ всякие и пр., но к коду именно модулей Супермага ни разу руку не прикладывал, только костыли всякие писал, а от подсказок в отчетах сам зверею...

Цитата:

paul ➤ Не вдаваясь в смысл запроса, как по Вашему оракл должен разобрать это - "AND TO_DATE ('31.03.2006', 'DD.MM.YYYY') BETWEEN a.datefrom AND a.dateto". Только фулскан по таблице a!

Да ни разу... С чего бы это "только" (поручики, молчать)?

Цитата:

SQL Statement from editor:

INSERT INTO supermag.tttovreport
(doctype, opcode, useropcode, paycash, taxgroupid, taxid, taxsum)
SELECT m.saletype, m.saleop, NVL (m.saleuserop, -:"SYS_B_00"),
m.salepaycash, a.taxgroupid, t.taxid,
SUM (DECODE (m.saleq,
:"SYS_B_01", :"SYS_B_02",
t.taxsum * m.quantity / m.saleq
)
)
FROM supermag.fvmaprep m, supermag.smcardtax a, supermag.smspectax t
WHERE TO_DATE (:"SYS_B_03", :"SYS_B_04") BETWEEN a.datefrom AND a.dateto
AND a.article = m.article
AND NVL (m.salelocationfrom, m.salelocationto) = :"SYS_B_05"
AND m.saledate BETWEEN TO_DATE (:"SYS_B_06", :"SYS_B_07")
AND TO_DATE (:"SYS_B_08", :"SYS_B_09")
AND m.saletype IN (:"SYS_B_10", :"SYS_B_11")
AND m.saleop IN (:"SYS_B_12", :"SYS_B_13")
AND t.doctype = m.saletype
AND t.docid = m.saleid
AND t.specitem = m.salespecitem
GROUP BY m.saletype,
m.saleop,
NVL (m.saleuserop, -:"SYS_B_00"),
m.salepaycash,
a.taxgroupid,
t.taxid
------------------------------------------------------------

Statement Id=0 Type=
Cost=2,64013623522001E-308 TimeStamp=03-12-07::21::44:23

(1) INSERT STATEMENT CHOOSE
Est. Rows: 1 Cost: 49
(23) SORT GROUP BY
Est. Rows: 1 Cost: 49
(22) FILTER
(21) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSPECTAX [Analyzed]
(21) Blocks: 20 021 Est. Rows: 1 of 16 780 824 Cost: 1
Tablespace: BIG_USERS
(20) NESTED LOOPS
Est. Rows: 1 Cost: 32
(18) NESTED LOOPS
Est. Rows: 1 Cost: 31
(2) TABLE ACCESS FULL SUPERMAG.SMCARDTAX [Analyzed]
(2) Blocks: 181 Est. Rows: 93 of 37 039 Cost: 29
Tablespace: USERS
(17) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 1
(16) UNION-ALL PARTITION
(12) FILTER
(11) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP [Analyzed]
(11) Blocks: 29 008 Est. Rows: 1 of 16 562 905 Cost: 4
Tablespace: FF_ANAL
(10) BITMAP CONVERSION TO ROWIDS
(9) BITMAP AND
(3) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_ARTICLE
(5) BITMAP MERGE
(4) BITMAP INDEX RANGE SCAN SUPERMAG.FFMAPREP_SALEDATE
(8) BITMAP OR
(6) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_SALETYPE
(7) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_SALETYPE
(15) FILTER
(14) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP_ [Analyzed]
(14) Est. Rows: 1 Cost: 1
Tablespace: FF_ANAL
(13) NON-UNIQUE INDEX RANGE SCAN SUPERMAG.FFMAPREP_ARTICLE_ [Analyzed]
Est. Rows: 1 Cost: 1
(19) UNIQUE INDEX RANGE SCAN SUPERMAG.SMCSPECTAX_PK [Analyzed]
Est. Rows: 1 Cost: 1

На самом деле до сих пор в непонятках, при optimizer_index_cost_adj больше 2 - не работают заказы, но при отличном значении не работают отчеты. Сейчас optimizer_dynamic_sampling приравнял 2, вроде завелись отчеты... multiblock_read_count вообще как-то незначительно на планы влияет.. И плавают планы в зависимости от времени... Вообще обалдеть. Т.е. при старте базы одно, потом другое, потом опять возвращается, с разницей в часы... Загадка пока.