Oracle查看锁表情况（oracle查看锁表的sql）

大家好，我是讯享网，很高兴认识大家。

 <p style="margin-left:0;">在本节主要检查Oracle数据库性能情况&#xff0c;包含&#xff1a;检查数据库的等待事件&#xff0c;检查死锁及处理&#xff0c;检查cpu、I/O、内存性能&#xff0c;查看是否有僵死进程&#xff0c;检查行链接/迁移&#xff0c;定期做统计分析&#xff0c;检查缓冲区命中率&#xff0c;检查共享池命中率&#xff0c;检查排序区&#xff0c;检查日志缓冲区&#xff0c;总共十个部分。</p>

讯享网

4.1. 检查数据库的等待事件

set pages 80

set lines 120

col event for a40

select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT

from v\(session_wait where event not like 'SQL%' and event not like 'rdbms%'; 如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。 4.2. Disk Read最高的SQL语句的获取 SELECT SQL_TEXT FROM (SELECT * FROM V\)SQLAREA ORDER BY DISK_READS)

WHERE ROWNUM <= 5;

4.3. 查找前十条性能差的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; 4.4. 等待时间最多的5个系统等待事件的获取 SELECT * FROM (SELECT * FROM V\)SYSTEM_EVENT

WHERE EVENT NOT LIKE ‘SQL%’

ORDER BY TOTAL_WAITS DESC)

WHERE ROWNUM <= 5;

4.5. 检查运行很久的SQL

COLUMN USERNAME FORMAT A12

COLUMN OPNAME FORMAT A16

COLUMN PROGRESS FORMAT A8

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

4.6. 检查消耗CPU最高的进程

SET LINE 240

SET VERIFY OFF

COLUMN SID FORMAT 999

COLUMN PID FORMAT 999

COLUMN S# FORMAT 999

COLUMN USERNAME FORMAT A9 HEADING “ORA USER”

COLUMN PROGRAM FORMAT A29

COLUMN SQL FORMAT A60

讯享网

COLUMN OSNAME FORMAT A9 HEADING “OS USER”

SELECT P.PID PID,

S.SID SID,

P.SPID SPID,

S.USERNAME USERNAME,

S.OSUSER OSNAME,

P.SERIAL# S_#,

P.TERMINAL,

P.PROGRAM PROGRAM,

P.BACKGROUND,

S.STATUS,

RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V\(PROCESS P, V\)SESSION S,

V\(SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE '%&1%'; 4.7. 检查碎片程度高的表 SELECT segment_name table_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); 4.8. 检查表空间的I/O比例 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;

4.9. 检查文件系统的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#;

4.10.检查死锁及处理

查询目前锁对象信息：

select sid,

serial#,

username,

SCHEMANAME,

osuser,

MACHINE,

terminal,

PROGRAM,

owner,

object_name,

object_type,

o.object_id

from dba_objects o, v\(locked_object l, v\)session s

where o.object_id = l.object_id

and s.sid = l.session_id;

oracle级kill掉该session：

alter system kill session ‘&sid,&serial#’;

操作系统级kill掉session：

#>kill -9 pid

Oracle查看锁表情况（oracle查看锁表的sql）

相关推荐