Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    47

    Unanswered: performance degradation when going from 8.1.6 to 9 (bis with exec plan)

    Under 8.1.6, it takes 3s and this is the exec plan :
    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 7 173
    SORT GROUP BY 7 777 173 :Q27968013 P->S QC (RANDOM)
    SORT GROUP BY 7 777 173 :Q27968012 P->P HASH
    HASH JOIN 7 777 170 :Q27968012 PCWP
    HASH JOIN 14 1 K 168 :Q27968010 P->P HASH
    HASH JOIN 64 4 K 166 :Q27968008 P->P HASH
    HASH JOIN 177 12 K 164 :Q27968006 P->P HASH
    HASH JOIN 177 10 K 162 :Q27968004 P->P HASH
    HASH JOIN 258 13 K 149 :Q27968002 P->P HASH
    NESTED LOOPS 258 11 K 131 :Q27968000 S->P HASH
    INDEX RANGE SCAN PK_CALENDRIER 1 5 1
    TABLE ACCESS BY INDEX ROWID AG_REFERENCES 16 K 610 K 130
    INDEX RANGE SCAN PK_AG_REFERENCES 16 K 27
    TABLE ACCESS FULL REFERENCE 33 K 356 K 17 :Q27968001 P->P HASH
    TABLE ACCESS FULL MODELE 22 K 178 K 12 :Q27968003 P->P HASH
    TABLE ACCESS FULL FAMILLE 205 1 K 1 :Q27968005 P->P HASH
    TABLE ACCESS FULL METAFAMILLE 28 224 1 :Q27968007 P->P HASH
    TABLE ACCESS FULL RAYON 3 60 1 :Q27968009 P->P HASH
    TABLE ACCESS FULL DEPARTEMENT 1 12 1 :Q27968011 P->P HASH

    Under 9.2 it takes 1mn 5s and the exec plan is :
    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 21 113
    SORT GROUP BY 21 2 K 113 :Q482007 P->S QC (RANDOM)
    SORT GROUP BY 21 2 K 113 :Q482006 P->P HASH
    HASH JOIN 21 2 K 111 :Q482006 PCWP
    TABLE ACCESS FULL MODELE 22 K 224 K 3 :Q482004 P->P HASH
    HASH JOIN 4 K 492 K 107 :Q482005 P->P HASH
    HASH JOIN 4 K 438 K 103 :Q482002 P->P HASH
    TABLE ACCESS FULL FAMILLE 205 1 K 2 :Q482001 P->P BROADCAST
    TABLE ACCESS BY INDEX ROWID AG_REFERENCES 1 54 123 :Q482002 PCWC
    NESTED LOOPS 1 K 152 K 100 :Q482002 PCWP
    NESTED LOOPS 6 258 7 :Q482002 PCWP
    HASH JOIN 6 186 6 :Q482002 PCWP
    NESTED LOOPS 3 75 3 :Q482000 S->P BROADCAST
    INDEX UNIQUE SCAN PK_CALENDRIER 1 5
    INLIST ITERATOR
    TABLE ACCESS BY INDEX ROWID RAYON 1 20 2
    INDEX RANGE SCAN PK_RAYON 6 1
    TABLE ACCESS FULL METAFAMILLE 32 192 2 :Q482002 PCWP
    INLIST ITERATOR :Q482002 PCWP
    TABLE ACCESS BY INDEX ROWID DEPARTEMENT 1 12 1 :Q482002 PCWP
    INDEX UNIQUE SCAN PK_DEPARTEMENT 1 :Q482002 PCWP
    INDEX RANGE SCAN PK_AG_REFERENCES 248 26 :Q482002 PCWP
    TABLE ACCESS FULL REFERENCE 33 K 423 K 3 :Q482003 P->P HASH

    Any suggestion to have at least the same performance we have now with Oracle 8i.

    NB : the box for 9i is better than that in which we have the 8.1.6 (and the 9i SGA is bigger than that in 8i).

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Its a bit difficult to interpret your execution plan but it looks like the 9i one is going for the indexes rather than the full table scans for 8i. You may want to tinker with the optimizer_index* parameters to make it less likely to go for the index route. Also increasing dbfile_multiblock_read_count (sp?) may help as this tells the optimizer that FTS are cheaper.

    Alan

Posting Permissions

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