(转)Oracle维护常用SQL语句

Oracle维护常用SQL语句

 

1、查看表空间的名称及大小
　　
　　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;
　　
　　2、查看表空间物理文件的名称及大小
　　
　　select tablespace_name, file_id, file_name,
　　round(bytes/(1024*1024),0) total_space
　　from dba_data_files
　　order by tablespace_name;
　　
　　3、查看回滚段名称及大小
　　
　　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 ;
　　
　　4、查看控制文件
　　
　　select name from v$controlfile;
　　
　　5、查看日志文件
　　
　　select member from v$logfile;
　　
　　6、查看表空间的使用情况
　　
　　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;
　　
　　7、查看数据库对象
　　
　　select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
　　
　　8、查看 数据库 的版本
　　
　　Select version FROM Product_component_version
　　
　　Where SUBSTR(PRODUCT,1,6)='Oracle';
　　
　　9、查看数据库 的创建日期和归档方式 bitsCN.nET中国网管博客
　　
　　Select Created, Log_Mode, Log_Mode From V$Database;
　　
　　10、捕捉运行很久的SQL
　　
　　column username format a12
　　
　　column opname format a16
　　
　　column progress format a8
　　
　　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
　　
　　/
　　
　　11、查看数据表的参数信息
　　
　　SELECT partition_name, high_value, high_value_length, tablespace_name,
　　
　　pct_free, pct_used, ini_trans, max_trans, initial_extent,
　　
　　next_extent, min_extent, max_extent, pct_increase, FREELISTS,
　　
　　freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
　　
　　empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
　　
　　last_analyzed BBS.bitsCN.com网管论坛
　　
　　FROM dba_tab_partitions
　　
　　--WHERE table_name = :tname AND table_owner = :towner
　　
　　ORDER BY partition_position
　　
　　12、查看还没提交的事务
　　
　　select * from v$locked_object;
　　
　　select * from v$transaction;
　　
　　13、查找object为哪些进程所用
　　
　　select
　　
　　p.spid,
　　
　　s.sid,
　　
　　s.serial# serial_num,
　　
　　s.username user_name,
　　
　　a.type object_type,
　　
　　s.osuser os_user_name,
　　
　　a.owner,
　　
　　a.object object_name,
　　
　　decode(sign(48 - command),
　　
　　1,
　　
　　to_char(command), 'Action Code #' || to_char(command) ) action,
　　
　　p.program oracle_process,
　　
　　s.terminal terminal,
　　
　　s.program program,
　　
　　s.status session_status
　　
　　from v$session s, v$access a, v$process p
　　
　　where s.paddr = p.addr and
　　
　　s.type = 'USER' and bbs.bitsCN.com
　　
　　a.sid = s.sid and
　　
　　a.object='SUBSCRIBER_ATTR'
　　
　　order by s.username, s.osuser

 

---------------------------------------------------------------------------------------------------------------------

常用DBA SQL

 

查询最耗时的SQL
select t1.username,
t1.sid,
t1.opname,
t1.TARGET,
t2.sql_text,
t1.START_TIME,
t1.LAST_UPDATE_TIME
from v$session_longops t1, v$sql t2
where t1.sql_address = t2.address
and t1.sql_hash_value = t2.hash_value
order by t1.START_TIME

查找最占用资源的查询（基于V$SQL视图）
select * from (
select sql_text,
rank() over(order by buffer_gets desc) as rank_bufgets,
to_char(100 * ratio_to_report(buffer_gets) over(), '999.99') pct_bufgets
from v$sql ) where rank_bufgets <11

查找最占用资源的查询（基于V$SQLAREA 视图）
select b.username,
a.DISK_READS reads,
a.EXECUTIONS exec,
a.DISK_READS / decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS) rds_exec_ratio,
a.sql_text
from v$sqlarea a, dba_users b
where a.PARSING_USER_ID = b.user_id
and a.DISK_READS > 100000
order by a.DISK_READS desc

按OS进程ID查询数据库联系信息
select v2.* from v$process v1,v$session v2 where v1.ADDR=v2.PADDR and v1.SPID=16860

按OS进程ID查询数据库当前的SQL
SELECT b.sid,b.serail#,a.sql_text FROM
v$sqltext a,
v$session b,
v$process c
WHERE
a.hash_value = b.sql_hash_value and
b.ADDR=c.PADDR and
AND c.SID='&sid'
ORDER BY piece ASC

如何查看各个表空间占用磁盘情况？
select a.tb_name,
b.tb_size/1024/1024 tb_size_M,
(b.tb_size - a.tb_free)/1024/1024 tb_used_M,
a.tb_free/1024/1024 tb_free_M,
((b.tb_size - a.tb_free)/b.tb_size)*100 tb_used_rate
from (select t.tablespace_name tb_name, sum(t.bytes) tb_free
from sys.dba_free_space t
group by t.tablespace_name) a,
(select b.tablespace_name tb_name, sum(b.bytes) tb_size
from dba_data_files b
group by b.tablespace_name) b
where a.tb_name = b.tb_name
order by tb_used_rate desc

