1 2 Previous Next 27 Replies Latest reply: Jul 1, 2009 6:15 AM by user551641 RSS

    Cramer query strange behaviour

    user551641
      I was ask to look at a performance issue on a database used for Cramer.

      A stored procedure is executed which executes a couple of queries and returns some data.

      Like :
      PL/SQL procedure successfully completed.

      Elapsed: 00:00:01.33

      After a couple of tries the Elapsed time goes up to 20 minutes.
      After cancelling the next execution is quick again.

      I did traced both cases.
      The strange thing is that execution plan is in both cases the same except for the numer of rows returned by the steps.
      The explain plan is 228 lines long so I will not copy in the complete plan but just the last lines.

      Fast plan:
      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

      Slow plan:
      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)

      v$sql_plan shows only 1 plan.

      Does anyone have clue?
        • 1. Re: Cramer query strange behaviour
          Nicolas.Gasparotto
          Cramer ? What is this ?
          After cancelling the next execution is quick again
          What it means ?
          in both cases
          What are the "both cases", I don't understand where the explain plans are coming from, same query ?

          OS ? Oracle version ?
          Does the Oracle stats up-to-date ?

          Nicolas.
          • 2. Re: Cramer query strange behaviour
            SanjayRs
            When you killed your query halfway after 20 minutes the data was already in the buffers, next time when you ran the query it did not have to fetch the data so it was fast.

            SS
            • 3. Re: Cramer query strange behaviour
              user551641
              When you killed your query halfway after 20 minutes the data was already in the buffers, next time when you ran the query it did not have to fetch the data
              so it was fast.
              That doe not make sense because I had already run the package seconds before the attempt that took so long that I cancelled.
              Furthermore it does not explain the difference in returned rows for the last steps of the eplain plan.
              Cramer ? What is this ?
              Cramer is a standard application
              After cancelling the next execution is quick again
              What it means ? in both cases
              What are the "both cases", I don't understand where the explain plans are coming from, same query ?
              Both cases means the quick and the slow execution.
              The explain plans come from tracefiles. Generated by the traces of both cases.
              They are from the same query.
              OS ? Oracle version ?
              OS = Sun Solaris 5.10, Oracle version = 10.2.0.2
              Does the Oracle stats up-to-date ?
              Well I did not check as they did not change between executions.
              Even if the statistics do not reflect the real situation the elapsed time should be the same (perhaps wrong) all the time.
              • 4. Re: Cramer query strange behaviour
                riedelme
                I'd like to agree with Sanjay and credit the fast execution time to caching, but you said it ran fast, then slow, and then fast again. Unless data got swapped out between runs that doesn't make sense :(

                Can the data set be changing between runs? If so are the objects (tables, indexes, etc) re-analyzed?

                Why are the plan statistics you posted different?

                You'll need more information :(. Can you perform a 10046 trace on the multiple runs to get more information?
                • 5. Re: Cramer query strange behaviour
                  user551641
                  I'd like to agree with Sanjay and credit the fast execution time to caching, but you said it ran fast, then slow, and then fast again. Unless data got swapped >>out between runs that doesn't make sense :(
                  Can the data set be changing between runs? If so are the objects (tables, indexes, etc) re-analyzed?
                  Why are the plan statistics you posted different?
                  You'll need more information :(. Can you perform a 10046 trace on the multiple runs to get more information?
                  I discovered that increasing the pga_aggregate_target makes the phenomenon to disapper.

                  But everything about caching just makes no sense since the row source operations process different numbers of rows.
                  Caching does not influence that. Furthermore I had just had a quick run seconds before a slow run.
                  This is reproduced on a test environment with almost no changes.

                  It looks like a bug.
                  • 6. Re: Cramer query strange behaviour
                    26741
                    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)
                    The scan on CCT_PK and the subsequent Row Gets from CIRCUIT are where the diference in timing on the executions is significant. Obviously, the "slow execution" is with a different value for a particular parameter -- the one that queries the CIRCULT Table.
                    • 7. Re: Cramer query strange behaviour
                      user551641
                      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)
                      The scan on CCT_PK and the subsequent Row Gets from CIRCUIT are where the diference in timing on the executions is significant. Obviously, the "slow >>execution" is with a different value for a particular parameter -- the one that queries the CIRCULT Table.
                      Hello Hemant,

                      Well that may seem obvious but that is really not the case.
                      Same bind variables, same rows returned shortly after each other and no changes to the mentioned table.
                      I exported the tables needed for the query to a seperate test database without any users working on it except me.
                      Behaviour is the same, fasts a couple of times, slow 1 or more times, fast a couple of times.
                      The fast times the row sources you quoted have 0 rows and the slow times the same row source has 900000 rows.

                      regards Hans-Peter
                      • 8. Re: Cramer query strange behaviour
                        Jonathan Lewis
                        user551641 wrote:
                        The explain plan is 228 lines long so I will not copy in the complete plan but just the last lines.
                        Fast plan:
                        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
                        
                        Slow plan:
                        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)
                        
                        v$sql_plan shows only 1 plan.
                        Bytes are cheap - post both plans, and use the code tags to post them in fixed format.

                        The Buffer Sort looks like the second child of a Cartesian Merge Join with an input of 201 rows from the first child - but it doesn't connect to the last 4 lines, so we need to see more of the plan. And after we've seen the plan we still might need to see the query and and relevant view definitions.

                        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

                        To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                        {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                        fixed format
                        .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                        • 9. Re: Cramer query strange behaviour
                          26741
                          Same bind variables, same rows returned shortly after each other and no changes to the mentioned table.
                          The above statement implies that the data is the same and query is the same and the results are the same.


                          The fast times the row sources you quoted have 0 rows and the slow times the same row source has 900000 rows.
                          The above statement asserts that the result set for the fast is 0 rows and the result set for the slow is 900000 rows. There is a difference between 0 and 900000 , wouldn't you say ?
                          Behaviour is the same, fasts a couple of times, slow 1 or more times, fast a couple of times.
                          Do you mean that the 0 rows result set if fast a couple of times and slow 1 or more times and fast a couple of times ? OR do you mean to say that the fast is 0 rows and the slow is 900000 rows ?
                          • 10. Re: Cramer query strange behaviour
                            user551641
                            I mean to say that both have the same result set.

                            The difference of 9000000 rows is the result of a row source operations in the explain plan.
                            The fast has 0 rows for the same row source operation.

                            The behaviour alternates some times 0 rows for the same operation followed by 9000000 rows for the same row source operation.

                            It reproduces when exporting/importing the tables needed in some other database.
                            In my test database there are no interfering transactions and the behaviour is the same there.

                            regards HansP
                            • 11. Re: Cramer query strange behaviour
                              609621
                              Dear Niciloas,

                              Can you please give me a hint that how to make sure that Oracle stats are up-to-date ?.. for ex.. in my env, top consumers in EM still shows the old sql statements that doesn't exist (old sql's ran before 2 weeks).. stale stats..

                              How can I make sure that the stats are up to date ?

                              DB : 11.1.0.6.0

                              OS : Sun 5.10

                              Please guide

                              Kai
                              • 12. Re: Cramer query strange behaviour
                                sybrand_b
                                KaiS,

                                Please don't hijack the question.

                                I see you rather still continue to ask doc questions instead of reading the docs yourself.
                                Assuming you have the GATHER_STAT_JOBS running your statistics are always up to date.

                                Can you, for your next question, PLEASE, PLEASE, PLEASE, BEGGING ON MY KNEES, try to find the answer in the documentation, before you again ask a DOC question. Can you reread the Forums Etiquette post stating you should read the docs? Is there anything unclear about it? Or do you try to have your work done with as little effort as possible, dumping it in a forum of volunteers?

                                --------------
                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: Cramer query strange behaviour
                                  user551641
                                  quick version

                                  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)
                                  • 14. Re: Cramer query strange behaviour
                                    user551641
                                    slow version

                                    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)
                                    1 2 Previous Next