Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to check the order of SQL execution plan by oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

View the execution plan + execution plan sequence of SQL:

With sql_plan_data as

(select *

From gv$sql_plan a-- if it is in awr, it can be changed to dba_hist_sql_plan.

Where a.sql_id = 'gf95jb9ub5zp0'

And plan_hash_value = 3228133112)

Hierarchical_sql_plan_data as

(select *

From sql_plan_data

Start with id = 0

Connect by prior id = parent_id

Order siblings by id desc)

Select sql_id

Plan_hash_value

Id

Row_number () over (order by rownum desc) as exec_ord

Lpad ('', 2 * (depth-1)) | | operation | | options operation

Object_name

Cardinality

Bytes

Io_cost

Cpu_cost

Cost

Time

Access_predicates

From hierarchical_sql_plan_data

Order by id

# # #

--

-- Script: xplan.sql

--

-- Author: Adrian Billington

--

--

Description: Creates a package named XPLAN as a wrapper over DBMS_XPLAN. Provides access to

-- the following DBMS_XPLAN pipelined functions:

--

-1. DISPLAY

-2. DISPLAY_CURSOR

3. DISPLAY_AWR (optional-see Notes section for licence implications).

--

The XPLAN wrapper package has one purpose: to include an "order" column in the

-- plan output to show the order in which plan operations are performed. See the

-- following example for details.

--

-Example: DBMS_XPLAN output (format BASIC):

-

-| Id | Operation | Name |

-

-| 0 | SELECT STATEMENT | |

-| 1 | MERGE JOIN | |

-| 2 | TABLE ACCESS BY INDEX ROWID | DEPT |

-| 3 | INDEX FULL SCAN | PK_DEPT |

-| 4 | SORT JOIN | |

-| 5 | TABLE ACCESS FULL | EMP |

-

--

-Equivalent XPLAN output (format BASIC):

-

-| Id | Order | Operation | Name |

-

-| 0 | 6 | SELECT STATEMENT | |

-| 1 | 5 | MERGE JOIN | |

-| 2 | 2 | TABLE ACCESS BY INDEX ROWID | DEPT |

-| 3 | 1 | INDEX FULL SCAN | PK_DEPT |

-| 4 | 4 | SORT JOIN | |

-| 5 | 3 | TABLE ACCESS FULL | EMP |

-

--

-- Usage: SELECT * FROM TABLE (XPLAN.DISPLAY (...))

-- SELECT * FROM TABLE (XPLAN.DISPLAY_CURSOR (...))

-- SELECT * FROM TABLE (XPLAN.DISPLAY_AWR (...))

--

-- Usage for XPLAN is exactly the same as for DBMS_XPLAN. See the DBMS_XPLAN

-- documentation for all options.

--

-- Note that the only exception to this is that XPLAN.DISPLAY does not contain

-- the FILTER_PREDS parameter available in 10.2 + versions of DBMS_XPLAN.DISPLAY

-(this parameter enables us to limit the data being returned from an Explain

Plan but is of quite limited use).

--

-- See the Notes section for details on the licensing implications of using

-- XPLAN.DISPLAY_AWR.

--

Versions: This utility will work for all versions of 10g and upwards.

--

-- Required: 1) PLAN_TABLE of at least 10.1 format

--

-2) Either:

-- SELECT ANY DICTIONARY

-- Or:

-- SELECT on V$DATABASE

-- SELECT on V$SQL_PLAN

-- SELECT on V$SESSION

-- SELECT on V$MYSTAT

-- SELECT on DBA_HIST_SQL_PLAN

--

-3) CREATE TYPE, CREATE PROCEDURE

--

-- Notes: * IMPORTANT: PLEASE READ *

--

-- 1) Oracle license implications

-- The AWR functionality of XPLAN accesses a DBA_HIST% view which means

-- that it requires an Oracle Diagnostic Pack license. The XPLAN.DISPLAY_AWR

-- pipelined function is therefore disabled by default. It can be included

-- by modifying two substitution variables at the start of the script. Please

-- ensure that you are licensed to use this feature: the author accepts

-- no responsibility for any use of this functionality in an unlicensed database.

