oracle如何监控io oracle看监听

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

Oracle监控的关键指标有哪些？

1、监控事例的等待selectevent,sum(decode(wait_Time,0,0,1))"Prev", sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot" from v$session_Wait groupbyevent order by4; 2、回滚段的争用情况select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn; 3、监控表空间的 I/O 比例select df.tablespace_name name,df.file_name "file",f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name; 4、监控文件系统的 I/O 比例select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#; 5、在某个用户下找所有的索引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 order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position; 6、监控 SGA 的命中率select a.value + b.value "logical_reads", c.value "phys_reads", round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40; 7、监控 SGA 中字典缓冲区的命中率select parameter, gets,Getmisses, getmisses/(gets+getmisses)*100"miss ratio", (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hit ratio" from v$rowcache where gets+getmisses 0 groupby parameter, gets, getmisses; 8、监控 SGA 中共享缓存区的命中率，应该小于1%select sum(pins)"Total Pins", sum(reloads)"Total Reloads", sum(reloads)/sum(pins)*100 libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins)"hit radio",sum(reloads)/sum(pins)"reload percent" from v$librarycache; 9、显示所有数据库对象的类别和大小select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size) size_required from dba_object_size groupby type order by2; 10、监控 SGA 中重做日志缓存区的命中率，应该小于1%SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation','redo copy'); 11、监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)','sorts (disk)'); 12、监控当前数据库谁在运行什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece; 13、监控字典缓冲区SELECT (SUM(PINS - RELOADS))/ SUM(PINS)"LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED))/ SUM(GETS)"ROW CACHE" FROM V$ROWCACHE; SELECT SUM(PINS)"EXECUTIONS", SUM(RELOADS)"CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者，此比率小于1%，接近0%为好。 SELECT SUM(GETS)"DICTIONARY GETS",SUM(GETMISSES)"DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE 14、找ORACLE字符集select*from sys.props$ where name='NLS_CHARACTERSET'; 15、监控 MTSselect busy/(busy+idle)"shared servers busy"from v$dispatcher; 此值大于0.5时，参数需加大 select sum(wait)/sum(totalq)"dispatcher waits"from v$queue where type='dispatcher'; select count(*)from v$dispatcher; select servers_highwater from v$mts; servers_highwater接近mts_max_servers时，参数需加大 16、碎片程度select tablespace_name,count(tablespace_name)from dba_free_space groupby tablespace_name having count(tablespace_name)10; alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; select*from ts_blocks_v; select tablespace_name,sum(bytes),max(bytes),count(block_id)from dba_free_space groupby tablespace_name; 查看碎片程度高的表 SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS','SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX( COUNT(*)) FROM dba_segments GROUP BY segment_name); 17、表、索引的存储情况检查select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='tablespace_name'and segment_type='TABLE'groupby tablespace_name,segment_name; select segment_name,count(*)from dba_extents where segment_type='INDEX'and owner='owner' groupby segment_name; 18、找使用CPU多的用户sessionselect a.sid,spid,status,substr(a.program,1,10) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

如何提高Oracle对系统CPU和IO的利用率

Oracle中的IO问题及其解决思路

对于负载偏重点不同，我们可以简单的将数据库系统分为CPU负载系统（CPU

Bound

System）和IO负载系统（IO

Bound

System）。顾名思义，CPU负载系统的资源瓶颈在于CPU，而IO负载系统的瓶颈在于磁盘IO。

我们可以通过操作系统的一些命令来确认一个系统是否是存在IO负载。在UNIX下，可以使用"iostat"或者"sar

-d"来看系统的IO情况；在windows下，可以通过系统的性能监视器查看，但由于性能监控器中看到的IO是静态的IO总量信息，并不直观，因此也可以用本站的TopShow工具来查看实时的IO信息。

在UNIX系统下，发现CPU

IDEL很低并不一定代表这是一个CPU负载系统。一个IO负载系统在表面上看CPU的IDEL值也可能很低：

怎么监视oracle所执行的所有操作？具体的。

安装一个oracle sql developer（免费的），设置数据库连接后，tools-Monitor Sessions，会提示你选一个连接，然后就可以监控了，可以设置刷新的频率。

这是该软件首页，有下载连接：

除了awr外,怎么看oracle表空间io情况

通过配置本地数据库的tns配置文件实现：

去oracle安装目录下oracle\product\10.2.0\db_2\NETWORK\ADMIN\ 找到tnsnames.ora，用记事本打开，里边有远程数据库的tns连接配置串如下

ORCL23 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.23)(PORT = 1521))

)

(CONNECT_DATA =

(SID = ORCL)

)

添加好ip、端口和目标数据库的实例名(SID)，然后确保tns名称(ORCL23)在整个文档中唯一，保存后打开数据库连接工具，输入远程数据库上的用户名密码，选择数据库对象为你配置的连接名就可以了

oracle asm 能提高多少 io

监控ASM磁盘组IO吞吐量是管理ASM的基础功能，这篇文章讨论通过asmiostat.sh脚本和asmcmd iostat命令监控ASM磁盘组IO吞吐量，它类似于大部分Unix操作系统提供的iostat命令。

一.Oracle 11gR2 Database之前的版本参考如下文章使用asmiostat.sh脚本。

ASMIOSTAT Script to collect iostats for ASM disks (文档 ID 437996.1)修改时间:2013-2-15类型:SCRIPT

In this Document

Purpose

Requirements

Configuring

Instructions

Script

Sample Output

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.3 to 11.1.0.7 [Release 10.1 to 11.1]

Information in this document applies to any platform.

***Checked for relevance on 27-Feb-2011***PURPOSE

