SqlDbx Forum

garbuya

Member

Problem with Oracle common PLAN_TABLE

It looks like SqlDbx is locking a common PLAN_TABLE until the results window is open
I ran Query Plan and left the results open for one hour
During this time the common PLAN_TABLE remained locked and nobody was able to use it
Does anybody else have the same problem?

timeco

Member

Re: Problem with Oracle common PLAN_TABLE

Try using your local PLAN_TABLE:

1. create it using script below
2. configure Sqldbx to use it:   Tools -> Options -> Servers -> Oracle ->  PLAN TABLE  :  enter something like   SCHEMA1.YOUR_PLAN_TABLE

it has to be created though, and grant the appropriate DML rights to other users needed:
CREATE GLOBAL TEMPORARY TABLE SCHEMA1.YOUR_PLAN_TABLE
    (
      STATEMENT_ID      VARCHAR2 (30)
    , PLAN_ID           NUMBER
    , "TIMESTAMP"       DATE
    , REMARKS           VARCHAR2 (4000)
    , OPERATION         VARCHAR2 (30)
    , OPTIONS           VARCHAR2 (255)
    , OBJECT_NODE       VARCHAR2 (128)
    , OBJECT_OWNER      VARCHAR2 (30)
    , OBJECT_NAME       VARCHAR2 (30)
    , OBJECT_ALIAS      VARCHAR2 (65)
    , OBJECT_INSTANCE   INTEGER
    , OBJECT_TYPE       VARCHAR2 (30)
    , OPTIMIZER         VARCHAR2 (255)
    , SEARCH_COLUMNS    NUMBER
    , ID                INTEGER
    , PARENT_ID         INTEGER
    , DEPTH             INTEGER
    , POSITION          INTEGER
    , COST              INTEGER
    , CARDINALITY       INTEGER
    , BYTES             INTEGER
    , OTHER_TAG         VARCHAR2 (255)
    , PARTITION_START   VARCHAR2 (255)
    , PARTITION_STOP    VARCHAR2 (255)
    , PARTITION_ID      INTEGER
    , OTHER             LONG
    , OTHER_XML         CLOB
    , DISTRIBUTION      VARCHAR2 (30)
    , CPU_COST          INTEGER
    , IO_COST           INTEGER
    , TEMP_SPACE        INTEGER
    , ACCESS_PREDICATES VARCHAR2 (4000)
    , FILTER_PREDICATES VARCHAR2 (4000)
    , PROJECTION        VARCHAR2 (4000)
    , "TIME"            INTEGER
    , QBLOCK_NAME       VARCHAR2 (30)
    )
    ON COMMIT PRESERVE ROWS;