目录
1. Oracle 查询 SQL 语句
1.1. 性能查询常用 SQL
1.1.1. 查询最慢的 SQL
1.1.2. 列出使用频率最高的 5 个查询
1.1.3. 消耗磁盘读取最多的 sql top5
1.1.4. 找出需要大量缓冲读取(逻辑读)操作的查询
1.1.5. 查询每天执行慢的 SQL
1.1.6. 从 V$SQLAREA 中查询最占用资源的查询
1.1.7. 查询对应 session
1.1.8. 根据 sid 查找完整 SQL 语句
1.1.9. 未知 1
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_rank
from 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_rank
from 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_rank
from 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 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
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_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000 微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND 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_id
and 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_TIME
from SYS.V_$SQLAREA a
WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间
order by a.CPU_TIME desc