Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Generate SSH without password authentication. Linux/Unix
  • create trigger syntax Oracle
  • Passing from Unix to PLSQL using bind variables Linux/Unix
  • Good Doc 28-JUN-2006 Oracle
  • Oracle Recommended Patches — Oracle Database ID 756671.1 Oracle
  • metalink all dynamic view reference notes. Oracle
  • DBMS_JOB all example Oracle
  • Reclaim temp tablespace for oracle 8, 8i Oracle
  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • Good notes on Oracle Events Oracle
  • 272332.1 CRS 10g Diagnostic Collection Guide Oracle
  • Locktree.sql Oracle
  • How to specify 2 arch location to avoid any kind of DB hanging. Oracle
  • Query to Generate aggregate on every 30 mins. Oracle
  • TNSNAMES entries details Oracle

SQL_PROFILE – I explaination

Posted on 22-Dec-2008 By Admin No Comments on SQL_PROFILE – I explaination

Checked for relevance on 12-May-2008.

Clarification/Explanation

==========================

The query optimizer can sometimes produce inaccurate estimates about

an attribute of a statement due to lack of information,leading to poor

execution plans. Traditionally, users have corrected this problem by

manually adding hints to the application code to guide the optimizer

into making correct decisions. For packaged applications, changing

application code is not an option and the only alternative available

is to log a bug with the application vendor and wait for a fix.

Automatic SQL Tuning deals with this problem with its SQL Profiling

capability. The Automatic Tuning Optimizer creates a profile of the

SQL statement called a SQL Profile, consisting of auxiliary statistics

specific to that statement. The query optimizer under normal mode makes

estimates about cardinality, selectivity, and cost that can sometimes be

off by a significant amount resulting in poor execution plans. SQL Profile

addresses this problem by collecting additional information using

sampling and partial execution techniques to verify and, if necessary,

adjust these estimates.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution

history information of the SQL statement to appropriately set optimizer

parameter settings, such as changing the OPTIMIZER_MODE initialization

parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the

SQL Profile. A SQL Profile, once accepted, is stored persistently in

the data dictionary. Note that the SQL Profile is specific to a

particular query. If accepted, the optimizer under normal mode uses the

information in the SQL Profile in conjunction with regular

database statistics when generating an execution plan.

The availability of the additional information makes it possible

to produce well-tuned plans for corresponding SQL statement without

requiring any change to the application code.

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute.

This attribute determines which user sessions can apply the profile.

You can view the CATEGORY attribute for a SQL Profile in CATEGORY column

of the DBA_SQL_PROFILES view. By default, all profiles are created in the

DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY

initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you can determine which sessions are

affected by the creation of a profile. For example, by setting the category

of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY

initialization parameter is set to DEV can use the profile. All other sessions

do not have access to the SQL Profile and execution plans for SQL statements

are not impacted by the SQL profile. This technique enables you to test

a SQL Profile in a restricted environment before making it available to other

user sessions.

It is important to note that the SQL Profile does not freeze the execution plan

of a SQL statement, as done by stored outlines. As tables grow or indexes

are created or dropped, the execution plan can change with the same SQL Profile.

The information stored in it continues to be relevant even as the data

distribution or access path of the corresponding statement change.

However, over a long period of time, its content can become outdated and

would have to be regenerated. This can be done by running Automatic

SQL Tuning again on the same statement to regenerate the SQL Profile.

SQL Profiles apply to the following statement types:

SELECT statements

UPDATE statements

INSERT statements (only with a SELECT clause)

DELETE statements

CREATE TABLE statements (only with the AS SELECT clause)

MERGE statements (the update or insert operations)

Managing SQL Profiles

=====================

While SQL Profiles are usually handled by Oracle Enterprise Manager as part

of the Automatic SQL Tuning process, SQL Profiles can be managed through the

DBMS_SQLTUNE package. To use the SQL Profiles APIs, you need the

CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE

system privileges.

Accepting a SQL Profile

=======================

You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a

SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a

SQL Profile in the database. For example:

DECLARE

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => ‘my_sql_tuning_task’,

name => ‘my_sql_profile’);

END;

Where my_sql_tuning_task is the name of the SQL tuning task.

You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

======================

