oracle数据库top用法,Oracle TOP SQL&&HIT

Oracle TOP SQL&&HIT

TOP SQL

---

逻辑读

(CPU,MEM)

--1

select *

from (select

---substr(sql_text, 1, 40) sql,

sql_text,

buffer_gets,

executions,

buffer_gets / executions "Gets/Exec",

hash_value,

address

from v$sqlarea

where buffer_gets > 0

and executions > 0

order by buffer_gets desc)

where rownum <= 10;

--2

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 <= 10;

--3

SELECT EXECUTIONS,

DISK_READS,

BUFFER_GETS,

ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) Hit_radio,

ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS > 0

AND BUFFER_GETS > 0

AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8

ORDER BY 4 DESC;

---

物理读

(I/O)

SELECT SQL_TEXT

FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS desc)

WHERE ROWNUM <= 10;

--1

select *

from (select

---substr(sql_text, 1, 40) sql,

sql_text,

disk_reads,

executions,

disk_reads / executions "Reads/Exec",

hash_value,

address

from v$sqlarea

where disk_reads > 0

and executions > 0

order by disk_reads desc)

where rownum <= 10;

--2

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;

---

表空间的

I/O

比例

/*

PHYRDS

：已完成的物理读次数；

PHYBLKRD

：块读取数；

PHYWRTS

：

DBWR

完成的物理写次数；

PHYBLKWRT

：写入磁盘的块数；

*/

SELECT DF.TABLESPACE_NAME NAME,

DF.FILE_NAME       "FILE",

F.PHYRDS           PYR,

F.PHYBLKRD         PBR,

F.PHYWRTS          PYW,

F.PHYBLKWRT        PBW

FROM V$FILESTAT F, DBA_DATA_FILES DF

WHERE F.FILE# = DF.FILE_ID

ORDER BY DF.TABLESPACE_NAME;

---

文件系统

I/O

比例

SELECT SUBSTR(A.FILE#, 1, 2) "#",

SUBSTR(A.NAME, 1, 30) "NAME",

A.STATUS,

A.BYTES,

B.PHYRDS,

B.PHYWRTS

FROM V$DATAFILE A, V$FILESTAT B

WHERE A.FILE# = B.FILE#;

---

磁盘碎片高的段

SELECT segment_name, COUNT(*) extents

FROM dba_segments

WHERE owner NOT IN ('SYS', 'SYSTEM')

GROUP BY segment_name

HAVING COUNT(*) = (SELECT MAX(COUNT(*))

FROM dba_segments

GROUP BY segment_name);

---

执行次数

--1

select *

from (select substr(sql_text, 1, 40) sql,

sql_text,

executions,

rows_processed,

rows_processed / executions "Rows/Exec",

hash_value,

address

from v$sqlarea

where executions > 0

order by executions desc)

where rownum <= 10;

--2

select sql_text, executions

from (select sql_text,

executions,

rank() over(order by executions desc) exec_rank

from v$sql)

where exec_rank <= 10;

---

执行时间

select *

from (select t.sql_fulltext,

(t.last_active_time -

to_date(t.first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,

disk_reads,

buffer_gets,

rows_processed,

t.last_active_time,

t.last_load_time,

t.first_load_time

from v$sqlarea t

order by t.first_load_time desc)

where rownum < 10;

---

运行时间长的

SQL

---V$SESSION_LONGOPS

视图显示运行超过

6

秒的操作的状态。

SELECT USERNAME,

SID,

OPNAME,

ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,

TIME_REMAINING,

SQL_TEXT

FROM V$SESSION_LONGOPS, V$SQL

WHERE TIME_REMAINING <> 0

AND SQL_ADDRESS = ADDRESS

AND SQL_HASH_VALUE = HASH_VALUE;

---

查询单条语句占用内存

select sum(bytes) from dba_segments;

select sql_text,

operation_type,

policy,

(last_memory_used / 1024 / 1024),

last_execution,

last_tempseg_size

from v$sql i, v$sql_workarea a

where i.hash_value = a.hash_value

and sql_text like 'select sum(bytes) from dba_segments%';

---Parse Calls

select *

from (select substr(sql_text, 1, 40) sql,

parse_calls,

executions,

hash_value,

address

from v$sqlarea

where parse_calls > 0

order by parse_calls desc)

where rownum <= 10;

---shared memory

select *

from (select substr(sql_text, 1, 40) sql,

sharable_mem,

executions,

hash_value,

address

from v$sqlarea

where sharable_mem > 1048576

order by sharable_mem desc)

where rownum <= 10;

---

等待事件

SELECT *

FROM (SELECT *

FROM V$SYSTEM_EVENT

WHERE EVENT NOT LIKE 'SQL%'

ORDER BY TOTAL_WAITS DESC)

WHERE ROWNUM <= 5;

HIT

---

检查缓冲区命中率

SELECT a.VALUE + b.VALUE logical_reads,

c.VALUE phys_reads,

round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio

FROM v$sysstat a, v$sysstat b, v$sysstat c

WHERE a.NAME = 'db block gets'

AND b.NAME = 'consistent gets'

AND c.NAME = 'physical reads';

---

检查共享池命中率

select sum(pinhits)/sum(pins)*100 from v$librarycache;

---

数据字典缓存命中率：

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

---

库缓存命中率：

select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;

---

检查日志缓冲区

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

---

检查排序区

select name,value from v$sysstat where name like '%sort%';

---PGA

内存排序命中率：

select a.value "Disk

Sorts",

b.value "Memory Sorts",

round((100 * b.value) /

decode((a.value + b.value), 0, 1, (a.value + b.value)),

2) "Pct Memory Sorts"

from v$sysstat a, v$sysstat b

where a.name = 'sorts (disk)'

and b.name = 'sorts (memory)';

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！