Вкладка "Документы", раздел "Карточка" : Oracle

reddevil · █ 18.03.2009 15:39

Если не сложно, покажите план для:

Код:

select sum( S.Quantity * decode (D.LocationTo, 199, 1, decode (D.LocationFrom, 199 , -1, 0) ) )
from Supermag.SmCard C, Supermag.SmDocuments D, Supermag.SmSpec S
where D.DocType = S.DocType and D.ID = S.DocID and C.Article = S.Article and C.Article =:ART 
and D.DocState >= 2 and D.CreatedAt<TO_DATE('20090309','YYYYMMDD') and  ( D.LocationTo = 199 or  D.LocationFrom = 199 )
group by C.Article

У меня почему то идеть по индексу SMSPEC_ART и занимает по нескольку минут, хотя если подсказать SMCSPEC_PK - несколько секунд. Пока пытаюсь пересобрать статитику, какие еще варианты без модификации запроса. Вобще смысловая нагрузка индекса SMSPEC_ART какова? Может его удалить а то с тех пор как SMSPEC к полмилиона записей подходит - от него одни проблемы.

OlegON · █ 18.03.2009 16:17

Код:

  1  select sum( S.Quantity * decode (D.LocationTo, 199, 1, decode (D.LocationFrom, 199 , -1, 0) ) )
  2  from Supermag.SmCard C, Supermag.SmDocuments D, Supermag.SmSpec S
  3  where D.DocType = S.DocType and D.ID = S.DocID and C.Article = S.Article and C.Article ='40316'
  4  and D.DocState >= 2 and D.CreatedAt<TO_DATE('20090309','YYYYMMDD') and  ( D.LocationTo = 199 or  D.LocationFrom = 199 )
  5* group by C.Article

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2665093416

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |     1 |    62 |     8   (0)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY NOSORT                |                     |     1 |    62 |     8   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID  | SMSPEC              |     1 |    25 |     3   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                      |                     |     1 |    62 |     8   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                     |                     |     1 |    37 |     5   (0)| 00:00:01 |       |       |
|*  5 |      INDEX UNIQUE SCAN               | SMCARD_PK           |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|*  6 |      TABLE ACCESS BY INDEX ROWID     | SMDOCUMENTS         |     1 |    31 |     5   (0)| 00:00:01 |       |       |
|   7 |       BITMAP CONVERSION TO ROWIDS    |                     |       |       |            |          |       |       |
|   8 |        BITMAP OR                     |                     |       |       |            |          |       |       |
|   9 |         BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |          |       |       |
|* 10 |          INDEX RANGE SCAN            | SMDOCUMENTS_LOCFROM |       |       |     1   (0)| 00:00:01 |       |       |
|  11 |         BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |          |       |       |
|* 12 |          INDEX RANGE SCAN            | SMDOCUMENTS_LOCTO   |       |       |     1   (0)| 00:00:01 |       |       |
|  13 |     PARTITION LIST ITERATOR          |                     |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 14 |      INDEX RANGE SCAN                | SMSPEC_ART          |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------

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

   5 - access("C"."ARTICLE"='40316')
   6 - filter("D"."DOCSTATE">=2 AND "D"."CREATEDAT"<TO_DATE(' 2009-03-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  10 - access("D"."LOCATIONFROM"=199)
  12 - access("D"."LOCATIONTO"=199)
  14 - access("S"."ARTICLE"='40316' AND "D"."DOCTYPE"="S"."DOCTYPE" AND "D"."ID"="S"."DOCID")


Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
         17  consistent gets
          2  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        480  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

reddevil · █ 19.03.2009 06:19

Цитата:

OlegON ➤

Код:

  1  select sum( S.Quantity * decode (D.LocationTo, 199, 1, decode (D.LocationFrom, 199 , -1, 0) ) )
  2  from Supermag.SmCard C, Supermag.SmDocuments D, Supermag.SmSpec S
  3  where D.DocType = S.DocType and D.ID = S.DocID and C.Article = S.Article and C.Article ='40316'
  4  and D.DocState >= 2 and D.CreatedAt<TO_DATE('20090309','YYYYMMDD') and  ( D.LocationTo = 199 or  D.LocationFrom = 199 )
  5* group by C.Article

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2665093416

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |     1 |    62 |     8   (0)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY NOSORT                |                     |     1 |    62 |     8   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID  | SMSPEC              |     1 |    25 |     3   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                      |                     |     1 |    62 |     8   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                     |                     |     1 |    37 |     5   (0)| 00:00:01 |       |       |
|*  5 |      INDEX UNIQUE SCAN               | SMCARD_PK           |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|*  6 |      TABLE ACCESS BY INDEX ROWID     | SMDOCUMENTS         |     1 |    31 |     5   (0)| 00:00:01 |       |       |
|   7 |       BITMAP CONVERSION TO ROWIDS    |                     |       |       |            |          |       |       |
|   8 |        BITMAP OR                     |                     |       |       |            |          |       |       |
|   9 |         BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |          |       |       |
|* 10 |          INDEX RANGE SCAN            | SMDOCUMENTS_LOCFROM |       |       |     1   (0)| 00:00:01 |       |       |
|  11 |         BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |          |       |       |
|* 12 |          INDEX RANGE SCAN            | SMDOCUMENTS_LOCTO   |       |       |     1   (0)| 00:00:01 |       |       |
|  13 |     PARTITION LIST ITERATOR          |                     |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 14 |      INDEX RANGE SCAN                | SMSPEC_ART          |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------

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

   5 - access("C"."ARTICLE"='40316')
   6 - filter("D"."DOCSTATE">=2 AND "D"."CREATEDAT"<TO_DATE(' 2009-03-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  10 - access("D"."LOCATIONFROM"=199)
  12 - access("D"."LOCATIONTO"=199)
  14 - access("S"."ARTICLE"='40316' AND "D"."DOCTYPE"="S"."DOCTYPE" AND "D"."ID"="S"."DOCID")


Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
         17  consistent gets
          2  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        480  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Отлично, осталось только узнать версия и время выполнения для существующих МХ и артикула!

OlegON · █ 19.03.2009 06:57

1024.6, но я структуру много где перевернул... В какой-то версии, вроде, бага была, что тормозила, посмотри в СМ-разделе.

Цитата:

SQL> select sum( S.Quantity * decode (D.LocationTo, 52, 1, decode (D.LocationFrom, 52 , -1, 0) ) )
from Supermag.SmCard C, Supermag.SmDocuments D, Supermag.SmSpec S
where D.DocType = S.DocType and D.ID = S.DocID and C.Article = S.Article and C.Article ='00378'
and D.DocState >= 2 and D.CreatedAt<TO_DATE('20090309','YYYYMMDD') and ( D.LocationTo = 52 or D.LocationFrom = 52 )
group by C.Article 2 3 4 5 ;

SUM(S.QUANTITY*DECODE(D.LOCATIONTO,52,1,DECODE(D.LOCATIONFROM,52,-1,0)))
------------------------------------------------------------------------
9

Elapsed: 00:00:00.49

Statistics
----------------------------------------------------------
4635 recursive calls
0 db block gets
13759 consistent gets
7 physical reads
648 redo size
579 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
77 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

reddevil · █ 19.03.2009 08:54

Код:

SQL> explain plan for select sum( S.Quantity * decode (D.LocationTo, 199, 1, decode (D.LocationFrom, 199 , -1, 0) ) )
  2  from Supermag.SmCard C, Supermag.SmDocuments D, Supermag.SmSpec S
  3  where D.DocType = S.DocType and D.ID = S.DocID and C.Article = S.Article and C.Article =:ART
  4  and D.DocState >= 2 and D.CreatedAt<TO_DATE('20090309','YYYYMMDD') and  ( D.LocationTo = 199 or  D.LocationFrom = 199 )
  5  group by C.Article
  6  /

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                      |  Name            | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |    79 |   77
|   1 |  SORT GROUP BY NOSORT          |                  |     1 |    79 |   77
|   2 |   NESTED LOOPS                 |                  |    19 |  1501 |   77
|   3 |    NESTED LOOPS                |                  |  3540 |   169K|   38
|*  4 |     INDEX UNIQUE SCAN          | SMCARD_PK        |     1 |    13 |     
|   5 |     TABLE ACCESS BY INDEX ROWID| SMSPEC           |  3540 |   124K|   38
|*  6 |      INDEX RANGE SCAN          | SMSPEC_ART       |  3540 |       |     
|*  7 |    TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS      |     1 |    30 |     
|*  8 |     INDEX UNIQUE SCAN          | SMCDOCUMENTS_PK  |     1 |       |     
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   4 - access("C"."ARTICLE"=:Z)                                                 
   6 - access("S"."ARTICLE"=:Z)                                                 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
   7 - filter("D"."DOCSTATE">=2 AND "D"."CREATEDAT"<TO_DATE(' 2009-03-09        
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("D"."LOCATIONTO"=199 OR 
              "D"."LOCATIONFROM"=199))                                          
   8 - access("D"."DOCTYPE"="S"."DOCTYPE" AND "D"."ID"="S"."DOCID")             
Note: cpu costing is off                                                        

26 rows selected

SQL> 
SQL> explain plan for select /*+ index(s smcspec_pk)*/ sum( S.Quantity * decode (D.LocationTo, 199, 1, decode (D.LocationFrom, 199 , -1, 0) ) )
  2  from Supermag.SmCard C, Supermag.SmDocuments D, Supermag.SmSpec S
  3  where D.DocType = S.DocType and D.ID = S.DocID and C.Article = S.Article and C.Article ='Т0000012233'
  4  and D.DocState >= 2 and D.CreatedAt<TO_DATE('20090309','YYYYMMDD') and  ( D.LocationTo = 199 or  D.LocationFrom = 199 )
  5  group by C.Article
  6  /

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                        |  Name                | Rows  | Bytes 
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |     1 |    79 
|   1 |  SORT GROUP BY NOSORT            |                      |     1 |    79 
|   2 |   CONCATENATION                  |                      |       |       
|*  3 |    TABLE ACCESS BY INDEX ROWID   | SMSPEC               |     1 |    36 
|   4 |     NESTED LOOPS                 |                      |     1 |    79 
|   5 |      NESTED LOOPS                |                      |   560 | 24080 
|*  6 |       INDEX UNIQUE SCAN          | SMCARD_PK            |     1 |    13 
|*  7 |       TABLE ACCESS BY INDEX ROWID| SMDOCUMENTS          |   560 | 16800 
|*  8 |        INDEX RANGE SCAN          | SMDOCUMENTS_LOCFROM  | 19416 |       
|*  9 |      INDEX RANGE SCAN            | SMCSPEC_PK           |     6 |       
|* 10 |    TABLE ACCESS BY INDEX ROWID   | SMSPEC               |     1 |    36 
|  11 |     NESTED LOOPS                 |                      |     1 |    79 
|  12 |      NESTED LOOPS                |                      |   560 | 24080 
|* 13 |       INDEX UNIQUE SCAN          | SMCARD_PK            |     1 |    13 
|* 14 |       TABLE ACCESS BY INDEX ROWID| SMDOCUMENTS          |   560 | 16800 
|* 15 |        INDEX RANGE SCAN          | SMDOCUMENTS_LOCTO    | 19416 |       

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|* 16 |      INDEX RANGE SCAN            | SMCSPEC_PK           |     6 |       
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   3 - filter("S"."ARTICLE"='Т0000012233')                                      
   6 - access("C"."ARTICLE"='Т0000012233')                                      
   7 - filter("D"."DOCSTATE">=2 AND "D"."CREATEDAT"<TO_DATE(' 2009-03-09 00:00:0
              'syyyy-mm-dd hh24:mi:ss'))                                        
   8 - access("D"."LOCATIONFROM"=199)                                           
   9 - access("D"."DOCTYPE"="S"."DOCTYPE" AND "D"."ID"="S"."DOCID")             
  10 - filter("S"."ARTICLE"='Т0000012233')                                      
  13 - access("C"."ARTICLE"='Т0000012233')                                      
  14 - filter("D"."CREATEDAT"<TO_DATE(' 2009-03-09 00:00:00', 'syyyy-mm-dd hh24:
              AND "D"."DOCSTATE">=2 AND LNNVL("D"."LOCATIONFROM"=199))          
  15 - access("D"."LOCATIONTO"=199)                                             
  16 - access("D"."DOCTYPE"="S"."DOCTYPE" AND "D"."ID"="S"."DOCID")             
Note: cpu costing is off                                                        

40 rows selected

SQL>

Вобщем задача сводиться к тому что привести план ко второму. который не сомтря на большую стоимость имеет время выполнения в 100 раз меньше.

OlegON · █ 19.03.2009 09:00

Код:

select banner from v$version;

Код:

show parameter cost_adj

системная статистика?

reddevil · █ 19.03.2009 09:08

Код:

SQL> show parameter cost_adj

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     11
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL>

reddevil · █ 19.03.2009 09:10

Цитата:

OlegON ➤ системная статистика?

Как именно? (Я к этому с опаской отношусь.)

reddevil · █ 19.03.2009 09:37

Цитата:

OlegON ➤

Код:

select banner from v$version;

Код:

show parameter cost_adj

системная статистика?

Проверено на 4 базах с разными обьемами, разными версиями и параметрами. На одной из них план нормальный. Пока все уперлось в необходимость удаления и сбора статитики по SMSPEC.

OlegON · █ 19.03.2009 09:42

:( У меня на 9ке тоже с системной не сдружилось. Может что-то не так делал... Но на 10ку пойти - лучше будет, если версия СМ позволяет. Еще, насколько много народа у тебя пишет спецификации? В качестве варианта (у тебя Enterprise) и если не так много народу забивает спецификации - повтыкать битмапы. Они значительно меньше по размеру и планы с ними красивее. Опять же, помня, что у меня 10ка, я от adj ушел (=100). Не нравилось мне, как он работает, уж не помню где именно. На 9ке стабильно ставил = 1, иначе во многих местах не бегало... Трудно советовать, я многое что гонял уже на 10ке.