Wednesday, June 18, 2008

Oracle EM Grid Control - Custom Reports

Creating Custom Grid Report for database availability

Oracle Enterprise Manager GRID Server contains built in reports to support Database Administrators to generate sightly reports. However, in my company, we need some reports that cannot be find in GRID and needs custom development. Here is how i did it.

First Thing is to click to "Reports" tab on the top, right hand side of the GRID Management Console (figure_grid_management_console). This page is where all the predefined and custom reports can be find. Now click on the "Create" Button on top of the page to create a new custom report. "Create Report Definition" page comes to the screen. There are four tabs for the definition of the report.

General
After specifiying title of the report then select category and subcategory from the comboboxes. Category and subcategory defines our newly custom report's group and standing place in the reports list of the GRID reports page. You can also create new categories by clicking the buttons between the comboboxes.

Targets section enables you to limit the available list of target selection while generating the report or you can select a specified target to be used. I used "Specified Target" and left the textbox blank because this report will apply to all database and rac targets and is not allowed to be changed.

You can enable to select time periods by end user or you can preset the time period from the "Time Period" section (figure_create_report_general_tab). This custom report always displays the last known and most updated state of the databases uptimes and downtimes.

Elements
In the Elements tab, you can define the report elements and their positions in the report page (figure_add_elements_1). Report elements are structures that displays selected data from the GRID Servers repository. Click "Add" Button to add elements. I used IPMSG_USER_CHART_FROM_SQL and IPMSG_USER_TABLE_FROM_SQL elements (figure_add_elements_2). Click on the "Set Parameters" icon on the right hand side of the listed items to obtain a statement for the elements (figure_add_elements_2). Here you can enter the sql statement that brings data into the element from Management Servers repository. Following you can find the sql statements for the elements.

IPMSG_USER_TABLE_FROM_SQL

select
target_name,
target_type,
sum(up_time) up_time,
sum(down_time) down_time,
sum(blackout_time) blackout_time,
trunc(sum(up_time)/(sum(nvl(up_time,1))+sum(nvl(down_time,1)))*100) availability_pct
from
(
select
target_name,
target_type,
sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24)) total_hours,
case availability_status
when 'Target Down' then
0
when 'Target Up' then
0
when 'Blackout' then
sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24))
end blackout_time,
case availability_status
when 'Target Down' then
0
when 'Target Up' then
sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24))
when 'Blackout' then
0
end up_time,
case availability_status
when 'Target Down' then
sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24))
when 'Target Up' then
0
when 'Blackout' then
0
end down_time,
availability_status
from
MGMT$AVAILABILITY_HISTORY
where
target_type in ('oracle_database','rac_database') and
availability_status in ('Target Down','Target Up','Blackout')
group by
target_name, target_type, availability_status
order by target_name, availability_status
)
group by target_name, target_type
order by target_name

--sample output:

TARGET_NAME TARGET_TYPE UP_TIME DOWN_TIME BLACKOUT_TIME AVAILABILITY_PCT
----------- ----------- ------- --------- ------------- ----------------
ACTV rac_database 4823 0 1 100
ACTV_ACTV1 oracle_database 4823 0 1 100
ACTV_ACTV2 oracle_database 3964 0 1 100
ARCH.AVEA.COM oracle_database 3334 0 0 100
ARCHCM oracle_database 2210 1126 0 66
ARCHDB rac_database 3858 0 653 100
ARCHDB_ARCHDB1 oracle_database 3514 31 652 99
ARCHDB_ARCHDB2 oracle_database 3725 0 652 100
ASPDB oracle_database 1750 2 0 99
...


IPMSG_USER_CHART_FROM_SQL

select
availability_status,
sum(trunc((end_timestamp-start_timestamp)*24)) total_hours
from
MGMT$AVAILABILITY_HISTORY
where
target_type in ('oracle_database','rac_database') and
availability_status in ('Target Down','Target Up','Blackout')
group by
availability_status

--sample output:

AVAILABILITY_STATUS TOTAL_HOURS
------------------- -----------
Target Up 130008
Target Down 2086
Blackout 2405


Schedule
You can schedule a report to be emailed to an email by setting schedules (figure_schedule_tab).

Access
Report creator can define user groups or users who will be allowed to access this report. Here i used user groups (figure_access_tab).

Database availability report is now ready for managers

Friday, June 13, 2008

deleting from table causes fragmentation

One of our application groups periodically deletes data, which are older than 10 days, from some tables. This periodic action splits the table blocks to a wide range of datafile blocks. This causes performance problems on daily data loading and daytime reporting. As the first action plan gather statistics for trusted analysis.
begin
dbms_stats.gather_table_stats(
    ownname=>'PQ',
    tabname=>'GPRS_CELL_GPRS',
    estimate_percent=>33,
    degree=>2,
    cascade=>true);
end;
You can see the space fragmentation from the following. Currently the tables consumes nearly 3Gb 's of datafile usage. But if you calculate the average row length with the number of table rows, datafile usage should be 328Mb.
select ceil((blocks*8)/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
2926
*/
select ceil((avg_row_len*num_rows)/1024/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
328
*/ 
After this finding, following procedure is set to run periodically to delete 10 days older data from the table. After the delete operation table is moved for defragmentation. Table indexes should also be rebuilded. After the move and rebuild operation statistics should also be gathered. Keep in mind that table move and index rebuild operations blocks user access.

create or replace procedure avea_sysmon.P_TABLE_MAINTENANCE
  (g_table_name varchar2, g_owner varchar2) AUTHID CURRENT_USER as 
  
  cursor c1 is
    select * from sys.dba_indexes where table_owner=g_owner and table_name=g_table_name;

  r1 c1%rowtype;  
begin
    
    execute immediate 'delete from PQ.GPRS_TRAFGPRS3 where ' || 
                      'to_number(substr(CDATE,1,6)) < to_char(sysdate-15,''YYMMDD'')';
    commit; 
    execute immediate 'alter table ' || g_owner || '.' || g_table_name || ' move';
    
    open c1;    
    loop
      fetch c1 into r1;
      exit when c1%NOTFOUND;  
      execute immediate 'alter index ' || r1.table_owner || '.' || 
                         r1.index_name || '  rebuild';      
    end loop;
    
    dbms_stats.gather_table_stats(ownname=>g_owner,tabname=>g_table_name,
                                  estimate_percent=>33,cascade=>true);
    
end P_TABLE_MAINTENANCE;
FOLLOW UP: (23.11.2010) With Oracle10g there is a new way of reorganising your table segments. The best thing is there is not any unusable indexes anymore.
select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,29790

alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX enable row movement;
alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX shrink space compact;

select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,26790