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 '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 reads direct (lob)’ and
d.name=’session logical reads’;
————————————————————————————————————————————————
31.Soft parse ratio：这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。
公式：1 - ( parse count (hard) / parse count (total) )
执行：
select 1-(a.value/b.value)
from v$sysstat a,v$sysstat b
Where a.name=’parse count (hard)’ and b.name=’parse count (total)’;
————————————————————————————————————————————————
32.In-memory sort ratio：该项显示内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。
公式：sorts (memory) / ( sorts (memory) + sorts (disk) )
执行：
select a.value/(b.value+c.value)
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name=’sorts (memory)’ and
b.name=’sorts (memory)’ and c.name=’sorts (disk)’;
————————————————————————————————————————————
33.Parse to execute ratio：在生产环境，最理想状态是一条sql语句一次解析多数运行。
公式：1 - (parse count/execute count)
执行：
select 1-(a.value/b.value)
from v$sysstat a,v$sysstat b
where a.name=’parse count (total)’ and b.name=’execute count’;
————————————————————————————————————————————
34.Parse CPU to total CPU ratio：该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低，说明系统执行了太多的解析。
公式：1 - (parse time cpu / CPU used by this session)
执行：
select 1-(a.value/b.value)
from v$sysstat a,v$sysstat b
where a.name=’parse time cpu’ and
b.name=’CPU used by this session’;
——————————————————————————————————————————————
35.Parse time CPU to parse time elapsed：通常，该项显示锁竞争比率。这项比率计算
是否时间花费在解析分配给CPU进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费
公式：parse time cpu / parse time elapsed
执行：
select a.value/b.value
from v$sysstat a,v$sysstat b
where a.name=’parse time cpu’ and b.name=’parse time elapsed’;
—————————————————————————————————————————————
36.从V$SYSSTAT获取负载间档(Load Profile)数据
负载间档是监控系统吞吐量和负载变化的重要部分，该部分提供如下每秒和每个事务的统计信息：logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.
被格式化的数据可检查’rates’是否过高，或用于对比其它基线数据设置为识别system profile在期间如何变化。例如，计算每个事务中block changes可用如下公式：
db block changes / ( user commits + user rollbacks )
执行：
select a.value/(b.value+c.value)
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name=’db block changes’ and
b.name=’user commits’ and c.name=’user rollbacks’;
其它计算统计以衡量负载方式，如下：
l Blocks changed for each read：这项显示出block changes在block reads中的比例。它将指出是否系统主要用于只读访问或是主要进行诸多数据操作(如：inserts/updates/deletes)
公式：db block changes / session logical reads
执行：select a.value/b.value from v$sysstat a,v$sysstat b where a.name=’db block changes’ and b.name=’session logical reads’ ;
——————————————————————————————————————————————————
Rows for each sort：
公式：sorts (rows) / ( sorts (memory) + sorts (disk) )
执行：
select a.value/(b.value+c.value)
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name=’sorts (rows)’ and
b.name=’sorts (memory)’ and c.name=’sorts (disk)’;
——————————————————————————————————————————————————
37.查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper(’&table_name’);
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper(’&table_owner’) and c.table_name = upper(’&table_name’)
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
————————————————————————————————————————————————38.查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
___________________________________________________________________________________39.查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES ”% USED”,(C.BYTES*100)/A.BYTES ”% FREE”
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
___________________________________________________________________________________40.当移动一个表的多个分区时的脚本
BEGIN
FOR x IN (SELECT partition_name
FROM user_tab_partitions
WHERE table_name = ‘BIG_TABLE2′)
LOOP
EXECUTE IMMEDIATE ‘alter table big_table2 move partition ‘
|| x.partition_name;
END LOOP;
END;
/
___________________________________________________________________________________41.查看LOCK
SELECT /*+ ORDERED USE_HASH(H,R) */
H.SID HOLD_SID,
R.SID WAIT_SID,
decode(H.type,
”MR”, ”Media Recovery”,
”RT”, ”Redo Thread”,
”UN”, ”User Name”,
”TX”, ”Transaction”,
”TM”, ”DML”,
”UL”, ”PL/SQL User Lock”,
”DX”, ”Distributed Xaction”,
”CF”, ”Control File”,
”IS”, ”Instance State”,
”FS”, ”File Set”,
”IR”, ”Instance Recovery”,
”ST”, ”Disk Space Transaction”,
”TS”, ”Temp Segment”,
”IV”, ”Library Cache Invalidation”,
”LS”, ”Log Start or Switch”,
”RW”, ”Row Wait”,
”SQ”, ”Sequence Number”,
”TE”, ”Extend Table”,
”TT”, ”Temp Table”,
H.type) type,
decode(H.lmode,
0, ”None”, 1, ”Null”,
2, ”Row-S (SS)”, 3, ”Row-X (SX)”,
4, ”Share”, 5, ”S/Row-X (SSX)”,
6, ”Exclusive”, to_char(H.lmode)) hold,
decode(r.request, 0, ”None”,
1, ”Null”, 2, ”Row-S (SS)”,
3, ”Row-X (SX)”, 4, ”Share”,
5, ”S/Row-X (SSX)”,6, ”Exclusive”,
to_char(R.request)) request,
R.ID1,R.ID2,R.CTIME
FROM VLOCK H,VLOCK R
WHERE H.BLOCK = 1 AND R.BLOCK=0
and H.TYPE <> ”MR” AND R.TYPE <> ”MR”
AND H.ID1 = R.ID1 AND H.ID2 = R.ID2
___________________________________________________________________________________42.查看ORACLE运行的OS平台
SQL> run
1 begin
2 dbms_output.put_line(
3 dbms_utility.port_string);
4* end;
___________________________________________________________________________________43.表空间统计
col 表空间名 for a15
SELECT upper(f.tablespace_name) "表空间名",
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "使用比",f.total_bytes "空闲空间(M)",f.max_bytes "最大块(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
___________________________________________________________________________________
44 查看无法扩展的段
A、 脚本说明：
ORACLE对一个段比如表段或索引无法扩展时，取决的并不是表空间中剩余的空间是多少，而是取于这些剩余空间中最大的块是否够表比索引的“NEXT”值大，所以有时一个表空间剩余几个G的空闲空间，在你使用时ORACLE还是提示某个表或索引无法扩展，就是由于这一点，这时说明空间的碎片太多了。这个脚本是找出无法扩展的段的一些信息。
B、脚本原文：
SELECT segment_name,
segment_type,
owner,
a.tablespace_name "tablespacename",
initial_extent/1024 "inital_extent(K)",
next_extent/1024 "next_extent(K)",
pct_increase,
b.bytes/1024 "tablespace max free space(K)",
b.sum_bytes/1024 "tablespace total free space(K)"
FROM dba_segments a,
(SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
AND next_extent>b.bytes
ORDER BY 4,3,1;
___________________________________________________________________________________
45、 查看段(表段、索引段)所使用空间的大小
A、 脚本说明：
有时你可能想知道一个表或一个索引占用多少M的空间，这个脚本就是满足你的要求的，把<>中的内容替换一下就可以了。
B、脚本原文：
SELECT owner,
segment_name,
SUM(bytes)/1024/1024
FROM dba_segments
WHERE owner=<segment owner>
And segment_name=<your table or index name>
GROUP BY owner,segment_name
ORDER BY 3 DESC;
___________________________________________________________________________________
46、 查看数据库中的表锁
A、 脚本说明：
这方面的语句的样式是很多的，各式一样，不过我认为这个是最实用的，不信你就用一下，无需多说，锁是每个DBA一定都涉及过的内容，当你相知道某个表被哪个session锁定了，你就用到了这个脚本。
B、脚本原文：
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
ORDER BY 1,2;
___________________________________________________________________________________
47、 处理存储过程被锁
A、 脚本说明：
实际过程中可能你要重新编译某个存储过程理总是处于等待状态，最后会报无法锁定对象，这时你就可以用这个脚本找到锁定过程的那个sid，需要注意的是查v$access这个视图本来就很慢，需要一些布耐心。
B、脚本原文：
SELECT * FROM V$ACCESS
WHERE owner=<object owner>
And object<procedure name>
___________________________________________________________________________________ 48、 查看回滚段状态
A、 脚本说明
这也是DBA经常使用的脚本，因为回滚段是online还是full是他们的关怀之列嘛
B、
SELECT a.segment_name,b.status
FROM Dba_Rollback_Segs a,
v$rollstat b
WHERE a.segment_id=b.usn
ORDER BY 2
____________________________________________________________________________________
49、 看哪些session正在使用哪些回滚段
A、 脚本说明：
当你发现一个回滚段处理full状态，你想使它变回online状态，这时你便会用alter rollback segment rbs_seg_name shrink,可很多时侯确shrink不回来，主要是由于某个session在用，这时你就用到了这个脚本，找到了sid的serial#余下的事就不用我说了吧。
B、脚本原文
SELECT r.name 回滚段名,
s.sid,
s.serial#,
s.username 用户名,
s.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程序
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
AND r.NAME IN ('ZHYZ_RBS')
ORDER BY t.cr_get,t.phy_io
____________________________________________________________________________________
50、 查看正在使用临时段的session
A、 脚本说明：
许多的时侯你在查看哪些段无法扩展时，回显的结果是临时段，或你做表空间统计时发现临段表空间的可用空间几乎为0,这时按oracle的说法是你只有重新启动数据库才能回收这部分空间。实际过程中没那么复杂，使用以下这段脚本把占用临时段的session杀掉，然后用alter tablespace temp coalesce;这个语句就把temp表空间的空间回收回来了。
B、 脚本原文
SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
____________________________________________________________________________________
51、查询shared_pool里面的sql
找出除了谓词内容不同之外都相同的sql语句重复次数。
其实就是看看变量绑定情况
create table t1 as select sql_text from v$sqlarea;
alter table t1 add sql_text_wo_constants varchar2(1000);
create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
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;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);
select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/
____________________________________________________________________________________
52、查询不够优化的SQL语句是导致cache buffers chains latch的主要原因。如果SQL语句需要访问过多的内存数据块，那么必然会持有latch很长时间。找出逻辑读特别大的sql语句进行调整，一个比较有针对性的、查找这种引起较为严重的cache buffers chains latch的SQL语句的方式，其原理是根据latch的地址，到x$bh中找对应的buffer header，x$bh的hladdr表示该buffer header所对应的latch地址。然后根据buffer header可以找到所对应的表的名称。最后可以到v$sqltext（也可以到stats$sqltext）中找到引用了这些表的SQL语句。
Select/*+rule*/ s.sql_text
From x$bh a,dba_extents b,
(select * from (select addr from v$latch_children
Where name=’cache buffers chains’ order by sleeps desc)
Where rownum<11) c,
v$sqltext s
where a.hladdr=c.addr
and a.dbarfil=b.relative_fno
and a.dbablk between b.block_id and b.block_id+b.blocks
and s.sql_text like ‘%’||b.segment_name||’%’ and b.segment_type=’TABLE’
order by s.hash_value,s.address,s.piece
/
select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 11);
通过x$bh.tch来查找热对象
select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.
___________________________________________________________________________________
53. 显示缓冲区的相关SQL
SELECT tch, file#, dbablk,
CASE
WHEN obj = 4294967295
THEN 'rbs/compat segment'
ELSE (SELECT MAX ( '('
|| object_type
|| ') '
|| owner
|| '.'
|| object_name
)
|| DECODE (COUNT (*), 1, '', ' maybe!')
FROM dba_objects
WHERE data_object_id = x.obj)
END what
FROM (SELECT tch, file#, dbablk, obj
FROM x$bh
WHERE state <> 0
ORDER BY tch DESC) x
WHERE ROWNUM <= 5;
__________________________________________________________________________________
54. 在v$session_longops视图中，sofar字段表示已经扫描的块数，totalwork表示总得需要扫描的块数，所以我们即可以对正在运行的长查询进行监控，比如在索引创建时，查看索引创建的进度，也可以查看系统中以往的长查询。。。
col opname format a32
col target_desc format a32
col perwork format a12
set lines 131
select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork
from v$session_longops where sofar!=totalwork;
set lines 121
set pages 999
col opname format a29
col target format a29
col target_desc format a12
col perwork format a12
col remain format 99
col start_time format a21
col sofar format 99999999
col totalwork format 99999999
col sql_text format a101
col bufgets format 99999999
select opname,target,to_char(start_time,'yy-mm-dd:hh24:mi:ss') start_time,elapsed_seconds elapsed,
executions execs,buffer_gets/decode(executions,0,1,executions) bufgets,module,sql_text
from v$session_longops sl,v$sqlarea sa
where sl.sql_hash_value = sa.hash_value and upper(substr(module,1,4)) <> 'RMAN' and substr(opname,1,4) <> 'RMAN'
and module <> 'SQL*Plus' and sl.start_time>trunc(sysdate)
order by start_time;
==========================================================
55. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait group by event order by 4;
56. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;
57. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;
58. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;
59.在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;
60. 监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
61. 监控 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;
62. 监控 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;
63. 显示所有数据库对象的类别和大小 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;
64. 监控 SGA 中重做日志缓存区的命中率，应该小于1% 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');
65. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
66. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
67. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者,此比率小于1%,接近0%为好。 SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE
68. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
69. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher; 此值大于0.5时，参数需加大 select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; select count(*) from v$dispatcher; select servers_highwater from v$mts; servers_highwater接近mts_max_servers时，参数需加大
70. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10; alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; select * from ts_blocks_v; select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name; 查看碎片程度高的表 SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
__________________________________________________________________________________
71 寻找CPU使用过量的session ，找出高CPU利用率的SQL：
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC;
___________________________________________________________________________________72 找出当前session中最高的logical和Physical I/O比率.
下列SQL语句显示了所有连接到数据库的session逻辑、物理读比率(每秒)。logical和physical I/O比率是通过自登陆后的时间消耗计算得出。对于sessions连接到数据库这种长周期操作而言也许不够精确，不过做个示例却足够了。
先获得session逻辑读和物理读统计项的STATISTIC#值：
SELECT name, statistic#
FROM V$STATNAME
WHERE name IN ('session logical reads','physical reads') ;
NAME STATISTIC#
------------------------------ ----------
session logical reads 9
physical reads 40
通过上面获得的STATISTIC#值执行下列语句：
SELECT ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, 60*24*(sysdate-ses.logon_time) "Minutes"
FROM V$SESSION ses
, V$SESSTAT sta
WHERE ses.status = 'ACTIVE'
AND sta.sid = ses.sid
AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;
___________________________________________________________________________________73 .select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;
查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
___________________________________________________________________________________74. 查看librariy cache 交换的情况
select namespace,
pins,
pinhits,
reloads,
invalidations,
pinhitratio * 100 pinhitratio
from v$librarycache;
2 3 4 5 6 7
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS PINHITRATIO
--------------- ---------- ---------- ---------- ------------- -----------
SQL AREA 7501530 7033847 240241 4498 93.7654985
TABLE/PROCEDURE 3752257 3184629 290543 0 84.8723582
BODY 81211 71968 8757 0 88.6185369
TRIGGER 10136 7619 1832 0 75.167719
INDEX 60769 38163 16237 0 62.8001119
CLUSTER 43475 42162 1237 0 96.9798735
OBJECT 0 0 0 0 100
PIPE 0 0 0 0 100
JAVA SOURCE 0 0 0 0 100
JAVA RESOURCE 0 0 0 0 100
JAVA DATA 0 0 0 0 100
11 rows selected.
___________________________________________________________________________________75. 如果看到latch free对应的latch为library cache pin，看看是谁block了谁，
SELECT 'waitting object: ' as "OBJECT", kglnaobj "VALUE"
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library cache pin')
union all
SELECT 'holder session: ' as "OBJECT", a.SID||' '||a.username||' '||a.program "VALUE"
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library cache pin')
union all
SELECT 'SQL text: ' as "OBJECT", sql_text "VALUE"
FROM v$sqlarea
WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
SELECT sql_address, sql_hash_value
FROM v$session
WHERE SID IN (
SELECT SID
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')))；
x$kghlu中看看各个sub pool的状态
物化视图停止同步的处理过程
select * from dba_jobs where broken='Y';
exec dbms_mview.refresh('t_edr_base','f');
exec dbms_job.broken(1399,false)
————————————————————————————————————————————————76. 如何查看session级的等待事件
当我们对数据库的性能进行调整时，一个最重要的参考指标就是系统等待事件。$system_event,v$session_event,v$session_wait这三个视图里记录的就是系统级和session级的等待事件，通过查询这些视图你可以发现数据库的一些操作到底在等待什么？是磁盘I/O，缓冲区忙，还是插锁等等。
通过如下sql你可以查询你的每个应用程序到底在等待什么，从而针对这些信息对数据库的性能进行调整。
Select s.username,s.program,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait from v$session s,v$session_event se
Where s.sid=se.sid
And se.event not like ‘SQl*Net%’
And s.status = ‘ACTIVE’
And s.username is not null；
————————————————————————————————————————————————
77.数据库使用plsqldev 需要单独授权
grant imp_full_database to user_name;
