write a script for this pattern...

Shell Programming and Scripting
1

I have a file say test.txt, data in that file is in format such that
1st column represents number of executions of particular function
2nd column represents total amount of time
3rd column represents function name

I want to get the poor/slow performing function from this file.
So, ideally what need to do is...
(1) divide the 2nd column by 1st column
(make sure there is no divide by ZERO error)
(2) sort the output in such a way that the record with greater output of step(1) is at top
(3) depending on this sorting display the result file.
result should not contain the division but should contain the original value.

example...

inputfile...

$ cat test.txt
3       (0 sec, 804275 nanosec)  abcd
721     (253 sec, 632655866 nanosec)     efgh
1873    (21 sec, 887264375 nanosec)      hijk
2006    (0 sec, 922300 nanosec)  lmhn
3       (0 sec, 3332 nanosec)    sjhfdf
2       (0 sec, 2367 nanosec)    dkfi
580     (0 sec, 2138703 nanosec)         dfhjf
44486   (0 sec, 858447770 nanosec)       oet
21102   (739 sec, 891470054 nanosec)     lldff
140     (0 sec, 90282 nanosec)   sfowe
123     0 sec, 324590 nanosec)  xjjv
8939    (0 sec, 337230856 nanosec)       asadj
19      (0 sec, 1455434 nanosec)         sahjdi
210     (0 sec, 89131 nanosec)   posdio
3       (0 sec, 3750149 nanosec)         jhd
873     (260 sec, 715043557 nanosec)     lppeuv
16      (0 sec, 212024 nanosec)  hjass
13668   (2 sec, 506391927 nanosec)       nnuua

-------------<end of file>------------------------

here... after division we see these are greater...
253/721 = 0.35090152
260/873 = 0.29782359

so output should look like...
721 (253 sec, 632655866 nanosec) efgh
873 (260 sec, 715043557 nanosec) lppeuv
.
.

2

if you have Python

#!/usr/bin/env python
import operator
d={}
for line in open("file"):
    line=line.strip().split()
    d.setdefault(line[-1],"")
    try:
        d[line[-1]] = int(line[1].strip("("))/int(line[0])
    except Exception,e: pass
for i in sorted(d.iteritems(), key=operator.itemgetter(1), reverse=True):
    print i

output

# ./test.py
('efgh', 0.35090152565880722)
('lppeuv', 0.29782359679266895)
('lldff', 0.035020377215429818)
('hijk', 0.011211959423384944)
('nnuua', 0.00014632718759145449)
('abcd', 0.0)
('sahjdi', 0.0)
('sfowe', 0.0)
('jhd', 0.0)
('posdio', 0.0)
('asadj', 0.0)
('oet', 0.0)
('sjhfdf', 0.0)
('lmhn', 0.0)
('dkfi', 0.0)
('xjjv', 0.0)
('hjass', 0.0)
('dfhjf', 0.0)
3

this will solve your divide by zero problem

awk -F"[( ]" '{if ($1=="0"){print "0.000000 "$NF}else{printf "%f %s\n",($3+($5/1000000000))/$1,$NF}}' filename|sort -r -k1
4

