10 Replies Latest reply: May 31, 2012 6:35 AM by 726965 RSS

    Performance Degradation - High fetches and Prses

    user4566776
      Hello,

      My analysis on a particular job trace file drew my attention towards:

      1) High rate of Parses instead of Bind variables usage.

      2) High fetches and poor number/ low number of rows being processed

      Please let me kno as to how the performance degradation can be minimised, Perhaps the high number of SQL* Net Client wait events may be due to multiple fetches and transactions with the client.
      EXPLAIN PLAN FOR SELECT /*+ FIRST_ROWS (1)  */ * FROM  SAPNXP.INOB 
      WHERE MANDT = :A0 
      AND KLART = :A1 
      AND OBTAB = :A2 
      AND OBJEK LIKE :A3 AND ROWNUM <= :A4;
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse      119      0.00       0.00          0          0          0           0
      Execute    239      0.16       0.13          0          0          0           0
      Fetch      239   2069.31    2127.88          0   13738804          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total      597   2069.47    2128.01          0   13738804          0           0
      
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 1235313998
      
      ---------------------------------------------------------------------------------------
      | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |        |     2 |   268 |     1   (0)| 00:00:01 |
      |*  1 |  COUNT STOPKEY               |        |       |       |            |          |
      |*  2 |   TABLE ACCESS BY INDEX ROWID| INOB   |     2 |   268 |     1   (0)| 00:00:01 |
      |*  3 |    INDEX SKIP SCAN           | INOB~2 |  7514 |       |     1   (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=TO_NUMBER(:A4))
         2 - filter("OBJEK" LIKE :A3 AND "KLART"=:A1)
         3 - access("MANDT"=:A0 AND "OBTAB"=:A2)
             filter("OBTAB"=:A2)
      
      18 rows selected.
      
      SQL> SELECT INDEX_NAME,TABLE_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE INDEX_OWNER='SAPNXP' AND INDEX_NAME='INOB~2';
      
      INDEX_NAME      TABLE_NAME                     COLUMN_NAME
      --------------- ------------------------------ --------------------
      INOB~2          INOB                           MANDT
      INOB~2          INOB                           CLINT
      INOB~2          INOB                           OBTAB
      Is it possible to Maximise the rows/fetch
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse      163      0.03       0.00          0          0          0           0
      Execute    163      0.01       0.03          0          0          0           0
      Fetch   174899     55.26      59.14          0    1387649          0     4718932
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total   175225     55.30      59.19          0    1387649          0     4718932
      
      Misses in library cache during parse: 0
      Optimizer mode: ALL_ROWS
      Parsing user id: 27
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
        28952  TABLE ACCESS BY INDEX ROWID EDIDC (cr=8505 pr=0 pw=0 time=202797 us)
        28952   INDEX RANGE SCAN EDIDC~1 (cr=1457 pr=0 pw=0 time=29112 us)(object id 202995)
      
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        SQL*Net message to client                  174899        0.00          0.16
        SQL*Net more data to client                155767        0.01          5.69
        SQL*Net message from client                174899        0.11        208.21
        latch: cache buffers chains                     2        0.00          0.00
        latch free                                      4        0.00          0.00
      ********************************************************************************
        • 1. Re: Performance Degradation - High fetches and Prses
          Taral
          Please provide oracle DB version and OS.

          From first plan it seems like mostly all time is spent on parsing. Do you have your stats updated.

          Well, it depends on version
          • 2. Re: Performance Degradation - High fetches and Prses
            user4566776
            Oralce 10G, HP AIX, Yes stats are upto date.

            Does the above info really matter? anyways here u have it
            • 3. Re: Performance Degradation - High fetches and Prses
              user4566776
              Any solutions ?? Fetches seems to be high whereas the rows returned are poor/none in count
              • 4. Re: Performance Degradation - High fetches and Prses
                Timur Akhmadeev
                Please post a section of "Row Source Operation" from the trace of a single run of the query. And follow this instructions.
                • 5. Re: Performance Degradation - High fetches and Prses
                  710790
                  Hi,

                  Increase your 'arraysize' and check the EXPLAIN PLAN once again.

                  By Default the arraysize for sqlplus client is 15.

                  Change it to 100 and check the PLAN.
                  sql> set arraysize 100
                  Regards,
                  Pabolu
                  • 6. Re: Performance Degradation - High fetches and Prses
                    Pavan Kumar
                    Hi,

                    Query :-
                    SELECT /*+ FIRST_ROWS (1)  */ * FROM  SAPNXP.INOB 
                    WHERE MANDT = :A0 
                    AND KLART = :A1 
                    AND OBTAB = :A2 
                    AND OBJEK LIKE :A3 AND ROWNUM <= :A4;
                    
                    PLAN_TABLE_OUTPUT
                    ------------------------------------------------------------------------------------------------------------------------------------------------------
                    Plan hash value: 1235313998
                     
                    ---------------------------------------------------------------------------------------
                    | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                    ---------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT             |        |     2 |   268 |     1   (0)| 00:00:01 |
                    |*  1 |  COUNT STOPKEY               |        |       |       |            |          |
                    |*  2 |   TABLE ACCESS BY INDEX ROWID| INOB   |     2 |   268 |     1   (0)| 00:00:01 |
                    |*  3 |    INDEX SKIP SCAN           | INOB~2 |  7514 |       |     1   (0)| 00:00:01 |
                    ---------------------------------------------------------------------------------------
                    
                       1 - filter(ROWNUM<=TO_NUMBER(:A4))
                       2 - filter("OBJEK" LIKE :A3 AND "KLART"=:A1)
                       3 - access("MANDT"=:A0 AND "OBTAB"=:A2)
                           filter("OBTAB"=:A2)
                    If you check properly the It's going of index and skip scan - number of Rows = 7514.
                    Why you are going for dynamic value for rownum < (?)

                    Can you post on which Columns indexes are available on this table and your are using the hint of First_rows(1) and with respect to dynamic rownum which changes that its effecting the high fetch count.

                    I think that might be Issue.

                    Modify the First_rows (100) and check the explain and Parsing and fetch rate of SQL Query

                    - Pavan Kumar N
                    Oracle 9i/10g - OCP
                    http://oracleinternals.blogspot.com/

                    Edited by: Pavan Kumar on Dec 30, 2009 12:04 AM
                    • 7. Re: Performance Degradation - High fetches and Prses
                      Jonathan Lewis
                      user4566776 wrote:

                      My analysis on a particular job trace file drew my attention towards:

                      1) High rate of Parses instead of Bind variables usage.
                      But if you look at the text you are using bind variables.

                      The first query is executed 239 times - which matches the 239 fetches. You cut off some of the useful information from the tkprof output, but the figures show that you're executing more than once per parse call. The time is CPU time spent using a bad execution plan to find no data -- this looks like a bad choice of index, possibly a side effect of the first_rows(1) hint.

                      2) High fetches and poor number/ low number of rows being processed
                      The second query is doing a lot of fetches because in 163 executions it is fetching 4.7 million rows at roughly 25 rows per fetch. You might improve performance a little by increasing the array fetch size - but probably not by more than a factor of 2.

                      You'll notice that even though you record 163 parse calls for the second statement the number of " Misses in library cache during parse" is zero - so the parse calls are pretty irrelevant, the cursor is being re-used.

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

                      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
                      .
                      
                      
                      "Science is more than a body of knowledge; it is a way of thinking" 
                      Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                      • 8. Re: Performance Degradation - High fetches and Prses
                        710790
                        Hi Jonathan Lewis,

                        I am great fan of you. I love reading your articles and books.

                        I am having a doubt, maybe silly.

                        In the second query, why are no of fetches *(174899)* more than the no of executions *(163)*
                        call     count       cpu    elapsed       disk      query    current        rows
                        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                        Parse      163      0.03       0.00          0          0          0           0
                        Execute    163      0.01       0.03          0          0          0           0
                        Fetch   174899     55.26      59.14          0    1387649          0     4718932
                        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                        total   175225     55.30      59.19          0    1387649          0     4718932
                        Regards,
                        Pabolu

                        Edited by: Pabolu on Dec 30, 2009 10:49 AM
                        • 9. Re: Performance Degradation - High fetches and Prses
                          Hemant K Chitale
                          Divide the number of rows by the number of fetches to get the array size. You would see that each fetch gets 27 rows.

                          If the row size isn't very large, you could set a larger array size (eg using SET ARRAYSIZE in SQLPlus and the corresponding settings in ODBC if using ODBC etc).


                          Hemant K Chitale
                          http://hemantoracledba.blogspot.com
                          • 10. Re: Performance Degradation - High fetches and Prses
                            726965
                            HI,


                            We are using EBS r12.0.6 with database 10.2.0.3 in RHEL 4 AS 32-bit.

                            We are facing the same issue like the default size of array is 15, and when I set it to 1000 this is giving
                            better response.

                            So, in EBS r12 where this parameters should we set for all custom and standard reports access from application node?


                            Ahmed