CODEWITHSUNDEEP
databaseoracle-databaseperformancedatabase-performance
sinedsem
sinedsem

Reputation: 5723

Oracle SQL different plan for same query on different environments

Two very similar environments, same database structure, one query, two execution plans: one with nested loops and indexes (fast), another with hash joins and full access (slow).

Which steps should I perform to find the cause of plans difference?

Upvotes: 0

Views: 1753

Answers (1)

Ikrom
Ikrom

Reputation: 484

  1. Make sure that the Database version is same on both env.:

SELECT * FROM V$VERSION;

  1. And then check your table's statistics on both environments:

    SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME = 'YOUR_TABLE_NAME';

  2. If the statistics are different then run below script on both env.:

BEGIN EXEC dbms_stats.gather_table_stats('YOUR_SCHEMA','YOUR_TABLE',cascade=>TRUE); END;

Upvotes: 2

Related Questions