This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jul 1, 2009 4:15 AM by user551641 Go to original post RSS
  • 15. Re: Cramer query strange behaviour
    user551641 Newbie
    Currently Being Moderated
    Query.
    Only inline views involved no real views.


    select :fnm o_StreetName, :hsnm o_HouseNumber, :hsext o_HouseNumberExt,
    :pstcd o_DutchPostCode, :city o_CityName, :lat o_gpsLatitude,
    :longt o_gpsLongitude, :shnam o_ShelfName, :shpos o_ShelfPosition, :shid
    o_ShelfId, :shtype o_ShelfType, :shtat o_ShelfStatus,
    enum_service.name as O_ENUMBER, qethb.O_SERVICEId
    as O_SERVICEId, qethb.O_SERVICEOPERATORId as O_SERVICEOPERATORId,
    :cid o_CardId, :cnam o_CardName, :ctyp o_CardType, :cver
    o_CardVersion, :cbra o_CardBrand, :cstat o_CardStatus, :cpos
    o_CardPosition,:pphy o_PhysicalPortId, :pnm o_PortName, :ptype o_PortType,
    :pstat o_PortStatus, :ppin o_Pin
    from
    service enum_service, serviceobject enum_so, (select
    distinct(c.circuitid), c.circuit2endport,
    qSvlan.O_SERVICEId as O_SERVICEId ,
    qSvlan.O_SERVICEOPERATORId as O_SERVICEOPERATORId from
    circuitcircuit cc, circuit
    c , ( select
    svlan.circuitid,
    ckv.srv_instance_id as O_SERVICEId, sub.name
    as O_SERVICEOPERATORId from circuit cvlan,
    CIRCUIT_KPN_VLAN ckv, circuit svlan,
    circuitcircuit cc, circuit wba, node sodomain,
    subscriber sub, service transpinst, serviceobject sotop,
    topologycircuit topct, topconnection topc,
    toprelobjects tr, (select cvlanport.portid
    from port
    cvlanport, port ebport, port portuni, porttype_m pt,
    port dslport, port localloopport
    where portuni.portid
    = :portid and
    portuni.port2porttype = pt.porttypeid
    and upper(pt.class) = 'COPPER'
    and localloopport.parentport2port =
    portuni.portid and
    dslport.parentport2port = localloopport.portid
    and ebport.parentport2port = dslport.portid
    and cvlanport.parentport2port =
    ebport.portid union all select
    cvlanport.portid from port
    cvlanport, port ebport, port portuni, porttype_m pt
    where portuni.portid = :portid
    and portuni.port2porttype =
    pt.porttypeid and upper(pt.class)
    = 'FIBRE' and
    ebport.parentport2port = portuni.portid
    and cvlanport.parentport2port = ebport.portid ) cvlanport
    where cvlan.circuit2endport = cvlanport.portid
    and cvlan.circuit2endnode = :nodeid
    and cvlan.circuit2circuittype = 150010000
    and ckv.circuitid = cvlan.circuitid
    and cvlan.circuitid =
    cc.uses2circuit and cc.usedby2circuit
    = wba.circuitid and wba.circuit2circuittype
    = 1900000000 and wba.circuit2startnode
    = sodomain.nodeid and
    sodomain.node2nodedef = 1900000005 and
    wba.circuitid = topct.topologycircuit2circuit
    and topct.topologycircuit2topology =
    sotop.serviceobject2object (+) and 23 =
    sotop.serviceobject2dimobject (+) and
    sotop.serviceobject2service = transpinst.serviceid (+)
    and 1900000003 = transpinst.service2servicetype (+)
    and transpinst.service2subscriber = sub.subscriberid (+)
    and topct.topologycircuit2topology =
    tr.toprelobjects2topology and
    tr.toprelobjectsid = topc.topconnection2toprelobjects
    and 3 = topc.topconnection2dimobject
    and topc.topconnection2object = svlan.circuitid
    and 150010000= svlan.circuit2circuittype ) qSvlan where
    c.circuitid = cc.uses2circuit and c.circuit2circuittype =
    150000064 and c.circuit2startnode = :nodeid
    connect by prior cc.uses2circuit = cc.usedby2circuit start with
    cc.usedby2circuit = qSvlan.circuitid ) qethb where
    qethb.circuit2endport = enum_so.serviceobject2object (+) and 1900000004
    = enum_service.service2servicetype (+) and 4 =
    enum_so.serviceobject2dimobject (+) and
    enum_so.serviceobject2service = enum_service.serviceid (+)
  • 16. Re: Cramer query strange behaviour
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    You paste twice the same explain plan. And for keeping readability, enclose it by {code} before and after

    Nicolas.
  • 17. Re: Cramer query strange behaviour
    user551641 Newbie
    Currently Being Moderated
    Sorry I forgot the formatting stuff.
  • 18. Re: Cramer query strange behaviour
    user551641 Newbie
    Currently Being Moderated
    Sorry this is the slow one.
     (lowercase, curly brackets, no spaces)
    
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          3  NESTED LOOPS OUTER (cr=18272725 pr=1085127 pw=1341800 time=841007912 us)
          3   NESTED LOOPS OUTER (cr=18272716 pr=1085127 pw=1341800 time=841008013 us)
          3    VIEW  (cr=18272704 pr=1085127 pw=1341800 time=841007666 us)
          3     HASH UNIQUE (cr=18272704 pr=1085127 pw=1341800 time=841007654 us)
        603      FILTER  (cr=18272704 pr=1085127 pw=1341800 time=840434488 us)
      64722       CONNECT BY WITH FILTERING (cr=18272704 pr=1085127 pw=1341800 time=841066862 us)
        201        NESTED LOOPS  (cr=2114 pr=0 pw=0 time=11669 us)
        201         NESTED LOOPS  (cr=1710 pr=0 pw=0 time=13442 us)
        201          NESTED LOOPS  (cr=1305 pr=0 pw=0 time=9393 us)
        201           NESTED LOOPS OUTER (cr=901 pr=0 pw=0 time=5954 us)
        201            NESTED LOOPS  (cr=497 pr=0 pw=0 time=3913 us)
        201             NESTED LOOPS  (cr=92 pr=0 pw=0 time=1055 us)
          3              NESTED LOOPS OUTER (cr=78 pr=0 pw=0 time=897 us)
          3               NESTED LOOPS OUTER (cr=70 pr=0 pw=0 time=820 us)
          3                NESTED LOOPS  (cr=62 pr=0 pw=0 time=721 us)
          3                 NESTED LOOPS  (cr=54 pr=0 pw=0 time=631 us)
          3                  NESTED LOOPS  (cr=46 pr=0 pw=0 time=576 us)
          3                   NESTED LOOPS  (cr=38 pr=0 pw=0 time=516 us)
          3                    NESTED LOOPS  (cr=33 pr=0 pw=0 time=465 us)
          3                     NESTED LOOPS  (cr=25 pr=0 pw=0 time=392 us)
          3                      VIEW  (cr=17 pr=0 pw=0 time=285 us)
          3                       UNION-ALL  (cr=17 pr=0 pw=0 time=281 us)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=5 pr=0 pw=0 time=91 us)
          1                         NESTED LOOPS  (cr=5 pr=0 pw=0 time=76 us)
          0                          NESTED LOOPS  (cr=5 pr=0 pw=0 time=71 us)
          0                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=67 us)
          0                            NESTED LOOPS  (cr=5 pr=0 pw=0 time=63 us)
          0                             NESTED LOOPS  (cr=5 pr=0 pw=0 time=60 us)
          1                              TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=30 us)
          1                               INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=18 us)(object id 192317)
          0                              TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=28 us)
          1                               INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=13 us)(object id 192325)
          0                             TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                              INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          3                        TABLE ACCESS BY INDEX ROWID PORT (cr=12 pr=0 pw=0 time=161 us)
          5                         NESTED LOOPS  (cr=10 pr=0 pw=0 time=453 us)
          1                          NESTED LOOPS  (cr=8 pr=0 pw=0 time=96 us)
          1                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=45 us)
          1                            TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=22 us)
          1                             INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=12 us)(object id 192317)
          1                            TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=22 us)
          1                             INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=11 us)(object id 192325)
          1                           TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=48 us)
          1                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=31 us)(object id 192314)
          3                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=32 us)(object id 192314)
          3                      TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=96 us)
          3                       INDEX RANGE SCAN CCT_PORT_DEFINED_BY_FK_I (cr=5 pr=0 pw=0 time=38 us)(object id 191351)
          3                     TABLE ACCESS BY INDEX ROWID CIRCUIT_KPN_VLAN (cr=8 pr=0 pw=0 time=61 us)
          3                      INDEX UNIQUE SCAN SYS_C0089772 (cr=5 pr=0 pw=0 time=38 us)(object id 191378)
          3                    INDEX RANGE SCAN CC_UK (cr=5 pr=0 pw=0 time=47 us)(object id 191358)
          3                   TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=55 us)
          3                    INDEX UNIQUE SCAN CCT_PK (cr=5 pr=0 pw=0 time=35 us)(object id 191354)
          3                  TABLE ACCESS BY INDEX ROWID NODE (cr=8 pr=0 pw=0 time=47 us)
          3                   INDEX UNIQUE SCAN NODE_PK (cr=5 pr=0 pw=0 time=26 us)(object id 192097)
          3                 TABLE ACCESS BY INDEX ROWID TOPOLOGYCIRCUIT (cr=8 pr=0 pw=0 time=82 us)
          3                  INDEX RANGE SCAN TPCT_PK (cr=5 pr=0 pw=0 time=43 us)(object id 192921)
          3                TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=8 pr=0 pw=0 time=77 us)
          3                 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=5 pr=0 pw=0 time=44 us)(object id 192668)
          3               TABLE ACCESS BY INDEX ROWID SERVICE (cr=8 pr=0 pw=0 time=64 us)
          3                INDEX UNIQUE SCAN SERV_PK (cr=5 pr=0 pw=0 time=30 us)(object id 192658)
        201              TABLE ACCESS BY INDEX ROWID TOPRELOBJECTS (cr=14 pr=0 pw=0 time=474 us)
        201               INDEX RANGE SCAN TOPR_TOP_FK_I (cr=6 pr=0 pw=0 time=51 us)(object id 192927)
        201             TABLE ACCESS BY INDEX ROWID TOPCONNECTION (cr=405 pr=0 pw=0 time=2274 us)
        201              INDEX RANGE SCAN TOPC_TOPR_FK_I (cr=204 pr=0 pw=0 time=1320 us)(object id 192908)
        201            TABLE ACCESS BY INDEX ROWID SUBSCRIBER (cr=404 pr=0 pw=0 time=1429 us)
        201             INDEX UNIQUE SCAN SUBS_PK (cr=203 pr=0 pw=0 time=777 us)(object id 192773)
        201           TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=404 pr=0 pw=0 time=2003 us)
        201            INDEX UNIQUE SCAN CCT_PK (cr=203 pr=0 pw=0 time=878 us)(object id 191354)
        201          TABLE ACCESS BY INDEX ROWID CIRCUITCIRCUIT (cr=405 pr=0 pw=0 time=3099 us)
        201           INDEX RANGE SCAN CC_PK (cr=204 pr=0 pw=0 time=1301 us)(object id 191357)
        201         TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=404 pr=0 pw=0 time=1650 us)
        201          INDEX UNIQUE SCAN CCT_PK (cr=203 pr=0 pw=0 time=733 us)(object id 191354)
      21507        NESTED LOOPS  (cr=44458 pr=0 pw=0 time=173553 us)
      21507         MERGE JOIN CARTESIAN (cr=1442 pr=0 pw=0 time=22989 us)
        201          NESTED LOOPS  (cr=1305 pr=0 pw=0 time=8255 us)
        201           NESTED LOOPS OUTER (cr=901 pr=0 pw=0 time=6034 us)
        201            NESTED LOOPS  (cr=497 pr=0 pw=0 time=4014 us)
        201             NESTED LOOPS  (cr=92 pr=0 pw=0 time=788 us)
          3              NESTED LOOPS OUTER (cr=78 pr=0 pw=0 time=646 us)
          3               NESTED LOOPS OUTER (cr=70 pr=0 pw=0 time=588 us)
          3                NESTED LOOPS  (cr=62 pr=0 pw=0 time=522 us)
          3                 NESTED LOOPS  (cr=54 pr=0 pw=0 time=466 us)
          3                  NESTED LOOPS  (cr=46 pr=0 pw=0 time=418 us)
          3                   NESTED LOOPS  (cr=38 pr=0 pw=0 time=368 us)
          3                    NESTED LOOPS  (cr=33 pr=0 pw=0 time=330 us)
          3                     NESTED LOOPS  (cr=25 pr=0 pw=0 time=284 us)
          3                      VIEW  (cr=17 pr=0 pw=0 time=216 us)
          3                       UNION-ALL  (cr=17 pr=0 pw=0 time=211 us)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=5 pr=0 pw=0 time=67 us)
          1                         NESTED LOOPS  (cr=5 pr=0 pw=0 time=56 us)
          0                          NESTED LOOPS  (cr=5 pr=0 pw=0 time=53 us)
          0                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=50 us)
          0                            NESTED LOOPS  (cr=5 pr=0 pw=0 time=48 us)
          0                             NESTED LOOPS  (cr=5 pr=0 pw=0 time=47 us)
          1                              TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=21 us)
          1                               INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=13 us)(object id 192317)
          0                              TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=23 us)
          1                               INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=11 us)(object id 192325)
          0                             TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                              INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          3                        TABLE ACCESS BY INDEX ROWID PORT (cr=12 pr=0 pw=0 time=126 us)
          5                         NESTED LOOPS  (cr=10 pr=0 pw=0 time=306 us)
          1                          NESTED LOOPS  (cr=8 pr=0 pw=0 time=89 us)
          1                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=48 us)
          1                            TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=23 us)
          1                             INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=13 us)(object id 192317)
          1                            TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=24 us)
          1                             INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=9 us)(object id 192325)
          1                           TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=38 us)
          1                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=15 us)(object id 192314)
          3                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=13 us)(object id 192314)
          3                      TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=71 us)
          3                       INDEX RANGE SCAN CCT_PORT_DEFINED_BY_FK_I (cr=5 pr=0 pw=0 time=40 us)(object id 191351)
          3                     TABLE ACCESS BY INDEX ROWID CIRCUIT_KPN_VLAN (cr=8 pr=0 pw=0 time=41 us)
          3                      INDEX UNIQUE SCAN SYS_C0089772 (cr=5 pr=0 pw=0 time=23 us)(object id 191378)
          3                    INDEX RANGE SCAN CC_UK (cr=5 pr=0 pw=0 time=37 us)(object id 191358)
          3                   TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=47 us)
          3                    INDEX UNIQUE SCAN CCT_PK (cr=5 pr=0 pw=0 time=24 us)(object id 191354)
          3                  TABLE ACCESS BY INDEX ROWID NODE (cr=8 pr=0 pw=0 time=40 us)
          3                   INDEX UNIQUE SCAN NODE_PK (cr=5 pr=0 pw=0 time=20 us)(object id 192097)
          3                 TABLE ACCESS BY INDEX ROWID TOPOLOGYCIRCUIT (cr=8 pr=0 pw=0 time=56 us)
          3                  INDEX RANGE SCAN TPCT_PK (cr=5 pr=0 pw=0 time=36 us)(object id 192921)
          3                TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=8 pr=0 pw=0 time=49 us)
          3                 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=5 pr=0 pw=0 time=41 us)(object id 192668)
          3               TABLE ACCESS BY INDEX ROWID SERVICE (cr=8 pr=0 pw=0 time=45 us)
          3                INDEX UNIQUE SCAN SERV_PK (cr=5 pr=0 pw=0 time=24 us)(object id 192658)
        201              TABLE ACCESS BY INDEX ROWID TOPRELOBJECTS (cr=14 pr=0 pw=0 time=1429 us)
        201               INDEX RANGE SCAN TOPR_TOP_FK_I (cr=6 pr=0 pw=0 time=621 us)(object id 192927)
        201             TABLE ACCESS BY INDEX ROWID TOPCONNECTION (cr=405 pr=0 pw=0 time=2893 us)
        201              INDEX RANGE SCAN TOPC_TOPR_FK_I (cr=204 pr=0 pw=0 time=1730 us)(object id 192908)
        201            TABLE ACCESS BY INDEX ROWID SUBSCRIBER (cr=404 pr=0 pw=0 time=1601 us)
        201             INDEX UNIQUE SCAN SUBS_PK (cr=203 pr=0 pw=0 time=910 us)(object id 192773)
        201           TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=404 pr=0 pw=0 time=1969 us)
        201            INDEX UNIQUE SCAN CCT_PK (cr=203 pr=0 pw=0 time=872 us)(object id 191354)
      21507          BUFFER SORT (cr=137 pr=0 pw=0 time=1306 us)
        107           NESTED LOOPS  (cr=137 pr=0 pw=0 time=1678 us)
         75            BUFFER SORT (cr=0 pr=0 pw=0 time=74 us)
         75             CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=79 us)
        107            TABLE ACCESS BY INDEX ROWID CIRCUITCIRCUIT (cr=137 pr=0 pw=0 time=653 us)
        107             INDEX RANGE SCAN CC_PK (cr=77 pr=0 pw=0 time=377 us)(object id 191357)
      21507         TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=43016 pr=0 pw=0 time=141550 us)
      21507          INDEX UNIQUE SCAN CCT_PK (cr=21509 pr=0 pw=0 time=67716 us)(object id 191354)
    9112536        NESTED LOOPS  (cr=18226132 pr=18090 pw=90 time=91208084 us)
    9112536         MERGE JOIN CARTESIAN (cr=1058 pr=18090 pw=90 time=18307758 us)
        201          NESTED LOOPS  (cr=909 pr=0 pw=0 time=41062 us)
        201           NESTED LOOPS  (cr=505 pr=0 pw=0 time=30232 us)
        201            NESTED LOOPS  (cr=100 pr=0 pw=0 time=5985 us)
          3             NESTED LOOPS OUTER (cr=86 pr=0 pw=0 time=1830 us)
          3              NESTED LOOPS OUTER (cr=78 pr=0 pw=0 time=1703 us)
          3               NESTED LOOPS OUTER (cr=70 pr=0 pw=0 time=1560 us)
          3                NESTED LOOPS  (cr=62 pr=0 pw=0 time=1364 us)
          3                 NESTED LOOPS  (cr=54 pr=0 pw=0 time=1178 us)
          3                  NESTED LOOPS  (cr=46 pr=0 pw=0 time=1048 us)
          3                   NESTED LOOPS  (cr=38 pr=0 pw=0 time=928 us)
          3                    NESTED LOOPS  (cr=33 pr=0 pw=0 time=834 us)
          3                     NESTED LOOPS  (cr=25 pr=0 pw=0 time=684 us)
          3                      VIEW  (cr=17 pr=0 pw=0 time=425 us)
          3                       UNION-ALL  (cr=17 pr=0 pw=0 time=414 us)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=5 pr=0 pw=0 time=99 us)
          1                         NESTED LOOPS  (cr=5 pr=0 pw=0 time=83 us)
          0                          NESTED LOOPS  (cr=5 pr=0 pw=0 time=78 us)
          0                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=77 us)
          0                            NESTED LOOPS  (cr=5 pr=0 pw=0 time=74 us)
          0                             NESTED LOOPS  (cr=5 pr=0 pw=0 time=73 us)
          1                              TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=34 us)
          1                               INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=22 us)(object id 192317)
          0                              TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=36 us)
          1                               INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=15 us)(object id 192325)
          0                             TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                              INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          3                        TABLE ACCESS BY INDEX ROWID PORT (cr=12 pr=0 pw=0 time=273 us)
          5                         NESTED LOOPS  (cr=10 pr=0 pw=0 time=436 us)
          1                          NESTED LOOPS  (cr=8 pr=0 pw=0 time=144 us)
          1                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=69 us)
          1                            TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=29 us)
          1                             INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=14 us)(object id 192317)
          1                            TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=33 us)
          1                             INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=15 us)(object id 192325)
          1                           TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=68 us)
          1                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=28 us)(object id 192314)
          3                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=19 us)(object id 192314)
          3                      TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=266 us)
          3                       INDEX RANGE SCAN CCT_PORT_DEFINED_BY_FK_I (cr=5 pr=0 pw=0 time=95 us)(object id 191351)
          3                     TABLE ACCESS BY INDEX ROWID CIRCUIT_KPN_VLAN (cr=8 pr=0 pw=0 time=134 us)
          3                      INDEX UNIQUE SCAN SYS_C0089772 (cr=5 pr=0 pw=0 time=88 us)(object id 191378)
          3                    INDEX RANGE SCAN CC_UK (cr=5 pr=0 pw=0 time=98 us)(object id 191358)
          3                   TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=101 us)
          3                    INDEX UNIQUE SCAN CCT_PK (cr=5 pr=0 pw=0 time=58 us)(object id 191354)
          3                  TABLE ACCESS BY INDEX ROWID NODE (cr=8 pr=0 pw=0 time=116 us)
          3                   INDEX UNIQUE SCAN NODE_PK (cr=5 pr=0 pw=0 time=70 us)(object id 192097)
          3                 TABLE ACCESS BY INDEX ROWID TOPOLOGYCIRCUIT (cr=8 pr=0 pw=0 time=186 us)
          3                  INDEX RANGE SCAN TPCT_PK (cr=5 pr=0 pw=0 time=64 us)(object id 192921)
          3                TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=8 pr=0 pw=0 time=119 us)
          3                 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=5 pr=0 pw=0 time=64 us)(object id 192668)
          3               TABLE ACCESS BY INDEX ROWID SERVICE (cr=8 pr=0 pw=0 time=113 us)
          3                INDEX UNIQUE SCAN SERV_PK (cr=5 pr=0 pw=0 time=66 us)(object id 192658)
          3              TABLE ACCESS BY INDEX ROWID SUBSCRIBER (cr=8 pr=0 pw=0 time=95 us)
          3               INDEX UNIQUE SCAN SUBS_PK (cr=5 pr=0 pw=0 time=60 us)(object id 192773)
        201             TABLE ACCESS BY INDEX ROWID TOPRELOBJECTS (cr=14 pr=0 pw=0 time=4278 us)
        201              INDEX RANGE SCAN TOPR_TOP_FK_I (cr=6 pr=0 pw=0 time=2050 us)(object id 192927)
        201            TABLE ACCESS BY INDEX ROWID TOPCONNECTION (cr=405 pr=0 pw=0 time=22185 us)
        201             INDEX RANGE SCAN TOPC_TOPR_FK_I (cr=204 pr=0 pw=0 time=8222 us)(object id 192908)
        201           TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=404 pr=0 pw=0 time=8087 us)
        201            INDEX UNIQUE SCAN CCT_PK (cr=203 pr=0 pw=0 time=4308 us)(object id 191354)
    9112536          BUFFER SORT (cr=149 pr=18090 pw=90 time=1151263 us)
      45336           INDEX FAST FULL SCAN CC_UK (cr=149 pr=0 pw=0 time=70 us)(object id 191358)
    9112536         TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=18225074 pr=0 pw=0 time=68090494 us)
    9112536          INDEX UNIQUE SCAN CCT_PK (cr=9112538 pr=0 pw=0 time=31488868 us)(object id 191354)
          3    TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=12 pr=0 pw=0 time=269 us)
          3     INDEX RANGE SCAN SEROBJ_OBJ_I (cr=9 pr=0 pw=0 time=110 us)(object id 192668)
          3   TABLE ACCESS BY INDEX ROWID SERVICE (cr=9 pr=0 pw=0 time=87 us)
          3    INDEX UNIQUE SCAN SERV_PK (cr=6 pr=0 pw=0 time=56 us)(object id 192658
    (lowercase, curly brackets, no spaces)
  • 19. Re: Cramer query strange behaviour
    user551641 Newbie
    Currently Being Moderated
    And now the quick one formated. Thanks to Jonathan.
     (lowercase, curly brackets, no spaces) 
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          3  NESTED LOOPS OUTER (cr=47994 pr=0 pw=0 time=630386 us)
          3   NESTED LOOPS OUTER (cr=47985 pr=0 pw=0 time=630441 us)
          3    VIEW  (cr=47973 pr=0 pw=0 time=630288 us)
          3     HASH UNIQUE (cr=47973 pr=0 pw=0 time=630281 us)
        603      FILTER  (cr=47973 pr=0 pw=0 time=493675 us)
      64722       CONNECT BY WITH FILTERING (cr=47973 pr=0 pw=0 time=607510 us)
        201        NESTED LOOPS  (cr=2114 pr=0 pw=0 time=11907 us)
        201         NESTED LOOPS  (cr=1710 pr=0 pw=0 time=13680 us)
        201          NESTED LOOPS  (cr=1305 pr=0 pw=0 time=9424 us)
        201           NESTED LOOPS OUTER (cr=901 pr=0 pw=0 time=5985 us)
        201            NESTED LOOPS  (cr=497 pr=0 pw=0 time=4140 us)
        201             NESTED LOOPS  (cr=92 pr=0 pw=0 time=1080 us)
          3              NESTED LOOPS OUTER (cr=78 pr=0 pw=0 time=929 us)
          3               NESTED LOOPS OUTER (cr=70 pr=0 pw=0 time=848 us)
          3                NESTED LOOPS  (cr=62 pr=0 pw=0 time=743 us)
          3                 NESTED LOOPS  (cr=54 pr=0 pw=0 time=644 us)
          3                  NESTED LOOPS  (cr=46 pr=0 pw=0 time=588 us)
          3                   NESTED LOOPS  (cr=38 pr=0 pw=0 time=526 us)
          3                    NESTED LOOPS  (cr=33 pr=0 pw=0 time=477 us)
          3                     NESTED LOOPS  (cr=25 pr=0 pw=0 time=401 us)
          3                      VIEW  (cr=17 pr=0 pw=0 time=296 us)
          3                       UNION-ALL  (cr=17 pr=0 pw=0 time=289 us)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=5 pr=0 pw=0 time=96 us)
          1                         NESTED LOOPS  (cr=5 pr=0 pw=0 time=81 us)
          0                          NESTED LOOPS  (cr=5 pr=0 pw=0 time=75 us)
          0                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=72 us)
          0                            NESTED LOOPS  (cr=5 pr=0 pw=0 time=68 us)
          0                             NESTED LOOPS  (cr=5 pr=0 pw=0 time=66 us)
          1                              TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=37 us)
          1                               INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=21 us)(object id 192317)
          0                              TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=25 us)
          1                               INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=12 us)(object id 192325)
          0                             TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                              INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          3                        TABLE ACCESS BY INDEX ROWID PORT (cr=12 pr=0 pw=0 time=167 us)
          5                         NESTED LOOPS  (cr=10 pr=0 pw=0 time=469 us)
          1                          NESTED LOOPS  (cr=8 pr=0 pw=0 time=101 us)
          1                           NESTED LOOPS  (cr=5 pr=0 pw=0 time=48 us)
          1                            TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=23 us)
          1                             INDEX UNIQUE SCAN PORT_PK (cr=2 pr=0 pw=0 time=13 us)(object id 192317)
          1                            TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=2 pr=0 pw=0 time=22 us)
          1                             INDEX UNIQUE SCAN PTYPE_PK (cr=1 pr=0 pw=0 time=10 us)(object id 192325)
          1                           TABLE ACCESS BY INDEX ROWID PORT (cr=3 pr=0 pw=0 time=49 us)
          1                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=31 us)(object id 192314)
          3                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=2 pr=0 pw=0 time=31 us)(object id 192314)
          3                      TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=93 us)
          3                       INDEX RANGE SCAN CCT_PORT_DEFINED_BY_FK_I (cr=5 pr=0 pw=0 time=40 us)(object id 191351)
          3                     TABLE ACCESS BY INDEX ROWID CIRCUIT_KPN_VLAN (cr=8 pr=0 pw=0 time=66 us)
          3                      INDEX UNIQUE SCAN SYS_C0089772 (cr=5 pr=0 pw=0 time=39 us)(object id 191378)
          3                    INDEX RANGE SCAN CC_UK (cr=5 pr=0 pw=0 time=45 us)(object id 191358)
          3                   TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=8 pr=0 pw=0 time=56 us)
          3                    INDEX UNIQUE SCAN CCT_PK (cr=5 pr=0 pw=0 time=38 us)(object id 191354)
          3                  TABLE ACCESS BY INDEX ROWID NODE (cr=8 pr=0 pw=0 time=50 us)
          3                   INDEX UNIQUE SCAN NODE_PK (cr=5 pr=0 pw=0 time=30 us)(object id 192097)
          3                 TABLE ACCESS BY INDEX ROWID TOPOLOGYCIRCUIT (cr=8 pr=0 pw=0 time=86 us)
          3                  INDEX RANGE SCAN TPCT_PK (cr=5 pr=0 pw=0 time=41 us)(object id 192921)
          3                TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=8 pr=0 pw=0 time=87 us)
          3                 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=5 pr=0 pw=0 time=50 us)(object id 192668)
          3               TABLE ACCESS BY INDEX ROWID SERVICE (cr=8 pr=0 pw=0 time=65 us)
          3                INDEX UNIQUE SCAN SERV_PK (cr=5 pr=0 pw=0 time=35 us)(object id 192658)
        201              TABLE ACCESS BY INDEX ROWID TOPRELOBJECTS (cr=14 pr=0 pw=0 time=477 us)
        201               INDEX RANGE SCAN TOPR_TOP_FK_I (cr=6 pr=0 pw=0 time=249 us)(object id 192927)
        201             TABLE ACCESS BY INDEX ROWID TOPCONNECTION (cr=405 pr=0 pw=0 time=2230 us)
        201              INDEX RANGE SCAN TOPC_TOPR_FK_I (cr=204 pr=0 pw=0 time=1283 us)(object id 192908)
        201            TABLE ACCESS BY INDEX ROWID SUBSCRIBER (cr=404 pr=0 pw=0 time=1394 us)
        201             INDEX UNIQUE SCAN SUBS_PK (cr=203 pr=0 pw=0 time=790 us)(object id 192773)
        201           TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=404 pr=0 pw=0 time=2061 us)
        201            INDEX UNIQUE SCAN CCT_PK (cr=203 pr=0 pw=0 time=902 us)(object id 191354)
        201          TABLE ACCESS BY INDEX ROWID CIRCUITCIRCUIT (cr=405 pr=0 pw=0 time=3108 us)
        201           INDEX RANGE SCAN CC_PK (cr=204 pr=0 pw=0 time=1323 us)(object id 191357)
        201         TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=404 pr=0 pw=0 time=1655 us)
        201          INDEX UNIQUE SCAN CCT_PK (cr=203 pr=0 pw=0 time=749 us)(object id 191354)
      21507        NESTED LOOPS  (cr=45859 pr=0 pw=0 time=181059 us)
      21507         MERGE JOIN CARTESIAN (cr=2843 pr=0 pw=0 time=30490 us)
        402          NESTED LOOPS  (cr=2598 pr=0 pw=0 time=14859 us)
        402           NESTED LOOPS OUTER (cr=1791 pr=0 pw=0 time=11223 us)
        402            NESTED LOOPS  (cr=984 pr=0 pw=0 time=7189 us)
        402             NESTED LOOPS  (cr=175 pr=0 pw=0 time=1535 us)
          6              NESTED LOOPS OUTER (cr=148 pr=0 pw=0 time=1925 us)
          6               NESTED LOOPS OUTER (cr=133 pr=0 pw=0 time=1746 us)
          6                NESTED LOOPS  (cr=118 pr=0 pw=0 time=1512 us)
          6                 NESTED LOOPS  (cr=103 pr=0 pw=0 time=1307 us)
          6                  NESTED LOOPS  (cr=88 pr=0 pw=0 time=1146 us)
          6                   NESTED LOOPS  (cr=73 pr=0 pw=0 time=994 us)
          6                    NESTED LOOPS  (cr=64 pr=0 pw=0 time=860 us)
          6                     NESTED LOOPS  (cr=49 pr=0 pw=0 time=699 us)
          6                      VIEW  (cr=34 pr=0 pw=0 time=472 us)
          6                       UNION-ALL  (cr=34 pr=0 pw=0 time=462 us)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=10 pr=0 pw=0 time=129 us)
          2                         NESTED LOOPS  (cr=10 pr=0 pw=0 time=111 us)
          0                          NESTED LOOPS  (cr=10 pr=0 pw=0 time=104 us)
          0                           NESTED LOOPS  (cr=10 pr=0 pw=0 time=102 us)
          0                            NESTED LOOPS  (cr=10 pr=0 pw=0 time=97 us)
          0                             NESTED LOOPS  (cr=10 pr=0 pw=0 time=96 us)
          2                              TABLE ACCESS BY INDEX ROWID PORT (cr=6 pr=0 pw=0 time=46 us)
          2                               INDEX UNIQUE SCAN PORT_PK (cr=4 pr=0 pw=0 time=29 us)(object id 192317)
          0                              TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=4 pr=0 pw=0 time=46 us)
          2                               INDEX UNIQUE SCAN PTYPE_PK (cr=2 pr=0 pw=0 time=23 us)(object id 192325)
          0                             TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                              INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          6                        TABLE ACCESS BY INDEX ROWID PORT (cr=24 pr=0 pw=0 time=277 us)
         10                         NESTED LOOPS  (cr=20 pr=0 pw=0 time=533 us)
          2                          NESTED LOOPS  (cr=16 pr=0 pw=0 time=140 us)
          2                           NESTED LOOPS  (cr=10 pr=0 pw=0 time=74 us)
          2                            TABLE ACCESS BY INDEX ROWID PORT (cr=6 pr=0 pw=0 time=34 us)
          2                             INDEX UNIQUE SCAN PORT_PK (cr=4 pr=0 pw=0 time=20 us)(object id 192317)
          2                            TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=4 pr=0 pw=0 time=35 us)
          2                             INDEX UNIQUE SCAN PTYPE_PK (cr=2 pr=0 pw=0 time=17 us)(object id 192325)
          2                           TABLE ACCESS BY INDEX ROWID PORT (cr=6 pr=0 pw=0 time=61 us)
          2                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=4 pr=0 pw=0 time=30 us)(object id 192314)
          6                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=4 pr=0 pw=0 time=24 us)(object id 192314)
          6                      TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=15 pr=0 pw=0 time=189 us)
          6                       INDEX RANGE SCAN CCT_PORT_DEFINED_BY_FK_I (cr=9 pr=0 pw=0 time=103 us)(object id 191351)
          6                     TABLE ACCESS BY INDEX ROWID CIRCUIT_KPN_VLAN (cr=15 pr=0 pw=0 time=117 us)
          6                      INDEX UNIQUE SCAN SYS_C0089772 (cr=9 pr=0 pw=0 time=71 us)(object id 191378)
          6                    INDEX RANGE SCAN CC_UK (cr=9 pr=0 pw=0 time=102 us)(object id 191358)
          6                   TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=15 pr=0 pw=0 time=110 us)
          6                    INDEX UNIQUE SCAN CCT_PK (cr=9 pr=0 pw=0 time=63 us)(object id 191354)
          6                  TABLE ACCESS BY INDEX ROWID NODE (cr=15 pr=0 pw=0 time=118 us)
          6                   INDEX UNIQUE SCAN NODE_PK (cr=9 pr=0 pw=0 time=62 us)(object id 192097)
          6                 TABLE ACCESS BY INDEX ROWID TOPOLOGYCIRCUIT (cr=15 pr=0 pw=0 time=165 us)
          6                  INDEX RANGE SCAN TPCT_PK (cr=9 pr=0 pw=0 time=101 us)(object id 192921)
          6                TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=15 pr=0 pw=0 time=122 us)
          6                 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=9 pr=0 pw=0 time=106 us)(object id 192668)
          6               TABLE ACCESS BY INDEX ROWID SERVICE (cr=15 pr=0 pw=0 time=116 us)
          6                INDEX UNIQUE SCAN SERV_PK (cr=9 pr=0 pw=0 time=67 us)(object id 192658)
        402              TABLE ACCESS BY INDEX ROWID TOPRELOBJECTS (cr=27 pr=0 pw=0 time=912 us)
        402               INDEX RANGE SCAN TOPR_TOP_FK_I (cr=11 pr=0 pw=0 time=71 us)(object id 192927)
        402             TABLE ACCESS BY INDEX ROWID TOPCONNECTION (cr=809 pr=0 pw=0 time=5214 us)
        402              INDEX RANGE SCAN TOPC_TOPR_FK_I (cr=407 pr=0 pw=0 time=3106 us)(object id 192908)
        402            TABLE ACCESS BY INDEX ROWID SUBSCRIBER (cr=807 pr=0 pw=0 time=3107 us)
        402             INDEX UNIQUE SCAN SUBS_PK (cr=405 pr=0 pw=0 time=1754 us)(object id 192773)
        402           TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=807 pr=0 pw=0 time=3808 us)
        402            INDEX UNIQUE SCAN CCT_PK (cr=405 pr=0 pw=0 time=1729 us)(object id 191354)
      21507          BUFFER SORT (cr=245 pr=0 pw=0 time=2254 us)
        107           NESTED LOOPS  (cr=245 pr=0 pw=0 time=2505 us)
        182            BUFFER SORT (cr=0 pr=0 pw=0 time=219 us)
        182             CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=191 us)
        107            TABLE ACCESS BY INDEX ROWID CIRCUITCIRCUIT (cr=245 pr=0 pw=0 time=1257 us)
        107             INDEX RANGE SCAN CC_PK (cr=185 pr=0 pw=0 time=876 us)(object id 191357)
      21507         TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=43016 pr=0 pw=0 time=156031 us)
      21507          INDEX UNIQUE SCAN CCT_PK (cr=21509 pr=0 pw=0 time=75839 us)(object id 191354)
          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0         MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us)
          0          NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0           NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0            NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0             NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
          0              NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
          0               NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
          0                NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                 NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                  NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                      VIEW  (cr=0 pr=0 pw=0 time=0 us)
          0                       UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                         NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                          NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                           NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                            NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                             NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                              TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                               INDEX UNIQUE SCAN PORT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192317)
          0                              TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=0 pr=0 pw=0 time=0 us)
          0                               INDEX UNIQUE SCAN PTYPE_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192325)
          0                             TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                              INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                        TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                         NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                          NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                           NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
          0                            TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX UNIQUE SCAN PORT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192317)
          0                            TABLE ACCESS BY INDEX ROWID PORTTYPE_M (cr=0 pr=0 pw=0 time=0 us)
          0                             INDEX UNIQUE SCAN PTYPE_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192325)
          0                           TABLE ACCESS BY INDEX ROWID PORT (cr=0 pr=0 pw=0 time=0 us)
          0                            INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                          INDEX RANGE SCAN PORT_PORT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192314)
          0                      TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=0 pr=0 pw=0 time=0 us)
          0                       INDEX RANGE SCAN CCT_PORT_DEFINED_BY_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 191351)
          0                     TABLE ACCESS BY INDEX ROWID CIRCUIT_KPN_VLAN (cr=0 pr=0 pw=0 time=0 us)
          0                      INDEX UNIQUE SCAN SYS_C0089772 (cr=0 pr=0 pw=0 time=0 us)(object id 191378)
          0                    INDEX RANGE SCAN CC_UK (cr=0 pr=0 pw=0 time=0 us)(object id 191358)
          0                   TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=0 pr=0 pw=0 time=0 us)
          0                    INDEX UNIQUE SCAN CCT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 191354)
          0                  TABLE ACCESS BY INDEX ROWID NODE (cr=0 pr=0 pw=0 time=0 us)
          0                   INDEX UNIQUE SCAN NODE_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192097)
          0                 TABLE ACCESS BY INDEX ROWID TOPOLOGYCIRCUIT (cr=0 pr=0 pw=0 time=0 us)
          0                  INDEX RANGE SCAN TPCT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192921)
          0                TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=0 pr=0 pw=0 time=0 us)
          0                 INDEX RANGE SCAN SEROBJ_OBJ_I (cr=0 pr=0 pw=0 time=0 us)(object id 192668)
          0               TABLE ACCESS BY INDEX ROWID SERVICE (cr=0 pr=0 pw=0 time=0 us)
          0                INDEX UNIQUE SCAN SERV_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192658)
          0              TABLE ACCESS BY INDEX ROWID SUBSCRIBER (cr=0 pr=0 pw=0 time=0 us)
          0               INDEX UNIQUE SCAN SUBS_PK (cr=0 pr=0 pw=0 time=0 us)(object id 192773)
          0             TABLE ACCESS BY INDEX ROWID TOPRELOBJECTS (cr=0 pr=0 pw=0 time=0 us)
          0              INDEX RANGE SCAN TOPR_TOP_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192927)
          0            TABLE ACCESS BY INDEX ROWID TOPCONNECTION (cr=0 pr=0 pw=0 time=0 us)
          0             INDEX RANGE SCAN TOPC_TOPR_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 192908)
          0           TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=0 pr=0 pw=0 time=0 us)
          0            INDEX UNIQUE SCAN CCT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 191354)
          0          BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
          0           INDEX FAST FULL SCAN CC_UK (cr=0 pr=0 pw=0 time=0 us)(object id 191358)
          0         TABLE ACCESS BY INDEX ROWID CIRCUIT (cr=0 pr=0 pw=0 time=0 us)
          0          INDEX UNIQUE SCAN CCT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 191354)
          3    TABLE ACCESS BY INDEX ROWID SERVICEOBJECT (cr=12 pr=0 pw=0 time=116 us)
          3     INDEX RANGE SCAN SEROBJ_OBJ_I (cr=9 pr=0 pw=0 time=69 us)(object id 192668)
          3   TABLE ACCESS BY INDEX ROWID SERVICE (cr=9 pr=0 pw=0 time=60 us)
          3    INDEX UNIQUE SCAN SERV_PK (cr=6 pr=0 pw=0 time=38 us)(object id 192658
    
     
    (lowercase, curly brackets, no spaces)
  • 20. Re: Cramer query strange behaviour
    706417 Explorer
    Currently Being Moderated
    +"PLEASE, PLEASE, PLEASE, BEGGING ON MY KNEES"+

    I just knew you were into all that sort of Dutch/German weird naughtiness! You sly old rascal, you!

    Post a few photos!


    Regards - Don Lewis
  • 21. Re: Cramer query strange behaviour
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Your query is a "connect by" query on a complex join. Here, for comparison, is an example of a very simple "connect by" and its 10g plan.
    select 
         count(*) X
    from 
         kill_cpu
    connect by
         n > prior n
    start with 
         n = 1
    /
    
    ---------------------------------------------------------------------------
    | Id  | Operation                  | Name              | Rows  | Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                   |     1 | 00:00:01 |
    |   1 |  SORT AGGREGATE            |                   |     1 |          |
    |*  2 |   CONNECT BY WITH FILTERING|                   |       |          |
    |*  3 |    INDEX UNIQUE SCAN       | SYS_IOT_TOP_98360 |     1 | 00:00:01 |
    |   4 |    NESTED LOOPS            |                   |       |          |
    |   5 |     BUFFER SORT            |                   |       |          |
    |   6 |      CONNECT BY PUMP       |                   |       |          |
    |*  7 |     INDEX RANGE SCAN       | SYS_IOT_TOP_98360 |     1 | 00:00:01 |
    |   8 |    INDEX FAST FULL SCAN    | SYS_IOT_TOP_98360 |    23 | 00:00:01 |
    ---------------------------------------------------------------------------
    Notice how the table (in this case the index top of an IOT) appears three times.
    Line 8 (the third appearance) is, I believe, a "failsafe" line in case the "connect by pump" has to spill to disc. I've not seen it used myself but I believe its use is triggered based on the current run-time resource usage.

    If you look at your query, you will see two things:
    <ul>
    a) it follows the same "three copies" pattern
    b) the bit which changes performance dramatically is the fail-safe bit
    c) in the fast query the second part shows roughly double the rows compared to the slow query for most of its lines, with the third part in the slow query getting (roughly) the balance.
    </ul>

    I think that at run-time, Oracle may be switching strategies on your query in mid-execution, and that happens to be a big mistake as far as you're concerned.

    I don't know WHY this is happening, but it's one of the reasons why a query can get a change in performance with the same execution plan - and particularly relevant for "complex connect by queries".

    Oracle played around a lot with "connect by" queries in 10gR2, and a couple of parameters and hints appeared to control what was going on. If you add the hint /*+ no_connect_by_filtering */ to your query it should change to the equivalent of the following (which may make it impossible for your problem to appear - but I really don't know if that's the case):
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                   |     1 |    13 |   827   (1)| 00:00:10 |
    |   1 |  SORT AGGREGATE               |                   |     1 |    13 |            |          |
    |*  2 |   CONNECT BY WITHOUT FILTERING|                   |       |       |            |          |
    |*  3 |    INDEX UNIQUE SCAN          | SYS_IOT_TOP_98360 |     1 |    39 |     1   (0)| 00:00:01 |
    |   4 |    INDEX FULL SCAN            | SYS_IOT_TOP_98360 |    23 |   299 |    26   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "For every expert there is an equal and opposite expert."
    Arthur C. Clarke
  • 22. Re: Cramer query strange behaviour
    user551641 Newbie
    Currently Being Moderated
    Thanks.

    Increasing the pga_aggregate_target from 50M to 1G helps to get rid of the phenomenon.

    regards HansP
  • 23. Re: Cramer query strange behaviour
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Just a quick follow-up - you might be interested in this note from Christian Antognini's blog, which seems to be highly relevant: http://antognini.ch/2008/06/operation-connect-by-with-filtering/

    Regards
    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 24. Re: Cramer query strange behaviour
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Jonathan Lewis wrote:
    I've not seen it used myself but I believe its use is triggered based on the current run-time resource usage.
    Jonathan,

    so you mean to say that this a decision not of the optimizer at parse time but of the runtime engine at execution time based on the current "PGA - global memory bound" setting?

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 25. Re: Cramer query strange behaviour
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user551641 wrote:
    Query.
    Only inline views involved no real views.
    In passing, looking at the execution plan and a (formatted) version of the query:
    SELECT :fnm                 o_StreetName               ,
           :hsnm                o_HouseNumber              ,
           :hsext               o_HouseNumberExt           ,
           :pstcd               o_DutchPostCode            ,
           :city                o_CityName                 ,
           :lat                 o_gpsLatitude              ,
           :longt               o_gpsLongitude             ,
           :shnam               o_ShelfName                ,
           :shpos               o_ShelfPosition            ,
           :shid                o_ShelfId                  ,
           :shtype              o_ShelfType                ,
           :shtat               o_ShelfStatus              ,
           enum_service.name         AS O_ENUMBER          ,
           qethb.O_SERVICEId         AS O_SERVICEId        ,
           qethb.O_SERVICEOPERATORId AS O_SERVICEOPERATORId,
           :cid                         o_CardId           ,
           :cnam                        o_CardName         ,
           :ctyp                        o_CardType         ,
           :cver                        o_CardVersion      ,
           :cbra                        o_CardBrand        ,
           :cstat                       o_CardStatus       ,
           :cpos                        o_CardPosition     ,
           :pphy                        o_PhysicalPortId   ,
           :pnm                         o_PortName         ,
           :ptype                       o_PortType         ,
           :pstat                       o_PortStatus       ,
           :ppin                        o_Pin
    FROM   service enum_service                               ,
           serviceobject enum_so                              ,
           (SELECT DISTINCT(c.circuitid)                      ,
                            c.circuit2endport                 ,
                            qSvlan.O_SERVICEId         AS O_SERVICEId ,
                            qSvlan.O_SERVICEOPERATORId AS O_SERVICEOPERATORId
           FROM             circuitcircuit cc                         ,
                            circuit c                                 ,
                            ( SELECT svlan.circuitid                  ,
                                    ckv.srv_instance_id AS O_SERVICEId,
                                    sub.name            AS O_SERVICEOPERATORId
                            FROM    circuit cvlan        ,
                                    CIRCUIT_KPN_VLAN ckv ,
                                    circuit svlan        ,
                                    circuitcircuit cc    ,
                                    circuit wba          ,
                                    node sodomain        ,
                                    subscriber sub       ,
                                    service transpinst   ,
                                    serviceobject sotop  ,
                                    topologycircuit topct,
                                    topconnection topc   ,
                                    toprelobjects tr     ,
                                    (SELECT cvlanport.portid
                                    FROM    port cvlanport,
                                            port ebport   ,
                                            port portuni  ,
                                            porttype_m pt ,
                                            port dslport  ,
                                            port localloopport
                                    WHERE   portuni.portid                = :portid
                                        AND portuni.port2porttype         = pt.porttypeid
                                        AND UPPER(pt.class)               = 'COPPER'
                                        AND localloopport.parentport2port = portuni.portid
                                        AND dslport.parentport2port       = localloopport.portid
                                        AND ebport.parentport2port        = dslport.portid
                                        AND cvlanport.parentport2port     = ebport.portid
                                    
                                    UNION ALL
                                    
                                    SELECT cvlanport.portid
                                    FROM   port cvlanport,
                                           port ebport   ,
                                           port portuni  ,
                                           porttype_m pt
                                    WHERE  portuni.portid            = :portid
                                       AND portuni.port2porttype     = pt.porttypeid
                                       AND UPPER(pt.class)           = 'FIBRE'
                                       AND ebport.parentport2port    = portuni.portid
                                       AND cvlanport.parentport2port = ebport.portid
                                    ) cvlanport
                            WHERE   cvlan.circuit2endport          = cvlanport.portid
                                AND cvlan.circuit2endnode          = :nodeid
                                AND cvlan.circuit2circuittype      = 150010000
                                AND ckv.circuitid                  = cvlan.circuitid
                                AND cvlan.circuitid                = cc.uses2circuit
                                AND cc.usedby2circuit              = wba.circuitid
                                AND wba.circuit2circuittype        = 1900000000
                                AND wba.circuit2startnode          = sodomain.nodeid
                                AND sodomain.node2nodedef          = 1900000005
                                AND wba.circuitid                  = topct.topologycircuit2circuit
                                AND topct.topologycircuit2topology = sotop.serviceobject2object (+)
                                AND 23                             = sotop.serviceobject2dimobject (+)
                                AND sotop.serviceobject2service    = transpinst.serviceid (+)
                                AND 1900000003                     = transpinst.service2servicetype (+)
                                AND transpinst.service2subscriber  = sub.subscriberid (+)
                                AND topct.topologycircuit2topology = tr.toprelobjects2topology
                                AND tr.toprelobjectsid             = topc.topconnection2toprelobjects
                                AND 3                              = topc.topconnection2dimobject
                                AND topc.topconnection2object      = svlan.circuitid
                                AND 150010000                      = svlan.circuit2circuittype
                            ) qSvlan
           WHERE            c.circuitid                      = cc.uses2circuit
                        AND c.circuit2circuittype            = 150000064
                        AND c.circuit2startnode              = :nodeid
                            CONNECT BY prior cc.uses2circuit = cc.usedby2circuit
                            START WITH cc.usedby2circuit     = qSvlan.circuitid
           ) qethb
    WHERE  qethb.circuit2endport         = enum_so.serviceobject2object (+)
       AND 1900000004                    = enum_service.service2servicetype (+)
       AND 4                             = enum_so.serviceobject2dimobject (+)
       AND enum_so.serviceobject2service = enum_service.serviceid (+)
    Besides your issue that sometimes the execution is performing differently since the runtime engine switches to a different path of the execution plan, it seems to be suspicious that the inline view "qethb" uses a SELECT DISTINCT (and suggests the incorrect usage of DISTINCT(col), ... since DISTINCT always refers to all projected expressions and not only that in parenthesis).

    Do you get duplicates from the subquery "qethb" without the DISTINCT, and if yes, why?

    The execution plan shows a cartesian product for the hierarchical parts (both, section 2 and 3) of the plan, and your CONNECT BY expression doesn't mention the qSvlan inline view (whereas the START WITH does, which looks like that the result from the inline view determines the root nodes of the hierarchy).

    The cartesian product seems to be the main reason why the slow execution is slow, since it generates 201 times 45336 = 9112536 rows... So is this cartesian product necessary, and how could the CONNECT BY expression be rewritten to avoid it?

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 26. Re: Cramer query strange behaviour
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Randolf Geist wrote:

    so you mean to say that this a decision not of the optimizer at parse time but of the runtime engine at execution time based on the current "PGA - global memory bound" setting?
    Yes,

    In a way it's a hybrid of two different aspects of how the optimizer can build a plan;

    On one hand you have the "conditional plan" (see for example my blog: http://jonathanlewis.wordpress.com/2007/01/09/conditional-sql which shows a plan containing two optional paths so that the run-time engine can pick a part of the plan that is most relevant to the incoming values for bind variables).

    On the other hand you have issues like sorts or hash joins - where a query can change performance because variable demands for memory by other sessions may mean that the same query, with the same amount of data, sometimes is allowed enough memory to complete in memory and other times is rationed and forced to spill to disk.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "For every expert there is an equal and opposite expert."
    Arthur C. Clarke
  • 27. Re: Cramer query strange behaviour
    user551641 Newbie
    Currently Being Moderated
    Hello Randolf,

    I cannot answer your questions since I am on vacation right now.
    Furthermore Cramer is as far as I know more or less of the shelf.
    There may be improvements possible but I doubt whether I can have the vendor change its queries.

    regards HansP
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points