In part 1 of this series we saw two basic ways of creating, or acquiring, execution plans. The first method gave us the optimizer’s prediction of what it would (possibly) do if you ran the query:

explain plan for {sql statement}
select * from table(dbms_xplan.display);

The second method told us what the optimizer had done after we ran the query.

set serveroutput off
{sql statement}
select * from table(dbms_xplan.display_cursor);

In fact, in part 1 I deliberately produced an example to show that the two methods could produce different plans – especially when bind variables were involved.

In part 2 we will be reviewing a couple more ways of seeing actual execution plans, but first we’re going to look at some of the options for the calls we’ve looked at so far that will allow us to be a little more confident that when we’re looking at an execution plan it’s likely to be one that would reappear in the end-user’s environment.

Format Options

Broadly speaking, to be confident that we are doing a good job of emulating the end-users’ environment we need a copy of their data, with statistics that match the production statistics, parameter settings that match their settings, and a query with matching inputs (i.e. values used in predicates). The data and statistics are pretty much a given – so what we really need is a way of finding out the special details of the users’ environment and inputs; fortunately there are ways we can ask the database to help us if we can get to it at the right moment.

There is a warning that goes with synchronizing data, stats and predicates – if, for example, you’re using a production backup from a couple of weeks ago you need to remember to use the same predicate values that the users were using two weeks ago; this can be particularly difficult to emulate well if you have lots of queries referencing SYSDATE.

As I mentioned in part 1, the call to dbms_xplan,display_cursor() can take three parameters, which are the sql_id, child number, and a formatting option. Two of the most helpful formatting parameters, if you’re trying to solve a problem that appeared in the very recent past are “peeked_binds” and “outline”. The former lists (but only if we are a little lucky) the actual values used to optimize the query, and the latter supplies the list of hints that would be stored if we were to create a stored outline or SQL Plan Baseline for the query and among those hints we may find a few that tell us a little bit about the optimizer environment at the time.

Here’s a small example demonstrating the use and output from a query against a table that is a copy of the dba_extents view:

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 10485760;

alter session set optimizer_mode = first_rows_10;
alter session set "_hash_join_enabled" = false;

set serveroutput off

variable m_owner varchar2(32)
variable m_object varchar2(32)
execute :m_owner := 'TEST_USER'
execute :m_object := 'T1'

select /*+ tracking */  * from t1 
where owner = :m_owner 
and segment_name = :m_object 
order by extent_id;

select * from table(
	dbms_xplan.display_cursor(null,null,'outline peeked_binds')
);

I’ve modified a few optimizer-related parameters, set serveroutput off so that dbms_xplan,display_cursor() doesn’t tell me about a call to dbms_output.get_lines(), and declared a couple of variables so I can run a query demonstrating bind variable usage. Then I’ve used the call to display_cursor() that will report my most recent statement (null,null) and add any available bind values to the report along with the Outline/SQL Plan management information. This is the result (with a tiny bit of cosmetic editing):

SQL_ID  0wwbn4bhvrrxj, child number 0
-------------------------------------
select /*+ tracking */  * from t1 where owner = :m_owner and
segment_name = :m_object order by extent_id

Plan hash value: 3684778271

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT ORDER BY               |       |     1 |    65 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    65 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I2 |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."SEGMENT_NAME"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------
   1 - :M_OWNER (VARCHAR2(30), CSID=178): 'TEST_USER'
   2 - :M_OBJECT (VARCHAR2(30), CSID=178): 'T1'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=:M_OWNER)
   3 - access("SEGMENT_NAME"=:M_OBJECT)

In this case we’ve been lucky with the bind variables – the optimizer has recorded all of them with the execution plan information, including a nice little detail in the CSID (character set id) which sometimes causes surprises when you start doing things with NLS character sets.

Looking at the “Outline Data” then, we can see that one of our session changes has been recorded through the hint “first_rows(10)” – unfortunately the rest of the changes haven’t been captured; the format option has given us a reminder, though, to do a little extra checking if we’re going to start experimenting with this query to see if we can get a better plan. Here’s what we can do to fill in the gaps – and this time I’m going to start with the assumption that someone else has run the query and we have to track it down by searching for a little recognizable text before we do anything else (which is why I put that “/*+ tracking */ “ bit in the SQL – it’s not really a hint).

column sql_id new_value m_sql_id
column child_number new_value m_child_no

select  sql_id, child_number, sql_text 
from    v$sql 
where   sql_text like '%tracking%' 
and     sql_text not like '%v$sql%'
;

select * from table(
	dbms_xplan.display_cursor('&m_sql_id',&m_child_no,'outline peeked_binds')
);

select 
	name, value 
from 
	v$sql_optimizer_env 
where 
	sql_id       = '&m_sql_id' 
and	child_number = &m_child_no 
and	isdefault    = 'NO'
;

This is a bit of code that can run from SQL*Plus, and I’ve set up a couple of column definitions to capture the last values returned by a query – the SQL_ID and CHILD_NUMBER of a statement I’m searching for. I’ve cheated a little bit here because I’ve assumed I’m only going to find one statement with one child cursor. As you can see I’ve then used “ampersand substitution” to put the captured values into a call to display_cursor() and a query against v$sql_optimizer_env. I won’t show you the output from the former (it would just repeat the plan and details from the previous call) but here’s the output from the final query.

NAME                                     VALUE
---------------------------------------- -------------------------
hash_area_size                           20971520
sort_area_size                           10485760
optimizer_mode                           first_rows_10
_hash_join_enabled                       false
workarea_size_policy                     manual
_smm_auto_cost_enabled                   false

The view v$sql_optimizer_env holds the values of the optimizer environment parameters that were in force when each child cursor was optimized. I’ve just selected the ones that were not at their default and, as you can see, Oracle has reported 6 parameters – the four that we set, plus two more: hash_area_size, which is set to twice the sort_area_size if not set explicitly, and _smm_auto_cost_enabled which changed when we disabled the automatic workarea_size_policy. In 11.2.0.4 there are 330 optimizer parameters (increasing to 415 in 12.1.0.1, which is just one reason why it’s hard to work out from second-hand information what execution path a given query will take) of which only 50 are usually visible in the view; most of the rest are “underscore” (hidden) parameters, which only become visible when they are changed from their default.

I’ll mention one other formatting option very briefly, that’s the ‘advanced’ option which, strangely, seems to give you more information than the ‘all’ option. If you want to see just about everything that’s available to be dumped from memory after running a query, the display_cursor({sql_id},{child_number},’all’) is the call you want. (If there are multiple child cursors you can get them all dumped at once by using NULL for the child_number parameter. I’ll be coming back to formatting options as we look into trouble-shooting execution plans, but for the present I’m going to leave it at that and move on to a couple of other options for getting plans out of a production system “after the event”. For a little more information on the available options, you can look in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

AWR and Statspack.

If a query was interesting enough (for which read “sufficiently resource intensive”) to appear in a statspack or AWR report then its execution plan, with some stats, are in the repository. For the AWR there’s a graphic interface from OEM (or Grid Control, or Cloud Control) – but there’s also a SQL interface for both statspack and the AWR in the $ORACLE_HOME/rdbms/admin directory. In the case of statspack (the free option) the script is sprepsql.sql, for the AWR (the licensed option, available only with Enterprise Edition) it’s awrsqrpt.sql.

If you’re familiar with running the AWR or statspack report from the command line, the SQL equivalent is virtually identical except you will be asked for an SQL_ID (AWR) or “old hash value” (statspack). The report will give you the full text of the statement, some execution statistics, all the different execution plans that have appeared in the interval for that query, and a timetable of when different plans entered and left the library cache.

The plans don’t report bind values, outline information, or even predicates, just the basic shape of the execution plan and some performance statistics. AWR is better in this respect than Statspack as it aggregates the stats for each different excution plan separately, so it’s easier to see if one plan is more efficient than another.

Another advantage the AWR has over Statspack for historic execution plans is that there is a call in dbms_xplan to report from the AWR: dbms_xplan.display_awr(). This can take 4 parameters, and SQL_ID, plan_hash_value, database id, and format option; in its simplest it will report all the execution plans captured in the AWR for a given SQL_ID, but you can be selective about which plan you want to report and, as with the call to dbms_xplan.display_cursor() you can report the outline and bind values used for optimisation, e.g.:

select * from table(
	dbms_xplan.display_awr('7rfj3amj9g53z', null, null,'outline peeked_binds')
);

There are many more variations in how dbms_xplan can find and manipulate the content of execution plans, and the possibilities keep expanding as newer versions of Oracle appear – recent versions even allow for reporting differences between plans – but one of the most useful little features actually relates to how you can populate the AWR rather than how you report from it. In 11g you can mark an SQL_ID as “interesting”, in which case the AWR snapshot process will capture any stats that are available for the statement when the snapshot is taken. The statement may not appear in the AWR report unless it features as one of the “top N” statements, but you can run the awrsqrpt.sql script or use dbms_xplan.display_awr() to report the plan.

If you want to tag a statement the relevant call is in the package dbms_workload_repository:

execute dbms_workload_repository.add_colored_sql({sql_id})
--
--	report any statement currently tagged
--
select * from wrm$_colored_sql;
--
--	unmark a statement you no longer wish to track
--
execute dbms_workload_repository.remove_colored_sql({sql_id})

The benefit of a procedure like this – even for statements which are very efficient – is that sometimes an application that has been behaving well will suddenly perform badly. If you have “colored” all the frequently executed SQL then you may be able to find very easily what has gone wrong and when the plans changed.

Conclusion

Although we haven’t exhausted all the possible ways we can generate or retrieve execution plans, we’ve reviewed enough mechanism to ensure that we will be able to obtain a sufficiently accurate picture of the environment that an execution plan is coming from so that when we examine the plan we know that it’s (almost certainly) the plan we need to examine. We’ve also seen a couple of ways of retrieving plans for which there is a historical record, even when the plan is no longer in memory; and a way (from 11g onwards) for ensuring that interesting queries will always be targeted by the AWR snapshot.

In the next article we’ll look at the basic principles of interpreting execution plans – starting with simple select statements with a view to identifying the join order, access methods, and join methods used.

 

Tags: , ,