文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Oracle10g/11g动态、静态关闭DRM特性方法

2024-04-02 19:55

关注

10g动态关闭

其实动态关闭其实只是把DRM的条件放大,并不是真正的关闭

alter system set "_gc_affinity_limit=1000000 scope=both;

alter system set "_gc_affinity_minimum"=10000000 scope=both;


10g静态关闭,需同时重启所有数据库实例

alter system set "_gc_affinity_time"=0 scope=spfile;

alter system set "_gc_undo_affinity"=false scope=spfile;


11g动态关闭,在sga较大时可能出现问题

alter system set "_gc_policy_minimum"=100000 scope=both;


或者,在Oracle 11gR2版本中,可以在线通过如下方式来动态关闭DRM功能: 

alter system set "_lm_drm_disable"=5 scope=both sid='*';

说明:

level 4   disable read mostly

level 5   disable DRM for all but undo

level 7   disable drm for all including undo


11g静态关闭,需同时重启所有数据库实例

alter system set "_gc_policy_time"=0 sid='*' scope=spfile;

alter system set "_gc_undo_affinity"=false sid='*' scope=spfile;


--------------附官方检测DRM的脚本------------

-- NAME: DRMDIAG.SQL

-- ------------------------------------------------------------------------

-- AUTHOR: Michael Polaski - Oracle Support Services

-- ------------------------------------------------------------------------

-- PURPOSE:

-- This script is intended to provide a user friendly guide to troubleshoot

-- drm (dynamic resource remastering) waits. The script will create a file

-- called drmdiag_<timestamp>.out in your local directory.

set echo off

set feedback off

column timecol new_value timestamp

column spool_extension new_value suffix

select to_char(sysdate,'Mondd_hh34mi') timecol,

'.out' spool_extension from sys.dual;

column output new_value dbname

select value || '_' output

from v$parameter where name = 'db_name';

spool drmdiag_&&dbname&&timestamp&&suffix

set trim on

set trims on

set lines 140

set pages 100

set verify off

set feedback on


PROMPT DRMDIAG DATA FOR &&dbname&&timestamp

PROMPT Important paramenters:

PROMPT

PROMPT _gc_policy_minimum (default is 1500). Increasing this would cause DRMs to happen less frequently.

PROMPT Use the "OBJECT_POLICY_STATISTICS" section later in this report to see how active various objects are.

PROMPT

PROMPT _gc_policy_time (default to 10 (minutes)). Amount of time to evaluate policy stats. Use the

PROMPT "OBJECT_POLICY_STATISTICS" section later in this report to see how active various objects are for the

PROMPT _gc_policy_time. Usually not necessary to change this parameter.

PROMPT

PROMPT _gc_read_mostly_locking (default is TRUE). Setting this to FALSE would disable read mostly related DRMs.

PROMPT

PROMPT gcs_server_processes (default is derived from CPU count/4). May need to increase this above the

PROMPT default to add LMS processes to complte the work during a DRM but the default is usually adequate.

PROMPT

PROMPT _gc_element_percent (default is 110). May need to apply the fix for bug 14791477 and increase this to

PROMPT 140 if running out of lock elements. Usually not necessary to change this parameter.

PROMPT

PROMPT GC Related parameters set in this instance:

show parameter gc

PROMPT

PROMPT CPU count on this instance:

show parameter cpu_count


PROMPT

PROMPT SGA INFO FOR &&dbname&&timestamp

PROMPT

PROMPT Larger buffer caches (above 100 gig) may increase the cost of DRMs significantly.

set lines 120

set pages 100

column component format a40 tru

column current_size format 99999999999999999

column min_size format 99999999999999999

column max_size format 99999999999999999

column user_specified_size format 99999999999999999

select component, current_size, min_size, max_size, user_specified_size

from v$sga_dynamic_components

where current_size > 0;


PROMPT

PROMPT ASH THRESHOLD...

PROMPT

PROMPT This will be the threshold in milliseconds for total drm freeze

PROMPT times. This will be used for the next queries to look for the worst

PROMPT 'drm freeze' minutes. Any minutes that have an average log file

PROMPT sync time greater than the threshold will be analyzed further.

column threshold_in_ms new_value threshold format 999999999.999

select decode(min(threshold_in_ms),null,0,min(threshold_in_ms)) threshold_in_ms

from (select inst_id, to_char(sample_time,'Mondd_hh34mi') minute,

sum(time_waited)/1000 threshold_in_ms

from gv$active_session_history

where event like '%drm freeze%'

group by inst_id,to_char(sample_time,'Mondd_hh34mi')

order by 3 desc)

where rownum <= 10;


PROMPT

PROMPT ASH WORST MINUTES FOR DRM FREEZE WAITS:

PROMPT

PROMPT APPROACH: These are the minutes where the avg drm freeze time

PROMPT was the highest (in milliseconds).

column event format a30 tru

column program format a35 tru

column total_wait_time format 999999999999.999

column avg_time_waited format 999999999999.999

select to_char(sample_time,'Mondd_hh34mi') minute, inst_id, event,

sum(time_waited)/1000 TOTAL_WAIT_TIME , count(*) WAITS,

avg(time_waited)/1000 AVG_TIME_WAITED

