1 2
█ 16.07.2010 09:25
Цитата:
vdm ➤ 2 fullscan - только в одной самой мелкой базе.
Интересно... На 10ке принцип оценки другой и меня немного не радуют параметры, при которых он самостоятельно переходит на индекс.
По умолчанию... Блок какой?
█ 16.07.2010 10:35
Код:
SQL*Plus: Release 10.2.0.4.0 - Production on Пт Июл 16 10:33:04 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Присоединен к:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> EXPLAIN PLAN FOR 
  2  SELECT   TO_CHAR (ch.printtime, 'YYYY') AS YEAR, TO_CHAR (ch.printtime, 'MM') AS MONTH, ch.loci
d, chit.article,
  3           SUM (CASE
  4                   WHEN ch.opcode = 1
  5                      THEN chit.quantity
  6                   ELSE -chit.quantity
  7                END) AS qty, SUM (CASE
  8                                     WHEN ch.opcode = 1
  9                                        THEN chit.totalsum
 10                                     ELSE -chit.totalsum
 11                                  END) AS tsum
 12      FROM supermag.smcashchecks ch INNER JOIN supermag.smcashcheckitems chit
 13           ON (ch.checknum = chit.checknum) AND (ch.znum = chit.znum) AND (ch.desknum = chit.desk
num) AND (ch.locid = chit.locid)
 14     WHERE ch.printtime BETWEEN '01.07.2010' AND '15.07.2010'
 15  GROUP BY TO_CHAR (ch.printtime, 'YYYY'), TO_CHAR (ch.printtime, 'MM'), ch.locid, chit.article;

Объяснено.

SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3798077075

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   179 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY               |                      |     1 |   179 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SMCASHCHECKITEMS     |     1 |   105 |     0   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                      |     1 |   179 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL        | SMCASHCHECKS         |     1 |    74 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | SMCCASHCHECKITEMS_PK |     1 |       |     0   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------

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

   4 - filter("CH"."PRINTTIME">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "CH"."PRINTTIME"<=TO_DATE(' 2010-07-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("CH"."LOCID"="CHIT"."LOCID" AND "CH"."DESKNUM"="CHIT"."DESKNUM" AND
              "CH"."ZNUM"="CHIT"."ZNUM" AND "CH"."CHECKNUM"="CHIT"."CHECKNUM")

20 строк выбрано.
█ 16.07.2010 10:51
Цитата:
OlegON ➤ По умолчанию... Блок какой?
Блок 8К
mbrc = 16
█ 16.07.2010 12:28
Код:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1702629616

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |   331K|    18M|       | 36238   (1)| 00:08:28 |
|   1 |  HASH GROUP BY                 |                            |   331K|    18M|    48M| 36238   (1)| 00:08:28 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | SMCASHCHECKITEMS           |     9 |   279 |       |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                            |   331K|    18M|       | 33374   (1)| 00:07:48 |
|   4 |     TABLE ACCESS BY INDEX ROWID| SMCASHCHECKS               | 38652 |   981K|       | 10176   (1)| 00:02:23 |
|*  5 |      INDEX RANGE SCAN          | SMCCASHCHECKS_AM_PRINTTIME | 38652 |       |       |    16   (0)| 00:00:01 |

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

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

   5 - access("CH"."PRINTTIME">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "CH"."PRINTTIME"<=TO_DATE(' 2010-07-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("CH"."LOCID"="CHIT"."LOCID" AND "CH"."DESKNUM"="CHIT"."DESKNUM" AND "CH"."ZNUM"="CHIT"."ZNUM"
              AND "CH"."CHECKNUM"="CHIT"."CHECKNUM")

21 строк выбрано.
█ 16.07.2010 13:28
10.2.0.4
на большой базе ЦО
Код:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3798077075

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |    43 |    53   (6)| 00:00:01 |
|   1 |  HASH GROUP BY               |                      |     1 |    43 |    53   (6)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SMCASHCHECKITEMS     |     1 |    23 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                      |     1 |    43 |    52   (4)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL        | SMCASHCHECKS         |     1 |    20 |    50   (4)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | SMCCASHCHECKITEMS_PK |     1 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------

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

   4 - filter("CH"."PRINTTIME">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "CH"."PRINTTIME"<=TO_DATE(' 2010-07-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("CH"."LOCID"="CHIT"."LOCID" AND "CH"."DESKNUM"="CHIT"."DESKNUM" AND
              "CH"."ZNUM"="CHIT"."ZNUM" AND "CH"."CHECKNUM"="CHIT"."CHECKNUM")
болшая база магазина

Код:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |    27 |  1512 |  5986   (4)|
|   1 |  HASH GROUP BY               |                      |    27 |  1512 |  5986   (4)|
|   2 |   TABLE ACCESS BY INDEX ROWID| SMCASHCHECKITEMS     |    10 |   300 |     4   (0)|
|   3 |    NESTED LOOPS              |                      |    27 |  1512 |  5985   (4)|
|*  4 |     TABLE ACCESS FULL        | SMCASHCHECKS         |     3 |    78 |  5973   (4)|
|*  5 |     INDEX RANGE SCAN         | SMCCASHCHECKITEMS_PK |     2 |       |     3   (0)|
------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("CH"."PRINTTIME">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CH"."PRINTTIME"<=TO_DATE(' 2010-07-15 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   5 - access("CH"."LOCID"="CHIT"."LOCID" AND "CH"."DESKNUM"="CHIT"."DESKNUM" AND
              "CH"."ZNUM"="CHIT"."ZNUM" AND "CH"."CHECKNUM"="CHIT"."CHECKNUM")
9.2.0.8 база магазина
Код:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                 | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |    19 |  1064 |   815 |
|   1 |  SORT GROUP BY               |                       |    19 |  1064 |   815 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SMCASHCHECKITEMS      |     7 |   210 |     4 |
|   3 |    NESTED LOOPS              |                       |    19 |  1064 |   813 |
|*  4 |     TABLE ACCESS FULL        | SMCASHCHECKS          |     3 |    78 |   801 |
|*  5 |     INDEX RANGE SCAN         | SMCCASHCHECKITEMS_PK  |     1 |       |     3 |
--------------------------------------------------------------------------------------
█ 18.07.2010 22:44
Всем спасибо большое. Так и не разгадал пока причин неправильно собираемой статистики у себя... MBRC традиционно лезет в потолок, а MREADTIM меньше SREADTIM. Точнее, причины-то понятны, непонятно, как заставить его работать правильно :(
█ 24.02.2011 22:02
Сами мы не местные...
В общем, прошу план запроса:
Код:
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('20110221','YYYYMMDD') 
and Trunc(H.PrintTime) <=TO_DATE('20110223','YYYYMMDD') 
and exists (select * from Supermag.SVCashDiscCard DC where DC.LocID=H.LocID and DC.DeskNum=H.DeskNum and DC.ZNum=H.ZNum and DC.CheckNum=H.CheckNum and DC.State=H.State and DC.Code='2910045113994') and ((H.LocId in ('-1', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25','26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52'
, '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83','84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104','105', '106', '107', '108', '109', '110', '111','112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '172', '173', '174')));
Поиск по диск. карте и времени чека медленно умер :(
█ 28.02.2011 09:07
БД ЦО:
Код:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2922879695

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |     1 |  8227 |   836K (74)| 00:00:21 |
|   1 |  NESTED LOOPS                    |                      |     1 |  8227 |   836K (74)| 00:00:21 |
|   2 |   SORT UNIQUE                    |                      |   287 | 23534 |   508  (17)| 00:00:01 |
|   3 |    VIEW                          | SVCASHDISCCARD       |   287 | 23534 |   508  (17)| 00:00:01 |
|   4 |     HASH UNIQUE                  |                      |   287 |  6371 |   508  (17)| 00:00:01 |
|   5 |      UNION-ALL                   |                      |       |       |            |          |
|*  6 |       INDEX SKIP SCAN            | SMCASHDISCCARD_PK    |   286 |  6292 |   507  (17)| 00:00:01 |
|*  7 |       INDEX SKIP SCAN            | SMCONLINEDISCCARD_PK |     1 |    79 |     1   (0)| 00:00:01 |
|*  8 |   VIEW                           | SVCASHCHECKS         |     1 |  8145 |   836K (74)| 00:00:21 |
|   9 |    UNION-ALL                     |                      |       |       |            |          |
|  10 |     NESTED LOOPS ANTI            |                      |   127K|    10M|   836K (74)| 00:00:21 |
|  11 |      INLIST ITERATOR             |                      |       |       |            |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID| SMCASHCHECKS         |   127K|  4100K|   826K (73)| 00:00:20 |
|* 13 |        INDEX RANGE SCAN          | SMCCASHCHECKS_PK     |    50M|       |   115K (79)| 00:00:03 |
|* 14 |      INDEX UNIQUE SCAN           | SMCONLINECHECKS_PK   |     1 |    52 |     1   (0)| 00:00:01 |
|  15 |     INLIST ITERATOR              |                      |       |       |            |          |
|* 16 |      TABLE ACCESS BY INDEX ROWID | SMONLINECHECKS       |     1 |   100 |     1   (0)| 00:00:01 |
|* 17 |       INDEX RANGE SCAN           | SMCONLINECHECKS_PK   |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
█ 28.02.2011 09:49
Спасибо большое. Правильно я понимаю, что отбор чеков по номеру дисконтной карты и интервалу времени не дает результатов во вменяемое время?
█ 28.02.2011 10:12
Цитата:
OlegON ➤ Правильно я понимаю, что отбор чеков по номеру дисконтной карты и интервалу времени не дает результатов во вменяемое время?
Да, так и есть.
Часовой пояс GMT +3, время: 10:10.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.