database usage statistics - 数据库的使用统计信息

database usage statistics - 数据库的使用统计信息



1.
DBA_FEATURE_USAGE_STATISTICS 视图显示了数据库中各个特性的使用情况，比如哪种特性被使用了多少次，最近使用时间等. 10gR1新增
set pages 9999 line 120
col name for a50
select * from dba_feature_usage_statistics;

SQL> select * from dba_feature_usage_statistics;

DBID NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE
---------- -------------------------------------------------- ----------------- --------------- ------------- -----
FIRST_USAGE_DATE LAST_USAGE_DATE AUX_COUNT
------------------- ------------------- ----------
FEATURE_INFO LAST_SAMPLE_DATE LAST_SAMPLE_PERIOD
-------------------------------------------------------------------------------- ------------------- ------------------
SAMPLE_INTERVAL
---------------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
1955690436 Advanced Replication 10.2.0.4.0 0 28 FALSE

2008-12-25 23:35:42 604800
604800
Advanced Replication has been enabled.

...
1955690436 Automatic Undo Management 10.2.0.4.0 28 28 TRUE
2008-06-16 12:18:20 2008-12-25 23:35:42 1
(Retention: NOGUARANTEE, TS Count: 1, Size MB: 24) (Undo Blo 2008-12-25 23:35:42 604800
cks: 31841, Max Concurrency: 5) (Snapsho
t Old Info - Begin Time: 2008-12-21 23:3
8:26, End Time: 2008-12-25 23:35:42, SSO
LD Error Count: 0)
604800
Oracle automatically manages undo data using an UNDO tablespace.

...
1955690436 PL/SQL Native Compilation 10.2.0.4.0 0 28 FALSE

2008-12-25 23:35:42 604800
604800
PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.


69 rows selected.

SQL>

2.
DBA_HIGH_WATER_MARK_STATISTICS 显示数据库中最高水位的统计信息，如表空间，文件，表等曾达到的最大值. 10gR1新增
select * from dba_high_water_mark_statistics;

SQL> select * from dba_high_water_mark_statistics;

DBID NAME VERSION HIGHWATER LAST_VALUE
---------- -------------------------------------------------- ----------------- ---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
1955690436 ACTIVE_SESSIONS 10.2.0.4.0 0 0
Maximum Number of Active Sessions seen in the system

1955690436 CPU_COUNT 10.2.0.4.0 2 2
Maximum Number of CPUs

1955690436 DATAFILES 10.2.0.4.0 10 10
Maximum Number of Datafiles

1955690436 DB_SIZE 10.2.0.4.0 1.3285E+10 1967128576
Maximum Size of the Database (Bytes)

...
1955690436 USER_MV 10.2.0.4.0 6 4
Maximum Number of Materialized Views (User)

1955690436 USER_TABLES 10.2.0.4.0 316 316
Number of User Tables


15 rows selected.

SQL>

3.
DBA_CPU_USAGE_STATISTICS 显示中央处理器使用的统计信息. 10gR2新增
select * from dba_cpu_usage_statistics;

SQL> select * from dba_cpu_usage_statistics;

DBID VERSION TIMESTAMP CPU_COUNT CPU_CORE_COUNT CPU_SOCKET_COUNT
---------- ----------------- ------------------- ---------- -------------- ----------------
1955690436 10.2.0.4.0 2008-06-16 12:18:20 2 1

SQL>

视图1.2.3.的数据源是自动工作负载信息库(AWR)中的一些以wri$开头的表(AWR Internal tables)
DBMS_FEATURE_USAGE 注册特性, DBMS_FEATURE_USAGE_REPORT 生成报告


4.
V$OPTION 列出数据库软件安装了哪些选项(option)以及具有哪些特性(feature)
col parameter for a40
select * from v$option;

SQL> select * from v$option;

PARAMETER VALUE
---------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
...
Data Mining Scoring Engine FALSE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Real Application Testing TRUE

56 rows selected.

SQL>

5.
DBA_REGISTRY 显示了数据库加载了哪些组件(component)，这是数据库级的. 9i新增.
select * from dba_registry;

SQL> select * from dba_registry;

COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------------------------------------------------------------------
VERSION STATUS MODIFIED
------------------------------ -------------------------------------------- -----------------------------
NAMESPACE CONTROL SCHEMA
------------------------------ ------------------------------ ------------------------------
PROCEDURE STARTUP PARENT_ID
------------------------------------------------------------- -------- ------------------------------
OTHER_SCHEMAS
------------------------------------------------------------------------------------------------------------------------
CONTEXT
Oracle Text
10.2.0.4.0 VALID 17-JUN-2008 01:55:34
SERVER SYS CTXSYS
VALIDATE_CONTEXT


OWM
Oracle Workspace Manager
10.2.0.4.3 VALID 16-JUN-2008 11:17:38
SERVER SYS WMSYS
VALIDATE_OWM


CATALOG
Oracle Database Catalog Views
10.2.0.4.0 VALID 16-JUN-2008 11:16:44
SERVER SYS SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG


CATPROC
Oracle Database Packages and Types
10.2.0.4.0 VALID 16-JUN-2008 11:16:44
SERVER SYS SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC
DBSNMP,OUTLN,SYSTEM


SQL>

6.
V$LICENSE 包括了许可限制信息，如实例中用户会话数和中央处理器数的最大值，当前值和最高水位值
select * from v$license;

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX CPU_COUNT_CURRENT CPU_CORE_COUNT_CURRENT
------------ ---------------- ---------------- ------------------ ---------- ----------------- ----------------------
CPU_SOCKET_COUNT_CURRENT CPU_COUNT_HIGHWATER CPU_CORE_COUNT_HIGHWATER CPU_SOCKET_COUNT_HIGHWATER
------------------------ ------------------- ------------------------ --------------------------
0 0 3 13 0 2
1 2 1


SQL>

7.
V$RESOURCE_LIMIT 显示出资源使用情况，包括初始值，当前值，最大值和最高限制
select * from v$resource_limit;

SQL> select * from v$resource_limit;

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION
------------------------------ ------------------- --------------- ----------------------------------------
LIMIT_VALUE
----------------------------------------
processes 22 35 1500
1500

sessions 26 45 1655
1655

...
max_shared_servers 1 1 UNLIMITED
UNLIMITED

parallel_max_servers 0 0 40
3600


22 rows selected.

SQL>

外部链接:
Checking Database Feature Usage Stats
Hidden Costs
Remote Diagnostic Agent (RDA) 4 - Content Modules Man Page



-fin-