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.

通过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_IDSQL> 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_IDSQL> 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 ignoredSQL> 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_IDSERVICE_MODULESQL> 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_IDSERVICE_MODULESERVICE_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_statsSQL> /AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE	      easy	 tm				 user calls				 0SERVICE_MODULE	      easy	 tm				 DB time				 0SERVICE_MODULE	      easy	 tm				 DB CPU 				 0SERVICE_MODULE	      easy	 tm				 parse count (total)			 0SERVICE_MODULE	      easy	 tm				 parse time elapsed			 0SERVICE_MODULE	      easy	 tm				 execute count				 0SERVICE_MODULE	      easy	 tm				 sql execute elapsed time		 0SERVICE_MODULE	      easy	 tm				 opened cursors cumulative		 0SERVICE_MODULE	      easy	 tm				 session logical reads			 0SERVICE_MODULE	      easy	 tm				 physical reads 			 0SERVICE_MODULE	      easy	 tm				 physical writes			 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE	      easy	 tm				 redo size				 0SERVICE_MODULE	      easy	 tm				 user commits				 0SERVICE_MODULE	      easy	 tm				 workarea executions - optimal		 0SERVICE_MODULE	      easy	 tm				 workarea executions - onepass		 0SERVICE_MODULE	      easy	 tm				 workarea executions - multipas 	 0								 sSERVICE_MODULE	      easy	 tm				 session cursor cache hits		 0SERVICE_MODULE	      easy	 tm				 user rollbacks 			 0SERVICE_MODULE	      easy	 tm				 db block changes			 0SERVICE_MODULE	      easy	 tm				 gc cr blocks received			 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE	      easy	 tm				 gc cr block receive time		 0SERVICE_MODULE	      easy	 tm				 gc current blocks received		 0SERVICE_MODULE	      easy	 tm				 gc current block receive time		 0SERVICE_MODULE	      easy	 tm				 cluster wait time			 0SERVICE_MODULE	      easy	 tm				 concurrency wait time			 0SERVICE_MODULE	      easy	 tm				 application wait time			 0SERVICE_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_statsSQL> /AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE_ACTION easy	 tm	    ta			 user calls				 0SERVICE_MODULE_ACTION easy	 tm	    ta			 DB time				 0SERVICE_MODULE_ACTION easy	 tm	    ta			 DB CPU 				 0SERVICE_MODULE_ACTION easy	 tm	    ta			 parse count (total)			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 parse time elapsed			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 execute count				 0SERVICE_MODULE_ACTION easy	 tm	    ta			 sql execute elapsed time		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 opened cursors cumulative		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 session logical reads			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 physical reads 			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 physical writes			 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE_ACTION easy	 tm	    ta			 redo size				 0SERVICE_MODULE_ACTION easy	 tm	    ta			 user commits				 0SERVICE_MODULE_ACTION easy	 tm	    ta			 workarea executions - optimal		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 workarea executions - onepass		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 workarea executions - multipas 	 0								 sSERVICE_MODULE_ACTION easy	 tm	    ta			 session cursor cache hits		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 us<span>本文来源gaodai#ma#com搞*!代#%^码$网*</span>er rollbacks 			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 db block changes			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 gc cr blocks received			 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE_ACTION easy	 tm	    ta			 gc cr block receive time		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 gc current blocks received		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 gc current block receive time		 0SERVICE_MODULE_ACTION easy	 tm	    ta			 cluster wait time			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 concurrency wait time			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 application wait time			 0SERVICE_MODULE_ACTION easy	 tm	    ta			 user I/O wait time			 0SERVICE_MODULE	      easy	 tm				 user calls				 0SERVICE_MODULE	      easy	 tm				 DB time				 0SERVICE_MODULE	      easy	 tm				 DB CPU 				 0SERVICE_MODULE	      easy	 tm				 parse count (total)			 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE	      easy	 tm				 parse time elapsed			 0SERVICE_MODULE	      easy	 tm				 execute count				 0SERVICE_MODULE	      easy	 tm				 sql execute elapsed time		 0SERVICE_MODULE	      easy	 tm				 opened cursors cumulative		 0SERVICE_MODULE	      easy	 tm				 session logical reads			 0SERVICE_MODULE	      easy	 tm				 physical reads 			 0SERVICE_MODULE	      easy	 tm				 physical writes			 0SERVICE_MODULE	      easy	 tm				 redo size				 0SERVICE_MODULE	      easy	 tm				 user commits				 0SERVICE_MODULE	      easy	 tm				 workarea executions - optimal		 0SERVICE_MODULE	      easy	 tm				 workarea executions - onepass		 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE	      easy	 tm				 workarea executions - multipas 	 0								 sSERVICE_MODULE	      easy	 tm				 session cursor cache hits		 0SERVICE_MODULE	      easy	 tm				 user rollbacks 			 0SERVICE_MODULE	      easy	 tm				 db block changes			 0SERVICE_MODULE	      easy	 tm				 gc cr blocks received			 0SERVICE_MODULE	      easy	 tm				 gc cr block receive time		 0SERVICE_MODULE	      easy	 tm				 gc current blocks received		 0SERVICE_MODULE	      easy	 tm				 gc current block receive time		 0SERVICE_MODULE	      easy	 tm				 cluster wait time			 0AGGREGATION_TYPE      SERVICE_NA MODULE     ACTION		 STAT_NAME			     VALUE--------------------- ---------- ---------- -------------------- ------------------------------ ----------SERVICE_MODULE	      easy	 tm				 concurrency wait time			 0SERVICE_MODULE	      easy	 tm				 application wait time			 0SERVICE_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_statsSQL> /CLIENT_IDE STAT_NAME			       VALUE---------- ------------------------------ ----------es	   user calls				  28es	   DB time			       45170es	   DB CPU			       20997es	   parse count (total)			  15es	   parse time elapsed		       13894es	   execute count			  14es	   sql execute elapsed time		8309es	   opened cursors cumulative		  19es	   session logical reads		  35es	   physical reads			   0es	   physical writes			   0CLIENT_IDE STAT_NAME			       VALUE---------- ------------------------------ ----------es	   redo size				1976es	   user commits 			   0es	   workarea executions - optimal	   0es	   workarea executions - onepass	   0es	   workarea executions - multipas	   0	   ses	   session cursor cache hits		   6es	   user rollbacks			   0es	   db block changes			  12es	   gc cr blocks received		   0CLIENT_IDE STAT_NAME			       VALUE---------- ------------------------------ ----------es	   gc cr block receive time		   0es	   gc current blocks received		   0es	   gc current block receive time	   0es	   cluster wait time			   0es	   concurrency wait time		   0es	   application wait time		   0es	   user I/O wait time			   0已选择27行。

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

SQL> l  1* select aggregation_type from dba_enabled_aggregationsSQL> /AGGREGATION_TYPE---------------------CLIENT_IDSERVICE_MODULESERVICE_MODULE_ACTIONSQL> 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行。

喜欢 (0)赏

[搞代码]

分享 (0)