[ОТВЕТИТЬ]
Опции темы
19.07.2010 16:30  
leonid
Запрос:
Код:
EXPLAIN PLAN FOR 
select
     avg(supermag.smgetclassauxnumprop(d.id,128)) proc,
     l.name loc_name,
     '-' xz,
     rr.realiz,
     sum(decode(f.saleop,8,f.saleq,0)) N_quantity,
     sum(decode(f.saleop,8,f.salesum,0)) N_salesum,
     sum(decode(f.saleop,8,f.primecost,0)) N_zakup_sum,
     sum(decode(f.saleop,8,f.saleq* nvl(supermag.documents.getarticlepriceondate(f.article,h.pricetype,l.id,f.saledate),supermag.documents.getarticleprice(f.article,'',h.pricetype,l.id)),0)) N_rozn_sum,
     sum(decode(f.saleop,9,f.saleq,0,f.saleq,0)) I_quantity,
     sum(decode(f.saleop,9,f.salesum,0,f.salesum,0)) I_salesum,
     sum(decode(f.saleop,9,f.primecost,0,f.primecost,0)) I_zakup_sum,
     sum(decode(f.saleop,9,f.saleq*nvl(supermag.documents.getarticlepriceondate(f.article,h.pricetype,l.id,f.saledate),
      supermag.documents.getarticleprice(f.article,'',h.pricetype,l.id))
      ,0,f.saleq*nvl(supermag.documents.getarticlepriceondate(f.article,h.pricetype,l.id,f.saledate),
      supermag.documents.getarticleprice(f.article,'',h.pricetype,l.id)),0)) I_rozn_sum,
     sum(decode(f.saleop,9,f.saleq,0,f.saleq,0))-sum(decode(f.saleop,8,f.saleq,0)) itog_kol,
     sum(decode(f.saleop,8,f.saleq* nvl(supermag.documents.getarticlepriceondate(f.article,h.pricetype,l.id,f.saledate),supermag.documents.getarticleprice(f.article,'',h.pricetype,l.id)),0))-sum(decode(f.saleop,9,
            f.saleq*nvl(supermag.documents.getarticlepriceondate(f.article,h.pricetype,l.id,f.saledate),
      supermag.documents.getarticleprice(f.article,'',h.pricetype,l.id))
      ,0,f.saleq*nvl(supermag.documents.getarticlepriceondate(f.article,h.pricetype,l.id,f.saledate),
      supermag.documents.getarticleprice(f.article,'',h.pricetype,l.id)),0)) itog_rozn,
      sum(decode(f.saleop,9,f.primecost,0,f.primecost,0))-sum(decode(f.saleop,8,f.primecost,0)) itog_zak
 from
     supermag.ffmaprep f,
     supermag.smcard c,
     supermag.smstorelocations l,
     supermag.sacardclass d,
     (select l.locid,l.pricetype from supermag.smlocprices l where l.flags>1) h
        ,  (
     select  decode(SIGN(f.salelocationfrom),-1,f.salelocationto,f.salelocationfrom) mag,
     sum(decode(f.saletype,'CR',-f.salesum,f.salesum)) realiz from supermag.ffmaprep f,
     supermag.smcard c, supermag.sacardclass cc
where f.rectype=1 and f.saletype in ('CR','CS')
and f.saledate>='01.01.2010'
and f.saledate<='31.05.2010'
and c.article=f.article and c.idclass=cc.id
group by  decode(SIGN(f.salelocationfrom),-1,f.salelocationto,f.salelocationfrom)
     ) rr
     , ( select l.id, cc.tree from supermag.smstorelocations l, supermag.sacardclass cc where l.id<>-1 ) der
 where
     f.rectype=1
     and (f.saleop in (8,9) or f.saleop=0 and f.saleuserop=171)
     and c.article=f.article
     and c.datatype=0
     and l.id=decode(f.salelocationfrom,'-2',f.salelocationto,f.salelocationfrom)
     and l.id=h.locid
     and c.idclass=d.id
     and f.saledate in (to_date('02.01.2010','DD.MM.YYYY'),to_date('03.01.2010','DD.MM.YYYY'),to_date('04.01.2010','DD.MM.YYYY'),to_date('05.01.2010','DD.MM.YYYY'),to_date('06.01.2010','DD.MM.YYYY'),
           to_date('07.01.2010','DD.MM.YYYY'),to_date('08.01.2010','DD.MM.YYYY'),to_date('09.01.2010','DD.MM.YYYY'),to_date('10.01.2010','DD.MM.YYYY'),to_date('11.01.2010','DD.MM.YYYY'),
           to_date('12.01.2010','DD.MM.YYYY'),to_date('13.01.2010','DD.MM.YYYY'),to_date('14.01.2010','DD.MM.YYYY'),to_date('15.01.2010','DD.MM.YYYY'),to_date('16.01.2010','DD.MM.YYYY'),
           to_date('17.01.2010','DD.MM.YYYY'),to_date('18.01.2010','DD.MM.YYYY'),to_date('19.01.2010','DD.MM.YYYY'),to_date('20.01.2010','DD.MM.YYYY'),to_date('21.01.2010','DD.MM.YYYY'),
           to_date('22.01.2010','DD.MM.YYYY'),to_date('23.01.2010','DD.MM.YYYY'),to_date('24.01.2010','DD.MM.YYYY'),to_date('25.01.2010','DD.MM.YYYY'),to_date('26.01.2010','DD.MM.YYYY'),
           to_date('27.01.2010','DD.MM.YYYY'),to_date('28.01.2010','DD.MM.YYYY'),to_date('29.01.2010','DD.MM.YYYY'),to_date('30.01.2010','DD.MM.YYYY'),to_date('31.01.2010','DD.MM.YYYY'),
           to_date('01.02.2010','DD.MM.YYYY'),to_date('02.02.2010','DD.MM.YYYY'),to_date('03.02.2010','DD.MM.YYYY'),to_date('04.02.2010','DD.MM.YYYY'),to_date('05.02.2010','DD.MM.YYYY'),
           to_date('06.02.2010','DD.MM.YYYY'),to_date('07.02.2010','DD.MM.YYYY'),to_date('08.02.2010','DD.MM.YYYY'),to_date('09.02.2010','DD.MM.YYYY'),to_date('10.02.2010','DD.MM.YYYY'),
           to_date('11.02.2010','DD.MM.YYYY'),to_date('12.02.2010','DD.MM.YYYY'),to_date('13.02.2010','DD.MM.YYYY'),to_date('14.02.2010','DD.MM.YYYY'),to_date('15.02.2010','DD.MM.YYYY'),
           to_date('16.02.2010','DD.MM.YYYY'),to_date('17.02.2010','DD.MM.YYYY'),to_date('18.02.2010','DD.MM.YYYY'),to_date('19.02.2010','DD.MM.YYYY'),to_date('20.02.2010','DD.MM.YYYY'),
           to_date('21.02.2010','DD.MM.YYYY'),to_date('22.02.2010','DD.MM.YYYY'),to_date('23.02.2010','DD.MM.YYYY'),to_date('24.02.2010','DD.MM.YYYY'),to_date('25.02.2010','DD.MM.YYYY'),
           to_date('26.02.2010','DD.MM.YYYY'),to_date('27.02.2010','DD.MM.YYYY'),to_date('28.02.2010','DD.MM.YYYY'),to_date('01.03.2010','DD.MM.YYYY'),to_date('02.03.2010','DD.MM.YYYY'),
           to_date('03.03.2010','DD.MM.YYYY'),to_date('04.03.2010','DD.MM.YYYY'),to_date('05.03.2010','DD.MM.YYYY'),to_date('06.03.2010','DD.MM.YYYY'),to_date('07.03.2010','DD.MM.YYYY'),
           to_date('08.03.2010','DD.MM.YYYY'),to_date('09.03.2010','DD.MM.YYYY'),to_date('10.03.2010','DD.MM.YYYY'),to_date('11.03.2010','DD.MM.YYYY'),to_date('12.03.2010','DD.MM.YYYY'),
           to_date('13.03.2010','DD.MM.YYYY'),to_date('14.03.2010','DD.MM.YYYY'),to_date('15.03.2010','DD.MM.YYYY'),to_date('16.03.2010','DD.MM.YYYY'),to_date('17.03.2010','DD.MM.YYYY'),
           to_date('18.03.2010','DD.MM.YYYY'),to_date('19.03.2010','DD.MM.YYYY'),to_date('20.03.2010','DD.MM.YYYY'),to_date('21.03.2010','DD.MM.YYYY'),to_date('22.03.2010','DD.MM.YYYY'),
           to_date('23.03.2010','DD.MM.YYYY'),to_date('24.03.2010','DD.MM.YYYY'),to_date('25.03.2010','DD.MM.YYYY'),to_date('26.03.2010','DD.MM.YYYY'),to_date('27.03.2010','DD.MM.YYYY'),
           to_date('28.03.2010','DD.MM.YYYY'),to_date('29.03.2010','DD.MM.YYYY'),to_date('30.03.2010','DD.MM.YYYY'),to_date('31.03.2010','DD.MM.YYYY'),to_date('01.04.2010','DD.MM.YYYY'),
           to_date('02.04.2010','DD.MM.YYYY'),to_date('03.04.2010','DD.MM.YYYY'),to_date('04.04.2010','DD.MM.YYYY'),to_date('05.04.2010','DD.MM.YYYY'),to_date('06.04.2010','DD.MM.YYYY'),
           to_date('07.04.2010','DD.MM.YYYY'),to_date('08.04.2010','DD.MM.YYYY'),to_date('09.04.2010','DD.MM.YYYY'),to_date('10.04.2010','DD.MM.YYYY'),to_date('11.04.2010','DD.MM.YYYY'),
           to_date('12.04.2010','DD.MM.YYYY'),to_date('13.04.2010','DD.MM.YYYY'),to_date('14.04.2010','DD.MM.YYYY'),to_date('15.04.2010','DD.MM.YYYY'),to_date('16.04.2010','DD.MM.YYYY'),
           to_date('17.04.2010','DD.MM.YYYY'),to_date('18.04.2010','DD.MM.YYYY'),to_date('19.04.2010','DD.MM.YYYY'),to_date('20.04.2010','DD.MM.YYYY'),to_date('21.04.2010','DD.MM.YYYY'),
           to_date('22.04.2010','DD.MM.YYYY'),to_date('23.04.2010','DD.MM.YYYY'),to_date('24.04.2010','DD.MM.YYYY'),to_date('25.04.2010','DD.MM.YYYY'),to_date('26.04.2010','DD.MM.YYYY'),
           to_date('27.04.2010','DD.MM.YYYY'),to_date('28.04.2010','DD.MM.YYYY'),to_date('29.04.2010','DD.MM.YYYY'),to_date('30.04.2010','DD.MM.YYYY'),to_date('01.05.2010','DD.MM.YYYY'),
           to_date('02.05.2010','DD.MM.YYYY'),to_date('03.05.2010','DD.MM.YYYY'),to_date('04.05.2010','DD.MM.YYYY'),to_date('05.05.2010','DD.MM.YYYY'),to_date('06.05.2010','DD.MM.YYYY'),
           to_date('07.05.2010','DD.MM.YYYY'),to_date('08.05.2010','DD.MM.YYYY'),to_date('09.05.2010','DD.MM.YYYY'),to_date('10.05.2010','DD.MM.YYYY'),to_date('11.05.2010','DD.MM.YYYY'),
           to_date('12.05.2010','DD.MM.YYYY'),to_date('13.05.2010','DD.MM.YYYY'),to_date('14.05.2010','DD.MM.YYYY'),to_date('15.05.2010','DD.MM.YYYY'),to_date('16.05.2010','DD.MM.YYYY'),
           to_date('17.05.2010','DD.MM.YYYY'),to_date('18.05.2010','DD.MM.YYYY'),to_date('19.05.2010','DD.MM.YYYY'),to_date('20.05.2010','DD.MM.YYYY'),to_date('21.05.2010','DD.MM.YYYY'),
           to_date('22.05.2010','DD.MM.YYYY'),to_date('23.05.2010','DD.MM.YYYY'),to_date('24.05.2010','DD.MM.YYYY'),to_date('25.05.2010','DD.MM.YYYY'),to_date('26.05.2010','DD.MM.YYYY'),
           to_date('27.05.2010','DD.MM.YYYY'),to_date('28.05.2010','DD.MM.YYYY'),to_date('29.05.2010','DD.MM.YYYY'),to_date('30.05.2010','DD.MM.YYYY'),to_date('31.05.2010','DD.MM.YYYY'))
     and l.id in (select l.id from supermag.smstorelocations l where  l.idclass>0)
     and rr.mag(+)=decode(f.salelocationfrom,'-2',f.salelocationto,f.salelocationfrom)
