how to create sql profile manually

After going through number Documents regarding sql profile. i got some solution regarding  how to create sql profile manually. so below is example shows details of creation of sql profile.


1) Session I (connect normal user i used scott user).

SQL> create table test (id number );
Table created.

SQL> declare
          begin
           for i in 1 .. 10000 loop
               insert into test values(i);
               commit;
           end loop;
          end;
/
PL/SQL procedure successfully completed.

SQL> create index test_idx on test(id);
Index created.

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

PL/SQL procedure successfully completed.

sql>set autotrace on

sql>select /*+ no_index(test test_idx) */ * from test where id=1;


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)

2) Session II (connect as sysdba).

Create and execute tuning task and run report tuning task.
Accept recommended SQL Profile.

sql>declare
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  begin
     my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where id=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;
/
PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

set long 10000
set longchunksize 1000
set linesize 100
set heading off
sql>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
set heading on

Output:
            GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_2
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/04/2013 10:27:27
Completed at       : 06/04/2013 10:27:32

-------------------------------------------------------------------------------
Schema Name: SCOTT

SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where id=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: 95%)
  ---------------------------------------

  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2', task_owner => 'SCOTT', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time(us):                 600                40      93.33 %
  CPU Time(us):                     399                 0        100 %
  User I/O Time(us):                  0                 0
  Buffer Gets:                       20                 1         95 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes

  -----
  1. The original plan was first executed to warm the buffer cache.
  2. Statistics for original plan were averaged over next 9 executions.
  3. The SQL profile plan was first executed to warm the buffer cache.
  4. Statistics for the SQL profile plan were averaged over next 9 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
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)


2- Using SQL Profile
--------------------
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)

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



Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
       4134  recursive calls
        954  db block gets
       2614  consistent gets
         50  physical reads
        572  redo size
       5812  bytes sent via SQL*Net to client
       1280  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
         78  sorts (memory)
          0  sorts (disk)
          1  rows processed

sql>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;
/

 

PL/SQL procedure successfully completed.

3) Session I (connect to scott user).

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where id=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
----------------------------------------------------------
          9  recursive calls
          0  db block gets
          7  consistent gets
          1  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
note:- now check CPU usage of particulate query.

hope this document will help you to solve the problem which you face during the creation of sql profile.also i am saying thanks to my friend fahim Mulla who help during creation of this Document.