2 identical SIDS but different execution plans and different ordering results
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: 2 identical SIDS but different execution plans and different ordering results

  1. #1
    Join Date
    Nov 2000
    Posts
    162

    Unhappy

    Good morning all,

    We have 2 identical databases siting on the same Unix HP box, having same environment settings, I will expect to have same execution plan and same query result. Yet the execution plan turns out to be different. The database that used indexes scan runs faster than the one used full table scan. With large data records coming from client soon, will significantly slow down the run time especially on the database that uses full table scan.

    Here is the query that runs on both databases,

    SELECT pay, app, refer, enter_date,
    DECODE(ssn, 11,'USA', 22,'NA', 33,'SD',
    44,'RA', ssn) ssn,
    DECODE(SUBSTR(pay,2,1), ssn||SUBSTR(refer,3,1),app),
    amt
    FROM payment
    WHERE refer IN ('TEP','AAP','ST') AND
    ((app LIKE 'MAI%') OR (app LIKE 'CUT%'))
    ORDER BY 1,DECODE(ssn, 11,'USA', 22,'NA', 33,'SD',
    44,'RA', ssn) ssn,
    DECODE(SUBSTR(pay,2,1), ssn||SUBSTR(refer,3,1),app);

    The execution plan for database 1 is:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=77 Bytes=42
    35)

    1 0 SORT (ORDER BY) (Cost=120 Card=77 Bytes=4235)
    2 1 TABLE ACCESS (FULL) OF 'PAYMENT' (Cost=118 Card=77 Byte
    s=4235)


    Statistics
    ----------------------------------------------------------
    9 recursive calls
    7 db block gets
    1934 consistent gets
    660 physical reads
    0 redo size
    23924 bytes sent via SQL*Net to client
    2788 bytes received via SQL*Net from client
    32 SQL*Net roundtrips to/from client
    0 sorts (memory)
    1 sorts (disk)
    459 rows processed


    The execution plan for database 2 is,
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=69 Card=77 Bytes=908
    6)

    1 0 SORT (ORDER BY) (Cost=69 Card=77 Bytes=9086)
    2 1 INLIST ITERATOR
    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENT' (Cost=66 C
    ard=77 Bytes=9086)

    4 3 INDEX (RANGE SCAN) OF 'CONT_INDEX' (NON-UNIQUE) (C
    ost=3 Card=77)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    1634 consistent gets
    0 physical reads
    0 redo size
    23891 bytes sent via SQL*Net to client
    2788 bytes received via SQL*Net from client
    32 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    459 rows processed

    I placed a hint for index on the query before it was running on database 2, but the same execution plan was generated. Can you explain why 2 different plans are generated and how to use index on database 2 to speed up the query performance? The query results in both databases are also different in the order. The sorting of the query is critical. Can anyone suggest a solution of how to have the same ordering on the results?


    Thanks!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1 check up init.ora files are same.
    2 If CBO is used, analyze tables and indexes.
    3 Drop unwanted indexes.
    4 Run explain plan and see the results on both databases.



  3. #3
    Join Date
    Nov 2000
    Posts
    162
    Tamilselvan,

    Thanks, I have the problem resolved. It turns out to be the rule base optimizer. Once I dropped the statistics on both databases, they both used rule base and the execution plans are same, and the order on the result seems to be right. However, I don't understand why cost base uses different execution plans on both databases, while all settings are same including same init.ora settings and indexes? Also, how does the cost base and rule base affect the query result, especially the order of the result? Please advise.


    Many thanks!

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