Отбор кассовых чеков, Oracle 10g : Супермаг Плюс (Супермаг 2000)

orekhov · █ 25.05.2009 19:52

Oracle 10.2.0.4, Супермаг+ 1.026.3 SP7
Отбираю кассовые чеки за один день по дате чека. Около 1800 строк за 3,5 секунды.
Добавляю в критерии отбора номер артикула. Получаю в итоге результат через 9,5 минут. Статистика собиралась как штатными средствами, так и через dbms_stats.gather_schema_stats.
У кого 10-ка, проверьте у себя пожалуйста.

OlegON · █ 25.05.2009 20:48

10.2.0.4 24.6 - 3 сек/15 сек. (>10000 строк в первой выборке)
10.2.0.4 26.3сп4 - <1сек,<1сек (400 строк в первой выборке)
10.2.0.4 26.2 - <1сек,<1сек (600 строк в первой выборке)
тебя сп7 именно беспокоит? сп7 у меня нет, а остальное - 9ка...

kadr · █ 26.05.2009 08:03

А все индексы живы и доступны?

OlegON · █ 26.05.2009 15:06

В переводе - ждем плана того запроса, что корячится 9 минут.

Shiba · █ 27.05.2009 16:34

Сам запрос.

Цитата:

SELECT
H.LocID,H.DeskNum,H.ZNum,H.CheckNum,Trunc(H.PrintTime) as PrintDate,To_char(H.PrintTime,'HH24:MI') as PrintTime,H.TotalSum,H.OpCode,H.ClientName,H.DiscCard,H.CredCardName,H.State,H.Printed,H.CashierSurname,H.CloseDate,H.ZReady
FROM Supermag.SVCashChecks H
WHERE Trunc(H.CloseDate) =TO_DATE('20090525','YYYYMMDD') and exists (select * from Supermag.SVCashCheckItems SP where SP.Article = '076052' and SP.LocID=H.LocID and SP.DeskNum=H.DeskNum and SP.ZNum=H.ZNum and SP.CheckNum=H.CheckNum and SP.State=H.State) and ((H.LocId in ('-1', '1', '2')))

OlegON · █ 27.05.2009 17:36

Код:

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |     1 |  8215 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI               |                        |     1 |  8215 |    14   (0)| 00:00:01 |
|   2 |   VIEW                           | SVCASHCHECKS           |     2 | 16306 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL                     |                        |       |       |            |          |
|   4 |     NESTED LOOPS ANTI            |                        |     1 |    84 |     6   (0)| 00:00:01 |
|   5 |      INLIST ITERATOR             |                        |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| SMCASHCHECKS           |     1 |    32 |     6   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | SMCCASHCHECKS_PK       |     1 |       |     5   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN           | SMCONLINECHECKS_PK     |     1 |    52 |     0   (0)| 00:00:01 |
|   9 |     INLIST ITERATOR              |                        |       |       |            |          |
|  10 |      TABLE ACCESS BY INDEX ROWID | SMONLINECHECKS         |     1 |   100 |     0   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN           | SMCONLINECHECKS_PK     |     1 |       |     0   (0)| 00:00:01 |
|* 12 |   VIEW                           | SVCASHCHECKITEMS       |     1 |    62 |     4   (0)| 00:00:01 |
|  13 |    UNION ALL PUSHED PREDICATE    |                        |       |       |            |          |
|  14 |     NESTED LOOPS ANTI            |                        |     1 |    89 |     4   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS BY INDEX ROWID | SMCASHCHECKITEMS       |     1 |    24 |     4   (0)| 00:00:01 |
|* 16 |       INDEX RANGE SCAN           | SMCCASHCHECKITEMS_PK   |     1 |       |     3   (0)| 00:00:01 |
|* 17 |      INDEX UNIQUE SCAN           | SMCONLINECHECKITEMS_PK |     1 |    65 |     0   (0)| 00:00:01 |
|* 18 |     TABLE ACCESS BY INDEX ROWID  | SMONLINECHECKITEMS     |     1 |    79 |     0   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN            | SMCONLINECHECKITEMS_PK |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

orekhov · █ 27.05.2009 17:37

Собственно запрос и план.

SQL Statement from editor:

SELECT --+ FIRST_ROWS
h.locid, h.desknum, h.znum, h.checknum,
TRUNC (h.printtime) AS printdate,
TO_CHAR (h.printtime, 'HH24:MI') AS printtime, h.totalsum, h.opcode,
h.clientname, h.disccard, h.credcardname, h.state, h.printed,
h.cashiersurname, h.closedate, h.zready
FROM supermag.svcashchecks h
WHERE TRUNC (h.printtime) = TO_DATE ('20090301', 'YYYYMMDD')
AND EXISTS (
SELECT *
FROM supermag.svcashcheckitems sp
WHERE sp.article = '000084'
AND sp.locid = h.locid
AND sp.desknum = h.desknum
AND sp.znum = h.znum
AND sp.checknum = h.checknum
AND sp.state = h.state)
AND ((h.locid IN ('-1', '1', '2')))

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