awk -F"[( ]" '{if ($1=="0"){print "0.000000 "$NF}else{printf "%f %s\n",($3+($5/1000000000))/$1,$NF}}'perf.fmt|sort -r -k1
Syntax Error The source line is 1.
The error context is
{if ($1=="0"){print "0.000000 (NF)}else{printf "%f >>> %s\n",($3+($5/1000000000))/$1(NF)}}perf. <<< fmt
awk: 0602-500 Quitting The source line is 1.

-----Post Update-----

After changing input file name from perf.fmt to perf, im getting below error

$ awk -F"[( ]" '{if ($1=="0"){print "0.000000 "$NF}else{printf "%f %s\n",($3+($5/1000000000))/$1,$NF}}'perf|sort -r -k1

awk: 0602-566 Cannot divide by zero.
The input line number is 1.
The source line number is 1.

5

can anyone fix this problem?

6

you have to give a space between awk and filename

awk -F"[( ]" '{if ($1=="0"){print "0.000000 "$NF}else{printf "%f %s\n",($3+($5/1000000000))/$1,$NF}}'<space>filename

IF the test file you provided is correct code works perfectly

/home/vidya_perl> cat vv
3 (0 sec, 804275 nanosec) abcd
721 (253 sec, 632655866 nanosec) efgh
1873 (21 sec, 887264375 nanosec) hijk
2006 (0 sec, 922300 nanosec) lmhn
3 (0 sec, 3332 nanosec) sjhfdf
2 (0 sec, 2367 nanosec) dkfi
580 (0 sec, 2138703 nanosec) dfhjf
44486 (0 sec, 858447770 nanosec) oet
21102 (739 sec, 891470054 nanosec) lldff
140 (0 sec, 90282 nanosec) sfowe
123 (0 sec, 324590 nanosec) xjjv
8939 (0 sec, 337230856 nanosec) asadj
19 (0 sec, 1455434 nanosec) sahjdi
210 (0 sec, 89131 nanosec) posdio
3 (0 sec, 3750149 nanosec) jhd
873 (260 sec, 715043557 nanosec) lppeuv
16 (0 sec, 212024 nanosec) hjass
13668 (2 sec, 506391927 nanosec) nnuua
/home/vidya_perl> awk -F"[( ]" '{if ($1=="0"){print "0.000000 "$NF}else{printf "%f %s\n",($3+($5/1000000000))/$1,$NF}}' vv |sort ->
0.351779 efgh
0.298643 lppeuv
0.035063 lldff
0.011686 hijk
0.001250 jhd
0.000268 abcd
0.000183 nnuua
0.000077 sahjdi
0.000038 asadj
0.000019 oet
0.000013 hjass
0.000004 dfhjf
0.000003 xjjv
0.000001 sjhfdf
0.000001 sfowe
0.000001 dkfi
0.000000 posdio
0.000000 lmhn
7

Thanks vidyadhar85 for details.

Now im getting good results but...there is something diff from your results...check the 1st row. It has NaNQ.

$ $3+($5/1000000000))/$1,$NF}}' perf |sort -r -k1 | more <
NaNQ xjjv
0.351779 efgh
0.298643 lppeuv
0.035063 lldff
0.011686 hijk
0.001250 jhd
0.000268 abcd
0.000183 nnuua
0.000077 sahjdi
0.000038 asadj
0.000019 oet
0.000013 hjass
0.000004 dfhjf
0.000001 sjhfdf
0.000001 sfowe
0.000001 dkfi
0.000000 posdio
0.000000 lmhn

When I use this script for different input file which has similar format/pattern....I get all 1st column values as NaNQ.
Please let me know where is the problem.

8

Thats because the row containing NaNQ is missing a "(" after no of execution

9

Thanks vidyadhar85 I got that meaning now.
But, still the script dont work for other input files....plz see this input file...

