Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

dbms_sqltune tips


Oracle Tips by Burleson Consulting

Inside the dbms_sqltune Package tasks

SQL tuning work is one of the most time consuming and challenging tasks faced by Oracle DBAs and application developers and the Oracle SQL Tuning Advisor is intended to facilitate SQL tuning tasks and to help the DBA find the optimal SQL execution plan.

 

The SQL Tuning Advisor can search the SQL cache, the AWR, or user inputs searching for inefficient SQL statements. The SQL Tuning Advisor is available through the OEM console, or the dbms_sqltune package can be invoked manually.  Figure 8.3 is a representation of the SQL Tuning Advisor tool in the OEM console.

 

Figure 8.3:  Access SQL Tuning Advisor in the OEM Console.

 

The Ion tool, which has been provided free with the purchase of this book, also supports use of the SQL Tuning Advisor and allows the DBA to tune any SQL statement that is stored in the AWR.  Figure 8.4 is a representation of the SQL Tuning Advisor in the Ion tool.

 

Figure 8.4:  Access SQL Tuning Advisor in Ion

 

The dbms_sqltune package provides the DBA with a PL/SQL API for using the SQL Tuning Advisor tool. Running the SQL Tuning Advisor using PL/SQL API includes two steps:

§       Create the SQL tuning task.

§       Execute the SQL tuning task.

There are several options for the creation of an SQL tuning task. For example, the following process will examine the invocation of a single SQL statement. The dbms_sqltune.create_task function can be used to do the following:

 

DECLARE

  my_sqltext CLOB;

  task_name VARCHAR2(30);

BEGIN

  my_sqltext := 'SELECT object_type, count(*) FROM ';

  my_sqltext := my_sqltext || ' all_objects GROUP BY object_type';

  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,

                                   bind_list => sql_binds(anydata.ConvertNumber(100)),

                                   user_name => 'DABR',

                                   scope => 'COMPREHENSIVE',

                                   time_limit => 60,

                                   task_name => 'sql_tuning_task1');

 

END;

/

 

SQL tuning tasks can be created with SQL from the cursor cache, the AWR views, or previously collected SQL Tuning Sets. The overloaded dbms_sqltune.create_task functions are provided to allow the DBA to change the inputs.

 

After successfully creating a SQL tuning task, the DBA can launch the SQL Tuning Optimizer  to produce tuning recommendations. Use the dbms_sqltune.execute_tuning_task procedure to execute the specified task:

 

exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task1');

 

Now, the DBA is ready to review recommendation details produced by the SQL Tuning Advisor.  A query like the one below can be used to retrieve the SQL analysis results:

 

select dbms_sqltune.report_tuning_task(‘sql_tuning_task1’) from dual;

 

The following is the resulting report.

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK1’)

--------------------------------------------------------------------

GENERAL INFORMATION SECTION

--------------------------------------------------------------------

Tuning Task Name   : SQL_TUNING_TASK1

Scope              : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at         : 09/07/2004 16:24:41

Completed at       : 09/07/2004 16:24:41

 

--------------------------------------------------------------------

SQL ID  : g2wr3u7s1gtf3

SQL Text: 'SELECT object_type, count(*) FROM

           all_objects GROUP BY object_type'

 

--------------------------------------------------------------------

FINDINGS SECTION (1 finding)

--------------------------------------------------------------------

 

1- Statistics Finding

---------------------

  Optimizer statistics for table "SYS"."OBJ$" and its indices are stale.

 

  Recommendation

  --------------

    Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'SYS',

 

The recommendation report contains problem findings and the corresponding recommendations for fixing them.

 

The analysis process could consume significant processing times. Therefore, the dbms_sqltune package provides an API to manage tuning tasks such as:

§       The interrupt_tuning_task procedure is used to stop the executing task. Any results that have already been produced will be preserved.

§       The cancel_tuning_task procedure terminates the task that is executing without preserving its results.

§       The reset_tuning_task procedure is used to stop the running task and reset it to the initial state.

§       The drop_tuning_task  procedure can be used to remove the task from the database.

During tuning analysis, the SQL Tuning Advisor can recommend and automatically create SQL profiles. The SQL profile is a special object that is used by the optimizer. The SQL Profile contains auxiliary statistics specific to a particular SQL statement.

 

The SQL optimizer uses the information in the SQL profile to adjust the execution plan for the SQL statement that has the associated SQL profile. SQL profiles are great for SQL tuning because it is possible to tune SQL statements without any modification of the application source code or the text of SQL queries. The dba_sql_profiles view shows information about all existing SQL profiles.

 

