需要管理员用户下执行(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 ;



https://www.cnblogs.com/hziwei/p/15588249.html