dba_enabled_aggregations

来源：互联网发布：淘宝超级店长在哪里编辑：程序博客网时间：2024/04/30 14:12

DBA_ENABLED_AGGREGATIONS displays information about enabled on-demand statistic aggregation.

ColumnDatatypeNULLDescriptionAGGREGATION_TYPEVARCHAR2(21) Type of the aggregation:

CLIENT_ID
SERVICE
SERVICE_MODULE
SERVICE_MODULE_ACTION

PRIMARY_IDVARCHAR2(64) Primary qualifier (specific client identifier or service name)QUALIFIER_ID1VARCHAR2(48) Secondary qualifier (specific module name)QUALIFIER_ID2VARCHAR2(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_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 user 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 time8309es   opened cursors cumulative  19es   session logical reads  35es   physical reads   0es   physical writes   0CLIENT_IDE STAT_NAME       VALUE---------- ------------------------------ ----------es   redo size1976es   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_NASTAT_ID STAT_NAME    VALUE----------------- ---------- ---------- ------------------------------ ----------       3427055676 SYS$USERS  2666645286 logons cumulative       48165959219 SYS$BACKGR 2666645286 logons cumulative       47  OUND       3271786180 easy     2666645286 logons cumulative       19       1671308587 jj     2666645286 logons cumulative0       2349869997 pointXDB   2666645286 logons cumulative0       3427055676 SYS$USERS  2882015696 user calls      244       ......       3271786180 easy     3332107451 user I/O wait time  1756694       1671308587 jj     3332107451 user I/O wait time0       2349869997 pointXDB   3332107451 user I/O wait time0已选择140行。

1 0