This discussion is archived
10 Replies Latest reply: May 31, 2012 4:35 AM by 726965 RSS

Performance Degradation - High fetches and Prses

711242 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    Any solutions ?? Fetches seems to be high whereas the rows returned are poor/none in count
  • 4. Re: Performance Degradation - High fetches and Prses
    680087 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    PavanKumar Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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