PLAN_TABLE会话间数据隔离的实现

最新推荐文章于 2024-11-05 10:52:54 发布

转载最新推荐文章于 2024-11-05 10:52:54 发布 · 1.1k 阅读

Oracle Database 专栏收录该内容

82 篇文章

订阅专栏

本文揭示了Oracle数据库中PLAN_TABLE并非实际表，而是由SYS.PLAN_TABLE$同义词引用的临时表。这解释了为什么不同会话间看到的数据不同，深入解析了计划表的工作原理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PLAN_TABLE会话间数据隔离的实现

PLAN_TABLE表是我们在数据库管理中经常间接的被用到的一张表。在我们是用explain plan for / autotrace的使用都会使用到它。今天在测试的时候发现PLAN_TABLE各个SESSION看到的数据不同，于是有了如下的发现：

SQL> select owner, object_id, object_type from dba_objects where object_name ='PLAN_TABLE';

OWNER                           OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------------
PUBLIC                               5006 SYNONYM

SQL> select owner, synonym_name, table_owner, table_name from dba_synonyms where SYNONYM_NAME='PLAN_TABLE';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------
PUBLIC                         PLAN_TABLE                     SYS                            PLAN_TABLE$

发现PLAN_TABLE其实不是一张表，而是一个指向SYS.PLAN_TABLE$的同义词。下面我们来看看PLAN_TABLE$是何许人也：

SQL> desc sys.plan_table$
 Name                                                                                      Null?    Type
 ------------------------------- -------- -----------------------------
 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                          NUMBER(38)
 OBJECT_TYPE                              VARCHAR2(30)
 OPTIMIZER                                VARCHAR2(255)
 SEARCH_COLUMNS                           NUMBER
 ID                                       NUMBER(38)
 PARENT_ID                                NUMBER(38)
 DEPTH                                    NUMBER(38)
 POSITION                                 NUMBER(38)
 COST                                     NUMBER(38)
 CARDINALITY                              NUMBER(38)
 BYTES                                    NUMBER(38)
 OTHER_TAG                                VARCHAR2(255)
 PARTITION_START                          VARCHAR2(255)
 PARTITION_STOP                           VARCHAR2(255)
 PARTITION_ID                             NUMBER(38)
 OTHER                                    LONG
 OTHER_XML                                CLOB
 DISTRIBUTION                             VARCHAR2(30)
 CPU_COST                                 NUMBER(38)
 IO_COST                                  NUMBER(38)
 TEMP_SPACE                               NUMBER(38)
 ACCESS_PREDICATES                        VARCHAR2(4000)
 FILTER_PREDICATES                        VARCHAR2(4000)
 PROJECTION                               VARCHAR2(4000)
 TIME                                     NUMBER(38)
 QBLOCK_NAME                              VARCHAR2(30)

SQL> select dbms_metadata.get_ddl('TABLE', 'PLAN_TABLE$', 'SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','PLAN_TABLE$','SYS')
------------------------------------------------------------------------------

  CREATE GLOBAL TEMPORARY TABLE "SYS"."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" NUMBER(*,0),
        "OBJECT_TYPE" VARCHAR2(30),
        "OPTIMIZER" VARCHAR2(255),
        "SEARCH_COLUMNS" NUMBER,
        "ID" NUMBER(*,0),
        "PARENT_ID" NUMBER(*,0),
        "DEPTH" NUMBER(*,0),
        "POSITION" NUMBER(*,0),
        "COST" NUMBER(*,0),
        "CARDINALITY" NUMBER(*,0),
        "BYTES" NUMBER(*,0),
        "OTHER_TAG" VARCHAR2(255),
        "PARTITION_START" VARCHAR2(255),
        "PARTITION_STOP" VARCHAR2(255),
        "PARTITION_ID" NUMBER(*,0),
        "OTHER" LONG,
        "OTHER_XML" CLOB,
        "DISTRIBUTION" VARCHAR2(30),
        "CPU_COST" NUMBER(*,0),
        "IO_COST" NUMBER(*,0),
        "TEMP_SPACE" NUMBER(*,0),
        "ACCESS_PREDICATES" VARCHAR2(4000),
        "FILTER_PREDICATES" VARCHAR2(4000),
        "PROJECTION" VARCHAR2(4000),
        "TIME" NUMBER(*,0),
        "QBLOCK_NAME" VARCHAR2(30)
   ) ON COMMIT PRESERVE ROWS