Oracle CBO :: Different Predicates, Same Execution Plan

There are some unfortunate and rare situations which I encounter on few misfortune days of my career, whose investigations lead me to new theories and discoveries. While internet becoming so much information rich these days that it becomes quite easy to find such solutions on a single search in Google, and opens up various solutions and other possibilities for a database administrator. So was the situation with me when I had gone completely frustrated to find some of the PeopleSoft application build queries taking up different predicates for same execution plan, and it became necessary for me to find where these plans were out of order, or were not matching with each other.

One tool that I very frequently use in my day to day investigations when it comes to SQL Tuning is the beautifully crafted SQLT Tool, developed by Carlos Sierra, which is available for free on the Metalink with any DBA having a valid registration.  The document ID on MOS for downloading and more information on SQLT is 215187.1. What I would provide below are sets of two very very useful scripts developed and shared by Carlos himself for tackling such situations like the one I’ve above.

Firstly, couple of situations (given below) wherein we can see such issues in the databases we support. There are surely more of them, which the Oracle support guys would be able to advise more.

  1. You have a SQL with multiple child cursors. They all could have the same Plan Hash Value (PHV) or maybe more than one. Performance wise even the same PHV can exhibit differences for multiple reason, and one of them is the possibility of having the same PHV but not exactly the same filter or access Predicates. If you have two or more predicates on the same Plan Operation, the difference could be the order of these Predicates, and if their execution performance is quite different, one version could perform better than the other.
  2. You have two or more versions of a core query, where some of these versions have slightly different Predicates (typical case where the SQL is dynamically assembled by an application). This family of similar queries would have different SQL_IDs but they may or may not produce the same PHV. When they do, it is hard to find in which Plan Operations the different Predicates are used.

Now, here’s the script (alongside the expected output) which would provide us with the difference in execution plans of such queries. The first inputs a SQL_ID and it outputs a list of those Predicates for each PHV where there is more than one version for any Plan Operation ID. The second inputs a PHV and performs the same analysis regardless if the PHV is associated to one or many SQL_IDs. Both are RAC aware. Both are stand-alone and install nothing.

Script # 1 : Input is the SQL_ID

SPO difpred1.txt;

SET HEA ON LIN 80 NEWP 1 PAGES 100 LIN 300 TRIMS ON TI OFF TIMI OFF;
-- shows which plan lines have different predicates for given sql_id
WITH d AS (
SELECT sql_id,
 plan_hash_value,
 id,
 COUNT(DISTINCT access_predicates) distinct_access_predicates,
 COUNT(DISTINCT filter_predicates) distinct_filter_predicates
 FROM gv$sql_plan_statistics_all
 WHERE sql_id = '&&sql_id.'
 GROUP BY
 sql_id,
 plan_hash_value,
 id
HAVING MIN(NVL(access_predicates, 'X')) != MAX(NVL(access_predicates, 'X'))
 OR MIN(NVL(filter_predicates, 'X')) != MAX(NVL(filter_predicates, 'X'))
)
SELECT v.plan_hash_value,
 v.id,
 'access' type,
 v.inst_id,
 v.child_number,
 v.access_predicates predicates
 FROM d,
 gv$sql_plan_statistics_all v
 WHERE v.sql_id = d.sql_id
 AND v.plan_hash_value = d.plan_hash_value
 AND v.id = d.id
 AND d.distinct_access_predicates > 1
 UNION ALL
SELECT v.plan_hash_value,
 v.id,
 'filter' type,
 v.inst_id,
 v.child_number,
 v.filter_predicates predicates
 FROM d,
 gv$sql_plan_statistics_all v
 WHERE v.sql_id = d.sql_id
 AND v.plan_hash_value = d.plan_hash_value
 AND v.id = d.id
 AND d.distinct_filter_predicates > 1
 ORDER BY
 1, 2, 3, 6, 4, 5;
SET HEA ON LIN 80 NEWP 1 PAGES 14 LIN 80 TRIMS OFF TI OFF TIMI OFF;
SPO OFF;

Sample Output of Script # 1:

old 8: WHERE sql_id = '&&sql_id.'

new 8: WHERE sql_id = 'cy9pxhp4y6u78'
PLAN_HASH_VALUE ID TYPE INST_ID CHILD_NUMBER
--------------- ---------- ------ ---------- ------------
PREDICATES
--------------------------------------------------------------------------------
 3724264953 2 filter 1 0
("C2"='WHATEVER' AND TRANSLATE(TO_CHAR(SIN("N4")),'1','a')='a')
3724264953 2 filter 1 1
(TRANSLATE(TO_CHAR(SIN("N4")),'1','a')='a' AND "C2"='WHATEVER')
That’s great! Now let’s look into the 2nd script.
Script # 2: Input is the Plan hash value
SPO difpred2.txt;

SET HEA ON LIN 80 NEWP 1 PAGES 100 LIN 300 TRIMS ON TI OFF TIMI OFF;
-- shows which plan lines have different predicates for given phv
WITH d AS (
SELECT plan_hash_value,
 id,
 COUNT(DISTINCT access_predicates) distinct_access_predicates,
 COUNT(DISTINCT filter_predicates) distinct_filter_predicates
 FROM gv$sql_plan_statistics_all
 WHERE plan_hash_value = &&plan_hash_value.
 GROUP BY
 plan_hash_value,
 id
HAVING MIN(NVL(access_predicates, 'X')) != MAX(NVL(access_predicates, 'X'))
 OR MIN(NVL(filter_predicates, 'X')) != MAX(NVL(filter_predicates, 'X'))
)
SELECT v.id,
 'access' type,
 v.sql_id,
 v.inst_id,
 v.child_number,
 v.access_predicates predicates
 FROM d,
 gv$sql_plan_statistics_all v
 WHERE v.plan_hash_value = d.plan_hash_value
 AND v.id = d.id
 AND d.distinct_access_predicates > 1
 UNION ALL
SELECT v.id,
 'filter' type,
 v.sql_id,
 v.inst_id,
 v.child_number,
 v.filter_predicates predicates
 FROM d,
 gv$sql_plan_statistics_all v
 WHERE v.plan_hash_value = d.plan_hash_value
 AND v.id = d.id
 AND d.distinct_filter_predicates > 1
 ORDER BY
 1, 2, 6, 3, 4, 5;
SET HEA ON LIN 80 NEWP 1 PAGES 14 LIN 80 TRIMS OFF TI OFF TIMI OFF;
SPO OFF;
Output of Script # 2:
Enter value for plan_hash_value: 2339135578

old 7: WHERE plan_hash_value = &&plan_hash_value.
new 7: WHERE plan_hash_value = 2339135578
ID TYPE SQL_ID INST_ID CHILD_NUMBER
---------- ------ ------------- ---------- ------------
PREDICATES
--------------------------------------------------------------------------------
 5 filter 72jhgzs8nb1p4 1 0
MAX("E2"."HIREDATE")=:B1
5 filter 8a4x4867rajuv 1 0
(MAX("E2"."HIREDATE")=:B1 OR :B2 IS NULL)
5 filter 8a4x4867rajuv 1 1
(MAX("E2"."HIREDATE")=:B1 OR :B2 IS NULL)
I certainly hope it helps you/all in case you ever face such situations in your career. I would be further investigating on the various causes of such occurrences, and would also try to dig out some typical packaged applications where we’d get these situations more often than others.
Important note: Please go through Carlos’ blog, which is full of such insights, more of such useful scripts and loads of information on SQLT Tool and Performance Tuning of Oracle databases in general.
This entry was posted in Computers, Oracle, Oracle DBA, Oracle Performance Tuning, programming, scripting, technology and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s