oracle 常用查询语句

柔情只为你懂 2023-06-03 03:59 34阅读 0赞

一、一般日常用的脚本

1、检查源库每个节点至少3组redo  
select group\#,thread\#,bytes/1024/1024,members,status from v$log;

select group\#,thread\#,sequence\#,BYTES/1024/1024,archived,used,status from v$standby\_log;

\#重启完后，可通过下面语句查看修改地方：  
set lines 500 pages 0  
col value for a90  
col name for a50  
select name,value  
from v$parameter  
where name in('db\_name','db\_unique\_name',  
'log\_archive\_config',  
'log\_archive\_dest\_1','log\_archive\_dest\_2',  
'log\_archive\_dest\_1',  
'log\_archive\_dest\_2',  
'remote\_login\_passwordfile',  
'log\_archive\_format',  
'log\_archive\_max\_processes',  
'fal\_server',  
'db\_file\_name\_convert',  
'log\_file\_name\_convert',  
'standby\_file\_management')  
/

表空间总容量和使用的表空间：  
select  
(select sum(bytes)/1024/1024/1024/1024 bytes from dba\_data\_files) total\_TB,  
(select sum(bytes)/1024/1024/1024/1024 from dba\_segments ) use\_TB,  
(select sum(bytes)/1024/1024/1024 from dba\_segments where SEGMENT\_TYPE='TABLE') table\_G,  
(select sum(bytes)/1024/1024/1024 from dba\_segments where SEGMENT\_TYPE='INDEX') index\_G  
from dual;

SMART\_DT2

收缩表空间：  
alter tablespace name shrink space 200; --收缩到200M。

alter tablespace name shrink space; 不加keep 收缩到最小。

alter tablespace name shrink datafile '路径';

由于以上shrink不能shrink 本地管理的表空间，只能shrink临时表空间，所以用resize;

SELECT 'alter database datafile ' || D.FILE\_ID || ' resize 1G;'  
FROM DBA\_DATA\_FILES D  
WHERE D.TABLESPACE\_NAME = 'SMART\_NSNRP\_2017';

临时表空间：  
set lin 140  
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)",  
TO\_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')  
|| '/'  
|| TO\_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') "Used (M)",  
TO\_CHAR (NVL (t.BYTES / a.BYTES \* 100, 0), '990.00') "Used %"  
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';

查看表使用的表空间：

select distinct tablespace\_name from user\_tab\_partitions where table\_name='S\_T\_RTNRP\_STATUS';

resize 重置数据文件大小：

