常用sql

DBA常用的视图:

 

1.示例：已知hash_value:3111103299，查询sql语句：  
select * from v$sqltext   
where hashvalue='3111103299' 
order by piece   
2.查看消耗资源最多的SQL：  
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls  
FROM V$SQLAREA  
WHERE buffer_gets > 10000000OR disk_reads > 1000000  
ORDERBY buffer_gets + 100 * disk_reads DESC;  
 
3.查看某条SQL语句的资源消耗：  
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls  
FROM V$SQLAREA  
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');  
 
4.查询sql语句的动态执行计划:  
        首先使用下面的语句找到语句的在执行计划中的address和hash_code  
        SELECT sql_text, address, hash_value FROM v$sql t  
                where (sql_text like '%FUNCTION_T(表名大写!)%')  
        然后:  
        SELECT operation, options, object_name, cost FROM v$sql_plan  
                WHERE address = 'C00000016BD6D248' AND hash_value = 664376056;  
 
5.查询oracle的版本:  
select * from v$version;  
 
6.查询数据库的一些参数:  
select * from v$parameter  
 
7.查找你的session信息  
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS  
FROM V$SESSION WHERE audsid = userenv('SESSIONID');  
 
8.当machine已知的情况下查找session  
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL  
FROM V$SESSION  
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';  
 
9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100  
select b.sql_text   
from v$session a,v$sqlarea b   
where a.sql_hashvalue=b.hash_value and a.sid=100