Oracle数据字典:aux_stats$基表

AUX_STATS$基表用于存放一些优化器使用的辅助统计信息。该基表会在创建数据库(create database)调用sql.bsq(11g以前)或者doptim.bsq(sql.bsq->dboptim.sql 11g以后)时被创建。

 

其中sname、pname 2列维护主键, 在这2列上还有一个唯一索引  i_aux_stats$。

 

Rem The aux_stats$ table contains auxiliary statistics used by optimizer.
Rem sname and pname maintain primary key where sname stores name of set
Rem of parameters and pname is name of parameter. pval1 or pval2 store
Rem parameter value in character or number format.
create table aux_stats$ (
  sname varchar2("M_IDEN") not null, /* Name of set */
  pname varchar2("M_IDEN") not null, /* Name of parameters*/
  pval1 number,                      /* NUMBER parameter value */
  pval2 varchar2(255)                /* VARCHAR2 parameter value */
)
/
create unique index i_aux_stats$ on aux_stats$(sname, pname)
/
procedure gather_system_stats (
  gathering_mode  varchar2 default 'NOWORKLOAD',
  interval  integer  default 60,
  stattab   varchar2 default null,
  statid    varchar2 default null,
  statown   varchar2 default null);
--
-- This procedure gathers system statistics.
--
-- Input arguments:
--   mode - Allowable values: INTERVAL, START, STOP.
--     INTERVAL:
--       In INTERVAL mode user can provide interval parameter. After <interval>
--       minutes elapsed system statistics in dictionary or stattab will be
--       updated or created. This statistics captures system activity during
--       specified interval.
--     START | STOP:
--       START will initiate gathering statistics. STOP will calculate
--       statistics for elapsed period of time (since START) and refresh
--       dictionary or stattab. Interval in these modes is ignored.
--   interval - Specifies period of time in minutes for gathering statistics
--      in INTERVAL mode.
--   stattab - The user stat table identifier describing where to save
--      the current statistics.
--   statid - The (optional) identifier to associate with these statistics
--      within stattab.
--   statown - The schema containing stattab (if different then ownname)
--
-- Exceptions:
--   ORA-20000: Object does not exist or insufficient privileges
--   ORA-20001: Bad input value
--   ORA-20002: Bad user statistics table, may need to upgrade it
--   ORA-20003: Unable to gather system statistics
--   ORA-20004: Error in "INTERVAL" mode :
--              system parameter job_queue_processes must be > 0
SQL> col sname for a30
SQL> col pname for a30
SQL> col pval1 for 99999999
SQL> col pval2 for a30
SQL> set linesize 200 pagesize 1400
SQL> select * from aux_stats$;
SNAME                          PNAME                              PVAL1 PVAL2
------------------------------ ------------------------------ --------- ------------------------------
SYSSTATS_INFO                  STATUS                                   COMPLETED
SYSSTATS_INFO                  DSTART                                   09-17-2011 10:21
SYSSTATS_INFO                  DSTOP                                    09-17-2011 10:21
SYSSTATS_INFO                  FLAGS                                  1
SYSSTATS_MAIN                  CPUSPEEDNW                          1752
SYSSTATS_MAIN                  IOSEEKTIM                             10
SYSSTATS_MAIN                  IOTFRSPEED                          4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR
$ sqlplus / as sysdba
alter session set nls_date_format='DD-MM-YY hh24:mi:ss';
set serveroutput on ;
exec DBMS_STATS.CREATE_STAT_TABLE ('SYS','sys_stats');
--
-- The following gather system stats should be done at the peak workload time frame to get reliable stats
--
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS ('interval',interval => 180, stattab => 'sys_stats', statid => 'OLTP');
END;
/
DECLARE
STATUS VARCHAR2(20);
DSTART DATE;
DSTOP  DATE;
PVALUE NUMBER;
PNAME  VARCHAR2(30);
BEGIN
PNAME := 'cpuspeed';
DBMS_STATS.GET_SYSTEM_STATS(status,
dstart,
dstop,
pname,
pvalue,
stattab => 'sys_stats',
statid  => 'OLTP',
statown => 'SYS');
DBMS_OUTPUT.PUT_LINE('status : ' || status);
DBMS_OUTPUT.PUT_LINE('cpu in mhz : ' || pvalue);
DBMS_OUTPUT.PUT_LINE('start :' || dstart);
DBMS_OUTPUT.PUT_LINE('stop :' || dstop);
PNAME := 'sreadtim';
DBMS_STATS.GET_SYSTEM_STATS(status,
dstart,
dstop,
pname,
pvalue,
stattab => 'sys_stats',
statid  => 'OLTP',
statown => 'SYS');
DBMS_OUTPUT.PUT_LINE('single block readtime in ms : ' || pvalue);
PNAME := 'mreadtim';
DBMS_STATS.GET_SYSTEM_STATS(status,
dstart,
dstop,
pname,
pvalue,
stattab => 'sys_stats',
statid  => 'OLTP',
statown => 'SYS');
DBMS_OUTPUT.PUT_LINE('multiblock readtime in ms : ' || pvalue);
PNAME := 'mbrc';
DBMS_STATS.GET_SYSTEM_STATS(status,
dstart,
dstop,
pname,
pvalue,
stattab => 'sys_stats',
statid  => 'OLTP',
statown => 'SYS');
DBMS_OUTPUT.PUT_LINE('average multiblock readcount: ' || pvalue);
END;
/
status : COMPLETED
cpu in mhz : 727
start :13-02-10 14:00:00
stop :13-02-10 17:00:00
single block readtime in ms : 15.294
multiblock readtime in ms : 29.6
average multiblock readcount: 4
PL/SQL procedure successfully completed.

 

