We are using the same query for different country and when we used the country code US query is executing in 10 secs but when we are using different country code it is taking ~2Mins 30 secs.
Below are the query:
1) Query executing in ~10 secs:
Explain Plan :
Query 2 : Execution time is 2 mins 30 secs.
did you check DBQL for estimated vs. actual number of rows?
The optimizer's choice picking the index on transaction_country is bad.
There's a bad condition, comparing string to numeric, which results in a loss of statistics:
and FREE_CHAR_ATTRIBUTE_1 IN (20,10,30,50)
better change to
and FREE_CHAR_ATTRIBUTE_1 IN ('20','10','30','50')
Check if this helps.
What's the partitioning/indexes of this table?