Oracle SQL statements Run efficiently

--Current execution SQL statement

SELECT A.sid,
a.serial#,
A.username,
B.parse_calls,
B.parsing_schema_name,
b.cpu_time/1000000,
b.elapsed_time/1000000,
B.disk_reads,
B.direct_writes,
B.buffer_gets,
A.event,
B.sql_text,
B.sql_fulltext
From V$session A
INNER JOIN V$sqlarea b on a.sql_hash_value = B.hash_value
and b.parsing_schema_name = UPPER (' smsdb ')

--Physical read highest SQL statement

SELECT A.username,
A.USER_ID,
B.parse_calls,
B.parsing_schema_name,
b.cpu_time/1000000,
b.elapsed_time/1000000,
B.disk_reads,
B.direct_writes,
B.buffer_gets,
B.sql_text,
B.sql_fulltext
From Dba_users A
INNER JOIN V$sqlarea b on a.user_id = b.parsing_user_id
and b.parsing_schema_name = UPPER (' smsdb ')
and Disk_reads > 1000000

--Query the top 10 execute the most number of SQL statements

SELECT sql_text ' SQL statement ',
Executions ' execution times '
From (SELECT Sql_text,
Executions,
RANK () over (ORDER by executions DESC) Exec_rank
From V$sqlarea
)
WHERE Exec_rank <= 10;

--Query the top 10 CPU-intensive SQL statements

Select Sql_text ' SQL statement ',

c_t ' SQL execution time (seconds) ', executions ' execution count ', CS ' execution time (seconds) ' from (select Sql_text,

cpu_time/1000000 C_t,executions,ceil (executions/(cpu_time/1000000)) CS,

Rank () Over (order by cpu_time Desc) top_time

From V$sqlarea) where Top_time <= 10

--Query the top 10 longest executing SQL statements

SELECT sql_text ' SQL statement ',
c_t ' processing time (seconds) ',
Executions ' execution times ',
CS ' time per execution (seconds) '
From (SELECT Sql_text,
elapsed_time/1000000 c_t,
Executions,
Ceil (Executions/(elapsed_time/1000000)) CS,
RANK () over (ORDER by Elapsed_time DESC) top_time
From V$sqlarea
)
WHERE Top_time <= 10

--Query the top 10 most resource-consuming SQL statements

SELECT sql_text ' SQL statement ',
Disk_reads ' physical read Times ',
CS ' time per execution (seconds) '
From (SELECT Sql_text,
elapsed_time/1000000 c_t,
Executions,
Ceil (Executions/(elapsed_time/1000000)) CS,
Disk_reads,
RANK () over (ORDER by Disk_reads DESC) Top_disk
From V$sqlarea
)
WHERE Top_disk <= 10

--Query the top 10 memory-consuming SQL statements

Select Sql_text ' SQL statement ',

Buffer_gets ' Memory read count ', CS ' execution time (seconds) '

From (select Sql_text,

elapsed_time/1000000 C_t,executions,ceil (executions/(elapsed_time/1000000)) Cs,buffer_gets,

Rank () Over (order by buffer_gets Desc) top_mem

From V$sqlarea) where Top_mem <= 10

--View the Lock table statement

Select

C.sid,

c.serial#,

D.name,

B.object_name,

C.username,

C.program,

C.osuser

From Gv$locked_object A, all_objects B, Gv$session C, audit_actions D

where a.object_id = b.object_id

and a.inst_id = c.inst_id (+)

and a.session_id = C.sid (+)

and C.command = d.action;

Oracle SQL statements Run efficiently