Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • column level grant syntax Oracle
  • Jai Shree Ram Oracle
  • Removing Ctrl-M from end of line using vi Linux/Unix
  • secure crt settings Linux/Unix
  • Recovering lost SYS password Oracle
  • Unix split command to split files Linux/Unix
  • Nice Article about semaphores and init.ora Processes parameter relations Linux/Unix
  • Export With Query Another Example. Oracle
  • How to Make Trace Files Created by Oracle Readable by All Users ? Oracle
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • First Entry in RAC Oracle
  • Guide to Linux System Command Mastery Linux/Unix
  • mutex in Oracle 10.2.0.2 or Oracle 10g Oracle
  • normal maintenance for exp-imp and renaming table Oracle
  • plan10g.sql good 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

  • Find All internal Parameters Oracle
  • How do I get Oracle to automatically start when my server boots up? Oracle
  • Find all users who have DML privileges Oracle
  • All About Trace Fils Oracle
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • Difference between SYNC and AFFIRM 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • shr1.sql for MTS or Shared server configuration Oracle
  • sid_wise_cursor.sql find open cursor basis on username or SID Oracle
  • Oracle Material from OTN Oracle
  • Check Oracle installed products using one command Oracle
  • find_cons.sql Oracle
  • Optimizer SORT Operations Oracle
  • find_err.sql for finding errors from dba_errors. Oracle
  • Standby Database Behavior when a Datafile is Resized on the Primary Database Note:123883.1 Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme