dba_enabled_aggregations

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 PRI

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 - multipas 	 0
								 s

SERVICE_MODULE	      easy	 tm				 session cursor cache hits		 0
SERVICE_MODULE	      easy	 tm				 user rollbacks 			 0
SERVICE_MODULE	      easy	 tm				 db block changes			 0
SERVICE_MODULE	      easy	 tm				 gc cr blocks received			 0

AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE
--------------------- ---------- ---------- -------------------- ------------------------------ ----------
SERVICE_MODULE	      easy	 tm				 gc cr block receive time		 0
SERVICE_MODULE	      easy	 tm				 gc current blocks received		 0
SERVICE_MODULE	      easy	 tm				 gc current block receive time		 0
SERVICE_MODULE	      easy	 tm				 cluster wait time			 0
SERVICE_MODULE	      easy	 tm				 concurrency wait time			 0
SERVICE_MODULE	      easy	 tm				 application wait time			 0
SERVICE_MODULE	      easy	 tm				 user I/O wait time			 0

已选择27行。

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_ACTION easy	 tm	    ta			 user calls				 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 DB time				 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 DB CPU 				 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 parse count (total)			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 parse time elapsed			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 execute count				 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 sql execute elapsed time		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 opened cursors cumulative		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 session logical reads			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 physical reads 			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 physical writes			 0

AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE
--------------------- ---------- ---------- -------------------- ------------------------------ ----------
SERVICE_MODULE_ACTION easy	 tm	    ta			 redo size				 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 user commits				 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 workarea executions - optimal		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 workarea executions - onepass		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 workarea executions - multipas 	 0
								 s

SERVICE_MODULE_ACTION easy	 tm	    ta			 session cursor cache hits		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 user rollbacks 			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 db block changes			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 gc cr blocks received			 0

AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE
--------------------- ---------- ---------- -------------------- ------------------------------ ----------
SERVICE_MODULE_ACTION easy	 tm	    ta			 gc cr block receive time		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 gc current blocks received		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 gc current block receive time		 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 cluster wait time			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 concurrency wait time			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 application wait time			 0
SERVICE_MODULE_ACTION easy	 tm	    ta			 user I/O wait time			 0
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

AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE
--------------------- ---------- ---------- -------------------- ------------------------------ ----------
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
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

AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE
--------------------- ---------- ---------- -------------------- ------------------------------ ----------
SERVICE_MODULE	      easy	 tm				 workarea executions - multipas 	 0
								 s

SERVICE_MODULE	      easy	 tm				 session cursor cache hits		 0
SERVICE_MODULE	      easy	 tm				 user rollbacks 			 0
SERVICE_MODULE	      easy	 tm				 db block changes			 0
SERVICE_MODULE	      easy	 tm				 gc cr blocks received			 0
SERVICE_MODULE	      easy	 tm				 gc cr block receive time		 0
SERVICE_MODULE	      easy	 tm				 gc current blocks received		 0
SERVICE_MODULE	      easy	 tm				 gc current block receive time		 0
SERVICE_MODULE	      easy	 tm				 cluster wait time			 0

AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE
--------------------- ---------- ---------- -------------------- ------------------------------ ----------
SERVICE_MODULE	      easy	 tm				 concurrency wait time			 0
SERVICE_MODULE	      easy	 tm				 application wait time			 0
SERVICE_MODULE	      easy	 tm				 user I/O wait time			 0

已选择54行。

视图：v$client_stats

SQL> l
  1* select client_identifier,stat_name,value from v$client_stats
SQL> /

CLIENT_IDE STAT_NAME			       VALUE
---------- ------------------------------ ----------
es	   user calls				  28
es	   DB time			       45170
es	   DB CPU			       20997
es	   parse count (total)			  15
es	   parse time elapsed		       13894
es	   execute count			  14
es	   sql execute elapsed time		8309
es	   opened cursors cumulative		  19
es	   session logical reads		  35
es	   physical reads			   0
es	   physical writes			   0

CLIENT_IDE STAT_NAME			       VALUE
---------- ------------------------------ ----------
es	   redo size				1976
es	   user commits 			   0
es	   workarea executions - optimal	   0
es	   workarea executions - onepass	   0
es	   workarea executions - multipas	   0
	   s

es	   session cursor cache hits		   6
es	   user rollbacks			   0
es	   db block changes			  12
es	   gc cr blocks received		   0

CLIENT_IDE STAT_NAME			       VALUE
---------- ------------------------------ ----------
es	   gc cr block receive time		   0
es	   gc current blocks received		   0
es	   gc current block receive time	   0
es	   cluster wait time			   0
es	   concurrency wait time		   0
es	   application wait time		   0
es	   user I/O wait time			   0

已选择27行。

注意：在service级别的统计信息总是开启的，例如

SQL> l
  1* select aggregation_type from dba_enabled_aggregations
SQL> /

AGGREGATION_TYPE
---------------------
CLIENT_ID
SERVICE_MODULE
SERVICE_MODULE_ACTION

SQL> select * from v$service_stats;

SERVICE_NAME_HASH SERVICE_NA	STAT_ID STAT_NAME			    VALUE
----------------- ---------- ---------- ------------------------------ ----------
       3427055676 SYS$USERS  2666645286 logons cumulative		       48
	165959219 SYS$BACKGR 2666645286 logons cumulative		       47
		  OUND

       3271786180 easy	     2666645286 logons cumulative		       19
       1671308587 jj	     2666645286 logons cumulative			0
       2349869997 pointXDB   2666645286 logons cumulative			0
       3427055676 SYS$USERS  2882015696 user calls			      244
       ......

       3271786180 easy	     3332107451 user I/O wait time		  1756694
       1671308587 jj	     3332107451 user I/O wait time			0
       2349869997 pointXDB   3332107451 user I/O wait time			0

已选择140行。