Fixed execution plan using Oracle SQL profile

Experiment with SQL profile fixed execution plan

Oracle has outlines before 10 Gb, and SQL profile after 10 Gb is one of the new features.

Outlines is insufficient for SQL statements that are not bound to variables.

The following is the experiment process.

1. -- 1. preparation stage
2. SQL> select * from v $ version;
4. BANNER
5. ----------------------------------------------------------------
6. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
7. PL/SQL Release 10.2.0.1.0-Production
8. CORE 10.2.0.1.0 Production
9. TNS for 32-bit Windows: Version 10.2.0.1.0-Production
10. NLSRTL Version 10.2.0.1.0-Production
12. SQL> createtable test_raugher asselect * from dba_objects;
14. The table has been created.
16. SQL> createindex ind_objectid on test_raugher (object_id );
18. The index has been created.
20. SQL> select object_id from test_raugher where rownum <2;
22. OBJECT_ID
23. ----------
24. 20
26. SQL> exec dbms_stats.gather_table_stats (user, 'test _ RAUGHER ', cascade => true );
28. The PL/SQL process is successfully completed.
30. -- Original SQL Execution Plan
31. SQL> set autot trace explain
32. SQL> select * from test_raugher where object_id = 20;
34. Execution Plan
35. ----------------------------------------------------------
36. Plan hash value: 800879874
38. Bytes --------------------------------------------------------------------------------------------
39. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
40. Bytes --------------------------------------------------------------------------------------------
41. | 0 | select statement | 1 | 95 | 2 (0) | 00:00:01 |
42. | 1 | table access byindex rowid | TEST_RAUGHER | 1 | 95 | 2 (0) | 00:00:01 |
43. | * 2 | index range scan | IND_OBJECTID | 1 | 1 (0) | 00:00:01 |
44. Bytes --------------------------------------------------------------------------------------------
46. Predicate Information (identified by operation id ):
47. ---------------------------------------------------
49. 2-access ("OBJECT_ID" = 20)
51. SQL>
52. -- New SQL Execution Plan
53. SQL> select/* + full (test_raugher) */* from test_raugher where object_id = 20;
55. Execution Plan
56. ----------------------------------------------------------
57. Plan hash value: 3725671026
59. ----------------------------------------------------------------------------------
60. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
61. ----------------------------------------------------------------------------------
62. | 0 | select statement | 1 | 95 | 166 (2) | 00:00:02 |
63. | * 1 | table access full | TEST_RAUGHER | 1 | 95 | 166 (2) | 00:00:02 |
64. ----------------------------------------------------------------------------------
66. Predicate Information (identified by operation id ):
67. ---------------------------------------------------
69. 1-filter ("OBJECT_ID" = 20)
71. -- 2. Obtain the SQL _id of the new SQL statement.
72. SQL> col SQL _id for a20
73. SQL> col SQL _text for a100
74. SQL> select SQL _id, SQL _text from v $ SQL where SQL _text like '% full (test_raugher) % ';
76. SQL _ID SQL _TEXT
77. -------------------- Success ------------------------------------------------------------------------------------------------------------------------
78. 5nkhk1_705z3 select SQL _id, SQL _text from v $ SQL where SQL _text like '% full (test_raugher) %'
79. G23hbdmcsdahc select/* + full (test_raugher) */* from test_raugher where object_id = 20
80. Dqp79vx5pmw0k explain plan set STATEMENT_ID = 'plus4294967295 'FORselect/* + full (test_raugher) */* from test_raug
81. Her where object_id = 20
83. -- 3. Obtain the outline of the new SQL statement
84. SQL> set pagesize 1000
85. SQL> select * fromtable (dbms_xplan.display_cursor ('g23hbdmcsdahc ', null, 'outline '));
87. PLAN_TABLE_OUTPUT
88. Bytes -----------------------------------------------------------------------------------------------
89. Bytes -----------------------------------------------------------------------------------------------
90. SQL _ID g23hbdmcsdahc, child number 0
91. -------------------------------------
92. Select/* + full (test_raugher) */* from test_raugher where object_id = 20
94. Plan hash value: 3725671026
96. ----------------------------------------------------------------------------------
97. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
98. ----------------------------------------------------------------------------------
99. | 0 | select statement | 166 (100) |
100. | * 1 | table access full | TEST_RAUGHER | 1 | 95 | 166 (2) | 00:00:02 |
101. ----------------------------------------------------------------------------------
103. Outline Data
104. -------------
106. /* +
107. BEGIN_OUTLINE_DATA
108. IGNORE_OPTIM_EMBEDDED_HINTS
109. OPTIMIZER_FEATURES_ENABLE ('10. 2.0.1 ')
110. ALL_ROWS
111. OUTLINE_LEAF (@ "SEL $1 ")
112. FULL (@ "SEL $1" "TEST_RAUGHER" @ "SEL $1 ")
113. END_OUTLINE_DATA
114. */
116. Predicate Information (identified by operation id ):
117. ---------------------------------------------------
119. 1-filter ("OBJECT_ID" = 20)
122. 31 rows have been selected.
124. -- 4. Create an SQL profile (SQLPROFILE_001)
125. SQL> declare
126. 2 v_hints sys. sqlprof_attr;
127. 3 begin
128. 4 v_hints: = sys. sqlprof_attr (
129. 5 'begin_outline_data ',
130. 6 'ignore _ OPTIM_EMBEDDED_HINTS ',
131. 7 'optimizer _ FEATURES_ENABLE (''10. 2.0.1 '')',
132. 8 'all _ ROWS ',
133. 9 'outline _ LEAF (@ "SEL $1 ")',
134. 10 'full (@ "SEL $1" "TEST_RAUGHER" @ "SEL $1 ")',
135. 11 'end _ OUTLINE_DATA ');
136. 12 dbms_sqltune.import_ SQL _profile (
137. 13 'select * from test_raugher where object_id = 20 ',
138. 14 v_hints, 'sqlprofile _ 001 ',
139. 15 force_match => true, replace => false );
140. 16 end;
141. 17/
143. The PL/SQL process is successfully completed.
145. -- 5. Check whether SQL profile is used
146. SQL> set autot trace explain
147. SQL> select * from test_raugher where object_id = 20;
149. Execution Plan
150. ----------------------------------------------------------
151. Plan hash value: 3725671026
153. ----------------------------------------------------------------------------------
154. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
155. ----------------------------------------------------------------------------------
156. | 0 | select statement | 1 | 95 | 166 (2) | 00:00:02 |
157. | * 1 | table access full | TEST_RAUGHER | 1 | 95 | 166 (2) | 00:00:02 |
158. ----------------------------------------------------------------------------------
160. Predicate Information (identified by operation id ):
161. ---------------------------------------------------
163. 1-filter ("OBJECT_ID" = 20)
165. Note
166. -----
167. -SQL profile "SQLPROFILE_001" used for this statement
169. SQL> select * from test_raugher where object_id = 200;
171. Execution Plan
172. ----------------------------------------------------------
173. Plan hash value: 3725671026
175. ----------------------------------------------------------------------------------
176. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
177. ----------------------------------------------------------------------------------
178. | 0 | select statement | 1 | 95 | 166 (2) | 00:00:02 |
179. | * 1 | table access full | TEST_RAUGHER | 1 | 95 | 166 (2) | 00:00:02 |
180. ----------------------------------------------------------------------------------
182. Predicate Information (identified by operation id ):
183. ---------------------------------------------------
185. 1-filter ("OBJECT_ID" = 200)
187. Note
188. -----
189. -SQL profile "SQLPROFILE_001" used for this statement

1 2 Next Page