A responsible database administrator connects an observed problem to evidence, chooses a change within the task's scope, verifies its effect, and leaves enough information for another operator to continue. The work combines access control, diagnosis, a reversible improvement, and an operational handoff: a change has to help without adding unnecessary access or concealing remaining work.
Oracle DBA Lesson 106 Part 2 — Operate and hand off a database responsibly
A reporting incident
A reporting account cannot read orders. A report is slow, and maintenance is approaching. Treat the causes as unknown until you collect evidence. An access error can prevent the report from running. After access is corrected, a separate performance problem may remain.
Record the actual user, database, container, service, error text, timestamps, and reported business impact. Confirm the intended service and application identity rather than diagnosing an unintended connection. In an ordinary connected session:
SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
SYS_CONTEXT('USERENV','CON_NAME') AS con_name,
SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name
FROM dual;These values identify the current session. Compare them with the recorded topology and service map. They do not establish all application access or readiness tests.
See SYS_CONTEXT: current session identity.
Keep access scoped to the task
The access matrix names each account or role, the relevant object, the required action, the source of its effective privilege, and the test result. Inspect direct grants, enabled roles, relevant system privileges, and other paths such as PUBLIC. The object's owner, or a scoped security administrator, may need to inspect grants that the reporting account cannot see.
If the approved requirement is to read SHOP.ORDERS, the correct object exists in the intended container, and the missing SELECT privilege has been established, the owner or another grantor can use:
GRANT SELECT ON shop.orders TO reporting;This grants table-level SELECT to the reporting account. It gives access to the table's rows and columns within the applicable security policies. If the requirement allows only a subset, design the narrower view or policy first and grant access to that object. Do not use this command to bypass that requirement.
Test the allowed read with the reporting account and retain the actual result. Also show that a write the account should not perform is denied. Use a disposable object or a data copy, because an unexpectedly allowed statement, trigger, or commit can change data. Record the attempted action, the actual error, and the effective identity. Retain configured audit evidence. An empty audit query does not prove that no action occurred when the necessary policy was not enabled.
An unexplained DBA role grant adds extensive capabilities for a narrow read. Reject that alternative, and reject killing a session by guesswork or using an unentitled licensed advisor.
Record the pre-change grant paths and a restoration procedure the owner can run. If this exact direct grant was newly added, remove it with:
REVOKE SELECT ON shop.orders FROM reporting;Revoking this grant does not remove equivalent access through another role, system privilege, or grant path. Review dependencies and retest both required access boundaries. Grant and revoke are DDL. Oracle performs implicit commits around DDL, so transaction rollback is not the restoration method. Use a separate administrative session with no unrelated pending transaction, and preserve the original grants.
See GRANT: object and system privileges, REVOKE: direct grants and remaining privilege paths, and COMMIT: explicit and implicit transaction boundaries.
Diagnose the performance symptom
Build a timeline and compare competing explanations. Useful candidates include server waits, a changed execution plan, a different workload, and client fetch or output overhead. Each proposed cause needs a check that could distinguish it from another cause.
Capture the SQL text or a stable query identity, bind values, the data version, the complete returned row count, result checks, client settings, start and end times, user impact, and the concurrent workload. Measure from query submission through completion of the intended full fetch and output. The comparison must use the same boundaries.
Inspect the target session. Substitute the actual session identity as a bind:
SELECT sid, serial#, con_id, sql_id,
event, wait_class, state
FROM v$session
WHERE sid = :target_sid;SID and SERIAL# help identify the session instance. Record the container and the connection lifetime as well. EVENT, WAIT_CLASS, and STATE help interpret the sample. If STATE is not WAITING, the event can describe the last wait. A single sample does not describe a whole slow run. Collect spaced observations over the interval and correlate them with the application timeline and the current plan. Do not infer a root cause solely from a familiar event name.
For a bounded round-trip comparison, snapshot the target session's statistic before and after the report:
SELECT ss.sid, sn.name, ss.value
FROM v$sesstat ss
JOIN v$statname sn ON sn.statistic# = ss.statistic#
WHERE ss.sid = :target_sid
AND sn.name = 'SQL*Net roundtrips to/from client';Subtract the before value from the after value for the same session lifetime. The statistic is cumulative and includes other exchanges made by that session. Isolate the report interval and record unavoidable extra work. It counts Oracle Net exchanges, so do not calculate an exact value from the row count divided by the array size. A reconnect invalidates the old baseline. The reporting account does not need broad catalog access. Use a scoped privilege that can read these views.
Use application logs and current session and SQL evidence as the baseline. Verify the applicable edition, deployment, and entitlement before using licensed capabilities. AWR, ASH, ADDM, and SQL tuning tools have specific licensing conditions. Availability, or a successful query, does not establish entitlement.
See V$SESSION: session identity, state and wait fields, V$SESSTAT: session statistics, Database statistic descriptions: SQL*Net roundtrips to/from client, and Licensing Information: feature and management-pack eligibility.
Test one reversible client change
ARRAYSIZE controls the SQL*Plus fetch batch size. Where repeated fetch exchanges contribute to delay, larger batches can reduce that overhead. The effect depends on the SQL*Plus and database versions, the network, and the workload. Recent versions may show little improvement. Very large settings can also hurt performance through client resource use.
First record the actual client settings:
SHOW ARRAYSIZE
SHOW ROWPREFETCH
SHOW TIMINGThen, in the same SQL*Plus client:
SET ARRAYSIZE 100The trial changes the fetch setting in that SQL*Plus process. It does not change table contents, table grants, or an Oracle instance parameter. Preserve the original ARRAYSIZE for reversal. Keep ROWPREFETCH, query text, inputs, data, output destination, formatting, client host, network route, and workload conditions fixed. Separate or balance warm-up effects, and alternate or repeat matched runs. Do not flush shared caches or change database-wide settings to manufacture a comparison.
If you enable SET TIMING ON for additional client timing, record it and later restore the previous setting. Preserve an external or application measurement of the full-fetch and output interval too, and report exactly what each timer includes. Use the identical report script and fetch all rows in both variants. A COUNT(*) replacement would measure a different workload.
Compare the matched runs
These figures fix one comparison. Both variants return the same rows. The trial time is inside the limit. Impact on other work still has to be measured.
| Measure | Baseline | Trial | Requirement |
|---|---|---|---|
| Fully returned rows | 10,000 | 10,000 | Same expected rows and values |
| Full fetch time | 2.4 seconds | 1.1 seconds | At most 1.5 seconds under the agreed workload |
| Other workload impact | Requires measurement | Requires measurement | Within the agreed tolerance |
The trial meets the elapsed-time threshold. Equal row counts still require representative values and a complete result comparison. They cannot establish data correctness alone. Record repeated-run variability, round-trip deltas, plan and wait context, and any resource or workload regression. Accept the change only after every agreed criterion passes.
If there is no material benefit, the results differ, or another workload regresses, restore the previously recorded ARRAYSIZE and repeat the checks. Only if the saved value was 15:
SET ARRAYSIZE 15
SHOW ARRAYSIZERestore any other client settings changed for measurement. Keep the unsuccessful hypothesis, the measurements, and the restoration result in the incident record. That evidence helps select the next check. The unresolved problem remains open. SQL*Plus settings are reversed through their own settings, not through SQL transaction rollback.
See SQL*Plus tuning: client fetch settings, network effects and timing and SQL*Plus SET variables: ARRAYSIZE, ROWPREFETCH and TIMING.
Record the complete handoff
A bounded access and performance decision is one part of the record. The handoff includes:
- Environment and eligibility: topology, actual release and release update, database, container, and service map, operator privileges, and feature-entitlement boundaries.
- Access: role and object-grant matrix, the actual successful allowed action, the actual denied action, relevant audit evidence, and the grant restoration result.
- Data movement: source and destination identities, expected object counts, actual counts by object type, representative keyed rows, required complete comparisons, and constraint states. Investigate missing or invalid objects and disabled or unvalidated constraints. Preserve source data and use the data-movement procedure that matches the method already chosen.
- Incident reasoning: timestamped observations, competing causes, checks that distinguish them, rejected alternatives, and remaining uncertainty.
- Workload baseline: query and inputs, interval, full-fetch boundary, returned results, wait and plan evidence, concurrency, and user impact.
- Change: approval and owner, recorded prior state, one bounded adjustment, acceptance limits agreed before the trial, actual before and after evidence, and a measured restoration result where reversal was needed.
- Operational readiness: patch inventory and rehearsal result, recovery readiness, alert destination and owner, and a deliberately induced automation failure in a disposable test, with its exit status, log, alert, and escalation evidence.
- Handoff: redacted runbook, executed versus documented-only versus unverified steps, remaining risk, next action and owner, and a peer's independent reproduction result.
When you check data movement, inspect objects and constraints. ALL_OBJECTS exposes accessible objects and their STATUS. ALL_CONSTRAINTS includes STATUS and VALIDATED. Visibility must cover the whole object set. Counts alone can conceal missing and extra objects that cancel each other out, so compare the expected names and types too. Row-count equality can conceal changed values. A constraint marked ENABLED but NOT VALIDATED does not establish that every existing row was checked.
A peer should be able to identify the intended service, reproduce the baseline, interpret the decision, and locate the reversal instructions without asking the original author. Redact credentials, private endpoints, customer data, and unrelated log content, and keep the evidence the next operator needs.
See ALL_OBJECTS: object visibility and status and ALL_CONSTRAINTS: enabled and validated states.
Recap
A reporting failure is two problems until evidence separates them. Record the user, database, container, service, error, time, and business impact. SYS_CONTEXT identifies the current session. Compare that identity with the recorded topology and service map before treating the connection as the intended one.
Scope the grant to the missing action. When SHOP.ORDERS is the required object and SELECT is the missing privilege, GRANT SELECT ON shop.orders TO reporting is the direct grant. A subset requirement needs a view or policy first. Test the allowed read and a denied write, and keep audit evidence that was actually configured. An unexplained DBA grant, a guessed session kill, or an unentitled advisor does not answer a narrow read. REVOKE removes only that direct grant. Grant and revoke are DDL, so rollback does not restore the previous grants.
Performance causes need checks that can tell them apart. Sample V$SESSION across the interval. If STATE is not WAITING, the event can be the last wait. The round-trip statistic is cumulative for the session lifetime and is not the row count divided by the array size. Confirm edition, deployment, and entitlement before AWR, ASH, ADDM, or SQL tuning tools.
ARRAYSIZE changes the fetch batch in that SQL*Plus process only. Record ARRAYSIZE, ROWPREFETCH, and TIMING, then change one setting and fetch the same rows. In the matched figures, both runs return 10,000 rows and the trial time falls from 2.4 seconds to 1.1 seconds, inside the 1.5-second limit. Other workload impact still needs a measurement, and equal counts do not prove the values. If the trial does not help, restore the recorded ARRAYSIZE and leave the problem open.
The handoff names the environment, the access tests, the data-movement comparison, the competing causes, the workload baseline, the one change, operational readiness, and which steps were executed, documented only, or still unverified. Compare object names and types, not counts alone. An ENABLED constraint that is NOT VALIDATED has not checked every existing row. A peer should find the service, the baseline, the decision, and the reversal without asking the author.
No comments:
Post a Comment