oracle数据库监控脚本

最新推荐文章于 2024-03-25 15:37:06 发布

cqwcw86668

最新推荐文章于 2024-03-25 15:37:06 发布

阅读量264

点赞数

文章标签：数据库人工智能 awk

oracle数据库监控脚本
===========================================================
--查看表主键信息
select cu.*
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name
and au.constraint_type = 'P'
--and au.table_name = 表名
--查看数据库运行时间
SELECT TO_CHAR (startup_time, 'DD-MON-YYYY HH24:MI:SS') started_at,
TRUNC (SYSDATE - (startup_time))
|| ' day(s), ' || TRUNC ( 24 * ((SYSDATE - startup_time) -
TRUNC (SYSDATE - startup_time)))
|| ' hour(s), '|| MOD (TRUNC ( 1440 * ( (SYSDATE - startup_time) -
TRUNC (SYSDATE - startup_time))),60)
|| ' minute(s), '|| MOD (TRUNC ( 86400 * ( (SYSDATE - startup_time) -
TRUNC (SYSDATE - startup_time))),60)
|| ' seconds' uptime
FROM v$instance;

--显示数据缓存区中的表占用块及大小
select o.object_name, blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
group by o.object_name, blsiz
order by blocks desc
--测试日志量的视图
CREATE OR REPLACE VIEW redo_size
AS
SELECT VALUE
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.NAME = 'redo size'
--查看当前系统等待事件
select sid,event,p1,p1text from v$session_wait;
--查看前10位严重等待事件
SELECT *
FROM v$system_event
WHERE event NOT IN
('pmon timer', 'rdbms ipc message', 'class slave wait',
'PX Deq: Par Recov Reply', 'PX Deq: Par Recov Execute',
'PX Deq: Par Recov Change Vector', 'smon timer',
'virtual circuit status', 'dispatcher timer', 'jobq slave wait',
'PX Deque wait', 'PX Idle Wait', 'PX Deq: Join ACK',
'SQL*Net message from client', 'PL/SQL lock timer',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait')
ORDER BY time_waited DESC

--查看当前等待事件
SELECT TA.sid ,
TA.seq# ,
TB.Username ,
TB.Terminal ,
TB.Program ,
Decode(TB.Command,0 ,'[ 0] NoCommand',
1 ,'[ 1] CreateTable',
2 ,'[ 2] Insert',
3 ,'[ 3] Select',
6 ,'[ 6] Update',
7 ,'[ 7] Delete',
9 ,'[ 9] CreateIndex',
15,'[15] AlterTable',
21,'[21] CreateView',
23,'[23] ValidateIndex',
35,'[35] AlterDatabase',
39,'[39] CreateTablespace',
41,'[41] DropTablespace',
40,'[40] AlterTablespace',
53,'[53] DropUser',
62,'[62] AnalyzeTable',
63,'[63] AnalyzeIndex',
TB.Command || ':Other') Command,
DECODE(TA.event,'db file scattered read','通表扫描',
'db file sequential read','索引扫描',
'latch free','latch contention',
'free buffer waits','等待DBWR 清除弄脏块',
'log file sync','LGWR写COMMIT或ROLLBACK数据',
'write complete waits','等待DBWR写',
'buffer busy wait','可能是FreeList竞争',
TA.event) Event,
TA.p1text,TA.p1 ,TA.p1raw ,
TA.p2text,TA.p2 ,TA.p2raw ,
TA.p3text,TA.p3 ,TA.p3raw ,
TA.wait_time ,
TA.seconds_in_wait,
TA.state,
TB.sql_address,
TB.sql_hash_value
FROM v$session_wait TA,
v$session TB
WHERE --TB.terminal='FUTURE-MGET' AND
TA.SID = TB.SID AND
TA.event NOT LIKE '% timer' AND
TA.event NOT LIKE 'rdbms ipc message' AND
TA.event NOT LIKE 'SQL*Net %'
--调度任务
SELECT A.TASK_ID as "任务名称",
A.EXEC_START_TIME as "任务开始时间",
A.EXEC_END_TIME as "任务结束时间",
A.EXEC_STATUS as "任务运行状态代码",
B.EXEC_STATUS_DESC as "任务运行状态",
ROUND((A.EXEC_END_TIME - A.EXEC_START_TIME) * 24 * 60) AS "任务运行时间",
A.FAILED_REASON as "任务运行详细状态"
FROM ctrl.task_status A
inner join ctrl.EXEC_STATUS_DEF B ON A.EXEC_STATUS = B.EXEC_STATUS
WHERE A.EXEC_START_TIME > to_date('2008-9-21 13:26:00', 'yyyy-mm-dd hh24:mi:ss')
and instr(upper(task_id), 'DTS') = 0
ORDER BY A.exec_start_time DESC for update --ETL_D_Day ETL_COM_CONFIG_SALEDEPT

DTS_taskproc
--ETL_D_Day ETL_COM_CONFIG_SALEDEPT
delete from task_to_deal where task_id = 'DTS_taskproc';
insert into task_to_deal
(task_id, deal_date, stamp, deal_flag, run_mode)
values
('DTS_taskproc', sysdate + 1 / (24 * 60), sysdate, '0', 'A');
commit;
--重新跑数需要运行ETL_COM_CONFIG_SALEDEPT
delete from task_to_deal where task_id = 'ETL_COM_CONFIG_SALEDEPT';
insert into task_to_deal
(task_id, deal_date, stamp, deal_flag, run_mode)
values
('ETL_COM_CONFIG_SALEDEPT', sysdate + 1 / (24 * 60), sysdate, '0', 'A');
commit;

delete from task_def where task_id in ('ETL_D_Day','ETL_COM_CONFIG_SALEDEPT');

insert into task_def values
('ETL_COM_CONFIG_SALEDEPT','营销服务部增

量','ETL_COM_CONFIG_SALEDEPT','DLL','dd',1,to_date('2008-12-27 00:40:00','YYYY-MM

-DD HH24:MI:SS'),null,null,'C_ETL_Dimen','N','I',1,60);

insert into task_def values
('ETL_D_Day','日期维度更新','ETL_D_Day','DLL','dd',1,to_date('2008-12-27

00:30:00','YYYY-MM-DD HH24:MI:SS'),null,null,'etl_bi_dmd','N','I',1,60);
commit;
--select * from task_status t order by t.exec_start_time desc for update

SELECT * FROM TASK_TO_DEAL for update;
SELECT * FROM ctrl.para_def for update;
SELECT * FROM TASK_STATUS;

select sysdate from dual
--查询用户在表空间占用大小
select * from user_ts_quotas
--查询SGA中缓存的内容(sys执行)
select tch, file#, dbablk, DUMMY
from x$bh, (select dummy from dual)
where bj = (select data_object_id
from dba_objects
where object_name = 'TIME_TEST'
and data_object_id is not null)
--获得整个scheme的ddl语句的脚本
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
--测量当前会话使用的pga和uga内存量
select a.name, to_char(b.value, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%';
--查询pga,uga及临时表空间的使用
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = &sid
and (a.name like '%ga %'
or a.name like '%direct temp%')
--查询硬解析数
select name,value from v$sysstat where name like '%parse%'
--回滚段统计信息
SELECT n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status
FROM v$rollname n,v$rollstat s
WHERE n.usn=s.usn;
--回滚段当前活动事物
SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
FROM v$session s,v$transaction t
WHERE s.saddr=t.ses_addr;
--查看回滚段撤消段信息
select segment_name as seg,tablespace_name as tab from dba_rollback_segs;

--返回由当前事务所使用的重做记录的数量
SELECT USED_UREC FROM V$TRANSACTION;
--查看表中字段的柱状图
select COLUMN_NAME ,ENDPOINT_NUMBER, ENDPOINT_VALUE , ENDPOINT_ACTUAL_VALUE from

dba_histograms t
where table_name = 'F_PAYEVENT' and column_name = 'PAYTYPE';

--查询存储过程中的内容
SELECT distinct name FROM ALL_SOURCE where TYPE='PROCEDURE' AND TEXT LIKE '%减额缴

清%';
--显示ORACLE打的补丁
select comp_name,version from dba_registry;

--下面的SQL显示了在一个oracle9i数据库中，由出现在共享SQL区中语句使用的所有索引
select object_owner, object_name, options, count(*)
from v$sql_plan
where peration='INDEX'
and object_owner!='SYS'
group by object_owner, object_name, operation, options
order by count(*) desc;

--查询用户进程数
select count(*)
from v$session a
left join v$process b on (a.paddr = b.addr)
where a.username = 'NCLTJ'
group by cube(a.paddr)

--查询用户会话数(同一paddr的会话是一个进程)
select a.username, a.sid, a.serial#, a.server, a.paddr,

a.status,a.MACHINE,a.TERMINAL, b.program
from v$session a
left join v$process b on (a.paddr = b.addr)
where a.username = 'NCLTJ'
order by a.MACHINE

alter system kill session '306,9782';

--X$KCVFH ，[K]ernel Layer， [C]ache Layer， Reco[V]ery Component，[F]ile [H]

eaders ，表示存放所有数据文件头。
--但我们在恢复的时候通常只注意其中几个字段。一个是hxfil 文件号, fhrba_seq 当前恢复

需要的redo log sequence.

select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN

CHK,
FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

结果发出来看看

还有看看要恢复的日志是不是在online log里

select * from v$log;

如果第一个查出来的sequence都在第二个查询的sequence里，你就输入online log恢复吧

--当前执行语句
SELECT a.sid ,c.spid,a.SQL_HASH_VALUE,

a.osuser,a.MACHINE,a.TERMINAL,a.PROGRAM,a.LAST_CALL_ET as 运行时间,a.username,

b.sql_text
from v$session a, v$sqltext b,v$process c
where a.sql_address = b.address
and a.paddr=c.addr
and a.STATUS = 'ACTIVE'
order by address, piece;

--根据sid查询unix下的pid
select a.sid,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr;

--计算多池命中率
SELECT name,
1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO"
FROM v$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0;

--查看长时间运行语句
select t.SQL_HASH_VALUE, t.*
from V$SESSION_LONGOPS T
where t.SOFAR <> t.TOTALWORK

----查看谁用临时表空间在干什么
SELECT b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#,
a.username, a.osuser, a.status, c.sql_text
FROM v$session a, v$sort_usage b, v$sql c
WHERE a.saddr = b.session_addr AND a.sql_address = c.address(+)
ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks

--查看哪个用户的活动连接
select 'alter system kill session ''' || t.sid || ',' || t.SERIAL# || ''';',
t.PROGRAM,
t.SQL_HASH_VALUE,
t.OSUSER,
t.MACHINE,
t. *
from v$session t
where t.STATUS = 'ACTIVE'
and t.USERNAME = 'BTAS' -- 146 33847 15939

select 'alter system kill session ''' || t.sid || ',' || t.SERIAL# || ''';',
t.PROGRAM,
t.SQL_HASH_VALUE,
t.OSUSER,
t.MACHINE,
t. *
from v$session t
where t.STATUS = 'ACTIVE'
and t.USERNAME = 'CIRCSTAT' or t.USERNAME = 'NCLTJ' or t.program = 'JDBC Thin

Client'

--查看用户在干什么
select * from v$sqltext t where t.HASH_VALUE = 2656069942 order by t.PIECE

--查看索引名称
select index_name, table_name, column_name
from dba_ind_columns
where lower(table_name) in ('f_payevent')

--查看表空间使用率
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

--查看临时表空间使用率
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
NVL(t.bytes, 0) / 1024 / 1024 || '/' ||
NVL(a.bytes / 1024 / 1024, 0) "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %",
t.bytes
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'

--锁类型检查
用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发

生
等待的锁，有可能的话，杀掉该进程。
　　
　这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生

了两个锁，
一个是表锁，一个是行锁。
　　
　可以通过《删除回话及进程》来杀掉会话
　　
SELECT /*+ rule */
S.USERNAME,
DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
Decode(L.LMode,0,'[0] none',
1,'[1] null 空',
2,'[2] Row-S 行共享(RS)：共享表锁，sub share ',
3,'[3] Row-X 行独占(RX)：用于行的修改，sub exclusive ',
4,'[4] Share 共享锁(S)：阻止其他DML操作，share',
5,'[5] S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive ',
6,'[6] exclusive 独占(X)：独立访问使用，exclusive ',
'['||L.LMode||'] Other Lock') LockMode,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
S.SID,
S.SERIAL#,
S.TERMINAL,
S.MACHINE,
S.PROGRAM,
S.OSUSER,
S.sql_address,
S.sql_hash_value
FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL

--查看回滚段状态
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
from v$rollstat order by rssize;

--查看一下SQL到底在做些什么事情，查询SQL如下所示：

select sql_text,spid,b.program,process
from v$sqlarea a,
v$session b,
v$process d
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and b.paddr = d.addr
and d.spid in (2173);

--杀进程语句
select 'exec dbms_stats.gather_table_stats(ownname => user,tabname => ''' ||
T.table_name ||
''', estimate_percent => 1,method_opt => ''for all indexed columns size

skewonly'', cascade => true, degree => 2);'
,t.*
from user_tables t
where (t.last_analyzed is null or
t.last_analyzed < to_date('20060808', 'yyyymmdd'))

--查看表所占大小
Select Segment_Name, Sum(bytes) / 1024 / 1024 / 1024
From User_Extents
--where tablespace_name = 'NCLTJ_ODS'
Group By Segment_Name

--查看内存相关
select sum(value) from v$sesstat s, v$statname n, v$session
where s.statistic# = n.statistic# and v$session.sid = s.sid and
name like '%memory'and value > 0 ;

SELECT username,sess.sid,sum(trunc((value/1024))) MEM_USED
FROM v$session sess, v$sesstat stat, v$statname name
WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name in ('session uga memory' ,'session pga memory')
group by username,sess.sid
order by MEM_USED

select spid,username,program,sum(PGA_USED_MEM) ,sum(pga_alloc_mem),
sum(pga_freeable_mem),sum(pga_max_mem)
from v$process
group by spid,username,program
having sum(PGA_USED_MEM)>10000000 --- > 10M
order by 6

select username, server, sum(value), name from v$sesstat s, v$statname n,

v$session
where s.statistic# = n.statistic# and v$session.sid = s.sid and
name like '%memory'and value > 0
group by username, server, name;

SELECT username users,
sql_text,
executions,
parse_calls,
sharable_mem,
persistent_mem
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
ORDER BY 5;

-- 数据缓冲区高速缓存
SELECT physical_reads, db_block_gets, consistent_gets, NAME,
100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets -

physical_reads))) "Data Buffer Hit Ratio"
FROM v$buffer_pool_statistics;

select round(100*(1-(a.value-b.value-nvl(c.value,0))/d.value),2) "Buffer Hit(%)"
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'

-- 重做日至缓冲区
SELECT a.VALUE redo_entries, b.VALUE redo_buffer_allocation_retries,
ROUND ((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'redo entries' AND b.NAME = 'redo buffer allocation retries';

-- 数据字典高速缓存
SELECT SUM (pinhits) / SUM (pins) * 100 "hit radio"
FROM v$librarycache;

-- 库高速缓存
SELECT TO_CHAR (ROUND ((1 - SUM (getmisses) / SUM (gets)) * 100, 1)) || '%'

"Dictionary Cache Hit Ratio"
FROM v$rowcache;

-- 排序
SELECT a.VALUE disk_sort, b.VALUE memory_sort, ROUND ((1 - a.VALUE / (a.VALUE +

b.VALUE)) * 100, 4) sort_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'sorts (disk)' AND b.NAME = 'sorts (memory)';

--查看连接等待事件
SELECT Sid || ' ' || Event || ' ' || Total_Waits || ' ' || Average_Wait
FROM V$session_Event
WHERE Sid = 128

每个用户命中率(命中率应该超过90%)
SELECT TA.Sid "连接ID",
username "用户名",
consistent_gets "读一致性",
block_gets "缓冲区读",
physical_reads "物理读",
100*(consistent_gets+block_gets-physical_reads)/(consistent_gets+block_gets)

hiratio,
TA.sql_address,
TA.sql_hash_value
FROM v$session TA,
v$sess_io TB
WHERE TA.sid=TB.sid
AND (consistent_gets+block_gets)>0
AND username IS NOT NULL
ORDER BY 6 ASC;

--查询耗CPU资源的SQL
SELECT SS.SID,
Decode(SE.Command,0 ,'[ 0] NoCommand',
1 ,'[ 1] CreateTable',
2 ,'[ 2] Insert',
3 ,'[ 3] Select',
6 ,'[ 6] Update',
7 ,'[ 7] Delete',
9 ,'[ 9] CreateIndex',
15,'[15] AlterTable',
21,'[21] CreateView',
23,'[23] ValidateIndex',
35,'[35] AlterDatabase',
39,'[39] CreateTablespace',
41,'[41] DropTablespace',
40,'[40] AlterTablespace',
53,'[53] DropUser',
62,'[62] AnalyzeTable',
63,'[63] AnalyzeIndex',
SE.Command || ':Other'),
SS.VALUE CPU,
SE.USERNAME,
SE.PROGRAM,
SE.sql_address,
SE.sql_hash_value
FROM V$SESSTAT SS, V$SESSION SE
WHERE SS.STATISTIC# IN
(SELECT STATISTIC#
FROM V$STATNAME
WHERE NAME = 'CPU used by this session')
AND SE.SID = SS.SID
AND SS.SID > 6
ORDER BY SS.SID;

v$session中的列部分说明
0 - WAITING (当前等待的 Session)
-2 - WAITED UNKNOWN TIME (最后等待持续时间未知)
-1 - WAITED SHORT TIME (最后的等待 <1/100 秒)
>0 - WAITED KNOWN TIME (WAIT_TIME = 最后等待持续时间)

STATUS VARCHAR2(8) Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - sql及其session没有释放或正常退出......
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - Session inactive, waiting on the client

--根据上面的地址找对应的SQL
SELECT *
FROM v$sqltext
WHERE address=HexToRaw('0700000036D20268')
AND hash_value=2348072240
ORDER BY address,hash_value,piece;
--或者
SELECT *
FROM v$sqlarea
WHERE address=HexToRaw('0700000036D20268')
AND hash_value=2348072240
ORDER BY address,hash_value;

HexToRaw('0700000036D20268')和hash_value=2348072240
对应的是v$session中的sql_address,sql_hash_value,
这两个值分别在楼上的SQL中有取出

--根据ID找SQL
SELECT tb.command_type,
Tb.Piece,
Tb.Sql_Text
FROM V$open_Cursor Ta,
V$sqltext Tb
WHERE Ta.Sid = 137
AND Ta.Address = Tb.Address
AND Ta.Hash_Value = Tb.Hash_Value
ORDER BY Tb.Address,
Tb.Hash_Value,
Tb.Piece ASC;

--寻找没有使用绑定变量的sql语句
SELECT Plan_Hash_Value,
COUNT(*)
FROM V$sql
WHERE Plan_Hash_Value <> 0
GROUP BY Plan_Hash_Value
ORDER BY 2 DESC;

--查询正打开的游标
SELECT User_Name,
Sql_Text 　　
FROM V$open_Cursor 　　
WHERE Sid IN (SELECT Sid
FROM (SELECT Sid,
Serial#,
Username,
Program 　　
FROM V$session 　　
WHERE Status = 'ACTIVE'))

--子查询
SELECT Sid,
Serial#,
Username,
Program 　　
FROM V$session 　　
WHERE Status = 'ACTIVE'
查出的是不活动的session的sid

--锁表检查
锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待以下的语句可以
查询到谁锁了表，而谁在等待。
SELECT /*+ rule */
LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
--DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
Decode(L.Locked_Mode,0,'[0] none',
1,'[1] null 空',
2,'[2] Row-S 行共享(RS)：共享表锁，sub share ',
3,'[3] Row-X 行独占(RX)：用于行的修改，sub exclusive ',
4,'[4] Share 共享锁(S)：阻止其他DML操作，share',
5,'[5] S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive ',
6,'[6] exclusive 独占(X)：独立访问使用，exclusive ',
'['||L.Locked_Mode||'] Other Lock') LockMode,
S.SID,
S.SERIAL#,
S.sql_address,
S.sql_hash_value
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
ORDER BY O.OBJECT_ID, XIDUSN DESC;

--当前用户排序情况
SELECT s.sid "连接ID",
machine "对方机器",username "系统用户",
program "应用程序",osuser "登陆系统用户",
sd.VALUE "磁盘排序",sm.VALUE "内存排序",
sr.VALUE "排序行数",
Round(sm.VALUE/Decode(Sign(sm.VALUE+sr.VALUE),0,NULL,(sm.VALUE+sr.VALUE)*100),4) "

排序效率(%)",
s.sql_address,
s.sql_hash_value
FROM v$session s,
v$sesstat sd,
v$sesstat sm,
v$sesstat sr
WHERE s.sid = sd.sid AND
s.sid = sm.sid AND
s.sid = sr.sid AND
sd.statistic# = 101 AND
sm.statistic# = 100 AND
sr.statistic# = 102 AND
s.TYPE != 'BACKGROUND'
ORDER BY 6 Desc;

--检查文件物理IO读写
SELECT name,phyrds,phywrts
FROM v$datafile TA,
v$filestat TB
WHERE TA.file#=TB.File#
将读写比较多的文件放到速度比较快的硬盘上
或者将集中读写的文件分布到不同的硬盘上

优化日志缓存
日志高速缓存：
=================================================================================
SELECT Latch "Latch",
Gets "成功",
misses "失败",
immediate_gets "立即成功",
immediate_misses "立即失败",
IGetsHitRatio "IGets Hit Ratio (%)",
GetsHitRatio "Gets Hit Ratio (%)",
DECODE(Sign(GetsHitRatio-99),1,'正常','请增加log_buffer') "争用",
DECODE(Sign(GetsHitRatio-99),1,'正常','请增加log_buffer') "立即争用"
FROM (SELECT Substr(name,1,20) Latch,
gets Gets,
misses misses,
immediate_gets immediate_gets,
immediate_misses immediate_misses,
Round(100*(1-immediate_misses/Decode

(immediate_misses+immediate_gets,0,NULL,immediate_misses+immediate_gets)),2)

IGetsHitRatio,
Round(100*(1-misses/Decode(misses+gets,0,NULL,misses+gets)),2) GetsHitRatio
FROM v$Latch
WHERE name like '%redo%')
如果失败超过成功的1%，就可能出现Redo allocation和/或Redo Copy Latch争用，减少或消

除
此争用是内存调整进程的一部分。

--查看日志缓冲区大小【三个值可能不一样V$parameter是初始化值】
SELECT Substr(NAME, 1, 10) NAME,
Substr(VALUE, 1, 10) VALUE
FROM V$parameter
WHERE NAME = 'log_buffer';

SELECT * FROM v$sgastat WHERE pool IS NULL

SELECT * FROM v$sga;

--统计数据文件IO
select d.tablespace_name TABLESPACE, d.file_name, f.phyrds, f.phyblkrd,
f.readtim, f.phywrts, f.phyblkwrt, f.writetim
from v$filestat f, dba_data_files d
where f.file# = d.file_id
order by tablespace_name, file_name;

--磁盘排序与内存排序的比值应小于 5%.
如果比值大于5%,增大SORT_AREA_SIZE.
select disk.value "Disk", mem.value "Mem",
(disk.value/mem.value)*100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';

数据库监控脚本（二）
Oracle — 作者 Basileus1999 @ 14:22
--1、查找trace文件
--2、session下的重做数量
--3、估算自数据库启动以来每天的平均日志量
--4、估算日志数量
--5、查找隐含参数
--6、创建session的重做日志视图
--7、一致性读取的段及数据块信息
--8、等待事件分类及数量
--9、根据sid找到相应的sql语句
--10、系统自启动以来的累计等待时间前十名
--11、查找全表扫描(full scan)及快速全索引扫描(fast full index)
--12、通过具体的等待事件查找到有问题的sql语句(输入参数等待事件如：free buffer

waits)
--13、查找数据库最繁忙的buffer
--14、查找热点buffer来自哪些对象
--15、关于latch信息
--16、具体热点块的latch及buffer信息及找到相应对象的sql语句
--17、创建临时表保存X$KSMSP的状态
--18、找出library cache pin等待的原因
--19、获得参数的描述信息
--20、oracle收集的buffer cache及shared pool 的建议信息
--21、是10g中，决定各参数组件大小的查询
--22、10g各动态组件调整时间及调整类型
--23、sql在工作区中工作方式所占比例
--24、pga动态性能视图信息
--25、获得存在问题的sql，根据pid
--26、fast_start_mttr_target
--27、实例恢复的时间计算
--28、show_space过程及使用
--29、分析表
--30、unix环境快速shutdown数据库之前先删除各个进程

--1、查找trace文件
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_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 t.INSTANCE
FROM SYS.v$thread t, SYS.v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM SYS.v$parameter
WHERE NAME = 'user_dump_dest') d
/

--2、session下的重做数量
col name for a30
select a.name,b.value
from v$statname a,v$mystat b
where a.STATISTIC# = b.STATISTIC# and a.name = 'redo size';

--3、估算自数据库启动以来每天的平均日志量
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select startup_time from v$instance;

select (select value/1024/1024/1024 from v$sysstat where name='redo size')/
(select round(sysdate - ( select startup_time from v$instance)) from dual)

REDO_GB_PER_DAY
from dual;

--4、估算日志数量
--一段时间的
SELECT NAME, completion_time, blocks * block_size / 1024 / 1024 mb
FROM v$archived_log
WHERE ROWNUM < 11
AND completion_time BETWEEN TRUNC (SYSDATE) - 2 AND TRUNC (SYSDATE) - 1
/
--每日全天的
SELECT TRUNC (completion_time), SUM (mb) / 1024 day_gb
FROM (SELECT NAME, completion_time, blocks * block_size / 1024 / 1024 mb
FROM v$archived_log
WHERE completion_time BETWEEN TRUNC (SYSDATE) - 2 AND TRUNC (SYSDATE) - 1)
GROUP BY TRUNC (completion_time)
/
--最近日期的日志生成统计
SELECT TRUNC (completion_time), SUM (mb) / 1024 day_gb
FROM (SELECT NAME, completion_time, blocks * block_size / 1024 / 1024 mb
FROM v$archived_log)
GROUP BY TRUNC (completion_time)
order by 1
/

--5、查找隐含参数
set linesize 132
column name format a30
column value format a25
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ')
/

--6、创建session的重做日志视图
CREATE OR REPLACE VIEW redo_size
AS
SELECT VALUE
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.NAME = 'redo size'
/

--7、一致性读取的段及数据块信息
select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state
from x$bh a,dba_extents b
where b.RELATIVE_FNO = a.dbarfil
and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk
and b.owner='SCOTT' and b.segment_name='EMP'
/

--8、等待事件分类及数量
SELECT wait_class#, wait_class_id, wait_class, COUNT (*) AS "count"
FROM v$event_name
GROUP BY wait_class#, wait_class_id, wait_class
ORDER BY wait_class#
/

--9、根据sid找到相应的sql语句
SELECT sql_text
FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value
FROM v$session b
WHERE b.SID = '&sid')
ORDER BY piece ASC
/

--10、系统自启动以来的累计等待时间前十名
SELECT *
FROM (SELECT event, time_waited
FROM v$system_event
ORDER BY time_waited DESC)
WHERE ROWNUM < 10;

--11、查找全表扫描(full scan)及快速全索引扫描(fast full index)
SELECT sql_text
FROM v$sqltext t, v$sql_plan p
WHERE t.hash_value = p.hash_value
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
ORDER BY p.hash_value, t.piece;

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;

--12、通过具体的等待事件查找到有问题的sql语句(输入参数等待事件如：free buffer

waits)
SET linesize 120
COL operation format a55
COL cost format 99999
COL kbytes format 999999
COL object format a25
SELECT hash_value, child_number,
LPAD (' ', 2 * DEPTH)
|| operation
|| ' '
|| options
|| DECODE (ID,
0, SUBSTR (optimizer, 1, 6) || ' Cost=' || TO_CHAR (COST)
) operation,
object_name OBJECT, COST, ROUND (BYTES / 1024) kbytes
FROM v$sql_plan
WHERE hash_value IN (
SELECT a.sql_hash_value
FROM v$session a, v$session_wait b
WHERE a.SID = b.SID
AND b.event = '&waitevent')
ORDER BY hash_value, child_number, ID;

--13、查找数据库最繁忙的buffer
SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11;

--14、查找热点buffer来自哪些对象
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk;

--15、关于latch信息
--主要latch free信息
select * from
(select * from v$latch order by misses desc)
where rownum<11;
--获得session的等待信息
select sid,seq#,event from v$session_wait
--获得具体的子latch信息
SELECT *
FROM (SELECT addr, child#, gets, misses, sleeps, immediate_gets igets,
immediate_misses imiss, spin_gets sgets
FROM v$latch_children
WHERE NAME = 'cache buffers chains'
ORDER BY sleeps DESC)
WHERE ROWNUM < 11;

--16、具体热点块的latch及buffer信息及找到相应对象的sql语句
SELECT b.addr, a.ts#, a.dbarfil, a.dbablk, a.tch, b.gets, b.misses, b.sleeps
FROM (SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch, hladdr
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) a,
(SELECT addr, gets, misses, sleeps
FROM v$latch_children
WHERE NAME = 'cache buffers chains') b
WHERE a.hladdr = b.addr
/

SELECT distinct e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk;
--找引起竞争的相应sql语句
break on hash_value skip 1
SELECT /*+ rule */ hash_value,sql_text
FROM v$sqltext
WHERE (hash_value, address) IN (
SELECT a.hash_value, a.address
FROM v$sqltext a,
(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.dbablk) b
WHERE a.sql_text LIKE '%' || b.segment_name || '%'
AND b.segment_type = 'TABLE')
ORDER BY hash_value, address, piece
/

--17、创建临时表保存X$KSMSP的状态
CREATE GLOBAL TEMPORARY TABLE e$ksmsp ON COMMIT PRESERVE ROWS AS
SELECT a.ksmchcom,
SUM (a.CHUNK) CHUNK,
SUM (a.recr) recr,
SUM (a.freeabl) freeabl,
SUM (a.SUM) SUM
FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
SUM (ksmchsiz) SUM
FROM x$ksmsp GROUP BY ksmchcom, ksmchcls) a
where 1 = 0
GROUP BY a.ksmchcom;

--使用创建的临时表保存当前的shared pool的状态
INSERT INTO E$KSMSP
SELECT a.ksmchcom,
SUM (a.CHUNK) CHUNK,
SUM (a.recr) recr,
SUM (a.freeabl) freeabl,
SUM (a.SUM) SUM
FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
SUM (ksmchsiz) SUM
FROM x$ksmsp
GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom
/

--比较前后shared pool内存分配的变化
select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk - b.chunk) c_diff,(a.sum -

b.sum) s_diff
from
(SELECT a.ksmchcom,
SUM (a.CHUNK) CHUNK,
SUM (a.recr) recr,
SUM (a.freeabl) freeabl,
SUM (a.SUM) SUM
FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
SUM (ksmchsiz) SUM
FROM x$ksmsp
GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom) a,e$ksmsp b
where a.ksmchcom = b.ksmchcom and (a.chunk - b.chunk) <>0
/

--18、找出library cache pin等待的原因
--获得library cache pin等待的对象
select p1raw from v$session_wait where event like 'library%'; --获得等待handle的地

址

--通过上面获得的地址得到等待的对象
col KGLNAOWN for a10
col KGLNAOBJ for a20
select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='&adr'
/

--获得持有等待对象的session信息，其中&hdl=(上面获得的handle的地址)
select

a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '&hdl' and b.KGLPNMOD<>0
/

--上面的语句可以综合到下面的语句具体实现
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')
/

SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
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%')
/

--根据sid信息取得sql信息
SELECT sql_text
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%')))
/

--19、获得参数的描述信息
set linesize 120
col name for a30
col value for a20
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%&par%'
/

--20、oracle收集的buffer cache及shared pool 的建议信息
select id,name,block_size,size_for_estimate sfe,size_factor sf,
estd_physical_read_factor eprf,estd_physical_reads epr
from v$db_cache_advice;

select SHARED_POOL_SIZE_FOR_ESTIMATE SPSFE,SHARED_POOL_SIZE_FACTOR SPSF,
ESTD_LC_SIZE,ESTD_LC_MEMORY_OBJECTS ELMO,ESTD_LC_TIME_SAVED ELTS,
ESTD_LC_TIME_SAVED_FACTOR ELTSF,ESTD_LC_MEMORY_OBJECT_HITS ELMOH
from v$shared_pool_advice;

--21、是10g中，决定各参数组件大小的查询
col name for a30
col value for a30
col describ for a50

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm like '%pool_size%'
/

--22、10g各动态组件调整时间及调整类型
col component for a30
select COMPONENT,CURRENT_SIZE,MIN_SIZE,LAST_OPER_TYPE,LAST_OPER_MODE,to_char

(LAST_OPER_TIME,'yyyy-mm-dd hh24:mi:ss') LOT
from v$sga_dynamic_components;

--23、sql在工作区中工作方式所占比例
col value for 999999999999
SELECT NAME, VALUE,
100
* ( VALUE
/ DECODE ((SELECT SUM (VALUE) FROM v$sysstat
WHERE NAME LIKE 'workarea executions%'),
0, NULL,
(SELECT SUM (VALUE) FROM v$sysstat
WHERE NAME LIKE 'workarea executions%')
)
) pct
FROM v$sysstat
WHERE NAME LIKE 'workarea executions%'
/

--24、pga动态性能视图信息
SELECT pga_target_factor factor, low_optimal_size / 1024 low,
ROUND (high_optimal_size / 1024) high,
estd_optimal_executions estd_opt, estd_onepass_executions estd_op,
estd_multipasses_executions estd_mp, estd_total_executions estd_exec
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 0.25 AND estd_total_executions > 0
/

--25、获得存在问题的sql，根据pid
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
/

--26、fast_start_mttr_target
select MTTR_TARGET_FOR_ESTIMATE MttrEst,
ADVICE_STATUS AD,
DIRTY_LIMIT DL,
ESTD_CACHE_WRITES ESTCW,
ESTD_CACHE_WRITE_FACTOR EstCWF,ESTD_TOTAL_WRITES ESTW,
ESTD_TOTAL_WRITE_FACTOR ETWF,ESTD_TOTAL_IOS ETIO
from v$mttr_target_advice;
/

--27、实例恢复的时间计算
select RECOVERY_ESTIMATED_IOS REIO,
ACTUAL_REDO_BLKS ARB,
TARGET_REDO_BLKS TRB,
LOG_FILE_SIZE_REDO_BLKS LFSRB,
LOG_CHKPT_TIMEOUT_REDO_BLKS LCTRB,
LOG_CHKPT_INTERVAL_REDO_BLKS LCIRB,
FAST_START_IO_TARGET_REDO_BLKS FSIOTRB,
TARGET_MTTR TMTTR,
ESTIMATED_MTTR EMTTR,
CKPT_BLOCK_WRITES CBW
from v$instance_recovery;

--而在10g中则为，其中writes_autotrne字段指由于自动调整检查点执行的写出次数，而

ckpt_block_writes指检查点写出的block数量
SELECT recovery_estimated_ios reios, target_mttr tmttr, estimated_mttr emttr,
writes_mttr wmttr, writes_other_settings woset,
ckpt_block_writes ckptbw, writes_autotune wauto,
writes_full_thread_ckpt wftckpt
FROM v$instance_recovery;

--28、show_space过程及使用
--普通版本（不适应assm）
create or replace procedure show_space ( p_segname in varchar2,
p_owner in varchar2 default user, p_type in varchar2 default 'TABLE' )
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number ) is
begin
dbms_output.put_line( rpad(p_label,40,'.') || p_num );
end;
begin
dbms_space.free_blocks ( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
dbms_space.unused_space ( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;

--完整版本
create or replace procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE' ,
p_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);

l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;

l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;

procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;

if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;

if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;

if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;

dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );

p( 'Free Blocks', l_free_blks );
end if;

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );

/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);

end if;

end;

ASSM 类型的表

SQL> exec show_space('t','auto');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................78
Unused Bytes............................638976
Last Used Ext FileId....................9
Last Used Ext BlockId...................25608
Last Used Block.........................50

PL/SQL procedure successfully completed.

ASSM 类型的索引

SQL> exec show_space('t_index','auto','i');
Total Blocks............................80
Total Bytes.............................655360
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................9
Last Used Ext BlockId...................25312
Last Used Block.........................3

PL/SQL procedure successfully completed.

对analyze 过的segment 可以这样

SQL> exec show_space('t','auto','T','Y');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................78
Unused Bytes............................638976
Last Used Ext FileId....................9
Last Used Ext BlockId...................25608
Last Used Block.........................50
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................418
Total bytes.............................3424256

PL/SQL procedure successfully completed.

摘自：关于SHOW_SPACE()工具的用法

--29、分析表
analyze table table_name compute statistics for table for all indexes for all

indexed columns;

Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
等等。
如果想分析整个用户或数据库，还可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)
如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语

句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息

--30、unix环境快速shutdown数据库之前先删除各个进程
$ ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill
然后你再shutdown immediate就很快的了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25136010/viewspace-683030/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25136010/viewspace-683030/

cqwcw86668

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle数据库监控脚本

oracle数据库监控脚本 =========================================================== --查看表主键信息select cu.*from user_cons_colum...
复制链接

扫一扫