Chinaunix首页 | 论坛 | 博客
  • 博客访问: 52523
  • 博文数量: 11
  • 博客积分: 469
  • 博客等级: 下士
  • 技术积分: 150
  • 用 户 组: 普通用户
  • 注册时间: 2012-01-17 09:45
文章分类

全部博文(11)

文章存档

2012年(11)

我的朋友

分类: Oracle

2012-08-21 08:35:34

1   根据进程号查找占用资源的sql语句
select sess.username,sql1.SQL_TEXT from v$session sess,v$sqltext sql1,v$process pro
where sess.SQL_ADDRESS=sql1.ADDRESS and pro.ADDR=sess.PADDR and pro.SPID='&1' order by  sql1.PIECE;
————————————————————————————————————————————————
2   查找相关对象的锁
select  oracle_username,os_user_name,process,object_name,o.SESSION_ID from v$locked_object o,dba_objects d where o.OBJECT_ID=d.object_id and object_name='T_PLY_BASE';
————————————————————————————————————————————————
3   杀掉某模块的session
SELECT distinct 'ALTER SYSTEM KILL SESSION '''||s.SID||','||s.SERIAL#||''';',p.PID
FROM V$SESSION S,v$process p
WHERE s.PADDR=p.ADDR
 and s.PROGRAM like '&1%';
————————————————————————————————————————————————
4   杀掉某台机器的session
select s.username,s.sid,s.SERIAL#,machine,s.terminal,spid from v$session  s , v$process p where machine like '%NZF%' and s.PADDR=p.ADDR;
对上面问题的补充,当上面语句不能解决问题时
select 'kill -9 '||p.spid
 from v$process p,v$session s where p.ADDR=s.PADDR  and s.PROGRAM like '&1%';
————————————————————————————————————————————————
5 决定pga_aggregate_target的最小值
select min(pga_target_for_estimate)
from   v$pga_target_advice
where  estd_pga_cache_hit_percentage>95;       
————————————————————————————————————————————————
6 察看shared_pool_size实用情况
col value for 999,999,999,999 heading "Shared Pool Size";
col bytes for 999,999,999,999 heading "Free Bytes";
select to_number(v$parameter.value) value,v$sgastat.bytes,
       (v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
from v$sgastat,v$parameter
where v$sgastat.name='free memory'
and v$parameter.name='shared_pool_size'
and v$sgastat.pool='shared pool';
————————————————————————————————————————————————
7 如何监控 SGA 中字典缓冲区的命中率?
  select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
  (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
  from v$rowcache
  where gets+getmisses <>0
  group by parameter, gets, getmisses;
————————————————————————————————————————————————
8 如何监控 SGA 中共享缓存区的命中率,应该小于1% ?
  select sum(pins) 'Total Pins', sum(reloads) 'Total Reloads',
  sum(reloads)/sum(pins) *100 libcache
  from v$librarycache;
  select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins)  "reload percent"
  from v$librarycache;
————————————————————————————————————————————————
9 如何显示所有数据库对象的类别和大小?
  select count(name) num_instances ,type ,sum(source_size) source_size ,
  sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size)  error_size,
  sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size)  size_required
  from dba_object_size
  group by type order by 2;
