Unlock the value of the data in Your OEM repositpory

1 unleash the value of the data in Your OEM RepositoryIordan K. America Marketing (NewsCorp) SYSAUXSYSAUXSYSAUXAWR/ASHDB1DB2DB3 Sources of Information in OEMOEM RepositoryOEM DBDatabasesDatabasesDatabasesOSOSOSU pload filesUpload filesUpload filesOEM agentOEM agentOEM agentOEM ServerOEM RepositoryASMMGMT$METRIC_CURRENT MGMT$METRIC_DETAILS MGMT$METRIC_HOURLY MGMT$METRIC_DAILY Process of Metrics Gathering in OEMRawRawAggregateAggregateReference DataSome Metrics-related OEM Repository Tables Useful Applications averages over any period of timeFind the average run queue length during business hours(M-F, 7 am 8 pm) for 30 daysselect avg(average) frommgmt$metric_hourlywhere rollup_timestamp> sysdate 30andtarget_name= 'dbtest01' and metric_name= 'Load'and column_labal= 'Run Queue Length (1 minute average)' and to_char(rollup_timestamp,'DAY') not in ('SATURDAY','SUNDAY')and rollup_timestampbetween trunc(rollup_timestamp,'DD') + 7/24 and trunc(rollup_timestamp,'DD') + 20/24 Query:Output.

2 Unlimited flexibility in managing OEM metrics dataUseful Applications averages over any period of timeFind the average run queue length during business hours(M-F, 7 am 8 pm) for 30 daysMonitor Force Logging mode for all production UDM in each production database. The sole purpose of this UDM would be to supply data to the OEM repositoryUseful Applications enforcing enterprise-wide policiesCreate UDM in the OEM repository database. This UDM will notify/page DBAs if a DB is not in Force Logging modeUseful Applications enforcing enterprise-wide policiesMonitor Force Logging mode for all production count( member_target_guid) from mgmt$group_derived_membershipso ,mgmt$targettwhere PROD'and o. member_target_guid= ( 'rac_database'or( 'oracle_database'and != 'RACINST ))and not exists (select * from mgmt$metric_currentiwhere metric_name= 'SQLUDM'and column_label= 'ForcedLogging'and Metric_Column= 'StrValue'and collection_timestamp> sysdate-20/1440and value = 'YES )The full query behind the OEM repository UDM:Useful Applications enforcing enterprise-wide policiesMonitor Force Logging mode for all production about exempting a database?

3 One need to do a concerted effort to exempt a Applications enforcing enterprise-wide policiesMonitor Force Logging mode for all production to deliver customized solutions all but two tablespaces(TBLS_STG, TBLS_UNRECOVERABLE)must be in Force Logging modeUseful Applications enforcing enterprise-wide policiesMonitor Force Logging mode for all production corr( , ) , count(*) from (select sample_time,time_waitedfrom dba_hist_active_sess_history@prod_dbwher e event = 'db file sequential read'and session_state= 'WAITING'and sample_time> sysdate-1 ) db , ( select collection_timestamp, value from mgmt$metric_detailswhere target_name= '+ASM_PROD' and metric_name= 'Single_Instance_DiskGroup_Performance'a nd metric_column= <ASM Metric>' ) and ASH/AWR and OEM repositoryWhere does the variation in single block read time come from?Very different sampling rates usually result in lower correlationUseful Applications integration with other data sources select corr( , )from( select * from mgmt$metric_detailswhere target_name= 'STRDEST' and metric_name= 'streams_latency_throughput' and column_label= 'Latency' ) streams_latency,(select * from mgmt$metric_detailswhere target_name= STRDEST' and metric_name= 'instance_throughput'and column_label= 'I/O Megabytes (per second)') IO_loadwhere + 5/(60*24) and > sysdate 1 Why is the latency of Streams Apply so high?

4 Could it be the disk IO? It is often the :Output: : Hypothesis rejected! Need to look for other Applications the power of Oracle s own SQLC heck:select corr( , + )from ( select * from mgmt$metric_detailswhere target_name= 'STRDEST' and metric_name= 'streams_latency_throughput' and column_label= 'Latency' ) streams_latency,(select * from mgmt$metric_detailswhere target_name= 'STRSRC_STRSRC 1'and metric_name= 'instance_throughput'and column_label= 'Redo Generated (per second)' ) redo_source_1 ,(select * from mgmt$metric_detailswhere target_name= 'STRSRC_STRSRC 2' and metric_name= 'instance_throughput'and column_label= 'Redo Generated (per second)' ) redo_source_2 where and + 5/(60*24) and and + 5/(60*24)and > sysdate 1 Interpretation: The redo volume on the Capture side is likely the reason for increased latency on the Apply sideWhy is the latency of Streams Apply so high? Maybe it is the redo volume on the Capture Applications the power of Oracle s own SQLO utput: Forecasting Example -tablespacesize forecastSimple linear regression:tbls(t) = tbls0 + incr*t tbls0 (Intercept) tablespacesize for time 0incr(Slope ) how fast a tablespacegrowst timetbls(t) tablespacesize at time t050001000015000200002500030000-56-54-47 -40-33-26-19-18-11-431017243138455259667 38087 HistoricalMin RangeMax RangeForecastQuality of forecasting -95% prediction interval of the regression line Advanced Forecasting Example -tablespacesize forecastTime (days)Size (MB)insert into raw_dataselect , mgmt$metric_dailym , mgmt$target_typetwhere ( 'rac_database'or ( 'oracle_database'and !))

5 = 'RACINST'))and (Database) and 'tbspAllocation and 'spaceUsed' and >= sysdate-p_period_histand <= sysdateand p_tablespace_name;Getting raw historical tablespacesizing data from the OEM repositoryAdvanced Forecasting Example -tablespacesize forecastUtilize Oracle s built-in packages and functions to compute most of the statistics in this projectcreate or replaceFUNCTION f_forecast_t_dist(p_degree_freedomNUMBER , p_confNUMBER) RETURN NUMBER ISl_resNUMBER;l_one_tail_confNUMBER;BEGI N---Custom CodeEND f_forecast_t_dist;Import data and create functions that are not available in Oracleselect regr_intercept(average , rollup_timestamp-sysdate) ,regr_slope(average , rollup_timestamp-sysdate),regr_r2 (average , rollup_timestamp-sysdate),regr_count(ave rage ,rollup_timestamp-sysdate),regr_avgx(ave rage ,rollup_timestamp-sysdate),regr_sxx(aver age ,rollup_timestamp-sysdate) ,regr_syy(average ,rollup_timestamp-sysdate),regr_sxy(aver age ,rollup_timestamp-sysdate)into.

6 From raw_dataAdvanced Forecasting Example -tablespacesize forecastl_ci:=sqrt( 1/ l_cnt+power((p_period_forecast-l_avgx),2 )/l_sxx);l_sigma:= sqrt(abs((l_syy-(power(l_sxy,2)/l_sxx))) /(l_cnt-2) ) ;p_out_estimate_95_range:=(l_ci*f_foreca st_t_dist(l_cnt-2, )*l_sigma) ;Final computationsAdvanced Forecasting Example -tablespacesize forecastAdvanced Forecasting Example -tablespacesize forecastDelivery Methods -VariousSample Output:Advanced Forecasting Example -tablespacesize forecastReferences MetalinkNote (Examples: Creating Custom Reports). Oracle Enterprise Manager Extensibility 10g Release 2 ( ) for Windows or UNIX Liang s Blog: 11g appears to have the same OEM repository structures as OEM 10g