Statement Id=18 Type=TABLE ACCESS
Cost=2 TimeStamp=27-05-09::16::32:26

(1) SELECT STATEMENT HINT: FIRST_ROWS
Est. Rows: 1 Cost: 17543
(20) NESTED LOOPS
Est. Rows: 1 Cost: 17543
(9) SORT UNIQUE
Est. Rows: 175 Cost: 4743
(8) VIEW VIEW SUPERMAG.SVCASHCHECKITEMS
Est. Rows: 175 Cost: 4743
(7) HASH UNIQUE
Est. Rows: 175 Cost: 4743
(6) UNION-ALL
(4) NESTED LOOPS ANTI
Est. Rows: 174 Cost: 4740
(2) TABLE TABLE ACCESS FULL SUPERMAG.SMCASHCHECKITEMS [Analyzed]
(2) Blocks: 21317 Est. Rows: 174 of 3240893 Cost: 4740
Tablespace: USERS
(3) INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCONLINECHECKITEMS_PK [Analyzed]
Est. Rows: 1
(5) TABLE TABLE ACCESS FULL SUPERMAG.SMONLINECHECKITEMS [Analyzed]
(5) Blocks: 5 Est. Rows: 1 of 1 Cost: 3
Tablespace: USERS
(19) VIEW VIEW SUPERMAG.SVCASHCHECKS
Est. Rows: 1 Cost: 12799
(18) UNION-ALL
(14) NESTED LOOPS ANTI
Est. Rows: 9671 Cost: 12797
(12) INLIST ITERATOR
(11) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCASHCHECKS [Analyzed]
(11) Blocks: 5410 Est. Rows: 9671 of 967121 Cost: 12796
Tablespace: USERS
(10) INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCCASHCHECKS_PK [Analyzed]
Est. Rows: 967121 Cost: 2575
(13) INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCONLINECHECKS_PK [Analyzed]
Est. Rows: 1
(17) INLIST ITERATOR
(16) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMONLINECHECKS [Analyzed]
(16) Blocks: 5 Est. Rows: 1 of 1 Cost: 2
Tablespace: USERS
(15) INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCONLINECHECKS_PK [Analyzed]
Est. Rows: 1 Cost: 1

OlegON · █ 27.05.2009 17:45

Код:

SQL>   1  SELECT --+ FIRST_ROWS
  2           h.locid, h.desknum, h.znum, h.checknum,
  3           TRUNC (h.printtime) AS printdate,
  4           TO_CHAR (h.printtime, 'HH24:MI') AS printtime, h.totalsum, h.opcode,
  5           h.clientname, h.disccard, h.credcardname, h.state, h.printed,
  6           h.cashiersurname, h.closedate, h.zready
  7      FROM supermag.svcashchecks h
  8     WHERE TRUNC (h.printtime) = TO_DATE ('20090301', 'YYYYMMDD')
  9       AND EXISTS (
 10              SELECT *
 11                FROM supermag.svcashcheckitems sp
 12               WHERE sp.article = '000084'
 13                 AND sp.locid = h.locid
 14                 AND sp.desknum = h.desknum
 15                 AND sp.znum = h.znum
 16                 AND sp.checknum = h.checknum
 17                 AND sp.state = h.state)
 18*      AND ((h.locid IN ('-1', '1', '2')))

no rows selected

Elapsed: 00:00:23.77

