Using SQL Profiles

The database can profile the following types of statement:

  • DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)
  • CREATE TABLE statements (only with the AS SELECT clause)
  • MERGE statements (the update or insert operations)

 

Views :
dba_sql_profiles;
dba_advisor_log;
dba_advisor_tasks;
v$advisor_progress;

Packages :
DBMS_SQLTUNE.CREATE_TUNING_TASK();
DBMS_SQLTUNE.EXECUTE_TUNING_TASK();
DBMS_SQLTUNE.REPORT_TUNING_TASK();
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE();

Example : 
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 => 'SYS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_1',
description => 'Task to tune a query on a specified table');
end;
/

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/
set long 10000
set longchunksize 1000
set linesize 100
set heading off
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
set heading on
DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_1',
name => 'my_sql_profile');
end;
/

Outputs :

Normal trace output (without sql profiling)


SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
N
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

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

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

   1 - filter("N"=1)
Statistics
----------------------------------------------------------
126  recursive calls
0  db block gets
41  consistent gets
0  physical reads
0  redo size
404  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
4  sorts (memory)
0  sorts (disk)
1  rows processed


 

 

Trace output (using sql profiling)

The result below is after running DBMS_SQLTUNE.REPORT_TUNING_TASK();

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_1
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 12/10/2012 19:28:17
Completed at                      : 12/10/2012 19:28:18
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------------
Schema Name: SYS

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)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 84.54%)
------------------------------------------

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

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

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     6   (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 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     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
----------------------------------------------------------
2674  recursive calls
298  db block gets
861  consistent gets
20  physical reads
0  redo size
4108  bytes sent via SQL*Net to client
987  bytes received via SQL*Net from client
7  SQL*Net roundtrips to/from client
121  sorts (memory)
0  sorts (disk)
1  rows processed

~~~~~~ Above statistics due to first load ~~~~~~
Statistics
---------------------------------------------------------
1  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
404  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

As you can see, the database found a better plan for a SELECT statement that uses index. The recommendation is to run DBMS_SQLTUNE.ACCEPT_SQL_PROFILE to accept the profile.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s