Oracle DBA常用SQL语句（3）- cache、undo、索引和等待事件

查看数据库 cache 或 keep 了哪些 object

COL table_name FORMAT A16
COL index_name FORMAT A16
SELECT 
table_name AS "Table", 
NULL, 
buffer_pool, 
cache 
FROM user_tables
WHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'
UNION
SELECT 
table_name, 
index_name, 
NULL, 
buffer_pool 
FROM user_indexes
WHERE buffer_pool != 'DEFAULT'
ORDER BY 1, 2 NULLS FIRST;

复制

取消 cache 或 keep(keep pool)

ALTER TABLE XX NOCACHE;
SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'
FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';

--检查 undo
show parameter undo_

复制

检查 undo rollback segment 使用情况

select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b 
where
a.usn=b.usn order by waits desc;
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where 
name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;

复制

计算每秒钟产生的 undoblk 数量

select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;

复制

Undospace=URUPSblocksize + overload(10%), 计算 undo tablespace 大小

show parameter block_size
show parameter undo_retention

复制

计算 undo 表空间大小select undo_retention* 每 秒 产 生 undoblk 数 量 *block_size/1024/1024/1024+(1+1undo_retention* 每秒产生 undoblk 数量*block_size/1024/1024/1024*0.1) from dual;查询 undo 具体信息

COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT undoblks "UndoB", txncount "Trans"
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"
FROM v$undostat;

复制

在内存中排序比率 ( 最优排序 )

SELECT 
'Sorts in Memory ' "Ratio", ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')
/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 5)
||'%' "Percentage"
FROM DUAL;

复制

查看当前系统 undo 使用情况

SELECT DISTINCT STATUS "状态",
COUNT(*) "EXTENT 数量",
SUM(BYTES)  1024  1024  1024 "UNDO 大小"
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;

复制

查看当前系统和 undo 相关的会话

SELECT r.NAME 回滚段名,s.sid SID,s.serial# Serial,
s.username 用户名,s.machine 机器名,
t.start_time 开始时间,t.status 状态,
t.used_ublk 撤消块,USED_UREC 撤消记录,
t.cr_get 一致性取,t.cr_change 一致性变化,
t.log_io "逻辑 I/O",t.phy_io "物理 I/O",
t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) 操作程序
FROM v$session s, v$transaction t, v$rollname r,v$rollstat g
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
AND r.usn = g.usn
ORDER BY t.used_ublk desc;

复制

检查数据库中无效对象

SELECT owner, object_type,count(object_name) FROM dba_objects WHERE status= 'INVALID'group by owner,object_type;

复制

检查是否有禁用约束

SELECT 
owner, 
constraint_name, 
table_name, 
constraint_type, 
status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';

复制

检查是否有禁用 trigger

col owner for a10
col taigger_name for a10
cok table_name for a30
col table_name for a30

SELECT owner, trigger_name, table_name, status 
FROM dba_triggers WHERE status =
'DISABLED';

复制

在某个表下找的索引情况

col column_name for a12
set linesize 180

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
AND user_indexes.table_name='&tb_name'
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;

复制

检查与索引相关的字段

select * from user_ind_columns where index_name=upper('&index_name');

复制

检查索引的唯一性的

col uniq format a10 heading 'Uniqueness' justify c trunc
col indname format a40 heading 'Index Name' justify c trunc
col colname format a25 heading 'Column Name' justify c trunc
break -
on indname skip 1 -
on uniq
select
ind.uniqueness uniq,
ind.owner||'.'||col.index_name indname,
col.column_name colname
from
dba_ind_columns col,
dba_indexes ind
where
ind.owner = upper('&ixowner')
and
ind.table_name = upper('&tabname')
and
col.index_owner = ind.owner
and
col.index_name = ind.index_name
order by
col.index_name,
col.column_position

复制

检查系统中排行前 10 的等待事件

col event for a30
--包括空闲等待事件
select * from (
select 
sid,event,p1,p2,p3,
p1text,WAIT_TIME,SECONDS_IN_WAIT 
from v$session_wait 
where event not like 'SQL%' 
and event not like 'rdbms%' 
order by wait_time desc
) 
where rownum 
<=10;
--不包括空闲等待事件
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT,state from 
v$session_wait where wait_class# <> 6
order by wait_time desc) where rownum <=10;

