[ОТВЕТИТЬ]
Опции темы
06.06.2013 22:49  
necse
Цитата:
Сообщение от Stels
а скрипт сервиспака после генератора надо прогонять?
Нет. Не надо.
 
06.06.2013 23:08  
Stels
генератором прошёлся

запускал штатные задания:
Полное пересоздание индексов,
Полный сбор статистики для Оракла 10

ситуация не меняется
 
07.06.2013 11:11  
Neutron
Цитата:
Сообщение от Stels
а скрипт сервиспака после генератора надо прогонять?
Если есть СП то надо!
Посмотри в сторону PGA SGA...какие параметры памяти были до 10g и какие после?
 
14.06.2013 13:01  
cb
Надо смотреть запрос. Может для оракловского оптимизатора стоит all_rows, а в запросе стоит свой неоптимальный хинт. И смотреть как распределена память. Хотя если все работает и нет жалоб, то скорее всего запрос.
 
14.06.2013 23:38  
Stels
т.е. один и тот же запрос на Oracle9 и Oracle10 может выполнятся по-разному?
 
15.06.2013 12:37  
Neutron
Цитата:
Сообщение от Stels
т.е. один и тот же запрос на Oracle9 и Oracle10 может выполнятся по-разному?
Как насчет параметров памяти?
 
17.06.2013 06:54  
cb
Цитата:
Сообщение от Stels
т.е. один и тот же запрос на Oracle9 и Oracle10 может выполнятся по-разному?
Если не собрана статистика, неправильно распределена память, сломались индексы при импорте, то план может быть другой.
 
29.07.2013 08:45  
Stels
продолжу
в тот раз откатился обратно на Oracle 9i
сейчас пробую снова.
И снова этот заказной отчёт тормозит