group by l.name  , rr.realiz
order by l.name,  N_Quantity;
Его план (на базе ЦО, 10G)
Код:
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 937395665                                                                                                                  
                                                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    
----------------------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                                |                      |     1 |   187 |   151K (58)| 00:00:04 |       |       |    
|   1 |  SORT ORDER BY                                  |                      |     1 |   187 |   151K (58)| 00:00:04 |       |       |    
|   2 |   HASH GROUP BY                                 |                      |     1 |   187 |   151K (58)| 00:00:04 |       |       |    
|   3 |    MERGE JOIN CARTESIAN                         |                      |     1 |   187 |   151K (58)| 00:00:04 |       |       |    
|*  4 |     HASH JOIN OUTER                             |                      |     1 |   187 |   151K (58)| 00:00:04 |       |       |    
|   5 |      MERGE JOIN CARTESIAN                       |                      |     1 |   161 | 77912  (59)| 00:00:02 |       |       |    

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       TABLE ACCESS BY INDEX ROWID               | SMLOCPRICES          |     1 |     8 |     1   (0)| 00:00:01 |       |       |    
|   7 |        NESTED LOOPS                             |                      |     1 |   157 | 77910  (59)| 00:00:02 |       |       |    
|   8 |         NESTED LOOPS                            |                      |     1 |   149 | 77909  (59)| 00:00:02 |       |       |    
|   9 |          NESTED LOOPS                           |                      |     1 |   142 | 77908  (59)| 00:00:02 |       |       |    
|  10 |           NESTED LOOPS                          |                      |     1 |   119 | 77907  (59)| 00:00:02 |       |       |    
|  11 |            NESTED LOOPS                         |                      |     1 |   115 | 77906  (59)| 00:00:02 |       |       |    
|  12 |             INLIST ITERATOR                     |                      |       |       |            |          |       |       |    
|  13 |              PARTITION RANGE ITERATOR           |                      |     1 |   101 | 77905  (59)| 00:00:02 |KEY(I) |KEY(I) |    
|  14 |               PARTITION LIST ALL                |                      |     1 |   101 | 77905  (59)| 00:00:02 |     1 |     4 |    
|* 15 |                TABLE ACCESS BY LOCAL INDEX ROWID| FFMAPREP             |     1 |   101 | 77905  (59)| 00:00:02 |KEY(I) |KEY(I) |    
|  16 |                 BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |          |       |       |    

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
|* 17 |                  BITMAP INDEX SINGLE VALUE      | FFMAPREP_SALEDATE    |       |       |            |          |KEY(I) |KEY(I) |    
|* 18 |             TABLE ACCESS BY INDEX ROWID         | SMCARD               |     1 |    14 |     1   (0)| 00:00:01 |       |       |    
|* 19 |              INDEX UNIQUE SCAN                  | SMCARD_PK            |     1 |       |     1   (0)| 00:00:01 |       |       |    
|* 20 |            INDEX UNIQUE SCAN                    | SACCARDCLASS_PK      |     1 |     4 |     1   (0)| 00:00:01 |       |       |    
|  21 |           TABLE ACCESS BY INDEX ROWID           | SMSTORELOCATIONS     |     1 |    23 |     1   (0)| 00:00:01 |       |       |    
|* 22 |            INDEX UNIQUE SCAN                    | SMCSTORELOCATIONS_PK |     1 |       |     1   (0)| 00:00:01 |       |       |    
|* 23 |          TABLE ACCESS BY INDEX ROWID            | SMSTORELOCATIONS     |     1 |     7 |     1   (0)| 00:00:01 |       |       |    
|* 24 |           INDEX UNIQUE SCAN                     | SMCSTORELOCATIONS_PK |     1 |       |     1   (0)| 00:00:01 |       |       |    
|* 25 |         INDEX RANGE SCAN                        | SMCLOCPRICES_PK      |     2 |       |     1   (0)| 00:00:01 |       |       |    
|  26 |       BUFFER SORT                               |                      |    57 |   228 | 77910  (59)| 00:00:02 |       |       |    
|* 27 |        INDEX FULL SCAN                          | SMCSTORELOCATIONS_PK |    57 |   228 |     1   (0)| 00:00:01 |       |       |    

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
|  28 |      VIEW                                       |                      |     1 |    26 | 73627  (57)| 00:00:02 |       |       |    
|  29 |       HASH GROUP BY                             |                      |     1 |    63 | 73627  (57)| 00:00:02 |       |       |    
|  30 |        NESTED LOOPS                             |                      |     1 |    63 | 73626  (57)| 00:00:02 |       |       |    
|  31 |         PARTITION RANGE ITERATOR                |                      |     1 |    52 | 73625  (57)| 00:00:02 |    14 |    15 |    
|  32 |          PARTITION LIST INLIST                  |                      |     1 |    52 | 73625  (57)| 00:00:02 |KEY(I) |KEY(I) |    
|* 33 |           TABLE ACCESS BY LOCAL INDEX ROWID     | FFMAPREP             |     1 |    52 | 73625  (57)| 00:00:02 |   KEY |   KEY |    
|  34 |            BITMAP CONVERSION TO ROWIDS          |                      |       |       |            |          |       |       |    
|* 35 |             BITMAP INDEX RANGE SCAN             | FFMAPREP_SALEDATE    |       |       |            |          |   KEY |   KEY |    
|* 36 |         INDEX UNIQUE SCAN                       | SMCARD_PK            |     1 |       |     1   (0)| 00:00:01 |       |       |    
|  37 |     BUFFER SORT                                 |                      |  1941 |       |   151K (58)| 00:00:04 |       |       |    
|  38 |      INDEX FULL SCAN                            | SACCARDCLASS_PK      |  1941 |       |     5  (60)| 00:00:01 |       |       |    

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------    
                                                                                                                                            
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
                                                                                                                                            
   4 - access("RR"."MAG"(+)=DECODE(TO_CHAR("F"."SALELOCATIONFROM"),'-2',"F"."SALELOCATIONTO","F"."SALELOCATIONFROM"))                       
   6 - filter("L"."FLAGS">1)                                                                                                                
  15 - filter("F"."RECTYPE"=1 AND (("F"."SALEOP"=8 OR "F"."SALEOP"=9) OR "F"."SALEOP"=0 AND "F"."SALEUSEROP"=171))                          
  17 - access("F"."SALEDATE"=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-03               
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR           
              "F"."SALEDATE"=TO_DATE(' 2010-01-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-06 00:00:00',    

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-09 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-12 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-15 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-18 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-21 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
              "F"."SALEDATE"=TO_DATE(' 2010-01-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-24 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-27 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-30 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-02 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-05 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-08 00:00:00',    

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-11 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-14 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-17 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-20 00:00:00',    
              'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR                      
              "F"."SALEDATE"=TO_DATE(' 2010-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."SALEDATE"=TO_DATE(' 2010-02-23 00:00:00', )  
  18 - filter("C"."DATATYPE"=0)                                                                                                             

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
  19 - access("C"."ARTICLE"="F"."ARTICLE")                                                                                                  
  20 - access("C"."IDCLASS"="D"."ID")                                                                                                       
  22 - access("L"."ID"=DECODE(TO_CHAR("F"."SALELOCATIONFROM"),'-2',"F"."SALELOCATIONTO","F"."SALELOCATIONFROM"))                            
       filter("L"."ID"<>(-2) AND "L"."ID"<>(-3))                                                                                            
  23 - filter("L"."IDCLASS">0)                                                                                                              
  24 - access("L"."ID"="L"."ID")                                                                                                            
       filter("L"."ID"<>(-2) AND "L"."ID"<>(-3))                                                                                            
  25 - access("L"."ID"="L"."LOCID")                                                                                                         
       filter("L"."LOCID"<>(-2) AND "L"."LOCID"<>(-3))                                                                                      
  27 - filter("L"."ID"<>(-1))                                                                                                               
  33 - filter("F"."RECTYPE"=1)                                                                                                              

PLAN_TABLE_OUTPUT                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------
  35 - access("F"."SALEDATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."SALEDATE"<=TO_DATE('                       
              2010-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                                                              
  36 - access("C"."ARTICLE"="F"."ARTICLE")                                                                                                  

102 rows selected.
Зависает на 10-ке с ожиданиями:
latch: cache buffers chains
cursor: pin S wait on X
cursor: pin S


на 9-ке
latch free

Попробовал на нескольких базах, зависает и все.
Попробуйте на ваших базах.

Думаю, что дело в запросе, а не в настройках базы.
Но в чем именно?
 
20.07.2010 09:35  
leonid
Вопрос снимается, зависает из за таких выражений:
avg(supermag.smgetclassauxnumprop(d.id,128)) proc
 
 
Опции темы



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

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