Beware of explain plan in combination with alter session set current_schema

The situation: do an explain plan for a SQL which contains table names without schema qualifiers, which reside in a schema other than my own. To explain the SQL without any modifications, alter session set current_schema was used.

connect me@db

alter session set current_schema=other

explain plan for
select this, that 
from
there
;

select * from table(dbms_xplan.display(format=>'typical +alias'))

It would always give me a bogus explain plan, which not only belonged to a totally different SQL, but it would also be the same identical explain plan, no matter what SQL I explained!

EXPLAIN PLAN will generate the plan table in my own schema. However, in this situation, when selecting the explain plan in the last line, the session is still altered to assume a different „current“ schema, resulting in displaying the other schema’s plan_table, which will constantly give me bogus results.

Kommentar verfassen

Bitte logge dich mit einer dieser Methoden ein, um deinen Kommentar zu veröffentlichen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s