To add up column values in a file - UNIX

To all up all the values in a particular column of a file in unix use below command

Ex: sample.txt contains below data
10 20 30
40 50 60
70 80 90

Now I want to add all values in column two of above file sample.txt

$ cat sample.txt | awk -F' '  '{print $2}'  | awk '{total = total +$1}END{print total}'

Output: 150

How to rename or unzip or untar and vice-versa all files in a directory with same file extension - UNIX

To rename or unzip or untar and vice-versa all files in a directory use below commands

Command:
$ for i in `ls -latr *.txt.2013 | awk '{print $9}'`
do
mv $i `basename $i .2013`
done

The above command will rename all files in a directory for example test01.txt.2013, test02.txt.2013, test03.txt.2013 to test01.txt, test02.txt, test03.txt

Note: All lines after $ symbol line together form the command.

A similar command can be used to unzip or untar all files in a directory

Command:

$ for i in `ls -latr *.txt.gz | awk '{print $9}'`
do
gunzip $i
done

The above command will unizp all files in a directory for example test01.txt.gz, test02.txt.gz, test03.txt.gz to test01.txt, test02.txt, test03.txt

Note: All lines after $ symbol line together form the command.

How to print or trim end characters of a string/line in UNIX

The commands below help in printing end characters of a line or a string. I assume that ORACLE_SID is set to "TESTDB" for examples below.

Print last one character from ORACLE_SID: 

$ echo $ORACLE_SID | sed -e "s/^.*\(.\)$/1/"
Output: B

Print last two characters:
$ echo $ORACLE_SID | sed -e "s/^.*\(..\)$/1/"
Output: DB

Print last three characters:
$ echo $ORACLE_SID | sed -e "s/^.*\(...\)$/1/"
Output: TDB

The commands below help in trimming end characters of a line or a string.  I assume that ORACLE_SID is set to "TESTDB" for examples below.

Trims last one character:
$ echo $ORACLE_SID | sed 's/.$//'
Output: TESTD

Trims last two characters:
$ echo $ORACLE_SID | sed 's/..$//'
Output: TEST

Trims last three characters:
$ echo $ORACLE_SID | sed 's/...$//'
Output: TES

SQL Query to find top SQL's with two specific AWR Snapshots

SQL:

WITH subq as
(SELECT *
    FROM (SELECT s.sql_id,
                                  SUM(nvl(s.executions_delta, 0)) execs,
                                  SUM(round(s.elapsed_time_delta / 1000000, 3)) exec_time,
                                  SUM(round(s.buffer_gets_delta)) io_logical,
                                  SUM(round(s.cpu_time_delta / 1000000, 3)) cpu_time,
                                  SUM(nvl(s.parse_calls_delta, 0)) parse_calls,
                                  SUM(round(s.iowait_delta / 1000000, 3)) io_wait,
                                  SUM(nvl(s.disk_reads_delta, 0)) disk_reads
                     FROM dba_hist_sqlstat s, dba_hist_snapshot ss
                  WHERE s.snap_id BETWEEN start_snap_id AND end_snap_id
                        AND ss.snap_id = s.snap_id
                        AND ss.instance_number = s.instance_number
                        AND s.executions_delta > 0
                 GROUP BY s.sql_id
                 ORDER BY exec_time DESC)
  WHERE rownum < num_of_sqls)
SELECT subq.*, substr(sql_text, 1, 8000) sqltext
    FROM dba_hist_sqltext st, subq
 WHERE st.sql_id=subq.sql_id
 ORDER BY 3 desc

Things to replace in above sql before executing:

start_snap_id: replace this with starting AWR snapshot number of your database from your peak day snapshots for the period which you want collect the top SQL's. For example: Starting snapshot of database TEST on wednesday at 9AM is 12345.

end_snap_id: replace this with ending AWR snapshot number of your database from your peak day snapshots for the period which you want collect the top SQL's. For example: Ending snapshot of database TEST on wednesday at 9AM is 12355.

num_of_sqls: Give the number of how many top SQL's you want to collect using the query for example if you want to collect top 50 SQL's replace num_of_sqls with 50

Undo Latch Contention

Here is the SQL to find sessions causing UNDO Latch Contention:

select

   swh.seq#,

   sess.sid,

   sess.username username,

   swh.event     event,

   swh.p1,

   swh.p2

from

   v$session               sess,

   v$session_wait_history  swh

where

   sess.sid = 798

and

   sess.sid = swh.sid

order by

   swh.seq#;

Sample Output:

     SEQ#     SID      USERNAME        EVENT                                                  P1                      P2
---------- -------    --------------- ------------------------------------   --------------       ----------
         1        798     ORCL               latch free                                              5.0440E+17        127
         2        798     ORCL               latch free                                              5.0440E+17        127
         3        798     ORCL               latch free                                              5.0440E+17        127
         4        798     ORCL               latch free                                              5.0440E+17        127
         5        798     ORCL               latch: cache buffers chains           5.0440E+17        122
   


5 rows selected.