This demonstration is based on
Metalink Note 1487302.1 "Using Sqltxplain to create a 'SQL Profile' to consistently reproduce a good plan".
First of all, we need to install the Sqltxplain tool. Go to
Metalink Note 215187.1 "SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results", download
sqlt.zip and install it under your ORACLE_HOME.
Let's test it. First we create 2 tables, based on DBA_OBJECTS and DBA_SEGMENTS and indexes on OBJECT_NAME and SEGMENT_NAME columns respectively.
sqlplus helpdesk
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 21 11:55:47 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLE HELPDESK.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> CREATE INDEX HELPDESK.OBJ_NAME ON HELPDESK.OBJECTS(OBJECT_NAME);
Index created.
SQL> CREATE TABLE HELPDESK.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.
SQL> CREATE INDEX HELPDESK.SEG_NAME ON HELPDESK.SEGMENTS(SEGMENT_NAME);
Index created.
We will use a query that joins those tables using the indexed columns.
SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 917395572
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 298 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 298 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 2 | 158 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | OBJ_NAME | 2 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SEG_NAME | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | SEGMENTS | 1 | 70 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OBJECT_NAME" LIKE 'HELP%')
filter("A"."OBJECT_NAME" LIKE 'HELP%')
5 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
855 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)
2 rows processed
In this execution plan with plan hash value 917395572, both indexes are used.
Let's find out our query's SQL_ID. Run as SYSDBA:
SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT A.OBJECT_ID%';
| SQL_ID | SQL_TEXT |
| 8qu7mmcwnygtv | SELECT A.OBJECT_ID, A.OBJECT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%' |
Now, we will drop index HELPDESK.OBJ_NAME, in order to get a new execution plan for our query, where a full table scan will be used on table HELPDESK.OBJECTS.
SQL> DROP INDEX HELPDESK.OBJ_NAME;
Index dropped.
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 298 | 83 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 298 | 83 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 2 | 140 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 2 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | OBJECTS | 3 | 237 | 79 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
54 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
855 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
A new plan was created with a full table scan on HELPDESK.OBJECTS and plan hash value 2874684176.
We recreate the index and we check the old plan is being used again.
SQL> CREATE INDEX HELPDESK.OBJ_NAME ON HELPDESK.OBJECTS(OBJECT_NAME);
Index created.
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 917395572
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 298 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 298 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 2 | 158 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | OBJ_NAME | 2 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SEG_NAME | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | SEGMENTS | 1 | 70 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OBJECT_NAME" LIKE 'HELP%')
filter("A"."OBJECT_NAME" LIKE 'HELP%')
5 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
139 consistent gets
1 physical reads
0 redo size
855 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)
2 rows processed
Now, we have 2 execution plans for our query.
We create an AWR snapshot as SYSDBA and we will use a query from Document 1487302.1 to compare those execution plans.
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
PL/SQL procedure successfully completed.
WITH
P AS (
SELECT PLAN_HASH_VALUE
FROM GV$SQL_PLAN
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND OTHER_XML IS NOT NULL
UNION
SELECT PLAN_HASH_VALUE
FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND OTHER_XML IS NOT NULL ),
M AS (
SELECT PLAN_HASH_VALUE,
SUM(ELAPSED_TIME)/SUM(EXECUTIONS) AVG_ET_SECS
FROM GV$SQL
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND EXECUTIONS > 0
GROUP BY
PLAN_HASH_VALUE ),
A AS (
SELECT PLAN_HASH_VALUE,
SUM(ELAPSED_TIME_TOTAL)/SUM(EXECUTIONS_TOTAL) AVG_ET_SECS
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND EXECUTIONS_TOTAL > 0
GROUP BY
PLAN_HASH_VALUE )
SELECT P.PLAN_HASH_VALUE,
ROUND(NVL(M.AVG_ET_SECS, A.AVG_ET_SECS)/1E6, 3) AVG_ET_SECS
FROM P, M, A
WHERE P.PLAN_HASH_VALUE = M.PLAN_HASH_VALUE(+)
AND P.PLAN_HASH_VALUE = A.PLAN_HASH_VALUE(+)
ORDER BY
AVG_ET_SECS NULLS LAST;
| PLAN_HASH_VALUE | AVG_ET_SECS |
| 917395572 | 0,008 |
| 2874684176 | 0,019 |
Obviously, the execution plan with the 2 index range scans (917395572) is faster, but for the sake of our scenario we want to force plan 2874684176 to be used, when both indexes exist.
We will use Sqltxplain's
coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE] as SYSDBA.
SQL> @$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile.sql '8qu7mmcwnygtv' '2874684176';
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
917395572 .008
2874684176 .019
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "8qu7mmcwnygtv"
PLAN_HASH_VALUE: "2874684176"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
on TARGET system in order to create a custom SQL Profile
with plan 2874684176 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
Now, we need to execute as SYSDBA the generated coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql.
SQL>@$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql 11.4.4.4 2012/12/21 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 8qu7mmcwnygtv based on plan hash
SQL>REM value 2874684176.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_8qu7mmcwnygtv_2874684176');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[SELECT A.OBJECT_ID,
15 A.OBJECT_NAME,
16 B.SEGMENT_TYPE,
17 B.TA]');
18 wa(q'[BLESPACE_NAME
19 FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
20 WH]');
21 wa(q'[ERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP]');
22 wa(q'[%']');
23 DBMS_LOB.CLOSE(sql_txt);
24 h := SYS.SQLPROF_ATTR(
25 q'[BEGIN_OUTLINE_DATA]',
26 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
27 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
28 q'[DB_VERSION('11.2.0.3')]',
29 q'[ALL_ROWS]',
30 q'[OUTLINE_LEAF(@"SEL$1")]',
31 q'[INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("SEGMENTS"."SEGMENT_NAME"))]',
32 q'[FULL(@"SEL$1" "A"@"SEL$1")]',
33 q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]',
34 q'[USE_HASH(@"SEL$1" "A"@"SEL$1")]',
35 q'[END_OUTLINE_DATA]');
36 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
37 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
38 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
39 sql_text => sql_txt,
40 profile => h,
41 name => 'coe_8qu7mmcwnygtv_2874684176',
42 description => 'coe 8qu7mmcwnygtv 2874684176 '||:signature||' '||:signaturef||'',
43 category => 'DEFAULT',
44 validate => TRUE,
45 replace => TRUE,
46 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
47 DBMS_LOB.FREETEMPORARY(sql_txt);
48 END;
49 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
5858216496569750688
SIGNATUREF
---------------------
1896484766659468535
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_8qu7mmcwnygtv_2874684176 completed
Now, the plan 2874684176 will be used, every time our query is run.
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 436 | 64964 | 98 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 436 | 64964 | 98 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 417 | 29190 | 18 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 75 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | OBJECTS | 1176 | 92904 | 79 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')
Note
-----
- SQL profile "coe_8qu7mmcwnygtv_2874684176" used for this statement <-- This is the SQL profile created by coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
288 consistent gets
1 physical reads
0 redo size
855 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)
2 rows processed
In 11g databases, it's recommended to use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function, instead:
SQL> SET SERVEROUTPUT ON
VAR N NUMBER
BEGIN
:N:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'8qu7mmcwnygtv', PLAN_HASH_VALUE=>2874684176, FIXED =>'NO', ENABLED=>'YES');
DBMS_OUTPUT.PUT_LINE('Number of plans loaded: '||:N);
END;
/
Number of plans loaded: 1
PL/SQL procedure successfully completed.
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 436 | 64964 | 98 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 436 | 64964 | 98 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 417 | 29190 | 18 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 75 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | OBJECTS | 1176 | 92904 | 79 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')
Note
-----
- SQL plan baseline "SQL_PLAN_52m4hvq7pz550a442f8ad" used for this statement <-- This is the SQL plan baseline we have just created
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
288 consistent gets
1 physical reads
0 redo size
855 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)
2 rows processed
