Re: Problems during explain plan

Reid

There is no reason why each user can't have their own copy of plan table. Get the users to run $ORACLE_HOME/dbms/admin/utlxplan.sql. Alternatively you could partition the plan table by user or by session id using the STATEMENT_ID whereby each user sets STATEMENT_ID = user for instance, or use as script such as:
SET echo OFF
SET pagesize 1000
SET verify OFF
SET feedback OFF

COLUMN sessionid NEW_VALUE sid
SET termout off
SELECT LTRIM( USERENV( 'SESSIONID' ) ) "sessionid" FROM dual; SET termout on

COLUMN id FORMAT 99
COLUMN operation FORMAT a32
COLUMN options FORMAT a12
COLUMN object_name FORMAT a30

DELETE FROM plan_table WHERE statement_id = '&&sid'; COMMIT; SPOOL explain.out

EXPLAIN PLAN
  SET STATEMENT_ID = '&&sid' FOR
 <<put your SQL statement here>>

Ian Stevenson
co-author of Oracle Design, published by O'Reilly and Associates, available April 1997

Reid Lai <reidlai_at_hk.super.net> wrote in article <01bc1a30$4f78cb00$0b0a40ca_at_hk.super.net.hk.super.net>...
> 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
>
  Received on Fri Feb 14 1997 - 00:00:00 CST