Systems Engineering and RDBMS

Archive for July 31st, 2009

VMWare DRS (Distributed Resource Scheduler)

Posted by decipherinfosys on July 31, 2009

Distributed Resource Scheduler or DRS is an add-on feature of VI 3 infrastructure that is managed by Virtual Center. DRS allow balancing the CPU and memory resources of the virtual machines or VMs and the other ESX servers in the cluster.

DRS helps to balance the CPU and memory of its cluster members based on the configured resource pool policies such as shares, reservations and limits. The hosts and VMs are continuously monitored by the virtual center. Based on the configuration, if there is any imbalance of resources, the VMs are moved across the hosts in the DRS cluster.

The placement of VMs across the cluster can be configured based on:

  1. Affinity and anti-affinity rules – Rules that define which VMs can run together (affinity) and cannot run together(anti-affinity) in any given host. A perfect example for anti-affinity would be placement of a SQL server and Exchange server. At any point of time, you don’t want to place both the servers in the same host.
  2. VMotion compatibility – VMotion has it’s own set of requirements to move the VMs across the hosts. For example, if a VM that has a local network (not connected to any physical adapter) cannot be moved using VMotion.

Based on the environment and needs DRS automation can be set to the following levels:

  1. Manual – DRS only provides recommendation on placing the VMs. Manual action is required to place them on recommended hosts
  2. Partially automated – During VM power-on, they will be placed on the DRS recommended hosts. VM migrations caused by resource imbalance will be recommended by DRS but won’t be moved automatically
  3. Fully automated – DRS automatically places the VM during power-on also during resource imbalance on the DRS recommended hosts. The migration threshold level can also be set with this level between conservative and aggressive using a slide bar.

Few factors to consider about DRS:

  • There can be up to 32 hosts per DRS cluster.
  • It’s recommended to use combination of DRS automation levels based on the critical nature of VMs. To accomplish this, the cluster level DRS automation can be overridden by the VM level automation setting.
  • In the manual and partial automation level, it is important to pay attention to the number of stars on the recommendation. A 5-star recommendation should always be considered and applied.
  • Swap file location for the VMs is configurable in the DRS cluster and it is recommended to keep the swap file in the same directory in the VMFS datastore for performance reasons. Choosing to keep the swap file of the VM in the datastore based on the host setting will result in a poor VMotion performance during a resource imbalance.


More details about DRS cluster can be found here:

Posted in VMWare | Leave a Comment »

Creating an adhoc ASH (Active session history) report using timeframe

Posted by decipherinfosys on July 31, 2009

In one of our previous blog post, we demonstrated that how can we generate adhoc awr report based on the given range of snap_id.  As we are aware that default timeframe for collecting snapshot data is every hour and data is retained up to 7 days. Now let us assume that snapshot is taken every hour but we want some statistics within one hour period let’s say between 1:15 PM and 1:30 PM or between 2:17 PM to 2:48 PM etc. How can we generate report for such time frame? Again we can resort back to DBMS_WORKLOAD_REPOSITORY package. This package has quite a few procedures, which can help us out to troubleshoot the problem. Keep in mind that Oracle always recommends using awrrpt.sql and ashrpt.sql to generate the AWR or ASH reports. In this blog post, we will see once again how we can get the data for specific time range using ASH_REPORT_TEXT procedure.

ASH data is sampled every second and whenever AWR snapshot is flushed to the disk, content from v$active_session_history is also flushed to the disk. It system during that time is heavily utilized, then only sample data will be flushed to the disk.  Historical data is stored in another view called DBA_HIST_ACTIVE_SESS_HISTORY. Now let us get the data:

select dbid,instance_number ino,begin_interval_time,end_interval_time
from dba_hist_snapshot
order by begin_interval_time desc


2234839021    1    7/29/2009 5:00:46.793 PM    7/29/2009 5:30:37.953 PM
2234839021    1    7/29/2009 4:30:55.638 PM    7/29/2009 5:00:46.793 PM
2234839021    1    7/29/2009 4:00:01.491 PM    7/29/2009 4:30:55.638 PM
2234839021    1    7/29/2009 3:30:10.348 PM    7/29/2009 4:00:01.491 PM

Now, we know the value of dbid, instance_number and interval start time and interval end time. We can take this time as is and run report for the time range or we can run report for in between time range as well. In this case, we will run report for in between time range. But before that here is the signature of the procedure.

l_dbid          IN NUMBER,
l_inst_num      IN NUMBER,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL)
RETURN awrrpt_text_type_table PIPELINED;

We already know the value for l_Dbid, l_instnum, l_btime and l_etime from the first sql. For rest of the parameters we will go with the default values. Here is the sql to run for a specific time range.

SELECT output
to_Date('07/29/2009 16:10','MM/DD/YYYY HH24:MI'),
to_date('07/29/2009 16:25','MM/DD/YYYY HH24:MI'))

We can spool the output to the file from above sql and open it up as a text file to investigate the results. Here is the sample that confirms that report is generated for a specified time frame.


Analysis Begin Time:   29-Jul-09 16:10:00
Analysis End Time:   29-Jul-09 16:25:00
Elapsed Time:        15.0 (mins)
Sample Count:         527
Average Active Sessions:        0.59
Avg. Active Session per CPU:        0.05
Report Target:   None specified

As mentioned earlier, policy to collect AWR data varies from company to company as each company has its own set of standards to follow.


  • Oracle-base article – here.
  • Oracle 10g Performance Tuning guide – here.

Posted in Oracle | 1 Comment »