Home » RDBMS Server » Performance Tuning » How to find out the bad sql statemets ?
|
|
Re: How to find out the bad sql statemets ? [message #233917 is a reply to message #233913] |
Sat, 28 April 2007 03:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cliff_tian
Messages: 3 Registered: August 2005
|
Junior Member |
|
|
Hi,
Script below can help you identify SLOW sqls clearly.
set linesize 174
set pagesize 100
col username for a10
col cpu_time_secs for 999,999,999,999
col elapsed_time_secs for 999,999,999,999,999
col Elap_Exec for 999,999,999,999
col LAST_LOAD_TIME for a20
col executions for 999,999,999
col sql_text for a65
select c.username,CPU_TIME*0.000001 cpu_time_secs,
ELAPSED_TIME*0.000001 elapsed_time_secs,round(ELAPSED_TIME*0.000001/executions) Elap_per_Exec,
executions,LAST_LOAD_TIME,b.piece,b.sql_text
from v$sql a, v$sqltext b, dba_users c
where
a.address=b.address
and
(
ELAPSED_TIME*0.000001>5
or executions > 1000
)
and executions>0
and c.user_id=a.PARSING_USER_ID
order by Elap_per_Exec,ELAPSED_TIME,CPU_TIME,a.HASH_VALUE, b.piece asc;
/
|
|
|
|
Re: How to find out the bad sql statemets ? [message #255690 is a reply to message #233958] |
Wed, 01 August 2007 08:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ashok_mni
Messages: 7 Registered: June 2005
|
Junior Member |
|
|
You see formatted script below
set linesize 174
set pagesize 100
col username for a10
col cpu_time_secs for 999,999,999,999
col elapsed_time_secs for 999,999,999,999,999
col Elap_Exec for 999,999,999,999
col LAST_LOAD_TIME for a20
col executions for 999,999,999
col sql_text for a65
SELECT c.UserName,
cpu_Time * 0.000001 cpu_Time_secs,
Elapsed_Time * 0.000001 Elapsed_Time_secs,
Round(Elapsed_Time * 0.000001 / Executions) eLap_Per_exec,
Executions,
Last_Load_Time,
b.Piece,
b.sql_Text
FROM v$sql a,
v$sqlText b,
dba_Users c
WHERE a.Address = b.Address
AND (Elapsed_Time * 0.000001 > 5
OR Executions > 1000)
AND Executions > 0
AND c.User_Id = a.ParSing_User_Id
ORDER BY eLap_Per_exec,
Elapsed_Time,
cpu_Time,
a.Hash_Value,
b.Piece ASC;
/
Regards
Ashok M
|
|
|
|
Re: How to find out the bad sql statemets ? [message #255732 is a reply to message #255696] |
Wed, 01 August 2007 10:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
DreamzZ
Messages: 1666 Registered: May 2007 Location: Dreamzland
|
Senior Member |
|
|
You see formatted script below
set linesize 174
set pagesize 100
col username for a10
col cpu_time_secs for 999,999,999,999
col elapsed_time_secs for 999,999,999,999,999
col Elap_Exec for 999,999,999,999
col LAST_LOAD_TIME for a20
col executions for 999,999,999
col sql_text for a65
SELECT c.UserName,
cpu_Time * 0.000001 cpu_Time_secs,
Elapsed_Time * 0.000001 Elapsed_Time_secs,
Round(Elapsed_Time * 0.000001 / Executions) eLap_Per_exec,
Executions,
Last_Load_Time,
b.Piece,
b.sql_Text
FROM v$sql a,
v$sqlText b,
dba_Users c
WHERE a.Address = b.Address
AND (Elapsed_Time * 0.000001 > 5
OR Executions > 1000)
AND Executions > 0
AND c.User_Id = a.ParSing_User_Id
ORDER BY eLap_Per_exec,
Elapsed_Time,
cpu_Time,
a.Hash_Value,
b.Piece ASC;
/
Yes
|
|
|
|
Goto Forum:
Current Time: Mon Aug 26 14:03:23 CDT 2024
|