SQL tuning is one of the main duty covered by Database Administrator. It is not easy and requires both technology knowledge and sql-development skills.
It is usually an emergency and reactive task: DBAs are asked to improve sql performance of already deployed and production-running queries. In many cases, performance problems are related to poorly designed queries or system resource bottle-necks. So, it is not always true that change a query plan could results in a complete disappearance of performance issues.
But, under certain circumstances, Oracle Optimizer doesn’t choose the best performing plan. There are several cases when this could happen: for instance, when RDBMS parameters are not properly set taking into account application patterns, and the other way around, when Application are not completely aware of the system they are using.
In these cases, could be useful to force the sql statement to switch from the current plan to another best one. This can be done in few ways. I think that the most used and mainstream way to do that is by using the coe_xfr_sql_profile.sql script (by Carlos Sierra). Basically, this script allows administrator to force a statement to uses an already-used (in the past) execution plan.
Actually, the coe_xfr script executes nothing more than a sql profile swapping: creates a Sql Profile from past execution plan outlines and associates this new profile to the indicated sql_id.
In this article we are going to see from closer, how the profile swapping could be executed manually because there could be few cases in which coe_xfr script can’t be used. For example, when you want that your query uses the execution plan of another statement. In these cases, the coe_xfr script can’t be used because the execution plan we want that our query uses is not available as past query plan. Of course, in order to do that, we have to be sure that the two queries are accessing same objects in a similar way.
For simplicity, in this article will be explained how a query can be forced to uses an index by creating a profile using outlines. So, we are just going to swap the sql profile of an execution plan that we want to force to our query. Anyway, this is the same procedure you would use to force the execution plan of a query to a different sql statement.
Oracle Stored Outlines
From Oracle Documentation, a stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
So, the outline is an object that force the optimizer to set a certain execution plan to a query. In order to force a query to uses the same sql plan of another (or past) sql statement, we can switch stored outlines from a performing query plan to the one we want optimize.
Let’s see how Oracle SQL Profile Swapping works.
SQL Profile Swapping example
In this example, we are going to use the testing_table created and populated in the following way:
--------------------------------------------------------------------------- -- CREATE AND POPULATE TABLE --------------------------------------------------------------------------- SQL> -- create table SQL> create table test.testing_table( NOME1 varchar2(20), NOME2 varchar2(20), NUM number) tablespace users; Table created. SQL> -- PL/SQL Block that populates the testing_table SQL> declare v_id number := 123 ; v_name1 varchar2(20) ; v_name2 varchar2(20); v_num number ; begin while (v_id < 1000) loop v_name1 := 'Office' || v_id; v_name2 := 'Test' || v_id; v_num := v_id * 100; INSERT INTO test.testing_table (NOME1,NOME2,NUM) values(v_name1,v_name2,v_num); v_id := v_id +1; commit; end loop; end; / PL/SQL procedure successfully completed.
Our sql statements will be the following one:
SQL> -- execution of the statement for the first time SQL> set lines 300 SQL> set pages 400 SQL> select * from test.testing_table where nome1 like 'Office%'; ... ... Office984 Test984 98400 Office985 Test985 98500 Office986 Test986 98600 Office987 Test987 98700 Office988 Test988 98800 Office989 Test989 98900 Office990 Test990 99000 Office991 Test991 99100 Office992 Test992 99200 Office993 Test993 99300 Office994 Test994 99400 Office995 Test995 99500 Office996 Test996 99600 Office997 Test997 99700 Office998 Test998 99800 Office999 Test999 99900 877 rows selected.
Just by executing the query over this new table, we are causing the creation of a sql_id (in this case the da6w40x5k3jtw):
-------------------------------------------------------------------------------- -- FIND SQL_ID -------------------------------------------------------------------------------- SQL> select sql_id, sql_text from v$sqltext where sql_text like '%testing_table%'; SQL_ID SQL_TEXT ------------- ---------------------------------------------------------------- 2zmpwxk8snza5 := v_id * 100; INSERT INTO test.testing_table (NOME1,NOME2,NU bp4ncc82wu1jy create table test.testing_table( NOME1 varchar2(20), NOME2 varch da6w40x5k3jtw select * from test.testing_table where nome1 like 'Office%'
Consequently, the execution plan of this query can be extracted directly from the PLAN_TABLE executing the explan plan statement:
-------------------------------------------------------------------------------- -- PLAN_TABLE -------------------------------------------------------------------------------- explain plan for select * from test.testing_table where nome1 like 'Ufficio%'; select * from table(dbms_xplan.display); Plan hash value: 1011028032 ------------------------------------------------------------------------------- | Id | Operation | Name | R | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 37 | 3 (0) | 00:00:01 | |* 1 | TABLE ACCESS FULL| TESTING_TABLE | 1 | 37 | 3 (0) | 00:00:01 | -------------------------------------------------------------------------------
As can be seen, the execution plan is very simple and consists of a Full Table Scan over the Testing_table.
Now, we can think that this execution plan can be improved by the creation of an index in the nome1 column of the table:
--------------------------------------------------------------------------------
-- CREATE INDEX
--------------------------------------------------------------------------------
SQL> create index test.testing_table_index on test.testing_table(NOME1) tablespace users;
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats('test', 'testing_table');
PL/SQL procedure successfully completed.
This is not actually true because the ‘Office%’ filter is a poor filter and in order to be satisfied, every row in the table should be read.
In fact, even after having created the index (and flushed the cursor from the shared pool), Oracle still continues to perform a full table scan over this testing_table:
--------------------------------------------------------------------------------
-- SELECT: SQL_ID CREATION (after having flushed the cursor)
--------------------------------------------------------------------------------
SQL> select * from test.testing_table where nome1 like 'Office%';
Office650 Test650 65000
Office651 Test651 65100
Office652 Test652 65200
Office653 Test653 65300
Office654 Test654 65400
Office655 Test655 65500
Office656 Test656 65600
Office657 Test657 65700
Office658 Test658 65800
Office659 Test659 65900
Office660 Test660 66000
877 rows selected.
SQL> -- execution plan from cursor cache
SQL> select plan_table_output from
SQL> table(dbms_xplan.display_cursor('da6w40x5k3jtw',null,'advanced'));
SQL> Plan hash value: 1011028032
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100) |
|* 1 | TABLE ACCESS FULL| TESTING_TABLE | 876 | 19272 | 3 (0) | 00:00:01
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TESTING_TABLE@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_fix_control' '26019148:1 25476149:1 23249829:1 26536320:1
26986173:1 25120742:1 20107874:1 27466597:1 27321179:1 26423085:1
28072567:1 25405100:1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTING_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
As said, the plan remains the same (1011028023).
In this case we have extracted the plan directly from the cursor with the advanced parameter that shows even the Outline Data. This Outline Data is nothing more than the Stored Outlines we were talking about in the previous section of this article.
But, let’s imagine that for some reason we want to force the optimizer to use the index, regardless the fact that this could downgrade performances, how can we force the optimizer to do it?
We could do in several ways. For example, we can use an optimizer hints or we can change the optimizer_index_cost_adj parameter that lets you tune optimizer behavior for access path selection to be more index friendly.
Setting the optimizer_index_cost_adj to 1 at session level would forces the optimizer to uses an index scan as table access method:
SQL> -- change optimizer_index_cost_adj at session level
SQL> alter session set optimizer_index_cost_adj=1;
Session altered.
SQL> explain plan for
SQL> select * from test.testing_table where nome1 like 'Office%';
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL, 'OUTLINE'));
Plan hash value: 3640206530
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 876 | 19272 | 1 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX R| TESTING_TAB | 876 | 19272 | 1 (0)| 00:00:01
|* 2 | INDEX RANGE SCAN | TESTING_TAX | 876 | | 1 (0)| 00:00:01
----------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TESTING_TABLE"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TESTING_TABLE"@"SEL$1" ("TESTING_TABLE"."NOME1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_fix_control' '26019148:1 25476149:1 23249829:1 26536320:1 26986173:1 25120742:1
20107874:1 27466597:1 27321179:1 26423085:1 28072567:1 25405100:1')
OPT_PARAM('optimizer_index_cost_adj' 1)
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
In order to make this change permanent, we need to modify the code (adding an optimizer hint) or set the optimizer_index_cost_adj to 1 at system level, but in many cases we can’t implement this kind of modifications for several reason.
In this example the SQL Profile Swapping could be implemented just by using the coe_xfr_sql_profile.sql scripts, but let’s assume that the new plan (3640206530) is associated to another different sql_id (and not to the da6w40x5k3jtw). The coe_xfr script would not prompt this plan as one of the alternative.
What we can do is to generate a SQL Profile using Outlines of 3640206530 plan and associate this profile to the text of the da6w40x5k3jtw statement.
This can be done using the DBMS_SQLTUNE package:
--------------------------------------------------------------------------------
-- Create a sql profile with outlines
--------------------------------------------------------------------------------
SQL>
declare
ar_profile_hints sys.sqlprof_attr;
SQL_FTEXT CLOB;
-- sys.sqlprof_attr must contains the OUTLINE DATA of the 3640206530 plan
BEGIN
ar_profile_hints := sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TESTING_TABLE"@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "TESTING_TABLE"@"SEL$1" ("TESTING_TABLE"."NOME1"))OUTLINE_LEAF(@"SEL$1")',
'ALL_ROWS',
'OPT_PARAM(''_fix_control'' ''26019148:1 25476149:1 23249829:1 26536320:1 26986173:1 25120742:120107874:1 27466597:1 27321179:1 26423085:1 28072567:1 25405100:1'')',
'OPT_PARAM(''optimizer_index_cost_adj'' 1)',
'OPT_PARAM(''_optimizer_aggr_groupby_elim'' ''false'')',
'DB_VERSION(''12.2.0.1'')',
'OPTIMIZER_FEATURES_ENABLE(''12.2.0.1'')',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'END_OUTLINE_DATA',
);
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'da6w40x5k3jtw';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
profile => ar_profile_hints,
name => 'PROFILE_da6w40x5k3jtw'
);
END;
/
PL/SQL procedure successfully completed.
SQL> -- check sql profile just created
SQL> select name, sql_text,status from dba_sql_profiles
NAME STATUS --------------------------- -----------
PROFILE_da6w40x5k3jtw ENABLED
As you can see, the sys.sqlprof_attr contains the 3640206530 outlines (pay attention to the quotes).
Now, executing the statement from a new session without having changed the code or any session parameter, we could notice that the index is used and the hash plan is 3640206530 as expected:
-----------------------------------------------------------------------------
-- check sql_id
-----------------------------------------------------------------------------
SQL> set autotrace traceonly exp
SQL> select * from test.testing_table where nome1 like 'Office%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3640206530
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 876 | 19272 | 1 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX R| TESTING_TAB | 876 | 19272 | 1 (0)| 00:00:01
|* 2 | INDEX RANGE SCAN | TESTING_TAX | 876 | | 1 (0)| 00:00:01
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NOME1" LIKE 'Office%')
filter("NOME1" LIKE 'Office%')
Note
-----
- SQL profile "PROFILE_da6w40x5k3jtw" used for this statement
In addition, as Note field in the explain plan, we can find the information about the Sql Profile just created. This means that this execution plan has been generated taking into account that da6w40x5k3jtw sql_id has a profile associated.
Even if explained with this simple example, Sql Profile swapping could solve more interesting and critical issues just by doing the same things described in this article. In addition, extracting outlines from a sql plan and manually creating a Sql Profile is a method that in some case could give Administrators more control in the Sql Tuning process than using the coe_xfr script.
credit: https://www.linkedin.com/pulse/oracle-sql-profile-swapping-diego-zucca/