Execution Plan
----------------------------------------------------------
Plan hash value: 765341505

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |     1 |  8215 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI               |                        |     1 |  8215 |    14   (0)| 00:00:01 |
|   2 |   VIEW                           | SVCASHCHECKS           |     2 | 16306 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL                     |                        |       |       |            |          |
|   4 |     NESTED LOOPS ANTI            |                        |     1 |    84 |     6   (0)| 00:00:01 |
|   5 |      INLIST ITERATOR             |                        |       |       |            |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID| SMCASHCHECKS           |     1 |    32 |     6   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | SMCCASHCHECKS_PK       |    14 |       |     5   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN           | SMCONLINECHECKS_PK     |     1 |    52 |     0   (0)| 00:00:01 |
|   9 |     INLIST ITERATOR              |                        |       |       |            |          |
|* 10 |      TABLE ACCESS BY INDEX ROWID | SMONLINECHECKS         |     1 |   100 |     0   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN           | SMCONLINECHECKS_PK     |     1 |       |     0   (0)| 00:00:01 |
|* 12 |   VIEW                           | SVCASHCHECKITEMS       |     1 |    62 |     4   (0)| 00:00:01 |
|  13 |    UNION ALL PUSHED PREDICATE    |                        |       |       |            |          |
|  14 |     NESTED LOOPS ANTI            |                        |     1 |    89 |     4   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS BY INDEX ROWID | SMCASHCHECKITEMS       |     1 |    24 |     4   (0)| 00:00:01 |
|* 16 |       INDEX RANGE SCAN           | SMCCASHCHECKITEMS_PK   |     1 |       |     3   (0)| 00:00:01 |
|* 17 |      INDEX UNIQUE SCAN           | SMCONLINECHECKITEMS_PK |     1 |    65 |     0   (0)| 00:00:01 |
|* 18 |     TABLE ACCESS BY INDEX ROWID  | SMONLINECHECKITEMS     |     1 |    79 |     0   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN            | SMCONLINECHECKITEMS_PK |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

orekhov · █ 27.05.2009 17:47

Получается, TRUNC вызывает фулскан по smcashcheckitems.
В качестве эксперимента убираю TRUNC и добавляю время чека к дате - тормоза пропадают:

SQL Statement from editor:

SELECT --+ FIRST_ROWS
h.locid, h.desknum, h.znum, h.checknum,
TRUNC (h.printtime) AS printdate,
TO_CHAR (h.printtime, 'HH24:MI') AS printtime, h.totalsum, h.opcode,
h.clientname, h.disccard, h.credcardname, h.state, h.printed,
h.cashiersurname, h.closedate, h.zready
FROM supermag.svcashchecks h
WHERE h.printtime = TO_DATE ('200903011217', 'YYYYMMDDHH24MI')
AND EXISTS (
SELECT *
FROM supermag.svcashcheckitems sp
WHERE sp.article = '000084'
AND sp.locid = h.locid
AND sp.desknum = h.desknum
AND sp.znum = h.znum
AND sp.checknum = h.checknum
AND sp.state = h.state)
AND ((h.locid IN ('-1', '1', '2')))

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

Statement Id=18 Type=TABLE ACCESS
Cost=2 TimeStamp=27-05-09::16::42:59

(1) SELECT STATEMENT HINT: FIRST_ROWS
Est. Rows: 1 Cost: 12768
(20) NESTED LOOPS SEMI
Est. Rows: 1 Cost: 12768
(11) VIEW VIEW SUPERMAG.SVCASHCHECKS
Est. Rows: 3 Cost: 12750
(10) UNION-ALL
(6) NESTED LOOPS ANTI
Est. Rows: 2 Cost: 12748
(4) INLIST ITERATOR
(3) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCASHCHECKS [Analyzed]
(3) Blocks: 5410 Est. Rows: 2 of 967121 Cost: 12748
Tablespace: USERS
(2) INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCCASHCHECKS_PK [Analyzed]
Est. Rows: 967121 Cost: 2575
(5) INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCONLINECHECKS_PK [Analyzed]
Est. Rows: 1
(9) INLIST ITERATOR
(8) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMONLINECHECKS [Analyzed]
(8) Blocks: 5 Est. Rows: 1 of 1 Cost: 2
Tablespace: USERS
(7) INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCONLINECHECKS_PK [Analyzed]
Est. Rows: 1 Cost: 1
(19) VIEW VIEW SUPERMAG.SVCASHCHECKITEMS
Est. Rows: 1 Cost: 6
(18) UNION ALL PUSHED PREDICATE
(15) NESTED LOOPS ANTI
Est. Rows: 1 Cost: 4
(13) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCASHCHECKITEMS [Analyzed]
(13) Blocks: 21317 Est. Rows: 1 of 3240893 Cost: 4
Tablespace: USERS
(12) INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCCASHCHECKITEMS_PK [Analyzed]
Est. Rows: 1 Cost: 3
(14) INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCONLINECHECKITEMS_PK [Analyzed]
Est. Rows: 1
(17) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMONLINECHECKITEMS [Analyzed]
(17) Blocks: 5 Est. Rows: 1 of 1 Cost: 2
Tablespace: USERS
(16) INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCONLINECHECKITEMS_PK [Analyzed]
Est. Rows: 1 Cost: 1

OlegON · █ 27.05.2009 17:49

Цитата:

orekhov ➤ (2) TABLE TABLE ACCESS FULL SUPERMAG.SMCASHCHECKITEMS [Analyzed]

ну например это... и по онлайновым... по ним, конечно, меньшая проблема, но общая тенденция...