The dbms_sqltune package can be used to manage SQL profiles. The SQL Tuning Advisor  can recommend the use of a specific SQL Profile. This SQL profile can be associated with SQL statements that are being analyzed by accepting it using dbms_sqltune.accept_sql_profile:

 

DECLARE

  sqlprofile VARCHAR2(30);

BEGIN

  sqlprofile := dbms_sqltune.accept_sql_profile (

  task_name => 'sql_tuning_task1',

  name => 'sql_profile1');

END;

 

After the profile is defined, the DBA can alter any stored SQL profile attributes such as status  , name , description , and category  using dbms_sqltune.alter_sql_profile. The category attribute is used to limit user sessions that can use the particular SQL profile. There is an initialization parameter called sqltune_category , which allows the DBA to set up the default SQL profile category for the database.

 

The dbms_sqltune.drop_sql_profile  procedure is used to remove the SQL profile from the database.

 

The dbms_sqltune package also provides a PL/SQL API to work with SQL Tuning Sets (STS). The STS is a database object that contains one or more SQL statements combined with their execution statistics and context such as particular schema, application module name, list of bind variables, etc. The STS also includes a set of basic execution statistics such as CPU and elapsed times, disk reads and buffer gets, number of executions, etc.

 

When creating a STS, the SQL statements can be filtered by different patterns such as application module name or execution statistics, such as high disk reads. Once created, STS can be an input source for the SQL Tuning Advisor.

 

Typically, the following steps are used to work with STS using the dbms_sqltune API:

1.     STS is created using the dbms_sqltune.create_sqlset procedure. For example, the following script can be used to create a STS called SQLSET1:

 

exec dbms_sqltune.create_sqlset ( ‘SQLSET1’);

 

2.     STS is loaded from such sources as the AWR, another STS, or the cursor cache. The following sample PL/SQL block loads STS from the current cursor cache:

 

DECLARE

  cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN cur FOR

SELECT VALUE(p)

FROM TABLE (dbms_sqltune.select_cursor_cache) p;

dbms_sqltune.load_sqlset(

sqlset_name => 'SQLSET1',

populate_cursor => cur);

END;

/

 

3.     An SQL tuning task that uses STS as input can be created and executed like this:

 

exec dbms_sqltune.create_tuning_task (sqlset_name  => ‘SQLSET1’, task_name => ‘TASK1’);

exec dbms_sqltune.execute_tuning_task (‘TASK1’);

 

4.     The following syntax can be used to drop a SQL tuning set when finished:

 

exec dbms_sqltune.drop_sqlset ( ‘SQLSET1’ );

 

All SQL tuning sets created in the database by querying the dba_sqlset , dba_sqlset_binds , dba_sqlset_definitions , and dba_sqlset_statements  views are reviewed. For example, the dbms_sqltune_show_sts.sql query below shows the particular SQL statements associated with STS:

 

SELECT

  s.sql_text,

  s.cpu_time

FROM

  dba_sqlset_statements s,

  dba_sqlset a

WHERE

  a.name = 'SQLSET1'

AND

   s.sqlset_id =   a.id

AND

   rownum <= 10

ORDER BY

  s.cpu_time DESC     

 

Clearly, Oracle10g has introduced a rich set of powerful tools for the DBA to use to identify and resolve possible performance problems and while these advisors cannot yet replicate the behavior of a senior DBA, they promise to get more intelligent with each new release of Oracle.



One of the most encouraging new areas with Oracle 10g and 11g are the many advisors, and in particular, those to assist with SQL optimization. The days of manually working solely with cryptic explain plans are essentially over. While along the way Oracle offered interim solutions such as stored outlines and profiles, the ultimate solution has now evolved resulting in the SQL Performance Analyzer  (SPA), SQL Tuning Advisor  and the critically important concept of SQL Tuning Sets  (STS).

 

Now we can work with small manageable SQL workload bundles called tuning sets, which include SQL Text, Bind variables, execution plans and execution statistics, and test-encapsulated scenarios for before and after relative comparisons. That, in turn, paves the way for focusing on the results rather than the explain plans resulting in those satisfactory results. Furthermore, Oracle 11g has automated candidate identification so that DBAs need only to concentrate on those flagged as being potential issues.

 

This feature in available only in Oracle 10g and higher and specifically only for those who have purchased the OEM optional Tuning Packs.

 

