Oracle 查询 SQL 语句

1. Oracle 查询 SQL 语句

1.1. 性能查询常用 SQL

1.1.1. 查询最慢的 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

1.1.2. 列出使用频率最高的 5 个查询

select sql_text,executions
from (select sql_text,executions,rank() over(order by executions desc) exec_rankfrom v$sql)
where exec_rank <=5;

1.1.3. 消耗磁盘读取最多的 sql top5

select disk_reads,sql_text
from (select sql_text,disk_reads,dense_rank() over(order by disk_reads desc) disk_reads_rankfrom v$sql)
where disk_reads_rank <=5;

1.1.4. 找出需要大量缓冲读取(逻辑读)操作的查询

select buffer_gets,sql_text
from (select sql_text,buffer_gets,dense_rank() over(order by buffer_gets desc) buffer_gets_rankfrom v$sql)
where buffer_gets_rank<=5;

1.1.5. 查询每天执行慢的 SQL

SELECT S.SQL_TEXT,S.SQL_FULLTEXT,S.SQL_ID,ROUND(ELAPSED_TIME / 1000000 / (CASEWHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN1ELSEEXECUTIONSEND),2) "执行时间'S'",S.EXECUTIONS "执行次数",S.OPTIMIZER_COST "COST",S.SORTS,S.MODULE, --连接模式(JDBC THIN CLIENT: 程序)-- S.LOCKED_TOTAL,S.PHYSICAL_READ_BYTES "物理读",-- S.PHYSICAL_READ_REQUESTS "物理读请求",S.PHYSICAL_WRITE_REQUESTS "物理写",-- S.PHYSICAL_WRITE_BYTES "物理写请求",S.ROWS_PROCESSED      "返回行数",S.DISK_READS          "磁盘读",S.DIRECT_WRITES       "直接路径写",S.PARSING_SCHEMA_NAME,S.LAST_ACTIVE_TIMEFROM GV$SQLAREA SWHERE ROUND(ELAPSED_TIME / 1000000 / (CASEWHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN1ELSEEXECUTIONSEND),2) > 5 --100 0000 微秒=1SAND S.PARSING_SCHEMA_NAME = USERAND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =TO_CHAR( SYSDATE, 'YYYY-MM-DD' )AND S.COMMAND_TYPE IN (2 , 3, 5, 6 , 189)ORDER BY "执行时间'S'" DESC;

/*
SQL 中 COMMAND_TYPE 意义:
2: INSERT
3: SELECT
6: UPDATE
7: DELETE
189: MERGE

详情可通过查找 V$SQLCOMMAND 视图
*/

• V$SQLAREA 官网解释: http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3064.htm#REFRN30259
• V$SQLCOMMAND 官网解释: http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3066.htm#REFRN30632

1.1.6. 从 V$SQLAREA 中查询最占用资源的查询

select b.username username,a.disk_reads reads,a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_idand a.disk_reads > 100000
order by a.disk_reads desc;

用 buffer_gets 列来替换 disk_reads 列可以得到占用最多内存的 sql 语句的相关信息。

• v$sql: 内存共享 SQL 区域中已经解析的 SQL 语句。(即时)

1.1.7. 查询对应 session

select SE.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 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;
SELECT sql_address FROM V$SESSION SS,V$SQLTEXT TT
WHERE SS.SQL_HASH_VALUE=TT.HASH_VALUE AND SID=439;

• v$sqltext: 存储的是完整的 SQL,SQL 被分割
• v$sqlarea: 存储的 SQL 和一些相关的信息, 比如累计的执行次数, 逻辑读, 物理读等统计信息(统计)
• v$sql: 内存共享 SQL 区域中已经解析的 SQL 语句。(即时)

1.1.8. 根据 sid 查找完整 SQL 语句

select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid')
order by piece asc

1.1.9. 未知 1

select a.CPU_TIME,--CPU 时间 百万分之一(微秒)a.OPTIMIZER_MODE,--优化方式a.EXECUTIONS,--执行次数a.DISK_READS,--读盘次数a.SHARABLE_MEM,--占用 shared pool 的内存多少a.BUFFER_GETS,--读取缓冲区的次数a.COMMAND_TYPE,--命令类型 (3:select,2:insert;6:update;7delete;47:pl/sql 程序单元)a.SQL_TEXT,--Sql 语句a.SHARABLE_MEM,a.PERSISTENT_MEM,a.RUNTIME_MEM,a.PARSE_CALLS,a.DISK_READS,a.DIRECT_WRITES,a.CONCURRENCY_WAIT_TIME,a.USER_IO_WAIT_TIMEfrom SYS.V_$SQLAREA aWHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间order by a.CPU_TIME desc