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 188.8.131.52, 184.108.40.206.1 and 220.127.116.11.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