Database Reference
In-Depth Information
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| T |
-----------------------------------
2 - filter("N"=42)
Note
-----
- SQL plan baseline SQL_PLAN_3u6sbgq7v4u8z3fdbb376 used for this statement
Then, based on the SQL plan name provided by the previous output, you find the identifier of the SQL plan
baseline, the SQL handle , through the dba_sql_plan_baselines view:
SQL> SELECT sql_handle
2 FROM dba_sql_plan_baselines
3 WHERE plan_name = 'SQL_PLAN_3u6sbgq7v4u8z3fdbb376';
SQL_HANDLE
--------------------
SQL_3d1b0b7d8fb2691f
Finally, you replace the execution plan used by the SQL plan baseline. To do so, you load the execution plan
associated with the SQL statement leading to the index scan and remove the one associated with the full table scan.
The former is referenced by the SQL identifier and the execution plan hash value, the latter by the SQL handle and
SQL plan name:
ret := dbms_spm. load_plans_from_cursor_cache (sql_handle => 'SQL_3d1b0b7d8fb2691f',
sql_id => 'dat4n4845zdxc',
plan_hash_value => '3694077449');
ret := dbms_spm. drop_sql_plan_baseline (sql_handle => 'SQL_3d1b0b7d8fb2691f',
plan_name => 'SQL_PLAN_3u6sbgq7v4u8z3fdbb376');
To check whether the replacement has correctly taken place, you test the new SQL plan baseline. Notice that even
if the SQL statement contains the full hint, the execution plan no longer uses a full table scan.
inappropriate hints occur frequently in practice as the reason for inefficient execution plans. Being able to over-
ride them with the technique you've seen in this section is extremely useful.
Note
SQL> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
 
 
Search WWH ::




Custom Search