There are so many new database advisors, with the list seeming to grow by the minute, that the best way to utilize them is most often via the OEM screens accessible via Advisor Central as shown here in Figure 7.6. However, the PL/SQL interface in this chapter will be examined for completeness. The next chapter will focus more on the task and its processes as well as the various screens to visit.

 

Figure 7.6: SQL Tuning Advisor in OEM

The following data types and constants are used by the DBMS_SQLTUNE  package:

 

  ----------------------------------------------------------------------------

  --                      global constant declarations                      --

  ----------------------------------------------------------------------------

  --

  -- sqltune advisor name

  ADV_SQLTUNE_NAME  CONSTANT VARCHAR2(18) := 'SQL Tuning Advisor';

 

  --

  -- SQLTune advisor task scope parameter values

  --

  SCOPE_LIMITED       CONSTANT VARCHAR2(7)  := 'LIMITED';

  SCOPE_COMPREHENSIVE CONSTANT VARCHAR2(13) := 'COMPREHENSIVE';

 

  --

  --  SQLTune advisor time_limit constants

  --

  TIME_LIMIT_DEFAULT  CONSTANT   NUMBER := 1800;

 

  --

  -- report type (possible values) constants

  --

  TYPE_TEXT           CONSTANT   VARCHAR2(4) := 'TEXT'       ;

  TYPE_XML            CONSTANT   VARCHAR2(3) := 'XML'        ;

  TYPE_HTML           CONSTANT   VARCHAR2(4) := 'HTML'       ;

 

  --

  -- report level (possible values) constants

  --

  LEVEL_TYPICAL       CONSTANT   VARCHAR2(7) := 'TYPICAL'    ;

  LEVEL_BASIC         CONSTANT   VARCHAR2(5) := 'BASIC'      ;

  LEVEL_ALL           CONSTANT   VARCHAR2(3) := 'ALL'        ;

 

  --

  -- report section (possible values) constants

  --

  SECTION_FINDINGS    CONSTANT   VARCHAR2(8) := 'FINDINGS'   ;

  SECTION_PLANS       CONSTANT   VARCHAR2(5) := 'PLANS'      ;

  SECTION_INFORMATION CONSTANT   VARCHAR2(11):= 'INFORMATION';

  SECTION_ERRORS      CONSTANT   VARCHAR2(6) := 'ERRORS'     ;

  SECTION_ALL         CONSTANT   VARCHAR2(3) := 'ALL'        ;

  SECTION_SUMMARY     CONSTANT   VARCHAR2(7) := 'SUMMARY'    ;

 

  -- some common date format

  DATE_FMT       constant varchar2(21)       :=  'mm/dd/yyyy hh24:mi:ss';

 

  --

  -- script section constants

  --

  REC_TYPE_ALL          CONSTANT   VARCHAR2(3)  := 'ALL';

  REC_TYPE_SQL_PROFILES CONSTANT   VARCHAR2(8)  := 'PROFILES';

  REC_TYPE_STATS        CONSTANT   VARCHAR2(10) := 'STATISTICS';

  REC_TYPE_INDEXES      CONSTANT   VARCHAR2(7)  := 'INDEXES';

 

  --

  -- capture section constants

  --

  MODE_REPLACE_OLD_STATS CONSTANT   NUMBER := 1;

  MODE_ACCUMULATE_STATS  CONSTANT   NUMBER := 2;

 

---- SYS.SQLSET_ROW ----

 

  --

  -- sql tuning set basic attributes

  --

  sql_id                   VARCHAR(13),   /* unique SQL ID */

  force_matching_signature NUMBER,        /* literals, case, spaces removed */

  sql_text                 CLOB,          /* unique SQL hache value */

  object_list              sql_objects,   /* objects referenced by this stmt */

  bind_data                RAW(2000),     /* bind data as captured for SQL */

  parsing_schema_name      VARCHAR2(30),  /* schema where the SQL is parsed */

  module                   VARCHAR2(48),  /* last app. module for the SQL */

  action                   VARCHAR2(32),  /* last app. action for the SQL */

  elapsed_time             NUMBER,        /* elapsed time for SQL statement */

  cpu_time                 NUMBER,        /* CPU time for this SQL */

  buffer_gets              NUMBER,        /* number of buffer gets */

  disk_reads               NUMBER,        /* number of disk reads  */

  direct_writes            NUMBER,        /* number of direct writes */

  rows_processed           NUMBER,        /* # of rows processed by this SQL */

  fetches                  NUMBER,        /* number of fetches */

  executions               NUMBER,        /* total executions of this SQL */

  end_of_fetch_count       NUMBER,        /* exec. count up to end of fetch */

  optimizer_cost           NUMBER,        /* Optimizer cost for this SQL */

  optimizer_env            RAW(2000),     /* optimizer environment */

  priority                 NUMBER,        /* user-defined priority (1,2,3) */

  command_type             NUMBER,        /* statement type - INSERT, etc. */

  first_load_time          VARCHAR2(19),  /* load time of parent cursor */

  stat_period              NUMBER,        /* period (seconds) when the */

                                          /* stats for SQL stmt collected */

  active_stat_period       NUMBER,        /* effecive time (in seconds) */

                                          /* for which SQL stmt was active */

  other                    CLOB,          /* col for user defined attrs */

  plan_hash_value          NUMBER,        /* plan hash value of the plan */

  sql_plan                 sql_plan_table_type, /* explain plan */

  bind_list                sql_binds,     /* list of user specified binds */

 

