常用SQL

1、通过sql查询日志文件位置 alert_xxx.log

select value from v$diag_info where name = 'Diag Trace';

2、正在执行的sql

SELECT B.SID ORACLEID,
B.USERNAME 登录ORACLE用户名,
PADDR,
B.OSUSER,
B.MACHINE 计算机名,
B.SECONDS_IN_WAIT,
B.SQL_ID,
b.SQL_CHILD_NUMBER,
B.STATUS,
('alter system kill session ''' || b.sid || ',' || b.serial# || ''';') AS killsql,
(SELECT C.ELAPSED_TIME/1000000 FROM V$SQL C WHERE C.SQL_ID = B.SQL_ID and b.SQL_CHILD_NUMBER=c.CHILD_NUMBER) AS ELAPSED_TIME_累计秒,
(SELECT (case when EXECUTIONS=0 then 0 else C.ELAPSED_TIME/c.EXECUTIONS/1000000 end) FROM V$SQL C where C.SQL_ID = B.SQL_ID and b.SQL_CHILD_NUMBER=c.CHILD_NUMBER) AS 平均单次秒,
(SELECT C.SQL_TEXT FROM V$SQL C WHERE C.SQL_ID = B.SQL_ID and b.SQL_CHILD_NUMBER=c.CHILD_NUMBER) AS SQL_TEXT,
(SELECT C.SQL_FULLTEXT FROM V$SQL C WHERE C.SQL_ID = B.SQL_ID AND ROWNUM <= 1) AS SQL_FULLTEXT
FROM V$SESSION B
WHERE B.OSUSER <> 'oracle'
AND B.SQL_ID IS NOT NULL
ORDER BY 平均单次秒 desc nulls last;

 

--好奇 什么情况下以下能查到值 即查询正在执行的sql理论上可以去掉v$process
select * from v$session t where t.PADDR not in (select ADDR from v$process);

3、

alter system set statistics_level=basic;  
alter system set statistics_level=typical;
alter system set statistics_level=all; 
or  
alter session set statistics_level=basic; 
alter session set statistics_level=typical
alter session set statistics_level=all;  

alter system flush shared_pool;
alter system flush buffer_cache;
select * from table(dbms_xplan.display_cursor('29n9dgbsc12r2','1','allstats last'));

4、捕获非绑定变量sql

版本1（所有sql）

select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100)

变更版（每个分组一条代表sql。不加4000效率极慢）

 select max(dbms_lob.substr(SQL_FULLTEXT,4000)) SQL_ID,count(1) countnum
from v$sql o
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100;

或者

create or replace function remove_constants( p_query in varchar2 )
return varchar2
as
l_query long;
l_char varchar2(10);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10 - i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10 - i,' '), ' ' );
end loop;
return upper(l_query);
end;

create global temporary table sql_area_tmp
on commit preserve rows
as
select sql_text, sql_text sql_text_wo_constants
from v$sqlarea
where 1=0;

insert into sql_area_tmp (sql_text) select sql_text from v$sqlarea;

update sql_area_tmp set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
from sql_area_tmp
group by sql_text_wo_constants
having count(*) > 10
order by 2;

http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8force_matching_signature%E6%8D%95%E8%8E%B7%E9%9D%9E%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8Fsql.html

删除指定sql_id的执行计划

select SQL_TEXT,sql_id, address, hash_value, executions, loads, parse_calls, invalidations  
from v$sqlarea  where sql_id='ctna3p862c1f0';

--call sys.dbms_shared_pool.purge('address, hash_value','c');
call sys.dbms_shared_pool.purge('0000000645971DD0,203818432','c');

指定sql_id的执行计划　　

SELECT C.ELAPSED_TIME/1000000 as ELAPSED_TIME_累计秒 ,
(case when EXECUTIONS=0 then 0 else C.ELAPSED_TIME/c.EXECUTIONS/1000000 end) as 平均单次秒,
c.USER_IO_WAIT_TIME,c.CHILD_NUMBER
FROM V$SQL c where sql_id = 'a21930x3zkhpa';

根据sql_id获取绑定变量

select name,b.DATATYPE_STRING,value_string from dba_hist_sqlbind b where sql_id='2vymmtcm2gdhw' and snap_id=6515;

v$sql_bind_capture或dba_hist_sqlbind

执行计划

select * from table(dbms_xplan.display_cursor('14y68msrshwsk','0','allstats last'));

查看oracle被锁的表是谁锁的

SELECT b.session_id AS sid, 
       NVL(b.oracle_username, '(oracle)') AS username, 
       a.owner AS object_owner, 
       a.object_name, 
       Decode(b.locked_mode, 0, 'None', 
                             1, 'Null (NULL)', 
                             2, 'Row-S (SS)', 
                             3, 'Row-X (SX)', 
                             4, 'Share (S)', 
                             5, 'S/Row-X (SSX)', 
                             6, 'Exclusive (X)', 
                             b.locked_mode) locked_mode, 
       b.os_user_name 
FROM   dba_objects a, 
       v$locked_object b 
WHERE  a.object_id = b.object_id 
ORDER BY 1, 2, 3, 4

oracle查锁杀锁

SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
　　l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
　　FROM v$locked_object l, all_objects o, v$session s
　　WHERE l.object_id = o.object_id
　　AND l.session_id = s.sid
　　ORDER BY sid, s.serial# ;

 alter system kill session 'sid,serial#';

alter system kill session '146,759';