$ cat test.txt
192     (324 sec, 747387967 nanosec)     sqljsParse
194     (324 sec, 742760541 nanosec)     sqljsParseRdbAccessed
507     (283 sec, 660176478 nanosec)     sqlkf_sync_request
273     (269 sec, 673915252 nanosec)     sqlkdInterrupt
39      (269 sec, 337740235 nanosec)     sqlrkcmt
39      (269 sec, 654343714 nanosec)     sqlrrcom_dps
39      (269 sec, 666073125 nanosec)     sqlrrcom
39      (269 sec, 698604620 nanosec)     sqlrr_commit
39      (269 sec, 698958957 nanosec)     sqljs_ddm_rdbcmm
78      (269 sec, 583300457 nanosec)     sqlrr2pd
417     (37 sec, 834469926 nanosec)      sqlra_cache_space_mgmt
417     (37 sec, 846534878 nanosec)      sqlra_cache_reserve_mem
597     (31 sec, 705247032 nanosec)      sqlo_xlatch::getConflict
417     (30 sec, 633582480 nanosec)      sqlra_csm_flush_vars
39      (27 sec, 660368820 nanosec)      sqlrr_execimmd
39      (27 sec, 674433628 nanosec)      sqljs_ddm_excsqlimm
75      (19 sec, 155225907 nanosec)      sqlra_find_var
75      (19 sec, 190364402 nanosec)      sqlra_get_var
39      (16 sec, 305720732 nanosec)      sqlrr_execute_immed
117     (14 sec, 151555035 nanosec)      sqlrices
117     (14 sec, 177620542 nanosec)      sqlriCloseExecuteSection
117     (14 sec, 54696517 nanosec)       sqlrigca
156     (14 sec, 126475719 nanosec)      sqleReceiveAndMergeReplies
234     (14 sec, 513398100 nanosec)      sqloWaitEDUWaitPost
234     (14 sec, 526877995 nanosec)      sqlkf_get_next_buffer
234     (14 sec, 527734589 nanosec)      sqkd_bds_buffer_table::getNextBuffer
234     (14 sec, 528675358 nanosec)      sqlkd_rcv_buffer
234     (14 sec, 529943418 nanosec)      sqlkd_rcv_get_next_buffer
234     (14 sec, 530817210 nanosec)      sqlkd_rcv_init
234     (14 sec, 545068593 nanosec)      sqlkdReceiveReply
35      (14 sec, 825808972 nanosec)      sqlrr_prepare
35      (14 sec, 830216291 nanosec)      sqljs_ddm_prpsqlstt
154     (12 sec, 142066439 nanosec)      sqlragsn
194     (12 sec, 340492976 nanosec)      sqlrr_sql_request_pre

--------------<end of input file>------------------

awk -F"[( ]" '{if ($1=="0"){print "0.000000 "$NF}else{printf "%f %s\n",($3+($5/1000000000))/$1,$NF}}' test.txt |sort -r -k1 | more
0.000000 sqlrrcom_dps
0.000000 sqlrrcom
0.000000 sqlrr_sql_request_pre
0.000000 sqlrr_prepare
0.000000 sqlrr_execute_immed
0.000000 sqlrr_execimmd
0.000000 sqlrr_commit
0.000000 sqlrr2pd
0.000000 sqlrkcmt
0.000000 sqlrigca
0.000000 sqlrices
0.000000 sqlriCloseExecuteSection
0.000000 sqlragsn
0.000000 sqlra_get_var
0.000000 sqlra_find_var
0.000000 sqlra_csm_flush_vars
0.000000 sqlra_cache_space_mgmt
0.000000 sqlra_cache_reserve_mem
0.000000 sqlo_xlatch::getConflict
0.000000 sqloWaitEDUWaitPost
0.000000 sqlkf_sync_request
0.000000 sqlkf_get_next_buffer
0.000000 sqlkd_rcv_init
0.000000 sqlkd_rcv_get_next_buffer
0.000000 sqlkd_rcv_buffer
0.000000 sqlkdReceiveReply
0.000000 sqlkdInterrupt
0.000000 sqljs_ddm_rdbcmm
0.000000 sqljs_ddm_prpsqlstt
0.000000 sqljs_ddm_excsqlimm
0.000000 sqljsParseRdbAccessed
0.000000 sqljsParse
0.000000 sqleReceiveAndMergeReplies
Standard input

10

can anyone fix this?

11

With your script and input, I am getting this result...I have not done anything to get different o/p... Are you sure, you are running it properly.