accept_sql_profile is both a dbms_sqltune procedure and function that creates the SQL Profile recommended by the SQL Tuning Advisor . The SQL Text is supplied via a SQL Tuning Task object. The function returns the name of the SQL Profile as a VARCHAR2.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

OBJECT_ID

NUMBER

IN

NULL

NAME

VARCHAR2

IN

NULL

DESCRIPTION

VARCHAR2

IN

NULL

CATEGORY

VARCHAR2

IN

NULL

TASK_OWNER

VARCHAR2

IN

NULL

REPLACE

BOOLEAN

IN

FALSE

FORCE_MATCH

BOOLEAN

IN

FALSE

Table 7.158:  Accept_sql_profile Parameters

add_sqlset_reference is a dbms_sqltune function that adds a new reference to a pre-existing SQL Tuning Set (STS). The description can be up to 256 characters. It returns the new reference’s identifier as a NUMBER.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

DESCRIPTION

VARCHAR2

IN

NULL

Table 7.159:  Add_sqlset_reference Parameters

alter_sql_profile is a dbms_sqltune procedure that modified the specified attributes of a pre-existing SQL Profile. The legal values for attributes to change are STATUS, NAME, DESCRIPTION and CATEGORY.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

NAME

VARCHAR2

IN

 

ATTRIBUTE

VARCHAR2

IN

 

VALUE

VARCHAR2

IN

 

Table 7.160:  Alter_sql_profile Parameters

cancel_tuning_task is a dbms_sqltune procedure that terminates the currently executing tuning task with a complete cleanup where all intermediate data is deleted.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

Table 7.161:  Cancel_tuning_task Parameter

capture_cursor_cache_sqlset is a dbms_sqltune procedure that incrementally captures workload activity from the cursor cache into a SQL Tuning Set (STS). The capture time is expressed in seconds, the capture option can be INSERT, UPDATE or MERGE, and the capture mode can be either MODE_REPLACE_OLD_STATS or MODE_ACCUMULATE_STATS.

 

ARGUMENT

TYPE

IN/OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

TIME_LIMIT

POSITIVE

IN

1800

REPEAT_INTERVAL

POSITIVE

IN

300

CAPTURE_OPTION

VARCHAR2

IN

‘MERGE’

CAPTURE_MODE

NUMBER

IN

MODE_REPLACE_OLD_STATS

BASIC_FILTER

VARCHAR2

IN

NULL

SQLSET_OWNER

VARCHAR2IN

IN

NULL

Table 7.162:  Capture_cursor_cache_sqlset Parameters

create_sqlset is a both a dbms_sqltune procedure and function that creates a new SQL Tuning Set (STS). When used as a function, it returns the name of the new SQL Tuning Set as a VARCHAR2.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

DESCRIPTION

VARCHAR2

IN

NULL

SQLSET_OWNER

VARCHAR2

IN

NULL

Table 7.163:  Create_sqlset Parameters

create_stgtab_sqlprof is a dbms_sqltune procedure that creates a staging table for copying SQL Profiles across systems, like those for export and import operations.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TABLE_NAME

VARCHAR2

IN

 

Table 7.164:  Create_stgtab_sqlprof Parameter

create_stgtab_sqlset_sqlprofis a procedure that creates a staging table for copying SQL Tuning Sets (STS) across systems, like those for export and import operations.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TABLE_NAME

VARCHAR2

IN

 

SCHEMA_NAME

VARCHAR2

IN

NULL

TABLESPACE_NAME

