Home » RDBMS Server » Performance Tuning » How to find out the bad sql statemets ?
How to find out the bad sql statemets ? [message #233908] Sat, 28 April 2007 01:53 Go to next message
kesevan
Messages: 17
Registered: April 2007
Location: MUMABI
Junior Member
Dear all,

Please guide me all possible ways to find out the BAD PERMANCE SQL STATEMENTS IN MY DATABASE ?

thank you
kesavan.

Report message to a moderator

Re: How to find out the bad sql statemets ? [message #233913 is a reply to message #233908] Sat, 28 April 2007 02:26 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I would look for Stats Pack report or even 10046 event for process.

At lower level, may be a explain plan.

Brayan.

Report message to a moderator

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 messageGo to next message
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;
/

Report message to a moderator

Re: How to find out the bad sql statemets ? [message #233958 is a reply to message #233917] Sat, 28 April 2007 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Read How to format your posts

2/ Where did you find this script?

3/ What is its rationale? How does it work? What does it show?

Regards
Michel

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

Re: How to find out the bad sql statemets ? [message #255696 is a reply to message #255690] Wed, 01 August 2007 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ashok,

Read and follow the first link I posted.

Regards
Michel

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

Re: How to find out the bad sql statemets ? [message #255734 is a reply to message #255732] Wed, 01 August 2007 10:55 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not really formatted.
Writing each word on first column is not what I call formatted.

Regards
Michel

Report message to a moderator

Previous Topic: View doesn't use Index
Next Topic: performance problem when use parallel and hash join on large tables
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 06:43:32 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.