查看表空间中Table类型和Index类型对象占用的空间大小
select segment_name , sum(bytes) as total, count(*) ext_quan
from dba_extents
where tablespace_name = '&tablespace_name'
and segment_type = 'TABLE' or segment_type = 'INDEX'
group by tablespace_name, segment_name
order by total desc

杀Session
alter system kill session 'sid,serial#'

分析索引的B树Level

execute Dbms_Stats.gather_index_stats('MSTORE','MSG_SEND_IDX1');

select blevel,index_name from user_indexes where index_name='MSG_SEND_IDX1';

一般情况下如果删除了20～25％的记录，需要重建索引降低 B树Level（应该在5以下）

查看当前Scheme下的索引类型

select * from user_indexes t

查看各个表空间的磁盘IO情况

select b.NAME, a.PHYRDS, a.PHYWRTS, a.READTIM, a.WRITETIM
from v$filestat a, v$dbfile b
where a.FILE# = b.FILE#
order by a.READTIM desc

查看各个表空间的extents的数量（一般情况下应小于1024）
select t.tablespace_name,sum(t.extents) from dba_segments t group by t.tablespace_name

查询当前当前Scheme的中最占空间的segment，对应占用空间比较大的Object应该增加其extents的大小 （可以设置tablespace 为 的extents 为 uniform 格式，或者设置 objects 的 stoage 参数），以保证extents的数量不至于太多
select t.segment_name,
t.segment_type,
t.bytes,
t.blocks,
t.extents,
t.bytes / (1024 * 1024) as bytes_M
from user_segments t
order by t.bytes desc

查看数据缓存（db_cache）的命中率，至少应该在95％以上（从90%提高到98％会提高500%的性能）
select physical_reads,
db_block_gets,
consistent_gets,
(1 - physical_reads / (db_block_gets + consistent_gets)) * 100 hitratio
from (select sum(decode(name, 'physical reads', value, 0)) physical_reads,
sum(decode(name, 'db block gets', value, 0)) db_block_gets,
sum(decode(name, 'consistent gets', value, 0)) consistent_gets
from v$sysstat t)

查看
'sga_max_size','pga_aggregate_target','db_cache_size','shared_pool_size'
这个的参数的配置值
select t.name,t.VALUE/1024/1024 M from v$parameter t
where t.name in ('sga_max_size','pga_aggregate_target','db_cache_size','shared_pool_size'
)

查看数据字典缓存的命中率，至少应该在95％以上。如果低于95％增大SHARED_POOL_SIZE参数的值
select (1 - (sum(GETMISSES) / (sum(gets) + sum(GETMISSES)))) * 100 hit_Rate
from v$rowcache t
where t.GETS + t.GETMISSES <> 0;

使用单独的行参数缓存来查看共享池的使用情况（重点分析丢失率在10％的项目，tag 为×）
select t.PARAMETER,
t.GETS,
t.GETMISSES,
t.MODIFICATIONS,
t.FLUSHES,
(getmisses / decode(gets, 0, 1, gets)) "getmiss_ratio%",
(case
when (getmisses / decode(gets, 0, 1, gets)) > 0.1 then
'*'
else
''
end) tag
from v$rowcache t
where t.GETS + t.GETMISSES <> 0
order by "getmiss_ratio%" desc

查看库缓存（LibCache）的重载率（为0）和命中率（接近1） 【否则增大SHARED_POOL_SIZE】
select sum(pins) Hits,
sum(reloads) Misses,
((sum(reloads) / sum(pins)) * 100) "Reload%",
sum(pins) / (sum(pins) + sum(reloads)) * 100 "Hit Ratio%"
from v$librarycache
查看库缓存分项（LibCache）的重载率（低于15％）和命中率（接近1）【否则增大SHARED_POOL_SIZE】
select t.NAMESPACE,
t.PINS,
t.PINHITS,
t.PINHITRATIO "PinHitRatio%",
t.RELOADS / decode(t.PINS, 0, 1, t.PINS) "PinReLoadRatio%"
from v$librarycache t

使用可以内存来判断SHARED_POOL_SIZE是否设置正确（如果运行的时间足够长，且还有大量可用内存则无需增加SHARED_POOL_SIZE）
select to_number(b.VALUE) "Shared Pool Size",
a.BYTES / 1024 / 1024 "Free MB",
(a.BYTES / b.VALUE) * 100 "Percent Free%"
from v$sgastat a, v$parameter b
where a.NAME = 'free memory'
and b.NAME = 'shared_pool_size'
and a.POOL = 'shared pool';

转自：http://gghhgame51333.blog.51cto.com/138362/69123