ORACLE displays SQL statements with multiple execution plans, oraclesql

ORACLE displays SQL statements with multiple execution plans, oraclesql

In the SQL optimization process, you sometimes need to check which sqls have Multiple execution Plans (Multiple Executions Plans for the same SQL statement ), because the same SQL statement has multiple execution plans, it generally means that the code is faulty or for some other reasons. For example, the SQL statement uses the Bind Variable, however, the types or lengths of the bound variables may be inconsistent, resulting in different execution plans for the same SQL statement.

-- Check the SQL _ID of the SQL statement with multiple execution plans in the database

SELECT SQL_ID, COUNT(1) AS PLAN_NUM

FROM V$SQL

GROUP BY SQL_ID

HAVING COUNT(1) >=2

ORDER BY 2 DESC ;

-- View the corresponding SQL statement based on the specific SQL _ID

SELECT * FROM V$SQL

WHERE SQL_ID=:SQL_ID;

You can use the following SQL statement to query all or partial execution plans of the corresponding SQL statement, and analyze the causes of multiple execution plans.

-- Query all SQL Execution plans

SELECT * FROM TABLE(SYS.DBMS_XPLAN.DISPLAY_CURSOR('9x4fggs2mzu0m',null))

-- Query the execution plan with the child number of SQL being 0

SELECT * FROM TABLE(SYS.DBMS_XPLAN.DISPLAY_CURSOR('9x4fggs2mzu0m',0))

-- Query the execution plan whose child number is 1

SELECT * FROM TABLE(SYS.DBMS_XPLAN.DISPLAY_CURSOR('9x4fggs2mzu0m',1))