set lin 200 pages 2000  
select ' alter database datafile '''||file\_name ||''' resize 200m;' from dba\_data\_files where tablespace\_name  
in (  
'SMART\_NRRPSTA08',  
'SMART\_NRRPSTA09',  
'SMART\_NRRPSTA10',  
'SMART\_NRRPSTA05',  
'SMART\_NRRPSTA11',  
'SMART\_NRRPSTA12',  
'SMART\_NRRPSTA07',  
'SMART\_NRRPSTA06',  
'SMART\_NRRPSTA04',  
'SMART\_NRRPSTA02',  
'SMART\_NRRPSTA03',  
'SMART\_NRRPSTA01'  
);  
  
查看分区的个数以及每个分区记录数：

select t.table\_name, t.partition\_name, t.num\_rows, t.last\_analyzed  
from user\_tab\_partitions t  
where t.table\_name = 'S\_T\_REPORT\_PUSH';

如果统计信息不准确：

\--做一次表分析  
exec dbms\_stats.gather\_table\_stats(ownname=>'PINTEST',tabname=>'BILLLOG\_T',granularity=>'ALL');

create table S\_OPERATELOG\_TONGJI\_62TEST tablespace SMART\_DT2 as select \* from S\_OPERATELOG\_TONGJI where 1=0;

set lin 200 pages 1000(这个有问题)  
select a.tablespace\_name,round(sum(a.bytes/1024/1024/1024),2) total\_G,  
round(sum(a.maxbytes/1024/1024/1024),2) max\_extended\_G,  
round(sum(b.bytes/1024/1024/1024)) free\_G,  
round((sum(a.bytes/1024/1024/1024)-sum(b.bytes/1024/1024/1024)),2) used\_G,  
round(sum((a.maxbytes-a.bytes)/1024/1024/1024),2) need\_extend\_G  
from dba\_data\_files a,dba\_free\_space b  
where a.TABLESPACE\_NAME=b.TABLESPACE\_NAME  
group by a.tablespace\_name order by 5 desc ;

查看表空间最大扩展情况：

set lin 200 pages 1000  
select tablespace\_name,round(sum(bytes/1024/1024/1024),2) total\_G,  
round(sum(maxbytes/1024/1024/1024),2) max\_extended\_G,  
round(sum((maxbytes-bytes)/1024/1024/1024),2) need\_extend\_G,  
round(sum(bytes)/sum(maxbytes),4)\*100 "MAXRate"  
from dba\_data\_files group by tablespace\_name order by 5 desc ;

62,61热盘的排序：  
select tablespace\_name,  
ROUND(sum(bytes / 1024 / 1024 / 1024),2) G  
from dba\_data\_files  
where file\_name like '+FDATADG%'  
GROUP BY TABLESPACE\_NAME ORDER BY 2 DESC;

查看表空间所包含的对象：

select owner,TABLE\_NAME from dba\_tables name where tablespace\_name='&name'  
union  
select owner,index\_name from dba\_indexes where tablespace\_name='&name'  
union  
select table\_owner,table\_name from dba\_tab\_subpartitions where tablespace\_name='&name'  
union  
select table\_owner,table\_name from dba\_tab\_partitions where tablespace\_name='&name'

分区大小：

select round(sum(bytes / 1024 / 1024 / 1024), 2) G  
from dba\_segments  
where segment\_name = 'S\_OPERATELOG\_2018' and partition\_name like 'OPLOG\_1811%'

查看ASM磁盘使用情况

select GROUP\_NUMBER,NAME,STATE,TYPE,TOTAL\_MB/1024 TOTAL\_G,FREE\_MB/1024 FREE\_G,  
ROUND((TOTAL\_MB-FREE\_MB)/TOTAL\_MB,4)\*100 "RATE%" from v$asm\_diskgroup;

查看数据文件：  
select file\_name,bytes/1024/1024/1024 total\_G,tablespace\_name,maxbytes/1024/1024/1024,AUTOEXTENSIBLE from dba\_data\_files where tablespace\_name='';

col tablespace\_name for a30  
col file\_name for a60  
set lin 140  
set pagesize 50  
with  
D as (SELECT TABLESPACE\_NAME ,  
ROUND((SUM(BYTES)/(1024\*1024)),2) SPACE,  
SUM(BLOCKS) BLOCKS  
FROM DBA\_DATA\_FILES  
GROUP BY TABLESPACE\_NAME),  
F as (SELECT TABLESPACE\_NAME,ROUND(SUM(BYTES)/(1024\*1024),2)  
FREE\_SPACE FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME),  
T as (select distinct TABLESPACE\_NAME,AUTOEXTENSIBLE from dba\_data\_files)  
SELECT D.TABLESPACE\_NAME,  
SPACE "SUM\_SPACE(M)" ,  
SPACE-NVL(FREE\_SPACE,0) "USED\_SPACE(M)",  
FREE\_SPACE "FREE\_SPACE(M)" ,  
ROUND((1-NVL(FREE\_SPACE,0)/SPACE) \*100,2) "USED\_RATE(%)",t.AUTOEXTENSIBLE  
from D,F,T  
WHERE T.TABLESPACE\_NAME(+)=D.TABLESPACE\_NAME AND D.TABLESPACE\_NAME=F.TABLESPACE\_NAME(+)  
ORDER BY "USED\_RATE(%)";

查看会话：

set lin 200 pages 100  
col s.machine for a20  
col substr(p.program, 1, 40) for a30  
select s.machine,  
substr(s.program, 1, 40) prog,  
COUNT(\*)  
from v$session s, v$process p where s.username is not null  
AND s.paddr = p.addr  
GROUP BY s.machine,substr(s.program, 1, 40) ORDER BY 3 DESC;

查看SQL:

netstat -nuptl |grep 32756

查看sql语句：  
select \* from v$sqlarea c  
where c.HASH\_VALUE =(  
select d.PREV\_HASH\_VALUE from v$session d  
where d.sid='&sid'  
);

查看等待事件（enquence）：

col event for a30  
col username for a15  
set lin 140  
select sid,serial\#,username, event, sql\_id, last\_call\_et as "times(s)",p1, p2, p3,program  
from v$session  
where status ='ACTIVE' and username is not null  
order by event,last\_call\_et desc;

ps -ef|grep pmon|grep -v grep|awk '\{print "kill -9 "$1\}'  
杀进程  
查看进程：  
ps -ef|grep pmon|grep -v grep|awk '\{print "kill -9 "$1,$2\}'

查看生成的trac 文件；  
SQL> ORADEBUG setmypid

分开窗口执行  
SQL> ORADEBUG hanganalyze

利用trace文件，查看sql\_id;

select sid,serial\#,paddr,username,status,sql\_id from v$session where sid=数字;  
查看sql语句：  
select sql\_text from v$sqltext where sql\_id = '&sql\_id' order by address,piece；

方法一：杀进程源头：ps -ef |grep 655902（ospid）

查看spid(ospid)是否为local=no  
kill -9 spid(ospid)  
方法2：一个个杀：  
select s.sid,p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid in  
(select sid from v$session where event='latch: cache buffers chains' );  
确认所有spid 为local=no;  
kill -9 spid 一个个杀；  
检查是否存在坏块：  
desc v$database\_block\_corruption  
select \* from v$database\_block\_corruption;

select event,count(1) from gv$session group by event order by 2;  
select \* from v$lock\_type where type='TT';  
select sid,last\_call\_et,blocking\_sid from v$session where xxxx

set line 999 pages 1000  
set long 200  
col machine for a20  
col prog for a30  
col OSUSER for a20  
select to\_char(s.LOGON\_TIME,'yyyy-MM-dd HH24:mi:ss') LOGON\_TIME,  
s.sid,  
s.serial\#,  
s.machine,  
p.spid,  
s.status,  
substr(s.program, 1, 40) prog,  
s.osuser,  
s.USERNAME  
from v$session s, v$process p where s.username is not null  
AND s.paddr = p.addr and  
\--substr(s.program, 1, 40)  
s.machine='WORKGROUP\\MD-100-81';

输入：S\_T\_RTNRP\_STATUS\_2017

重置表空间的数据文件：

select ' alter ' || ' database datafile ''' || file\_name ||  
''' resize 2g ' || ';'  
from dba\_data\_files  
where tablespace\_name = 'SMART\_NRRPSTA\_2017';

五、DG传输过程监控

1、查看备库工作模式及状态

set lin 140 pages 100  
select inst\_id,db\_unique\_name,database\_role,open\_mode,protection\_mode,protection\_level,switchover\_status,force\_logging from gv$database;

select db\_unique\_name,protection\_mode,synchronization\_status,SYNCHRONIZED from v$archive\_dest\_status;

2、日志恢复进程  
archive log list;  
select thread\#,max(sequence\#) from v$archived\_log group by thread\#;  
select pid,process,client\_process,client\_pid,thread\#,sequence\#,status,DELAY\_MINS from v$managed\_standby;

\--RFS进程从主数据库接收重做数据，并将其写入备用重做日志。

3、查看standbylog状态，如果是RAC两节点，那么每个节点至少有一个是ACTIVE的状态，否则不对  
set lines 200  
select group\#,thread\#,sequence\#,bytes/1024/1024,archived,used,status,first\_change\#,last\_change\# from v$standby\_log;

检查备库已恢复的最大归档日志序号  
select thread\#,max(sequence\#),registrar,applied,status from v$archived\_log where applied='YES' and registrar='RFS' and name is not null  
group by thread\#,registrar,applied,status;

4、检查应用率和活动率（PS）  
\--Redo Applied 值以MB衡量。剩余两个以KB/s计算。  
select to\_char(start\_time,'DD-MON-RR HH24:MI:SS') start\_time,ITEM,sofar  
from v$recovery\_progress  
where item in ('Active Apply Rate','Average Apply Rate','Redo Applied');

5、审阅传输和应用滞后（PS+LS）  
\--transport lag 表明从主数据库到物理备用数据库的重做数据传输时间。  
\--apply lag 表明应用滞后时间，它反映了archive\_log\_dest\_n参数中 DELAY 特性。

COL NAME FOR A13  
COL VALUE FOR A20  
COL UNIT FOR A30  
SET LINES 200  
select name,value,unit,time\_computed  
from v$dataguard\_stats  
where name like '%lag%';

6、查看Data Guard状态视图中的错误  
set lines 132  
col message for a80  
col timestamp for a20  
select error\_code,severity,message,  
to\_char(timestamp,'DD-MON-RR HH24:MI:SS') timestamp  
from v$dataguard\_status  
where callout='YES'  
and timestamp > sysdate -1;

7、检查日志文件是否传输到备用数据库  
select dest\_name,status,error from v$archive\_dest where dest\_id=2;  
DEST\_NAME STATUS ERROR  
\-------------------- ---------- --------------------  
LOG\_ARCHIVE\_DEST\_2 VALID

status列如果为valid，说明归档成功，可以查看error列得到不能归档的原因

六、修改DG保护模式  
1、查看当前保护模式 主库查询  
select inst\_id,db\_unique\_name,database\_role,open\_mode,protection\_mode,protection\_level,switchover\_status,force\_logging from gv$database;

2、将备库转换为最大可用模式  
\--主库数据库操作 --将最大性能转为最大可用  
alter database set standby database to maximize availability;

二、较全一点的脚本

\------------------------------SGA---------------------------------------------  
\--SGA 各部分大小  
show sga  
select \* from v$sga;  
SELECT \* FROM V$SGAINFO;  
\--SGA设置大小  
show parameter sga\_target

\--SGA各个池大小  
COL name FORMAT a32;  
SELECT pool, name, bytes/1024/1024 M  
FROM v$sgastat  
WHERE pool IS NULL  
OR pool != 'shared pool'  
OR (pool = 'shared pool' AND  
(name IN  
('dictionary cache', 'enqueue', 'library  
cache', 'parameters', 'processes', 'sessions', 'free memory')))  
ORDER BY pool DESC NULLS FIRST, name;  
  
  
\------------------------BUFFER CACHE-----------------------------------------------  
\--查看buffer cache 命中率  
select 1 - (sum(decode(name, 'physical reads', value, 0)) /  
(sum(decode(name, 'db block gets', value, 0)) +  
(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"  
from v$sysstat;

select name,  
physical\_reads,  
(consistent\_gets + db\_block\_gets) logic\_reads,  
1 - (physical\_reads) / (consistent\_gets + db\_block\_gets) hit\_radio  
from v$buffer\_pool\_statistics;

\--查看buffer cache建议  
select size\_for\_estimate,  
estd\_physical\_read\_factor,  
to\_char(estd\_physical\_reads, 99999999999999999999999) as"estd\_physical\_reads"  
from v$db\_cache\_advice  
where name = 'DEFAULT';  
  
\--查看buffer cache建议 --适用于指定SGA的目的  
COL pool FORMAT a10;  
SELECT (SELECT ROUND(value / 1024 / 1024, 0)  
FROM v$parameter  
WHERE name = 'db\_cache\_size') "Current Cache(Mb)",  
name "Pool",  
size\_for\_estimate "Projected Cache(Mb)",  
ROUND(100 - estd\_physical\_read\_factor, 0) "Cache Hit Ratio%"  
FROM v$db\_cache\_advice  
WHERE block\_size = (SELECT value FROM v$parameter WHERE name = 'db\_block\_size')  
ORDER BY 3;

\--查看cache  
show parameter cache

\--各种读取的统计  
\---Database read buffer cache hit ratio =  
\---1 – (physical reads / (db block gets + consistent gets))  
SELECT to\_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN  
('physical reads', 'db block gets', 'consistent gets');

SELECT 'Database Buffer Cache Hit Ratio ' "Ratio"  
, ROUND((1-  
((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')  
/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')  
\+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets')  
))) \* 100)||'%' "Percentage"  
FROM DUAL;

\------------------------SHARED\_POOL-----------------------------------------------

show parameter shared

\---检查整体命中率(library cache)  
select sum(pinhits) get ,  
sum(pins)-sum(pinhits) miss,  
sum(pinhits) / sum(pins)  
from v$librarycache;

\-- 查看library cache 命中率（分类）

select t.NAMESPACE,t.GETHITRATIO\*100  
from v$librarycache t;

select sum(pins) "hits",  
sum(reloads) "misses",  
sum(pins) / (sum(pins) + sum(reloads)) "Hits Ratio"  
from v$librarycache;e  
  
\---检查shered pool free space  
SELECT \*  
FROM V$SGASTAT  
WHERE NAME = 'free memory'  
AND POOL = 'shared pool';

\---检查row cache(数据字典缓冲区)命中率  
\---当执行一个dml或ddl都会造成对数据字典的递归修改  
column updates format 999,999,999  
SELECT parameter  
, sum(gets)  
, sum(getmisses)  
, 100\*sum(gets - getmisses) / sum(gets) pct\_succ\_gets  
, sum(modifications) updates  
FROM V$ROWCACHE  
WHERE gets > 0  
GROUP BY parameter;

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE"  
FROM V$ROWCACHE;

\---查看Shared pool latch(多池技术)  
  
/\*col parameter for a20  
col session for a20\*/  
select a.ksppinm "Parameter",  
b.ksppstvl "Session Value",  
c.ksppstvl "Instance Value"  
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c  
where a.indx = b.indx and a.indx = c.indx  
and a.ksppinm = '\_kghdsidx\_count';  
  
\---查看shared pool建议  
column c1 heading 'Pool |Size(M)'  
column c2 heading 'Size|Factor'  
column c3 heading 'Est|LC(M) '  
column c4 heading 'Est LC|Mem. Obj.'  
column c5 heading 'Est|Time|Saved|(sec)'  
column c6 heading 'Est|Parse|Saved|Factor'  
column c7 heading 'Est|Object Hits' format 999,999,999  
SELECT shared\_pool\_size\_for\_estimate c1,  
shared\_pool\_size\_factor c2,  
estd\_lc\_size c3,  
estd\_lc\_memory\_objects c4,  
estd\_lc\_time\_saved c5,  
estd\_lc\_time\_saved\_factor c6,  
to\_char(estd\_lc\_memory\_object\_hits, 99999999999) c7  
FROM V$SHARED\_POOL\_ADVICE;

\--查看shared pool中 各种类型的chunk的大小数量  
SELECT KSMCHCLS CLASS,  
COUNT(KSMCHCLS) NUM,  
SUM(KSMCHSIZ) SIZ,  
To\_char(((SUM(KSMCHSIZ) / COUNT(KSMCHCLS) / 1024)), '999,999.00') || 'k' "AVG SIzE"  
FROM X$KSMSP  
GROUP BY KSMCHCLS;

\--查看是否有库缓冲有关的等待事件  
select sid, seq\#, event, p1, p1raw, p2, p2raw, p3, p3raw, state  
from v$session\_wait  
where event like 'library%';  
  
\--row cache命中率  
SELECT 'Dictionary Cache Hit Ratio ' "Ratio",  
ROUND((1 - (SUM(GETMISSES) / SUM(GETS))) \* 100, 2) || '%' "Percentage"  
FROM V$ROWCACHE;

\---library cache中详细比率信息  
SELECT 'Library Lock Requests' "Ratio",  
ROUND(AVG(gethitratio) \* 100, 2) || '%' "Percentage"  
FROM V$LIBRARYCACHE  
UNION all  
SELECT 'Library Pin Requests' "Ratio",  
ROUND(AVG(pinhitratio) \* 100, 2) || '%' "Percentage"  
FROM V$LIBRARYCACHE  
UNION all  
SELECT 'Library I/O Reloads' "Ratio",  
ROUND((SUM(reloads) / SUM(pins)) \* 100, 2) || '%' "Percentage"  
FROM V$LIBRARYCACHE ;  
  
  
\--查看library cache 内存分配情况(对哪类对象)  
SELECT lc\_namespace "Library",  
LC\_INUSE\_MEMORY\_OBJECTS "Objects",  
LC\_INUSE\_MEMORY\_SIZE "Objects Mb",  
LC\_FREEABLE\_MEMORY\_OBJECTS "Freeable Objects",  
LC\_FREEABLE\_MEMORY\_SIZE "Freeable Mb"  
FROM v$library\_cache\_memory;

\---查看使用shard\_pool保留池情况  
SELECT request\_misses, request\_failures, free\_space  
FROM v$shared\_pool\_reserved;  
  
  
  
\---查看cache中所有pool,命中情况  
COL pool FORMAT a10;  
SELECT a.name "Pool", a.physical\_reads, a.db\_block\_gets  
, a.consistent\_gets  
,(SELECT ROUND((1-(physical\_reads / (db\_block\_gets + consistent\_gets)))\*100)  
FROM v$buffer\_pool\_statistics  
WHERE db\_block\_gets+consistent\_gets != 0  
AND name = a.name) "Ratio"  
FROM v$buffer\_pool\_statistics a;  
  
  
相关命令  
\--- alter table xx cache  
\---ALTER TABLE(INDEX) xx STORAGE(BUFFER\_POOL KEEP);  
\---取消cache或keep(keep pool)  
\---ALTER TABLE XX NOCACHE;  
\---SELECT 'ALTER INDEX '||index\_name||' STORAGE(BUFFER\_POOL DEFAULT);'  
\---FROM USER\_INDEXES WHERE BUFFER\_POOL!='DEFAULT';

\------------------------PGA-----------------------------------------------  
  
\---查看pga  
show parameters area\_size  
\--- 查看pga  
SELECT \* FROM v$pgastat;  
  
\--查看pga建议  
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter  
WHERE name = 'pga\_aggregate\_target') "Current Mb"  
, ROUND(pga\_target\_for\_estimate/1024/1024,0) "Projected Mb"  
, ROUND(estd\_pga\_cache\_hit\_percentage) "%"  
FROM v$pga\_target\_advice  
ORDER BY 2;

\------------------------其他指标类---------------------------------------

\---查看数据库中行chain  
SELECT 'Chained Rows ' "Ratio",  
ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch continued row')/  
(SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch byrowid')  
)\* 100, 3)||'%' "Percentage"  
FROM DUAL;  
  
\---在内存中排序比率(最优排序)  
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;  
  
  
\---查询解析比率  
SELECT 'Soft Parses ' "Ratio",  
ROUND(((SELECT SUM(value)  
FROM V$SYSSTAT  
WHERE name = 'parse count (total)') -  
(SELECT SUM(value)  
FROM V$SYSSTAT  
WHERE name = 'parse count (hard)')) /  
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') \* 100,  
2) || '%' "Percentage"  
FROM DUAL  
UNION  
SELECT 'Hard Parses ' "Ratio",  
ROUND((SELECT SUM(value)  
FROM V$SYSSTAT  
WHERE name = 'parse count (hard)') /  
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') \* 100,  
2) || '%' "Percentage"  
FROM DUAL  
UNION  
SELECT 'Parse Failures ' "Ratio",  
ROUND((SELECT SUM(value)  
FROM V$SYSSTAT  
WHERE name = 'parse count (failures)') /  
(SELECT SUM(value)  
FROM V$SYSSTAT  
WHERE name = 'parse count (total)') \* 100,  
5) || '%' "Percentage"  
FROM DUAL;  
  
  
\--查询latch free 等待事件相关信息  
COL event FORMAT a20;  
COL waits FORMAT 9999990;  
COL timeouts FORMAT 99999990;  
COL average FORMAT 99999990;  
SELECT event "Event",  
time\_waited "Total Time",  
total\_waits "Waits",  
average\_wait "Average",  
total\_timeouts "Timeouts"  
FROM V$SYSTEM\_EVENT  
WHERE event = 'latch free'  
ORDER BY EVENT;  
\---查看数据库中查询时主要访问方式,获取大表小表访问比率(2个表的访问算法不同)  
\---table scans (long tables)过多的话,一般db file scattered read比较显著  
\---\_small\_table\_threshold来定义大表和小表的界限。缺省为2%的Buffer数量 ,>这个参数为大表  
\---default 大表的全表扫描会被置于LRU的末端(最近最少使用,冷端)，以期尽快老化(让其尽快换出buffer cache)，减少Buffer的占用  
\--表访问统计  
SELECT value, name  
FROM V$SYSSTAT  
WHERE name IN ('table fetch by rowid',  
'table scans (short tables)',  
'table scans (long tables)');

\-----查看大表小表扫描对应的值  
SELECT value, name FROM V$SYSSTAT WHERE name IN  
('table fetch by rowid', 'table scans (short tables)'  
, 'table scans (long tables)');  
  
  
SELECT 'Short to Long Full Table Scans' "Ratio"  
, ROUND(  
(SELECT SUM(value) FROM V$SYSSTAT  
WHERE name = 'table scans (short tables)')  
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN  
('table scans (short tables)', 'table scans (long tables)'))  
\* 100, 2)||'%' "Percentage"  
FROM DUAL  
UNION  
SELECT 'Short Table Scans ' "Ratio"  
, ROUND(  
(SELECT SUM(value) FROM V$SYSSTAT  
WHERE name = 'table scans (short tables)')  
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN  
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))  
\* 100, 2)||'%' "Percentage"  
FROM DUAL  
UNION  
SELECT 'Long Table Scans ' "Ratio"  
, ROUND(  
(SELECT SUM(value) FROM V$SYSSTAT  
WHERE name = 'table scans (long tables)')  
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name  
IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))  
\* 100, 2)||'%' "Percentage"  
FROM DUAL  
UNION  
SELECT 'Table by Index ' "Ratio"  
, ROUND(  
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')  
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name  
IN ('table scans (short tables)', 'table scans (long tables)'  
, 'table fetch by rowid'))  
\* 100, 2)||'%' "Percentage"  
FROM DUAL  
UNION  
SELECT 'Efficient Table Access ' "Ratio"  
, ROUND(  
(SELECT SUM(value) FROM V$SYSSTAT WHERE name  
IN ('table scans (short tables)','table fetch by rowid'))  
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name  
IN ('table scans (short tables)', 'table scans (long tables)'  
, 'table fetch by rowid'))  
\* 100, 2)||'%' "Percentage"  
FROM DUAL;  
\------------------------------------------------------------------------------------------------------------------------------------  
redo 相关

\--查询归档模式  
select name ,open\_mode,log\_mode from v$database;  
archive log list

\---检查日志切换频率  
select sequence\#,  
to\_char(first\_time, 'yyyymmdd\_hh24:mi:ss') firsttime,  
round((first\_time - lag(first\_time) over(order by first\_time)) \* 24 \* 60,2) minutes  
from v$log\_history  
where 1=1  
\-- and first\_time > sysdate - 1  
order by first\_time, minutes;

\---检查lgwr i/o性能 (time\_waited/total\_waits：表示平均lgwr写入完成时间 若>1(百分之一秒)表示写入过慢)  
select total\_waits,  
time\_waited,  
average\_wait,  
time\_waited / total\_waits as avg\_time  
from v$system\_event  
where event = 'log file parallel write';

\---检查与redo相关性能指标  
select name,value from v$sysstat where name like '%redo%';  
  
\---查询redo block size  
select max(lebsz) from x$kccle;  
  
\---查看redo allocation latch

col name for a30  
select name, gets, misses, misses / gets  
from v$latch  
where name = 'redo allocation';

col name for a30  
select name, gets, misses, misses / gets  
from v$latch\_children  
where name = 'redo allocation';

\---查看与redo相关等待事件  
col event format a40  
select event,total\_waits,time\_waited ,total\_timeouts,average\_wait  
from v$system\_event  
where upper(event) like'%REDO%';

\---查看user commit次数  
select to\_number(value,99999999999) from v$sysstat where name='user commits';

\---查看系统运行时间  
select (sysdate - startup\_time)\*24\*60\*60 as seconds from v$instance

\---计算出每秒用户提交次数  
select user\_commit次数/系统运行时间 from dual;

\---计算出每个事务平均处理多少个redo block  
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  
\--------------------------------------------------------------------------------------------------------------------------------------  
总体配置  
\---检查database基本信息  
select \* from v$version;  
select name ,open\_mode,log\_mode from v$database;  
\--检查是否为rac 实例数量  
select instance\_number,instance\_name ,status from gv$instance;  
show parameter cpu\_count  
\--默认数据块大小  
show parameter block\_size

\--各种文件数量  
select count(\*) from v$controlfile  
select count(\*) from v$tempfile;  
select count(\*) from v$datafile;

\--资源限制  
SELECT \* FROM V$RESOURCE\_LIMIt ;

\--数据库安装信息  
SELECT \*  
FROM V$OPTION  
\--数据库参数  
show parameter  
  
\-----------------------------------------------------------------------------------------------------------------------------------------  
数据文件与空间类

\--各种文件数量  
select count(\*) from v$tempfile;  
select count(\*) from v$datafile;

\--表空间大小  
select tablespace\_name , sum(bytes)/1024/1024 M from dba\_temp\_files group by tablespace\_name  
union all  
select tablespace\_name , sum(bytes)/1024/1024 M from dba\_data\_files group by tablespace\_name;

\--数据文件状态  
select t.online\_status,count(\*)  
from dba\_data\_files t  
group by t.online\_status ;

\--表空间基本信息  
SELECT t.status,  
t.tablespace\_name,  
t.extent\_management,  
t.segment\_space\_management,  
t.contents  
FROM DBA\_TABLESPACES t  
order by t.status

\--临时段使用情况  
COL username FORMAT a10;  
COL segtype FORMAT a10;  
SELECT username,  
segtype,  
extents "Extents Allocated",  
blocks "Blocks Allocated"  
FROM v$tempseg\_usage;

\--查看临时表空间总体使用情况  
SELECT TMP\_TBS.TABLESPACE\_NAME,  
SUM(TMP\_TBS.TOTAL\_MB) TOTAL\_MB,  
SUM(USED\_TOT.USED\_MB) USED\_MB,  
SUM(USED\_TOT.USED\_MB) / SUM(TMP\_TBS.TOTAL\_MB) \* 100 USED\_PERSENT  
FROM (SELECT TABLESPACE\_NAME, SUM(BYTES) / 1024 / 1024 TOTAL\_MB  
FROM DBA\_TEMP\_FILES  
GROUP BY TABLESPACE\_NAME) TMP\_TBS,  
(SELECT TMP\_USED.TABLESPACE,  
SUM(TMP\_USED.BLOCKS \* PARA.DB\_BLOCK\_SIZE) / 1024 / 1024 USED\_MB  
FROM V$SORT\_USAGE TMP\_USED,  
(SELECT VALUE DB\_BLOCK\_SIZE  
FROM V$PARAMETER  
WHERE NAME = 'db\_block\_size') PARA  
GROUP BY TMP\_USED.TABLESPACE) USED\_TOT  
where TMP\_TBS.TABLESPACE\_NAME = USED\_TOT.TABLESPACE(+)  
GROUP BY TMP\_TBS.TABLESPACE\_NAME;

\--查看临时表空间中排序段和数据段的使用情况  
SELECT TMP\_TBS.TABLESPACE\_NAME, USED\_TOT.SEGTYPE TEMP\_SEG\_TYPE,  
SUM(TMP\_TBS.TOTAL\_MB) TOTAL\_MB,  
SUM(USED\_TOT.USED\_MB) USED\_MB,  
SUM(USED\_TOT.USED\_MB) / SUM(TMP\_TBS.TOTAL\_MB) \* 100 USED\_PERSENT  
FROM (SELECT TABLESPACE\_NAME, SUM(BYTES) / 1024 / 1024 TOTAL\_MB  
FROM DBA\_TEMP\_FILES  
GROUP BY TABLESPACE\_NAME) TMP\_TBS,  
(SELECT TMP\_USED.TABLESPACE, TMP\_USED.SEGTYPE,  
SUM(TMP\_USED.BLOCKS \* PARA.DB\_BLOCK\_SIZE) / 1024 / 1024 USED\_MB  
FROM V$SORT\_USAGE TMP\_USED,  
(SELECT VALUE DB\_BLOCK\_SIZE  
FROM V$PARAMETER  
WHERE NAME = 'db\_block\_size') PARA  
GROUP BY TMP\_USED.TABLESPACE, TMP\_USED.SEGTYPE) USED\_TOT  
where TMP\_TBS.TABLESPACE\_NAME = USED\_TOT.TABLESPACE(+)  
GROUP BY TMP\_TBS.TABLESPACE\_NAME, USED\_TOT.SEGTYPE;

\--表空间  
set linesize 200;  
col TABLESPACE\_NAME for a30;  
select a.TABLESPACE\_NAME tbs\_name,  
round(a.BYTES/1024/1024) Total\_MB,  
round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) Used\_MB,  
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))\*100,2) Pct\_Free,  
nvl(round(b.BYTES/1024/1024), 0) Free\_MB ,  
auto  
from (select TABLESPACE\_NAME,  
sum(BYTES) BYTES,  
max(AUTOEXTENSIBLE) AUTO  
from sys.dba\_data\_files  
group by TABLESPACE\_NAME) a,  
(select TABLESPACE\_NAME,  
sum(BYTES) BYTES  
from sys.dba\_free\_space  
group by TABLESPACE\_NAME) b  
where a.TABLESPACE\_NAME = b.TABLESPACE\_NAME (+)  
order by ((a.BYTES-b.BYTES)/a.BYTES) desc  
/

\---查看数据文件物理IO信息  
SELECT fs.phyrds "Reads",  
fs.phywrts "Writes",  
fs.avgiotim "Average I/O Time",  
df.name "Datafile"  
FROM v$datafile df, v$filestat fs  
WHERE df.file\# = fs.file\#  
/

\--查看所有数据文件i/o情况  
/\*COL ts FORMAT a10 HEADING "Tablespace";  
COL reads FORMAT 999990999;  
COL writes FORMAT 999999990;  
COL br FORMAT 999999990 HEADING "BlksRead";  
COL bw FORMAT 9999999990 HEADING "BlksWrite";  
COL rtime FORMAT 9999999990;  
COL wtime FORMAT 9999999990;  
set linesize 3000;  
set pagesize 9999;\*/  
SELECT ts.name AS ts,  
fs.phyrds "Reads",  
fs.phywrts "Writes",  
fs.phyblkrd AS br,  
fs.phyblkwrt AS bw,  
fs.readtim/100 "RTime\*s",  
fs.writetim/100 "WTime\*s"  
FROM v$tablespace ts, v$datafile df, v$filestat fs  
WHERE ts.ts\# = df.ts\#  
AND df.file\# = fs.file\#  
UNION  
SELECT ts.name AS ts,  
ts.phyrds "Reads",  
ts.phywrts "Writes",  
ts.phyblkrd AS br,  
ts.phyblkwrt AS bw,  
ts.readtim /100 "RTime\*s",  
ts.writetim/100 "WTime\*s"  
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts  
WHERE ts.ts\# = tf.ts\#  
AND tf.file\# = ts.file\#  
ORDER BY 1;

\--定位哪些object在buffer cache中存在，占用的buffer cache的量是多少以及占用的是什么类型的buffer cache。  
select decode(pd.bp\_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',  
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',  
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,  
bh.object\_name,bh.blocks  
from x$kcbwds ds,x$kcbwbpd pd,(select set\_ds,  
o.name object\_name,count(\*) BLOCKS  
from obj$ o, x$bh x where o.dataobj\# = x.obj  
and x.state !=0 and o.owner\# !=0  
group by set\_ds,o.name) bh  
where ds.set\_id >= pd.bp\_lo\_sid  
and ds.set\_id <= pd.bp\_hi\_sid  
and pd.bp\_size != 0  
and ds.addr=bh.set\_ds  
order by decode(pd.bp\_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',  
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',  
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),bh.blocks;

\--针对不同用户的占用buffer的合计

select decode(pd.bp\_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',  
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',  
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,  
own,sum(bh.blocks)\*8192/1024/1024 used\_M  
from x$kcbwds ds,x$kcbwbpd pd,(select set\_ds,  
o.name object\_name,count(\*) BLOCKS,u.name own  
from obj$ o, x$bh x,user$ u where o.dataobj\# = x.obj  
and x.state !=0 and o.owner\# !=0 and o.owner\#=u.user\#  
group by set\_ds,o.name,u.name) bh  
where ds.set\_id >= pd.bp\_lo\_sid  
and ds.set\_id <= pd.bp\_hi\_sid  
And pd.bp\_size != 0  
and ds.addr=bh.set\_ds  
group by decode(pd.bp\_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',  
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',  
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),  
own  
order by own;

\--buffer cache 对象所有者，名称， 类型，总大小，cache大小  
column c1 heading "Object|Name" format a30 truncate  
column c2 heading "Object|Type" format a12 truncate  
column c3 heading "Number of|Blocks" format 999,999,999,999  
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999  
break on report  
compute sum of c3 on report  
select owner,  
object\_name,  
object\_type,  
num\_blocks,  
sum(blocks),  
trunc((num\_blocks / decode(sum(blocks), 0, .001, sum(blocks))), 4) \* 100 || '%' -- buffer中的数据块比例  
from (select o.owner owner\_name,  
o.object\_name object\_name,  
o.object\_type object\_type,  
count(1) num\_blocks  
from dba\_objects o, v$bh bh  
where o.object\_id = bh.objd  
and o.owner not in ('SYS', 'SYSTEM')  
group by o.object\_name, o.object\_type, o.owner  
order by count(1) desc) t1,  
dba\_segments s  
where s.segment\_name = t1.object\_name  
and s.owner = t1.owner\_name  
and num\_blocks > 10  
group by object\_name, object\_type, num\_blocks, owner  
order by num\_blocks desc;

\--10个热点对象  
col objct\_name for a30  
select \* from  
(select  
ob.owner, ob.object\_name, sum(b.tch) Touchs  
from x$bh b , dba\_objects ob  
where b.obj = ob.data\_object\_id  
and b.ts\# > 0  
group by ob.owner, ob.object\_name  
order by sum(tch) desc)  
where rownum <=10  
/

\--逻辑读  
col objct\_name for a30  
select \*  
from (select owner, object\_name, value  
from v$segment\_statistics  
where statistic\_name = 'logical reads'  
order by value desc)  
where rownum <= 10  
/

\--物理读最多十个对象  
col objct\_name for a30  
select \*  
from (select owner, object\_name, value  
from v$segment\_statistics  
where statistic\_name = 'physical reads'  
order by value desc)  
where rownum <= 10  
/

\---查看热点数据文件(从单块读取时间判断)  
COL FILE\_NAME FOR A30  
COL TABLESPACE\_NAME FOR A20  
SELECT T.FILE\_NAME,  
T.TABLESPACE\_NAME,  
ROUND(S.SINGLEBLKRDTIM / S.SINGLEBLKRDS, 2) AS CS,  
S.READTIM/100 READTIME\_S,  
S.WRITETIM/100 WIRTETIME\_S  
FROM V$FILESTAT S, DBA\_DATA\_FILES T  
WHERE S.FILE\# = T.FILE\_ID  
AND ROWNUM <= 10  
ORDER BY CS DESC  
/  
\----------------------------------------------------------------------------------------------------------------------------------  
undo 与回滚段

\---检查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;  
  
\---每个事务产生的redo 块大小  
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;  
  
\---查询undo具体信息  
COL undob FORMAT 99990;  
COL trans FORMAT 99990;  
COL snapshot2old FORMAT 9999999990;  
SELECT t.BEGIN\_TIME BEGIN\_TIME,  
t.END\_TIME END\_TIME,  
undoblks "UndoB",  
txncount "Trans",  
maxquerylen "LongestQuery",  
maxconcurrency "MaxConcurrency",  
ssolderrcnt "Snapshot2Old",  
nospaceerrcnt "FreeSpaceWait"  
FROM v$undostat t;  
  
\--查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)  
\--COL RBS FORMAT a4;  
SELECT n.name "RBS",  
s.extends "Extends",  
s.shrinks "Shrinks",  
s.wraps "Wraps",  
s.aveshrink "AveShrink",  
s.aveactive "AveActive"  
FROM v$rollname n  
JOIN v$rollstat s  
USING (usn)  
WHERE n.name != 'SYSTEM';  
  
\---查询当前rollback segment使用情况  
COL RBS FORMAT a4;  
SELECT n.name "RBS",  
s.status,  
s.waits,  
s.gets,  
s.writes,  
s.xacts "Active Trans"  
FROM v$rollname n  
JOIN v$rollstat s  
USING (usn)  
WHERE n.name != 'SYSTEM';  
  
\---查询使用rollback segment时等待比率  
SELECT ROUND(SUM(waits/gets)\*100,2)||'%' "Contention" FROM  
v$rollstat;

转载于:https://www.cnblogs.com/hmwh/p/11563515.html