关于数据库open的深入探究

Fenng · 发表于 2004-3-31 18:57

[php]
create table tab$                                           /* table table */
( obj#       number not null,                         /* object number */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
  dataobj#    number,                         /* data layer object number */
  ts#          number not null,                      /* tablespace number */
  file#       number not null,             /* segment header file number */
  block#       number not null,             /* segment header block number */
  bobj#       number,             /* base object number (cluster / iot) */
  tab#       number, /* table number in cluster, NULL if not clustered */
  cols       number not null,                      /* number of columns */
  clucols    number,/* number of clustered columns, NULL if not clustered */
  pctfree$    number not null, /* minimum free space percentage in a block */
  pctused$    number not null, /* minimum used space percentage in a block */
  initrans    number not null,          /* initial number of transaction */
  maxtrans    number not null,          /* maximum number of transaction */
  flags       number not null, /* 0x00    = unmodified since last backup
                                 0x01    = modified since then
                                 0x02    = DML locks restricted to <= SX
                                 0x04    = DML locks <= SX not acquired
                                 0x08    = CACHE
                                 0x10    = table has been analyzed
                                 0x20    = table has no logging
                                 0x40    = 7.3 -> 8.0 data object
                                             migration required
                                 0x0080 = current summary dependency
                                 0x0100 = user-specified stats
                                 0x0200 = global stats
                                 0x0800 = table has security policy
                                 0x020000 = Move Partitioned Rows
                                 0x0400000 = table has sub tables
                              0x00800000 = row dependencies enabled */
            /* 0x10000000 = this IOT has a  physical rowid mapping table */
            /* 0x20000000 = mapping table of an IOT(with physical rowid) */
  audit$       varchar2("S_OPFL"

not null,          /* auditing options */
  rowcnt       number,                                  /* number of rows */
  blkcnt       number,                               /* number of blocks */
  empcnt       number,                         /* number of empty blocks */
  avgspc       number,    /* average available free space/iot ovfl stats */
  chncnt       number,                         /* number of chained rows */
  avgrln       number,                               /* average row length */
  avgspc_flb number,    /* avg avail free space of blocks on free list */
  flbcnt       number,                            /* free list block count */
  analyzetime date,                      /* timestamp when last analyzed */
  samplesize number,                /* number of rows sampled by Analyze */
/*
* Legal values for degree, instances:
*    NULL (used to represent 1 on disk/dictionary and implies noparallel), or
*    2 thru EB2MAXVAL-1 (user supplied values), or
*    EB2MAXVAL (implies use default value)
*/
  degree       number,    /* number of parallel query slaves per instance */
  instances    number,       /* number of OPS instances for parallel query */
/* <intcols> => the number of dictionary columns => the number of columns
* that have dictionary meta-data associated with them. This is a superset of
* <usercols> and <kernelcols>.
* <intcols> = <kernelcols> + <number_of_virtual_columns>
*/
  intcols    number not null,             /* number of internal columns */
/* <kernelcols> => the number of REAL columns (ie) columns that actually
* store data.
*/
  kernelcols number not null,       /* number of REAL (kernel) columns */
  property    number not null,          /* table properties (bit flags): */
                           /* 0x01 = typed table, 0x02 = has ADT columns, */
               /* 0x04 = has nested-TABLE columns, 0x08 = has REF columns, */
                  /* 0x10 = has array columns, 0x20 = partitioned table, */
            /* 0x40 = index-only table (IOT), 0x80 = IOT w/ row OVerflow, */
         /* 0x100 = IOT w/ row CLustering, 0x200 = IOT OVeRflow segment, */
            /* 0x400 = clustered table, 0x800 = has internal LOB columns, */
      /* 0x1000 = has primary key-based OID$ column, 0x2000 = nested table */
                  /* 0x4000 = View is Read Only, 0x8000 = has FILE columns */
   /* 0x10000 = obj view's OID is system-gen, 0x20000 = used as AQ table */
         /* 0x40000 = has user-defined lob columns */
                           /* 0x00080000 = table contains unused columns */
                        /* 0x100000 = has an on-commit materialized view */
                           /* 0x200000 = has system-generated column names */
                                    /* 0x00400000 = global temporary table */
                        /* 0x00800000 = session-specific temporary table */
                                    /* 0x08000000 = table is a sub table */
                                    /* 0x20000000 = pdml itl invariant */
                                       /* 0x80000000 = table is external  */
  trigflag    number, /* first two bytes for trigger flags, the rest for */
               /* general use, check tflags_kqldtvc in kqld.h for detail */
                                          /* 0x00000001 deferred RPC Queue */
                                                /* 0x00000002 snapshot log */
                                    /* 0x00000004 updatable snapshot log */
                                          /* 0x00000008 = context trigger */
                                 /* 0x00000010 = synchronous change table */
                                    /* 0x00200000 = table had rowmovement */
                                 /* 0x00400000 = lobs use shared segment */
                                       /* enabled at some point in past  */
  spare1       number,                      /* used to store hakan_kqldtvc */
  spare2       number,       /* committed partition # used by drop column */
  spare3       number,                         /* summary sequence number */
  spare4       varchar2(1000),       /* committed RID used by drop column */
  spare5       varchar2(1000),    /* summary related information on table */
  spare6       date                               /* flashback timestamp */
)
cluster c_obj#(obj#)
/

[/php]

Fenng · 发表于 2004-3-31 19:33

最初由 Yong Huang 发布
[B]

biti_rainy's research worked up my appetite. In case biti knows, (what's biti's real name anyway!), do you know what Oracle really does to the physical database? By that I mean, Oracle apparently did NOT really create the bootstrap$, tab$ and others every time the database is opened. Otherwise, a trace of database shutdown should show corresponding drop of those tables and clusters. I traced it and didn't find those drop statements. It's also hard to believe a potentially huge table tab$ is always recreated whenever the database is opened. So those tables bootstrap$, tab$ etc. "created" at the database open time are already in SYSTEM tablespace. But SGA needs information for the instance to work properly. However, even that is a puzzle to me, because you won't find those create table bootstrap$ statement in library cache (v$sql) and there's no alter system flush shared pool in there either.
[/B]

Yong Huang,

before create tab$ and others objects ,Oracle seems to create SYSTEM tablespace :

create tablespace SYSTEM datafile "D_DBFN"
"D_DSTG" online
/

but I don't know D_DBFN/D_DSTG 's meaing .

maybe a internal container for tab$ and others ?

Fenng

Yong Huang · 发表于 2004-4-1 05:35

最初由 Fenng 发布
[B]
Yong Huang,

before create tab$ and others objects ,Oracle seems to create SYSTEM tablespace :

create tablespace SYSTEM datafile "D_DBFN"
"D_DSTG" online
/

but I don't know D_DBFN/D_DSTG 's meaing .

maybe a internal container for tab$ and others ?

Fenng [/B]

As I said, these physical objects (including SYSTEM tablespace) are probably not really created at instance startup time. The datafile for SYSTEM tablespace of course exists on the filesystem (or raw partition) when the instance is down. We all know that.

I don't know the meanings of those words either. It would be interesting to find out. First D may be "definition". DBFN sounds like database file name. DSTG sounds like some staging. Some templates inside Oracle?

Yong Huang

biti_rainy · 发表于 2004-4-1 09:21

这些物理对象都是已经真实地存在的

而我们所做的“create”仅仅是在 SGA 中创建这个一个对应的结构，然后好根据表的特征来读取数据

而为了生成 tab$ 等结构，这个结构的 sql 又来自 bootstrap$ ,最开始，oracle只要拥有了 bootstrap$ 的结构信息，在sga中创建了结构，则立即可以去 system tablespace中获取bootstrap$的内容(而这个create的语句完全可以放在可执行程序或者dll中作为天生的一个蛋)，然后逐步完成sga中字典表结构的建立。当然所有这些过程中并没有真实地创建物理对象。

我没有明确地指出这点，但是，从一开始的描述应该是可以看出这个意思的。
我研究这个过程的目的，就是好奇oracle是怎么找到系统表空间的物理上的数据的，根据已经open的数据库来看是可以根据表方式访问的，那这个表的结构信息又来自哪里？所以才追述出了上面的内容

Yong Huang · 发表于 2004-4-1 13:16

最初由 biti_rainy 发布
[B]这些物理对象都是已经真实地存在的

而我们所做的“create”仅仅是在 SGA 中创建这个一个对应的结构，然后好根据表的特征来读取数据
... [/B]

Can you explain why creating these structures in SGA doesn't leave any trace in library cache or v$sql? Wouldn't any SQLs that had been run leave their information in v$sql or v$sqlarea, unless the shared pool was flushed? I didn't dump library cache. But I imagine a dump file would contain the same info as viewed in v$sql.

Yong Huang

biti_rainy · 发表于 2004-4-1 14:25

仅仅是猜测：

1：shared pool 是为了重用，而这些sql不需要重用
2：shared pool 和通常执行的sql，需要解析，需要相关数据字典信息来解析，而这些sql不需要相关字典信息，也不可能有相关信息。所以我怀疑这些sql的解析机制就是不一样的。

Yong Huang · 发表于 2004-4-1 14:36

最初由 biti_rainy 发布
[B]仅仅是猜测：

1：shared pool 是为了重用，而这些sql不需要重用
2：shared pool 和通常执行的sql，需要解析，需要相关数据字典信息来解析，而这些sql不需要相关字典信息，也不可能有相关信息。所以我怀疑这些sql的解析机制就是不一样的。 [/B]

Interesting points. I wonder if we can issue any SQLs and at the same time tell Oracle not to cache them. I don't know any way. Anybody familiar with OCI can comment here? The layer below OCI is the undocumented UPI (http://www.oreilly.com/catalog/orinternals/chapter/ch01.html). Maybe at that layer we can.

Yong Huang