Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR) is used to collect performance statistics including: 

•  Wait events used to identify performance problems. 
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views. 
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view. 
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views. 
• Object usage statistics. 
• Resource intensive SQL statements. 

Please find find below a sample report.

Analyzing Index Usage and Deciding to Remove Index

In my previous post Rebuilding Indexes for better Performance I talked about how to rebuild indexes for better performance. Here, we will see how to identify indexes which are not used.

Databases have two primary storage needs; data pages and index pages.  Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored.  On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used.  So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?

In SQL Server, many new dynamic management objects have been created that allow you insight into a lot of data that was not accessible or just difficult to get in previous versions of SQL Server.  One new function and one new view that provide data about index usage are sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats.

sys.dm_db_index_operational_stats

This function gives you information about insert, update and delete operations that occur on a particular index.  In addition, this view also offers data about locking, latching and access methods.  There are several columns that are returned from this view, but these are some of the more interesting columns:

• leaf_insert_count - total count of leaf level inserts
• leaf_delete_count - total count of leaf level deletes
• leaf_update_count  - total count of leaf level updates

Here is a sample query that provides some of the key columns that are helpful to determine insert, update and delete operations.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Here is the output from the above query.  From this function we can get an idea of how many inserts, updates and delete operations were performed on each table and index. From this view we can get an idea of how many inserts, updates and delete operations were performed on each table and index.



sys.dm_db_index_usage_stats

This view gives you information about overall access methods to your indexes.  There are several columns that are returned from this DMV, but here are some helpful columns about index usage:

• user_seeks - number of index seeks
• user_scans- number of index scans
• user_lookups - number of index lookups
• user_updates - number of insert, update or delete operations

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Below is the output from above query. From this view we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred.
From this view we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred.



Now there is one catch here, the values for these counters get reset each time you restart SQL Server.  In addition, the values for the data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available.

Stay connected for more :) Happy Learning.

Rebuilding Indexes for better Performance

Hi All, wishing you a Very Happy New Year and great success and learning this year. I am continuing from my last post http://performancetestingcentre.blogspot.in/2014/10/analyzing-extent-of-index-fragmentation.html

After you analyse the index report, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries.

You can use the following command

ALTER INDEX <<index_name>> REBUILD;

You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. You can use the below command to do so.

ALTER INDEX <<index_name>> REBUILD ONLINE;

Rebuilding an index based on an existing data source removes intra-block fragmentation. If we compare this to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

Before considering the rebuilding of indexes you should also check the below conditions for Rebuilds.

• Large free space (generally 50%+), which indexes rarely reach, and
• Large selectivity, which most index accesses never reach, and
• Response times are adversely affected, which rarely are.
• Note requirement of some free space anyways to avoid insert and
• subsequent free space issues

Click here to know more


Benefits of rebuild based on various dependencies which include:

• Size of index
• Clustering Factor
• Caching characteristics
• Frequency of index accesses
• Selectivity (cardinality) of index accesses
• Range of selectivity (random or specific range)
• Efficiency of dependent SQL
• Fragmentation characteristics (does it effect portion of index frequently used)
• I/O characteristics of index (serve contention or I/O bottlenecks)

Myths:

• The vast majority of indexes do not require rebuilding
• Oracle B-tree indexes can become "unbalanced" and need to be rebuilt is a myth
• Deleted space in an index is "deadwood" and over time requires the index to be rebuilt is a myth
• If an index reaches "x" number of levels, it becomes inefficient and requires the index to be rebuilt is a myth
• If an index has a poor clustering factor, the index needs to be rebuilt is a myth
• To improve performance, indexes need to be regularly rebuilt is a myth

Sometimes you create Indexes that are not even used and you can drop those Indexes, I will explain more on that in my next post. Happy Learning :)

Analyzing the extent of index fragmentation and rebuilding the indexes for performance improvement

Hi Readers, Glad to meet you again. Carrying forward the promise made in my earlier post I am writing here how to analyze the extent of fragmentation an index is having and how to rebuild an index in case that is required.

Click here to know more

1. Analyzing the extent of index fragmentation : There are various factors which help us to analyze this and below are the main ones.

a)      The number of deleted leaf nodes - The number of index nodes that have been logically deleted as a result of row deletes.

b)      Index height - The number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may initiate additional levels of an index to accommodate the new rows.

The commands to analyze an index are below.

ANALYZE INDEX index_name COMPUTE STATISTICS 

ANALYZE INDEX index_name VALIDATE STRUCTURE 

After you analyze the reports above, you may consider rebuilding the index where the height is more than three levels. I will explain other details in next post. Stay Tuned!!! Stay hungry to learn :)

Database Tuning – Indexes

Hi Guys, after a quite long time I am posting this article today, this post is more of into database tuning and in this I have elaborated following things.

1. What are indexes in databases?

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

Click here to know more

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

2. What is Index Fragmentation?

When data is inserted into, deleted from, or updated in a SQL Server table, the indexes defined on that table are automatically updated to reflect those changes. As the indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering of the data no longer matches the physical ordering, which can lead to a deterioration of query performance.

There are two types of fragmentation:

a) Internal Fragmentation indicates that there is too much free space on the index page.

b) External Fragmentation indicates that the logical ordering and physical ordering do not match

I will explain more on this later, in my next post and off course very soon so stay tuned :) . Keep reading.

General Performance Counters in Performance Testing - Processor, Memory, Disk and Network Counters

People often ask what counters are. What do you normally measure in performance testing?

A lot of them think only response time is enough, but this is not the case. Here, I have stated the main counters that we track while load testing.

These are divided mainly into four types:

Click here to know more
1. Processor Performance Counters. The Processor object is focused on the CPU of the system. In this category we track following things:

Processor : % Interrupt Time

Processor : % User Time

Processor : %Privilege Time

System : Processor Queue Length

System : System Calls/sec


2. Memory Performance Counters. In the category of memory object, we track following counters: 

Memory : Available Memory

Memory : Page Faults/sec

Memory : Pages Input/sec

Memory : Pages Output/sec

Memory : Committed Bytes


3. Disk Performance Counters. The Disk Performance counters help to evaluate the performance of the disk subsystem. The disk subsystem includes disk controller card, the I/O bus of the system, and the disk.

PhysicalDisk : Current Disk Queue Length

PhysicalDisk : % Disk Time

PhysicalDisk : Disk Reads Bytes/sec

LogicalDisk : Disk Reads Bytes/sec


4. Network Performance Counters. In this we track the counters associated with the network interface, network segment, and TCP/IP components.

Network Interface : Bytes Sent/sec

Network Interface: Bytes Received/sec

Network Segment : %Broadcasts

Network Segment : %Multicasts


Will be back soon!!!

Stay Tuned!!!

Happy Learning :) !!!

How to change the 90th percentile column in the summary report of Loadrunner Analysis to get customized report with some other percentile values?

Loadrunner always shows the transaction summary table with 90^th percentile of transaction response time values as shown in below image. 

Suppose, I want to prepare a customized report showing 50^th percentile for the transaction response time values.

To change the percentile value go to properties window and click on percentile value as shown below.

Click here to know more

Analysis summary filter window will open.

Now change the transaction percentile value from 90 to 50.

Click ok. Loadrunner analysis will show a message box displaying Applying summary filter.

 And the transaction summary table will change to below.

Bingo!!! You did it.

What is the 90th percentile and how to calculate 90th percentile?

Before explaining the 90th percentile let’s see what is percentile. A percentile is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations fall.

For example the 30th percentile is the value below which 30 percent of the observations may be found.

In similar way the 90th percentile is the value below which 90 percent of the observations may be found.

How to calculated 90^th percentile?

Click here to know more

As we know it is the value from which 90% of the samples are smaller so let’s find the 90^th percentile in simple easy steps.

Step 1: Sort the values in increasing order.

Step 2: Eliminate the top 10% values.

Step 3: The highest value you are left with is the 90^th percentile.

Example:

Suppose we have following sample of data: 1, 5, 3, 9, 4, 7, 7, 9, 11, and 21.

Sorting the data in ascending order gives: 1, 3, 4, 5, 7, 7, 9, 11 and 21.

After removing top 10% values (10% of 10 = 1) we have 1, 3, 4, 5, 7, 7, 9 and 11.

The 90^th percentile is 11.

What is the significance of 90^th percentile?

It answers the following questions:

1. What percentage of transactions have a response time less than or equal to X seconds?

2. What is the time under which 90 percent of the transactions are responding?

Loadrunner Analysis parsing error, unable to analyse results and generate analysis report.

When we run a test for longer duration of time the number of samples in the result set will also be more.  The total number of samples generated can easily be calculated using the below formula:

Number of Samples = Total Duration of Run / Frequency sampling

Suppose, we want to calculate total samples for a test run for 1 hour and frequency of sampling is 10 seconds. Using the formula we have

Number of samples = 1 hour/ 10 seconds = 60*60 seconds/10 seconds = 360 samples.

Now these samples contribute to the size of result set, so more number of samples will make a very big result set and this creates issues (sometimes).

While analysing the big result set the Analysis throws below error.

Getting Parsing error with large result sets (less than 2 GB).: Parse Command line error: Unable to cast COM object of type ‘System._ComObject’ to interface type ‘Borland.Vcl.Fields’. This operation failed because the QueryInterface call on the COM component with IID ‘{XXXXXXXXXXXX}’ failed due to the following error: No such interface supported. (Exception from HRESULT XXXXXXXXX (E_NOINTERFACE)).

Click here to know more

This issue is resolved by changing the Data Source setting in the Result Collection tab of LR Analysis. 

Go to Tools > Options > Result Collection tab. 

The default value is Generate Complete data only and we need to change this to Display Summary while generating complete data as shown in the below image.

Stay Tuned for more Analysis stuff!!! Happy Learning and Loadrunning!!!

What is pacing time and why to use pacing time?

Pacing time is used to control the pace of the users in load test. This helps us to accurately achieve desired load against application under test. Pacing is the wait time between the action iterations.

The pacing is set in run time setting as shown below.

We have following options for pacing:

1. As soon as the previous iteration ends: Loadrunner starts the next iteration as soon as possible after the previous iteration ends.

2. After the previous iteration ends: In this Loadrunner starts each new iteration after a specified amount of time, the end of the previous iteration. We can either give an exact number of seconds or a range of time.

3. At <fixed/random>intervals: In this we can specify the time between iteration either a fixed number of seconds or a range of seconds from the beginning of the previous iteration. The next scheduled iteration will only begin when the previous iteration is complete.

Why to use pacing time?

Click here to know more

Pacing time is used to control the transactions passed during the test and the hits on Application under test.

I will explain How to calculate the pacing time and think time? In my next post so stay tuned. Happy Learning!!!

What is Load Balancing? How Load balancing helps in improved performance?

Load balancing is mainly to distribute the workload across multiple computers or a computer cluster, CPUs, disk drives, or other system resources.

This optimizes resource use, maximizes throughput, minimizes response time, and avoids overload. Using multiple components with load balancing instead of a single component may increase reliability through redundancy. Load balancing is usually provided by dedicated software or hardware, such as a multilayer switch or a Domain Name System server Process.

Why to do Load Balancing?

Click here to know more

From the first glance it looks like an overhead but it is not; actually this helps us to improve performance.

1.      Load balancing increases scalability to accommodate higher volume work by distributing (balancing) client requests among several servers. This allows more servers (hosts) to be added to handle additional load.

2.      Load balancing ensures uninterrupted continuous availability of critical and key business applications. When the applications are on more than one machine, operations personnel can work on one machine while the other is busy working.

3.    It helps the system to be ready to accept and handle growth and make it fault resilience, so if a server crashes or just needs maintenance work done, an alternative server can take over.

Types:

There are various algorithms for load balancing and it totally depends on the context which one you want to use. Some of them are listed below:

1.    Round robin: This distributes the request to different servers in a round robin manner independent of the load on each server. The problem with this type is it works blindly and even if server is overloaded it will queue the request.

2.    Denoted Connections: This algorithm keeps track of the number of active connections to each server and always sends new request to server with least number of connections. If two servers have same number of connections, it selects the server with lowest server identifier. The disadvantage with this is when system is empty the same server is used all the time.

3.    Round Trip: This algorithm monitors the first buffer time for each connections of the servers. The mean time is calculated over a averaging window and the average value is reset at the end of averaging window. The server with least mean value will get the request. This is complex and can be implemented with limited set of variables.

4.    Transmit Byte: This algorithm keeps track of the amount of transmitted bytes from each web server since the last averaging reset and uses this to allocate the request to the server.

How to encode a string as if a password in Loadrunner?

There are many ways to encode a string in Loadrunner. I already have explained the one using password encoder tool. Below is another way to encode the string but this will convert the plain string to URL format. 

Let’s see how, in this I have defined a function EncodePlainToURL() which accepts a string and encodes it to URL format.

char *EncodePlainToURL(const char *sIn, char *sOut)

 {

                    int i;

                    char cCurChar;

                    char sCurStr[4] = {0};

                     sOut[0] = '\0';

                     for (i = 0; cCurChar = sIn[i]; i++)

                     {

                            if (isdigit(cCurChar) || isalpha(cCurChar))

                             {

                                     sprintf(sCurStr, "%X", cCurChar);

                              }

                             else

                             {

                                                                                                                                                                                      sprintf(sCurStr, "%%%X", cCurChar)

                              }

                               strcat(sOut, sCurStr);

                       }

                    return sOut;

 }

Action()

{

                char sOut[100];

                char sIn[] = "password";

                lr_output_message("the encoded output is %s", EncodePlainToURL(sIn, sOut));

Click here to know more

                return 0;

}

Hope you will try this at home ;) . Thanks for reading :) 

Can we use Load generators on Virtual machines?

Well of course we can, if it is just for the sake of using but this will alter the response time of the tests.

HP never recommends using VM’s and the HP guide states that the VMware's clock does not synchronize with the hardware clock. This causes inaccuracy in the response time obtained.

This implies that load test results are not showing the actual performance of the application as opposed to what it would be in the real world.

What is VM Clock drift?

Click here to know more

The term Clock drift describes situations where the reported time diverges from the correct time at an approximately constant rate.

There are two main reasons for this:

•  Time in a virtual machine drifts because the hardware time source used by the virtual machine monitor drifts
•  Time in a virtual machine drifts at a constant rate

The system clock in VM’s is also virtualized and occasionally has to re synchronize with the physical system clock. This system clock is tied to your virtual user timing directly and the amount of drift is unpredictable and ungovernable.

What is a page fault?

An interrupt that occurs when a program requests data that is not currently in real memory. The interrupt triggers the operating system to fetch the data from a virtual memory and load it into RAM.

An invalid page fault or page fault error occurs when the operating system cannot find the data in virtual memory. This usually happens when the virtual memory area, or the table that maps virtual addresses to real addresses, becomes corrupt.

Click here to know more
The read/write speed of a hard drive is much slower than RAM, and the technology of a hard drive is not geared toward accessing small pieces of data at a time. If your system has to rely too heavily on virtual memory, you will notice a significant performance drop. The key is to have enough RAM to handle everything you tend to work on simultaneously -- then, the only time you "feel" the slowness of virtual memory is is when there's a slight pause when you're changing tasks. When that's the case, virtual memory is perfect.

