2 identical SIDS but different execution plans and different ordering results

[Unna]

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!

[tamilselvan]

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.

[Unna]

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!