Oracle 性能维护方法

2019独角兽企业重金招聘Python工程师标准>>>

-- oracle 查询最近执行过的 SQL语句
SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA; 

-- 查找出被锁的表  
select b.owner,b.object_name,a.session_id,a.locked_mode  
from v$locked_object a,dba_objects b  
where b.object_id = a.object_id;  

select b.username,b.sid,b.serial#,logon_time  
from v$locked_object a,v$session b  
where a.session_id = b.sid order by b.logon_time; 

select sess.sid,
   sess.serial#,
   lo.oracle_username,
   lo.os_user_name,
   ao.object_name,
   lo.locked_mode
   from v$locked_object lo,
   dba_objects ao,
   v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

-- 杀进程中的会话  
alter system kill session 'sid,serial#';  

-- oracle 查询最近执行过的 SQL语句
SELECT sql_fulltext, last_load_time FROM v$sql 
WHERE last_load_time IS NOT NULL ORDER BY last_load_time DESC;

-- 正在执行的
select a.username, a.sid, b.SQL_FULLTEXT
from v$session a, v$sqlarea b 
where a.sql_address = b.address ;

-- 执行过的
select b.SQL_TEXT, b.FIRST_LOAD_TIME, b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2016-10-15/09:24:47' and
       '2016-12-16/09:24:47' order by b.FIRST_LOAD_TIME ;
       
select OSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_FULLTEXT
from V$SESSION A LEFT JOIN V$SQL B 
ON A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE 
order by b.cpu_time desc ;
       
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 
COMMAND_TYPE,DISK_READS,sql_fulltext FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM < 10 ;

-- 查看占io较大的正在运行的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 ;

-- 捕捉运行很久的SQL 
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 partition_name, high_value, high_value_length, tablespace_name, 
         pct_free, pct_used, ini_trans, max_trans, initial_extent, 
         next_extent, min_extent, max_extent, pct_increase, FREELISTS, 
         freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, 
         empty_blocks, chain_cnt, sample_size, 
         last_analyzed 
    FROM dba_tab_partitions 
   --WHERE table_name = :tname AND table_owner = :towner 
 ORDER BY partition_position ;
 
-- 查看有哪些用户连接 
select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command), 
 'Action Code #' || to_char(command) ) action, p.program oracle_process,    
 status session_status, s.terminal terminal, s.program program,   
 s.username user_name, s.fixed_table_sequence activity_meter, '' query,   
 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num   
 from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER' 
 order by s.username, s.osuser;
       
       

 

转载于:https://my.oschina.net/itsuifeng/blog/806679