复制

查看经常被使用而没有 pin 在内存中的对象

形成生成 pin 住共享池中当前没有被 pin 住的对象的 sql 语句。在执行 exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能会报出未定义的错误，需要在 sqlplus 下执行脚本$ORACLE_HOME/rdbms/admin/dbmspool.sql

select 
'exec sys.DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(3
9)||');' as sql_to_run
from V$DB_OBJECT_CACHE 
where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') 
and loads > 50 
and kept='NO' 
and executions > 50;

复制

查看使用了超过 10MB 内存 而没有 pin 的对象

SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC;

复制

查看大的没有被 pin 住的对象

set linesize 150
col sz for a10
col name for a100
col keeped for a6
select 
to_char(sharable_mem  1024,'999999') sz_in_K, 
decode(kept, 'yes','yes ','') keeped,
owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,
null extra, 0 iscur 
from v$db_object_cache v 
where sharable_mem > 1024*1000;

复制

查看大的没有被 pin 住的过程，包和函数

col type for a25
col name for a40
col owner for a25
select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K from 
v$db_object_cache where kept = 'NO'
and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')
group by owner,name,type order by 4;

复制

需要被 pin 入内存中的对象主要有：常用的较大的存储对象，如 standard、diutil 包；编译的常用的 triggers；sequences。

最好在开机时就将其 pin 入内存中。这样，既是使用命令 alter system flush shared_pool 时，也不会讲这些 object flush 掉。具体 pin 对象到内存的方法使用 DBMS_SHARED_POOL.keep存储过程。可以用 unkeep 方法解除其 pin 状态。
db_object_cache 和碎片化，碎片化造成在共享池中虽然有许多小的碎片可以使用，但没有足够大的连续空间，这在共享池中是普遍的现象。消除共享池错误的关键就是即将加载对象的大小是否可能会产生问题。一旦知道了这个存在问题的 PL/SQL，那么就可以在数据库启动时(这时共享池是完全连续的)就将这个代码固定。这将确保在调用大型包时，它已经在共享池里，而不是在共享池中搜索连续的
碎片(在使用系统时，这些碎片可能就不复存在)。可以查询V$DB_OBJECT_CACHE 视图来判断 PL/SQL 是否很大并且还没有被标识为"kept"的标记。今后需要加载这些对象时，可能会产生问题(因为它们的大小和需要占用大量连续的内存)。通过查询 V$DB_OBJECT_CACHE表，可以发现那些没有固定，但由于所需空间太大而很有可能导致潜在问题的对象。

查询一下回滚段的使用情况，其中 USED_UREC 为 undo 记录的使用条目数，USED_UBLK为 undo 块的使用数目

set linesize 180
SELECT 
a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;

复制

查看锁住对象的会话信息，操作系统进程信息

set linesize 180
select 
object_name,machine,s.sid,s.serial#,p.spid
from 
v$locked_object l,dba_objects o ,v$session s,v$process p
where l.object_id=o.object_id 
and l.session_id=s.sid
and s.paddr=p.addr

复制

根据进程查看 sql

select sql_text
from v$sqltext_with_newlines
where (hash_value,address) in (
select sql_hash_value,sql_address 
from v$session 
where sid=(
select 
ses.sid 
from v$session ses,v$process pro
where pro.spid=&spid
and ses.paddr=pro.addr
)) order by address,piece;

复制

查看被锁的表的被锁时间

set linesize 180
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

复制

查看被锁的对象和引起锁的 sql

select a.sid,a.username,d.object_name, b.sql_text
from v$session a,v$sql b, v$locked_object c,dba_objects d
where a.sql_hashvalue=b.hash_value
and a.sid = c.session_id
and d.object_id = c.object_id;

复制

查看锁定的会话信息

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

复制

杀死相关会话

alter system kill session 'sid,serial#';

复制

如果出现 ora-00031 错误，则

alter system kill session 'sid,serial#' immediate；

复制

也可先查询该会话相对应的操作系统进程，在操作系统上进行 kill