dba_enabled_aggregations_懂视

dba_enabled_aggregations

2020-11-09 07:47:07 责编:小采

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	Description
AGGREGATION_TYPE	VARCHAR2(21)	Type of the aggregation: CLIENT_ID SERVICE SERVICE_MODULE SERVICE_MODULE_ACTION
PRIMARY_ID	VARCHAR2()	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	 134
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 26665286 logons cumulative	 48
	165959219 SYS$BACKGR 26665286 logons cumulative	 47
	 OUND

 3271786180 easy	 26665286 logons cumulative	 19
 1671308587 jj	 26665286 logons cumulative	0
 2349869997 pointXDB 26665286 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行。

下载本文

显示全文

全部频道