Oracle queries the most recently executed SQL statement (2014-06-09 18:02:43)
reproduced
Oracle queries recently executed SQL statements select Sql_text,last_load_time from V$sql order by last_load_time Desc;SELECT Sql_text, last_load_time from V$sql WHERE last_load_time are not NULL and sql_text as ' select% ' ORDER by Last_load_time DESC; SELECT Sql_text, last_load_time from V$sql WHERE last_load_time are not NULL and sql_text as ' update% ' ORDER by Last_load_time DESC; SELECT Sql_text, last_load_time from V$sql WHERE last_load_time are not NULL and last_load_time like ' 14-06-09% ' ORDER by Last_load_time DESC; ---is executing the
Select A.username, A.sid,b.sql_text, B.sql_fulltext
From V$session A, V$sqlarea b
where a.sql_address = b.address
---executed.
Select B.sql_text,b.first_load_time,b.sql_fulltext
From V$sqlarea b
where b.first_load_time between ' 2009-10-15/09:24:47 ' and
' 2009-10-15/09:24:47 ' ORDER by B.first_load_time
(This method benefits you to see the SQL that was executed for a time period, and Sql_fulltext contains the full SQL statement)
Other
Select Osuser,program,username,schemaname,b.cpu_time,status,b.sql_text
From V$session A left JOIN v$sql B in a.sql_address=b.address and a.sql_hash_value=b.hash_value order BY b.cpu_time Desc
Select Address, Sql_text, piece
From V$session, V$sqltext
where address = Sql_address
--and Machine = < your machine name >
Order by address, piece
find the top 10 poor performance SQL.
SELECT * FROM (select Parsing_user_id,executions,sorts,
Command_type,disk_reads,sql_text from V$sqlarea
ORDER by Disk_reads DESC) where rownum<10;
view a running session with a large IO
SELECT SE.SID,SE.SERIAL#,PR. Spid,se.username,se.status,
Se.terminal,se.program,se. MODULE, and Se.sql_address,st.event,st.
P1text,si.physical_reads,
Si.block_changes from V$session Se,v$session_wait St,
V$sess_io si,v$process PR WHERE St.sid=se.sid and St.
Sid=si.sid and SE. Paddr=pr. ADDR and Se.sid>6 and St.
Wait_time=0 and st.event not like '%sql% ' ORDER by physical_reads DESC
Oracle queries recently executed SQL statements (reprinted)
