Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Unanswered: Hierarchical query

    Any ideas on why a hierarchical query would run 2 sec in 8.1.7 and 30 min in 9.2.0.1? Same query, same code, different database.
    Michellea Southern-David

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    We've had other people reporting large performance differences between versions of 8 and 9, not yet with a decent explanation. Suggest you check the following

    1: Are stats gathered and up to date on both databases (same histograms as well if used?)

    2: Check the explain plan for both statements.

    3: Are the database parameters identical, hardware, SGA sizing, load etc

    Cheers
    Bill

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    For both databases:

    Set sql_trace=TRUE

    run your query.

    TKPROF the output with explain

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

  4. #4
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    thanks

    Thanks I will do and post after lunch.
    Michellea Southern-David

  5. #5
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    ok..here it is.

    I compared the two explain plans from 8.1.7 and 9i and found that in 9i the connect by statement runs twice where as it runs once in 8.1.7. I will copy paste into the next two messages.

    I don't understand why 9i is running the same query differently. This query has two hierarchical queries, one embedded into the other.

    The query is:

    select ITEM_UID, ITEM_NUMBER, LOCATION_UID, level, ITEM_TYPE || ':' || nvl(ITEM_SUBTYPE,'') ITEM_TYPE, ITEM_STATUS, ITEM_NUMBER
    from TRACKING.ITEMS
    where SUPERCEDED_BY_TRANSACTION_UID is null and
    ITEM_STATUS = 'IN TRANSIT' and
    ITEM_TYPE != 'BATCH'
    start with (ITEM_UID in (select ITEM_UID
    from TRACKING.ITEMS
    where SUPERCEDED_BY_TRANSACTION_UID is null and
    ITEM_TYPE != 'BATCH' and LOCATION_UID in (select LOCATION_UID
    from l_locations
    start with (LOCATION_UID in (select LOCATION_UID
    from l_locations
    where LOCATION_UID in (5199,5200,5201,5202,5203,5204,5205,5206,5207,5208 ,5209,5210,5211,5212,5213,5214,5215,5216,5217,5218 ,5219,5220,5221,5222,5223,5224,5225,5226,5227,5228 ,5229,5230,5231,5232,5233,5234,5235,5236,5237,5238 ,5239,5240,5241,5242,5243,5244,5245,5246,5247,5248 ,5249,5250,5251,5252,5253,5254,5255,5256,5257,5258 ,5259,5260,5261,5262,5263,5264,5265,5266,5267)))
    connect by prior LOCATION_UID = PARENT_LOCATION_UID)))
    connect by prior PARENT_ITEM_UID = ITEM_UID;
    Michellea Southern-David

  6. #6
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    8.1.7 explain plan

    Well the explain plans are too long to attach. 8.1.7 got 223 rows and 9i got 993 rows. Same data and same query. UG... I really think it is because of the hierarchical inside the other hierarchical.

    still working on this one...any ideas. I did make sure the db is the same size on 8.1.7 and 9i.
    Michellea Southern-David

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Are both tables identical (same number of rows, same indexes available etc). Also are the statistics up to date for both databases, if they are up to date have they been collected in the same manner (estimated, computed, histograms etc)?

    Hth
    Bill

  8. #8
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Same

    Yes, the data comes from a set of scripts that I run. This is a development environment. Both have the same data, the same query, the same server. I am not gathering stats on either. I just ran the explain plan once then turned all stats off.

    I think I will take this to Oracle. I will tell you guys what happens.
    Michellea Southern-David

  9. #9
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Oracle

    It is now in Oracle's hands. I have a Tar out there.
    Michellea Southern-David

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    attach a .txt/.prf file of both tkprof outputs.

    not just explain, but tkprof the trace to create a .prf and attach that.

    example:

    tkprof kod1_ora_23795.trc test_file explain=system/manager
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Thanks anyway

    Thanks Duck. But I do not need to now. Oracle says it is in the way the db parses. They changed the parsing hierarchy for hierarcies in 9i. I will tell you guys more when we get the info from oracle
    Michellea Southern-David

  12. #12
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Here is Why

    I found out why. I had to patch 9.2.0.1 to 9.2.0.2. There was a fix in 9.2.0.2.
    Michellea Southern-David

Posting Permissions

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