Sunday, August 26, 2012


Steps to Create and Transfer SQL Profile from One Oracle  Database to Another Database


Hello DBAs, In my second post for SQL PROFILE, I am just going to show you how we can transfer the same SQL PROFILE we have accepted in previous post Automatic SQL Tunig and SQL Profile  to another database let say 'TEST'.

So, Here I am again showing you how to create and accept SQL PROFILE that we have done already in my earlier post. Now here is the steps that you'll have to just follow.

1. Create SQL Profile in SCOTT schema
-----------------------------------------

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:


From Scott
-----------

1) create table test (n number );

2)

declare
          begin
           for i in 1 .. 10000 loop
               insert into test values(i);
               commit;
           end loop;
          end;
/



3) create index test_idx on test(n);




4) exec dbms_stats.gather_table_stats('','TEST');




5) Here you can see I am using no_index hint so optimzer will go for full table scan, which is but obvious.



SQL> set autotrace traceonly

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



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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      |     1 |     4 |     5   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     5   (0)| 00:00:01 | 
-------------------------------------------------------------------------- 





FROM SYS USER

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

6) Now, run SQLTUNING task to generate SQL PROFILE and accept the recommended profile. 


declare
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  begin
     my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
     my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
     sql_text => my_sqltext,
     user_name => 'SCOTT',
     scope => 'COMPREHENSIVE',
     time_limit => 60,
     task_name => 'my_sql_tuning_task_2',
     description => 'Task to tune a query on a specified table');
end;
/

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/



7) Now, see the report for SQL PROFILE recommendation. 


SQL> set long 1000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> 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                                            
Tuning Task Owner  : SYS                                                             
Workload Type      : Single SQL Statement                                            
Scope              : COMPREHENSIVE                                                   
Time Limit(seconds): 60                                                              
Completion Status  : COMPLETED                                                       
Started at         : 10/26/2011 15:07:04                                             
Completed at       : 10/26/2011 15:07:08                                             
                                                                                     
                                                                                     
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')                              
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------      
Schema Name: SCOTT                                                                   
SQL ID     : d4wgpc5g0s0vu                                                           
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)                             
--------------------------------------------------------                             
                                                                                     
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')                              
-------------------------------------------------------------------------------------
  A potentially better execution plan was found for this statement.                  
                                                                                     
  Recommendation (estimated benefit: 90.95%)                                         
  ------------------------------------------                                         
  - Consider accepting the recommended SQL profile.                                  
    execute dbms_sqltune.accept_sql_profile(task_name =>                             
            'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);    





9) Finally accept recommended profile.


DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/


AGAIN FROM SCOTT USER

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

10) This time if you will fire the same query again, optimizer will ignore no_index hint and will go for index scan because it will use SQL PROFILE.
----------------------------------------------------------------------------------------------------------------------------------

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


Execution Plan                                                                

-------------------------------------------------------------------------     
Plan hash value: 1416057887                                                   
                                                                              
----------------------------------------------------------------------------- 
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   1 - access("N"=1)                                                          
---------------                                                               
Note                                                                          
-----                                                                         
   - SQL profile "my_sql_profile" used for this statement                     


2. Creating a staging table to store the SQL Profiles
---------------------------------------------------------------

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.


Where,

table_name => name of the table to store the SQL Profiles.
schema_name => name of the schema where the table is to be created.


3. Pack the SQL Profiles into the Staging Table
---------------------------------------------------------

SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile',staging_schema_owner => 'SCOTT');

PL/SQL procedure successfully completed.



SQL> desc STAGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE_NAME                                       VARCHAR2(30)
 CATEGORY                                           VARCHAR2(30)
 SIGNATURE                                          NUMBER
 SQL_TEXT                                           CLOB
 DESCRIPTION                                        VARCHAR2(500)
 TYPE                                               VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 BOOLEAN_FLAGS                                      NUMBER
 ATTRIBUTES                                         SQLPROF_ATTR
 VERSION                                            NUMBER
 SPARE1                                             CLOB
 SPARE2                                             BLOB



4. Export the Staging Table to the Target Database Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.
---------------------------------------------------------------------------------------------


4a. Export from Source Database
-----------------------------------------

[oracle@node1 ~]$ expdp system/oracle_4U directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE

Export: Release 11.2.0.1.0 - Production on Sun Aug 26 20:27:29 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."STAGE"                             22.10 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/expdp/a.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 20:28:08






4b. Import into Target Database
-----------------------------------------

[oracle@node1 ~]$ impdp system/oracle_4U directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE

Import: Release 11.2.0.1.0 - Production on Sun Aug 26 20:28:57 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."STAGE"                             22.10 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 20:29:16




5. Unpack the SQL Profiles so that new database start using imported SQL PROFILE
---------------------------------------------------------------------------------------

5a. Test before unpacking
---------------------------------

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


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

NOTE: Here you can see, optimizer is not using SQL PROFILE because still it is not unpack for use.


5b. Unpack Staging Table from sys
--------------------------------------------


SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SCOTT');

PL/SQL procedure successfully completed.


6. Check the SQL Profile is enabled in Target Database
--------------------------------------------------------

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So finally you can see this time optimizer is using SQL Profile also in TEST DATABASE. :)

Thanks,

Automatic SQL Tunig and SQL Profile


What is Automatic SQL Tuning


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 had to  correct 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. An SQL Profile, once accepted, is stored persistently in the data dictionary. An 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.



What is a SQL Profile



SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.


The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans: The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on.


