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

    Unanswered: CBO confusing me

    okay, I JUST analyzed this schema.
    I traced a session, TKPROF the trace and also used EXPLAIN on the tkprof to verify the path.

    Look at the difference between what the explain-plan indexes should be using and what actually got used.

    Crazy. I cannot understand it.
    PHP Code:
    SELECT
      
    /* getCustomerForecast */ MIN(f.intvl_nbr) AS intvl_nbr,
      
    SUM(f.usage_nbr 4) * 0.46938775510204084 AS usage_nbr,
      
    COUNT(*) AS    sample_count
    FROM
      ACCOUNT ad
    ,
      
    USAGE_FORECAST f
    WHERE
      ad
    .org_id      = :AND
      
    ad.svc_type_cd = :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.01       0.00          3        199          0          25
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.01       0.01          3        199          0          25

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

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    25  SORT GROUP BY (cr=199 r=3 w=0 time=8009 us)
         
    96   NESTED LOOPS SEMI (cr=199 r=3 w=0 time=6826 us)
         
    96    MERGE JOIN CARTESIAN (cr=5 r=3 w=0 time=1982 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=67 us)(object id 32427)
         
    96     BUFFER SORT (cr=4 r=3 w=0 time=1764 us)
         
    96      INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=4 r=3 w=0 time=877 us)(object id 32710)
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=194 r=0 w=0 time=3655 us)(object id 40046)


    Rows     Execution Plan
    -------  ---------------------------------------------------
          
    0  SELECT STATEMENT   GOALCHOOSE
         25   SORT 
    (GROUP BY)
         
    96    MERGE JOIN (CARTESIAN)
         
    96     NESTED LOOPS (SEMI)
          
    1      TABLE ACCESS   GOALANALYZED (BY INDEX ROWIDOF
                     
    'ACCOUNT'
         
    96       INDEX   GOALANALYZED (RANGE SCANOF 'ACCOUNT_PK'
                      
    (UNIQUE)
         
    96      INDEX   GOALANALYZED (RANGE SCANOF
                     
    'USAGE_MI_END_DT_IX' (NON-UNIQUE)
         
    96     BUFFER (SORT)
          
    0      INDEX   GOALANALYZED (RANGE SCANOF
                     
    'USAGE_FORECAST_IDX_002' (NON-UNIQUE)

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

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    And don't get me started on this SAME query (not counting the multiplier) which comes from the application querying the same tables.

    Why would this take so long compared to me running it by hand above?
    PHP Code:
    select /* getCustomerForecast */ min(f.intvl_nbr) as intvl_nbr,
      
    sum(f.usage_nbr 4) * 0.875 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.03       0.02          0          0          0           0
    Fetch       10      9.96      10.10          0      15175          0          96
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      9.99      10.13          0      15175          0          96

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

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    96  SORT GROUP BY (cr=15175 r=0 w=0 time=10106221 us)
         
    96   NESTED LOOPS SEMI (cr=15175 r=0 w=0 time=10103134 us)
         
    96    MERGE JOIN CARTESIAN (cr=5 r=0 w=0 time=2358 us)
          
    1     INDEX RANGE SCAN ACCOUNT_CRTD_DT_IX (cr=1 r=0 w=0 time=65 us)(object id 32427)
         
    96     BUFFER SORT (cr=4 r=0 w=0 time=1850 us)
         
    96      INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=4 r=0 w=0 time=630 us)(object id 32710)
         
    96    INDEX RANGE SCAN USAGE_METER_INT_IND_01 (cr=15170 r=0 w=0 time=10098626 us)(object id 40046
    - 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
  •