Query being used is-
select * from test where w='sam';
explain plan for select * from test where w='sam';
Explained
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
SYS@SAM AS SYSDBA>
Now we will try to use hint to force the optimizer to choose index by taking the help of sql_profile
SYS@SAM AS SYSDBA> explain plan for select /*+INDEX (TEST) */ * from test where w='sam';
Explained.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3981825257
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 16 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
SYS@SAM AS SYSDBA>
Yes, so using the hint, the optimizer is actually using the index. Now we will create the sql profile
Original plan
SYS@SAM AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display(format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "TEST"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."N"[NUMBER,22], "W"[VARCHAR2,3]
Note
-----
- dynamic sampling used for this statement (level=2)
44 rows selected.
Modified plan or the one which we are telling the optimizer to choose
SYS@SAM AS SYSDBA> explain plan for select /*+INDEX (TEST) */ * from test where w='sam';
Explained.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display(format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3981825257
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 16 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."W"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."N"[NUMBER,22], "W"[VARCHAR2,3]
2 - "TEST".ROWID[ROWID,10], "W"[VARCHAR2,3]
Note
-----
- dynamic sampling used for this statement (level=2)
47 rows selected.
Now we will create the sql profile
SYS@SAM AS SYSDBA> DECLARE
l_sql clob;
BEGIN
l_sql := q'!select * from test where w='sam'!';
dbms_sqltune.import_sql_profile( sql_text => l_sql,
name => 'SQLPROFILE_02',
profile => sqlprof_attr(q'!INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."W"))!',
q'!OUTLINE_LEAF(@"SEL$1")!',
q'!ALL_ROWS!',
q'!OPT_PARAM('_gby_hash_aggregation_enabled' 'false')!',
q'!OPT_PARAM('_fast_full_scan_enabled' 'false')!',
q'!OPT_PARAM('_b_tree_bitmap_plans' 'false')!',
q'!DB_VERSION('11.2.0.3')!',
q'!OPTIMIZER_FEATURES_ENABLE('11.2.0.3')!',
q'!IGNORE_OPTIM_EMBEDDED_HINTS!'),
force_match => true );
end;
/
PL/SQL procedure successfully completed.
Once the procedure is executed, the profile is loaded and enabled.
Now that we have loaded the SQL profile, let’s get the explain plan again and make sure that the SQL profile works:
SYS@SAM AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3981825257
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
Note
-----
- SQL profile "SQLPROFILE_02" used for this statement
18 rows selected.
SYS@SAM AS SYSDBA>
to list of sql profiles, use the below query
SYS@SAM AS SYSDBA> select name, created, status from dba_sql_profiles;
NAME
------------------------------
CREATED STATUS
--------------------------------------------------------------------------- --------
SQLPROFILE_02
22-MAY-17 06.10.14.000000 AM ENABLED
Should we decide that the sql profile doesn't work well as expected, we can drop or disable it
begin dbms_sqltune.alter_sql_profile('SQLPROFILE_02','STATUS','DISABLED'); end;
/
SYS@SAM AS SYSDBA> begin dbms_sqltune.alter_sql_profile('SQLPROFILE_02','STATUS','DISABLED'); end;
2 /
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
SYS@SAM AS SYSDBA> begin dbms_sqltune.drop_sql_profile('SQLPROFILE_02'); end;
2 /
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> select name, created, status from dba_sql_profiles;
no rows selected
No comments:
Post a Comment