Infrastructure at your Service

Franck Pachot

Franck Pachot: AWR does not store explain plan predicates

What is the most important part in an execution plan? It’s probably the predicate section. It helps to see implicit conversions. It helps to understand why an index is used or not. Or to see additional predicates coming from constraints. When you get an execution plan from shared pool, the dbms_xplan.display_cursor() shows the predicates. But when you retrieve a plan from the past, dbms_xplan.display_awr() does not show them.

Statspack

With Statspack, you can store execution plans when you take level 7 snapshot (which I often set as default). But you don’t have the predicate section, for the simple reason that they are not collected.

Here is what you can find in spcpkg.sql, look at the lines commented out and replaced by 0


insert into stats$sql_plan
...
, access_predicates
, filter_predicates
...
, 0 -- should be max(sp.access_predicates) (2254299)
, 0 -- should be max(sp.filter_predicates)

AWR

AWR is not better. Finding the code is a bit more difficult. It’s optimized, run from the binaries. Let’s find it:
$ strings $ORACLE_HOME/bin/oracle | grep -i "v[$]sql_plan"
 
SELECT /*+ leading(S) use_nl(P) PARAM('_cursor_plan_unparse_enabled','FALSE') */ remarks, operation, options, object_node, object_owner, object_name, object_alias, object_type, optimizer, id, parent_id, depth, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates,projection, time, qblock_name FROM v$sql_plan_statistics_all P, v$sqlarea_plan_hash S WHERE P.sql_id = :1 AND P.plan_hash_value = :2 AND P.con_dbid = :3 AND S.sql_id = :4 AND S.plan_hash_value = :5 AND S.con_dbid = :6 AND P.child_address = s.last_active_child_address

The parameter description for _cursor_plan_unparse_enabled is ‘enables/disables using unparse to build projection/predicates’ which is true by default but is set to false for this query. This, access_predicates ant filter_predicates are null as in Statspack.

Why?

It you tried to use those access_predicate and filter_predicates in the days of 9i you probably remember that it finished in ORA-7445 most of the times. Those columns are a bit special. The predicates are not stored as-is. They are ‘unparsed’ from the execution plan code. And because of a few bugs, Oracle has probably chosen to avoid automatic collection on them.

I think there are still some bugs still until 11.1 But I query those columns very frequently (directly or through dbms_xplan.display_cursor) and I don’t think I’ve seen any problem in current versions. I hope that one day that limitation will be released.

Workaround

When I’ve a plan coming from AWR, the first thing I do is to try to find it in the shared pool with dbms_xplan.display_cursor – with same sql_id and same plan_hash_value. Then I’ll have the predicate section.

If it is not present anymore, then I’ll try to reproduce it with an EXPLAIN PLAN. In order to get the same plan, I retrieve the outlines (‘+OUTLINE’ format) and the bind variable values may help as well (‘+PEEKED_BINDS’ format) and then I reproduce it and check that I’ve the same plan_hash_value.

Let’s see on an example

I have the following plan from AWR
SQL> select * from table(dbms_xplan.display_awr('7ws837zynp1zv',3722429161,format=>'basic +predicate'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7ws837zynp1zv
--------------------
SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,
IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND
ROWNUM < :B1
 
Plan hash value: 3722429161
 
--------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CARD_DETAILS |
| 3 | INDEX RANGE SCAN | CARDDETAILS_CUST_IX |
--------------------------------------------------------------------
 
17 rows selected.

There is no predicates here.

Then I try to get the plan with explain plan:
SQL> alter session set current_schema=SOE;
 
Session altered.
 
SQL> explain plan for
2 SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,
3 IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND
4 ROWNUM select * from table(dbms_xplan.display(format=>'basic +predicate'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2597291669
 
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
|* 2 | TABLE ACCESS FULL| CARD_DETAILS |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter(ROWNUM < TO_NUMBER(:B1))
2 - filter("CUSTOMER_ID"=TO_NUMBER(:B2))
 
15 rows selected.

The problem is that I’ve not the same plan. I want the predicates for the index access (plan hash value 3722429161).

So I get the maximum information from the AWR plan, with +OUTLINE and +PEEKED_BINDS:
SQL> select * from table(dbms_xplan.display_awr('7ws837zynp1zv',3722429161,format=>'basic +outline +peeked_binds'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7ws837zynp1zv
--------------------
SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,
IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND
ROWNUM < :B1
 
Plan hash value: 3722429161
 
--------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CARD_DETAILS |
| 3 | INDEX RANGE SCAN | CARDDETAILS_CUST_IX |
--------------------------------------------------------------------
 
Outline Data
-------------
 
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "CARD_DETAILS"@"SEL$1"
("CARD_DETAILS"."CUSTOMER_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "CARD_DETAILS"@"SEL$1")
END_OUTLINE_DATA
*/
 
Peeked Binds (identified by position):
--------------------------------------
 
1 - :B2 (NUMBER): 315821
2 - :B1 (NUMBER): 15
 
39 rows selected.

And I can now do the explain plan with the hints coming from the outlines (I can also replace the variables with the binds if I want to, as they are those that were peeked to optimize the statement):
SQL> explain plan for
2 SELECT
3 /*+
4 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
5 DB_VERSION('12.1.0.2')
6 ALL_ROWS
7 OUTLINE_LEAF(@"SEL$1")
8 INDEX_RS_ASC(@"SEL$1" "CARD_DETAILS"@"SEL$1"
9 ("CARD_DETAILS"."CUSTOMER_ID"))
10 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "CARD_DETAILS"@"SEL$1")
11 */
12 CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,
13 IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND
14 ROWNUM select * from table(dbms_xplan.display(format=>'basic +predicate'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3722429161
 
--------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CARD_DETAILS |
|* 3 | INDEX RANGE SCAN | CARDDETAILS_CUST_IX |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter(ROWNUM < TO_NUMBER(:B1))
3 - access("CUSTOMER_ID"=TO_NUMBER(:B2))
 
16 rows selected.

Bingo, I’ve now the predicates.

Time for a Wish

I wish that one day Oracle will release that limitation so that we can get predicate information from AWR (when in EE + Diagnostic Pack) and Statspack (SE and EE without option).

I’ve posted the idea on OTN. Please vote for the idea here

 

One Comment

Leave a Reply

Franck Pachot
Franck Pachot

Technology Leader