The OS command iostat is normally used to monitoring system input/output device load. This script will provide similar information like iostat but specific for the ASM disks.

For details about iostat (ie cumulative or not, and so on) please refer to the man of iostat.

REQUIREMENTS

We use v$asm_disk_stat instead of v$asm_disk because the information is exactly the same.

The only difference is v$asm_disk_stat is the information available in memory while v$asm_disk access the disks to re-collect some information. Since the information required doesn't require to "re-collect" it from the disks, v$asm_disk_stat is more appropriate here.

On Solaris, please use :

- the /usr/wpg4/bin/grep utility instead of /usr/bin/grep to avoid the following error:

"grep: illegal option -- q"

- the /usr/wpg4/bin/awk utility instead of /usr/bin/awk to avoid the following error:

"awk: syntax error near line 48"

On other platforms, it could sometime fail due to Shell compatibility issues.

In such case, retry using another Shell (ie: Bash instead of Ksh)We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. This script is provided as an example. If it doesn't work, you must adapt it by yourself for your platformIn 10.1 asmcmd utility is not included:

asmcmd can be used against ASM versions 10gR1 (10.1.0.n) and 10gR2 (10.2.0.n). In ASM version 10.2 asmcmd is provided by default ASM installation.

To use asmcmd in ASM version 10.1 environment we can just copy relevant files from 10.2 installation into the 10.1CONFIGURING

Not required.

INSTRUCTIONS

Not required

CAUTION

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

SAMPLE OUTPUT

For every ASM disk, this is the output provided:

DiskPath - Path to ASM disk

DiskName - ASM disk name

Gr - ASM disk group number

Dsk - ASM disk number

Reads - Reads

Writes - Writes

AvRdTm - Average read time (in msec)AvWrTm - Average write time (in msec)KBRd - Kilobytes read

KBWr - Kilobytes written

AvRdSz - Average read size (in bytes)AvWrSz - Average write size (in bytes)RdEr - Read errors

WrEr - Write errors

Script Output

DiskPath - DiskName Gr Dsk Reads Writes AvRdTm AvWrTm KBRd KBWr AvRdSz AvWrSz RdEr WrEr/dev/asmdisk14 - DATA_0000 2 0 0 4 0.0 5.0 0 16 0 4096 0 0/dev/asmdisk4 - DATA_0001 2 1 10 0 17.0 0.0 0 0 0 0 0 0/dev/asmdisk17 - DATA_0002 2 2 0 1 0.0 0.0 0 0 0 512 0 0/dev/asmdisk5 - DATA_0003 2 3 0 0 0.0 0.0 0 0 0 0 0 0/dev/asmdisk16 - DATA_0004 2 4 3 3 6.7 3.3 0 0 0 0 0 0/dev/asmdisk23 - DATA_0005 2 5 0 0 0.0 0.0 0 0 0 0 0 0The asmiostat.txt output file can be used for .csv and graphical representation by using Doc 1373682.1请在这里下载asmiostat.zip工具。

二.11gR2使用asmcmd工具下的iostat命令。

1.ASMCMD工具iostat命令。

ASMCMD [+] help iostat

iostat

Displays I/O statistics for Oracle ASM disks in mounted disk groups.

iostat [-et][--io] [--suppressheader] [--region] [-G diskgroup] [interval]

iostat lists disk group statistics using the V$ASM_DISK_IOSTAT view.

The options for the iostat command are described below.

-e - Displays error statistics (Read_Err, Write_Err).

-G diskgroup - Displays statistics for the disk group name.

--suppressheader - Suppresses column headings.

--io - Displays information in number of I/Os, insteadof bytes.

-t - Displays time statistics (Read_Time, Write_Time).

--region - Displays information for cold and hot disk regions(Cold_Reads, Cold_Writes, Hot_Reads, Hot_Writes).

interval - Refreshes the statistics display based on theinterval value (seconds).

The attribute descriptions for iostat command output are describedbelow. To view the complete set of statistics for a disk group,use the V$ASM_DISK_IOSTAT view.

Group_Name Name of the disk group.

Dsk_Name Name of the disk.

Reads Total number of bytes read from the disk.

If the --io option is entered, then the valueis displayed as number of I/Os.

Writes Total number of bytes written from the disk.

If the --io option is entered, then the valueis displayed as number of I/Os.

Cold_Reads Total number of bytes read from the cold diskregion. If the --io option is entered, thenthe value is displayed as number of I/Os.

Cold_Writes Total number of bytes written from the colddisk region. If the --io option is entered,then the value is displayed as number of I/Os.

Hot_Reads Total number of bytes read from the hotdisk region. If the --io option is entered,then the value is displayed as number of I/Os.

Hot_Writes Total number of bytes written from the hot diskregion. If the --io option is entered, then thevalue is displayed as number of I/Os.

Read_Err Total number of failed I/O read requests forthe disk.

Write_Err Total number of failed I/O write requests forthe disk.

Read_Time Total I/O time (in hundredths of a second) forread requests for the disk if the

TIMED_STATISTICS initialization parameter isset to TRUE (0 if set to FALSE).

Write_Time Total I/O time (in hundredths of a second) forwrite requests for the disk if the

TIMED_STATISTICS initialization parameter isset to TRUE (0 if set to FALSE).

The following are examples of the iostat command. The first exampledisplays disk I/O statistics for the data disk group in total numberof bytes. The second example displays disk I/O statistics for the datadisk group in total number of I/O operations.

2.执行下面的命令，5秒钟显示一次data磁盘组的IO吞吐量，Reads，Writes单位是bytes。

ASMCMD [+] iostat -G data 5