This project is an interactive performance diagnostic script for Oracle databases. Its core capabilities include AWR report generation, ASH report extraction, and SQL Trace assistance. It addresses three common pain points: cross-version Oracle compatibility, error-prone snapshot selection, and cumbersome diagnostic workflows. Keywords: Oracle performance diagnostics, AWR, SQL Trace.
This is a lightweight diagnostic tool for multi-version Oracle operations
| Parameter | Description |
|---|---|
| Language | Python 3.6+ |
| Supported databases | Oracle 11g / 12c / 19c |
| Interaction model | CLI |
| Core protocol | Oracle database connection protocol / SQL*Net |
| Python drivers | oracledb, cx_Oracle |
| Report types | AWR, ASH, SQL Trace |
| Open source license | MIT |
| GitHub stars | Not provided in the source |
| Core dependencies | Oracle Instant Client, oracledb, or cx_Oracle |
The tool unifies common performance troubleshooting tasks behind a single entry point
In day-to-day Oracle operations, AWR supports system-wide performance analysis, ASH helps trace active sessions within a time window, and SQL Trace is better suited for deep analysis of a single session or an entire instance. Traditional workflows usually require switching between multiple command sets, handling version-specific function differences, and manually locating output files.
This project consolidates those tasks into a single Python script driven by an interactive menu. For DBAs, its value does not come from replacing native commands. Instead, it reduces repetitive low-value work, lowers the risk of operator error, and speeds up the diagnostic feedback loop.
The entry point is straightforward and easy to adopt
# Start the main script and enter the unified diagnostic menu
# Core logic: route a single entry point to AWR, ASH, and Trace capabilities
python Oracle-Performance-Collection-Tools.pyThis command launches the interactive diagnostic interface.
The AWR generation workflow strengthens snapshot selection and interval validation
The value of an AWR report comes from comparative system-level load analysis between two snapshots, but many invalid reports result from selecting the wrong snapshot interval. This tool first lists available snapshots, then validates the start and end IDs, and blocks input where the end value is smaller than the start value.
More importantly, it adds database restart detection. If the selected interval spans an instance restart, the resulting AWR report often loses its comparative value. The tool proactively rejects such intervals, improving report reliability at the source.
A typical AWR generation workflow looks like this
# View snapshots from the last 24 hours and select an interval
# Core logic: filter snapshots first, then validate the start and end SNAP_ID values
How many hours of snapshots do you want to display?: 24
Enter the starting snapshot ID: 57
Enter the ending snapshot ID: 58
Report format (html/text, default html): htmlThis workflow shows a typical interaction for generating an AWR report from a selected snapshot range.
The ASH reporting capability balances flexible time input with version compatibility
ASH analysis focuses more on time windows, so the tool supports two input modes: absolute time and relative time. Expressions such as 1h and 15m make ad hoc troubleshooting more efficient, especially when you need to investigate a performance spike that just occurred.
The project also handles differences in the DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_* function signatures across Oracle 11g, 12c, and 19c, and automatically retrieves the DBID and instance number. This means users do not need to maintain separate scripts for different Oracle versions.
Relative time mode is better suited for replaying recent incidents
# Generate an ASH report using relative time
# Core logic: convert expressions like 1h and 15m into an actual time range
Select input mode: 2
Relative start time: 1h
Relative end time: 0m
Report format (html/text, default html): htmlThis flow quickly generates an ASH activity report for the last hour.
The SQL Trace module covers both session-level and instance-level diagnostics
SQL Trace is a key technique for identifying slow SQL, wait events, and execution plan issues, but in practice it often involves multiple entry points such as ALTER SESSION, DBMS_SESSION, DBMS_SYSTEM, and DBMS_MONITOR. This tool standardizes them into three tracing modes.
The first mode traces the current session and is useful for personal debugging. The second mode traces a specified session and uses SID and SERIAL# to target a business connection precisely. The third mode traces an entire instance, which is useful for complex incidents, but the tool also clearly warns about the risks to avoid enabling expensive global tracing by mistake.
Specified session tracing is one of the most practical features for production diagnostics
-- Trace a target session by SID and SERIAL#
-- Core logic: enable Trace only for the specified connection to avoid broad impact
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(42, 9876, TRUE);This example demonstrates the idea of enabling SQL Trace for a specific target session.
The environment has minimal dependencies, but permission boundaries must be confirmed in advance
The runtime requirements are simple: Python 3.6+, an Oracle client or a configured ORACLE_HOME, and either the oracledb or cx_Oracle driver. For most operations hosts, deployment is low friction.
However, permissions are the real success factor in practice. AWR and ASH generally require SELECT_CATALOG_ROLE or access to DBA_HIST_* views, while cross-session tracing often requires even higher privileges. If privileges are insufficient, even a well-designed tool cannot produce useful output.
Installing the oracledb driver first is recommended
# Install the officially recommended driver
# Core logic: prefer the newer driver for better compatibility and maintainability
pip install oracledbThis command installs the Python database driver recommended by the project.
Preset environment variables make batch troubleshooting more efficient
The project supports pre-filling connection parameters through environment variables, including the username, host, port, service name, and report directory. This is highly practical for DBAs who repeatedly connect to the same group of databases, because it reduces repetitive input and makes automation easier to integrate.
If you later want to evolve the tool into a batch diagnostic platform, this preset model is also a natural transition layer. Standardize parameters in the CLI first, then gradually evolve toward a job-based, orchestrated inspection system for easier adoption.
The project works best as a standardized scaffold for Oracle troubleshooting
From a product positioning perspective, this is not a graphical platform. It is a highly practical diagnostic scaffold. It standardizes the most common, most error-prone, and most time-consuming parts of Oracle performance troubleshooting, making it especially suitable for individual DBAs, small operations teams, and temporary incident response.
If you frequently generate AWR and ASH reports manually, or temporarily enable SQL Trace, this open source project can significantly shorten the operational path. For teams that want to formalize internal diagnostic standards, it also offers solid secondary development potential.
AI Visual Insight: This animated image shows a social sharing prompt on a blog page. It does not illustrate the project architecture, terminal interface, or database diagnostic workflow, so it provides limited technical insight into the tool itself. You can treat it as platform-level supplementary media rather than a product feature screenshot.
FAQ
1. What advantages does this tool offer over running AWR and ASH commands manually?
Its core advantages are a unified entry point, automatic validation, and built-in version compatibility handling. You do not need to remember function differences across Oracle versions or manually determine whether a snapshot interval is valid.
2. Can this tool be used directly in production?
Yes, it can be used for production troubleshooting, but only if you strictly control privileges and the scope of tracing. Instance-level Trace in particular must be executed only with a clear understanding of the risk and within an approved maintenance window.
3. Should I use oracledb or cx_Oracle?
oracledb is the preferred choice. It is the more mainstream Python Oracle driver today, with better long-term compatibility and maintenance expectations. If your legacy environment is already tied to cx_Oracle, you can continue using it.
[AI Readability Summary]
This is an interactive Python-based performance diagnostic tool for Oracle 11g, 12c, and 19c. It unifies AWR report generation, ASH reporting, and SQL Trace workflows, and addresses version differences, snapshot validation, and report collection complexity. It is well suited for DBAs and database operations teams that need fast, standardized troubleshooting.