) Obtain the SQL statement that consumes the most CPU resources in real time.

In Oracle Performance Diagnosis and daily monitoring, CPU-consuming statements are usually the most important. Therefore, in the Oracle10g AWR, the statements with the highest CPU time and elapsed time are added to the report and placed in the first two of the SQL statements. In normal monitoring, you can also use shell scripts to capture the SQL statements that are being executed in the processes with the most CPU consumption in real time to diagnose and detect problems more effectively and timely.

First, write a script get_by_spid. SQL Based on spid or its SQL.

#! /Bin/KSh

# Creator: ningoo

# Function: Get SQL statement by spid

# Parameter: spid

# Useage: get_by_spid.sh spid

Sqlplus-S/nolog <connect/As sysdba;

Col serial # format 999999

Cola Sid format 99999

Col username format A10

Col machine format A12

Col program format A32

Col SQL _text format a81

Set lines 1000

Sets pages 1000

Set verify off

Col SQL _hash_value new_value hash_value head hash_value

Select Sid, serial #, username, program, SQL _hash_value,

To_char (logon_time, 'yyyy/MM/DD hh24: MI: ss') as login_time

From V \ $ session

Where paddr in (select ADDR from V \ $ process where spid = $1 );

Select SQL _text

From V \ $ sqltext_with_newlines

Where hash_value = & hash_value

Order by piece;

Exit;

EOF

Then, obtain the spid of the Oracle server process with the most CPU consumption in the system in another shell script topsql. Sh, and call the first script cyclically to obtain the SQL

#! /Bin/KSh

# Creator: ningoo

# Function: get top CPU SQL

# Parameter: N

# Useage: topsql. Sh n

If [$ #-EQ 0]; then

Echo "Usage: 'basename $0 'n"

Exit 1

Fi

Topcpu = 'ps auxw | grep local | sort-rn + 2 | head-$1 | awk '{print $2 }''

I = 0

For spid in $ topcpu

Do

I = 'expr $ I + 1'

Echo "\ 033 [32; 1 m =================== top $ I cpu SQL =======================\ 033 [0 m"

./Home/Oracle/worksh/get_by_spid.sh $ spid

Done

The call is very simple. If we want to look at the top 3 SQL statements in the system, we only need to execute topsql. SH 3. Of course, if we have obtained spid through top/topas and other tools, we only need to execute get_by_spid.sh spid to obtain the SQL statement being executed by the process.