Explain plan gives different results depending on schema

Question

I have a query that joins a multi million row table to one containing hundreds of thousands of rows. Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both have select, insert and delete privileges on each of the tables however 'DEF' also has SELECT CATALOG role.

There are no synonyms pointing either user to different versions of the same table.

When performing an EXPLAIN plan from user 'ABC' we see NESTED LOOP as the join mechanism and the query just runs and runs. the same query when EXPLAINed from the 'DEF' user (the one with SELECT CATALOG) shows a HASH join. It seems that the HASH join is the more efficient in this case but that is largely irrelevant for the question.

My question is why would the optimizer choose different joins for the same query depending on which user is running the query ?

Answer 1

In general, if 2 queries are identical, they will have the same sql_id, and as a result, the same execution plan (which may change over the time, but it shouldn't depend on which user executes the query). (** It's oversimplified, because starting with 11g Oracle can generate multiple plans based on values of bind variables, presence of histograms ).

So first thing I'd check if they're really identical - run both of them, and check v$sql/v$sql_area .

Another thing is OPTIMIZER_MODE. When it's set to FIRST_ROWS(N), optimizer prefers NESTED LOOPS over HASH/MERGE JOINS ; when it's ALL_ROWS , HASH JOIN is more preferable for optimizer. It's quite common that different users set different optimizer mode , FIRST_ROWS is great for OLTP applications; ALL_ROWS is more suitable for DSS.

You can also check whether any outlines are used.

If you're on Oracle 11 and higher, check IS_BIND_AWARE, IS_BIND_SENSITIVE values in v$sql.

Finally, always check actual execution plan with dbms_xplan.display_cursor, don't rely too much on the output of EXPLAIN without executing the query :
- run query
- find sql_id in v$sql
- check plan SELECT * FROM table(dbms_xplan.display_cursor('[sql_id from previous step'],null));