Name		Name	Last commit message	Last commit date
979-8-8688-3263-5.jpg		979-8-8688-3263-5.jpg
Contributing.md		Contributing.md
LI95D0~1.SQL		LI95D0~1.SQL
LICENSE.txt		LICENSE.txt
LIDB97~1.SQL		LIDB97~1.SQL
Listing 1-1. Querying ASH buffer time range.sql		Listing 1-1. Querying ASH buffer time range.sql
Listing 1-2. Retrieving hidden ASH parameters.sql		Listing 1-2. Retrieving hidden ASH parameters.sql
Listing 1-3. Influence of CONTROL_MANAGEMENT_PACK_ACCESS on ASH access.sql		Listing 1-3. Influence of CONTROL_MANAGEMENT_PACK_ACCESS on ASH access.sql
Listing 1-4. Influence of CONTROL_MANAGEMENT_PACK_ACCESS on AWR snapshot.sql		Listing 1-4. Influence of CONTROL_MANAGEMENT_PACK_ACCESS on AWR snapshot.sql
Listing 1-5. Querying usage statistics for ADDM and AWR.sql		Listing 1-5. Querying usage statistics for ADDM and AWR.sql
Listing 10-1. Monitoring system-wide I-O latency, throughput, and IOPS.sql		Listing 10-1. Monitoring system-wide I-O latency, throughput, and IOPS.sql
Listing 10-2. Calculating synchronous single-block read latency by file type.sql		Listing 10-2. Calculating synchronous single-block read latency by file type.sql
Listing 10-3. Querying ASH operation flags to compare parsing and SQL execution activity.sql		Listing 10-3. Querying ASH operation flags to compare parsing and SQL execution activity.sql
Listing 10-5. Setting CURSOR_SHARING=FORCE for a specific application user.sql		Listing 10-5. Setting CURSOR_SHARING=FORCE for a specific application user.sql
Listing 11-1. Reconstructing contended ROWIDs from historical ASH samples.sql		Listing 11-1. Reconstructing contended ROWIDs from historical ASH samples.sql
Listing 11-2. Displaying historical execution plans from AWR.sql		Listing 11-2. Displaying historical execution plans from AWR.sql
Listing 11-3. Checking current indexes on the ADDRESSES table.sql		Listing 11-3. Checking current indexes on the ADDRESSES table.sql
Listing 11-4. Tracing SQL plan changes across AWR snapshots.sql		Listing 11-4. Tracing SQL plan changes across AWR snapshots.sql
Listing 11-5. Narrowing the SQL plan transition with historical ASH.sql		Listing 11-5. Narrowing the SQL plan transition with historical ASH.sql
Listing 11-6. Querying the historical Resource Manager plan.sql		Listing 11-6. Querying the historical Resource Manager plan.sql
Listing 11-7. Querying DEFAULT_MAINTENANCE_PLAN directives.sql		Listing 11-7. Querying DEFAULT_MAINTENANCE_PLAN directives.sql
Listing 11-8. Querying historical Resource Manager consumer group statistics.sql		Listing 11-8. Querying historical Resource Manager consumer group statistics.sql
Listing 11-9. Removing the Resource Manager plan from the Sunday maintenance window.sql		Listing 11-9. Removing the Resource Manager plan from the Sunday maintenance window.sql
Listing 3-1. Querying recent ASH samples from V$ACTIVE_SESSION_HISTORY.sql		Listing 3-1. Querying recent ASH samples from V$ACTIVE_SESSION_HISTORY.sql
Listing 3-10. Session activity summary by type.sql		Listing 3-10. Session activity summary by type.sql
Listing 3-11. Creating an hourly activity timeline.sql		Listing 3-11. Creating an hourly activity timeline.sql
Listing 3-12. Analyzing SQL execution patterns by day.sql		Listing 3-12. Analyzing SQL execution patterns by day.sql
Listing 3-13. Analyzing workload distribution across RAC instances.sql		Listing 3-13. Analyzing workload distribution across RAC instances.sql
Listing 3-14. Identifying SQL during I-O spike.sql		Listing 3-14. Identifying SQL during I-O spike.sql
Listing 3-15. Counting sessions impacted by slow commits at noon yesterday.sql		Listing 3-15. Counting sessions impacted by slow commits at noon yesterday.sql
Listing 3-16. Identifying objects related to sequential reads.sql		Listing 3-16. Identifying objects related to sequential reads.sql
Listing 3-17. Top SQL with wait class breakdown.sql		Listing 3-17. Top SQL with wait class breakdown.sql
Listing 3-18. Analyzing transaction enqueue waits.sql		Listing 3-18. Analyzing transaction enqueue waits.sql
Listing 3-2. Querying recent ASH samples from DBA_HIST_ACTIVE_SESS_HISTORY.sql		Listing 3-2. Querying recent ASH samples from DBA_HIST_ACTIVE_SESS_HISTORY.sql
Listing 3-3. Identifying top SQL statements.sql		Listing 3-3. Identifying top SQL statements.sql
Listing 3-4. Identifying top wait events.sql		Listing 3-4. Identifying top wait events.sql
Listing 3-5. Identifying top accessed objects.sql		Listing 3-5. Identifying top accessed objects.sql
Listing 3-6. Identifying top PGA consumers.sql		Listing 3-6. Identifying top PGA consumers.sql
Listing 3-7. Reusable template for ASH analysis.sql		Listing 3-7. Reusable template for ASH analysis.sql
Listing 3-8. Session state breakdown by category.sql		Listing 3-8. Session state breakdown by category.sql
Listing 3-9. Session origin analysis.sql		Listing 3-9. Session origin analysis.sql
Listing 4-1. Displaying SQL execution plan with DBMS_XPLAN.sql		Listing 4-1. Displaying SQL execution plan with DBMS_XPLAN.sql
Listing 4-10. Querying blocker sessions during wait events.sql		Listing 4-10. Querying blocker sessions during wait events.sql
Listing 4-11. Gathering details about blocker sessions.sql		Listing 4-11. Gathering details about blocker sessions.sql
Listing 4-12. Investigating root blocker session activity.sql		Listing 4-12. Investigating root blocker session activity.sql
Listing 4-14. Analyzing RMAN session activity by wait event.sql		Listing 4-14. Analyzing RMAN session activity by wait event.sql
Listing 4-2. Identifying slowest execution plan step.sql		Listing 4-2. Identifying slowest execution plan step.sql
Listing 4-3. Tracking SQL execution plan changes over time.sql		Listing 4-3. Tracking SQL execution plan changes over time.sql
Listing 4-4. Tracking SQL execution timeline using V$ACTIVE_SESSION_HISTORY.sql		Listing 4-4. Tracking SQL execution timeline using V$ACTIVE_SESSION_HISTORY.sql
Listing 4-5. Tracking SQL execution timeline using DBA_HIST_ACTIVE_SESS_HISTORY.sql		Listing 4-5. Tracking SQL execution timeline using DBA_HIST_ACTIVE_SESS_HISTORY.sql
Listing 4-6. Identifying longest SQL executions within time window.sql		Listing 4-6. Identifying longest SQL executions within time window.sql
Listing 4-7. Identifying slowest executions for a known SQL ID.sql		Listing 4-7. Identifying slowest executions for a known SQL ID.sql
Listing 4-8. Querying ASH samples by machine and module.sql		Listing 4-8. Querying ASH samples by machine and module.sql
Listing 4-9. Querying wait event distribution during affected time frame.sql		Listing 4-9. Querying wait event distribution during affected time frame.sql
Listing 5-1. Querying AWR snapshot metadata.sql		Listing 5-1. Querying AWR snapshot metadata.sql
Listing 5-10. Granting privileges for AWR report generation.sql		Listing 5-10. Granting privileges for AWR report generation.sql
Listing 5-11. Generating an AWR report with awrrpt.sql		Listing 5-11. Generating an AWR report with awrrpt.sql
Listing 5-12. Generating AWR reports for peak workload intervals.sql		Listing 5-12. Generating AWR reports for peak workload intervals.sql
Listing 5-13. Comparing AAS from system metrics and ASH samples.sql		Listing 5-13. Comparing AAS from system metrics and ASH samples.sql
Listing 5-2. Using awrinfo.sql to estimate AWR space usage.sql		Listing 5-2. Using awrinfo.sql to estimate AWR space usage.sql
Listing 5-3. Creating a fixed AWR baseline.sql		Listing 5-3. Creating a fixed AWR baseline.sql
Listing 5-4. Creating a single-use baseline template.sql		Listing 5-4. Creating a single-use baseline template.sql
Listing 5-5. Creating a repeating baseline template.sql		Listing 5-5. Creating a repeating baseline template.sql
Listing 5-6. Enabling automatic AWR snapshots for a PDB.sql		Listing 5-6. Enabling automatic AWR snapshots for a PDB.sql
Listing 5-7. Verifying AWR snapshot settings across containers.sql		Listing 5-7. Verifying AWR snapshot settings across containers.sql
Listing 5-8. Querying CDB and PDB snapshot metadata.sql		Listing 5-8. Querying CDB and PDB snapshot metadata.sql
Listing 5-9. Generating a PDB-specific AWR report.sql		Listing 5-9. Generating a PDB-specific AWR report.sql
Listing 6-2. SQL script for reproducing the Load Profile section in an AWR report.sql		Listing 6-2. SQL script for reproducing the Load Profile section in an AWR report.sql
Listing 6-3. SQL script for reproducing the Top 10 Foreground Events by Total Wait Time section.sql		Listing 6-3. SQL script for reproducing the Top 10 Foreground Events by Total Wait Time section.sql
Listing 6-4. SQL script for reproducing the Wait Classes by Total Wait Time section.sql		Listing 6-4. SQL script for reproducing the Wait Classes by Total Wait Time section.sql
Listing 7-1. Calculating per-snapshot deltas from DBA_HIST_SYSSTAT.sql		Listing 7-1. Calculating per-snapshot deltas from DBA_HIST_SYSSTAT.sql
Listing 7-10. Comparing SQL performance between yesterday and today.sql		Listing 7-10. Comparing SQL performance between yesterday and today.sql
Listing 7-11. Monitoring I-O activity by database function.sql		Listing 7-11. Monitoring I-O activity by database function.sql
Listing 7-12. Monitoring I-O activity by file type.sql		Listing 7-12. Monitoring I-O activity by file type.sql
Listing 7-13. Breaking down the Others function by file type.sql		Listing 7-13. Breaking down the Others function by file type.sql
Listing 7-14. Monitoring I-O activity by tablespace.sql		Listing 7-14. Monitoring I-O activity by tablespace.sql
Listing 7-15. Tracking daily database size from DBA_HIST_TBSPC_SPACE_USAGE.sql		Listing 7-15. Tracking daily database size from DBA_HIST_TBSPC_SPACE_USAGE.sql
Listing 7-16. Identifying segments with the largest space usage growth.sql		Listing 7-16. Identifying segments with the largest space usage growth.sql
Listing 7-3. Recreating the AWR Service Statistics table from DBA_HIST_SERVICE_STAT.sql		Listing 7-3. Recreating the AWR Service Statistics table from DBA_HIST_SERVICE_STAT.sql
Listing 7-4. Calculating service-level activity over time from DBA_HIST_SERVICE_STAT.sql		Listing 7-4. Calculating service-level activity over time from DBA_HIST_SERVICE_STAT.sql
Listing 7-5. Querying selected system metric summaries from DBA_HIST_SYSMETRIC_SUMMARY.sql		Listing 7-5. Querying selected system metric summaries from DBA_HIST_SYSMETRIC_SUMMARY.sql
Listing 7-6. Pivoting Average Active Sessions by weekday and hour.sql		Listing 7-6. Pivoting Average Active Sessions by weekday and hour.sql
Listing 7-7. Querying one-minute Logons Per Sec samples from DBA_HIST_SYSMETRIC_HISTORY.sql		Listing 7-7. Querying one-minute Logons Per Sec samples from DBA_HIST_SYSMETRIC_HISTORY.sql
Listing 7-8. Identifying top SQL by elapsed time from DBA_HIST_SQLSTAT.sql		Listing 7-8. Identifying top SQL by elapsed time from DBA_HIST_SQLSTAT.sql
Listing 7-9. Tracing per-execution performance for a specific SQL statement.sql		Listing 7-9. Tracing per-execution performance for a specific SQL statement.sql
Listing 8-1. Generating an ADDM report with DBMS_ADDM.sql		Listing 8-1. Generating an ADDM report with DBMS_ADDM.sql
Listing 8-10. Checking automatic SQL Tuning Advisor actions for a SQL statement.sql		Listing 8-10. Checking automatic SQL Tuning Advisor actions for a SQL statement.sql
Listing 8-11. Generating an Automatic SQL Tuning Task report for a specific object.sql		Listing 8-11. Generating an Automatic SQL Tuning Task report for a specific object.sql
Listing 8-4. Generating an ADDM Compare Period report.sql		Listing 8-4. Generating an ADDM Compare Period report.sql
Listing 8-5. Checking the ADDM task retention period.sql		Listing 8-5. Checking the ADDM task retention period.sql
Listing 8-6. Retrieving high-value ADDM tasks.sql		Listing 8-6. Retrieving high-value ADDM tasks.sql
Listing 8-7. Summarizing recurring ADDM findings.sql		Listing 8-7. Summarizing recurring ADDM findings.sql
Listing 8-8. Summarizing high-benefit ADDM actions.sql		Listing 8-8. Summarizing high-benefit ADDM actions.sql
Listing 8-9. Identifying SQL statements with the highest ADDM-estimated benefit.sql		Listing 8-9. Identifying SQL statements with the highest ADDM-estimated benefit.sql
Listing 9-1. Verifying the load average captured by DBA_HIST_OSSTAT.sql		Listing 9-1. Verifying the load average captured by DBA_HIST_OSSTAT.sql
Listing 9-2. Comparing Oracle CPU usage with host user CPU usage by snapshot interval.sql		Listing 9-2. Comparing Oracle CPU usage with host user CPU usage by snapshot interval.sql
README.md		README.md

Repository files navigation

Apress Source Code

This repository accompanies Oracle Database Performance Tuning with AWR, ASH and ADDM by Yuan Yao (Apress, 2026).

Download the files as a zip using the green button, or clone the repository to your machine using Git.

Releases

Release v1.0 corresponds to the code in the published book, without corrections or updates.

Contributions

See the file Contributing.md for more information on how you can contribute to this repository.

About

link.springer.com/book/9798868832635

Custom properties

0 stars

0 watching

Report repository

Releases

Packages

Contributors