Oracle 26ai Database Resource Manager - Design Exercise

Course Project: Design a Resource Management Plan

Objective: Design a PDB resource plan that classifies web-store workloads, assigns relative CPU shares, and distinguishes SQL parallelism from application login controls.

Background and Scenario

You administer the House-O-Pets web store in a self-managed Oracle 26ai application PDB. The application uses pooled database connections. Website customer accounts are application identities; a new customer does not receive a separate Oracle database account.

The business defines three workload categories:

LOOKER: anonymous browsing and general product information, with the lowest CPU weight.
SHOPPER: shopping-cart activity before purchase, with a higher CPU weight than browsing.
BUYER: checkout activity and authenticated returning-customer requests that the application identifies as eligible for premium treatment, with the highest CPU weight.

BUYER SQL must run serially. Separately, the store permits only one active authenticated website login per customer account. Many different customers must still be able to use the store concurrently.

The development team can route requests through separate database services and connection pools, or supply reviewed application attributes for workload mapping. Customer status must be determined by trusted application logic. Assume the CDB administrator will review the resource policy needed to support the PDB plan.

Instructions

Write a design proposal rather than executable PL/SQL. Include:

A plan name and its PDB scope. Use a single-level plan without subplans.
A policy table with LOOKER, SHOPPER, BUYER, and OTHER_GROUPS. For each group, state its workload, CPU shares, and degree-of-parallelism (DOP) limit. Choose positive relative weights so BUYER receives more than SHOPPER and SHOPPER more than LOOKER.
A short explanation of why CPU shares are not hard CPU ceilings or guaranteed website response times.
A mapping strategy that works with pooled connections. Explain how a reused connection avoids retaining an inappropriate workload classification.
An explanation of how to enforce the single authenticated website login rule, and why BUYER DOP 1 does not enforce it.
A brief deployment and verification checklist covering the active plan, session classification, and representative workload behavior.

A directive associates a plan with a consumer group; do not invent separate directive objects named TOP_PRIORITY, MEDIUM_PRIORITY, or LOW_PRIORITY. Describe each directive through its group and settings.

Hints

Use SHARES for this new design. A DOP limit controls parallel execution per operation. An active-session pool controls concurrent active calls in a group, while a website login belongs to the application session layer. If you propose an optional active-call limit, justify it separately; no such limit is required here.

Review Database Resource Manager in Oracle 26ai. The later implementation lesson demonstrates creating, mapping, activating, and verifying a plan.

Exercise Scoring

This is a written exercise with a 10-point manual rubric. Use it for self-review or instructor assessment. The website does not automatically calculate a score or execute your proposed policy.

Manual assessment rubric, 10 points maximum
Criterion	Points
Names the plan and uses a single-level policy in the application PDB.	1
Defines LOOKER, SHOPPER, BUYER, and OTHER_GROUPS coverage.	2
Chooses justified CPU shares with BUYER above SHOPPER above LOOKER, and distinguishes weights from caps.	2
Specifies DOP limits, including BUYER DOP 1, and distinguishes parallelism from active calls and logins.	2
Proposes reliable workload classification for pooled connections.	1
Places the one-login-per-customer rule in the application session layer.	1
Describes activation and verification under representative load.	1

Submit Your Design

Enter your policy table and explanations below. Submitting displays your response with one possible solution.