Re: Problems during explain plan

"Reid Lai" <reidlai_at_hk.super.net> writes: > Hello world,
>
> 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.
>
> Could anybody help to solve this problem?
> --
> Best Regards,
>
> Reid Lai
> mailto://reidlai@hk.super.net
> http://www.hk.super.net/~reidlai

Maybe it helps if you use

EXPLAIN PLAN
  SET STATEMENT ID = 'text'
  FOR statement

Give each statement an unique text, then you will be able to query the explain plan result of each individual statement. It also allows you to delete the results of one particular statement (in case you want to have an explain plan for an improved version of the statement).

See also Appendix B, Performance Diagnostic Tools, in the Application Developer's Guide.

Best regards, Gerrit Scholten. Received on Mon Feb 17 1997 - 00:00:00 CST