from gv$active_session_history

where event like '%drm freeze%'

group by to_char(sample_time,'Mondd_hh34mi'), inst_id, event

having sum(time_waited)/1000 > &&threshold

order by 1,2;


PROMPT

PROMPT ASH DRM BACKGROUND PROCESS WAITS DURING WORST MINUTES:

PROMPT

PROMPT APPROACH: What are LMS and RMV doing when 'drm freeze' waits

PROMPT are happening? LMD and LMON info may also be relevant

column inst format 999

column minute format a12 tru

column event format a50 tru

column program format a55 wra

select to_char(sample_time,'Mondd_hh34mi') minute, inst_id inst,

sum(time_waited)/1000 TOTAL_WAIT_TIME , count(*) WAITS,

avg(time_waited)/1000 AVG_TIME_WAITED,

program, event

from gv$active_session_history

where to_char(sample_time,'Mondd_hh34mi') in (select to_char(sample_time,'Mondd_hh34mi')

from gv$active_session_history

where event like '%drm freeze%'

group by to_char(sample_time,'Mondd_hh34mi'), inst_id

having sum(time_waited)/1000 > &&threshold and sum(time_waited)/1000 > 0.5)

and (program like '%LMS%' or program like '%RMV%' or program like '%LMD%' or

program like '%LMON%' or event like '%drm freeze%')

group by to_char(sample_time,'Mondd_hh34mi'), inst_id, program, event

order by 1,2,3,5 desc, 4;


PROMPT

PROMPT POLICY HISTORY INFO:

PROMPT See if you can correlate policy history events with minutes of high

PROMPT wait time.

select * from gv$policy_history

order by event_date;

PROMPT

PROMPT DYNAMIC_REMASTER_STATS

PROMPT This shows where time is spent during DRM operations.

set heading off

set lines 60

select 'Instance: '||inst_id inst, 'Remaster Ops: '||remaster_ops rops,

'Remaster Time: '||remaster_time rtime, 'Remastered Objects: '||remastered_objects robjs,

'Quiesce Time: '||quiesce_time qtime, 'Freeze Time: '||freeze_time ftime,

'Cleanup Time: '||cleanup_time ctime, 'Replay Time: '||replay_time rptime,

'Fixwrite Time: '||fixwrite_time fwtime, 'Sync Time: '||sync_time stime,

'Resources Cleaned: '||resources_cleaned rclean,

'Replayed Locks Sent: '||replayed_locks_sent rlockss,

'Replayed Locks Received: '||replayed_locks_received rlocksr,

'Current Objects: '||current_objects

from gv$dynamic_remaster_stats

order by 1;

set lines 120

set heading on


PROMPT

PROMPT OBJECT_POLICY_STATISTICS:

PROMPT The sum of the last 3 columns (sopens,xopens,xfers) decides whether the object

PROMPT will be considered for DRM (_gc_policy_minimum). The duration of the stats

PROMPT are controlled by _gc_policy_time (default is 10 minutes).

select object,node,sopens,xopens,xfers from x$object_policy_statistics;


PROMPT

PROMPT ACTIVE OBJECTS (OBJECT_POLICY_STATISTICS)

PROMPT These are the objects that are above the default _gc_policy_minimum (1500).

select object, node, sopens+xopens+xfers activity

from x$object_policy_statistics

where sopens+xopens+xfers > 1500

order by 3 desc;


PROMPT LWM FOR LE FREELIST

PROMPT This number should never get near zero, if it does consider the fix for bug 14791477

PROMPT and/or increasing _gc_element_percent.

select sum(lwm) from x$kclfx;


PROMPT

PROMPT GCSPFMASTER INFO WITH OBJECT NAMES

column objname format a120 tru

select o.name || ' - '|| o.subname objname, o.type#, h.*

from v$gcspfmaster_info h, obj$ o where h.data_object_id=o.dataobj#

order by data_object_id;


PROMPT

PROMPT ASH DETAILS FOR WORST MINUTES:

PROMPT

PROMPT APPROACH: If you cannot determine the problem from the data

PROMPT above, you may need to look at the details of what each session

PROMPT is doing during each 'bad' snap. Most likely you will want to

PROMPT note the times of the high drm freeze waits, look at what

PROMPT LMS, RMV, LMD0, LMON is doing at those times, and go from there...

set lines 140

column program format a45 wra

column sample_time format a25 tru

column event format a30 tru

column time_waited format 999999.999

column p1 format a40 tru

column p2 format a40 tru

column p3 format a40 tru

select sample_time, inst_id inst, session_id, program, event, time_waited/1000 TIME_WAITED,

p1text||': '||p1 p1,p2text||': '||p2 p2,p3text||': '||p3 p3

from gv$active_session_history

where to_char(sample_time,'Mondd_hh34mi') in (select

to_char(sample_time,'Mondd_hh34mi')

from gv$active_session_history

where event like '%drm freeze%'

group by to_char(sample_time,'Mondd_hh34mi'), inst_id

having sum(time_waited)/1000 > &&threshold)

and time_waited > 0.5

order by 1,2,3,4,5;


spool off


PROMPT

PROMPT OUTPUT FILE IS: drmdiag_&&dbname&&timestamp&&suffix

PROMPT



阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