DBA_ENABLED_AGGREGATIONS displays information about enabled on-demand statistic aggregation.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| AGGREGATION_TYPE | VARCHAR2(21) | Type of the aggregation: CLIENT_ID SERVICE SERVICE_MODULE SERVICE_MODULE_ACTION |
|
| PRIMARY_ID | VARCHAR2(64) | Primary qualifier (specific client identifier or service name) | |
| QUALIFIER_ID1 | VARCHAR2(48) | Secondary qualifier (specific module name) | |
| QUALIFIER_ID2 | VARCHAR2(32) | Additional qualifier (specific action name) |
通过DBA_ENABLED_AGGREGATIONS视图可以查询通过DBMS_MONITOR包开启的统计信息收集。通过dbms_monitor我们可以按照如下方式收集统计信息： 基于session client identfier收集基于service、module、action的组合收集 示例如下：
SQL> execute dbms_session.set_identifier('es');
PL/SQL 过程已成功完成。
SQL> exec dbms_monitor.client_id_stat_enable('es');
PL/SQL 过程已成功完成。
SQL> select aggregation_type from dba_enabled_aggregations;
AGGREGATION_TYPE
---------------------
CLIENT_ID
SQL> exec dbms_application_info.set_module(module_name=>'tm',action_name=>'ta');
PL/SQL 过程已成功完成。
SQL> select aggregation_type from dba_enabled_aggregations;
AGGREGATION_TYPE
---------------------
CLIENT_ID
SQL> exec dbms_monitor.serv_mod_act_stat_enable(service_name=>'easy');
BEGIN dbms_monitor.serv_mod_act_stat_enable(service_name=>'easy'); END;
*
第 1 行出现错误:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'SERV_MOD_ACT_STAT_ENABLE' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
SQL> exec dbms_monitor.serv_mod_act_stat_enable(service_name=>'easy',module_name=>'tm');
PL/SQL 过程已成功完成。
SQL> select aggregation_type from dba_enabled_aggregations;
AGGREGATION_TYPE
---------------------
CLIENT_ID
SERVICE_MODULE
SQL> exec dbms_monitor.serv_mod_act_stat_enable(service_name=>'easy',module_name=>'tm',action_name=>'ta');
PL/SQL 过程已成功完成。
SQL> select aggregation_type from dba_enabled_aggregations;
AGGREGATION_TYPE
---------------------
CLIENT_ID
SERVICE_MODULE
SERVICE_MODULE_ACTION
查看统计信息： 视图：v$serv_mod_act_stats
SQL> l 1* select aggregation_type,service_name,module,action,stat_name,value from v$serv_mod_act_stats SQL> / AGGREGATION_TYPE SERVICE_NA MODULE ACTION STAT_NAME VALUE --------------------- ---------- ---------- -------------------- ------------------------------ ---------- SERVICE_MODULE easy tm user calls 0 SERVICE_MODULE easy tm DB time 0 SERVICE_MODULE easy tm DB CPU 0 SERVICE_MODULE easy tm parse count (total) 0 SERVICE_MODULE easy tm parse time elapsed 0 SERVICE_MODULE easy tm execute count 0 SERVICE_MODULE easy tm sql execute elapsed time 0 SERVICE_MODULE easy tm opened cursors cumulative 0 SERVICE_MODULE easy tm session logical reads 0 SERVICE_MODULE easy tm physical reads 0 SERVICE_MODULE easy tm physical writes 0 AGGREGATION_TYPE SERVICE_NA MODULE ACTION STAT_NAME VALUE --------------------- ---------- ---------- -------------------- ------------------------------ ---------- SERVICE_MODULE easy tm redo size 0 SERVICE_MODULE easy tm user commits 0 SERVICE_MODULE easy tm workarea executions - optimal 0 SERVICE_MODULE easy tm workarea executions - onepass 0 SERVICE_MODULE easy tm workarea executions -