VARCHAR2

IN

NULL

Table 7.165:  Create_stgtab_sqlset_sqlprof Parameters

create_tuning_task is an overloaded function for creating tuning tasks via a SQL statement’s text, a SQL statement’s cursor cache identifier, an AWR range of snapshot identifiers, or a SQL Tuning Set. In all cases, it returns the new tuning task name as a VARCHAR2. Create_tuning_task based upon a SQL statement’s text is as follows:

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQL_TEXT

CLOB

IN

 

BIND_LIST

SQLBinds

IN

NULL

USER_NAME

VARCHAR2

IN

NULL

SCOPE

VARCHAR2

IN

SCOPE_COMPREHENSIVE

TIME_LIMIT

NUMBER

IN

TIME_LIMIT_DEFAULT

TASK_NAME

VARCHAR2

IN

NULL

DESCRIPTION

VARCHAR2

IN

NULL

Table 7.166:  Create_tuning_task Parameters Based on SQL Statement’s Text

create_tuning_task  based upon a SQL statement’s cursor cache identifier is as follows:

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQL_ID

VARCHAR2

IN

 

PLAN_HASH_VALUE

NUMBER

IN

NULL

SCOPE

VARCHAR2

IN

SCOPE_COMPREHENSIVE

TIME_LIMIT

NUMBER

IN

TIME_LIMIT_DEFAULT

TASK_NAME

VARCHAR2

IN

NULL

DESCRIPTION

VARCHAR2

IN

NULL

Table 7.167:  Create_tuning_task Parameters Based on SQL Statement’s Cursor Cache Identifier

create_tuning_task based upon an AWR range of snapshot identifiers is as follows:

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

BEGIN_SNAP

NUMBER

IN

 

END_SNAP

NUMBER

IN

 

SQL_ID

VARCHAR2

IN

 

PLAN_HASH_VALUE

NUMBER

IN

NULL

SCOPE

VARCHAR2

IN

SCOPE_COMPREHENSIVE

TIME_LIMIT

NUMBER

IN

TIME_LIMIT_DEFAULT

TASK_NAME

VARCHAR2

IN

NULL

DESCRIPTION

VARCHAR2

IN

NULL

Table 7.168:  Create_tuning_task Parameters Based on AWR range of snapshot identifiers

create_tuning_task based upon an SQL Tuning Set (STS) is as follows:

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

BASIC_FILTER

VARCHAR2

IN

NULL

OBJECT_FILTER

VARCHAR2

IN

NULL

RANK1

VARCHAR2

IN

NULL

RANK2

VARCHAR2

IN

NULL

RANK3

VARCHAR2

IN

NULL

RESULT_PERCENTAGE

NUMBER

IN

NULL

RESULT_LIMIT

NUMBER

IN

NULL

SCOPE

VARCHAR2

IN

SCOPE_COMPREHENSIVE

TIME_LIMIT

NUMBER

IN

TIME_LIMIT_DEFAULT

TASK_NAME

VARCHAR2

IN

NULL

DESCRIPTION

VARCHAR2

IN

NULL

PLAN_FILTER

VARCHAR2

IN

‘MAX_ELAPSED_TIME’

SQLSET_OWNER

VARCHAR2

IN

NULL

Table 7.169:  Create_tuning_task Parameters Based on STS

delete_sqlset is a procedure that deletes or drops the SQL statements from an STS.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

BASIC_FILTER

VARCHAR2

IN

NULL

SQLSET_OWNER

VARCHAR2

IN

NULL

Table 7.170:  Delete_sqlset Parameters

drop_sql_profile is a procedure that  drops the user specified SQL Profile.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

NAME

VARCHAR2

IN

 

IGNORE

BOOLEAN

IN

FALSE

Table 7.171:  Drop_sql_profile Parameters

drop_sqlset is a procedure that deletes the user specified STS and it must be inactive.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

SQLSET_OWNER

VARCHAR2

IN

NULL

Table 7.172:  Drop_sqlset Parameters

drop_tuning_task is a procedure that deletes the user specified SQL tuning task. The task and all intermediate data are deleted.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

Table 7.173:  Drop_tuning_task Parameters

execute_tuning_task is both a procedure and function that executes a pre-existing SQL tuning task. As a function, it returns the new task’s name as a VARCHAR2.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

EXECUTION_NAME

VARCHAR2

IN

NULL

EXECUTION_PARAMS

DBMS_ADVISOR.ArgList

IN

NULL

EXECUTION_DESC

