1. Introduction
Modern enterprise databases must balance two critical priorities: strong security and optimal performance. Database administrators are responsible not only for protecting data but also for ensuring efficient query execution, workload stability, and proactive monitoring.
This document provides a practical, hands-on guide to Oracle Database SecurityHardening and Performance Tuning within a multi-tenant (CDB/PDB) architecture. It covers key administrative domains including user and role management, database security controls, DBSAT assessment, SQL tuning, AWR analysis, and ADDM recommendations.
The goal of this guide is to present a real-world, production-oriented approach that reflects actual Oracle DBA responsibilities. It demonstrates how to secure database environments, assess risks, monitor workload behavior, and optimize SQL performance using native Oracle tools.
Based on practical implementation scenarios, this documentation serves as a structured reference for Oracle DBAs and learners who want a clear understanding of both database hardening and performance optimization in modern enterprise environments.
2. Objective
The primary objective of this document is to:
Implement database security hardening best practices
Perform privilege and role management
Assess database security posture using DBSAT
Identify and resolve SQL performance issues
Analyze database workload using AWR and ADDM
Improve overall database performance and stability
Provide a structured, real-world DBA reference guide
3. Scope
This document covers:
User and role management best practices
Privilege control and least privilege model
Database security hardening techniques
Oracle Database Security Assessment Tool (DBSAT) usage
SQL performance tuning techniques
AWR report analysis
ADDM diagnostics and recommendations
End-to-end DBA operational workflow
4. Environment Details
Component Details
Database Architecture Multi-tenant (CDB/PDB)
CDB Name PROD
PDBs PRODPDB, TESTPDB
Storage ASM Disk Groups
Deployment Clustered Database Environment
OS User oracle
Monitoring Tools AWR, ADDM, ASH
Security Tool DBSAT
5. Prerequisites
Before implementation, ensure the following:
Oracle Database installed and configured
Multi-tenant architecture enabled (CDB/PDB)
SYSDBA privileges available
AWR snapshots enabled
Diagnostic Pack licensed (for AWR/ADDM usage)
DBSAT utility downloaded and configured
OS-level access for running DBSAT collector
Basic understanding of Oracle performance views
6. Use Cases
This implementation is useful in the following scenarios:
Database security audits
Pre-production hardening
Compliance readiness (ISO, SOC, etc.)
Performance troubleshooting
Production health checks
Migration readiness validation
DBA knowledge building and documentation
7. Methodology
The implementation follows a structured and layered approach:
1. Secure user and role configuration
2. Apply database security hardening measures
3. Run DBSAT to assess security posture
4. Identify risky privileges and sensitive data exposure
5. Generate and analyze AWR reports
6. Identify top SQL performance bottlenecks
7. Use ADDM findings for root cause analysis
8. Apply SQL tuning and configuration improvements
9. Validate performance improvements
8. Implementation Steps
This section describes the practical implementation workflow for Oracle Database Security Hardening and Performance Tuning in a multi-tenant environment.
Environment Validation and Database Readiness Check
All critical components were validated successfully:
✅ Clusterware healthy
✅ RAC nodes operational
✅ ASM disk groups mounted
✅ Database instances running on both nodes
✅ Multi-tenant architecture validated
✅ PDBs open and operational
The environment is confirmed as:
Production-ready and stable for security hardening and performance tuning implementation.
8.1: User and Role Management
1. Overview
The first phase of database security hardening focuses on implementing strong identity and access management using the least privilege principle. Proper user and role management ensures that only authorized users can access database resources with minimal required privileges.
In multi-tenant architectures, access control must be carefully designed at both the Container Database (CDB) and Pluggable Database (PDB) levels to prevent privilege escalation and reduce attack surfaces.
2. Objective
The objective of this phase is to:
Implement secure password policies
Enforce role-based access control (RBAC)
Create application users with minimal privileges
Separate system users and application users
Enforce security controls at the PDB level
This ensures controlled and auditable database access.
3. Why This Is Important
Improper user management is one of the most common security risks in Oracle environments. Overprivileged users can lead to:
Unauthorized data access
Privilege escalation
Compliance violations
Increased attack surface
Implementing RBAC and password policies ensures a secure and compliant environment.
4. Implementation Steps
Step 1: Switch to Target PDB
User and role creation was performed at the pluggable database level to isolate application access.
This ensures that the security configuration is applied locally within the PDB.
Step 2: Create Secure Password Profile
A strong password profile was created to enforce authentication policies.
Security Controls Implemented:
Account lock after 5 failed logins
Mandatory password rotation (90 days)
Password reuse restrictions
Complexity validation using Oracle verify function
This enforces enterprise-grade password security.
Step 3: Create Application User
A dedicated application user was created with controlled resource usage.
This ensures:
Resource isolation
Password policy enforcement
Controlled tablespace usage
Step 4: Grant Minimum Required Privileges
The user was granted only the required login privilege.
This follows the least privilege model.
Step 5: Implement Role-Based Access Control (RBAC)
A read-only role was created to avoid direct privilege assignment.
Benefits of RBAC:
Centralized privilege management
Easier audits
Simplified privilege revocation
Improved scalability
5. Validation
The user and role creation were verified successfully at the PDB level.
Key validations performed:
Profile assigned correctly
Role inheritance confirmed
Session creation privileges validated
This confirms successful RBAC implementation.
6. Best Practices Followed
Created users at PDB level (not CDB root)
Implemented strong password policies
Used role-based privilege assignment
Avoided direct system privileges
Followed least privilege model
These practices align with enterprise Oracle security standards.
7. Key Outcome
After this implementation:
✅ Secure authentication enforced
✅ Controlled application access
✅ Role-based privilege model implemented
✅ Reduced privilege exposure
This establishes a strong identity and access management foundation for further hardening.
8.2: Security Hardening
1. Overview
Account hardening is a critical step in Oracle database security that focuses on minimizing the attack surface by disabling unused, default, and sample accounts. Many Oracle installations include pre-created schemas and legacy accounts that may pose security risks if left enabled.
This implementation ensures that only necessary and actively used accounts remain accessible across both Container Database (CDB) and Pluggable Databases (PDBs).
2. Objective
The objective of account hardening is to:
Identify Oracle-maintained and custom accounts
Lock unused default accounts
Disable legacy schemas
Harden both CDB and PDB layers
Prevent unauthorized lateral access
This significantly reduces database exposure.
3. Why Account Lockdown Is Important
Unused accounts are a common entry point for attackers due to:
Default credentials
Weak password policies
Legacy dependencies
Misconfigurations
Locking unused accounts ensures:
Reduced attack surface
Better compliance readiness
Improved audit posture
Stronger access governance
4. Implementation Steps
Step 1: Identify Oracle-Maintained Accounts
Switched to root container to identify Oracle-managed users.
This distinguishes system-managed accounts from user-created accounts.
Step 2: Identify Non-Oracle Accounts
Custom users were identified for review.
This helped identify:
Application schemas
Test users
Sample schemas (HR, SCOTT)
Step 3: Lock Unused System Accounts
Non-required system accounts were locked at the CDB level.
This prevents unauthorized use of legacy accounts.
Step 4: Lock Sample Schemas in PDB
Switched to the target PDB and locked sample users.
This eliminates risks from:
Demo schemas
Default passwords
Legacy objects
Step 5: Cross-Container Validation
Performed container-wide validation to ensure consistent lockdown.
This confirmed account lockdown across:
CDB$ROOT
PRODS
TESTPDB
5. Best Practices Followed
Locked unused default accounts
Disabled sample schemas
Hardened both CDB and PDB levels
Verified lockdown across containers
Avoided locking critical Oracle-managed accounts blindly
6. Key Outcome
After implementing account hardening:
✅ Reduced attack surface
✅ Disabled legacy and unused accounts
✅ Eliminated risks from sample schemas
✅ Enforced container-wide lockdown
This establishes a secure baseline before enabling auditing and compliance controls.
8.3: Database Auditing
1. Overview
Database auditing provides visibility into user activities and privileged operations. It enables tracking of security-sensitive actions such as configuration changes, user management, and data access, which is essential for compliance and forensic analysis.
In this implementation, traditional Oracle auditing was configured to monitor critical database activities.
Unified Auditing is centralized, efficient, and easier to manage, while Traditional Auditing is older, fragmented, and less performant.
2. Objective
The objective of auditing implementation is to:
Validate current auditing mode
Enable auditing for privileged operations
Track user and schema changes
Monitor sensitive data access
Establish audit visibility for compliance
This improves traceability and accountability.
3. Auditing Strategy
The auditing approach focused on:
Privileged operations monitoring
User lifecycle auditing
Data access auditing
Minimal performance overhead
This ensures balanced security and performance.
4. Implementation Steps
Step 1: Verify Auditing Mode
Validated whether Unified Auditing is enabled.
Result:
Unified Auditing was disabled.
Step 2: Check Traditional Auditing Configuration
Verified database auditing parameter.
Result:
audit_trail = DB
Traditional auditing enabled
This confirms audit records are stored in database tables.
Step 3: Enable Statement Auditing
Enabled auditing for critical administrative actions.
These actions help detect:
Unauthorized configuration changes
Suspicious user creation
Privilege misuse
Step 4: Enable Data Access Auditing
Enabled auditing for table access.
This ensures visibility into sensitive data access patterns.
Step 5: Validate Audit Configuration
Audit settings were verified using data dictionary views.
Validation confirmed:
Auditing enabled for both success and failure
Coverage for high-risk operations
5. Best Practices Followed
Verified auditing mode before configuration
Enabled targeted auditing (not blanket auditing)
Balanced visibility and performance
Audited high-risk operations first
Validated audit policies post-implementation
6. Key Outcome
After implementing auditing:
✅ Visibility into privileged operations
✅ Traceability for user management actions
✅ Monitoring of sensitive data access
✅ Improved compliance readiness
✅ Stronger forensic capabilities
This completes the foundational database security layer.
8.4: Run DBSAT Assessment
Oracle's Database Security Assessment Tool (DBSAT) is a utility designed to help database administrators evaluate the security posture of Oracle databases. It performs a comprehensive security scan and generates reports highlighting configuration issues, account weaknesses, and auditing gaps.
1.Overview
DBSAT provides:
Analysis of database configuration against security best practices.
Identification of weak passwords, user privileges, and roles.
Review of auditing and system privileges.
Exportable reports in HTML, TXT, JSON, and XLSX formats.
DBSAT supports both on-premises and cloud databases (like Oracle 19c in a multi- tenant environment).
2 Installation & Setup
1. Prepare Environment
Ensure Java 1.8 is installed and set:
2. Unpack DBSAT
3 Running DBSAT Collection
Execute the collector to scan the database:
Notes:
The collector connects as SYSDBA to retrieve configuration, user, privilege, and audit information.
Some OS-level checks may be skipped depending on environment permissions.
Output is generated in a compressed .zip file (dbsat_out.zip) and can be encrypted with a password.
4 Generating DBSAT Reports
After collection, generate the security report:
Reports are created in multiple formats:
dbsat_out_report.html — Viewable in any browser.
dbsat_out_report.txt — Plain text summary.
dbsat_out_report.xlsx — Excel for analysis.
dbsat_out_report.json — Machine-readable format.
The report can be transferred to a Windows workstation using WinSCP, FileZilla, or similar tools to review the HTML or TXT output.
5 Benefits of DBSAT
Provides comprehensive security assessment without manual querying.
Identifies misconfigurations and excessive privileges.
Helps ensure compliance with company and regulatory security policies.
Generates readable and actionable reports for auditors or management.
6 Best Practices
Run DBSAT regularly as part of database security audits.
Use the generated HTML/Excel reports to track remediation progress.
Avoid running on production during peak hours to reduce load.
Encrypt output files to protect sensitive information.
Security Remediation
Based on DBSAT findings, remediation is performed.
Common Remediation Tasks
Revoke excessive privileges
Mask sensitive data exposure
Implement auditing controls
Remove insecure configurations
Enforce password complexity
Example
REVOKE ANY TABLE FROM app_user;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;
Validation
Re-run DBSAT
Compare risk reduction
8.5: AWR Report Generation
AWR (Automatic Workload Repository) captures and stores performance statistics for Oracle databases. AWR reports help DBAs identify performance issues, workload patterns, and resource bottlenecks.
1.Overview
AWR (Automatic Workload Repository) is an Oracle database performance monitoring and tuning tool that collects, processes, and maintains performance statistics. It provides detailed insights into database workloads, helping DBAs identify bottlenecks and optimize system performance.
2.Objectives
Track database performance trends over time.
Identify high-load SQL statements and resource bottlenecks.
Support proactive performance tuning and capacity planning.
Provide historical performance data for troubleshooting.
3.Why Use AWR
Helps in diagnosing performance issues quickly.
Enables data-driven decisions for tuning SQL, memory, and I/O.
Facilitates trend analysis and historical comparisons.
Required for Enterprise Manager performance dashboards.
4.How It Works
1. AWR captures snapshots of database performance statistics periodically (default every 60 minutes).
2. Snapshots are stored in the SYSAUX tablespace within the DBA_HIST views.
3. Reports are generated by selecting begin and end snapshot IDs for a given time period.
4. Output formats: HTML, text, active-html.
5.Use Case
Multi-node RAC database experiencing intermittent performance degradation.
DBA wants a 7-day performance summary and to identify which SQL statements
or resources are consuming the most time.
8.5.6 Implementation Steps
Report is generated in HTML format.
Transfer to local machine via WinSCP for review in a browser.
7 Best Practices
Run AWR reports periodically (weekly or monthly) for trend analysis.
For RAC, include all instances for complete performance visibility.
Archive AWR reports for historical reference.
Focus on top SQL, wait events, and load statistics.
8 Key Outcomes
Identifies high-load SQL and performance bottlenecks.
Provides resource utilization insights for tuning.
Helps in forecasting system scaling requirements.
Serves as a reference for ADDM analysis.
8.6: ADDM Analysis
1.Overview
ADDM (Automatic Database Diagnostic Monitor) analyzes AWR snapshots to provide actionable recommendations for improving Oracle database performance. ADDM highlights root causes of performance issues, eliminating manual analysis complexity.
2.Objectives
Automatically diagnose database performance issues.
Recommend tuning actions for SQL, memory, and I/O.
Quantify impact of performance problems.
Prioritize issues based on resource consumption.
3.Why Use ADDM
Reduces time and effort spent on manual performance analysis.
Provides instance-level and RAC-wide insights.
Helps proactively optimize system before end-user impact.
Supports capacity planning with workload impact projections.
4.How It Works
1. ADDM reads AWR snapshots and calculates metrics such as:
o Wait times
o SQL execution times
o I/O and memory statistics
2. It generates recommendations such as:
o SQL tuning
o Index suggestions
o Memory allocation adjustments
o Load balancing across RAC nodes
3. Reports are generated as text or HTML files.
5.Use Case
RAC database showing high CPU usage on prod1 node.
DBA runs ADDM for snapshots 100 → 103 to understand performance degradation
and identify actionable recommendations.
6.Implementation Steps
Output: addm_rac_rpt.txt
Transfer to local machine for detailed review.
7.Best Practices
Always run AWR before ADDM to have snapshot data.
Use ADDM reports to implement recommendations incrementally.
Archive reports for audit and trend analysis.
Prioritize high-impact recommendations first (CPU, I/O, top SQL).
8.Key Outcomes
Provides root-cause analysis for performance issues.
Offers practical tuning recommendations.
Highlights system bottlenecks for proactive action.
Improves RAC instance balance and resource utilization.
AWR Remediation
Overview: Analyze database performance using AWR reports to identify bottlenecks.
Objective: Improve SQL, memory, and I/O efficiency.
Common Tasks:
Tune high-load SQL
Adjust SGA/PGA memory
Optimize I/O and storage
Balance RAC instance load
Example:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
CREATE INDEX idx_emp_dept ON emp(department_id);
ALTER SYSTEM SET sga_target=4G SCOPE=BOTH;
Validation:
Re-run AWR reports
Check CPU, I/O, and SQL improvements
ADDM Remediation
Overview: Use ADDM to get actionable performance recommendations.
Objective: Apply targeted fixes to reduce wait events and improve throughput.
Common Tasks:
Fix top wait events
Tune SQL statements
Adjust memory parameters
Implement ADDM suggested changes
Example:
-- Apply ADDM recommendations
EXEC
DBMS_ADVISOR.IMPLEMENT_RECOMMENDATIONS('ADDMSQL_RECOMMEN
D');
Validation:
Re-run ADDM analysis
Compare improvements in response time and resource usage
8.7: SQL Tuning
Overview:
SQL Tuning improves query performance by reducing execution time, optimizing resource usage, and ensuring consistent execution plans.
Objective:
Reduce CPU, I/O, and memory usage
Minimize hard parses
Ensure consistent and efficient execution plans
Why SQL Tuning:
Even the same SQL can generate different PLAN_HASH_VALUEs due to changes in:
Table/index statistics
Execution plan changes (optimizer decisions)
Bind variable usage vs literals
Use Case:
High OLTP transaction systems
Reporting queries with large datasets
Queries showing variable execution times
Implementation Steps:
All SQL statements have to go through the following stages of execution:
1.Parse
a.syntactic
b.semantic check
2.Bind
3.Execute
4.Fetch
1) PARSE: Every SQL statement has to be parsed which includes checking the syntax, validating, ensuring that all references to objects are correct and ensuring that relevant privileges to those object exist.
Parsing -
1.syntactic
2.semantic check
u1-sql> select empname, job, sal, manager from emp where deptno=20;
syntactic check - standard for writing query
semantic check - object, users and their permission exist in DD
(The semantics check to confirm that the correct object name is used and also whether the user has the proper privileges to execute the query)
Hard Parse and Soft Parse –
If there is a fresh(new) query and its hash code does not exist in shared pool then that query has to pass through from the additional steps known as hard parsing otherwise if hash code exists then query does not passes through additional steps. It just passes directly to execution engine. This is known as soft parsing.
2) BIND: After parsing, the oracle server knows the meaning of the oracle statement but still may not have enough info(values for variables) to execute the statement. The process of obtaining these value is called as bind values.
SQL> select emp, ename, sal from emp where empno=7099;
SQL> select emp, ename, sal from Emp where empno=7900;
SQL> select emp, ename, Sal from emp where deptno=8712;
SQL> variable deptno number
SQL> exec :deptno := 10 (exec : deptno := text1.value)
SQL> select emp, ename, sal from emp where deptno = :deptno;
3) EXECUTE: After binding, the Oracle server executes the statement.
4) FETCH: In the fetch stage, rows are selected and ordered and each successive row retrieves another row of the result until the last row has been fetched. This stage is only for certain DQL statements like SELECT.
Whenever a SQL statement is executed, Oracle Engine performs below actions on it:
1. Validate the Syntax
2. Validate the objects being referenced in the statement
3. Privileges assigned to user executing the Job
4. Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value for the SQL statement and look in
select empno, ename from emp where deptno=10;
5. If statement is not already present then allocate shared memory and create a cursor in shared pool
6. Generate the Execution Plan
SQL_ID - A unique 13-character alphanumeric string that identifies a normalized SQL statement.
HASH_VALUE - What it is: A numeric hash of the SQL text, generated using Oracle's internal algorithm.
PLAN_HASH_VALUE - A numeric hash representing the execution plan structure.
Purpose: Identifies whether a SQL is using the same or different execution plan over time.
Usage: Helps in performance tuning, especially when the same SQL has multiple execution plans (adaptive, stats change, bind variables, etc.)
select empno, ename, job, sal from emp;
select empno, ename, job, sal from Emp;
select empno, Ename, job, sal from emp;
select empno, ename, job, sal from emP;
select empno, ename, job, sal from emp where empno=7839;
select sql_id, address, hash_value, plan_hash_value from v$sqlarea where
sql_text=<string to identify my query>
select sql_id, SQL_TEXT, address, hash_value, plan_hash_value from
v$sqlarea where sql_text like '%select empno%';
set lines 200
set pages 20
col SQL_TEXT for a60
select sql_id, SQL_TEXT, plan_hash_value from v$sqlarea where sql_text
like '%select empno%';
SQL> alter system flush shared_pool;
System altered.
sql> select empno, ename, job, sal from emp;
SQL> select sql_id, address, hash_value, plan_hash_value from v$sqlarea
where sql_text='select empno, ename, job, sal from emp';
SQL_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE
------------- ---------------- ---------- ---------------
1jvn42p3gccuc 0000000076986C10 1190540108 3956160932
SQL> Select empno, ename, job, sal from EMP;
SQL> select sql_id, address, hash_value, plan_hash_value from v$sqlarea
where sql_text='Select empno, ename, job, sal from EMP';
SQL_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE
------------- ---------------- ---------- ---------------
a6fs5r3mzr422 000000007EE6E9E0 3892023362 3956160932
Note:
The SQL ID is a hash of the text of the SQL statement.
Similarly, the hash_value is a numeric hash of the statement itself. You expect these to be the same every time you run a statement.
The plan_hash_value is a hash of the execution plan. This will change if you get a different plan.
Bind variable
Bind variables are «substitution» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.
select empno, ename, job, sal from emp where empno=7839; hash value
select empno, ename, job, sal from emp where empno=7840; hash value
In SQL*Plus you can use bind variables as follows:
SQL> variable deptno number
SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;
Note: At run time it will assign value to variable.
Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is known as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself.
The order of Query Execution is following
1)From Clause
2)Where Clause
3)Group By Clause
4)Having Clause
5 )Select
6)Order By Clause
how the same SQL can result in different PLAN_HASH_VALUEs due to plan changes in Oracle.
Creates the table
Inserts data
Runs SQL
create index on that table
Run SQL after index creation
Captures and compares execution plans
LAB PLAN_HASH_VALUEs
STEP 1: Drop table if exists
DROP TABLE plan_demo PURGE;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE plan_demo PURGE';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
STEP 2: Create table
CREATE TABLE plan_demo (
id NUMBER,
name VARCHAR2(50),
salary NUMBER
);
STEP 3: Insert sample data
BEGIN
FOR i IN 1 .. 100000 LOOP
INSERT INTO plan_demo VALUES (
i,
'Employee ' || i,
MOD(i, 5000) + 30000
);
END LOOP;
COMMIT;
END;
/
explain plan for SELECT * FROM plan_demo WHERE salary = 30001;
select * from table(DBMS_XPLAN.DISPLAY);
STEP 4: Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS('U1', 'PLAN_DEMO');
STEP 5: Run SQL before index (Expect FULL TABLE SCAN)
SELECT * FROM plan_demo WHERE salary = 30001;
STEP 6: Find SQL_ID and PLAN_HASH_VALUE
SELECT sql_id, plan_hash_value, sql_text
FROM v$sql
WHERE sql_text LIKE 'SELECT * FROM plan_demo WHERE salary =
30001%';
STEP 7: Show Execution Plan
explain plan for SELECT * FROM plan_demo WHERE salary = 30001;
select * from table(DBMS_XPLAN.DISPLAY);
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL,
'ALLSTATS LAST'));
STEP 8: Create index
CREATE INDEX idx_plan_salary ON plan_demo(salary);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,
'PLAN_DEMO');
STEP 9: Re-run the same SQL
SELECT * FROM plan_demo WHERE salary = 30001;
STEP 10: Check SQL_ID and PLAN_HASH_VALUE again
SELECT sql_id, plan_hash_value, sql_text
FROM v$sql
WHERE sql_text LIKE 'SELECT * FROM plan_demo WHERE salary =
30001%';
STEP 11: Show New Execution Plan
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL,
'ALLSTATS LAST'));
STEP 12: Clean up (Optional)
DROP TABLE plan_demo PURGE;
Best Practices:
Use bind variables to reuse SQL in shared pool (reduces hard parses)
Keep statistics up-to-date for optimizer efficiency
Avoid unnecessary full table scans; create proper indexes
Monitor SQL_ID and PLAN_HASH_VALUE for plan stability
Key Outcomes:
Faster query execution
Reduced CPU and I/O load
Consistent execution plans across runs
Better use of shared pool and caching
8.8 Session Locking and Blocking Analysis
Overview
In multi-user Oracle databases, concurrent sessions can block each other when accessing the same resources. Understanding session locks and blocking is critical for performance tuning and troubleshooting concurrency issues.
Objective
Identify blocking sessions, resolve locks, and ensure smooth transaction processing.
Why
Prevent long waits or application timeouts caused by uncommitted transactions.
Detect and resolve deadlocks or resource contention.
Maintain database concurrency and transactional consistency.
How
Query v$lock to find blocking and waiting sessions.
Query v$session to identify session details (SID, SERIAL#, USERNAME).
Kill or terminate blocking sessions if necessary.
Use Case
1. User U1 updates emp table and holds a lock.
2. User U2 tries to update the same row → session blocks.
3. SYS checks v$lock and v$session to find blocking session.
4. SYS kills U1’s session → lock released, U2 continues.
Implementation Steps
U1:
U2:
U2 hangs
Show blocking sessions
Identify session details
Kill blocking session
Confirm locks are released
Best Practices
Avoid long-running transactions on frequently updated tables.
Regularly monitor v$session and v$lock for blocking issues.
Use row-level locking instead of table-level where possible.
Communicate with users before killing sessions in production.
Key Outcomes
Quickly identify blocking sessions and resolve locks.
Reduce application wait times and prevent deadlocks.
Improve concurrency and transaction throughput.
Validation and Review
The final step validates both security and performance improvements.
Security Validation
Re-run DBSAT
Validate privilege models
Review audit settings
Performance Validation
Compare before/after AWR reports
Validate ADDM improvements
Monitor wait event reductions
Confirm improved SQL execution plans
Operational Checks
Validate across all PDBs
Monitor application behavior
Document baselines
Final Outcome
After implementation:
Hardened security posture
Reduced privilege risks
Optimized SQL execution
Improved concurrency handling
Proactive performance monitoring
Production-ready DBA baseline
9. Risks and Mitigation
Implementing database security hardening and performance tuning introduces certain operational and architectural risks. Proper planning and mitigation strategies are essential to ensure stability, compliance, and performance consistency.
1. Privilege Misconfiguration Risk
Risk:
Incorrect privilege revocation may break application functionality or administrative operations.
Mitigation:
Follow least privilege principle gradually
Perform privilege review in staging environment
Maintain rollback scripts for revoked privileges
Use privilege analysis tools before enforcement
2. Performance Impact from Security Controls
Risk:
Security measures such as auditing, encryption, or monitoring may introduce performance overhead.
Mitigation:
Enable auditing selectively
Monitor performance after security changes
Use AWR baselines before and after implementation
Apply controls incrementally
3. Misinterpretation of DBSAT Findings
Risk:
DBSAT reports may highlight risks that require contextual analysis, leading to over- hardening or unnecessary changes.
Mitigation:
Review findings based on business context
Prioritize high and critical risks first
Validate recommendations before implementation
Align remediation with security policies
4. SQL Tuning Regression Risk
Risk:
Improper SQL tuning or indexing may negatively impact execution plans or other
workloads.
Mitigation:
Capture execution plan baselines
Use SQL Plan Management (SPM) where applicable
Test tuning changes in non-production environments
Monitor performance after deployment
5. AWR/ADDM Licensing and Usage Risk
Risk:
Improper usage of AWR and ADDM without proper licensing may lead to compliance issues.
Mitigation:
Ensure Diagnostic Pack licensing compliance
Validate feature usage with licensing guidelines
Use Statspack as an alternative if required
6. Over-Hardening Risk
Risk:
Excessive security hardening may reduce operational flexibility or slow down troubleshooting.
Mitigation:
Maintain a balance between security and usability
Document security exceptions where required
Implement role-based administrative access
Follow phased hardening approach
7. Inconsistent Multi-tenant Configuration
Risk:
Security and performance configurations may differ across CDB and PDBs, leading to inconsistent behavior.
Mitigation:
Standardize configurations across containers
Validate settings at both CDB and PDB levels
Use common users and roles where applicable
Perform container-wide validation checks
8. Change Management Risk
Risk:
Untracked changes during hardening and tuning may lead to operational instability.
Mitigation:
Follow change management procedures
Maintain implementation documentation
Enable configuration auditing
Use version-controlled scripts
10. Conclusion
This implementation successfully demonstrated a comprehensive approach to Oracle Database Security Hardening and Performance Tuning through practical, real-world scenarios. The guide combined critical administrative domains including user and role management, security controls, DBSAT assessment, AWR and ADDM diagnostics, and SQL performance optimization into a unified operational workflow.
Through this hands-on implementation, essential security practices such as least privilege enforcement, privilege review, and database hardening were applied to strengthen theoverall security posture. The use of DBSAT provided deeper visibility into potential risks, sensitive data exposure, and privilege-related vulnerabilities, enabling informed remediationstrategies aligned with enterprise security standards.
On the performance side, AWR and ADDM were leveraged to analyze workload behavior, identify bottlenecks, and understand system performance patterns. SQL tuning techniques were then applied to optimize inefficient queries, improve execution plans, and enhance overall database responsiveness and stability.
This practical implementation highlights the importance of balancing security and performance in modern database environments. It reinforces the value of proactive monitoring, periodic security assessments, and continuous tuning as part of a mature Oracle DBA strategy.
Overall, this guide serves as a solid reference for Oracle DBAs seeking to implement structured security hardening and performance tuning practices. It provides a strong foundation for maintaining secure, stable, and high-performing Oracle database environments aligned with enterprise best practices and real-world operational requirements.
11. Limitations
This implementation is performed in a controlled lab environment designed to demonstrate practical Oracle database security and performance tuning concepts. While it effectively covers core administrative practices, it has certain limitations:
The implementation does not include enterprise security frameworks such as SIEM integration or centralized identity management
Real-time production workloads and large-scale concurrency patterns are not fully simulated
Advanced integrations like Data Guard security hardening, Zero Trust architecture, and cross-region deployments are not covered
Some performance diagnostics rely on licensed features such as AWR and ADDM
External security validation methods such as penetration testing or compliance audits are not included
Application-level performance tuning and code optimization are outside the scope of this document
These limitations are acceptable for learning and foundational implementation purposes but should be expanded upon when applying these practices in enterprise production environments.
No comments:
Post a Comment