Querying Oracle Performance-poor, executing, or executing SQL statements

Find the top 10 poor performance SQL.

SELECT *  from (SelectfromorderbyDESC ) where ROWNUM<

View a running session with a large IO

SELECTSE.SID,SE.SERIAL#,PR. Spid,se.username,se.status, Se.terminal,se.program,se. Module,se.sql_address,st.event,st. P1text,si.physical_reads, Si.block_changes fromv$session Se,v$session_wait St, v$sess_io si,v$process PRWHERESt.sid=Se.sid andSt. Sid=Si.sid andSe. Paddr=pr. ADDR andSe.sid>6  andSt. Wait_time=0  andSt.event not  like '%sql%' ORDER  byPhysical_readsDESC

---is executing the

Select A.username, A.sid,b.sql_text, B.sql_fulltext    from  where=

---executed.

Select B.sql_text,b.first_load_time,b.sql_fulltext    from V$sqlarea b where between ' 2009-10-15/09:24:47 '  and       ' 2009-10-15/09:24:47 ' Order  by

(This method benefits you to see the SQL that was executed for a time period, and Sql_fulltext contains the full SQL statement)

Other

SelectOsuser,program,username,schemaname,b.cpu_time,status,b.sql_text fromV$session A Left JOINV$sql B onA.sql_address=B.address andA.sql_hash_value=B.hash_valueOrder  byB.cpu_timedesc Selectaddress, sql_text, piece fromv$session, V$sqltextwhereAddress=sql_address--and machine = < your machine name >Order  byAddress, piece

Querying Oracle Performance-poor, executing, or executing SQL statements