Create Oracle SQL Tuning Task Manually

    An Oracle SQL Tuning Task is a logical container used by Oracle Database's built-in SQL Tuning Advisor to analyze and optimize specific SQL statements.

    When you create a tuning task, you register one or more SQL statements (retrieved from memory, the Automatic Workload Repository (AWR), or entered manually as text) to be evaluated. When executed, the database analyzes the execution path, identifies performance bottlenecks, and generates a detailed optimization report.

CREATE TUNING TASK

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '5mn47p0gv2q83',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 4000,
                          task_name   => 'Task_5mn47p0gv2q83',
                          description => 'Manual Tuning on 06 SEP 16');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXECUTE TUNING TASK

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Task_5mn47p0gv2q83');

CHECK STATUS OF TASK

SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'Task_5mn47p0gv2q83';

CHECK RECOMMENDATION

SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 1000;
SELECT DBMS_SQLTUNE.report_tuning_task('Task_5mn47p0gv2q83') AS recommendations FROM dual;

Linux sendmail utility to send attachment

     In Linux, sendmail is an inbuilt utility command used in scripts for system-generated notifications. It is also a powerful Mail Transfer Agent (MTA). There are a lot of email utilities like mailx, mail, mutt, and others, but some clients prefer not to install those utilities as part of the security requirements. Sending a simple email with the sendmail utility is easier, but attaching a file to the email is a little bit tricky. Here is the script you can use to send an email with an attachment using the sendmail utility.

Sending an email with an attachment using the sendmail utility.

#!/bin/bash

# Define variables. Replace with the required values.
FROM="noreply@dilli.com.np"
TO="someone@dilli.com.np"
SUBJECT="Sales report for the month of March 2026"

ATTACHMENT="/home/dillimaharjan/sales_2026_03.xls"
FILENAME=$(basename "$ATTACHMENT")
BOUNDARY="zKz_Stm68rdgV3UqG_u_njPvGH"

# Create headers and the email body as required. 
(
  echo "From: $FROM"
  echo "To: $TO"
  echo "Subject: $SUBJECT"
  echo "MIME-Version: 1.0"
  echo "Content-Type: multipart/mixed; boundary=\"$BOUNDARY\""
  echo ""
  echo "--$BOUNDARY"
  echo "Content-Type: text/plain; charset=US-ASCII"
  echo "Content-Transfer-Encoding: 7bit"
  echo ""
  echo "Dear sir,
Attach is the sales report for the month of March 2026.

Thank you,
Dilli Raj Maharjan."
  echo ""
  # Add the attachment
  echo "--$BOUNDARY"
  echo "Content-Type: application/octet-stream; name=\"$FILENAME\""
  echo "Content-Transfer-Encoding: base64"
  echo "Content-Disposition: attachment; filename=\"$FILENAME\""
  echo ""
  # Encode the attachment using base64
  base64 "$ATTACHMENT"
  echo ""
  # End the email
  echo "--$BOUNDARY--"
) | sendmail -t

Sending an email with HTML contents using the sendmail utility.

#!/bin/bash

# --- Configuration ---
TO="noreply@dilli.com.np"
FROM="someone@dilli.com.np"
SUBJECT="Sales report for month of March 2026"
HTML_FILE="/path/to/your/content.html" # Your HTML content is in this file

# --- Logic ---
(
  echo "From: $FROM"
  echo "To: $TO"
  echo "Subject: $SUBJECT"
  echo "MIME-Version: 1.0"
  echo "Content-Type: text/html; charset=UTF-8" # This line is crucial
  echo "" # An empty line separates headers from the body

  echo "Dear sir,

Here is the sales report for the Month of March 2026. "
  cat "$HTML_FILE" # Add the HTML content from your file

  echo "Regards,

  Dilli Maharjan"
) | sendmail -t