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 ?

  • Whats about the indexes being used in those execution plan? Also it would be better if you could add execution plan for both of the operations.
    – atokpas
    Commented May 6, 2016 at 14:20
  • If you run really the same query with both users: run query as 'ABC', then run query as 'DEF' (you can cancel it once its parsed, you do not need to wait for the results). Find the sql_id of the statement, then check V$SQL_SHARED_CURSOR for the specific sql_id, where the reason is stated for creating another child cursor (and possibly a new execution plan) for the same SQL statement. Commented May 6, 2016 at 19:15

1 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));

  • Some good points there. Particularly the OPTIMIZER_MODE - will check each users setting as well as the actual SQL_ID. Commented May 9, 2016 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.