VARCHAR2

IN

NULL

Table 7.174:  Execute_tuning_task Parameters

implement_tuning_task is a procedure that implements the SQL Profile recommendations made by the SQL Tuning Advisor .

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

REC_TYPE

VARCHAR2

IN

REC_TYPE_SQL_PROFILES

ONWER_NAME

VARCHAR2

IN

NULL

EXECUTION_NAME

VARCHAR2

IN

NULL

Table 7.175:  Implement_tuning_task Parameters

interrupt_tuning_task is a procedure that interrupts the currently running tuning task but does so as a normal type exit, and thus leaves intermediate results for review.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

Table 7.176:  Interrupt_tuning_task Parameters

load_sqlset is a procedure that populates the user specified SQL Tuning Set (STS) with the set of selected SQL statements. It can be run multiple times and the results are simply aggregated. The valid load options are INSERT, UPDATE and MERGE. The valid update options are REPLACE and ACCUMULATE. The valid update conditions are OLD and NEW. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

POPULATE_CURSOR

SQLSet_Cursor

IN

 

LOAD_OPTION

VARCHAR2

IN

‘INSERT’

UPDATE_OPTION

VARCHAR2

IN

‘REPLACE’

UPDATE_CONDITION

VARCHAR2

IN

NULL

UPDATE_ATTRIBUTES

VARCHAR2

IN

NULL

IGNORE_NULL

BOOLEAN

IN

TRUE

COMMIT_ROWS

POSITIVE

IN

NULL

SQLSET_OWNER

VARCHAR2

IN

NULL

Table 7.177:  Load_sqlset Parameters

pack_stgtab_sqlprof is a procedure that simply copies the SQL Profile data from SYS to the user specified staging table.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

PROFILE_NAME

VARCHAR2

IN

‘%’

PROFILE_CATEGORY

VARCHAR2

IN

‘DEFAULT’

STAGING_TABLE_NAME

VARCHAR2

IN

 

STAGING_SCHEMA_OWNER

VARCHAR2

IN

NULL

Table 7.178:  Pack_stgtab_sqlprof Parameters

pack_stgtab_sqlset is a procedure that simply copies the SQL Tuning Set from SYS to the user specified staging table.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

SQLSETOWNER

VARCHAR2

IN

NULL

STAGING_TABLE_NAME

VARCHAR2

IN

 

STAGING_SCHEMA_OWNER

VARCHAR2

IN

NULL

Table 7.179:  Pack_stgtab_sqlset Parameters

remap_stgtab_sqlprof is a procedure that permits modifications to the SQL Profile data in the staging table prior to extraction.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

OLD_PROFILE_NAME

VARCHAR2

IN

 

NEW_PROFILE_NAME

VACRHAR2

IN

NULL

PROFILE_CATEGORY

VARCHAR2

IN

‘DEFAULT’

STAGING_TABLE_NAME

VARCHAR2

IN

 

STAGING_SCHEMA_OWNER

VARCHAR2

IN

NULL

Table 7.180:  Remap_stgtab_sqlprof Parameters

remap_stgtab_sqlset is a procedure that permits modifications to the STS data in the staging table prior to extraction.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

OLD_SQLSET_NAME

VARCHAR2

IN

 

OLD_SQLSET_OWNER

VACRHAR2

IN

NULL

NEW_SQLSET_NAME

VACRHAR2

IN

NULL

NEW_SQLSET_OWNER

VACRHAR2

IN

NULL

STAGING_TABLE_NAME

VARCHAR2

IN

 

STAGING_SCHEMA_OWNER

VARCHAR2

IN

NULL

Table 7.181:  Remap_stgtab_sqlset Parameters

remove_sqlset_reference is a procedure that drops an existing reference to a pre-existing SQL Tuning Set.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

REFERENCE_ID

NUMBER

IN

 

Table 7.182:  Remove_sqlset_reference Parameters

report_auto_tuning_task is a function that displays a report based upon the automatic tuning task data. It returns that report as a CLOB.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

BEGIN_EXEC

VARCHAR2

IN

NULL

END_EXEC

VACRHAR2

IN

NULL

TYPE

VACRHAR2

IN

TYPE_TEXT

LEVEL

VACRHAR2

IN

LEVEL_TYPICAL

SECTION

VARCHAR2

IN

SECTION_ALL

OBJECT_ID

NUMBER

IN

NULL

RESULT_LIMIT

VARCHAR2

IN

NULL

SEGMENT_SCHEME