————————————————————————————————————————————————
10. 监控 SGA 中重做日志缓存区的命中率,应该小于1%
column name format A20;
  SELECT name, gets, misses, immediate_gets, immediate_misses,
  Decode(gets,0,0,misses/gets*100) ratio1,
  Decode(immediate_gets+immediate_misses,0,0,
  immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
  FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
————————————————————————————————————————————————11. 如何知道使用CPU多的用户session?   是cpu used by this session
  select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
  from v$session a,v$process b,v$sesstat c
  where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
————————————————————————————————————————————————
12. SQLPLUS下如何修改编辑器?
DEFINE _EDITOR="<编辑器的完整路经>" -- 必须加上双引号
来定义新的编辑器,也可以把这个写在$ORACLE_HOME/sqlplus/admin/glogin.sql里面使它永久有效。
————————————————————————————————————————————————
13.LINUX下查询磁盘竞争状况命令?
  Sar -d
————————————————————————————————————————————————
14. 察看日志切换速度
select b.recid,to_char(b.first_time,'dd-mon-yy hh24:mi:ss') start_time,a.recid,
to_char(a.first_time,'dd-mon-yy hh24:mi:ss')end_time,round(((a.first_time-b.first_time)*24)*60,2) minutes
from v$log_history a,v$log_history b where a.recid=b.recid+1 order by a.first_time asc;
————————————————————————————————————————————————
15. 察看锁的情况
column sess format a15;
select decode(request,0,'Holder:','Waiter:')||sid sess,id1,id2,lmode,request,type
from v$lock where (id1,id2,type) in (select id1,id2,type from v$lock where request>0)
order by id1,request;
查出锁的情况和进程
select s.sid,s.serial#,s.program,s.machine,s.username,s.last_call_et,
s.logon_time,sw.event from v$session s,v$session_wait sw where s.sid=sw.sid and s.sid=2883625;
根据进程号查出是什么东西锁住了
select s.sid,s.serial#,s.program,s.machine,s.username,s.sql_hash_value,s.row_wait_obj# from v$session s
where .sid=377'
select * from v$sqltext a,v$session b where a.HASH_VALUE=b.SQL_HASH_VALUE and  a.SID='&1' order by piece;
select 'alter system kill session '''||sid||','||serial#||''';' from v$session s,v$sqltext sq
 where s.SQL_HASH_VALUE=sq.HASH_VALUE and sq.SQL_TEXT like '%CUX_GL_TAX%'  order by logon_time
————————————————————————————————————————————————
16. 产生删除某个进程session的sql;
select distinct 'ALTER SYSTEM KILL SESSION '''||SID||','||s.SERIAL#||''';' from v$session s,v$process p where s.PADDR=p.ADDR and  p.sPID like '%&1%';
产生删除某个进程模块的sql;
select distinct 'ALTER SYSTEM KILL SESSION '''||SID||','||s.SERIAL#||''';' from v$session s,v$process p where s.PADDR=p.ADDR and  s.PROGRAM like 'svClaim%';
查询模块,机器,共执行的数目
select machine,schemaname,program,count(*) from (
select s.serial#,s.sid,s.MACHINE, p.pid,p.spid, s.SCHEMANAME,s.PROGRAM from v$process p,v$session s where p.ADDR=s.PADDR  and s.SCHEMANAME not in ('SYS') /*and s.PROGRAM like 'sv%'*//* order by s.SCHEMANAME,s.PROGRAM*/
) group by machine,schemaname,program;
————————————————————————————————————————————————
17.logminer 检查执行步骤
execute dbms_logmnr.add_logfile('/oradata1/archive/abstandby/1_28456.dbf',dbms_logmnr.new);
select low_time,high_time,low_scn,next_scn from v$logmnr_logs;
exec dbms_logmnr.start_logmnr( dictfilename=>'/oradata1/utlfile/shwdict.ora',startscn=>2379878362,endscn=>2379976020,starttime=>to_date('20060930 01:15:00','yyyymmdd hh24:mi:ss'),endtime=>to_date('20060930 21:40:30','yyyymmdd hh24:mi:ss'));

Select SCN,timestamp, session# session_num, sql_redo
  From V$LOGMNR_CONTENTS
  Order by 1
————————————————————————————————————————————————
18. 清除已删除的arch log 信息
 crosscheck archivelog all;
delete expired archivelog all;
————————————————————————————————————————————————
19。查找等待时间最长的语句
select a.USERNAME,a.OSUSER,a.PROCESS,a.MACHINE,a.ACTION,a.sid, a.last_call_et ,b.sql_text
from v$session a
    ,v$sqltext b
where a.username is not null
and   a.status = 'ACTIVE'
and   a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece ;
根据查出来的sid判断等待的对象
select owner,segment_name,segment_type
from (select p1 file#, p2 block# from  v$session_wait
      where sid = 284
      and event in ('buffer busy waits'
                   ,'db file sequential read'
                   ,'db file scattered read'
                   ,'free buffer waits')) b
,dba_extents a
where a.file_id = b.file#
and   b.block# between a.block_id and (a.block_id+blocks-1);
————————————————————————————————————————————————
20. 监控索引使用
select * from V$OBJECT_USAGE where used='YES'
select * from V$OBJECT_USAGE where used='NO'
select 'alter index '||index_name||' monitoring usage;' from dba_indexes where owner='ABSYS';
————————————————————————————————————————————————
21. 查找正在执行的存储过程
create or replace procedure sys.who_is_using(obj_name varchar2) is
begin
dbms_output.enable(1000000);
   for i in (SELECT distinct b.username,b.sid
             FROM SYS.x$kglpn a,v$session b,SYS.x$kglob c
             WHERE a.KGLPNUSE = b.saddr
               and upper(c.KGLNAOBJ)  like upper(OBJ_NAME)
               and a.KGLPNHDL = c.KGLHDADR)
               loop
   dbms_output.put_line('('||to_char(i.sid)||') - '||i.username);
   end loop;
end;
————————————————————————————————————————————————
22.查找全表扫描的SQL语句
Select sql_text from v$sqltext t, v$sql_plan p
Where t.hash_value=p.hash_value
And p.operation='INDEX'
And p.OPTIONS='FULL SCAN'
Order by p.HASH_VALUE, t.piece;
查找Fast Full index 扫描的Sql语句可以这样;
Select sql_text from v$sqltext t, v$sql_plan p
Where t.hash_value=p.hash_value
And p.operation='INDEX'
And p.OPTIONS='FULL SCAN'
Order by p.HASH_VALUE, t.piece;
 
————————————————————————————————————————————————
23.已经alter system kill session 但是没有kill干净,查找进程号
select p.addr from v$process p where pid <> 1 
minus 
select s.paddr from v$session s;
————————————————————————————————————————————————
24.10g自动收集数据
select JOB_NAME,ENABLED,state from dba_scheduler_jobs;
exec dbms_scheduler.disable('GATHER_STATS_JOB');
exec dbms_scheduler.enable('GATHER_STATS_JOB');
————————————————————————————————————————————————
25.查询有enqueue等待的事件
SELECT   b.SID, b.serial#, b.username, machine, event, wait_time,
            CHR (BITAND (p1, -16777216) / 16777215)
         || CHR (BITAND (p1, 16711680) / 65535) "Enqueue Type"
    FROM v$session_wait a, v$session b
   WHERE a.event NOT LIKE 'SQL*N%'
     AND a.event NOT LIKE 'rdbms%'
     AND a.SID = b.SID
     AND b.SID > 8
     AND a.event = 'enqueue'
ORDER BY username;
————————————————————————————————————————————————
26.如何确定哪个表空间读写频繁?
select name,phyrds,phywrts,readtim,writetim
         from v$filestat a,v$dbfile b
         where a.file# = b.file#
         order by readtim desc
————————————————————————————————————————————————
27.在磁盘上的物理写入和读取次数上如果出现很大的差别,就表明肯定有哪个磁盘负载过多!
    如果出现磁盘负载不平衡,可以通过移动数据文件来均衡文件I/O:
         alter tablespace tablespace_name offline;
         $cp /disk1/a.dbf /disk2/a.dbf;
         alter tablespace tablespace_name rename datafile ‘/disk1/a.dbf’ to ‘/disk2/a.dbf’;
         alter tablespace tablespace online;
         $rm /disk1/a.dbf
————————————————————————————————————————————————
28. 查询SQL语句执行时,硬语法分析的次数
select name,value
from v$sysstat
where name like ‘parse count%’;
————————————————————————————————————————————————
29.查询SQL语句中没有帮定变量的SQL语句,,按执行次数排序
SELECT SUBSTR (sql_text, 1, 40) “SQL”, COUNT (*),
SUM (executions) “TotExecs” FROM v$sqlarea WHERE executions < 5
GROUP BY SUBSTR (sql_text, 1, 40) HAVING COUNT (*) > 20 ORDER BY 2;
————————————————————————————————————————————————
30.该项显示buffer cache大小是否合适
公式:1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads)
执行:
select 1-((a.value-b.value-c.value)/d.value)
  from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d
  where a.name=’physical reads’ and
         b.name=’physical reads direct’ and
         c.name=’physical r
阅读(1836) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~