different performance with the same sql
I have one sql run in production database, it took 1 second, but in pre database, it ran for 5 hours. Pre is the same as production database. I am check sql plan for this sqlIf I ran explain plan for <sql statement>, then @$ORACLE_HOME/rdbms/admin/utlxpls.sql to get the plan which is the same as prod ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 70 | 7 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 70 | | | | 2 | NESTED LOOPS | | 1 | 70 | 7 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 1 | 36 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| PRW_VALID_TRANS | 1 | 10 |