VARCHAR2

IN

SEGMENT_NONE

Table 7.183:  Report_auto_tuning_task Parameters

report_sql_monitoris a function that displays a report based upon the real time SQL monitoring data being captured. It returns that report as a CLOB.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQL_ID

VARCHAR2

IN

NULL

SESSION_ID

NUMBER

IN

NULL

SESSION_SERIAL

NUMBER

IN

NULL

SQL_EXEC_START

DATE

IN

NULL

SQL_EXEC_ID

NUMBER

IN

NULL

INST_ID

NUMBER

IN

-1

START_TIME_FILTER

DATE

IN

NULL

END_TIME_FILTER

DATE

IN

NULL

INSTANCE_ID_FILTER

NUMBER

IN

NULL

PARALLEL_FILTER

VARCHAR2

IN

NULL

EVENT_DETAIL

VARCHAR2

IN

‘YES’

REPORT_LEVEL

VARCHAR2

IN

‘TYPICAL’

TYPE

VARCHAR2

IN

‘TEXT’

Table 7.184:  Report_sql_monitor Parameters

report_tuning_task  is a function that displays a report based upon the tuning task data. It returns that report as a CLOB.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

TYPE

VACRHAR2

IN

TYPE_TEXT

LEVEL

VACRHAR2

IN

LEVEL_TYPICAL

SECTION

VACRHAR2

IN

SECTION_ALL

OBJECT_ID

NUMBER

IN

NULL

RESULT_LIMIT

NUMBER

IN

NULL

OWNER_NAME

VARCHAR2

IN

NULL

EXECUTION_NAME

VARCHAR2

IN

NULL

Table 7.185:  Report_tuning_task Parameters

reset_tuning_task is a procedure that prepares a non-active SQL tuning task for re-execution, i.e. resets it to starting state.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

Table 7.186: Reset_tuning_task Parameter

resume_tuning_task is a procedure that resumes execution of a non-active SQL tuning task that was previously interrupted, meaning stopped and in a re-executable state. Note that one cannot resume a task to tune a single SQL statement. This procedure is for resuming those based on SQL Tuning Sets.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

BASIC_FILTER

VARCHAR2

IN

NULL

Table 7.187:  Resume_tuning_task Parameters

script_tuning_task is a function that generates a SQL*Plus script to implement a set of tuning advisor recommendations. It returns that SQL script as a CLOB. The valid record types include ALL, PROFILES, STATISTICS and any comma separated list combining these values.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

REC_TYPE

VACRHAR2

IN

REC_TYPE_ALL

OBJECT_ID

NUMBER

IN

NULL

RESULT_LIMIT

NUMBER

IN

NULL

OWNER_NAME

VACRHAR2

IN

NULL

EXECUTION_NAME

VACRHAR2

IN

NULL

Table 7.188:  Script_tuning_task Parameters

select_cursor_cache is a function that collects SQL statements live from the cursor cache and returns them one at a time, i.e. pipelined function, via a return type of SYS.SQLSET. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

BASIC_FILTER

VACRHAR2

IN

NULL

OBJECT_FILTER

VACRHAR2

IN

NULL

RANKING_MEASURE1

VACRHAR2

IN

NULL

RANKING_MEASURE2

VACRHAR2

IN

NULL

RANKING_MEASURE3

VACRHAR2

IN

NULL

RESULT_PERCENTAGE

NUMBER

IN

1

RESULT_LIMIT

NUMBER

IN

NULL

ATTRIBUTE_LIST

VACRHAR2

IN

NULL

Table 7.189:  Select_cursor_cache Parameters

select_sqlset is a function that reads the contents of a STS and returns those values one at a time via a return type of SYS.SQLSET. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

BASIC_FILTER

VACRHAR2

IN

NULL

OBJECT_FILTER

VACRHAR2

IN

NULL

RANKING_MEASURE1

VACRHAR2

IN

NULL

RANKING_MEASURE2

VACRHAR2

IN

NULL

RANKING_MEASURE3

VACRHAR2

IN

NULL

RESULT_PERCENTAGE

NUMBER

IN

1

RESULT_LIMIT

NUMBER

IN

NULL

ATTRIBUTE_LIST

VACRHAR2

IN

NULL

PLAN_FILTER

VACRHAR2

IN

NULL

SQLSET_OWNER

VACRHAR2

IN

NULL

Table 7.190:  Select_sqlset Parameters

