1、数据文件大小及头大小:
SELECT v1.file_name,v1.file_id,
num1 totle_space, --该数据文件的总大小，字节为单位
num3 free_space, --该数据文件的剩于大小，字节为单位
num1-num3 Used_space, --该数据文件的已用空间，字节为单位
nvl(num2,0) data_space, --该数据文件中段数据占用空间，也就是数据空间，字节为单位 num1-num3-nvl(num2,0) file_head --该数据文件头部占用空间，字节为单位
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files
GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3 WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
2、数据库各个表空间增长情况的检查：
SQL>select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
From (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name;
SQL>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
F.TABLESPACE_NAME
ORDER BY 4 DESC;
3、查看各个表空间占用磁盘情况:
SQL>col tablespace_name format a20;
SQL>select b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b D.TABLESPACE_NAME =
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
4、数据库对象下一扩展与表空间的free扩展值的检查
SQL>select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk
union select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;
5、Disk Read最高的SQL语句的获取：
SQL>select sql_text from (select * from v$sqlarea order by disk_reads)
where rownum<=5;
6、查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
sql_text FROM v$sqlarea ORDER BY disk_reads DESC)
WHERE ROWNUM<10 ;
7、等待时间最多的5个系统等待事件的获取
SQL>select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5;
8、查看当前等待事件的会话
col username format a10
set line 120
col EVENT format a30
select SE.Sid,s.Username,SE.Event,se.Total_Waits,SE.Time_Waited,SE.Average_Wait from v$session S,v$session_event SE where S.Username is not null and SE.Sid=S.Sid and S.Status='ACTIVE' and SE.Event not like '%SQL*Net%';
select sid, event, p1, p2, p3, wait_time, seconds_in_wait, state from v$session_wait where event not like '%message%' and event not like 'SQL*Net%' and event not like '%timer%' and event != 'wakeup time manager';
9、找到与所连接的会话有关的当前等待事件
select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;
10、查看表空间的名称及大小
SQL>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
11、查看表空间物理文件的名称及大小
SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
12、查看回滚段名称及大小
SQL>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;
13、如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句
SQL>select d.sql_text,a.name
from v$rollname a,v$transaction b,v$session c,v$sqltext d
where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=
d.address and c.sql_hash_value=d.hash_value
and a.usn=1;
(备注：你要看哪个，就把usn=?写成几就行了)
查看控制文件:
SQL>select * from v$controlfile;
查看日志文件:
SQL> col member format a50
SQL>select * from v$logfile;
如何查看当前SQL*PLUS用户的sid和serial#:
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');
如何查看当前数据库的字符集:
SQL>select userenv('language') from dual;
SQL>select userenv('lang') from dual;
怎么判断当前正在使用何种SQL优化方式:
用explain plan产生EXPLAIN PLAN，检查PLAN_TABLE中ID=0的POSITION列的值。 SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;
如何查看系统当前最新的SCN号：
SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;
在ORACLE中查找TRACE文件的脚本:
SQL>select u_dump.value || '/' || instance.value || '_ora_' ||
v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from
v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid'); SQL>select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from (select p.spid from sys.v_$mystat m,sys.v_$session s,
sys.v_$process p where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d;
如何查看客户端登陆的IP地址:
SQL>select sys_context('userenv','ip_address') from dual;
14、如何在生产数据库中创建一个追踪客户端IP地址的触发器
SQL>create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS' BEGIN
insert into session_history
select
username,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
from v$session where audsid = userenv('sessionid');
END;
查询当前日期:
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
查看所有表空间对应的数据文件名：
SQL>select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
15、查看表空间的使用情况:
SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space group by tablespace_name;
SQL>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;
column tablespace_name format a18;
column Sum_M format a12;
column Used_M format a12;
column Free_M format a12;
column pto_M format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M' Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M' Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,
sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100 FreePercentRatio from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by b.file_id,b.tablespace_name,b.bytes
order by b.tablespace_name) s group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc;
16、查看数据文件的hwm（可以resize的最小空间）和文件头大小
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 "USED_SPACE(HWM)",
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3 WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
17、怎样计算一个表占用的空间的大小？
SELECT OWNER,TABLE_NAME,NUM_ROWS,
BLOCKS*AAA/1024/1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME=’xxx’;
HERE:AAA is the value of db_block_size;
XXX is the table name you want to check
第二种方法：列出了所有表的占用情况
select segment_name,sum(bytes)/1024/1024 “Size M” from user_extents group by segment_name; BY
18、查询曾经到达的最大会话数
SELECT * FROM V$LICENSE;
其中sessions_highwater记录曾经到达的最大会话数
19、如何以archivelog的方式运行oracle
Init.ora->log_archive_start =true->RESTART DATABASE
20、怎么获取有哪些用户在使用数据库
Select username from v$session;
数据表中字段最大数，表或视图中的最大列数为1000
查询数据库的sid:select name from v$database;
21、如何将表移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
将索引移动表空间：
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
22、查询锁并解锁：
查锁
select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.object_name
from v$locked_object t1,v$session t2,dba_objects t3
where t1.session_id=t2.sid and t1.object_id = t3.object_id
order by t2.logon_time;
解锁
alter system kill session 'sid,serial#';
23、如何才能得知系统当前的SCN号？
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
注：需要以dba用户登录才能查询结果
24、扑捉运行很久的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;
25、监控当前数据库谁在运行什么sql语句
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
26、查找耗资源的进程（top session）
select s.schemaname schema_name,decode(sign(48 - command), 1,
to_char(command), 'Action Code #' || to_char(command) ) action,
status session_status,s.osuser os_user_name,s.sid,p.spid,
s.serial# serial_num,nvl(s.username,'[Oracle process]') user_name,
s.terminal terminal,s.program program,st.value criteria_value
from v$sesstat st,v$session s,v$process p
where st.sid = s.sid and st.statistic# = to_number('38')
and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr
order by st.value desc,p.spid asc,s.username asc,s.osuser asc;
27、根据PID查找对应的语句：
SELECT a.username,
a.machine,a.program,a.sid,a.serial#,
a.status,c.piece,c.sql_text
FROM v$session a,v$process b,v$sqltext c WHERE b.spid=spid
AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
28、根据SID找ORACLE的某个进程:
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
29、如何查看数据库中某用户，正在运行什么SQL语句
SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S
WHERE T.ADDRESS=S.SQL_ADDRESS
AND T.HASH_VALUE=S.SQL_HASH_VALUE
AND S.MACHINE='XXXXX' OR USERNAME='WACOS';
某sid对应的sql语句：
select COMMAND_TYPE,PIECE,sql_text from v$sqltext
where address=(select sql_address from v$session a where sid=18);
30、如何查出前台正在发出的sql语句:
SQL> select user_name,sql_text from v$open_cursor
where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'));
31、CPU用率最高的2条SQL语句的获取（执行：top，通过top获得CPU占用率最高的进程的pid）
select sql_text,spid,v$session.program,process
from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr and v$process.spid in (pid);
32、查看object分类数量:
select decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',
6, 'SEQUENCE','OTHER') object_type ,
count(*) quantity
from sys.obj$ o where o.type# > 1
group by decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER' ,4,'VIEW',5,'SYNONYM', 6,'SEQUENCE','OTHER')
union
select 'COLUMN', count(*)
from sys.col$
union select 'DB LINK' , count(*) from all_objects;
33、查看占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;
34、查看占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;
oracle数据库性能监控的SQL
1. 监控事例的等待
SQL>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;
2. 回滚段的争用情况
SQL>select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;
3. 监控表空间的 I/O 比例
SQL>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;
4. 监控文件系统的 I/O 比例
SQL>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#;
5.在某个用户下找所有的索引
SQL>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;
6. 监控 SGA 的命中率
SQL>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;
7. 监控 SGA 中字典缓冲区的命中率
SQL>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%
SQL>select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache from v$librarycache;
SQL>select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;
9. 显示所有数据库对象的类别和大小
SQL>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%
SQL>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. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 监控当前数据库谁在运行什么SQL语句
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SQL>SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SQL>SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SQL>SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;(后者除以前者,此比率小于1%,接近0%为好) SQL>SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY
CACHE GET MISSES" FROM V$ROWCACHE;
14. 查找ORACLE字符集
SQL>select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
SQL>select busy/(busy+idle) "shared servers busy" from v$dispatcher;
(此值大于0.5时，参数需加大)
SQL>select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; SQL>select count(*) from v$dispatcher;
SQL>select servers_highwater from v$mts;
(servers_highwater接近mts_max_servers时，参数需加大)
16. 碎片程度
SQL>select tablespace_name,count(tablespace_name)
tablespace_name having count(tablespace_name)>10;
SQL>alter tablespace name coalesce;
SQL>alter table name deallocate unused;
SQL>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;
SQL>select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;
查看碎片程度高的表
SQL>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);
17. 表、索引的存储情况检查
SQL>select segment_name,sum(bytes),count(*)
and ext_quan from dba_extents group where by tablespace_name='&tablespace_name'
tablespace_name,segment_name;
owner='&owner' group by segment_name;
(a.program,1,40) prog,a.termin
(12是cpu used by this session) segment_type='TABLE' from dba_free_space group by SQL>select segment_name,count(*) from dba_extents where segment_type='INDEX' and