Shell Script to Find Top SQL & PL/SQL Queries and Generate Tuning Reports in Oracle Database from last 24 hrs and Run Tuning advisor automatically

This script will do the following:

✅ Find the top SQL queries executed in the last 24 hours
✅ Identify SQL statements inside PL/SQL blocks
✅ Extract full SQL text for each SQL_ID
✅ Generate a summary report with SQL details
✅ Run sqltrpt.sql on each SQL statement
✅ Store recommendations in separate tuning reports

📌 Step 1: Create the Shell Script

Save the script as generate_sql_report.sh:

#!/bin/bash

# Set Oracle environment variables
export ORACLE_SID=yourDB
export ORAENV_ASK=NO
. oraenv

# Output directories
REPORT_DIR="/tmp/sql_reports"
TUNING_DIR="/tmp/sql_tuning_reports"
rm -rf $REPORT_DIR $TUNING_DIR
mkdir -p $REPORT_DIR $TUNING_DIR

REPORT_FILE="$REPORT_DIR/sql_summary_report.txt"
echo "SQL Performance Report - $(date)" > $REPORT_FILE
echo "----------------------------------------------------" >> $REPORT_FILE

# SQL Query to find top SQL & SQL inside PL/SQL Blocks
SQL_QUERY="
SET HEADING OFF;
SET FEEDBACK OFF;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET LINES 200 PAGES 200;
COl SQL_ID for a20;
COl parsing_schema_name for a20;

WITH SQL_INSIDE_PLSQL AS (
SELECT DISTINCT sql_id, parsing_schema_name, elapsed_time/1000000 elapsed_sec, buffer_gets
FROM v\$sql
WHERE sql_text LIKE '%CUSTOM TEXT SEARCH%'
AND last_active_time > SYSDATE - 1
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
AND sql_id IS NOT NULL
),
TOP_SQL AS (
SELECT DISTINCT sql_id, parsing_schema_name, elapsed_time/1000000 elapsed_sec, buffer_gets
FROM (
SELECT sql_id, parsing_schema_name, elapsed_time, buffer_gets
FROM v\$sql
WHERE last_active_time > SYSDATE - 1
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 10
)
SELECT sql_id, parsing_schema_name, elapsed_sec, buffer_gets FROM SQL_INSIDE_PLSQL
UNION
SELECT sql_id, parsing_schema_name, elapsed_sec, buffer_gets FROM TOP_SQL;
"

# Fetch SQL details and store in a temporary file
TMP_SQL_DETAILS="/tmp/sql_details.txt"
sqlplus -s / as sysdba <<EOF > "$TMP_SQL_DETAILS"
$SQL_QUERY
EXIT;
EOF

# Function to extract SQL text for a given SQL_ID
extract_sql_text() {
SQL_ID=$1
sqlplus -s / as sysdba <<EOF | sed '/^$/d' | sed 's/ */ /g'
SET HEADING OFF;
SET FEEDBACK OFF;
SET PAGESIZE 0;
SELECT sql_text FROM v\$sql
WHERE sql_id = '$SQL_ID'
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
AND ROWNUM = 1;
EXIT;
EOF
}

# Append header to the report
echo -e "SQL ID | Schema | Elapsed Time (Sec) | Buffer Gets | SQL Text" >> $REPORT_FILE
echo "--------------------------------------------------------------------------------------" >> $REPORT_FILE

# Process each SQL_ID and generate tuning reports
while read -r LINE; do
SQL_ID=$(echo "$LINE" | awk '{print $1}')
SCHEMA_NAME=$(echo "$LINE" | awk '{print $2}')
ELAPSED_TIME=$(echo "$LINE" | awk '{print $3}')
BUFFER_GETS=$(echo "$LINE" | awk '{print $4}')

if [[ -n "$SQL_ID" ]]; then
echo "Processing SQL ID: $SQL_ID"

# Extract full SQL text
SQL_TEXT=$(extract_sql_text "$SQL_ID")
if [[ -z "$SQL_TEXT" ]]; then
SQL_TEXT="SQL Text Not Found"
fi

# Append SQL details to report
echo -e "$SQL_ID | $SCHEMA_NAME | $ELAPSED_TIME | $BUFFER_GETS | $SQL_TEXT" >> $REPORT_FILE

# Run sqltrpt.sql for each SQL ID
sqlplus -s / as sysdba <<EOF > "$TUNING_DIR/sqltrpt_$SQL_ID.txt"
SET SERVEROUTPUT ON
SET LINESIZE 200
SET PAGESIZE 500
ALTER SESSION SET CURRENT_SCHEMA = SYS;
@?/rdbms/admin/sqltrpt.sql
$SQL_ID
EXIT;
EOF

echo "Report generated: $TUNING_DIR/sqltrpt_$SQL_ID.txt"
fi
done < "$TMP_SQL_DETAILS"

echo "All SQL & PL/SQL tuning reports are saved in $TUNING_DIR."

📌 Step 2: Make the Script Executable


chmod +x generate_sql_report.sh

📌 Step 3: Run the Script


./generate_sql_report.sh

📌 Step 4: Review Reports

SQL Performance Report (With SQL Text & Execution Details)
```
cat /tmp/sql_reports/sql_summary_report.txt
```

SQL Tuning Reports for Each SQL ID


ls -l /tmp/sql_tuning_reports/
cat /tmp/sql_tuning_reports/sqltrpt_<SQL_ID>.txt

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Shell Script to Find Top SQL & PL/SQL Queries and Generate Tuning Reports in Oracle Database from last 24 hrs and Run Tuning advisor automatically