1 2 Previous Next 24 Replies Latest reply on Dec 30, 2016 1:19 PM by AndrewSayer Go to original post
      • 15. Re: huge temp space used..
        Alfonso M. Franch

        Totally agree with what John Watson and Andrew Sawyer say. I would just add what would be my approach on this issue. If I have identified that my problem is the temporary space used by this query, first, I would try to find exactly where this space comes from.

        I would try to split the main query into subqueries and get the point where the temporary tablespace grows. Once I have identified the point (the part of the query I should blame..), then would look at the reason why it fillls (is a SORT?, is a HASH JOIN?, etc..) (the type of temporary segment can tell you why and the explain plan will tell you where). Once identified those points, then would work to find a way to minimize the space used, or to eliminate it at all. (perhaps one SORT is not necessary, perhaps we could use an index in some point and avoid a SORT, as an index is already ordered.., perhaps we could use an index to avoid a HASH JOIN, etc).

         

        This is the way I would act. Of course, I recommend you to try the optimizations suggested by John and Andrew.

         

        Hope this helps

        • 16. Re: huge temp space used..
          AndrewSayer

          Alfonso M. Franch wrote:

           

          Totally agree with what John Watson and Andrew Sawyer say. I would just add what would be my approach on this issue. If I have identified that my problem is the temporary space used by this query, first, I would try to find exactly where this space comes from.

          I would try to split the main query into subqueries and get the point where the temporary tablespace grows. Once I have identified the point (the part of the query I should blame..), then would look at the reason why it fillls (is a SORT?, is a HASH JOIN?, etc..) (the type of temporary segment can tell you why and the explain plan will tell you where). Once identified those points, then would work to find a way to minimize the space used, or to eliminate it at all. (perhaps one SORT is not necessary, perhaps we could use an index in some point and avoid a SORT, as an index is already ordered.., perhaps we could use an index to avoid a HASH JOIN, etc).

           

          This is the way I would act. Of course, I recommend you to try the optimizations suggested by John and Andrew.

           

          Hope this helps

          Or...

           

          SQL live monitor report (assuming diagnostics+tuning pack was paid for), it's easy if the SQL was already attempted:

          SELECT DBMS_SQLTUNE.report_sql_monitor(

            sql_id       => RTRIM(:sql_id ,','),

            type         => 'TEXT',

            report_level => 'ALL sql_text'

          ) AS report

          FROM DUAL;

          (obfuscated output for a simple but intensive query that I cancelled mid execution to replicate what would happen with a temp tablespace problem):

           

          Error: ORA-3135
          ------------------------------
          ORA-03135: connection lost contact
          
          
          
          
          Global Information
          ------------------------------
           Status              :  DONE (ERROR)
           Instance ID         :  1
           Session             :  ANDY (75:20772)
           SQL ID              :  fhkkxgs3x3ktu
           SQL Execution ID    :  16777216
           Execution Started   :  12/28/2016 13:13:03
           First Refresh Time  :  12/28/2016 13:13:08
           Last Refresh Time   :  12/28/2016 13:30:49
           Duration            :  1066s
           Module/Action       :  SQL*Plus/-
           Service             :  DB
           Program             :  sqlplus.exe
           Fetch Calls         :  1
          
          
          Global Stats
          ===========================================================================================
          | Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  | Write | Write |
          | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Reqs  | Bytes |
          ===========================================================================================
          |    1064 |     494 |      391 |      179 |     1 |   551K | 316K |  16GB | 91680 |  11GB |
          ===========================================================================================
          
          
          SQL Plan Monitoring Details (Plan Hash Value=4060895995)
          ===============================================================================================================================================================================================================================
          | Id |              Operation               |            Name            |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  | Write | Write |  Mem  | Temp  | Activity |        Activity Detail        |
          |    |                                      |                            | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |          (# samples)          |
          ===============================================================================================================================================================================================================================
          |  0 | SELECT STATEMENT                     |                            |         |       |        12 |  +1055 |     1 |        0 |       |       |       |       |       |       |          |                               |
          |  1 |   HASH UNIQUE                        |                            |   11060 |    1M |        12 |  +1055 |     1 |        0 |       |       |       |       |    1M |       |          |                               |
          |  2 |    FILTER                            |                            |         |       |        12 |  +1055 |     1 |     118K |       |       |       |       |       |       |          |                               |
          |  3 |     HASH JOIN                        |                            |      6M |    1M |       638 |   +429 |     1 |     118K |   563 | 136MB |  3512 | 851MB |  448M |  943M |     3.29 | Cpu (30)                      |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (3)     |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path write temp (2)    |
          |  4 |      VIEW                            | index$_join$_002           |     22M |  580K |       443 |   +429 |     1 |      47M |       |       |       |       |       |       |     0.47 | Cpu (5)                       |
          |  5 |       HASH JOIN                      |                            |         |       |       705 |   +167 |     1 |      47M | 30335 |   3GB | 30335 |   3GB |  440M |    4G |    18.34 | Cpu (150)                     |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (12)       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (32)    |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path write temp (1)    |
          |  6 |        HASH JOIN                     |                            |         |       |       425 |     +3 |     1 |      47M | 32486 |   4GB | 32486 |   4GB |  663M |    4G |    17.03 | Cpu (144)                     |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (24)       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (13)    |
          |  7 |         PARTITION RANGE ALL          |                            |     22M | 14995 |       154 |     +5 |     1 |      47M |       |       |       |       |       |       |          |                               |
          |  8 |          INDEX RANGE SCAN            | INDEX_01                   |     22M | 14995 |       157 |     +2 |   487 |      47M | 80335 |   1GB |       |       |       |       |    13.45 | Cpu (14)                      |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (37)       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | Disk file operations I/O (1)  |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file sequential read (91)  |
          |  9 |         PARTITION RANGE ALL          |                            |     22M | 83394 |       143 |   +165 |     1 |     127M |       |       |       |       |       |       |          |                               |
          | 10 |          INDEX FAST FULL SCAN        | INDEX_02                   |     22M | 83394 |       150 |   +158 |   487 |     127M | 11736 |   3GB |       |       |       |       |     7.71 | Cpu (22)                      |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (33)       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file scattered read (26)   |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file sequential read (1)   |
          | 11 |        PARTITION RANGE ALL           |                            |     22M | 46285 |       280 |   +429 |     1 |     127M |       |       |       |       |       |       |          |                               |
          | 12 |         BITMAP CONVERSION TO ROWIDS  |                            |     22M | 46285 |       282 |   +427 |   487 |     127M |       |       |       |       |       |       |     0.94 | Cpu (10)                      |
          | 13 |          BITMAP INDEX FULL SCAN      | INDEX_03                   |         |       |       282 |   +427 |   487 |      26M |  127K |   2GB |       |       |       |       |    20.98 | Cpu (17)                      |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (41)       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file sequential read (165) |
          | 14 |      VIEW                            | index$_join$_001           |     41M |  320K |        12 |  +1055 |     1 |       3M |       |       |       |       |       |       |     0.09 | Cpu (1)                       |
          | 15 |       HASH JOIN                      |                            |         |       |       147 |   +920 |     1 |       3M |  2767 | 324MB | 10563 |   1GB |  985M |    1G |     3.67 | Cpu (24)                      |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (7)        |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (1)     |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path write temp (7)    |
          | 16 |        HASH JOIN                     |                            |         |       |       166 |   +870 |     1 |      42M | 14778 |   2GB | 14789 |   2GB |  559M |    2G |     9.78 | Cpu (60)                      |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (15)       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (15)    |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | direct path write temp (14)   |
          | 17 |         PARTITION RANGE ALL          |                            |     41M | 26912 |        41 |   +871 |     1 |      42M |       |       |       |       |       |       |          |                               |
          | 18 |          INDEX FAST FULL SCAN        | A_INDEX_01                 |     41M | 26912 |        41 |   +871 |   486 |      42M |  5252 | 836MB |       |       |       |       |     1.79 | Cpu (4)                       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (3)        |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file scattered read (11)   |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file sequential read (1)   |
          | 19 |         PARTITION RANGE ALL          |                            |     41M |  4772 |        41 |   +920 |     1 |      42M |       |       |       |       |       |       |          |                               |
          | 20 |          BITMAP CONVERSION TO ROWIDS |                            |     41M |  4772 |        50 |   +911 |   486 |      42M |       |       |       |       |       |       |     0.19 | Cpu (2)                       |
          | 21 |           BITMAP INDEX FULL SCAN     | A_INDEX_02                 |         |       |        50 |   +911 |   486 |     889K |  9886 | 154MB |       |       |       |       |     1.79 | Cpu (1)                       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file sequential read (18)  |
          | 22 |        PARTITION RANGE ALL           |                            |     41M | 28833 |        16 |  +1051 |     1 |       8M |       |       |       |       |       |       |          |                               |
          | 23 |         INDEX FAST FULL SCAN         | A_INDEX_03                 |     41M | 28833 |        32 |  +1035 |    42 |       8M |   778 | 156MB |       |       |       |       |     0.47 | Cpu (1)                       |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | resmgr:cpu quantum (1)        |
          |    |                                      |                            |         |       |           |        |       |          |       |       |       |       |       |       |          | db file scattered read (3)    |
          ===============================================================================================================================================================================================================================
          

          Here, we can see that the max temp usage was from lines 5 and 6 (probably just rounded to the same) of 4G, the reason is obvious too: they were trying to hash join from 47M rows.

          I killed the execution before it got to the HASH UNIQUE step.

          • 17. Re: huge temp space used..
            jgarry

            3255304 wrote:

             

            There is now view,,

             

            SQL> select view_name from dba_views where view_name='VW_NSO_1';

             

            no rows selected

             

            SQL> show user

            USER is "SYS"

            SQL>

             

            Regards

            I believe that is a view that is created on the fly for Oracle to work on.  So it wouldn't be in dba_views.  Do you have any views that are hiding tables?  Also see https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2  for that vm_nwvw_2.

            • 18. Re: huge temp space used..
              AndrewSayer

              jgarry wrote:

               

              3255304 wrote:

               

              There is now view,,

               

              SQL> select view_name from dba_views where view_name='VW_NSO_1';

               

              no rows selected

               

              SQL> show user

              USER is "SYS"

              SQL>

               

              Regards

              I believe that is a view that is created on the fly for Oracle to work on. So it wouldn't be in dba_views. Do you have any views that are hiding tables? Also see https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2 for that vm_nwvw_2.

              See https://jonathanlewis.wordpress.com/2011/02/27/internal-views/ for some of the views that Oracle comes up with, this one is accompanied with "Note "VM" rather than "VW" - possibly related to complex view merging with distinct" which relates to the distinct in one of the subqueries that has been merged into the larger subquery.

               

              Which made me notice:

              "AND ST.acc_no IN (SELECT DISTINCT rbk.ac_no_system  "

               

              The distinct is not necessary and probably complicates things for the optimizer. (although John also pointed that one out, I missed it originally).

               

              The join inside the subquery doesn't look particularly pleasant for the optimizer either, perhaps that's why it originally opted to MJC R_AC_TYPE_MASTER with R_CONSOL_BNK_STMT rather than joining to r_bankaccounts first.

               

              I usually associate top-n queries with either small result sets (so sorting the entirety is fine) or results sets that can easily be accessed in order (supporting indexes). I don't think this query/plan ticks either box.

              • 19. Re: huge temp space used..
                Jonathan Lewis

                Paul,

                 

                I don't think the window buffer (in this case, at least) has anything to do with any indexing. In the first plan it occurs after a hash unique so the data will not be appearing in an order dictated by any index, its order will be dependent on the hashing. In the second plan the data could be reaching the window buffer in the order dictated by the index IDX_R_CNSL_BK_SM_STMTDT but I don't think the optimizer is smart enough to detect this through a hash join and two nested loop joins. (In passing it looks as if this index is likely to be start with the statement date and that suggests it's a function-based index on trunc(statement_date) which, perhaps is why the predicate on statement_date is the one we see).

                 

                 

                Regards

                Jonathan Lewis

                • 20. Re: huge temp space used..
                  Jonathan Lewis

                  Have you actually executed the query and seen the plan from the QSL Profile being used ?

                   

                  With an SQL profile in place we might expect some of the predictions of rowcounts and bytes to be reasonably accurate so your WINDOW BUFFER line showing 198M ought to be a reasonable estimate and could explain roughly 200MB of sort space (rather than 200GB). Is it possible that even after invoking the SQL profile Oracle still executed the old plan ?

                   

                  The difference between the two plans:

                   

                  In the profiled plan Oracle unnested the subquery (producing internal view vw_nso_1) before using it as the target of a semi-join with the r_consol_bank_stmt table and then outer joining to the other two tables.

                   

                  In theory it could have unnested the subquery then done a hash unique (or sort unique) on that inline view before doing a join (not semi-join) with the r_consol_bank_stmt and then on to the other two tables; in practice your original plan seems to have gone through that step and then done complex view merging to delay the hash unique to a point after doing all the joins.

                   

                  In terms of TEMP space requirements - the delayed hash unique MIGHT have been responsible for a lot of the volume - and that's really the most likely explanation; however the WINDOE BUFFER (which may have overflowed the buffer and spilled to disc) could have been responsible for a lot of the temp space usage.

                   

                  As others have said, you need to use either SQL Monitoring as the query runs, or enable rowsource execution stats and pull the plan from memory after the query has completed if you want to find out exactly where the space utilisation appeared:

                   

                  e.g. from SQL*Plus

                   

                  alter session set statistics_level = all

                  set serveroutput off

                  set linesize 256

                  set trimspool on

                  set pagesize 60

                   

                  alter session set statistics_level = all;

                  {run your query}

                  select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

                  alter session set statistics_level = typical;

                   

                   

                   

                  Regards

                  Jonathan Lewis

                  • 21. Re: huge temp space used..
                    Paulzip

                    Jonathan Lewis wrote:

                     

                    Paul,

                     

                    I don't think the window buffer (in this case, at least) has anything to do with any indexing. In the first plan it occurs after a hash unique so the data will not be appearing in an order dictated by any index, its order will be dependent on the hashing. In the second plan the data could be reaching the window buffer in the order dictated by the index IDX_R_CNSL_BK_SM_STMTDT but I don't think the optimizer is smart enough to detect this through a hash join and two nested loop joins. (In passing it looks as if this index is likely to be start with the statement date and that suggests it's a function-based index on trunc(statement_date) which, perhaps is why the predicate on statement_date is the one we see).

                     

                     

                    Regards

                    Jonathan Lewis

                    Hi Jonathan

                     

                    Yes, i think you're correct, mine was a hunch as I hadn't seen the source code for the view, when I posted.

                     

                    Do you think the view would benefit from inline order by with a rownum instead of row_number?

                     

                    Paul

                    • 22. Re: huge temp space used..
                      AndrewSayer

                      Paulzip wrote:

                       

                      Jonathan Lewis wrote:

                       

                      Paul,

                       

                      I don't think the window buffer (in this case, at least) has anything to do with any indexing. In the first plan it occurs after a hash unique so the data will not be appearing in an order dictated by any index, its order will be dependent on the hashing. In the second plan the data could be reaching the window buffer in the order dictated by the index IDX_R_CNSL_BK_SM_STMTDT but I don't think the optimizer is smart enough to detect this through a hash join and two nested loop joins. (In passing it looks as if this index is likely to be start with the statement date and that suggests it's a function-based index on trunc(statement_date) which, perhaps is why the predicate on statement_date is the one we see).

                       

                       

                      Regards

                      Jonathan Lewis

                      Hi Jonathan

                       

                      Yes, i think you're correct, mine was a hunch as I hadn't seen the source code for the view, when I posted.

                       

                      Do you think the view would benefit from inline order by with a rownum instead of row_number?

                       

                      Paul

                       

                      Taking a look at the SQL again:

                       

                      1. Row_number() 
                      2.                 over( 
                      3.                   ORDER BY 1) sr_no, 
                      4.               Count(1) 
                      5.                 over( 
                      6.                   ORDER BY 1) rc_internal 

                       

                      Hmm:

                      create table as_test_order as select trunc(dbms_random.value(0,100)) number_col from dual connect by rownum <=10;

                      select a.*, row_number() over (order by 1) rown, count(1) over(order by 1) cnt FROM as_test_order a;

                       

                       

                       

                       

                        NUMBER_COL         ROWN          CNT

                      ____________ ____________ ____________

                                15            1           10

                                77            2           10

                                93            3           10

                                64            4           10

                                93            5           10

                                42            6           10

                                36            7           10

                                58            8           10

                                 5            9           10

                                56           10           10

                       

                       

                      I don't think that was what OP had intended.

                       

                      I'll refer back to John's comment: ". When I see basic errors like this I think is usually best to throw away the query and start again, because the developer does not unserstand what he is doing. The query may not just be inefficient, it may also be delivering incorrect results."

                      • 23. Re: huge temp space used..
                        Jonathan Lewis

                        Well I made two mistakes reading that bit of the statement:

                        I failed to notice the absence of a partition clause, and I read the "order by 1" as "order by the first column in the select list"

                         

                        I think the absence of a partition then ordering by a constant would be enough to ensure that the window operation turned into a simple FIFO buffer event - i.e. window buffer.

                         

                        Regards

                        Jonathan Lewis

                         

                        P.S.  I have to say that I wouldn't regard the presence of the redundant DISTINCT in the subquery as a significant error.  In effect the optimizer would have put it in while parsing anyway so that it could take it out or move it during some of the transformation options. It MIGHT indicate lack of familiarity with concepts, on the other hand it might (on its own) be a case of a programmer making the meaning very explicit for the benefit of the next programmer along - rather like the programmer who puts in a few redundant pairs of brackets in a chain of conjuncts and distjuncts.

                        • 24. Re: huge temp space used..
                          AndrewSayer

                          Jonathan Lewis wrote:

                           

                          Well I made two mistakes reading that bit of the statement:

                          I failed to notice the absence of a partition clause, and I read the "order by 1" as "order by the first column in the select list"

                           

                          I think the absence of a partition then ordering by a constant would be enough to ensure that the window operation turned into a simple FIFO buffer event - i.e. window buffer.

                           

                          Regards

                          Jonathan Lewis

                           

                          P.S. I have to say that I wouldn't regard the presence of the redundant DISTINCT in the subquery as a significant error. In effect the optimizer would have put it in while parsing anyway so that it could take it out or move it during some of the transformation options. It MIGHT indicate lack of familiarity with concepts, on the other hand it might (on its own) be a case of a programmer making the meaning very explicit for the benefit of the next programmer along - rather like the programmer who puts in a few redundant pairs of brackets in a chain of conjuncts and distjuncts.

                          I commonly make the mistake of reading what I expect to see rather than what I actually see, that's why whenever I have to scratch my head I turn to my colleagues to get second opinions.

                           

                          Doing a rough test case on 12.1.0.2:

                          create table as_test_order as select trunc(dbms_random.value(0,100)) number_col from dual connect by rownum <=1000;

                          select * FROM (select a.*, row_number() over (order by 1) rown,

                          count(1) over(order by 1) cnt FROM as_test_order a) where rown between

                          1 and 20

                           

                           

                          Plan hash value: 3325039355

                           

                           

                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------

                          | Id  | Operation           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT    |               |      1 |        |       |     3 (100)|          |     20 |00:00:00.01 |       4 |      2 |       |       |          |

                          |*  1 |  VIEW               |               |      1 |   1000 | 39000 |     3   (0)| 00:00:01 |     20 |00:00:00.01 |       4 |      2 |       |       |          |

                          |   2 |   WINDOW BUFFER     |               |      1 |   1000 |  3000 |     3   (0)| 00:00:01 |   1000 |00:00:00.01 |       4 |      2 | 36864 | 36864 |32768  (0)|

                          |   3 |    TABLE ACCESS FULL| AS_TEST_ORDER |      1 |   1000 |  3000 |     3   (0)| 00:00:01 |   1000 |00:00:00.01 |       4 |      2 |       |       |          |

                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------

                           

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                           

                             1 - filter(("ROWN">=1 AND "ROWN"<=20))

                           

                          select * FROM (select a.*, row_number() over (order by 1) rown FROM

                          as_test_order a) where rown between 1 and 20

                           

                           

                          Plan hash value: 2920352010

                           

                           

                          ----------------------------------------------------------------------------------------------------------------------------------

                          | Id  | Operation              | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

                          ----------------------------------------------------------------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT       |               |      1 |        |       |     3 (100)|          |     20 |00:00:00.01 |       5 |

                          |*  1 |  VIEW                  |               |      1 |     20 |   520 |     3   (0)| 00:00:01 |     20 |00:00:00.01 |       5 |

                          |*  2 |   WINDOW NOSORT STOPKEY|               |      1 |   1000 |  3000 |     3   (0)| 00:00:01 |     20 |00:00:00.01 |       5 |

                          |   3 |    TABLE ACCESS FULL   | AS_TEST_ORDER |      1 |   1000 |  3000 |     3   (0)| 00:00:01 |     21 |00:00:00.01 |       5 |

                          ----------------------------------------------------------------------------------------------------------------------------------

                           

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                           

                             1 - filter(("ROWN">=1 AND "ROWN"<=20))

                             2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=20)

                           

                          So yes, you would be right if the query didn't also project the count analytic.

                           

                          I agree with your note about the distinct, but when combined with the filtering on trunc and the weird (IMO) analytics, I don't think it's being too harsh.

                          1 2 Previous Next