RE: Resource Manager Plans and SYSTEM_GROUP
Date: Thu, 10 Dec 2020 00:05:38 +0000
Message-ID: <BY5PR19MB4068709CE599E15F49E7C4EC9BCB0_at_BY5PR19MB4068.namprd19.prod.outlook.com>
Amen, Brother Mark! This actually looks pretty reasonable to me for “day-to-day” operations. Note especially, “Your mileage WILL vary”
I’ve seen that more times than I care to count! As Scotty (StarTrek) remarked, “The more they tinker with the plumbing, the easier it is to stop up the drain”.
On that note, I’ve also seen batch “mass change” jobs “pollute” statistics, this kind of tuning is definitely an iterative process and not for the “faint of heart”.
Be safe out there!
Clay Jackson
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mark W. Farnham Sent: Wednesday, December 9, 2020 12:47 PM To: brad_peek_at_yahoo.com; 'Oracle-l Digest Users' <oracle-l_at_freelists.org> Subject: RE: Resource Manager Plans and SYSTEM_GROUP
CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.
In answer to your question: Whenever you have significant user batch load that runs during any automatic system stats gathering and so forth. You don’t want to make the user work overdue or have batch operations intrude on heavy interactive windows for marginal performance improvements.
It is quite easy to choke out all user work with system work.
Resource allocation is not a cookie cutter process once a system’s overall workload becomes a signifigant fraction of system capacity.
Your mileage will vary.
mwf
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brad Peek (Redacted sender "brad_peek" for DMARC)
Sent: Wednesday, December 09, 2020 3:02 PM
To: Oracle-l Digest Users
Subject: Fw: Resource Manager Plans and SYSTEM_GROUP
I joined a team that has several databases with resource manager plans that prioritize SYSTEM_GROUP low relative to some other consumer groups.
Example:
GROUP Share
----------- -----
WEB 60 BATCH 20
SYSTEM_GROUP 10
OTHER_GROUP 10 Since the servers almost never reach 100% CPU it hasn't been a big issue, but I did want to confirm that the general best practice is to give SYSTEM_GROUP the highest priority among the consumer groups.
I suspect it is a case of copying the same plan across multiple DBs, but before I recommend changing this, can anyone think of a use case where having SYSTEM_GROUP with a relatively low priority would make sense?
Notes:
-- DB version 12.1.0.2
- The mapping for SYSTEM_GROUP is oracle_user in ('SYS', 'SYSTEM')
Brad Peek
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 10 2020 - 01:05:38 CET