8-Long_Running_Queries.r
# -----------------------------------------------------------------------------------# File Name : 8-Long_Running_Queries.r# Author : Pierre Montbleau# Description : Identifies and analyzes long-running queries in Oracle database, # generates visualizations and PDF report for performance monitoring.# Purpose : To provide DBA team with insights into query performance issues # and identify resource-intensive sessions for optimization.# Call Syntax : source("F:\\DBA\\Scripts\\R\\8-Long_Running_Queries.r")# Parameters : None (uses default 5-minute threshold for long-running queries)# Last Modified: 2025-12-17# -----------------------------------------------------------------------------------
# -----------------------------------------------------------------------------------# Load required packages# -----------------------------------------------------------------------------------library(DBI)library(odbc)library(ggplot2)library(gridExtra)library(knitr)library(grid)
# -----------------------------------------------------------------------------------# Function: connect_to_database# Description: Establishes connection to Oracle database using ODBC driver.# Includes connection validation with test query.# Parameters: None# Returns: Database connection object# -----------------------------------------------------------------------------------connect_to_database <- function() { tryCatch({ conn <- dbConnect(odbc::odbc(), dsn = "[DB_NAME]", uid = "[USERNAME]", pwd = "[PASSWORD]", timeout = 10) message("Successfully connected to database") # Verify connection works with a simple query test_query <- "SELECT 1 FROM DUAL" test_result <- dbGetQuery(conn, test_query) if (nrow(test_result) != 1) { stop("Test query failed - connection validation unsuccessful") } return(conn) }, error = function(e) { stop("Database connection failed: ", e$message) })}
# -----------------------------------------------------------------------------------# Function: get_long_running_queries# Description: Retrieves active user sessions running longer than specified threshold.# Includes session details, SQL text, and wait information.# Parameters:# - conn: Database connection object# - threshold_minutes: Minimum duration in minutes to consider a query "long-running"# Returns: Data frame containing long-running session details# -----------------------------------------------------------------------------------get_long_running_queries <- function(conn, threshold_minutes = 5) { query <- paste0(" SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.module, s.sql_id, sq.sql_text, ROUND(s.last_call_et/60, 2) as minutes_running, s.status, s.logon_time, s.blocking_session, s.wait_class, s.event FROM v$session s JOIN v$sql sq ON s.sql_id = sq.sql_id WHERE s.type = 'USER' AND s.status = 'ACTIVE' AND s.last_call_et > ", threshold_minutes * 60, " ORDER BY s.last_call_et DESC ") dbGetQuery(conn, query)}
# -----------------------------------------------------------------------------------# Function: create_visualizations# Description: Creates multiple ggplot2 visualizations from long-running query data.# Includes bar charts, distribution plots, and time-series analysis.# Parameters:# - long_running_data: Data frame containing query session information# Returns: List of ggplot2 visualization objects# -----------------------------------------------------------------------------------create_visualizations <- function(long_running_data) { if (nrow(long_running_data) == 0) { return(list( no_data_plot = ggplot() + annotate("text", x = 1, y = 1, label = "No long-running queries found") + theme_void() )) } # Plot 1: Query duration by user plot1 <- ggplot(long_running_data, aes(x = reorder(USERNAME, MINUTES_RUNNING), y = MINUTES_RUNNING)) + geom_bar(stat = "identity", fill = "steelblue") + coord_flip() + labs(title = "Long-Running Queries by User", x = "Username", y = "Minutes Running") + theme_minimal() # Plot 2: Query duration by program plot2 <- ggplot(long_running_data, aes(x = reorder(PROGRAM, MINUTES_RUNNING), y = MINUTES_RUNNING)) + geom_bar(stat = "identity", fill = "darkorange") + coord_flip() + labs(title = "Long-Running Queries by Program", x = "Program", y = "Minutes Running") + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Plot 3: Wait classes distribution if ("WAIT_CLASS" %in% colnames(long_running_data)) { plot3 <- ggplot(long_running_data, aes(x = WAIT_CLASS, fill = WAIT_CLASS)) + geom_bar() + labs(title = "Wait Class Distribution", x = "Wait Class", y = "Count") + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1)) } else { plot3 <- NULL } # Plot 4: Time series of query durations plot4 <- ggplot(long_running_data, aes(x = LOGON_TIME, y = MINUTES_RUNNING)) + geom_point(aes(color = USERNAME), size = 3) + labs(title = "Query Duration Over Time", x = "Logon Time", y = "Minutes Running") + theme_minimal() return(list( user_plot = plot1, program_plot = plot2, wait_class_plot = plot3, time_plot = plot4 ))}
# -----------------------------------------------------------------------------------# Function: generate_report# Description: Main orchestration function that connects to database, retrieves data,# creates visualizations, and generates PDF report.# Parameters: None# Returns: None (creates PDF file in output directory)# -----------------------------------------------------------------------------------generate_report <- function() { # Create output directory if it doesn't exist output_dir <- "F:/DBA/Scripts/R/Reports" if (!dir.exists(output_dir)) { dir.create(output_dir, recursive = TRUE) } # Generate filename with timestamp report_file <- file.path(output_dir, paste0("LongRunningQueries_", format(Sys.time(), "%Y%m%d_%H%M%S"), ".pdf")) # Initialize connection conn <- NULL tryCatch({ # Connect to database conn <- connect_to_database() # Get long running queries long_running_data <- get_long_running_queries(conn) # Create visualizations plots <- create_visualizations(long_running_data) # Start PDF device pdf(report_file, width = 11, height = 8.5) # Add title page grid.newpage() grid.text("Oracle Database Long-Running Queries Report", x = 0.5, y = 0.7, gp = gpar(fontsize = 20, fontface = "bold")) grid.text(paste("Generated on:", Sys.time()), x = 0.5, y = 0.6, gp = gpar(fontsize = 12)) grid.text(paste("Total long-running queries found:", nrow(long_running_data)), x = 0.5, y = 0.5, gp = gpar(fontsize = 12)) # Print plots if (nrow(long_running_data) > 0) { print(plots$user_plot) print(plots$program_plot) if (!is.null(plots$wait_class_plot)) print(plots$wait_class_plot) print(plots$time_plot) # Add data table grid.newpage() grid.table(head(long_running_data, 20)) # Show first 20 rows } else { print(plots$no_data_plot) } message(paste("Report successfully generated at:", report_file)) }, error = function(e) { message("Error generating report: ", e$message) }, finally = { # Close connection if it was opened if (!is.null(conn)) { dbDisconnect(conn) message("Database connection closed") } # Close PDF device if it's open if (exists("report_file") && dev.cur() > 1) { dev.off() } })}
# -----------------------------------------------------------------------------------# MAIN EXECUTION# -----------------------------------------------------------------------------------# Execute the report generationgenerate_report()
# -----------------------------------------------------------------------------------# END SCRIPT# -----------------------------------------------------------------------------------
# -----------------------------------------------------------------------------------# Load required packages# -----------------------------------------------------------------------------------library(DBI)library(odbc)library(ggplot2)library(gridExtra)library(knitr)library(grid)
# -----------------------------------------------------------------------------------# Function: connect_to_database# Description: Establishes connection to Oracle database using ODBC driver.# Includes connection validation with test query.# Parameters: None# Returns: Database connection object# -----------------------------------------------------------------------------------connect_to_database <- function() { tryCatch({ conn <- dbConnect(odbc::odbc(), dsn = "[DB_NAME]", uid = "[USERNAME]", pwd = "[PASSWORD]", timeout = 10) message("Successfully connected to database") # Verify connection works with a simple query test_query <- "SELECT 1 FROM DUAL" test_result <- dbGetQuery(conn, test_query) if (nrow(test_result) != 1) { stop("Test query failed - connection validation unsuccessful") } return(conn) }, error = function(e) { stop("Database connection failed: ", e$message) })}
# -----------------------------------------------------------------------------------# Function: get_long_running_queries# Description: Retrieves active user sessions running longer than specified threshold.# Includes session details, SQL text, and wait information.# Parameters:# - conn: Database connection object# - threshold_minutes: Minimum duration in minutes to consider a query "long-running"# Returns: Data frame containing long-running session details# -----------------------------------------------------------------------------------get_long_running_queries <- function(conn, threshold_minutes = 5) { query <- paste0(" SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.module, s.sql_id, sq.sql_text, ROUND(s.last_call_et/60, 2) as minutes_running, s.status, s.logon_time, s.blocking_session, s.wait_class, s.event FROM v$session s JOIN v$sql sq ON s.sql_id = sq.sql_id WHERE s.type = 'USER' AND s.status = 'ACTIVE' AND s.last_call_et > ", threshold_minutes * 60, " ORDER BY s.last_call_et DESC ") dbGetQuery(conn, query)}
# -----------------------------------------------------------------------------------# Function: create_visualizations# Description: Creates multiple ggplot2 visualizations from long-running query data.# Includes bar charts, distribution plots, and time-series analysis.# Parameters:# - long_running_data: Data frame containing query session information# Returns: List of ggplot2 visualization objects# -----------------------------------------------------------------------------------create_visualizations <- function(long_running_data) { if (nrow(long_running_data) == 0) { return(list( no_data_plot = ggplot() + annotate("text", x = 1, y = 1, label = "No long-running queries found") + theme_void() )) } # Plot 1: Query duration by user plot1 <- ggplot(long_running_data, aes(x = reorder(USERNAME, MINUTES_RUNNING), y = MINUTES_RUNNING)) + geom_bar(stat = "identity", fill = "steelblue") + coord_flip() + labs(title = "Long-Running Queries by User", x = "Username", y = "Minutes Running") + theme_minimal() # Plot 2: Query duration by program plot2 <- ggplot(long_running_data, aes(x = reorder(PROGRAM, MINUTES_RUNNING), y = MINUTES_RUNNING)) + geom_bar(stat = "identity", fill = "darkorange") + coord_flip() + labs(title = "Long-Running Queries by Program", x = "Program", y = "Minutes Running") + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Plot 3: Wait classes distribution if ("WAIT_CLASS" %in% colnames(long_running_data)) { plot3 <- ggplot(long_running_data, aes(x = WAIT_CLASS, fill = WAIT_CLASS)) + geom_bar() + labs(title = "Wait Class Distribution", x = "Wait Class", y = "Count") + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1)) } else { plot3 <- NULL } # Plot 4: Time series of query durations plot4 <- ggplot(long_running_data, aes(x = LOGON_TIME, y = MINUTES_RUNNING)) + geom_point(aes(color = USERNAME), size = 3) + labs(title = "Query Duration Over Time", x = "Logon Time", y = "Minutes Running") + theme_minimal() return(list( user_plot = plot1, program_plot = plot2, wait_class_plot = plot3, time_plot = plot4 ))}
# -----------------------------------------------------------------------------------# Function: generate_report# Description: Main orchestration function that connects to database, retrieves data,# creates visualizations, and generates PDF report.# Parameters: None# Returns: None (creates PDF file in output directory)# -----------------------------------------------------------------------------------generate_report <- function() { # Create output directory if it doesn't exist output_dir <- "F:/DBA/Scripts/R/Reports" if (!dir.exists(output_dir)) { dir.create(output_dir, recursive = TRUE) } # Generate filename with timestamp report_file <- file.path(output_dir, paste0("LongRunningQueries_", format(Sys.time(), "%Y%m%d_%H%M%S"), ".pdf")) # Initialize connection conn <- NULL tryCatch({ # Connect to database conn <- connect_to_database() # Get long running queries long_running_data <- get_long_running_queries(conn) # Create visualizations plots <- create_visualizations(long_running_data) # Start PDF device pdf(report_file, width = 11, height = 8.5) # Add title page grid.newpage() grid.text("Oracle Database Long-Running Queries Report", x = 0.5, y = 0.7, gp = gpar(fontsize = 20, fontface = "bold")) grid.text(paste("Generated on:", Sys.time()), x = 0.5, y = 0.6, gp = gpar(fontsize = 12)) grid.text(paste("Total long-running queries found:", nrow(long_running_data)), x = 0.5, y = 0.5, gp = gpar(fontsize = 12)) # Print plots if (nrow(long_running_data) > 0) { print(plots$user_plot) print(plots$program_plot) if (!is.null(plots$wait_class_plot)) print(plots$wait_class_plot) print(plots$time_plot) # Add data table grid.newpage() grid.table(head(long_running_data, 20)) # Show first 20 rows } else { print(plots$no_data_plot) } message(paste("Report successfully generated at:", report_file)) }, error = function(e) { message("Error generating report: ", e$message) }, finally = { # Close connection if it was opened if (!is.null(conn)) { dbDisconnect(conn) message("Database connection closed") } # Close PDF device if it's open if (exists("report_file") && dev.cur() > 1) { dev.off() } })}
# -----------------------------------------------------------------------------------# MAIN EXECUTION# -----------------------------------------------------------------------------------# Execute the report generationgenerate_report()
# -----------------------------------------------------------------------------------# END SCRIPT# -----------------------------------------------------------------------------------