Database Reference
In-Depth Information
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 |
TABLE ACCESS FULL
| T |
-----------------------------------
2 - filter("N"=42)
You notice that the column on which the restriction is applied (
n
) is indexed. You then wonder what the
performance is when the index is used. So, as shown in the following example, you execute the SQL statement by
specifying a hint to ensure that the index is used:
SQL> SELECT /*+
index(t)
*/ count(pad) FROM t WHERE n = 42;
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
SQL_ID dat4n4845zdxc, child number 0
-------------------------------------
Plan hash value: 3694077449
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
|* 3 |
INDEX RANGE SCAN
| I |
---------------------------------------------
3 - access("N"=42)
If the second execution plan is more efficient than the first one, your objective is to let the application use it. If
you can't change the application in order to remove or modify the hint, you can take advantage of a SQL plan baseline
to solve this problem. To do that, you could create a SQL plan baseline either automatically or manually, as described
earlier. In this case, you decide to use the
optimizer_capture_sql_plan_baselines
initialization parameter:
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
SQL> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
Once the SQL plan baseline is created, you check that it's really used. Notice how the
dbms_xplan
package clearly
shows that a SQL plan baseline, identified through a
SQL plan name
, was used to generate the execution plan:
SQL> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;