--

-- Installation: Installation requires SQL*Plus or any IDE that supports substitution

-- variables and SQL*Plus SET commands. To install, simply run the script in

-- the target schema.

--

-- Creates: 1) XPLAN_OT object type

-- 2) XPLAN_NTT collection type

-- 3) XPLAN package

--

-- Removal: 1) DROP PACKAGE xplan

-- 3) DROP TYPE xplan_ntt

-- 4) DROP TYPE xplan_ot

--

--

--

Define the "commenting-out" substitution variables for the AWR elements of this utility. The

-- default is commented out. To include the AWR functionality, change the variables to "" (i.e.

A single space).

--

SET DEFINE ON

DEFINE _ awr_start = "/ *"

DEFINE _ awr_end = "* /"

--

-- Supporting types for the pipelined functions...

--

CREATE OR REPLACE TYPE xplan_ot AS OBJECT (plan_table_output VARCHAR2)

/

CREATE OR REPLACE TYPE xplan_ntt AS TABLE OF xplan_ot

/

--

-- Xplan package...

--

CREATE OR REPLACE PACKAGE xplan AS

FUNCTION display (p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE'

P_statement_id IN VARCHAR2 DEFAULT NULL

P_format IN VARCHAR2 DEFAULT 'TYPICAL')

RETURN xplan_ntt PIPELINED

FUNCTION display_cursor (p_sql_id IN VARCHAR2 DEFAULT NULL

P_cursor_child_no IN INTEGER DEFAULT 0

P_format IN VARCHAR2 DEFAULT 'TYPICAL')

RETURN xplan_ntt PIPELINED

& & _ awr_start

FUNCTION display_awr (p_sql_id IN VARCHAR2

P_plan_hash_value IN INTEGER DEFAULT NULL

P_db_id IN INTEGER DEFAULT NULL

P_format IN VARCHAR2 DEFAULT 'TYPICAL')

RETURN xplan_ntt PIPELINED

& & _ awr_end

END xplan

/

CREATE OR REPLACE PACKAGE BODY xplan AS

TYPE ntt_order_map_binds IS TABLE OF VARCHAR2 (100)

TYPE aat_order_map IS TABLE OF PLS_INTEGER

INDEX BY PLS_INTEGER

G_map aat_order_map

G_hdrs PLS_INTEGER

G_len PLS_INTEGER

G_pad VARCHAR2 (300)

PROCEDURE reset_state IS

BEGIN

G_hdrs: = 0

G_len: = 0

G_pad: = NULL

G_map.DELETE

END reset_state

PROCEDURE build_order_map (p_sql IN VARCHAR2

P_binds IN ntt_order_map_binds) IS

TYPE rt_id_data IS RECORD

(id PLS_INTEGER

, ord PLS_INTEGER)

TYPE aat_id_data IS TABLE OF rt_id_data

INDEX BY PLS_INTEGER

Aa_ids aat_id_data

V_cursor SYS_REFCURSOR

V_sql VARCHAR2 (32767)

BEGIN

-- Build SQL template...

V_sql: = 'WITH sql_plan_data AS (' | |

P_sql | |'

)

, hierarchical_sql_plan_data AS (

SELECT id

FROM sql_plan_data

START WITH id = 0

CONNECT BY PRIOR id = parent_id

ORDER SIBLINGS BY id DESC

)

SELECT id

, ROW_NUMBER () OVER (ORDER BY ROWNUM DESC) AS ord

FROM hierarchical_sql_plan_data'

-- Binds will differ according to plan type...

-

CASE p_binds.COUNT

WHEN 0

THEN

OPEN v_cursor FOR v_sql

WHEN 1

THEN

OPEN v_cursor FOR v_sql USING p_binds (1)

WHEN 2

THEN

OPEN v_cursor FOR v_sql USING p_binds (1)

TO_NUMBER (p_binds (2))

WHEN 3

THEN

OPEN v_cursor FOR v_sql USING p_binds (1)

TO_NUMBER (p_binds (2))

TO_NUMBER (p_binds (3))

END CASE

-- Fetch the ID and order data...

FETCH v_cursor BULK COLLECT INTO aa_ids

CLOSE v_cursor

-- Populate the order map...

FOR i IN 1.. Aa_ids.COUNT LOOP

G_map (aa_ids (I) .id): = aa_ids (I) .ord

END LOOP

-- Use the map to determine padding needed to slot in our order column...

-

IF g_map.COUNT > 0 THEN

G_len: = LEAST (LENGTH (g_map.LAST) + 7,8)

G_pad: = LPAD ('-', g_len,'-')

END IF

END build_order_map

FUNCTION prepare_row (p_curr IN VARCHAR2

P_next IN VARCHAR2) RETURN xplan_ot IS

V_id PLS_INTEGER

V_row VARCHAR2 (4000)

V_hdr VARCHAR2 (64): ='% |% Id% |% Operation% |%'

BEGIN

-- Intercept the plan section to include a new column for the

-- the operation order that we mapped earlier. The plan output

-- itself will be bound by the 2nd, 3rd and 4th dashed lines.

-- We need to add in additional dashes, the order column heading

-- and the order value itself...

-

IF p_curr LIKE'-% 'THEN

IF p_next LIKE v_hdr THEN

G_hdrs: = 1

V_row: = g_pad | | p_curr

ELSIF g_hdrs BETWEEN 1 AND 3 THEN

G_hdrs: = g_hdrs + 1

V_row: = g_pad | | p_curr

ELSE

V_row: = p_curr

END IF

ELSIF p_curr LIKE v_hdr THEN

V_row: = REGEXP_REPLACE (

P_curr,'\ |'

RPAD ('|', GREATEST (g_len-7, 2)) | | 'Order |'

1, 2

);

ELSIF REGEXP_LIKE (p_curr,'^\ | [\ * 0-9] +\ |') THEN

V_id: = REGEXP_SUBSTR (p_curr,'[0-9] +')

V_row: = REGEXP_REPLACE (

P_curr,'\ |'

'|' | | LPAD (g_map (v_id), GREATEST (g_len-8, 6)) | |'|'

1, 2

);

ELSE

V_row: = p_curr

END IF

RETURN xplan_ot (v_row)

END prepare_row

FUNCTION display (p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE'

P_statement_id IN VARCHAR2 DEFAULT NULL

P_format IN VARCHAR2 DEFAULT 'TYPICAL')

RETURN xplan_ntt PIPELINED IS

V_plan_table VARCHAR2: = NVL (p_table_name, 'PLAN_TABLE')

V_sql VARCHAR2 (512)

V_binds ntt_order_map_binds: = ntt_order_map_binds ()

BEGIN

Reset_state ()

-- Prepare the inputs for the order map...

V_sql: = 'SELECT id, parent_id

FROM'| | v_plan_table | |'

WHERE plan_id = (SELECT MAX (plan_id))

FROM'| | v_plan_table | |'

WHERE id = 0% bind%)

ORDER BY id'

IF p_statement_id IS NULL THEN

V_sql: = REPLACE (v_sql,'% bind%')

ELSE

V_sql: = REPLACE (v_sql,'% bind%', 'AND statement_id =: bv_statement_id')

V_binds: = ntt_order_map_binds (p_statement_id)

END IF

-- Build the order map...

-

Build_order_map (v_sql, v_binds)

-- Now we can call DBMS_XPLAN to output the plan...

-

FOR r_plan IN (SELECT plan_table_output AS p

, LEAD (plan_table_output) OVER (ORDER BY ROWNUM) AS np

FROM TABLE (

DBMS_XPLAN.DISPLAY (

V_plan_table, p_statement_id, p_format

))

ORDER BY

ROWNUM)

LOOP

IF g_map.COUNT > 0 THEN

PIPE ROW (prepare_row (r_plan.p, r_plan.np))

ELSE

PIPE ROW (xplan_ot (r_plan.p))

END IF

END LOOP

Reset_state ()

RETURN

END display

FUNCTION display_cursor (p_sql_id IN VARCHAR2 DEFAULT NULL

P_cursor_child_no IN INTEGER DEFAULT 0

P_format IN VARCHAR2 DEFAULT 'TYPICAL')

RETURN xplan_ntt PIPELINED IS

V_sql_id v$sql_plan.sql_id%TYPE

V_child_no v$sql_plan.child_number%TYPE

V_sql VARCHAR2 (256)

V_binds ntt_order_map_binds: = ntt_order_map_binds ()

BEGIN

Reset_state ()

-- Set a SQL_ID if default parameters passed...

-

IF p_sql_id IS NULL THEN

SELECT prev_sql_id, prev_child_number

INTO v_sql_id, v_child_no

FROM v$session

WHERE sid = (SELECT m.sid FROM v$mystat m WHERE ROWNUM = 1)

AND username IS NOT NULL

AND prev_hash_value 0

ELSE

V_sql_id: = p_sql_id

V_child_no: = p_cursor_child_no

END IF

-- Prepare the inputs for the order mapping...

-

V_sql: = 'SELECT id, parent_id

FROM v$sql_plan

WHERE sql_id =: bv_sql_id

AND child_number =: bv_child_no'

V_binds: = ntt_order_map_binds (v_sql_id, v_child_no)

-- Build the plan order map from the SQL...

Build_order_map (v_sql, v_binds)

-- Now we can call DBMS_XPLAN to output the plan...

-

FOR r_plan IN (SELECT plan_table_output AS p

, LEAD (plan_table_output) OVER (ORDER BY ROWNUM) AS np

FROM TABLE (

DBMS_XPLAN.DISPLAY_CURSOR (

V_sql_id, v_child_no, p_format

))

ORDER BY

ROWNUM)

LOOP

IF g_map.COUNT > 0 THEN

PIPE ROW (prepare_row (r_plan.p, r_plan.np))

ELSE

PIPE ROW (xplan_ot (r_plan.p))

END IF

END LOOP

Reset_state ()

RETURN

END display_cursor

& _ awr_start

FUNCTION display_awr (p_sql_id IN VARCHAR2

P_plan_hash_value IN INTEGER DEFAULT NULL

P_db_id IN INTEGER DEFAULT NULL

P_format IN VARCHAR2 DEFAULT 'TYPICAL')

RETURN xplan_ntt PIPELINED IS

V_sql VARCHAR2 (256)

V_binds ntt_order_map_binds: = ntt_order_map_binds ()

BEGIN

Reset_state ()

-- Prepare the SQL for the order mapping...

V_sql: = 'SELECT id, parent_id

FROM dba_hist_sql_plan

WHERE sql_id =: bv_sql_id

AND plan_hash_value =: bv_plan_hash_value

AND dbid =: bv_dbid'

-- Determine all plans for the sql_id...

FOR r_awr IN (SELECT DISTINCT

Sql_id

, plan_hash_value

, dbid

FROM dba_hist_sql_plan

WHERE sql_id = p_sql_id

AND plan_hash_value = NVL (p_plan_hash_value, plan_hash_value)

AND dbid = NVL (p_db_id, (SELECT dbid FROM v$database))

ORDER BY

Plan_hash_value)

LOOP

-- Prepare the binds and build the order map...

-

V_binds: = ntt_order_map_binds (r_awr.sql_id

R_awr.plan_hash_value

R_awr.dbid)

-- Build the plan order map from the SQL...

Build_order_map (v_sql, v_binds)

-- Now we can call DBMS_XPLAN to output the plan...

-

FOR r_plan IN (SELECT plan_table_output AS p

, LEAD (plan_table_output) OVER (ORDER BY ROWNUM) AS np

FROM TABLE (

DBMS_XPLAN.DISPLAY_AWR (

R_awr.sql_id, r_awr.plan_hash_value

R_awr.dbid, p_format

))

ORDER BY

ROWNUM)

LOOP

IF g_map.COUNT > 0 THEN

PIPE ROW (prepare_row (r_plan.p, r_plan.np))

ELSE

PIPE ROW (xplan_ot (r_plan.p))

END IF

END LOOP

END LOOP

Reset_state ()

RETURN

END display_awr

& _ awr_end

END xplan

/

UNDEFINE _ awr_start

UNDEFINE _ awr_end

That's all for "how oracle checks the order of SQL execution plans". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.