Explain Plans and DBMS_XPLAN

This post gives an overview of using DBMS_XPLAN and selecting a SQL_ID to use.


Run the following SQL query to obtain some information regarding the SQL_ID of interest..

=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2014.12.17 18:32:47 =~=~=~=~=~=~=~=~=~=~=~=
run
  1  SELECT DISTINCT sql_id,
  2     address,
  3     hash_value,
  4     child_number,
  5     plan_hash_value
  6  FROM   v$sql_plan
  7  WHERE  sql_id = '2x409g450hfyc'
  8* ORDER  BY child_number

SQL_ID      ADDRESS       HASH_VALUE CHILD_NUMBER      PLAN_HASH_VALUE
------------- ---------------- ---------- ------------ ---------------
2x409g450hfyc 00000002DCF6C808  168311756          0      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          1      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          2      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          3      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          4       240831378
2x409g450hfyc 00000002DCF6C808  168311756          5      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          6      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          7      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          8      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          9      1748903202
2x409g450hfyc 00000002DCF6C808  168311756         10      1748903202

11 rows selected.

Or even:

SELECT sql_id,
       sql_text,
       hash_value,
       plan_hash_value,
       child_number
FROM   v$sql
WHERE  Regexp_like (sql_text, 'fred', 'i');

Now use the supplied Oracle package DBMS_XPLAN to obtain the explain plan.


SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('2x409g450hfyc',7,'ALL'));


where 2x409g450hfyc is the SQL_ID of interest and
7 is the child number.

By using different child numbers, you can select which plan hash value you wish to display. Child number 2, for example, displays the plan with a hash value of 1748903202 whilst child 4 gives you 240831378.


Keywords: oracle xplan explain plan sql_id