In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
10g dynamic shutdown
In fact, dynamic shutdown is only to enlarge the conditions of DRM, not really turn it off.
Alter system set "_ gc_affinity_limit=1000000 scope=both
Alter system set "_ gc_affinity_minimum" = 10000000 scope=both
10g static shutdown, all database instances need to be restarted at the same time
Alter system set "_ gc_affinity_time" = 0 scope=spfile
Alter system set "_ gc_undo_affinity" = false scope=spfile
11g dynamic shutdown, problems may occur when the sga is large
Alter system set "_ gc_policy_minimum" = 100000 scope=both
Alternatively, in the Oracle 11gR2 version, you can dynamically turn off the DRM feature online by:
Alter system set "_ lm_drm_disable" = 5 scope=both sid='*'
Description:
Level 4 disable read mostly
Level 5 disable DRM for all but undo
Level 7 disable drm for all including undo
11g static shutdown, all database instances need to be restarted at the same time
Alter system set "_ gc_policy_time" = 0 sid='*' scope=spfile
Alter system set "_ gc_undo_affinity" = false sid='*' scope=spfile
-an official script for testing DRM is attached.
-- 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_.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&×tamp&&suffix
Set trim on
Set trims on
Set lines 140
Set pages 100
Set verify off
Set feedback on
PROMPT DRMDIAG DATA FOR & & dbname&×tamp
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&×tamp
PROMPT
PROMPT Larger buffer caches (above 100gig) 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 & & 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.typewriter, 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 | |':'| | p1p1Maginp2text | |':'| | p2p2Maginp3text | |':'| | p3p3 |
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&×tamp&&suffix
PROMPT
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.