Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: FETCH 10 times?? can someone help?

    Can anyone take a guess as to why this is fetching 10 times?
    Should only fetch once, but I traced it from the application so
    I am not exactly sure what is going on.
    Why or How could the app fetch this 10 times?

    It was called only once.
    PHP Code:
    select /* getCustomerForecast */ min(f.intvl_nbr) as intvl_nbr,
      
    sum(f.usage_nbr 4) * 1.0 as usage_nbr,count(*) as sample_count
    FROM
     account ad
    usage_forecast f WHERE ad.org_id = :AND ad.svc_type_cd = :2
      
    AND ad.cust_id = :AND exists (select null from usage_meter_interval mi
      WHERE         mi
    .org_id ad.org_id AND         mi.svc_type_cd =
      
    ad.svc_type_cd AND         mi.cust_id ad.cust_id AND
      
    mi.prem_seq_nbr ad.prem_seq_nbr AND         mi.end_dt >= :AND
      
    mi.end_dt >= ad.created_dt) AND f.org_id ad.org_id AND f.cust_id =
      
    ad.cust_id AND f.day_type_cd = :5 GROUP BY f.intvl_nbr


    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       10      9.48       9.48          0      14214          0          96
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      9.48       9.48          0      14214          0          96

    Misses in library cache during parse
    0
    Optimizer goal
    CHOOSE
    Parsing user id
    32

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  SORT GROUP BY (cr=14214 r=0 w=0 time=9481298 us)
         
    96   NESTED LOOPS SEMI (cr=14214 r=0 w=0 time=9476335 us)
         
    96    MERGE JOIN CARTESIAN (cr=4 r=0 w=0 time=3355 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=106 us)(object id 243891)
         
    96     BUFFER SORT (cr=3 r=0 w=0 time=2705 us)
         
    96      INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=3 r=0 w=0 time=439 us)(object id 244142)
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=14210 r=0 w=0 time=9468540 us)(object id 244146)


    Elapsed times include waiting on following events:
      
    Event waited on                             Times   MaxWait  Total Waited
      
    ----------------------------------------   Waited  ----------  ------------
      
    SQL*Net message to client                      15        0.00          0.00
      SQL
    *Net message from client                    15       11.57         11.60
    ******************************************** 
    when I run/trace this by hand I get:
    PHP Code:
    SELECT /* getCustomerForecast */ MIN(f.intvl_nbr) AS intvl_nbr,
      
    SUM(f.usage_nbr 4) * 1.0 AS usage_nbr,COUNT(*) AS sample_count
    FROM
     ACCOUNT ad
    USAGE_FORECAST f WHERE ad.org_id = :AND ad.svc_type_cd = :2
      
    AND ad.cust_id = :AND EXISTS (SELECT NULL FROM USAGE_METER_INTERVAL mi
      WHERE         mi
    .org_id ad.org_id AND         mi.svc_type_cd =
      
    ad.svc_type_cd AND         mi.cust_id ad.cust_id AND
      
    mi.prem_seq_nbr ad.prem_seq_nbr AND         mi.end_dt >= :AND
      
    mi.end_dt >= ad.created_dt) AND f.org_id ad.org_id AND f.cust_id =
      
    ad.cust_id AND f.day_type_cd = :5 GROUP BY f.intvl_nbr

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.02       0.02          0        198          0          25
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.02       0.02          0        198          0          25

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    32

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    25  SORT GROUP BY (cr=198 r=0 w=0 time=23213 us)
         
    96   NESTED LOOPS SEMI (cr=198 r=0 w=0 time=18842 us)
         
    96    MERGE JOIN CARTESIAN (cr=4 r=0 w=0 time=4666 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=101 us)(object id 243891)
         
    96     BUFFER SORT (cr=3 r=0 w=0 time=4195 us)
         
    96      INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=3 r=0 w=0 time=2026 us)(object id 244142)
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=194 r=0 w=0 time=12431 us)(object id 244146)

    ************************* 
    ps: I know it's a cartesian product but let's take this one step at a time
    since I didn't write it (I just have to fix it).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Just to clarify, I know that:
    FETCH = Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

    I am just wondering if anyone can comment on why they think the statement is parsing once, executing once, but fetching 10 times instead of once.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: FETCH 10 times?? can someone help?

    Presumably the client array size is set to 10, so it takes 10 fetches to get 96 rows. When you ran it by hand you only got 25 rows but got them in 1 fetch - bigger arraysize in SQL Plus?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: FETCH 10 times?? can someone help?

    I'm not clear why you think it should get all the rows in 1 fetch necessarily?

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296

    Re: FETCH 10 times?? can someone help?

    Originally posted by andrewst
    I'm not clear why you think it should get all the rows in 1 fetch necessarily?
    How can I get it to retrieve all the rows in one fetch?
    I guess I am confused why it takes 10 fetches.

    I am trying to figure out why one environment on the same database runs so much slower than another environment (schemas).

    Production environment (same database) get 10 fetches but
    the difference is obvious:
    PHP Code:
    select /* getCustomerForecast */ min(f.intvl_nbr) as intvl_nbr,
      
    sum(f.usage_nbr 4) * 0.6578947368421053 as usage_nbr,count(*) as
      
    sample_count
    FROM
     account ad
    usage_forecast f WHERE ad.org_id = :AND ad.svc_type_cd = :2
      
    AND ad.cust_id = :AND exists (select null from usage_meter_interval mi
      WHERE         mi
    .org_id ad.org_id AND         mi.svc_type_cd =
      
    ad.svc_type_cd AND         mi.cust_id ad.cust_id AND
      
    mi.prem_seq_nbr ad.prem_seq_nbr AND         mi.end_dt >= :AND
      
    mi.end_dt >= ad.created_dt) AND f.org_id ad.org_id AND f.cust_id =
      
    ad.cust_id AND f.day_type_cd = :5 GROUP BY f.intvl_nbr


    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       10      0.12       0.09          0        152          0          96
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      0.12       0.10          0        152          0          96

    Misses in library cache during parse
    0
    Optimizer goal
    CHOOSE
    Parsing user id
    47

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  SORT GROUP BY (cr=152 r=0 w=0 time=98031 us)
         
    96   MERGE JOIN CARTESIAN (cr=152 r=0 w=0 time=96532 us)
          
    1    NESTED LOOPS SEMI (cr=148 r=0 w=0 time=94793 us)
          
    1     TABLE ACCESS BY INDEX ROWID ACCOUNT (cr=2 r=0 w=0 time=93 us)
          
    1      INDEX RANGE SCAN ACCOUNT_PK (cr=1 r=0 w=0 time=64 us)(object id 41982)
          
    1     INDEX RANGE SCAN USAGE_MI_END_DT_IX (cr=146 r=0 w=0 time=94684 us)(object id 42515)
         
    96    BUFFER SORT (cr=4 r=0 w=0 time=1479 us)
         
    96     INDEX RANGE SCAN USAGE_FORECAST_IDX_002 (cr=4 r=0 w=0 time=579 us)(object id 42424
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: FETCH 10 times?? can someone help?

    Like Tony mentioned you can look at the ArraySize to set the number of rows returned per fetch. Possibly one client has this value is set higher (ie 100 rows) and the other lower (ie 10)

    Are you getting the single Fetch from the same client? Or is sql-plus doing 1 fetch, an a different client doing the 10?

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I think from Java it has a different ArraySize than sql-plus or TOAD.

    At this point I am more interested in why the query performs so poorly
    in the first schema and so much better in the second schema.
    (all through the app)

    it boggles my mind.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    You are querying 2 different schemas?

    Is the data the same? You are losing 71 rows in the second query. Is this the result of a smaller data set in the tables?

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: FETCH 10 times?? can someone help?

    The first query is processing 14,000 rows, the second under 200! The 2 databases have very different data in them, hence the different timing.
    Have you analyzed the tables in the bigger schema?

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    this is the point, the stupid query is performing TOTALLY differently
    if run by hand versus run by the application.

    I re-ran the query by hand in sql-plus since toad was being stupid.
    the date is only checking for anything within the last 2 hours

    this is from the same environment with the 14000 rows and the
    poor performance:
    PHP Code:
    select /* getCustomerForecast */ min(f.intvl_nbr) as intvl_nbr,
      
    sum(f.usage_nbr 4) * 1.0 as usage_nbr,count(*) as sample_count
    FROM
     account ad
    usage_forecast f WHERE ad.org_id 'ESU' AND ad.svc_type_cd 'E'
      
    AND ad.cust_id '064014442500' AND exists (select null from usage_meter_interval mi
      WHERE         mi
    .org_id ad.org_id AND         mi.svc_type_cd =
      
    ad.svc_type_cd AND         mi.cust_id ad.cust_id AND
      
    mi.prem_seq_nbr ad.prem_seq_nbr AND         mi.end_dt >= sysdate-(2/24) AND
      
    mi.end_dt >= ad.created_dt) AND f.org_id ad.org_id AND f.cust_id =
      
    ad.cust_id AND f.day_type_cd 'WD' GROUP BY f.intvl_nbr

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.03       0.02          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        8      0.01       0.03          5        198          0          96
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       10      0.04       0.05          5        198          0          96

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    48

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  SORT GROUP BY (cr=198 r=5 w=0 time=29508 us)
         
    96   NESTED LOOPS SEMI (cr=198 r=5 w=0 time=28022 us)
         
    96    MERGE JOIN CARTESIAN (cr=4 r=3 w=0 time=22343 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=108 us)(object id 32427)
         
    96     BUFFER SORT (cr=3 r=3 w=0 time=22019 us)
         
    96      INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=3 r=3 w=0 time=21019 us)(object id 32710)
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=194 r=2 w=0 time=4151 us)(object id 40046

    from test environment (also bad performance through the app):
    PHP Code:
    select /* getCustomerForecast */ min(f.intvl_nbr) as intvl_nbr,
      
    sum(f.usage_nbr 4) * 1.0 as usage_nbr,count(*) as sample_count
    FROM
     account ad
    usage_forecast f WHERE ad.org_id '4' AND ad.svc_type_cd 'E'
      
    AND ad.cust_id 'LT1' AND exists (select null from usage_meter_interval mi
      WHERE         mi
    .org_id ad.org_id AND         mi.svc_type_cd =
      
    ad.svc_type_cd AND         mi.cust_id ad.cust_id AND
      
    mi.prem_seq_nbr ad.prem_seq_nbr AND         mi.end_dt >= sysdate-(2/24) AND
      
    mi.end_dt >= ad.created_dt) AND f.org_id ad.org_id AND f.cust_id =
      
    ad.cust_id AND f.day_type_cd 'WD' GROUP BY f.intvl_nbr

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.04       0.03          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        8      0.01       0.01          0        198          0          96
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       10      0.05       0.04          0        198          0          96

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    32

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  SORT GROUP BY (cr=198 r=0 w=0 time=10801 us)
         
    96   NESTED LOOPS SEMI (cr=198 r=0 w=0 time=8787 us)
         
    96    MERGE JOIN CARTESIAN (cr=4 r=0 w=0 time=2897 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=95 us)(object id 243891)
         
    96     BUFFER SORT (cr=3 r=0 w=0 time=2289 us)
         
    96      INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=3 r=0 w=0 time=757 us)(object id 244142)
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=194 r=0 w=0 time=4062 us)(object id 244146
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Everything I've seen so far suggests to me that the application is not accessing the same set of tables as you are when you test in SQL Plus. The application is seeing 14,210 rows in index USAGE_METER_INT_IND_01 but SQL Plus only sees 198.

    As far as the server is concerned, SQL Plus and your application are no different (apart from array size perhaps!), so the difference in numbers must be caused by some other factor like being logged in as different user with different tables/views, or using different values for the bind variables.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Tony, I KNOW it seems that way but that is not the case.
    I got all the bind variable from the trace file and I've tested all this
    stupid code over and over again.

    I tried rewriting the queries and the closest I came to
    duplicating the result was by running the below.
    As you can see I reversed the matches in the
    EXISTS clause so instead of mi. = ad. I used ad. = mi.

    any clues as to why this would come close to what the app is doing?

    PHP Code:
    SELECT
      
    /* getCustomerForecast */ MIN(f.intvl_nbr) AS intvl_nbr,
      
    SUM(f.usage_nbr 4) * 1.0 AS usage_nbr,
      
    COUNT(*) AS sample_count
    FROM
      ACCOUNT ad
    ,
      
    USAGE_FORECAST f
    WHERE
      ad
    .org_id = :AND
      
    ad.org_id f.org_id AND
      
    ad.svc_type_cd = :AND
      
    ad.svc_type_cd ad.svc_type_cd AND
      
    ad.cust_id = :AND
      
    ad.cust_id f.cust_id AND
      
    ad.prem_seq_nbr f.prem_seq_nbr AND
      
    f.day_type_cd = :AND
      
    EXISTS (SELECT NULL
                      FROM USAGE_METER_INTERVAL mi
              WHERE
                      ad
    .org_id mi.org_id AND
                      
    ad.svc_type_cd =    mi.svc_type_cd AND
                      
    ad.cust_id mi.cust_id AND
                      
    ad.prem_seq_nbr mi.prem_seq_nbr AND
                      
    ad.created_dt <=  mi.end_dt AND
                      
    mi.end_dt >= :)
    GROUP BY f.intvl_nbr

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch        1      9.34       9.22          0      14023          0          25
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      9.35       9.23          0      14023          0          25

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    32

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    25  SORT GROUP BY (cr=14023 r=0 w=0 time=9228574 us)
         
    96   NESTED LOOPS SEMI (cr=14023 r=0 w=0 time=9223906 us)
         
    96    NESTED LOOPS  (cr=5 r=0 w=0 time=4629 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=93 us)(object id 243891)
         
    96     TABLE ACCESS BY INDEX ROWID USAGE_FORECAST (cr=4 r=0 w=0 time=4065 us)
         
    96      INDEX RANGE SCAN USAGE_FORECAST_DAY_TYPE_IDX (cr=3 r=0 w=0 time=1299 us)(object id 244140
    )
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=14018 r=0 w=0 time=9215273 us)(object id 244146

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •