Tuesday Aug 25, 2015

Tips on SQL Plan Management and Oracle Database In-Memory - Part 2

In Part 1 of this series of tips on SQL Plan Management (SPM) and Oracle Database In-Memory, I covered what would happen if we have a SQL plan baseline for a full table scan query when the table was populating the In-Memory column store. 

In this part I’m going to cover a scenario where a query has more than one SQL plan baseline: 

  • There is a query (called Q2, for short).
  • Q2 queries a table called MYSALES, which is not yet populating the In-Memory column store.
  • Q2 filters rows in MYSALES using a predicate on the SALE_TYPE column.
  • Data in SALE_TYPE is skewed, so there’s an index and a histogram on this column.
  • Because there is data skew, Q2 has two accepted SQL plan baselines; one with a full table scan and one with an index range scan.

You’ve probably come across this situation many times: the Oracle Optimizer must choose between a full table scan or an index range scan depending on predicate selectivity. The ability to change the execution plan based on the value of bind variables is called adaptive cursor sharing. If you’ve not come across that, then you’ll find it useful to check out the section on this topic in the Database SQL Tuning Guide.

What’s great about SPM is that it allows you to have multiple SQL plan baselines for individual queries, so you're not forced to pick one plan in preference to another. This capability is most relevant in environments where SQL statements use bind variables and there is a good deal of data skew. Queries like this are likely to have their plans affected by Oracle In-Memory Database because in-memory full table scans will have a lower cost than storage-resident table scans. Clearly, the In-Memory column store will affect the point of inflection where a full table scan will become more efficient than an index range scan. How is this going to work with SPM? 

Take a look at the following example. Q2 executes and matches 2 million rows because I picked the value of bind variable “:val” to do just that. The Optimizer chooses a full table scan: 

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  d3u63rk540w0r, child number 1
-------------------------------------
select /* SPM */ count(*),sum(val) from mysales where sale_type = :val

Plan hash value: 3292460164

------------------------------------------------------------------------------
  Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |  2475 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| MYSALES |  2000K|    32M|  2475   (1)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("SALE_TYPE"=:VAL)

Note
-----
  - SQL plan baseline SQL_PLAN_93ct9zmnvtbuhc69cec1f used for this statement

For the second execution, the value “:val” is set so that it would match only 20,001 rows. This time the Optimizer chooses an index range scan: 

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  d3u63rk540w0r, child number 2
-------------------------------------
select /* SPM */ count(*),sum(val) from mysales where sale_type = :val

Plan hash value: 1266559460

------------------------------------------------------------------------------------------------
  Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |       |       |   133 (100)|          |
|   1 |  SORT AGGREGATE                      |         |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MYSALES | 20001 |   332K|   133   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SI      | 20001 |       |    44   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):a
---------------------------------------------------
  3 - access("SALE_TYPE"=:VAL)

Note
-----
  - SQL plan baseline SQL_PLAN_93ct9zmnvtbuh5d8bf80c used for this statement

As you will have figured out, the Optimizer has calculated that the index is less efficient than a full table scan when Q2 matches a large number of rows (2 million in this case) so we have two viable SQL execution plans for this query. Before I ran the queries above, I accepted two SQL plan baselines for Q2. You can see in the “note” sections above that two different baselines are used (one ending in “80c” and one ending in “c1f”). They can be seen in the dba_sql_plan_baselines view: 

SELECT plan_name,sql_text,enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%';

PLAN_NAME                           SQL_TEXT                                ENA ACC
----------------------------------- ----------------------------------      --- ---
SQL_PLAN_93ct9zmnvtbuhc69cec1f      select /* SPM */ count(*),sum(val)      YES YES
                                    from mysales where sale_type = :val           
SQL_PLAN_93ct9zmnvtbuh5d8bf80c      select /* SPM */ count(*),sum(val)      YES YES
                                    from mysales where sale_type = :val

We’re good shape here. The Optimizer is adapting the query execution plan to take into account bind variable values and data skew. What’s more, SPM is working with us and not against us because it is not forcing Q2 to use a single SQL execution plan.

What happens if we populate MYSALES into the In-Memory column store? 

-- Mark MYSALES with the In-Memory attribute
ALTER TABLE mysales INMEMORY;

-- Access MYSALES to trigger population into In-Memory column store
SELECT count(*) FROM mysales;

If we execute Q2 to match 2 million rows, the Optimizer continues to choose a full table scan: 

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  d3u63rk540w0r, child number 1
-------------------------------------
select /* SPM */ count(*),sum(val) from mysales where sale_type = :val

Plan hash value: 3292460164

---------------------------------------------------------------------------------------
  Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |   115 (100)|          |
|   1 |  SORT AGGREGATE             |         |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| MYSALES |  2000K|    32M|   115  (20)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - inmemory("SALE_TYPE"=:VAL)
      filter("SALE_TYPE"=:VAL)

Note
-----
  - SQL plan baseline SQL_PLAN_93ct9zmnvtbuhc69cec1f used for this statement

The full table scan is now annotated with INMEMORY, so we know that some or all of the data for MYSALES is scanned via the In-Memory column store. The “note” section reports that the same baseline is being used as before (ending in “c1f”). This is good news, and it’s the scenario that was covered in Part 1 of this series.  

What if we executed the query to match 20,001 rows? You can probably guess what’s coming; the Optimizer judges that the In-Memory scan is more efficient than the index range scan: 

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  d3u63rk540w0r, child number 2
-------------------------------------
select /* SPM */ count(*),sum(val) from mysales where sale_type = :val

Plan hash value: 3292460164

---------------------------------------------------------------------------------------
  Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |   115 (100)|          |
|   1 |  SORT AGGREGATE             |         |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| MYSALES | 20001 |   332K|   115  (20)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - inmemory("SALE_TYPE"=:VAL)
      filter("SALE_TYPE"=:VAL)

Note
-----
  - SQL plan baseline SQL_PLAN_93ct9zmnvtbuhc69cec1f used for this statement

Since there is a SQL plan baseline that allows a full table scan to be used, Q2 can use this access method straight away and we get immediate benefit from scanning the In-Memory column store!

Hold on a minute! Wasn’t that just a little bit too convenient? I arranged it so that there was a handy full-table-scan SQL plan baseline ready and waiting for when I "flipped the switch" and started using the In-Memory column store. This example might seem a little contrived, but it is a real-world example and I chose it to illustrate how SPM works together with both Oracle In-Memory Database and adaptive cursor sharing (and if you want more, there's an earlier blog on how adaptive cursor sharing interacts with SPM).

If, instead, I had started out with a single baseline that specified an index range scan, then this is the plan that would have been used even after MYSALES populated the In-Memory column store (and we would not have had an INMEMORY FULL scan). That’s not a bad thing; it is exactly what plan stability means and it is how SPM is meant to work. In the example above I made use of a couple of SQL execution plans that were validated and accepted before I initiated the In-Memory column store. In the more general case, where the Optimizer identifies a brand new execution plan for use with the In-Memory column store, we might want to validate it before we allow the database to use it in our critical application. How can we do that? Happily, it's what SPM evolution was built for, and it goes all the way back to the initial scenario I mentioned in Part 1. I'll cover the details in Part 3 (coming soon). 

If you want to try out this example for yourself, the scripts are in GitHub.

Wednesday Aug 12, 2015

Tips on SQL Plan Management and Oracle Database In-Memory Part 1

If you follow Oracle’s In-Memory blog then you probably came across a post mentioning how you should use SQL Plan Management when you’re upgrading to Oracle Database In-Memory. Whether you have read that post or not, you might be wondering what will happen if you have some SQL plan baselines and you begin to populate the In-Memory column store with a bunch of tables as used by those baselines. That’s what this post is about. Well, in fact, I’m going to break the topic up into a few posts because (as ever!) there is a little bit of subtlety to cover. Luckily, this will make your life easier rather than more difficult because you can get immediate benefit from In-Memory even if you don’t evolve SQL plan baselines on day one.  

When I started to think about this post I thought that I would start with the first scenario that probably comes to mind if you’re familiar with SQL Plan Management (SPM): 
  • The Optimizer comes up with a new execution plan for a SQL statement because something has changed, and Oracle Database In-Memory would be a very good example of that! 
  • If there’s a SQL plan baseline for the statement, the database will use the baseline execution plan and capture the new plan.
  • Where appropriate, the new plan will be validated and accepted using SQL plan evolution. 

I will get to that, but first it’s better to start with a couple of more subtle points. With this information in our back pocket it will be easier to understand (and explain) the more traditional aspects of SQL plan evolution in the context of Oracle Database In-Memory. 

Here, I will cover the following example:
  • There is a table called MYSALES that’s not yet populated into the In-Memory column store. 
  • A query (called “Q1”) includes a full table scan of MYSALES. There is no index on the table that’s useful to Q1. 
  • Q1 has an active SQL plan baseline.
  • MYSALES is subsequently populated into the In-Memory column store.

Let’s take a look at Q1 and its SQL execution plan before populating MYSALES into the In-Memory column store (and I'll explain the significance of the highlighted text further down)...

SQL_ID  4ss4zbb813250, child number 0
-------------------------------------
SELECT /* SPM */ COUNT(*) FROM   mysales WHERE  val = 'X'

Plan hash value: 3292460164

------------------------------------------------------------------------------
  Id  | Operation          | Name    | Rows  | Bytes | Cost  %CPU | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |    69 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| MYSALES | 99991 |   195K|    69   (2)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("VAL"='X')

Note
-----
  - SQL plan baseline SQL_PLAN_7469nmnn7nsu3c69cec1f used for this statement

Q1 performs a full table scan of MYSALES. The "note" section makes it clear that a SQL plan baseline is used. This is what that looks like:

SELECT PLAN_TABLE_OUTPUT
FROM   V$SQL s, DBA_SQL_PLAN_BASELINES b,
      TABLE(
        DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
      ) t
WHERE  s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND    b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND    s.SQL_ID='4ss4zbb813250';

--------------------------------------------------------------------------------
SQL handle: SQL_7219349d287a6343
SQL text: SELECT /* SPM */ COUNT(*) FROM   mysales WHERE  val = 'X'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7469nmnn7nsu3c69cec1f         Plan id: 3332172831
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3292460164

--------------------------------------
  Id  | Operation          | Name    |
--------------------------------------
|   0 | SELECT STATEMENT   |         |
|   1 |  SORT AGGREGATE    |         |
|   2 |   TABLE ACCESS FULL| MYSALES |
--------------------------------------

What happens if MYSALES is now populated into the In-Memory column store? 

-- Mark MYSALES with the In-Memory attribute
ALTER TABLE mysales INMEMORY;

-- Access MYSALES to trigger population into In-Memory column store
SELECT count(*) FROM mysales;

Let’s rerun our query and examine the execution plan:

SQL_ID  4ss4zbb813250, child number 1
-------------------------------------
SELECT /* SPM */ COUNT(*) FROM   mysales WHERE  val = 'X'

Plan hash value: 3292460164

---------------------------------------------------------------------------------------
  Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |         |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| MYSALES |   100K|   195K|     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - inmemory("VAL"='X')
       filter("VAL"='X')

Note
-----
   - SQL plan baseline SQL_PLAN_7469nmnn7nsu3c69cec1f used for this statement

There is still a full table scan, but this time the query will read data from MYSALES via the In-Memory column store rather than the storage-resident table and, even better, the same SQL plan baseline is used. That was pretty easy! The Optimizer chose a full table scan in both cases, so the same SQL plan baseline was used both cases. The INMEMORY annotation for the full table scan is “for your information only”; it tells you that the query scanned some or all of the data for your table via the In-Memory column store but as far as the Optimizer is concerned it is “just” a full table scan, as the keyword INMEMORY does not affect the plan hash value, so it will match the existing the SQL plan baseline (above, you can see that the plan hash value is always "3292460164" ).

Why do I say the INMEMORY keyword indicates some or all of the data for your table is scanned via the In-Memory column store? Remember until all of the data belonging to MYSALES has been populated into the In-Memory column store, Oracle will automatically pick up the rest of the data from wherever it resides. That could be from memory (e.g. the buffer cache) or from flash or from disk.

It should be pretty obvious by now that if we decide to remove MYSALES from the In-Memory column store, the query will revert to scanning the storage-resident table and the plan will display “TABLE ACCESS FULL”. 

This example is very simple, but the principle applies to queries that have the same execution plan for In-Memory versus non-In-Memory. What happens if there are execution plan changes and, in particular, if indexes are involved? Start by looking at Part 2.

If you want to try out this example for yourself, the scripts are in GitHub.


Wednesday Jun 24, 2015

What you need to know about SQL Plan Management and Auto Capture

SQL Plan Management (SPM) is an Oracle database feature that allows you to establish a set of SQL execution plans that will be used even if the database is subject to changes that would otherwise cause execution plan changes to occur. For example, you might have an end-of-day batch run that operates in a business context where there are extreme peaks and troughs in daily volume, or perhaps you are upgrading a database and want to be sure that plans are carried over (at least initially). You do not have to fix execution plans in stone with SPM, you can use plan evolution to automate the process of finding improved plans, improving performance in a controlled way and at your own pace. If you’re not familiar with SPM, a very good place to start is to take a look at Maria Colgan’s four-part blog post on the subject. It gives you all the tools you need to get started.

If you are using SPM in Oracle Database 11gR2 or 12c, or if you are considering whether you should use it, then this blog post is for you. I decided to publish this post because I recently encountered a couple of environments that ran into, let’s say, “difficulties” with SPM when capturing SQL plan baselines automatically and continuously over a very long period of time (more than a year in fact). I’d like to give you a few pointers to avoid running into the same problems and why automatic SQL baseline capture was never intended to be used in that way.

[Read More]

Monday Feb 11, 2013

How do adaptive cursor sharing and SQL Plan Management interact?

We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact.  We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact, and show an example. 

The simplest way to reason about the interaction is to remember that they are responsible for two different tasks.  ACS controls whether or not a child cursor is shared on a particular execution.  For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values.  SPM controls which plans the optimizer may choose.  If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.

[Read More]

Wednesday Jan 09, 2013

How does SQL Plan Management match SQL statements to SQL plan baselines?

Happy New Year to all of our readers!

As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly popular as a mechanism to maintain plan stability and subsequently system performance. However, one of the key frustrations folks encounter when using SPM is that the SQL plan baseline they painstakingly created is not used.

[Read More]

Tuesday May 08, 2012

What is the difference between SQL Profiles and SQL Plan Baselines?

Since Oracle Database 11g was released I have gotten a lot of questions about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can't. So I thought it would be a good idea to write a post explaining the differences between them and how they interact. But first let's briefly recap each feature.

The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan.  A SQL profile contains auxiliary information that mitigates this problem.  When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan.

A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved. We described this behavior in more detail in a previous post.

[Read More]

Monday Oct 24, 2011

Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?

Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g.


I believe the confusion arises from the wording in the Oracle Database Licensing documentation  that says the Tuning Pack includes "Automatic Plan Evolution of SQL Plan Management". What this is actually referring to is the interaction between the SQL Tune Advisor's nightly tuning task and SPM. This interaction does require you to have a license to use the Tuning pack.

In Oracle Database 11g, the SQL Tuning Advisor, runs automatically during the maintenance window. This automatic SQL tuning task targets high-load SQL statements. These statements are identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots and not from SPM. If the SQL Tuning Advisor finds a better execution plan for a SQL statement it will recommend a SQL profile. Some of these high-load SQL statements may already have SQL plan baselines created for them. If a SQL profile recommendation is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted plan to the existing SQL plan baseline. 


The Tuning pack or RAT pack are also required if you want to load plans from a SQL Tuning Set (STS) into SPM. Again this is because an STS can only be created if you have licensed one of these packs. It has nothing to do with SPM.

Maria Colgan+

Tuesday Jul 12, 2011

How do I migrate stored outlines to SQL Plan Management?

Stored outlines were deprecated in Oracle Database 11g and even though we have done a number of posts about SQL Plan Management (SPM) we have never discussed the reasons why you should migrate stored outlines to SQL plan baselines and the steps necessary to do the migration. It is strongly recommended that you migrate existing stored outlines to SPM because:

[Read More]

Tuesday Feb 15, 2011

How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?

I have gotten many variations on this question recently as folks begin to upgrade to Oracle Database 11g and there have been several posts on this blog and on others describing how to use SQL Plan Management (SPM) so that a non-hinted SQL statement can use a plan generated with hints. But what if the hint is supplied in the third party application and is causing performance regressions on your system?

[Read More]

Tuesday Nov 17, 2009

What should I do with old hints in my workload?

We promised in an earlier post to cover some of the questions from the Optimizer round table discussion at Oracle Open World. Here's our first in a series of posts that will address these questions.

Q: When moving from 10g to 11g, should hints in existing SQL be removed?

A: I was glad to see this question at the round table, since I think this is something people often overlook when adding hints to an application. Over time, hints can become stale. You may have added a hint to a query because of a weakness in an old version of the optimizer, but the hint might no longer be necessary to get the best plan. Even worse, the hint may force a plan which is suboptimal, for a couple of reasons:
  • Depending on how your data change over time, the plan choice that the hint enforces might no longer be a good choice. This problem can occur even without a database upgrade. For example, if the distribution of values in a column change over time, an old access path hint may no longer be appropriate.
  • The hint might prevent new optimizations from taking place. So after an upgrade, you will be stuck using the old hint-enforced plan, when the optimizer could have chosen something better. For example, you may have hinted a plan for a query with bind variables, to avoid a bad plan choice due to bind peeking in the pre-11g optimizer. In 11g, you will not get the benefits of adaptive cursor sharing because of the hints in the query.
These are just some of the reasons why it is good to re-evaluate the hints in your application from time to time. You can test this out using the _optimizer_ignore_hints parameter. Setting this parameter to TRUE will cause the optimizer to ignore the hints embedded in queries. You can set this on the session level, run your workload, and compare the performance to your baseline performance (with the hints). I recommend using SQL Performance Analyzer (SPA) to do this. Read the SPA white paper for more information on how to do that.

If you find that some of your queries really do still require hints to get an optimal plan, you should consider creating a SQL plan baseline, and allowing SPM to manage the plan for you. If you do this, then you will get the hinted plan for now, but if a better plan comes along later (for one of the reasons mentioned earlier), you will eventually get the benefits of the new plan, using the SPM evolve process. There are two ways that you can create a SQL plan baseline based on your hints that will be applied to the query without hints. The first way requires that a SQL plan baseline already exist for the unhinted query. That's described in an earlier post. Here are the steps to use if you don't already have a SQL plan baseline for the unhinted query.

1. Run the query with hints, and confirm that the plan is what you want:
var pid number
exec :pid := 100;
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; 
PROD_NAME                                          AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...
select sql_id from v$sql where sql_text like 'select /*+ leading(t) */ p.prod_name%';
SQL_ID
-------------
2qtu6hy4rf1j9
select * from table(dbms_xplan.display_cursor(sql_id=>'2qtu6hy4rf1j9', 
                                              format=>'basic note'));
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    |
---------------------------------------------------------------

2. Load SQL plan baseline from cursor cache, and associate it with the unhinted query:

var sqltext clob;

begin
:sqltext := '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';
end;
/

exec :pls := dbms_spm.load_plans_from_cursor_cache( -
              sql_id => '2qtu6hy4rf1j9', -
              plan_hash_value => 2290436051, -
              sql_text => :sqltext);

3. Run the query without hints, and check that the SQL plan baseline was used.

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;

PROD_NAME                                          AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...

select * from table(dbms_xplan.display_cursor(sql_id=>'a1ax3265pq8x7',
                                              format=>'basic note'));

EXPLAINED SQL STATEMENT:
------------------------
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 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    |
---------------------------------------------------------------
Note ----- - SQL plan baseline SQL_PLAN_4rw2dhryc2w5h888547d3 used for this statement

Monday Feb 02, 2009

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features

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.[Read More]

Monday Jan 26, 2009

SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines

In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline:[Read More]

Tuesday Jan 20, 2009

SQL Plan Management (Part 2 of 4) SPM Aware Optimizer

In Part 1, we saw how you can create SQL plan baselines. After you create a SQL plan baseline for a statement, subsequent executions on that statement will use the SQL plan baseline. From all the plans in the SQL plan baseline, the optimizer will select the one with the best cost in the current environment (including bind values, current statistics, parameters, etc.). The optimizer will also generate the best-cost plan that it would otherwise have used without a SQL plan baseline. However, this best-cost plan will not be used but instead added to the statement's plan history for later verification. In other words, the optimizer will use a known plan from the SQL plan baseline instead of a new and hitherto unknown plan. This guarantees no performance regression.[Read More]

Thursday Jan 08, 2009

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines

Do you ever experience performance regressions because an execution plan has changed for the worse? If you have, then we have an elegant solution for you in 11g called SQL Plan Management (SPM). The next four posts on our blog will cover SPM in detail. Let's begin by reviewing the primary causes for plan changes.[Read More]
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
13
14
15
16
17
18
19
20
21
22
23
24
26
27
28
29
30
31
     
Today