Oracle Optimized Auxiliary SQL statements

--Query The SQL statement being executed
SELECT osuser Computer Login status,
program that initiated the request,
USERNAME user name of the login system,
SCHEMANAME,
B.cpu_time spends Cpu time,
STATUS,
B.sql_text Execution of SQL,
B.
From V$session A
Left JOIN v$sql B on a.sql_address = b.address
and A.sql_hash_value = B.hash_value
ORDER by B.cpu_time DESC;

--Query CPU-consuming SQL statements
SELECT *
From (select v.sql_id,
V.child_number,
V.sql_text,
V.elapsed_time,
V.cpu_time,
V.disk_reads,
Rank () Over (order by v.cpu_time Desc) Elapsed_rank
From V$sql v) a
where Elapsed_rank <= 10;

--Query the SQL statement that consumes the disk

SELECT * FROM (select v.sql_id,
V.child_number, V.sql_text,
V.elapsed_time, V.cpu_time,
V.disk_reads,
Rank () Over (order by v.disk_reads Desc) Elapsed_rank
From V$sql v) a where Elapsed_rank <= 10;

--Query for slow SQL statements

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

--oracle queries for uncommitted transactions

Select A.sid,a.blocking_session,a.last_call_et,a.event,
object_name,
Dbms_rowid.rowid_create (1,data_object_id,rfile#,row_wait_block#,row_wait_row#) "rowID",
C.sql_text,c.sql_fulltext
From V$session A,v$sqlarea C, dba_objects,v$datafile
Where a.blocking_session is not null
and A.sql_hash_value = C.hash_value
and row_wait_obj#=object_id and file#=row_wait_file#;

Oracle Optimized Auxiliary SQL statements