--当前执行sql语句

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')

--物理读最高sql语句

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

--查询前10名执行最多次数SQL语句

select sql_text "SQL语句", executions "执行次数"

from (select sql_text,

executions,

rank() over

(order by executions desc) exec_rank

from v$sqlarea)

where exec_rank