SQL Tuning Tasks for Large SQL


I have need of generating SQL profiles for some very large SQL statements.

I propose to write a basic PL/SQL program that runs under SQL*Plus and reads a file on the local drive to create a SQL Profile for use in Automatic SQL Tuning.

General Plan

I am following the example given in Creating a SQL Tuning Task. Instead of using static text for the SQL, I plan to read a CLOB from an external file.

Create the Directory

Using Oracle XE, I created a directory pointing to my Documents folder, by using the CREATE DIRECTORY SQL command.

Use the Run SQL Command program in the Oracle Database 10g Express Edition program group to execute the following commands:

connect / as sysdba
CREATE DIRECTORY documents AS 'C:\Users\douglas\Documents';

This allows me to reference files external to the Oracle Server as long as they reside inside my Documents folder.

Create a Test User

connect / as sysdba
CREATE USER sql_tester
  IDENTIFIED BY "*******" -- password suppressed
  QUOTA UNLIMITED ON USERS;
GRANT
  CREATE SESSION,
  ADVISOR,
  ADMINISTER SQL TUNING SET
  TO sql_tester;
GRANT READ ON DIRECTORY documents TO sql_tester;

The privileges granted to SQL_TESTER are based upon the requirements listed in DBMS_SQLTUNE Security Model. CREATE SESSION is needed for the user to log onto the Oracle instance. This user can only read files in the folder.

Test Case

I have created a file called example.sql in my Documents folder with the following text:

SELECT 1 FROM DUAL

Note that there is no ending semicolon. If you put one in, you will get the following message in your tuning results:

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00911: invalid character

Become the Test User

I cease to be the DBA and become a user.

SQL> connect sql_tester
Enter password:
Connected.

First Draft of PL/SQL Block

I am going to use the following procedures from the DBMS_LOB package:

CREATETEMPORARY To create a temporary CLOB in the user’s run-time memory
LOADCLOBFROMFILE To read the entire source file into the temporary CLOB.
OPEN To open the SQL text file for reading.
CLOSE To close the SQL text file.
DECLARE
  l_sql_text
    CLOB;
  l_src_bfile
    BFILE
      := BFILENAME( 'DOCUMENTS', 'example.sql' );
  l_dest_offset
    INTEGER
      := 1;
  l_src_offset
    INTEGER
      := 1;
  l_lang_context
    INTEGER
      := dbms_lob.default_lang_ctx;
  l_warning
    INTEGER
      := dbms_lob.no_warning;
BEGIN
  dbms_lob.open( l_src_bfile, dbms_lob.lob_readonly );
  dbms_lob.createtemporary (
    lob_loc => l_sql_text,
    cache   => false,
    dur     => dbms_lob.call
  );
  dbms_lob.loadclobfromfile(
    dest_lob     => l_sql_text,
    src_bfile    => l_src_bfile,
    amount       => dbms_lob.lobmaxsize,
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    bfile_csid   => dbms_lob.default_csid,
    lang_context => l_lang_context,
    warning      => l_warning
  );
  dbms_lob.close( l_src_bfile );
END;
/

The BFILENAME function converts a file name and a directory into a BFILE object.

Cannot use EMPTY_CLOB() to initialise l_sql_text because of:

Restriction on LOB Locators
You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.

When I did so, I got the error message:

ORA-22275: invalid LOB locator specified

This procedure works in that no errors are returned.

Submit a Job to Create a Tuning Task

I am going to augment the above code with the following procedures from the DBMS_SQLTUNE package:

CREATE_TUNING_TASK To load the SQL text into a tuning task without any associated bind variables.
EXECUTE_TUNING_TASK To submit a job to find a suitable SQL profile that improves the performance of the SQL text.

I have also made use of SQL*Plus local bind variables to make the routine more general.

REM Variables specific to each invocation of PL/SQL block
VARIABLE directory VARCHAR2(30)
VARIABLE sql_file  VARCHAR2(256)
VARIABLE task_name VARCHAR2(30)
VARIABLE task_desc VARCHAR2(256)
EXEC :directory := 'DOCUMENTS'
EXEC :sql_file  := 'example.sql'
EXEC :task_name := 'Tune_Simple_SQL_1'
EXEC :task_desc := 'Very, very bad SQL'
REM PL/SQL block
DECLARE
  l_sql_text
    CLOB;
  l_src_bfile
    BFILE
      := BFILENAME( :directory, :sql_file );
  l_dest_offset
    INTEGER
      := 1;
  l_src_offset
    INTEGER
      := 1;
  l_lang_context
    INTEGER
      := dbms_lob.default_lang_ctx;
  l_warning
    INTEGER
      := dbms_lob.no_warning;
  l_task_id
    VARCHAR2(256)
      := NULL;
BEGIN
  -- Read SQL text from external file into temporary CLOB
  dbms_lob.open( l_src_bfile, dbms_lob.lob_readonly );
  dbms_lob.createtemporary (
    lob_loc => l_sql_text,
    cache   => false,
    dur     => dbms_lob.call
  );
  dbms_lob.loadclobfromfile(
    dest_lob     => l_sql_text,
    src_bfile    => l_src_bfile,
    amount       => dbms_lob.lobmaxsize,
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    bfile_csid   => dbms_lob.default_csid,
    lang_context => l_lang_context,
    warning      => l_warning
  );
  dbms_lob.close( l_src_bfile );
  -- Create a SQL Tuning Task with no variables
  l_task_id :=
    dbms_sqltune.create_tuning_task(
      sql_text    => l_sql_text,
      bind_list   => NULL,
      user_name   => NULL,
      scope       => dbms_sqltune.scope_comprehensive,
      time_limit  => dbms_sqltune.time_limit_default,
      task_name   => :task_name,
      description => :task_desc
    );
  -- Execute the SQL Tuning Task
  dbms_sqltune.execute_tuning_task(
    task_name => :task_name
  );
END;
/

See the Status of the Task

Refer to 12.2.4.3 Checking the Status of a SQL Tuning Task

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = :task_name;

STATUS
-----------
COMPLETED

See the Results of the Task

Refer to 12.2.4.5 Displaying the Results of a SQL Tuning Task

SET PAGESIZE 50000
SET LINESIZE 180
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :task_name )
  FROM DUAL;

The results are:

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TASK_NAME)
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tune_Simple_SQL_1
Tuning Task Owner  : SQL_TESTER
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 12/20/2008 08:44:51
Completed at       : 12/20/2008 08:44:51

-------------------------------------------------------------------------------
Schema Name: SQL_TESTER
SQL ID     : avfy0nbtwd2q6
SQL Text   : SELECT 1 FROM DUAL

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------
Advertisements

One thought on “SQL Tuning Tasks for Large SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s