Identifying Historically Expensive SQL Queries in Oracle

Introduction
Understanding Historical Query Performance
The SQL Query
Explanation of the Script
Analyzing the Results
Conclusion

Introduction

In Oracle Database, some SQL queries can historically consume significant amounts of time, affecting overall database performance over periods of operation. Identifying and optimizing these historically expensive queries is crucial for maintaining a stable and efficient database environment. This blog will guide you through the process of finding the top SQL queries that have taken the most time to complete historically using a specific SQL script, breaking down the query, and analyzing the results.

Understanding Historical Query Performance

Historical query performance refers to the efficiency of SQL queries over time, rather than at a single point. Analyzing this helps in understanding long-term performance trends and identifying queries that consistently cause issues. Monitoring and optimizing these queries ensures the database's health over time and helps in strategic performance tuning.

The SQL Query

To identify historically expensive SQL queries in Oracle, use the following SQL script. This script retrieves detailed information from historical system views, calculates the time spent on each query, and displays the results in a readable format. 

select
	*
from
(
	select
		ss.module,
		ss.snap_id,
		ss.sql_id,
		ss.plan_hash_value,
		ss.executions_total,
		case
		when ss.elapsed_time_total > 0 then
			ss.elapsed_time_total/1000
		else
			0
		end elapsed_time_ms,
		case
		when ss.executions_total > 0 then
			round(ss.elapsed_time_total/nvl(ss.executions_total, 1)/1000, 2)
		else
			0
		end elapsed_time_per_exec_ms,
		ss.rows_processed_total,
		ss.px_servers_execs_total,
		ss.sorts_total,
		ss.invalidations_total,
		ss.parse_calls_total,
		ss.buffer_gets_total,
		ss.disk_reads_total,
		ss.optimizer_mode,
		ss.sql_profile,
		to_char(substr(st.sql_text,1,4000)) sql_text
	from
		dba_hist_sqlstat ss
		inner join
		dba_hist_sqltext st
			on ss.sql_id = st.sql_id
	order by
		elapsed_time_per_exec_ms desc
)
where
	rownum <= 50

Explanation of the Script

This script performs the following tasks:
  • Selects historical SQL execution details from dba_hist_sqlstat.
  • Joins with dba_hist_sqltext to get the actual SQL text.
  • Calculates the total and per-execution elapsed time for each SQL query, converting microseconds to milliseconds.
  • Retrieves additional performance metrics such as executions, rows processed, buffer gets, and disk reads.
  • Orders the results by the average elapsed time per execution in descending order.
  • Limits the results to the top 50 queries to focus on the most time-consuming ones historically.

Analyzing the Results

Once you run the query, you'll get a list of historically expensive SQL queries along with detailed execution metrics. Here's how to interpret the results:
  • Module: The application module or program associated with the SQL query.
  • Snap ID: Snapshot identifier for the time period of the historical data.
  • SQL ID: A unique identifier for the SQL statement.
  • Elapsed Time (ms): The total time spent executing the query, in milliseconds.
  • Elapsed Time per Execution (ms): The average time spent per execution of the query, in milliseconds.
  • Rows Processed: The number of rows processed by the query.
  • Buffer Gets: The number of logical reads performed by the query.
  • Disk Reads: The number of physical reads performed by the query.
  • SQL Text: The actual SQL statement text.
Key Points to Focus On
- High Elapsed Time per Execution: Queries with high `elapsed_time_per_exec_ms` indicate inefficiencies and potential areas for optimization.
- Frequent Executions>: Queries with high execution counts but moderate elapsed times can cumulatively impact performance.
- Resource Consumption>: High buffer gets and disk reads suggest heavy resource usage, which may need optimization or indexing improvements.
- Historical Trends>: Identify queries that have consistently shown high resource usage over time to address long-term performance issues.

Conclusion

Identifying and optimizing historically expensive SQL queries in Oracle Database is essential for maintaining optimal performance over time. By using the provided SQL script, you can pinpoint queries that have been the primary consumers of time and resources historically, and take appropriate actions to improve their efficiency. Regular analysis and optimization of these queries will ensure a smoother and more responsive database environment. Understanding and addressing these historically expensive SQL queries will lead to faster query execution and a more efficient Oracle Database, ultimately benefiting your overall system performance.

Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access

All Rights Reserved @2024 | www.dbdocs.net | Privacy Policy | Disclaimer | Cookie Policy