6.915358 sqljs_ddm_rdbcmm
6.915349 sqlrr_commit
6.914515 sqlrrcom
6.914214 sqlrrcom_dps
6.906096 sqlrkcmt
3.456196 sqlrr2pd
1.691393 sqljsParse
1.673932 sqljsParseRdbAccessed
0.987817 sqlkdInterrupt
0.709601 sqljs_ddm_excsqlimm
0.709240 sqlrr_execimmd
0.559488 sqlkf_sync_request
0.423720 sqljs_ddm_prpsqlstt
0.423595 sqlrr_prepare
0.418095 sqlrr_execute_immed
0.255872 sqlra_get_var
0.255403 sqlra_find_var
0.121176 sqlriCloseExecuteSection
0.120953 sqlrices
0.120126 sqlrigca
0.090759 sqlra_cache_reserve_mem
0.090730 sqlra_cache_space_mgmt
0.090554 sqleReceiveAndMergeReplies
0.078845 sqlragsn
0.073462 sqlra_csm_flush_vars
0.063611 sqlrr_sql_request_pre
0.062158 sqlkdReceiveReply
0.062098 sqlkd_rcv_init
0.062094 sqlkd_rcv_get_next_buffer
0.062088 sqlkd_rcv_buffer
0.062084 sqkd_bds_buffer_table::getNextBuffer
0.062081 sqlkf_get_next_buffer
0.062023 sqloWaitEDUWaitPost
0.053108 sqlo_xlatch::getConflict
12

please attach your input file.. according to your present input that awk should work fine.. its giving correct o/p for me..

13

I am still having a mystery here....
I have tested it on my side on LINUX and AIX but I get same results as I pasted in my earlier reply....this is really a strange issue....

My input file is...

$ cat test.txt
192     (324 sec, 747387967 nanosec)     sqljsParse
194     (324 sec, 742760541 nanosec)     sqljsParseRdbAccessed
507     (283 sec, 660176478 nanosec)     sqlkf_sync_request
273     (269 sec, 673915252 nanosec)     sqlkdInterrupt
39      (269 sec, 337740235 nanosec)     sqlrkcmt
39      (269 sec, 654343714 nanosec)     sqlrrcom_dps
39      (269 sec, 666073125 nanosec)     sqlrrcom
39      (269 sec, 698604620 nanosec)     sqlrr_commit
39      (269 sec, 698958957 nanosec)     sqljs_ddm_rdbcmm
78      (269 sec, 583300457 nanosec)     sqlrr2pd
417     (37 sec, 834469926 nanosec)      sqlra_cache_space_mgmt
417     (37 sec, 846534878 nanosec)      sqlra_cache_reserve_mem
597     (31 sec, 705247032 nanosec)      sqlo_xlatch::getConflict
417     (30 sec, 633582480 nanosec)      sqlra_csm_flush_vars
39      (27 sec, 660368820 nanosec)      sqlrr_execimmd
39      (27 sec, 674433628 nanosec)      sqljs_ddm_excsqlimm
75      (19 sec, 155225907 nanosec)      sqlra_find_var
75      (19 sec, 190364402 nanosec)      sqlra_get_var
39      (16 sec, 305720732 nanosec)      sqlrr_execute_immed
117     (14 sec, 151555035 nanosec)      sqlrices
117     (14 sec, 177620542 nanosec)      sqlriCloseExecuteSection
117     (14 sec, 54696517 nanosec)       sqlrigca
156     (14 sec, 126475719 nanosec)      sqleReceiveAndMergeReplies
234     (14 sec, 513398100 nanosec)      sqloWaitEDUWaitPost
234     (14 sec, 526877995 nanosec)      sqlkf_get_next_buffer
234     (14 sec, 527734589 nanosec)      sqkd_bds_buffer_table::getNextBuffer
234     (14 sec, 528675358 nanosec)      sqlkd_rcv_buffer
234     (14 sec, 529943418 nanosec)      sqlkd_rcv_get_next_buffer
234     (14 sec, 530817210 nanosec)      sqlkd_rcv_init
234     (14 sec, 545068593 nanosec)      sqlkdReceiveReply
35      (14 sec, 825808972 nanosec)      sqlrr_prepare
35      (14 sec, 830216291 nanosec)      sqljs_ddm_prpsqlstt
154     (12 sec, 142066439 nanosec)      sqlragsn
194     (12 sec, 340492976 nanosec)      sqlrr_sql_request_pre
14 
awk '
sub(/\(/,"",$2) && $2 != 0 && $1 != 0{
  print $2/$1 , $NF | "sort -rn" 
}' file