.
\nWhat 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.<\/p>\n
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.<\/p>\n
Here is what you can find in spcpkg.sql, look at the lines commented out and replaced by 0<\/p>\n
\n insert into stats$sql_plan\n...\n , access_predicates\n , filter_predicates\n...\n , 0 -- should be max(sp.access_predicates) (2254299)\n , 0 -- should be max(sp.filter_predicates)\n<\/code><\/pre>\nAWR<\/h3>\n
AWR is not better. Finding the code is a bit more difficult. It’s optimized, run from the binaries. Let’s find it:<\/p>\n
$ strings $ORACLE_HOME\/bin\/oracle | grep -i \"v[$]sql_plan\"\n \nSELECT \/*+ 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<\/code><\/pre>\nThe 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.<\/p>\n
Why?<\/h3>\n
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.<\/p>\n
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.<\/p>\n
Workaround<\/h3>\n
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.<\/p>\n
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.<\/p>\n
Let’s see on an example<\/p>\n
I have the following plan from AWR<\/p>\n
SQL> select * from table(dbms_xplan.display_awr('7ws837zynp1zv',3722429161,format=>'basic +predicate'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\nSQL_ID 7ws837zynp1zv\n--------------------\nSELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,\nIS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND\nROWNUM < :B1\n \nPlan hash value: 3722429161\n \n--------------------------------------------------------------------\n| Id | Operation | Name |\n--------------------------------------------------------------------\n| 0 | SELECT STATEMENT | |\n| 1 | COUNT STOPKEY | |\n| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CARD_DETAILS |\n| 3 | INDEX RANGE SCAN | CARDDETAILS_CUST_IX |\n--------------------------------------------------------------------\n \n17 rows selected.\n<\/code><\/pre>\nThere is no predicates here.<\/p>\n
Then I try to get the plan with explain plan:<\/p>\n
SQL> alter session set current_schema=SOE;\n \nSession altered.\n \nSQL> explain plan for\n 2 SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,\n 3 IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND\n 4 ROWNUM select * from table(dbms_xplan.display(format=>'basic +predicate'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\nPlan hash value: 2597291669\n \n-------------------------------------------\n| Id | Operation | Name |\n-------------------------------------------\n| 0 | SELECT STATEMENT | |\n|* 1 | COUNT STOPKEY | |\n|* 2 | TABLE ACCESS FULL| CARD_DETAILS |\n-------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 1 - filter(ROWNUM < TO_NUMBER(:B1))\n 2 - filter(\"CUSTOMER_ID\"=TO_NUMBER(:B2))\n \n15 rows selected.\n<\/code><\/pre>\nThe problem is that I’ve not the same plan. I want the predicates for the index access (plan hash value 3722429161).<\/p>\n
So I get the maximum information from the AWR plan, with +OUTLINE and +PEEKED_BINDS:<\/p>\n
SQL> select * from table(dbms_xplan.display_awr('7ws837zynp1zv',3722429161,format=>'basic +outline +peeked_binds'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\nSQL_ID 7ws837zynp1zv\n--------------------\nSELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,\nIS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND\nROWNUM < :B1\n \nPlan hash value: 3722429161\n \n--------------------------------------------------------------------\n| Id | Operation | Name |\n--------------------------------------------------------------------\n| 0 | SELECT STATEMENT | |\n| 1 | COUNT STOPKEY | |\n| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CARD_DETAILS |\n| 3 | INDEX RANGE SCAN | CARDDETAILS_CUST_IX |\n--------------------------------------------------------------------\n \nOutline Data\n-------------\n \n \/*+\n BEGIN_OUTLINE_DATA\n IGNORE_OPTIM_EMBEDDED_HINTS\n OPTIMIZER_FEATURES_ENABLE('12.1.0.2')\n DB_VERSION('12.1.0.2')\n ALL_ROWS\n OUTLINE_LEAF(@\"SEL$1\")\n INDEX_RS_ASC(@\"SEL$1\" \"CARD_DETAILS\"@\"SEL$1\"\n (\"CARD_DETAILS\".\"CUSTOMER_ID\"))\n BATCH_TABLE_ACCESS_BY_ROWID(@\"SEL$1\" \"CARD_DETAILS\"@\"SEL$1\")\n END_OUTLINE_DATA\n *\/\n \nPeeked Binds (identified by position):\n--------------------------------------\n \n 1 - :B2 (NUMBER): 315821\n 2 - :B1 (NUMBER): 15\n \n39 rows selected.\n<\/code><\/pre>\nAnd 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):<\/p>\n
SQL> explain plan for\n 2 SELECT\n 3 \/*+\n 4 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')\n 5 DB_VERSION('12.1.0.2')\n 6 ALL_ROWS\n 7 OUTLINE_LEAF(@\"SEL$1\")\n 8 INDEX_RS_ASC(@\"SEL$1\" \"CARD_DETAILS\"@\"SEL$1\"\n 9 (\"CARD_DETAILS\".\"CUSTOMER_ID\"))\n 10 BATCH_TABLE_ACCESS_BY_ROWID(@\"SEL$1\" \"CARD_DETAILS\"@\"SEL$1\")\n 11 *\/\n 12 CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE,\n 13 IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND\n 14 ROWNUM select * from table(dbms_xplan.display(format=>'basic +predicate'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\nPlan hash value: 3722429161\n \n--------------------------------------------------------------------\n| Id | Operation | Name |\n--------------------------------------------------------------------\n| 0 | SELECT STATEMENT | |\n|* 1 | COUNT STOPKEY | |\n| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CARD_DETAILS |\n|* 3 | INDEX RANGE SCAN | CARDDETAILS_CUST_IX |\n--------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 1 - filter(ROWNUM < TO_NUMBER(:B1))\n 3 - access(\"CUSTOMER_ID\"=TO_NUMBER(:B2))\n \n16 rows selected.\n<\/code><\/pre>\nBingo, I’ve now the predicates.<\/p>\n
Time for a Wish<\/h3>\n
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).<\/p>\n
I’ve posted the idea on OTN. Please vote for the idea here<\/a><\/p>\nUpdate 13-MAY-2020<\/h3>\n