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

    Unhappy Unanswered: problem with query from application vesus hard-run

    Well, here is our problem.
    Now I need to figure out why it is a problem.
    The first is from the application. The second I ran as a hard-run. WHY ARE THEY SO DIFFERENT?

    AS you see the first is a cartesian product, but that still does not justify the time involved with query. If they are the same, why are the results so different?


    PHP Code:
    SELECT /* ReadingsDataAccess */ mi.end_dt-(15/60)/24 AS reading_start,
    mi.usage_nbr AS usage,
    arp.rate_period_type_cd AS rpt
    FROM meter_interval mi
    account_device adaccount_rate_period arp
    WHERE mi
    .org_id = :AND
    mi.device_id=ad.device_id AND
    ad.org_id = :AND
    ad.cust_id = :AND
    ad.svc_type_cd = :AND
    ad.prem_seq_nbr = :AND
    mi.org_id arp.org_id AND
    arp.org_id ad.org_id AND
    arp.cust_id ad.cust_id AND
    arp.svc_type_cd ad.svc_type_cd AND
    arp.prem_seq_nbr ad.prem_seq_nbr AND
    arp.rate_plan_cd = :AND
    arp.usage_qty IS NOT NULL AND
    (
    mi.end_dt -(15/60)/24) >= arp.start_dt AND
    (
    mi.end_dt -(15/60)/24) < arp.end_dt AND
    mi.end_dt > :AND
    mi.end_dt <= :AND
    mi.end_dt <= arp.end_dt

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        4      0.00       0.00          0          0          0           0
    Execute      4      0.04       0.06          0          0          0           0
    Fetch       33     28.26      32.84          0      33951          0         308
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       41     28.30      32.91          0      33951          0         308


    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  FILTER  (cr=8491 r=0 w=0 time=7195646 us)
         
    96   NESTED LOOPS  (cr=8491 r=0 w=0 time=7195355 us)
        
    284    MERGE JOIN CARTESIAN (cr=9 r=0 w=0 time=16508 us)
        
    284     INDEX RANGE SCAN AC_RPRD_TYPCD_IX (cr=8 r=0 w=0 time=4495 us)(object id 144472)
        
    284     BUFFER SORT (cr=1 r=0 w=0 time=7004 us)
          
    1      INDEX RANGE SCAN ACC_DEV_ACC_DEV_PK (cr=1 r=0 w=0 time=56 us)(object id 144043)
         
    96    INDEX RANGE SCAN MTRINT_USAGE_NBR_IX (cr=8482 r=0 w=0 time=7171041 us)(object id 144257

    BUT, when I run it through sql-plus on prod_copy I get the below.
    Why would it be so different?

    Is there any way to see the variables getting thrown in?
    That is the only thing I can think of that we do not know about.

    PHP Code:
    ********************************************************************************

    SELECT /* ReadingsDataAccess */ mi.end_dt-(15/60)/24 AS reading_start,
    mi.usage_nbr AS USAGE,
    arp.rate_period_type_cd AS rpt
    FROM METER_INTERVAL mi
    ACCOUNT_DEVICE adACCOUNT_RATE_PERIOD arp
    WHERE mi
    .org_id '4' AND
    mi.device_id=ad.device_id AND
    ad.org_id '4' AND
    ad.cust_id '1033' AND
    ad.svc_type_cd 'E' AND
    ad.prem_seq_nbr AND
    mi.org_id arp.org_id AND
    arp.org_id ad.org_id AND
    arp.cust_id ad.cust_id AND
    arp.svc_type_cd ad.svc_type_cd AND
    arp.prem_seq_nbr ad.prem_seq_nbr AND
    arp.rate_plan_cd 'Residential TOU' AND
    arp.usage_qty IS NOT NULL AND
    (
    mi.end_dt -(15/60)/24) >= arp.start_dt AND
    (
    mi.end_dt -(15/60)/24) < arp.end_dt AND
    mi.end_dt SYSDATE-AND
    mi.end_dt <= SYSDATE-AND
    mi.end_dt <= arp.end_dt

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        4      0.04       0.03          0          0          0           0
    Execute      4      0.00       0.00          0          0          0           0
    Fetch      196      0.09       0.06          0        344          0         384
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      204      0.13       0.10          0        344          0         384

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

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  FILTER  (cr=86 r=0 w=0 time=12770 us)
         
    96   NESTED LOOPS  (cr=86 r=0 w=0 time=12292 us)
          
    9    NESTED LOOPS  (cr=16 r=0 w=0 time=2223 us)
          
    9     INDEX RANGE SCAN AC_RPRD_TYPCD_IX (cr=8 r=0 w=0 time=1561 us)(object id 144472)
          
    9     INDEX RANGE SCAN ACC_DEV_ACC_DEV_PK (cr=8 r=0 w=0 time=461 us)(object id 144043)
         
    96    INDEX RANGE SCAN MTRINT_USAGE_NBR_IX (cr=70 r=0 w=0 time=9389 us)(object id 144257
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Because your using hard coded values as opposed to bind variables then oracle will create a new execution plan (it wont use the existing one with bind variables) and it will use the values of the hard coded variables to determine selectivity. This means that it may come up with a better plan for hard coded variables than for bind variables as it can use the histogram analysis data to get a better estimate for its execution plan. Obviously we try and avoid hard coded variables as it leads to repeated parsing but in this case it may lead to better execution.

    If you are using 8i or 9i then I think there is a way of viewing the bind variables , see http://www.revealnet.com/newsletter-v4/0603_A.htm for more info.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Thanks for the feedback Alan.
    I'll try to do some testing using the bind variables.
    - 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
  •