Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27

    Unanswered: explain plans different on production, performance issues

    haven't posted in a while, but i'm stuck in a tuning rut...already did all the statspack goodness, tkprof, index tuning, etc...

    our dev and prod servers are "close" as far as the data and hardware go.

    actually the dev server has 2 CPUS, the prod has 4

    anyway, a query that takes 45-60 seconds on dev, is taking upwards of 15mins on prod. and this is during times of NO connected users.

    all my ratios are 99%+, everything is checking out...there are some waits showingup in the stats pack report, but it doesn't seem to add up

    i've been staring at the explain plan from both servers, trying to see if this is somewhere i should start...can someone help me out? take a look at these and tell me if anything pops out?

    DEV (takes under 1 min):
    SELECT STATEMENT Cost=22 Cardinality=1 Bytes=199
    SORT UNIQUE Cost=22 Cardinality=1 Bytes=199
    NESTED LOOPS Cost=16 Cardinality=1 Bytes=199
    NESTED LOOPS Cost=14 Cardinality=1 Bytes=187
    NESTED LOOPS Cost=12 Cardinality=1 Bytes=157
    NESTED LOOPS Cost=10 Cardinality=1 Bytes=114
    NESTED LOOPS Cost=9 Cardinality=1 Bytes=87
    INDEX FAST FULL SCAN Object name=IX_FR_CALLSIGN Cost=4 Cardinality=4 Bytes=124
    TABLE ACCESS BY INDEX ROWID Object name=L_LO Cost=2 Cardinality=1 Bytes=56
    INDEX UNIQUE SCAN Object name=PK_L_LO Cost=1 Cardinality=1
    INDEX RANGE SCAN Object name=IX_L_HD_STATUS Cost=1 Cardinality=1 Bytes=27
    INLIST ITERATOR
    INDEX RANGE SCAN Object name=PK_L_EN Cost=2 Cardinality=1 Bytes=43
    TABLE ACCESS BY INDEX ROWID Object name=FR_GENERAL Cost=2 Cardinality=1 Bytes=30
    INDEX UNIQUE SCAN Object name=PK_FR_GENERAL Cost=1 Cardinality=1
    INDEX RANGE SCAN Object name=PK_FR_GENERAL Cost=2 Cardinality=1 Bytes=12

    PROD (15mins )
    Explain Plan
    SELECT STATEMENT (Cost: 2011)
    SORT UNIQUE (Card: 1, Bytes: 232, Cost: 2011)
    NESTED LOOPS (Card: 1, Bytes: 232, Cost: 2002)
    NESTED LOOPS (Card: 1, Bytes: 192, Cost: 2000)
    NESTED LOOPS (Card: 1, Bytes: 156, Cost: 1997)
    NESTED LOOPS (Card: 1, Bytes: 142, Cost: 1995)
    NESTED LOOPS (Card: 1, Bytes: 95, Cost: 1993)
    TABLE ACCESS BY INDEX ROWID L_LO (Card: 1, Bytes: 63, Cost: 1991)
    INDEX RANGE SCAN IX_LO_GEO (Card: 390501, Cost: 701)
    TABLE ACCESS BY INDEX ROWID L_HD (Card: 1, Bytes: 32, Cost: 2)
    INDEX UNIQUE SCAN PK_L_HD (Card: 1, Cost: 1)
    INLIST ITERATOR ()
    INDEX RANGE SCAN PK_L_EN (Card: 1, Bytes: 47, Cost: 2)
    INDEX RANGE SCAN PK_FR_GENERAL (Card: 1, Bytes: 14, Cost: 2)
    INDEX RANGE SCAN IX_FR_CALLSIGN (Card: 1, Bytes: 36, Cost: 3)
    TABLE ACCESS BY INDEX ROWID FR_GENERAL (Card: 1, Bytes: 40, Cost: 2)
    INDEX UNIQUE SCAN PK_FR_GENERAL (Card: 1, Cost: 1)


    i've been a DBA for a few years now, so I have a decent base of experience to go on here...i'm just running out of ideas...

    thanks in advance

  2. #2
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    here is some more info...from tkprof...this is the primary query that is giving us a headache (i changed some parts of it, but the structure is the same)

    SELECT DISTINCT c.callsign,
    e.frequency_assigned,
    d.location_type_code,
    d.location_number,
    round(d.latitudesec / 3600, 6) as lat_dec_deg,
    round(d.longitudesec / -3600, 6) as lon_dec_deg,
    d.latitudesec,
    d.longitudesec,
    d.Location_City,
    d.Location_County,
    d.Location_State,
    d.Location_Address,
    f.OWNED,
    f.PARTNERS,
    f.OPT,
    f.LEASE_BACK,
    f.CONTRACTED,
    f.MANAGED,
    e.POWER_ERP,
    c.ENTITY_NAME,
    b.GRANT_DATE,
    b.radio_service_code
    FROM (SELECT DISTINCT link as callsign, frequency_assigned
    FROM uls_fr_general
    WHERE frequency_assigned = xxxx) a,
    uls_l_hd b,
    uls_l_en c,
    uls_l_lo d,
    uls_l_fr e,
    uls_fr_general f
    WHERE b.callsign = a.callsign
    AND b.license_status = 'A'
    AND b.history = 'Active'
    AND c.callsign = b.callsign
    AND c.history = b.history
    AND c.entity_type IN ('L', 'O')
    AND d.CALLSIGN = b.CALLSIGN
    AND d.HISTORY = b.history
    AND d.latitudesec Between 111 And 111
    AND d.longitudesec Between 111 And 111
    AND e.callsign = d.callsign
    AND e.LOCATION_NUMBER = d.LOCATION_NUMBER
    AND e.FREQUENCY_ASSIGNED = a.frequency_assigned
    AND e.HISTORY = d.history
    AND f.link = e.callsign
    AND f.location_number = e.location_number
    AND f.antenna_number = e.antenna_number
    AND f.FREQUENCY_ASSIGNED = e.frequency_assigned

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.10 0.09 17 588 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 5 23.86 70.04 298452 321888 0 404
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 7 23.96 70.14 298469 322476 0 404

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 1030 (BLAH)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    404 SORT UNIQUE
    412 NESTED LOOPS
    412 NESTED LOOPS
    412 NESTED LOOPS
    404 NESTED LOOPS
    409 NESTED LOOPS
    7371 INDEX FAST FULL SCAN IX_FR_CALLSIGN (object id 40036)
    409 TABLE ACCESS BY INDEX ROWID L_LO
    7371 INDEX UNIQUE SCAN PK_L_LO (object id 38385)
    404 INDEX RANGE SCAN IX_L_HD_STATUS (object id 40037)
    412 INLIST ITERATOR
    412 INDEX RANGE SCAN PK_L_EN (object id 38382)
    412 TABLE ACCESS BY INDEX ROWID FR_GENERAL
    412 INDEX UNIQUE SCAN PK_FR_GENERAL (object id 38375)
    412 INDEX RANGE SCAN PK_FR_GENERAL (object id 38375)


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    404 SORT (UNIQUE)
    412 NESTED LOOPS
    412 NESTED LOOPS
    412 NESTED LOOPS
    404 NESTED LOOPS
    409 NESTED LOOPS
    7371 INDEX (FAST FULL SCAN) OF 'IX_FR_CALLSIGN'
    (NON-UNIQUE)
    409 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'L_LO'
    7371 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_L_LO'
    (UNIQUE)
    404 INDEX (RANGE SCAN) OF 'IX_L_HD_STATUS' (NON-UNIQUE)
    412 INLIST ITERATOR
    412 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_L_EN'
    (UNIQUE)
    412 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'FR_GENERAL'
    412 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_FR_GENERAL'
    (UNIQUE)
    412 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_FR_GENERAL'
    (UNIQUE)

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    what is the last analyzed date for the tables in each database?
    that would be my initial guess as to why plans differ.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Quote Originally Posted by The_Duck
    what is the last analyzed date for the tables in each database?
    that would be my initial guess as to why plans differ.
    the tables were analyzed 2 weeks ago for both instances.

    i should also add that tkprof result is from dev...here are some metrics the prod dba sent me regarding the query on prod:

    Avg Time/Execution: 356.861
    Disk Reads/Execution: 83385
    Buffer Gets/Execution: 902010
    Sorts/Execution: 1
    First Load Time: 2006-04-17/10:16:46

    Avg Time/Execution: 198.494
    Disk Reads/Execution: 32939
    Buffer Gets/Execution: 518607
    Sorts/Execution: 1
    First Load Time: 2006-04-17/16:35:10

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I'd prefer you prove that and post it for both databases, but I guess I can take your word for it. What analyze statement was used? Please post.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    any good reason why you have a sub-select and yet that same table is also in the from clause?
    Code:
    FROM (SELECT DISTINCT link as callsign, frequency_assigned
    FROM uls_fr_general
    WHERE frequency_assigned = xxxx) a,
    uls_l_hd b,
    uls_l_en c,
    uls_l_lo d,
    uls_l_fr e,
    uls_fr_general f
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Quote Originally Posted by The_Duck
    I'd prefer you prove that and post it for both databases, but I guess I can take your word for it. What analyze statement was used? Please post.
    from dev: LAST_ANALYZED 4/1/2006 9:23:47 AM

    can't get to prod right now, but it was analyzed on 4/7/2006

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    what are the columns of the PK on this table?
    uls_l_lo

    also, what are these parameter values set to on each database?
    optimizer_index_caching
    optimizer_index_cost_adj
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by aglio412
    from dev: LAST_ANALYZED 4/1/2006 9:23:47 AM

    can't get to prod right now, but it was analyzed on 4/7/2006
    well, they are a week off.
    What is the size difference between production and dev for the table:
    uls_l_lo

    that seems to be the largest table in the query (right?)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Quote Originally Posted by The_Duck
    any good reason why you have a sub-select and yet that same table is also in the from clause?
    Code:
    FROM (SELECT DISTINCT link as callsign, frequency_assigned
    FROM uls_fr_general
    WHERE frequency_assigned = xxxx) a,
    uls_l_hd b,
    uls_l_en c,
    uls_l_lo d,
    uls_l_fr e,
    uls_fr_general f
    not sure, i didn't write the query...problem is, it was running in seconds (up to 2-3mins) 3 weeks ago, not it's taking 1min-15mins.

    going to play around with the query a bit...any other input/advice would be appreciated

  11. #11
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Quote Originally Posted by The_Duck
    well, they are a week off.
    What is the size difference between production and dev for the table:
    uls_l_lo

    that seems to be the largest table in the query (right?)
    fr_general actually has 50mil+ rows, the others are less than 3-7mil on both servers

    as far as the stats being a week off, what is the major effect there? the stats were not regularly imported/exported between the instances.

    EDIT: i realize the plans are different because of the stats being different...i'm just trying to get a handle on some tuning tips at this point...
    EDIT: the stats were collected on dev, for the entire schema (using dbms_stats, i think, i didn't schedule it, but i'm assuming that's how the other dba did it)...not sure how the prod DBA did it.
    Last edited by aglio412; 04-18-06 at 17:11.

  12. #12
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Quote Originally Posted by The_Duck
    what are the columns of the PK on this table?
    uls_l_lo

    also, what are these parameter values set to on each database?
    optimizer_index_caching
    optimizer_index_cost_adj
    DEV:
    optimizer_index_cost_adj 100
    optimizer_index_caching 0

    PROD:
    whatever the default values are

    PK on ULS_L_LO:
    add constraint PK_L_LO primary key (CALLSIGN, LOCATION_NUMBER, HISTORY)

    thanks, for all of your help so far

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by aglio412
    as far as the stats being a week off, what is the major effect there? the stats were not regularly imported/exported between the instances.

    In production I am assuming you have at least inserts of new data into those tables (possible chances of deletes or updates?):
    1. This now changes what your statistics were. There is now different kinds/amounts/types of data within the tables.
    2. Depending on how active those tables are then over a 2-3 week period you would need to update your statistics so that oracle establish the proper plan and path

    Well, let's say no data gets inserted/updated/deleted in development:
    1. statistics will remain fresh since there is no change in data or rows

    IF data gets inserted/updated/deleted in development:
    1. statistics be out of synch with production if they both are not analyzed at the same intervals.

    this happened to me a while back when attempting to evaluate plans of production vs. development. It drove me crazy until I realized I was analyzing production DAILY and not analyzing development AT ALL.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Quote Originally Posted by The_Duck
    In production I am assuming you have at least inserts of new data into those tables (possible chances of deletes or updates?):
    1. This now changes what your statistics were. There is now different kinds/amounts/types of data within the tables.
    2. Depending on how active those tables are then over a 2-3 week period you would need to update your statistics so that oracle establish the proper plan and path

    Well, let's say no data gets inserted/updated/deleted in development:
    1. statistics will remain fresh since there is no change in data or rows

    IF data gets inserted/updated/deleted in development:
    1. statistics be out of synch with production if they both are not analyzed at the same intervals.

    this happened to me a while back when attempting to evaluate plans of production vs. development. It drove me crazy until I realized I was analyzing production DAILY and not analyzing development AT ALL.
    see, i totally follow where you're going...but those tables in production are not updated daily. they are periodically changed with mass inserts/updates. they are duplicate tables of a sql server database that another agency maintains and populates our tables when "it's time" i have no control over it...and from what i know (the prod dba has been pretty helpful) the stats are gathered following the synching of data (from the other agency's sql server db)

    i ran some queries to see if the indexes are out of control on dev, and they look fine...they were the prod dba's scripts...he came to the same conclusion on prod.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by aglio412
    PK on ULS_L_LO:
    add constraint PK_L_LO primary key (CALLSIGN, LOCATION_NUMBER, HISTORY)
    notice the PK order vs. the order in your where clause:
    Code:
    WHERE b.callsign = a.callsign
    AND b.license_status = 'A'
    AND b.history = 'Active'
    AND c.callsign = b.callsign
    AND c.history = b.history
    AND c.entity_type IN ('L', 'O')
    AND d.CALLSIGN = b.CALLSIGN
    AND d.HISTORY = b.history
    AND d.latitudesec Between 111 And 111
    AND d.longitudesec Between 111 And 111
    AND e.callsign = d.callsign
    AND e.LOCATION_NUMBER = d.LOCATION_NUMBER
    AND e.FREQUENCY_ASSIGNED = a.frequency_assigned
    AND e.HISTORY = d.history
    AND f.link = e.callsign
    AND f.location_number = e.location_number
    AND f.antenna_number = e.antenna_number
    AND f.FREQUENCY_ASSIGNED = e.frequency_assigned
    you should attempt to order your where clause in synch with the order of your PKs or indexes you want to use; like this possibly:
    Code:
    WHERE b.callsign = a.callsign
    AND b.license_status = 'A'
    AND b.history = 'Active'
    AND c.callsign = b.callsign
    AND c.history = b.history
    AND c.entity_type IN ('L', 'O')
    AND d.CALLSIGN = b.CALLSIGN
    AND e.callsign = d.callsign
    AND e.LOCATION_NUMBER = d.LOCATION_NUMBER
    AND d.HISTORY = b.history
    AND e.HISTORY = d.history
    AND d.latitudesec Between 111 And 111
    AND d.longitudesec Between 111 And 111
    AND e.FREQUENCY_ASSIGNED = a.frequency_assigned
    AND f.link = e.callsign
    AND f.location_number = e.location_number
    AND f.antenna_number = e.antenna_number
    AND f.FREQUENCY_ASSIGNED = e.frequency_assigned
    - 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
  •