The supplemental statistics in the SQL profile


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.



How can the scope of the SQL Profile be controlled 



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. 


select category,name from dba_sql_profiles;

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 ABC, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to ABC 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.


To what statements can a SQL Profile be applied?


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)



Automatic SQL Tuning - SQL Profiles
----------------------------------------

In this Demo, I have demonstrated 2 things.


1. How to create SQL PROFILE and accept that.


2. How to control any user from using newely accepted prolfile.





Section-1:

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

PART-1: In this Part i'll show you how to generate SQL PROFILE and how to accept that profile.

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

From Scott

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

1) create table test (n number );


2)

declare
          begin
           for i in 1 .. 10000 loop
               insert into test values(i);
               commit;
           end loop;
          end;
/



3) create index test_idx on test(n);




4) exec dbms_stats.gather_table_stats('','TEST');




5) Here you can see I am using no_index hint so optimzer will go for full table scan, which is but obvious.



SQL> set autotrace traceonly

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



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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      |     1 |     4 |     5   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     5   (0)| 00:00:01 | 
-------------------------------------------------------------------------- 





FROM SYS USER

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

6) Now, run SQLTUNING task to generate SQL PROFILE and accept the recommended profile. 


declare
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  begin
     my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
     my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
     sql_text => my_sqltext,
     user_name => 'SCOTT',
     scope => 'COMPREHENSIVE',
     time_limit => 60,
     task_name => 'my_sql_tuning_task_2',
     description => 'Task to tune a query on a specified table');
end;
/

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/



7) Now, see the report for SQL PROFILE recommendation. 


SQL> set long 1000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> 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                                            
Tuning Task Owner  : SYS                                                             
Workload Type      : Single SQL Statement                                            
Scope              : COMPREHENSIVE                                                   
Time Limit(seconds): 60                                                              
Completion Status  : COMPLETED                                                       
Started at         : 10/26/2011 15:07:04                                             
Completed at       : 10/26/2011 15:07:08                                             
                                                                                     
                                                                                     
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')                              
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------      
Schema Name: SCOTT                                                                   
SQL ID     : d4wgpc5g0s0vu                                                           
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)                             
--------------------------------------------------------                             
                                                                                     
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')                              
-------------------------------------------------------------------------------------
  A potentially better execution plan was found for this statement.                  
                                                                                     
  Recommendation (estimated benefit: 90.95%)                                         
  ------------------------------------------                                         
  - Consider accepting the recommended SQL profile.                                  
    execute dbms_sqltune.accept_sql_profile(task_name =>                             
            'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);    





9) Finally accept recommended profile.


DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/



AGAIN FROM SCOTT USER

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


10) This time if you will fire the same query again, optimizer will ignore no_index hint and will go for index scan because it will use SQL PROFILE.


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


Execution Plan                                                                

-------------------------------------------------------------------------     
Plan hash value: 1416057887                                                   
                                                                              
----------------------------------------------------------------------------- 
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   1 - access("N"=1)                                                          
---------------                                                               
Note                                                                          
-----                                                                         
   - SQL profile "my_sql_profile" used for this statement                     




PART-2:                                                                                                                                                


In part-2 of Section-1, I'll Show you that if current situation is different and you have new statistics for your query and if we have better plan for the query then 


optimzer will accept new plan with the help of new statistics and SQL PROFILE. 


(NOTE- 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.)



1) In your table again inert lots of 1's so that record will be repeated and then see what happen. Will optimizer choose SQL PROFILE or not?



SQL> declare

          begin
           for i in 1 .. 1000000 loop
               insert into test values(1);
               commit;
           end loop;
          end;
/

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats('','TEST');

 PL/SQL procedure successfully completed.






SQL> set autotrace traceonly

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

1010001 rows selected.



Execution Plan

----------------------------------------------------------
Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1011K|  2964K|   450   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| TEST |  1011K|  2964K|   450   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   1 - filter("N"=1)


Note

-----
   - SQL profile "my_sql_profile" used for this statement


Statistics

----------------------------------------------------------
          0  recursive calls
          0  db block gets
      70296  consistent gets
       2581  physical reads
          0  redo size
   17372507  bytes sent via SQL*Net to client
     741186  bytes received via SQL*Net from client
      67335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1010001  rows processed

SQL> 




So finally you can see, optimizer is using SQL profile and new statistics and going for the full table scan, but it is not going for index scan (According to the SQL 


PROFILE).






SECTION-2: HOW TO AVOID ANY USER TO USE SQL PROFILE

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



There are 2 method to implement this.


Method-1:

--------- 

All the profile accepted, they all are accepted as "DEFAULT" CATEGORY. So if you want to avoid any user from using that newely accepted profile then just change their 


session parameter "SQLTUNE_CATEGORY" and put any other category name.



Method-2:

---------

While accepting the sql profile change the category option to any thing else but not "DEFAULT".


1) 


SQL> alter session set sqltune_category=abc;


Session altered.



2) After changing their session parameter, now scott user will not use the accepted profile.


SQL> set autotrace traceonly

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

SQL> 


Execution Plan

----------------------------------------------------------
Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   1 - filter("N"=1)



Statistics

----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



3) Again change the session parameter to default then scott user again start using accepted profile for their query.



SQL> alter session set sqltune_category=default;


Session altered.


SQL>                                    

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

Execution Plan

----------------------------------------------------------
Plan hash value: 2882402178

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

| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   1 - access("N"=1)


Note

-----
   - SQL profile "my_sql_profile" used for this statement


Statistics

----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>