When it is not the case, the operating system has to constantly swap information back and forth between RAM and the hard disk. This is called thrashing, and it can make your computer feel incredibly slow.

The area of the hard disk that stores the RAM image is called a page file. It holds pages of RAM on the hard disk, and the operating system moves data back and forth between the page file and RAM. On a Windows machine, page files have a .SWP extension.

Next, we'll look at how to configure virtual memory on a computer.

How to convert plain text into hexadecimal and octal formats in Loadrunner?

I already have explained how to use web_convert_param function. In this post I will write about how to convert plain test to hexadecimal format. I once used this for encoding a string.

char *PlaintoHexadecimal(const char *InputS, char *OutputS)

{   

                int i;

                char CurrentChar;

                char CurrentStr[4] = {0};   

               

                OutputS[0] = '\0'; 

                for (i = 0; CurrentChar = InputS[i]; i++)  

                {

                                sprintf(CurrentStr, "%X", CurrentChar);          

                                strcat(OutputS, CurrentStr);   

               

                } 

            return OutputS;

               

}

Action()

{

   

char InputS[] = "This is plain string";

char OutputS[100];

lr_output_message("The input is %s",InputS);

lr_output_message("The output is %s", PlaintoHexadecimal(InputS, OutputS));

return 0;

}

Once you execute the above code, the string will be converted to hexadecimal format as shown below:

Wow Done!!!! Now how to convert the plain text in octal format?

Just change the X factor to o factor ;). I am not kidding. You just have to change the below string

sprintf(CurrentStr, "%X", CurrentChar);        to     sprintf(CurrentStr, "%o", CurrentChar);          

Hope you will execute that and see what happens. I got this:

Click here to know more

Thank you so much for reading and following.

How to convert plain text into URL format in Loadrunner?

Sometimes while parametrization and correlation we need to put a check on the type of value that is being passed. And we need to convert the format of the variable. For this we have a function in LoadRunner web_convert_param this function either converts HTML text to plain text or URL, or converts plain text to URL.

The syntax of web_convert_param is as:

int web_convert_param( const char *ParamName, [char *SourceString] char *SourceEncoding, char *TargetEncoding, LAST );

In the below example we are converting string "Plain text to be converted to URL" to URL format from plain format.

Example:

Click here to know more

Action()

{

char param1[]="Plain text to be converted to URL";

lr_save_string(param1, "Input");

lr_output_message("The value of input is : %s",lr_eval_string("{Input}"));

web_convert_param("Input","SourceEncoding=PLAIN","TargetEncoding=URL", LAST );

lr_output_message("The value after converting is : %s",lr_eval_string("{Input}"));

return 0;

}

The output will be as :

 The following table shows same content in the three formats :

HTML	URL	Plain Text
&lt;mytag&gt;&amp;	%3Cmytag%3E%26	<mytag>&

That’s all for today :). Thanks for reading guys!!!

How to encrypt a string in Loadrunner using password encoder? How to use the encrypted value in script?

Suppose you face a situation, when you want to encrypt your password and use this value in script. This is a cake walk.

You have to go to password encoder, the Password Encoder tool lets you enter regular text and convert it to an encoded string.

Choose Start > Programs > LoadRunner > Tools > Password Encoder to open the Password Encoder tool.

Password: Enter the password you want to encode, and then click Generate.

Encoded string: Displays the encoded password string.

Generate: Generates an encoded string after a password is entered.

Copy: Copies the encoded password string to the clipboard.

Done!!!

Now, How to use the encrypted value in script?

Click here to know more

This is done by using lr_decrypt function as shown below in script.

vuser_init()

{

    web_set_proxy("oregon:8080");

    web_set_user("oregon_user",

    lr_decrypt("518a296870b8b8d477e2f63cbbe5f2a0")," oregon:8080");

    web_url("web_url",

        "URL=http:// oregoncity.com /",

        "TargetFrame=",

        "Resource=0",

        "Referer=",

        LAST );

}