Big speed difference for the same query executed on 2 different Oracle schemas

I have 2 different schemas installed on the same Oracle server (v12c).

On each of these 2 schemas, I have the same DB LINK which points to a distant database which allows me to read a table (size ±12,000 rows, a few basic columns (no blob)).

When I execute/compile in TOAD a SELECT * from TABLE@DBLINK on the first schema: 140ms

When I execute/compile in TOAD a SELECT * from TABLE@DBLINK on the second schema: 900ms

I've compared the two schemas quickly, and they seem identical (same tablespaces, same grants, etc.).

What could explain this slowness?

Should I compare the differences between these schemas in depth with a tool, if that makes sense?

Execution plan schema 1

Execution plan schema 2

Apparently your "table" isn't a table, but a view that joins multiple tables on the remote database. And, you aren't simply querying the table by itself, but joining it to a local table (COUNTRY). Those differences matter.

In one case Oracle is merging the view and using the local database as the driving site, requiring it to pull all the pieces together locally and perform the joins there. In the other scenario, it is not merging the view and executing it entirely on the remote.

Try adding these hints (match the alias to the table alias, which you will want to add):

SELECT /*+ driving_site(x) no_merge(x) */ * from VIEW@DBLINK x

As to why it is different in one schema than another: there are many potential factors that might change it. The local table COUNTRY may be a different size in one schema than the other, or have different statistics, or be missing statistics in one. Your links may be pointed to two different databases even if the link name is the same; they may be using two different connect accounts which could be resolving to different objects on the remote. etc. It's enough to know that the smallest differences can result in considerably different plans; variability of plan like that is quite common.