search
HomeDatabaseMysql TutorialOracle 生成和显示执行计划的方法

Oracle 生成和显示执行计划的方法,除执行过的sql会在内存中生成执行计划外,还可以通过explain plan让优化器对sql语句进行解析,

生成执行计划

一条合法的语句在执行之后,就会在内存中至少产生一条执行计划,可以从视图v$sql_plan查询。每一条执行计划对于一个游标。一条语句生产的第一个游标的CHILD_NUMBER(即v$sql_plan中的CHILD_NUMBER)为0,同一条sql语句可能因为环境或版本等其他因素不同而产生不同的执行计划,也就是说一条sql可能有多个CHILD_NUMBER

除执行过的sql会在内存中生成执行计划外,还可以通过explain plan让优化器对sql语句进行解析,生成查询计划。执行explain plan命令后,Oracle会将解释生成的执行计划插入sys.plan_table$(10g之前,表名为plan_table;10g之后,通过公共同义词plan_table指向sys.plan_table$)中。explain plan产生的执行计划不会在语句执行时重用,而是以类似explain plan for的形式在缓存中。

显示执行计划

 通过DBMS_XPLAN包显示执行计划(推荐)

DBMS_XPLAN含有5个函数用于输出格式化的执行计划,如下:

display:显示explain plan命令解释的执行计划

display_cursor:显示内存中的执行计划

display_awr:awr历史数据中的执行计划

display_sqlset:SQL优化集中语句的执行计划

display_sql_plan_baseline:SQL执行计划基线

display的用法

语法: 

DBMS_XPLAN.DISPLAY( table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE', statement_id IN VARCHAR2 DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL', filter_preds IN VARCHAR2 DEFAULT NULL);

参数: 

ParameterDescription

table_name

存储查询计划的表名,默认值为PLAN_TABLE

statement_id

要显示查询计划的SQL语句的ID,如果为空,就会获取最近一条被解释的语句

format

输出格式,有以下几种预定义的格式可选择,,还可以自定义(这里不多说):

  • BASIC:基本格式,内容最少.

  • TYPICAL: 典型格式,通常够用,默认格式

  • SERIAL: 串行执行格式,这种格式和典型格式输出内容基本一致,只是对于并行查询,不会输出相关的并行内容

  • ALL: 完全格式,输出内容相对完整

  • filter_preds

    该参数用谓词条件过滤存储执行计划的表,例如'plan_id = 10或cost>100(显示估算代价大于100的操作),该值默认为空

    示例:

    用explain plan执行sql语句:

    EXPLAIN PLAN FOR
    SELECT * FROM emp e, dept d
      WHERE e.deptno = d.deptno
      AND e.ename='benoit';
    Display the plan using the DBMS_XPLAN.DISPLAY table function

    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);
    输出结果如下

    Plan hash value: 3693697075
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |
    ---------------------------------------------------------------------------
    |  0 | SELECT STATEMENT  |      |    1 |    57 |    6  (34)| 00:00:01 |
    |*  1 |  HASH JOIN        |      |    1 |    57 |    6  (34)| 00:00:01 |
    |*  2 |  TABLE ACCESS FULL| EMP  |    1 |    37 |    3  (34)| 00:00:01 |
    |  3 |  TABLE ACCESS FULL| DEPT |    4 |    80 |    3  (34)| 00:00:01 |
    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
      1 - access("E"."DEPTNO"="D"."DEPTNO")
      2 - filter("E"."ENAME"='benoit')

    15 rows selected.

    linux

    Statement
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    What Are the Limitations of Using Views in MySQL?What Are the Limitations of Using Views in MySQL?May 14, 2025 am 12:10 AM

    MySQLviewshavelimitations:1)Theydon'tsupportallSQLoperations,restrictingdatamanipulationthroughviewswithjoinsorsubqueries.2)Theycanimpactperformance,especiallywithcomplexqueriesorlargedatasets.3)Viewsdon'tstoredata,potentiallyleadingtooutdatedinforma

    Securing Your MySQL Database: Adding Users and Granting PrivilegesSecuring Your MySQL Database: Adding Users and Granting PrivilegesMay 14, 2025 am 12:09 AM

    ProperusermanagementinMySQLiscrucialforenhancingsecurityandensuringefficientdatabaseoperation.1)UseCREATEUSERtoaddusers,specifyingconnectionsourcewith@'localhost'or@'%'.2)GrantspecificprivilegeswithGRANT,usingleastprivilegeprincipletominimizerisks.3)

    What Factors Influence the Number of Triggers I Can Use in MySQL?What Factors Influence the Number of Triggers I Can Use in MySQL?May 14, 2025 am 12:08 AM

    MySQLdoesn'timposeahardlimitontriggers,butpracticalfactorsdeterminetheireffectiveuse:1)Serverconfigurationimpactstriggermanagement;2)Complextriggersincreasesystemload;3)Largertablesslowtriggerperformance;4)Highconcurrencycancausetriggercontention;5)M

    MySQL: Is it safe to store BLOB?MySQL: Is it safe to store BLOB?May 14, 2025 am 12:07 AM

    Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

    MySQL: Adding a user through a PHP web interfaceMySQL: Adding a user through a PHP web interfaceMay 14, 2025 am 12:04 AM

    Adding MySQL users through the PHP web interface can use MySQLi extensions. The steps are as follows: 1. Connect to the MySQL database and use the MySQLi extension. 2. Create a user, use the CREATEUSER statement, and use the PASSWORD() function to encrypt the password. 3. Prevent SQL injection and use the mysqli_real_escape_string() function to process user input. 4. Assign permissions to new users and use the GRANT statement.

    MySQL: BLOB and other no-sql storage, what are the differences?MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

    MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

    MySQL Add User: Syntax, Options, and Security Best PracticesMySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

    ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

    MySQL: How to avoid String Data Types common mistakes?MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

    ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

    See all articles

    Hot AI Tools

    Undresser.AI Undress

    Undresser.AI Undress

    AI-powered app for creating realistic nude photos

    AI Clothes Remover

    AI Clothes Remover

    Online AI tool for removing clothes from photos.

    Undress AI Tool

    Undress AI Tool

    Undress images for free

    Clothoff.io

    Clothoff.io

    AI clothes remover

    Video Face Swap

    Video Face Swap

    Swap faces in any video effortlessly with our completely free AI face swap tool!

    Hot Article

    Hot Tools

    Atom editor mac version download

    Atom editor mac version download

    The most popular open source editor

    WebStorm Mac version

    WebStorm Mac version

    Useful JavaScript development tools

    SublimeText3 English version

    SublimeText3 English version

    Recommended: Win version, supports code prompts!

    Dreamweaver Mac version

    Dreamweaver Mac version

    Visual web development tools

    Safe Exam Browser

    Safe Exam Browser

    Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.