需要管理员用户下执行(sys/sysdba)



--先查锁

select * from v$lock where lmode > 0 and type in ('TM','TX');

--查用户名

select * from v$session where username = 'PROD_JCJ'

-- 查编号方便下一步 kill

select sid,serial#,username from v$session where sid = '13313';

--kill 锁死的会话

alter system kill session '15489,47476';

alter system kill session '16215,2972';

alter system kill session '2545,51671';

alter system kill session '609,63963';

alter system kill session '6415,45951';

alter system kill session '2306,64117';

--查看被锁的表

select * from v$locked_object a, dba_objects b where b.object_id = a.object_id  

-- 执行以下语句子，获得被锁表的Session ID

select b.owner,b.object_name,a.session_id,a.locked_mode

from dba_objects b, v$locked_object a

where b.object_id = a.object_id

and b.object_name='PER_ALL_ASSIGNMENTS_F';

-- 1.查询oracle的最大连接数：

select * from v$parameter where name='processes';

select count(*) from v$process; --当前的连接数

select value from v$parameter where name = 'processes' --数据库允许的最大连接数

-- 日志文件位置

select * from V$diag_Info;

--查看表锁的原因

select l.session_id sid,

       s.serial#,

       l.locked_mode,

       l.oracle_username,

       s.user#,

       l.os_user_name,

       s.machine,

       s.terminal,

       a.sql_text,

       a.action from v$sqlarea a, v$session s, v$locked_object l

       where l.session_id = s.sid and s.prev_sql_addr = a.address

       order by sid,s.serial#;

--查询低效的SQL

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

-- 查看表空间大小及使用情况

SELECT a.tablespace_name "表空间名",

total "表空间大小",

free "表空间剩余大小",

(total - free) "表空间使用大小",

total / (1024 * 1024 * 1024) "表空间大小(G)",

free / (1024 * 1024 * 1024) "表空间剩余大小(G)",

(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

--查看各用户的各种资源占用，可以运行下面的SQL

select se.SID, ses.username, ses.osuser, n.NAME, se.VALUE

  from v$statname n, v$sesstat se, v$session ses

  where n.statistic# = se.statistic# and

        se.sid = ses.sid and

        ses.username is not null and

        n.name in ('CPU used by this session',

                   'db block gets',

                   'consistent gets',

                   'physical reads',

                   'free buffer requested',

                   'table scans (long tables)',

                   'table scan rows gotten',

                   'sorts (memory)',

                   'sorts (disk)',

                   'sorts (rows)',

                   'session uga memory max' ,

                   'session pga memory max')

  order by sid, n.statistic#;

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

select b.username username,a.buffer_gets reads,

    a.executions exec,a.buffer_gets/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.buffer_gets > 100000

order by a.buffer_gets desc;

--列出使用频率最高的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;

--消耗磁盘读取最多的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;

--找出需要大量缓冲读取（逻辑读）操作的查询：

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;

--查询日志文件位置

select * from V$diag_Info;

-- 查询v$session_wait获取各进程等待事件

select sid,event,p1,p1text from v$session_wait;

select event,count(*)  from v$session_event group by event order by count(*) desc ;
巴特西

Oracle 会话锁死

需要管理员用户下执行(sys/sysdba)

最新文章

热门文章
