12 Replies Latest reply on May 25, 2013 4:35 AM by rp0428

    Performance Query

      create global temporary table test_10
      ( id number,
      col1 number,
      col2 number,
      col3 number,
      seq_no number,
      CONSTRAINT pk_test_10 PRIMARY KEY (id,seq_no) ) on commit preserve rows;

      insert into test_10 values(20,10,50,25,1);
      insert into test_10 values(20,20,null,null,2);
      insert into test_10 values(20,10,null,null,3);
      insert into test_10 values(20,20,null,null,4);

      select *
      from test_10
      partition by ( id )
      dimension by ( row_number() over(partition by id order by seq_no) as i )
      measures (col1, col2, col3)
      rules automatic order (
      col2[i>1] = col1[cv()-1] + col3[cv()-1]
      , col3[i>1] = col2[cv()] + col3[cv()-1]

      Please find below the sql plan for this query.

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      | 0 | SELECT STATEMENT | | 1 | 65 | 3 (34)| 00:00:01 |
      | 1 | SQL MODEL CYCLIC | | 1 | 65 | 3 (34)| 00:00:01 |
      | 2 | WINDOW SORT | | 1 | 65 | 3 (34)| 00:00:01 |
      | 3 | TABLE ACCESS FULL| TEST_10 | 1 | 65 | 2 (0)| 00:00:01 |

      TEST_10 has millions of records. Could you please let me know if there is any way to improve the performance of this query.
        • 1. Re: Performance Query
          Why are you using a global temporary table?
          Have you tried to rewrite the query without using the MODEL clause? edit actually, on a closer look, MODEL seems most appropriate here...
          Have you read: {message:id=9360003} ?
          If so, why haven't you posted your database version and all other useful information?

          Edited by: Hoek on May 24, 2013 11:17 AM
          • 2. Re: Performance Query
            Seeing that you don't have a where clause and your query will always be going for a FTS and the data volume is high you can consider parallel query as an option. And again your system must be configured with adequate resource to handle parallel query.
            • 3. Re: Performance Query
              There is another process which populates this Global temp table
              and using this global temp table data some calculations have to be performed.and finally insert teh data into
              a permanent table.

              Oracle vesion which I am using is 11i
              • 4. Re: Performance Query
                Oracle vesion which I am using is 11i
                Please provide a full version, the result of:
                select * from v$version;
                Furthermore, you really should follow the steps from the previous link.
                In case you missed where it points to:
                It is essential to read and understand that one, you'll be using that knowledge over and over in the future.
                If possible, then provide trace/tkprof output with wait events of your process.
                Please ask your DBA to help you is you don't know how to do this yourself
                Your example is not showing us the real processing time (wallclock/cpu) and amounts of data.
                But it also is a nice example of what MODEL clause is capable of ;)
                An alternative would perhaps be a pipelined PL/SQL function, however, you first need to benchmark.
                • 5. Re: Performance Query
                  Maybe Hoek's initial crossed out idea might be on the right lines? If I read it correctly, you can lose the interdependence of c2 and c3 by substituting in the rhs of rule 1 for c2 in rule 2: Then the output row always depends just on the previous row, or is unchanged. You could do that very simply in PL/SQL. Here is pseudocode (pipe row could be replaced by any kind of processing, eg insert into the target table with calculations):
                    FOR rec IN (SELECT * FROM test_10 ORDER BY id, seq) LOOP
                      if (first instance of id) then
                        pipe row (rec)
                        rec_p = rec
                      rec_o.c1 = rec.c1
                      rec_o.c2 = rec_p.c1 + rec_p.c3
                      rec_o.c3 = rec_p.c1 + 2 * rec_p.c3
                      pipe row (rec_o)
                      rec_p = rec_o
                    END LOOP
                  ps Just read Hoek's second post - the above is essentially for pipelined function approach - but it could be just PL/SQL in a procedure too.
                  • 6. Re: Performance Query
                    In this case context switching between PLSQL and SQL will be more which will impact the performance
                    • 7. Re: Performance Query
                      It depends.... http://www.oracle-developer.net/display.php?id=429
                      But for now we're really depending on (more) input from your side.
                      • 8. Re: Performance Query
                        Well it may impact performance, but by how much, and how would it compare with Model? Best way to find out is to try it.
                        • 9. Re: Performance Query
                          I think you dont need this row_number as partition and dimension together have (usually) to be unique and that's the case here because of the PK.
                          So just
                          dimension by (seq_no i)
                          Perhaps it's also worth a try to rewrite the rules.
                          I dont see a need for automatically here
                          rules (
                           col3[i>1] = col1[cv()-1]+ 2*col3[cv()-1]
                          ,col2[i>1] = col1[cv()-1] + col3[cv()-1]
                          However, with a table of this size you might perhaps get a memory problem, because the model is done in memory. If the memory is not sized big enough, that may slow doen the hole thing.

                          Edited by: chris227 on 24.05.2013 04:10

                          Edited by: chris227 on 24.05.2013 04:13

                          Edited by: chris227 on 24.05.2013 04:17
                          • 10. Re: Performance Query
                            We don't have your full Oracle 11 version so just an idea: Recursive Subquery Factoring might work too.



                            Edited by: Etbin on 24.5.2013 21:09
                            link added (recursive with starts with 11.2)
                            • 11. Re: Performance Query
                              chris227 wrote:
                              I think you dont need this row_number as partition and dimension together have (usually) to be unique and that's the case here because of the PK.
                              This MODEL solution was suggested in this thread : {thread:id=2534810}
                              Unicity is indeed guaranteed, but there's also the fact it must be gap-free in order to use the rhs reference [cv()-1].
                              If some business rule guarantees that as well then I guess it's OK to use SEQ_NO directly.

                              A recursive subquery was suggested too : {message:id=11012550}
                              Worth a try if the (yet) unknown version permits it.
                              • 12. Re: Performance Query
                                TEST_10 has millions of records. Could you please let me know if there is any way to improve the performance of this query.
                                You haven't provided ANY information that indicates that the performance NEEDS to be improved.

                                You posted:

                                1. DDL for a temp table
                                2. four INSERT statements
                                3. A query
                                4. An explain plain showing it is taking 1 second to execute the query
                                0  SELECT STATEMENT    1  65  3 (34) 00:00:01  
                                Many times people post questions like this because they suffer from C.T.D. - compulsive tuning disorder.

                                Please explain how the information you posted even remotely suggests that there is a performance problem.