Re: Problems during explain plan

Reid Lai wrote:
>
> Recently, our project team has discovered a problem when we issue explain
> plan statement at the same time. I know we have a system table
> SYS.PLAN_TABLE and all result generated by EXPLAIN PLAN will be stored in
> this table. It works fine when a single developer to do so at a time.
>
> However, this will be a choas when we issue EXPLAIN PLAN statement at the
> same time. We could see that we cannot know which rows belongs to which
> user. And also all the result is accumulated when we issue the same
> statement again. For the time being, we can only delete all the rows in
> PLAN_TABLE and restrict only one developer to issue EXPLAIN PLAN at a time.

  You can use EXPLAIN_PLAN SET STATEMENT_ID = sysdate FOR query to identify the explain result.

  Moreover, you can try to run utlxplan.sql for each user that will generate the explain plan results in their schemas. Hence, there will be no any chaos.

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.