In the first three parts of this article, we have seen how SQL plan baselines are created, used and evolved. In this final installment, we will show some user interfaces, describe the interaction of SPM with other features and answer some of your questions.
DBMS_SPM package
A new package, DBMS_SPM, allows you to manage plan histories. We have already seen in previous examples how you can use it to create and evolve SQL plan baselines. Other management functions include changing attributes (like enabled status and plan name) of plans or dropping plans. You need the ADMINISTER SQL MANAGEMENT OBJECT privilege to execute this package. Viewing the plan history Regardless of how a plan history is created, you can view details about the various plans in the view DBA_SQL_PLAN_BASELINES. At the end of Part 3 of this blog, we saw that the SQL statement had two accepted plans:SQL> select sql_text, sql_handle, plan_name, enabled, accepted 2 from dba_sql_plan_baselines; SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC ------------------------ ------------------------ ----------------------------- --- --- select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES ount_sold, t.calendar_ye ar from sales s, products p , times t where s.prod_id = p.prod _id and s.time_id = t.time _id and p.prod_id < :pid select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES ount_sold, t.calendar_ye ar from sales s, products p , times t where s.prod_id = p.prod _id and s.time_id = t.time _id and p.prod_id < :pidThe SQL handle is a unique identifier for each SQL statement that you can use when managing your plan history using the DBMS_SPM package. Creating an accepted plan by modifying the SQL text Some of you may be manually tuning SQL statements by adding hints or otherwise modifying the SQL text. If you enable automatic capture of SQL plans and then execute this statement, you will be creating a SQL plan baseline for this modified statement. What you most likely want, however, is to add this plan to the plan history of the original SQL statement. Here's how you can do this using the above SQL statement as an example. Let's modify the SQL statement, execute it and look at the plan:
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure successfully completed.
SQL> select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('b17wnz4y8bqv1', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id and
s.time_id = t.time_id and p.prod_id < :pid
Plan hash value: 2290436051
---------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | TIMES |
| 4 | PARTITION RANGE ALL | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 6 | BITMAP CONVERSION TO ROWIDS | |
| 7 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 8 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 9 | INDEX RANGE SCAN | PRODUCTS_PK |
---------------------------------------------------------------
We can now create a new accepted plan for the original SQL statement by associating the modified statement's plan to the original statement's sql handle (obtained from DBA_SQL_PLAN_BASELINES):SQL> var pls number SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( - > sql_id => 'b17wnz4y8bqv1', - > plan_hash_value => 2290436051, - > sql_handle => 'SYS_SQL_4bf04d85fcc170b0');If the original SQL statement does not already have a plan history (and thus no SQL handle), another version of load_plans_from_cursor_cache allows you to specify the original statement's text. To confirm that we now have three accepted plans for our SQL statement, let's check in DBA_SQL_PLAN_BASELINES:
SQL> select sql_text, sql_handle, plan_name, enabled, accepted 2 from dba_sql_plan_baselines; SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC ------------------------ ------------------------ ----------------------------- --- --- select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0888547d3 YES YES ount_sold, t.calendar_ye ar from sales s, products p , times t where s.prod_id = p.prod _id and s.time_id = t.time _id and p.prod_id < :pid select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES ount_sold, t.calendar_ye ar from sales s, products p , times t where s.prod_id = p.prod _id and s.time_id = t.time _id and p.prod_id < :pid select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES ount_sold, t.calendar_ye ar from sales s, products p , times t where s.prod_id = p.prod _id and s.time_id = t.time _id and p.prod_id < :pid
Displaying plans
When the optimizer uses an accepted plan for a SQL statement, you can see it in the plan table (for explain) or V$SQL_PLAN (for shared cursors). Let's explain the SQL statement above and display its plan:SQL> explain plan for
2 select p.prod_name, s.amount_sold, t.calendar_year
3 from sales s, products p, times t
4 where s.prod_id = p.prod_id
5 and s.time_id = t.time_id
6 and p.prod_id < :pid;
Explained.
SQL> select * from table(dbms_xplan.display('plan_table', null, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 5 | INDEX RANGE SCAN | PRODUCTS_PK |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 10 | INDEX UNIQUE SCAN | TIME_PK |
| 11 | TABLE ACCESS BY INDEX ROWID | TIMES |
----------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fcc170b0a62d0f4d" used for this statement
The note at the bottom tells you that the optimizer used an accepted plan. A plan history might have multiple plans. You can see one of the accepted plans if the optimizer selects it for execution. But what if you want to display some or all of the other plans? You can do this using the display_sql_plan_baseline function in the DBMS_XPLAN package. Using the above example, here's how you can display the plan for all plans in the plan history.SQL> select *
2 from table(dbms_xplan.display_sql_plan_baseline(
3 sql_handle => 'SYS_SQL_4bf04d85fcc170b0', format => 'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_4bf04d85fcc170b0
SQL text: select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id < :pid
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0888547d3
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2290436051
---------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | TIMES |
| 4 | PARTITION RANGE ALL | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 6 | BITMAP CONVERSION TO ROWIDS | |
| 7 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 8 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 9 | INDEX RANGE SCAN | PRODUCTS_PK |
---------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b08cbcb825
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2361178149
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | PARTITION RANGE ALL| |
| 4 | TABLE ACCESS FULL | SALES |
| 5 | TABLE ACCESS FULL | TIMES |
| 6 | TABLE ACCESS FULL | PRODUCTS |
------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0a62d0f4d
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 5 | INDEX RANGE SCAN | PRODUCTS_PK |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 10 | INDEX UNIQUE SCAN | TIME_PK |
| 11 | TABLE ACCESS BY INDEX ROWID | TIMES |
----------------------------------------------------------------
Parameters
Two parameters allow you to control SPM. The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will beautomatically accepted. Subsequent plans for these statements will not be accepted until they are evolved. The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.
SPM and SQL profiles
A SQL statement can have both a SQL profile and a SQL plan baseline. Such a case was described in Part 3 where we evolved a SQL plan baseline by accepting a SQL profile. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.
SPM and Stored Outlines
It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it. If you are using stored outlines, you can test SPM by creating SQL plan baselines and disabling the stored outlines. If you are satisfied with SPM, you can either drop the stored outlines or leave them disabled. If SPM doesn't work for you (and we would love to know why), you can re-enable the stored outlines. If you are using stored outlines, be aware of their limitations: You can only have one stored outline at a time for a given SQL statement. This may be fine in some cases, but a single plan is not necessarily the best when the statement is executed under varying conditions (e.g., bind values). The second limitation is related to the first. Stored outlines do not allow for evolution. That is, even if a better plan exists, the stored outline will continue to be used, potentially degrading your system's performance. To get the better plan, you have to manually drop the current stored outline and generate a new one. If an access path (e.g., an index) used in a stored outline is dropped or otherwise becomes unusable, the partial stored outline will continue to be used with the potential of a much worse plan.
One question that readers have is what we plan to do with the stored outlines feature. Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 12c Release 2, stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines. If you have existing stored outlines, consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET procedure of the DBMS_SPM package. When the migration is complete, you should disable or remove the stored outlines.
SPM and Adaptive Cursor Sharing
Adaptive cursor sharing (ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse. When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set. Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.
Enterprise Manager
You can view SQL plan baselines and configure and manage most SPM tasks through the Enterprise Manager. The screenshots below show two of these tasks.Setting init.ora parameters for SPM
Loading SQL plan baselines from cursor cache
No comments:
Post a Comment