Find long-running queries

Get read, write times and file directories from your Oracle database to determine long-running queries.

This script will get read/write times and file directories from your Oracle database. It proved useful to my developers to determine long-running query problems. Developers could now see if the DB was a major factor in query performance and also see the sqltext being run from a function call. It has been tested on Oracle 9.1.0.1, 9.2.0.2.1 and 9.2.0.4.0.

SELECT d.name file_name, 
d.blocks, 
f.phywrts, 
f.phyrds, 
f.readtim, 
f.writetim, 
t.name tablespace_name, 
f.phywrts/d.blocks wrts_per_block, 
f.phyrds/d.blocks rds_per_block 
FROM v$filestat f, 
v$datafile d, 
sys.ts$ t 
WHERE f.file#=d.file# 
AND d.blocks >0 
AND t.ts#=d.ts# 
UNION ALL 
SELECT d.name file_name, 
d.blocks, 
f.phywrts, 
f.phyrds, 
f.readtim, 
f.writetim, 
t.name tablespace_name, 
f.phywrts/d.blocks wrts_per_block, 
f.phyrds/d.blocks rds_per_block 
FROM v$tempstat f, 
v$tempfile d, 
sys.ts$ t 
WHERE f.file#=d.file# 
AND d.blocks >0 
AND t.ts#=d.ts# 

CREATE OR REPLACE 
FUNCTION getusername(sidval number) return varchar2 is 
username varchar2(256); 
begin 
select username into username from v$session where sid = sidval; 
return username; 
end; 
/ 

SELECT DISTINCT /*+ORDERED*/ 
sql.sql_text, 
sql.address, 
sql.hash_value, 
0 piece, 
sid, 
serial#, 
getusername(sid)username 
FROM v$session s, 
v$sql sql 
WHERE sql.address=s.sql_address 
AND sql.hash_value=s.sql_hash_value 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in February 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close