监控语句

Oracle常用性能监控语句解析1、监控等待事件select event,sum(decode(wait_time,0,0,1)) prev,sum(decode(wait_time,0,1,0)) curr,count(*)from v$session_waitgroup by event order by 4;Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock. Locks are designed to ensure data integrity by limiting simultaneous data access.Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared. Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to accessa table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table.Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application. 也可利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：select * from v$system_eventwhere event in ('buffer busy waits','db file sequential read','db file scattered read','enqueue','free buffer waits','latch free','log file parallel write','log file sync','enq: TX - row lock contention');接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：selectse.sid,ername,se.event,se.total_waits,se.time_waited,se .average_waitfrom v$session s,v$session_event sewhere s.sid = se.sidand se.event not like 'SQL*Net%'and s.status = 'ACTIVE'and ername is not null;还可以组合v$session和v$session_wait视图进行查询：selectsw.sid,ername,sw.event,sw.wait_time,sw.state,sw.seconds _in_wait SEC_IN_WAITfrom v$session s,v$session_wait swwhere s.sid = sw.sidand sw.event not like 'SQL*Net%'and ername is not nullorder by sw.wait_time desc;查询具体会话等待事件的详细信息select sid,event,p1text,p1,p2text,p2,p3text,p3from v$session_waitwhere sid between &1 and &2and event not like '%SQL%'and event not like '%rdbms%';在查出会话执行了什么SQL语句发生等待事件：select s1.sid,s1.event,s2.sql_textfrom v$session s1,v$sql s2where s1.sid = &sid_inand s1.event in('enq: TX - row lock contention')and s1.SQL_ID = s2.sql_id ;2、监控表空间的I/O比例：select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbwfrom v$filestat f,dba_data_files dfwhere f.file#=df.file_id诊断：If the number of physical block reads is significantly higher than the number of physical reads, this is an indication that the indexes on these tables may need to be reviewed, or there may be full table scans being performed on the tables withinthe tablespace. In general, if the number of block reads is equal to the number of reads, the tables in the tablespace were being accessed by a ROWID, requiring the database to read only one data block.If one of the data files is getting a majority of the reads and writes, you may be able to improve performance by creating multiple data files on seperate disks or by striping the data file across multiple disks.3、查询是否有长时间的操作同时满足以下几个条件，操作信息才会出现在V$SESSION_LONGOPS中：1）、操作是以下几种操作之一# Table scan;# Index Fast Full Scan;# Hash join;# Sort/Merge;# Sort Output;# Rollback;# Gather Table's Index Statistics2）、操作时间大于6秒3）、读取的block数目大于一定量如果是TABLE FULL SCAN，读取的block数目至少大于10000如果是Index Fast Full Scan，读取的block数目至少大于1000 其他操作读取block的数目不明实验：create table tt as select * from all_objects;commit;Set timing on;select * from tt order by 1,2,3,4;用以下语句找出长时间操作的SQL语句：selectlongops.sid,longops.elapsed_seconds,longops.opname,sql.sql_ text fromv$session_longops longops , v$sql sql wherelongops.elapsed_seconds>6 and longops.sql_id=sql.sql_id;或者：SELECT SE.SID,OPNAME,TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK, ELAPSED_SECONDS ELAPSED,ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,SQL_TEXTFROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUEAND SL.SID = SE.SIDAND SOFAR != TOTALWORKORDER BY START_TIME;调整PGA优化排序：首先查看Oracle的v$pga_target_advice：SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb, estd_pga_cache_hit_percentage AS hit_ratio,estd_overalloc_countFROM v$pga_target_adviceORDER BY target_mb;然后调整PGAalter system set pga_aggregate_target=150M;在OLTP系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。