Wednesday, September 2, 2015

Deleting Duplicate Record In A Table

To delete duplicate records in a table use the below query:

delete /*+ PARALLEL(4) */ from letter_request A
WHERE a.rowid > 
   ANY (
     SELECT 
        B.rowid
     FROM 
        letter_request B
     WHERE 
      A.letter_name='WA20' and A.etl_batch_id=695
     AND
        A.loan_number = B.loan_number
     AND 
        A.sequence_number = B.sequence_number
        
     );

Wednesday, July 23, 2014

Capturing network packets using "tcpdump"

tcpdump is used for packet analyzer which allows the user to intercept and display TCP/IP and other packets being transmitted or received over a network to which the computer is attached.

Installation of tcpdump on Solaris/Linux:
   pkgutil is similar utility for Solaris compared to yum in Linux and apt-get in Ubuntu.
First let's install pkgutil using following command:
   /usr/sfw/bin/wget http://mirror.opencsw.org/opencsw/pkgutil.pkg
   pkgutil install at /opt/csw/bin/pkgutil on Solaris

Use below command to install tcpdump on Solaris:
pkgutil -i coreutils [This installs tcpdump at /usr/sbin/tcpdump]


Usage of tcpdump:
    tcpdump -w [output-file]  -i  [physical-interface] port [port]
    I noticed that capturing of the packets is only limited to 96 bytes. Checking manual of tcpdump I found that there is an option to disable the 96 bytes limitation by using the option "-s 0".  
Use the following command to capture 65535 bytes of packets.
    tcpdump -w [output-file] -i [physical-interface] port [port] -s 0

Example: tcpdump -w p001.pcap -i eth0 port 8080 -s 0
The above command captures any packet transfered on "physical-interface" on port 8080.

Analysis:
   The output file can be analyzed using "Wireshark" GUI application available for free.

Friday, April 18, 2014

Taking Thread Dumps At Regular Interval From CLI In Unix

Good to see posting some article on my blog after a long gap :)

Today I will explain on a very simple but important area needed for performance monitoring and analysis. This article is related to thread dump collection during performance execution in unix.

Topic: "How to collect thread dumps from CLI in Unix at regular interval"

Uuh... looks very simple post.. isn't it...!!! YES IT IS...

Use below command to capture thread dumps every 5 min:
nohup ./get_jstack  [Output-Log-Dir]  300  > /dev/null 2>&1 &

Now let's see what are the content of "get_jstack" file:

#!/bin/bash
LOGDIR=$1
WAIT=$2
mkdir $LOGDIR/serverjstack; mkdir $LOGDIR/muxjstack; mkdir $LOGDIR/wdjstack;
i=1
while [ $i -gt 0 ]
do
   PID1=`cat $LOGDIR/xmppd.pid`
   /usr/jdk/latest/bin/jstack $PID1 > $LOGDIR/serverjstack/jstack$i
   PID2=`cat $LOGDIR/mux.pid`
   /usr/jdk/latest/bin/jstack $PID2 > $LOGDIR/muxjstack/jstack$i
   PID3=`cat $LOGDIR/wd.pid`
   /usr/jdk/latest/bin/jstack $PID3 > $LOGDIR/wdjstack/jstack$i
    sleep $WAIT
    echo $i
    i=`expr $i + 1`
done

Make sure you provide the absolute path of jstack file. serverjstack, muxjstack and wdjstack are the folders for 3 processes which are running on the unix machine. 

here you go.. jstack will be created in the folder that you pass from command line. 
Enjoy Analyzing Thread Dumps For Performance Bottlenecks...

More to come soon.... :)

Thursday, July 12, 2012

Linux Multicore::Disable Core in Linux

We can disable the few cores from the multi-core linux machines by following the below commands:
$ sudo sh -c "echo 'n' > /sys/devices/system/cpu/cpu1/online"
E.g.
echo 0 > /sys/devices/system/cpu/cpu2/online; echo 0 > /sys/devices/system/cpu/cpu3/online;
The above line will disable the CPU 2 and 3 on a 4 CPU machine.
To enable it again:
 echo 1 > /sys/devices/system/cpu/cpu2/online; echo 1 > /sys/devices/system/cpu/cpu3/online;
The following command will display only enabled CPU:
cat /proc/cpuinfo

Wednesday, April 13, 2011

Calculating How Many Maximum Number Of Users Required If We Need To Achieve 12K Transactions With SLA Of 5 Minutes

Total Number of Transactions to be completed = 12,000
Average Response Time = 5 Minutes

To avoid putting much load on the system I will add 5 Minutes so that to total time taken to complete one iteration will be 10 Minutes. This addition will include the thinktime between the each screen/transaction traverse.

Now, I know each iteration will take 10 Minutes to complete, hence in 60 Minutes only 6 transactions can be completed. i.e. 1 User will do 6 transactions in an hour.

Now how to calculate how many users are required to complete 12,000 transactions in an hour?
The calculation will be:
#Users Required = 12,000/6
= 2000 Users

This way we can find the number of users required if total throughput is provided with an average response time.

Hence to achieve 12,000 transactions I need 2000 Users with 5 minutes of thinktime in between the iterations with an average response time of 5 Minutes.

Hope this post helps you to come-up with a Workload Modelling Concept (a bit). Please do let me know if my assumptions are wrong.

Calculating Pacing Time/Think Time to achieve 50 TPS with an average response time of 0.5 seconds with total of 100 Users

Let us start with calculating total number of transactions in an hour.
1 sec = 50 trnx
3600 sec = x
x = 3600 * 50 = 180000 trnx/hour by 100 Users

We have total number of users given as 100. Let us see how to calculate how many transactions each user will perform.
Total Number of Users = 100
Each User will perform 180000/100=1800 transactions/hour

Since, every transaction is taking on an average 0.5 seconds, let us see how much time is required to complete the each user transactions.
To complete 1800 trnx it will take 1800*0.5 = 15 minutes

So now, let us see how much think-time is required to complete the required number transactions per User in an hour.
1800 transactions will complete in 15 minutes
hence, 45 minutes of thinktime is required in between 1800 transactions (i.e. 45*60 = 2700 seconds of thinktime required in between 1800 transactions (per user))
2700 seconds = 1800 trnx
x = 1 trnx
x = 1.5 seconds think time need to include

Let us see how much time is required to complete each iteration.
Total time required to complete each Iteration = x + 0.5 seconds
= 1.5 + 0.5
= 2 seconds

Verification: Let us verify if our above calculation is correct.
Total time = 1800 * 2
= 3600 seconds
= 1 Hr

So, Each User will perform 1800 transactions where we will provide 2 seconds for each Iteration to complete.

Sunday, September 26, 2010

Bit On Performance Tuning

Let us have some information about tuning concept to be introduced during performance testing. In my scenario, the number of users were supported by the application was only 18. Beyond this number all users were failing to logging with the "Database Related Error". The error that I was getting was "Couldn't create Database Instance". [looks like it is a Database related error]
When I started looking into this issue, we had few areas that we could look for the solution. They were:
1. Connection Pools
2. Tomcat (if tomcat is the Web Server)
3. OS system file
4. Hardware resources

I encountered this issue on Solaris box. I started finding the solution for this issue and below are areas I looked into:

1. I started looking at the Application Server Connection pools issue. We had few parameters in the web.xml file under the middle-tier where min_connex and max_connex parameter was defined related to the Database. These parameters were set to 2 and 5 respectively. The best way to start finding the solutions for any issue is the log that is being written. In the middle-tier log, out of 4, 3 connection pools were always idle. So, there was no problem at the middle-tier side.

2. I tried increasing the tomcat memory from 512MB to 1024MB in catalina.sh file. This didn't resolve the issue.

3. Looked into the /etc/System file on the Solaris OS and saw some parameters like maxusers, maxuproc (maximum user processes). Setting maxusers to a value will control max_nprocs and maxuprc. The algorithms are:
max_nprocs = 10 + (16 x maxusers)
maxuprc = max_nprocs – reserved_procs (default is 5)
Even this setting didn't resolve the issue.

4. last but not the least, I looked into the System resources and found that the root path (i.e. "/") doesn't have much disk space to allocate DB resources any more beyond 18 users. Since the Oracle was setup at "/" path and the Oracle folder wasn't assigned any more system resources, Oracle was utilizing the space allocated to "/". Due to in-sufficient disk space on "/" path, users were not able to make connection with the Oracle DB and hence was failing.
Increasing the root disk space eliminated the issue with the user login.

Hope this was a good article to understand the tuning concept applied before looking into in details of performance tuning.