Overview

This document describes commands that can be used to identify nodes with long execution times after collecting statistics information from query execution plans in sql_trace, plan stat, xplan, and tpr.

By running each command according to the tool used, you can check the node numbers and execution times in order of longest execution time.

Method

t.txt plan

SQL_Trace

Command

tail -n +3 t.txt | cut -d"=" -f2 | cut -d"," -f1 | cat -n | awk '{print $1" "$2}' | sort -k 2 -nr | head -5

Execution Result

2 4443678 
6 681714 
1 310880 
3 182157 
7 143849

plan_stat

Command

tail -n +3 t.txt | cut -d"," -f1 | sed 's/Time:/#/g' | cut -d"#" -f2 | cat -n | sort -k 2 -nr | head -5

Execution Result

2 4121.77 ms 
6 622.27 ms 
1 236.71 ms
3 175.12 ms
7 95.79 ms

xplan

Command

tail -n +3 t.txt | cut -d"|" -f2,7 | sort -k 2 -t "|" -r | head -5

Execution Result

2 |00:00:04.4247 
6 |00:00:00.6391 
1 |00:00:00.3682 
3 |00:00:00.1727 
7 |00:00:00.1636

TPR Execution Plan

Command

tail -n +3 t.txt | awk '{print $1" "$3}' | sort -k 2 -nr | head -5

Execution Result

2 4.15 
6 0.84 
1 0.30 
3 0.19 
7 0.16