select_workload_repository is an overloaded function that scans the contents of a range of AWR snapshots or a snapshot baseline and returns those values one at a time via a return type of SYS.SQLSET. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes. The format for select_workload_repository when working off snapshot ranges is as follows:

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

BEGIN_SNAP

VARCHAR2

IN

 

END_SNAP

VARCHAR2

IN

 

BASIC_FILTER

VACRHAR2

IN

NULL

OBJECT_FILTER

VACRHAR2

IN

NULL

RANKING_MEASURE1

VACRHAR2

IN

NULL

RANKING_MEASURE2

VACRHAR2

IN

NULL

RANKING_MEASURE3

VACRHAR2

IN

NULL

RESULT_PERCENTAGE

NUMBER

IN

1

RESULT_LIMIT

NUMBER

IN

NULL

ATTRIBUTE_LIST

VACRHAR2

IN

NULL

Table 7.191:  Select_workload_repository Parameters Working off Snapshot Ranges

The format for select_workload_repositorywhen working off a baseline representing a range of snapshots is as follows:

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

BASELINE_NAME

VARCHAR2

IN

 

BASIC_FILTER

VACRHAR2

IN

NULL

OBJECT_FILTER

VACRHAR2

IN

NULL

RANKING_MEASURE1

VACRHAR2

IN

NULL

RANKING_MEASURE2

VACRHAR2

IN

NULL

RANKING_MEASURE3

VACRHAR2

IN

NULL

RESULT_PERCENTAGE

NUMBER

IN

1

RESULT_LIMIT

NUMBER

IN

NULL

ATTRIBUTE_LIST

VACRHAR2

IN

NULL

Table 7.192: Select_workload_repository Parameters Working off A Baseline

set_tuning_task_parameteris an overloaded procedure that updates a SQL Tuning parameter of type VARCHAR2 or NUMBER to a user specified value. The valid values for parameter are MODE, USERNAME, DAYS_TO_EXPIRE, EXECUTION_DAYS_TO_EXPIRE, DEFAULT_EXECUTION_TYPE, TIME_LIMIT, LOCAL_TIME_LIMIT, TEST_EXECUTE, BASIC_FILTER, OBJECT_FILTER, PLAN_FILTER, RANK_MEASURE1, RANK_MEASURE2, RANK_MEASURE3, RESUME_FILTER, SQL_LIMIT, SQL_PERCENTAGE, ACCEPT_SQL_PROFILES, MAX_AUTO_SQL_PROFILES, and MAX_SQL_PROFILES_PER_EXEC.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

TASK_NAME

VARCHAR2

IN

 

PARAMETER

VACRHAR2

IN

 

VALUE

VACRHAR2 |

NUMBER

IN

 

Table 7.193:  Set_tuning_task_parameter Parameters

sqltext_to_signature is a function that simply returns the signature for user specified SQL text. The value is returned as a NUMBER.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQL_TEXT

CLOB

IN

 

FORCE_MAATCH

BOOLEAN

IN

FALSE

Table 7.194:  Sqltext_to_signature Parameters

unpack_stgtab_sqlprof is a procedure that copies the SQL Profile data from the user specified staging table to SYS, thus creating a profile.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

PROFILE_NAME

VARCHAR2

IN

‘%’

PROFILE_CATEGORY

VARCHAR2

IN

‘DEFAULT’

REPLACE

BOOLEAN

IN

 

STAGING_TABLE_NAME

VARCHAR2

IN

 

STAGING_SCHEMA_OWNER

VARCHAR2

IN

NULL

Table 7.195:  Unpack_stgtab_sqlprof Parameters

unpack_stgtab_sqlset is a procedure that simply copies the SQL Tuning Set from the user specified staging table to SYS, thus creating a STS.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

‘%’

SQLSET_OWNER

VARCHAR2

IN

NULL

REPLACE

BOOLEAN

IN

 

STAGING_TABLE_NAME

VARCHAR2

IN

 

STAGING_SCHEMA_OWNER

VARCHAR2

IN

NULL

Table 7.196:  Unpack_stgtab_sqlset Parameters

update_sqlset  is an overloaded procedure that modifies the user selected fields for SQL statements within a SQL Tuning Set (STS).

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

SQLSET_NAME

VARCHAR2

IN

 

SQLSETOWNER

VARCHAR2

IN

 

ATTRIBUTE_NAME

VARCHAR2

IN

 

ATTRIBUTE_VALUE

VARCHAR2 |

NUMBER

IN

NULL

Table 7.197:  Update_sqlset Parameters

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 


 

 

  
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.