похоже надо крутить параметры базы :(
в этом не силён
что имеем:
Core 2DUO E6550 @ 2.33Hz
4Gb оперативы
Win2003R2 sp2 32bit
boot.ini : /PAE /3GB
raid intel SRCS28X .... 2 scsi винта в зеркале на 10 000 rpm

init.ora
Код:
kras01.__db_cache_size=1887436800
kras01.__java_pool_size=8388608
kras01.__large_pool_size=16777216
kras01.__shared_pool_size=192937984
kras01.__streams_pool_size=0
*.audit_file_dest='C:\oracle\admin\kras01\adump'
*.audit_sys_operations=FALSE
*.audit_trail='XML'
*.background_dump_dest='C:\oracle\admin\kras01\bdump'
*.commit_write='batch,nowait'
*.compatible='10.2.0.5.0'
*.control_files='I:\Oracle\Oradata\kras01\control01.ctl','I:\Oracle\Oradata\kras01\control02.ctl','I:\Oracle\Oradata\kras01\control03.ctl'
*.core_dump_dest='C:\oracle\admin\kras01\cdump'
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='kras01'
*.db_recovery_file_dest_size=2147483648
*.db_recycle_cache_size=32000000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=kras01XDB)'
*.fast_start_mttr_target=600
*.job_queue_processes=10
*.large_pool_size=10000000
*.log_archive_dest='h:\Backup_archivlog'
*.log_archive_start=TRUE
*.max_dump_file_size='5000000'
*.nls_language='RUSSIAN'
*.nls_territory='RUSSIA'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.optimizer_features_enable='10.2.0.5'
*.pga_aggregate_target=311427072
*.processes=150
*.query_rewrite_integrity='stale_tolerated'
*.recyclebin='off'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=2147483648
*.sga_target=2147483648
*.shared_pool_size=100000000
*.timed_statistics=TRUE
*.trace_enabled=FALSE
*.transactions_per_rollback_segment=1
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\kras01\udump'
насколько я понял висит вот на этом запросе
Код:
SQL Statement:

   SELECT sum(quantity) AS cnt, article, type_oper
    FROM (((((((SELECT b.quantity, b.article, 1 AS type_oper
                    FROM smdocuments a, smspec b
                    WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
                      AND a.doctype = 'CS'
                      AND a.id = b.docid
                      AND a.doctype = b.doctype
                      AND a.docstate > 1
                      AND a.locationfrom IN (SELECT id
                               FROM smstorelocations)
                UNION ALL
                SELECT -b.quantity, b.article, 1 AS type_oper
                    FROM smdocuments a, smspec b
                    WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
                      AND a.doctype = 'CR'
                      AND a.id = b.docid
                      AND a.doctype = b.doctype
                      AND a.docstate > 1
                      AND a.locationto IN (SELECT id
                               FROM smstorelocations))
               UNION ALL
               SELECT b.quantity, b.article, 2 AS type_oper
                   FROM smdocuments a, smspec b
                   WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
                     AND a.doctype IN ('WI', 'IW')
                     AND a.id = b.docid
                     AND a.doctype = b.doctype
                     AND a.docstate > 1
                     AND a.locationto IN (SELECT id
                              FROM smstorelocations))
              UNION ALL
              SELECT b.quantity, b.article, 3 AS type_oper
                  FROM smdocuments a, smspec b
                  WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
                    AND a.doctype IN ('WO', 'IW')
                    AND a.id = b.docid
                    AND a.doctype = b.doctype
                    AND a.docstate > 1
                    AND a.locationfrom IN (SELECT id
                             FROM smstorelocations))
             UNION ALL
             SELECT quantity, article, 0 AS type_oper
                 FROM smgoods
                 WHERE storeloc IN (SELECT id
                            FROM smstorelocations))
            UNION ALL
            SELECT -b.quantity, b.article, 4 AS type_oper
                FROM smdocuments a, smspec b
                WHERE a.createdat > '03.06.2013'
                  AND a.id = b.docid
                  AND a.doctype = b.doctype
                  AND a.docstate > 1
                  AND a.doctype IN ('WI', 'IW', 'CR', 'PO')
                  AND a.locationto IN (SELECT id
                           FROM smstorelocations))
           UNION ALL
           SELECT b.quantity, b.article, 4 AS type_oper
               FROM smdocuments a, smspec b
               WHERE a.createdat > '03.06.2013'
                 AND a.id = b.docid
                 AND a.doctype = b.doctype
                 AND a.docstate > 1
                 AND a.doctype IN ('WO', 'IW', 'CS', 'PE')
                 AND a.locationfrom IN (SELECT id
                          FROM smstorelocations))
          UNION ALL
          SELECT quantity, article, 4 AS type_oper
              FROM smgoods
              WHERE storeloc IN (SELECT id
                         FROM smstorelocations))
    WHERE 1 = 1
      AND article IN (SELECT b.article
               FROM smdocuments a, smspec b
               WHERE a.doctype = 'WI'
                 AND a.docstate > 1
                 AND a.clientindex IN (1416)
                 AND b.docid = a.id
                 AND b.doctype = a.doctype
               GROUP BY b.article)
    GROUP BY article, type_oper
    ORDER BY article, type_oper
 

Optimizer Mode Used:

   ALL_ROWS 

Total Cost:

   140 

Execution Steps:

Step # Step Name 
63  SELECT STATEMENT 
62  SORT [ORDER BY] 
61  HASH [GROUP BY] 
60  FILTER 
47  . VIEW 
46  UNION-ALL 
5  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
4  NESTED LOOPS 
2  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
1  SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN] 
3  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 
10  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
9  NESTED LOOPS 
7  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
6  SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN] 
8  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 
15  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
14  NESTED LOOPS 
12  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
11  SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN] 
13  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 
20  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
19  NESTED LOOPS 
17  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
16  SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN] 
18  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 
21  SUPERMAG.SMGOODS TABLE ACCESS [FULL] 
26  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
25  NESTED LOOPS 
23  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
22  SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN] 
24  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 
44  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
43  NESTED LOOPS 
41  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
40  BITMAP CONVERSION [TO ROWIDS] 
39  BITMAP AND 
29  BITMAP CONVERSION [FROM ROWIDS] 
28  SORT [ORDER BY] 
27  SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN] 
38  BITMAP OR 
31  BITMAP MERGE 
30  SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN] 
33  BITMAP MERGE 
32  SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN] 
35  BITMAP MERGE 
34  SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN] 
37  BITMAP MERGE 
36  SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN] 
42  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 
45  SUPERMAG.SMGOODS TABLE ACCESS [FULL] 
59  FILTER 
58  HASH [GROUP BY] 
57  SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID] 
56  NESTED LOOPS 
54  SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID] 
53  BITMAP CONVERSION [TO ROWIDS] 
52  BITMAP AND 
49  BITMAP CONVERSION [FROM ROWIDS] 
48  SUPERMAG.SMDOCUMENTS_CLIENT INDEX [RANGE SCAN] 
51  BITMAP MERGE 
50  SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN] 
55  SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN] 

Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned 
  1    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 -- 
  2    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 1 0,031 
  3    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 53 -- 
  4    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 15 0,894 
  5    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 53 1,501 
  6    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 -- 
  7    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 1 0,032 
  8    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 -- 
  9    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 1 0,061 
  10    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 1 0,028 
  11    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 -- 
  12    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 31 0,999 
  13    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 -- 
  14    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 222 13,441 
  15    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 7 0,198 
  16    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 -- 
  17    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 2 0,063 
  18    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 -- 
  19    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 2 0,119 
  20    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 1 0,028 
  21    This plan step retrieves all rows from table SMGOODS. 20 61 713 723,199 
  22    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 8 041 -- 
  23    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 14 2 430 78,311 
  24    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 -- 
  25    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 38 17 592 1 065,141 
  26    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 7 0,198 
  27    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 8 041 -- 
  28    This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause. -- -- -- 
  29    This plan step accepts a ROWID from its child node, and converts it to a bitmap representation of the index.    
  30    This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- -- 
  31    This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.    
  32    This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- -- 
  33    This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.    
  34    This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- -- 
  35    This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.    
  36    This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- -- 
  37    This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.    
  38    This plan step accepts bitmap representations of two indexes, performs a bitwise OR operation on them, and returns the result.    
  39    This plan step has no supplementary description information. -- -- -- 
  40    This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.    
  41    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 8 167 5,219 
  42    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 4 -- 
  43    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 10 5 452 324,777 
  44    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 33 0,935 
  45    This plan step retrieves all rows from table SMGOODS. 20 61 713 723,199 
  46    This plan step accepts multiple sets of rows, and combines them into one set including all duplicates.    
  47    This plan step represents the execution plan for the subquery defined by the view . 96 146 710 6 160,674 
  48    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CLIENT. 1 4 282 -- 
  49    This plan step accepts a ROWID from its child node, and converts it to a bitmap representation of the index.    
  50    This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- -- 
  51    This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.    
  52    This plan step has no supplementary description information. -- -- -- 
  53    This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.    
  54    This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 6 2 287 55,835 
  55    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 12 -- 
  56    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 29 27 541 1 344,775 
  57    This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 12 0,293 
  58    This plan step has no supplementary description information.    
  59    This plan step accepts a set of rows from its child node, eliminates some of them, and returns the rest.    
  60    This plan step accepts multiple sets of rows. Rows from the first set are eliminated using the data found in the second through n sets.    
  61    This plan step has no supplementary description information.    
  62    This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause. 140 46 274 1 943,146 
  63    This plan step designates this statement as a SELECT statement. 140 -- --
подскажите куда копать :(
 
31.07.2013 11:30  
cb
1.Зачем дважды считаются остатки с разными type_oper (0,4).
2. Тоже 'IW' с разными type_oper (2,3).
3.Первые 4 юниона можно объединить наподобии.
4.можно отказаться от конечного условия.



SELECT decode (b.doctype,'CS',b.quantity, 'WI',b.quantity, 'IW',b.quantity,'CR', -b.quantity, 0) quantity, b.article,
decode (b.doctype,'CS',1,'CR', 1,'WI',2,'IW', 2,0) type_oper
FROM smdocuments a, smspec b
WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
AND a.doctype in( 'CS','CR','WI', 'IW','WO')
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1

--пункт 4
and exists (SELECT b.article
FROM smdocuments aa, smspec bb
WHERE aa.doctype = 'WI'
AND aa.docstate > 1
AND aa.clientindex =1416
AND bb.docid = aa.id
AND bb.doctype = aa.doctype
and bb.article=b.article)
 
31.07.2013 13:07  
Stels
отчёт заказной
возможности внесения изменений нет
 
 


Опции темы



Часовой пояс GMT +3, время: 05:00.

Все в прочитанное - Календарь - RSS - - Карта - Вверх 👫 Яндекс.Метрика
Форум сделан на основе vBulletin®
Copyright ©2000 - 2016, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.