ORACLE displays SQL statements with multiple execution plans, oraclesql

Source: Internet
Author: User

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))

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.