Plan stability
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Plan stability

  1. #1
    Join Date
    Dec 1999
    Location
    North Carolina
    Posts
    4
    I have two execution plans for the same query from two different machines.
    Both are running on Tru64 v5.1, Oracle 8.1.6.3
    SORT_AREA_SIZE for plan1 is 47MB, plan2 is 12MB

    *************************************************
    1)
    SELECT STATEMENT Cost = 63297
    NESTED LOOPS
    NESTED LOOPS
    HASH JOIN
    TABLE ACCESS FULL TABLE4
    HASH JOIN
    TABLE ACCESS FULL TABLE3
    TABLE ACCESS FULL TABLE1
    TABLE ACCESS BY INDEX ROWID TABLE5
    INDEX UNIQUE SCAN SYS_C001602
    TABLE ACCESS BY INDEX ROWID TABLE2
    INDEX UNIQUE SCAN SYS_C001696
    ************************************************
    2)
    SELECT STATEMENT Cost = 45571
    NESTED LOOPS
    NESTED LOOPS
    MERGE JOIN
    SORT JOIN
    MERGE JOIN
    SORT JOIN
    TABLE ACCESS FULL TABLE3
    SORT JOIN
    TABLE ACCESS FULL TABLE4
    SORT JOIN
    TABLE ACCESS FULL TABLE1
    TABLE ACCESS BY INDEX ROWID TABLE5
    INDEX UNIQUE SCAN SYS_C002545
    TABLE ACCESS BY INDEX ROWID TABLE2
    INDEX UNIQUE SCAN SYS_C002617
    *************************************************

    There are approximately 200000 more records in all the tables for plan2 than for plan1 tables.
    1) Why is plan2 sorting?
    2) How does the init parameter SORT_AREA_SIZE effect the plan stability?

  2. #2
    Join Date
    Nov 2001
    Posts
    335
    Are you sure that sort_area_size is the only factor? What about hash_area_size ,hash_join_enabled ?
    There some other "hash" setting in init.ora which being used by optimizer to decide what to use: sort or hash join.
    My point is that make sure that all other init.ora parameters are exactly the same except sort_area_size.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

Posting Permissions

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


Click Here to Expand Forum to Full Width