Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script For Finding slow Running Query and Most CPU Utilization Query Using Top command PID

This script is very useful after Issue top command you will get PID which one Process taken More CPU Utilization  example for  32696 is the most CPU utilization process means take the PID then run the following script it will prompt "Enter the value Of PID" then give PID as 32696 you will get query then take the explain plan for that particular query then do Performance Tuning.
bash-4.1$ cat givepid.sh
HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
echo "Enter the value of PID";
read pid
$PSQL -d $DB -U $USER -p $PORT <<EOF
\pset format wrapped
SELECT now() - query_start as "runtime",state,query from pg_stat_activity WHERE pid= $pid;
EOF
exit 0;
OUTPUT:
Enter the value of PID
32696
Output format is wrapped.
    runtime     | state  |                                                                       query
----------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------
 00:00:01.93486 | active | select   timestatus::date from mhrornad_akl.edit_mut_new_audit WHERE ccode='270100010000760000' and report_status='Generated' and edit_mut_no=214
(1 row)

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL