大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
gc buffer busy release:是在本地实例session 1之前已经有远程实例session 2请求访问了本地实例的相同buffer,并且没有完成,那么本地实例的session 1就是在等待gc buffer busy release。
创新互联从2013年成立,是专业互联网技术服务公司,拥有项目成都网站设计、做网站网站策划,项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命,1280元清苑做网站,已为上家服务,为清苑各地企业和个人服务,联系电话:13518219792
Busy server or active paging/swapping due to low free memory
Individual waits-(用于在GV$SESSION_WAIT中看到的等待)
P1 File # P2 Block # P3 Mode requested/mode held/block class SECONDS_IN_WAIT Amount of time waited for the current event file# This is the file# of the file that Oracle is trying to read from. block# This is the starting block number in the file from where Oracle starts reading the blocks. blocks This parameter specifies the number of blocks that Oracle is rying to read from the file# starting at block# Inst_id instance number To determine the root blocker for sessions waiting on the gc wait events use the below options 1.system state dump at cluster level 2. oratop displays waiters/blockers 3. v$wait_chains can be used to find the root blocker for sessions that are blocked,Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1) 4. Using v$hang_info, v$hang_session_info, etc 5. Oracle Hang Manager (Doc ID 1534591.1) Using the above information we can find the sessions waiting for specific gc events with their final blockers at instance level
System Wide wait-(用于在V$SYSTEM_EVNET中看到的等待)
如果等待缓冲区花费的时间较长,则需要根据以下内容确定哪个段遭受争用: SELECT inst_id, sid, event, wait_class, P1, P2, P3 Mode requested / mode held / block class, seconds_in_wait FROM gv$session_wait WHERE event LIKE 'gc buffer%'; 从前面的输出中,使用P1和P2中的数据,可以使用以下命令获得相关的对象信息以下查询: SELECT segment_name FROM dba_extents WHERE file_id = &file AND &block BETWEEN block_id AND block_id + blocks - 1 AND ROWNUM = 1;
2)gc block busy、enq: TX - row lock contention以及其他等待可能会影响阻止会话或者LMS进程。
3)高网络延迟或网络问题
4)繁忙的服务器或活动的页面调度/交换(由于可用内存不足)
5)低效SQL语句
7)Oracle Bug
Solution is to reorganize the index in a way to avoid the contention or hot spots using the below options I. Global Hash partition the index CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARTITION BY HASH (c1,c2) (PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4); II. Recreate the index as reverse key index (not suitable for large table, could require buffer cache increased accordingly) III. If index key is generated from a sequence, increase cache size of the sequence and make the sequence 'no order' if application supports it. Refer the doc link: http://docs.oracle.com/database/121/VLDBG/GUID-BF3F38E1-62BB-4EE3-86C1-A2EF8A258B1F.htm#VLDBG1089
对于enq: TX - row lock contention:
Mode 4-Related to ITL waits 从AWR报告或使用以下SQL查找具有较高ITL等待的段: SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME = 'ITL waits' AND VALUE > 0 ORDER BY VALUE; 增加这些高ITL等待的segment的inittrans值 Mode 6-Primarily due to application issue: 这是一个应用程序问题,需要应用程序开发人员来调查所涉及的SQL语句。 以下文档可能有助于进一步深入研究: Note:102925.1 - Tracing sessions: waiting on an enqueue Note:179582.1 - How to Find TX Enqueue Contention in RAC or OPS Note:1020008.6 - SCRIPT: FULLY DECODED LOCKING Note:62354.1 - TX Transaction locks - Example wait scenarios Note:224305.1 -Autonomous Transaction can cause locking
How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1)
How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1).pdf