裘宏駿 現供職務于浙江縱橫通信服務集團，主持技術工作。負責浙江移動繳費券、空中充值、外呼外包等項目的技術工作。擅長大業務量業務支撐系統的設計、開發與后期運營，有3年以上的項目管理經驗、4年以上的Oracle 設計開發與優化經驗、5年以上的J2EE項目開發經驗、熟悉C++，Java，Perl，PL/SQL，UNIX Shell，熟悉WebLogic，Borland VisiBorker 中間件、熟悉Spring、Hibernate、iBatis、JSF等開發框架，對移動通信系統有較系統和全面的認識。聯系方式：MSN QiuHj1978@hotmail.com
http://dev2dev.bea.com.cn/blog/QiuHj/200710/23_524.html

查詢最耗時的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';

總結

以上是生活随笔為你收集整理的常用DBA SQL[转]的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。