可以看到aux_stats$实际存放的是Oracle通过DBMS_STATS.GATHER_SYSTEM_STATS收集到的一些主机系统的性能指标,包括CPU速度、IO寻道速度等等常见的主机性能参数。(The output from dbms_stats.gather_system_stats is stored in the AUX_STATS$ table and hence the above query output will provide the captured details)

 

Note:How to Collect and Display System Statistics (CPU and IO) for CBO use [ID 149560.1]
Note:System Statistics: How to gather system stats for a particular batch of work [ID 427939.1]
How To Gather and Display The Workload System Statistics? [ID 1148577.1]

分别指出了在不同环境中正确收集主机性能指标的方法。

Oracle中的Package/Procedure/Function存放在哪里?

有同学问Oracle 的package、Procedure、Function 这些PL/SQL程序单元分别存放在哪里?

针对这个问题我们可以通过对create package、Procedure、Function 做trace分析来了解其细节,如:

 

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------
www.askmaclean.com & www.askmaclean.com
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL> create or replace procedure maclean
2 as
3 begin
4 null
5 ;
6 end;
7 /
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_3503.trc

 

我们来分析以上trace的内容:

 

[oracle@vrh8 ~]$ grep "insert"  /s01/admin/G10R21/udump/g10r21_ora_3503.trc
insert into procedure$(obj#,audit$,options) values (:1,:2,:3)
insert into source$(obj#,line,source) values (:1,:2,:3)
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_char$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into settings$(obj#, param, value) values (:1, :2, :3)
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
insert into procedureinfo$(obj#,procedure#,overload#,procedurename,properties,itypeobj#) values (:1,:2,:3,:4,:5,:6)
insert into argument$( obj#,procedure$,procedure#,overload#,position#,sequence#,level#)
insert into procedureplsql$(obj#,procedure#,entrypoint#) values (:1,:2,:3)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into obj$(owner#,name,namespace,obj#,type#,

 

创建一个非常简单的PL/SQL Procedure牵涉到的数据字典基表却不少,包括了:

  • procedure$
  • source$
  • idl_sb4$
  • idl_ub1$
  • idl_char$
  • idl_ub2$
  • idl_sb4$
  • idl_ub1$
  • settings$
  • warning_settings$
  • procedureinfo$
  • access$
  • obj$

 

注意以上这些数据字典基表都存放在SYSTEM表空间上,丢失SYSTEM表空间也就意味着你可能找不回这些程序了(如果你没有其他备份的话),即使使用DUL工具也无法挽回。

 

我来简单介绍一下这些数据字典基表( 11g以前可以在sql.bsq文件中找到以下信息, 11g以后在$ORACLE_HOME/rdbms/admin/dplsql.bsq中):

 

 

procedure$ 基表(普通堆表)用于存放Package程序包和独立的procedure存储过程以及function 函数信息,每一条记录对应一个procedure或function(procedure$ contains information about packages and standalone procedures, functions. There is one row for a top-level object.)

 

 

procedureinfo$基表(普通堆表)用于存放独立的或程序包中procedure或function的详细信息,包括Name名字和属性。

这些属性包括:Aggregate、Pipelined、Parallel、deterministic等:

 

  properties    number not null,                     /* procedure properties */
/* 0x00001 =     1 = HIDDEN (internally generated) procedure */
/* 0x00002 =     2 = C implementation (in spec)              */
/* 0x00004 =     4 = Java implementation (in spec)           */
/* 0x00008 =     8 = Aggregate function                      */
/* 0x00010 =    16 = Pipelined function                      */
/* 0x00020 =    32 = Parallel enabled                        */
/* 0x00040 =    64 = Retrun Self as result (SQLJ)            */
/* 0x00080 =   128 = Constructor function (SQLJ)             */
/* 0x00100 =   256 = deterministic                           */
/* 0x00200 =   512 = Pipelined func; interface impl          */
/* 0x00400 =  1024 = Function with invokers rights           */
/* 0x00800 =  2048 = Func with partitioned argument(s)       */
/* 0x01000 =  4096 = Func with clustered argument(s)         */
/* 0x02000 =  8192 = Func with ordered i/p argument(s)       */
/* 0x04000 =  16384 = Partitioned arg: Hash partitioning     */
/* 0x08000 = 32768 = Partitioned arg: Range partitioning     */
/* 0x10000 = 65536 = Partitioned using any partitioning      */
/* The following field is relevant only for aggregate and pipelined        */
/*  functions that are implemented using an implementation type            */

 

source$用于存放PL/SQL程序的源代码, 这些代码包括已经被Oracle Wrap加密的代码,一般来说如Oracle EBS这样的应用这张表会非常地大:

 

 

idl_ub1$、idl_char$ 这种表名以idl_打头的字典基表用以存放编译好的PL/SQL程序代码,包括三种种类:

  • DIANA
    • Heap 2 (Diana) : Contains the DIANA (Parse tree metadata) for a PL/SQL object
  • Portable pcode
    • Heap 3 (Pcode): Stores the pseudocode for a PL/SQL object
  • machine-dependent code
    • Heap 4 (Mcode): Machine-dependent pseudocode for a PL/SQL object

 

 

argument$基表记录了存储过程或函数的调用时的参数信息, 如我们所常用的DBMS_METADATA.GET_DDL函数就有7个argument参数

 

select object_name,object_id,object_type from dba_objects where object_name='DBMS_METADATA';
OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
DBMS_METADATA              6097 PACKAGE
DBMS_METADATA              7105 PACKAGE BODY
DBMS_METADATA              6098 SYNONYM
SQL> select procedure$,sequence#,argument from sys.argument$ where procedure$='GET_DDL' and obj#=6097;
PROCEDURE$                      SEQUENCE# ARGUMENT
------------------------------ ---------- ------------------------------
GET_DDL                                 1
GET_DDL                                 2 OBJECT_TYPE
GET_DDL                                 3 NAME
GET_DDL                                 4 SCHEMA
GET_DDL                                 5 VERSION
GET_DDL                                 6 MODEL
GET_DDL                                 7 TRANSFORM
7 rows selected.

 

 

Slide:Oracle数据库升级前必要的准备工作

深入了解Oracle数据字典升级脚本catupgrd.sql调用过程

我们在升级数据库的大版本(如9i -> 10g )或大的补丁集( 如10.2.0.1 -> 10.2.0.4)时总是需要升级现有数据库的数据字典(dictionary),这是因为随着Oracle版本的升级,某些对象的属性需要改变,而这些改变操作都将体现在升级脚本catupgrd.sql中。

举例来说在11.2版本中为了ASH特性增加dbreplay的信息,那么我们到11.2的ORACLE_HOME/rdbms/admin下找到c1102000.sql,可以发现以下的DDL语句:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
alter table WRR$_REPLAY_FILTER_SET add (default_action varchar2(20));
Rem =======================================================================
Rem  End Changes for Database Replay
Rem =======================================================================

该c1102000.sql会被catupgrd.sql调用,换而言之在升级过程中会为WRR$_REPLAY_FILTER_SET基表增加default_action列。

而与之相对应的e1102000.sql 脚本存在以下drop default_action 列的语句:

Rem
Rem Drop this column for existing dbms_workload_replay
Rem
alter table WRR$_REPLAY_FILTER_SET drop column default_action;
commit;

 

该e1102000.sql在数据字典降级过程中会被catdwgrd.sql调用,也就是说当数据字典要降级到11.2之前的版本时会将WRR$_REPLAY_FILTER_SET基表还原到之前版本的表结构,而这一还原操作就包含在e1102000.sql脚本中。

请注意虽然数据字典的升级(catupgrd.sql)和降级(catdwgrd.sql)是2种逆向的操作,但实际他们对数据字典的变更并非是一一对应的。
假设在catupgrd.sql中创建了某些组件对象(component objects),那么在降级时并不会将这些新增加的组件对象全部drop掉,而是简单地truncate这些对象上的数据。

实际上c1102000.sql 脚本会在升级数据字典即catupgrd.sql脚本运行时被调用,而e1102000.sql 则会在降级数据字典版本即catdwgrd.sql脚本运行过程中被调用。

 

一般来说在$ORACLE_HOME/rdbms/admin目录下的脚本文件名表达了该脚本的作用,如:

 

cat*.sql       一般是用来创组件建对象(create objects)的,如catalog.sql脚本创建数据字典对象
cmpup*.sql     一般是用来升级组件component的,如cmpupjav.sql脚本用来升级JAVAVM和XML
ii1102000.sql  包含了数据字典变化必要的DDL操作
c1102000.sql   包含了绝大多数的数据字典变化
a1102000.sql   包含了更新字典数据的PL/SQL块
cmpupgrd.sql   该脚本调用必要的组件升级脚本,如JAVAVM,CONTEXT,Spatial等
f1102000.sql   该脚本使用PL/SQL包将数据字典变化恢复到老的版本
e1102000.sql   该脚本包含了恢复到老版本的其他一些必要字典变更

 

了解了这些升级脚本的作用之后,我们来看一个数据库升级的实例。 以下是由10.1.0.5 升级到 11.2.0.1 时 catupgrd.sql 脚本的调用追踪情况:

 

@catupgrd.sql
@catupstr.sql
@i0902000.sql -> @i1001000.sql -> @i1002000.sql -> i1101000.sql
@c1001000.sql -> @c1002000.sql -> @c1101000.sql
@catalog.sql
@catproc.sql
@catupprc.sql
@a1001000.sql -> @a1002000.sql -> @a1101000.sql
@cmpupgrd.sql
@cmpupstr.sql
@cmpupjav.sql
@cmpupnjv.sql
@cmpupxdb.sql
@cmpupnxb.sql
@cmpupord.sql
@cmpupmsc.sql
@cmpupend.sql
@catupend.sql

注意以上c*.sql的执行过程是c1001000.sql->@c1002000.sql -> @c1101000.sql -> @catalog.sql (其实就是c1102000.sql) -> @catproc.sql,这说明了当10.1.0.5升级到11.2.0.1时,首先还是要执行10.1、10.2、11.1的数据字典变更,而非直接由10.1.0.5 的字典一步到位到 11.2。

以下列出更多升级脚本的作用:

--- 11.2 Upgrade Scripts
i1102000.sql contains the subset of dictionary changes necessary for DDL operations 
c1102000.sql contains most of the dictionary changes, runs catalog and catproc 
a1102000.sql contains PL/SQL blocks to update data within the dictionary 
cmpupgrd.sql invokes the component upgrade scripts (JAVAVM, CONTEXT, Spatial, etc.) 
---  catupgrd.sql
@catupstr.sql – initial checks – runs “c” scripts
@catalog.sql – creates data dictionary objects
@catproc.sql – creates packages & types
@catupprc.sql – final RDBMS  “a” scripts 
@cmpupgrd.sql – invokes upgrade component scripts
@catupend.sql – final scripts to complete the upgrade
---  Downgrade Scripts – catdwgrd.sql
cmpdbdwg.sql invokes the component downgrade scripts (JAVAVM, CONTEXT, etc.) OR
cmpdwpth.sql invokes the component patch downgrade scripts (most components do not have patch downgrade scripts) 
f1102000.sql contains the dictionary changes that use PL/SQL packages to revert to the previous server 
e1102000.sql contains other dictionary changes necessary to revert to the previous server 
cmpupjav.sql – upgrades JAVAVM and XML
Cmpupnjv.sql – upgrades components not dependent on javavm or xml 
Cmpupxdb.sql – upgrades context and xdb
Cmpupnxb.sql – upgrades components not dependent on context or xdb, but dependent on javavm or xml
Cmpupord.sql – upgrades ordim and spatial (dependent on javavm, xml, and xdb)
Cmpupmsc.sql – upgrades other components dependent on context or xdb (odm, wk, exf, rul)

沪ICP备14014813号

沪公网安备 31010802001379号