You can alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of

an existing SQL Profile with the ALTER_SQL_PROFILE procedure. For example:

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => ‘my_sql_profile’,

attribute_name => ‘STATUS’,

value => ‘DISABLED’);

END;

/

In this example, my_sql_profile is the name of the SQL Profile that you want to alter.

The status attribute is changed to disabled which means the SQL Profile is

not used during SQL compilation.

Dropping a SQL Profile

======================

You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘my_sql_profile’);

END;

/

In this example, my_sql_profile is the name of the SQL Profile you want to drop.

You can also specify whether to ignore errors raised if the name does not exist.

For this example, the default value of FALSE is accepted.

Example

=======

SESSION 1 — SCOTT

=========

SQL> create table test (n number );

Table created.

declare

begin

for i in 1 .. 10000

loop

insert into test values(i);

commit;

end loop;

end;

PL/SQL procedure successfully completed.

create index test_idx on test(n);

Index created.

analyze table test estimate statistics (OR use dbms_stats)

Table analyzed.

select /*+ no_index(test test_idx) */ * from test where n=1

Execution Plan

———————————————————-

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)

1 0 TABLE ACCESS (FULL) OF ‘TEST’ (TABLE) (Cost=6 Card=1 Bytes

=13)

SESSION 2 — SYS

=========

1 DECLARE

2 my_task_name VARCHAR2(30);

3 my_sqltext CLOB;

4 BEGIN

5 my_sqltext := ‘select /*+ no_index(test test_idx) */ * from test where n=

1′;

6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

7 sql_text => my_sqltext,

8 user_name => ‘SCOTT’,

9 scope => ‘COMPREHENSIVE’,

10 time_limit => 60,

11 task_name => ‘my_sql_tuning_task_2’,

12 description => ‘Task to tune a query on a specified table’);

13* END;

14 /

PL/SQL procedure successfully completed.

1 BEGIN

2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_2’);

3* end;

SQL> /

PL/SQL procedure successfully completed.

1 SET LONG 1000

2 SET LONGCHUNKSIZE 1000

3 SET LINESIZE 100

4* SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_2’) from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)

——————————————————————————–

——————–

GENERAL INFORMATION SECTION

——————————————————————————-

Tuning Task Name : my_sql_tuning_task_2

Scope : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status : COMPLETED

Started at : 05/04/2004 17:36:05

Completed at : 05/04/2004 17:36:05

——————————————————————————-

SQL ID : d4wgpc5g0s0vu

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)

——————————————————————————–

——————–

SQL Text: select /*+ no_index(test test_idx) */ * from test where n=1

——————————————————————————-

FINDINGS SECTION (1 finding)

——————————————————————————-

1- SQL Profile Finding (see explain plans section below)

——————————————————–

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 83.84%)

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)

——————————————————————————–

——————–

——————————————

Consider accepting the recommended

1 DECLARE

2 my_sqlprofile_name VARCHAR2(30);

3 BEGIN

4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

5 task_name => ‘my_sql_tuning_task_2’,

6 name => ‘my_sql_profile’);

7* END;

8 /

PL/SQL procedure successfully completed.

SQL> select to_char(sql_text) from dba_sql_profiles;

TO_CHAR(SQL_TEXT)

————————————————————————

select /*+ no_index(test test_idx) */ * from test where n=1

SESSION 1 — SCOTT

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan

———————————————————-

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)

1 0 INDEX (RANGE SCAN) OF ‘TEST_IDX’ (INDEX) (Cost=1 Card=1 By

tes=13

Oracle, SQL scripts

Post navigation

Previous Post: shutdown linux
Next Post: Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository

Related Posts

  • Kill a session dynanically using execute immediate Oracle
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • Benefits and Usage of RMAN with Standby Databases Oracle
  • Implementing Listener Security Oracle
  • Wait Based Tuning Step by step with SQL statement Oracle
  • Query to Generate aggregate on every 30 mins. Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Finding locked objects Oracle
  • move_arch_files.ksh Linux/Unix
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1 Oracle
  • Process Map for CPU and Memory for OS processes Linux/Unix
  • compile_inv.sql Oracle
  • Alter procedure auditing Oracle
  • Processes Parameter decision Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme