`

Re-post: DBMS_XPLAN : Display Oracle Execution Plans

 
阅读更多

DBMS_XPLAN : Display Oracle Execution Plans

The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.

Related articles.

Setup

If it is not already present create the SCOTT schema.

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist.

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

DISPLAY Function

The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement.

CONN scott/tiger

EXPLAIN PLAN FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.

SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

SQL>

The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

  • table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
  • statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
  • format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

Note. From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with '-'). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

Plan hash value: 3625962092

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | EMP     |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
------------------------------------------------

12 rows selected.

SQL>

DISPLAY_CURSOR Function

In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional parameters:

  • sql_id - The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.
  • child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.
  • format - In addition to the setting available for the DISPLAY function, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively. These additional format options require "STATISTICS_LEVEL=ALL".

The following example show the advanced output from a query on the SCOTT schema.

CONN / AS SYSDBA
GRANT SELECT ON v_$session TO scott;
GRANT SELECT ON v_$sql TO scott;
GRANT SELECT ON v_$sql_plan TO scott;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott;

CONN scott/tiger

SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  gu62pbk51ubc3, child number 0
-------------------------------------
SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename
 = 'SMITH'

Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
       "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],
       "D"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

67 rows selected.

SQL>

Other Functions

There are some other useful functions in the package, but I don't find myself using them very often, so they are summarized below. If you need more information, follow the links at the bottom of the article for the appropriate database version.

  • DISPLAY_AWR - Introduced in Oracle 10g Release 1, this function displays an execution plan stored in the Advanced Workload Repository (AWR).
  • DISPLAY_SQLSET - Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.
  • DISPLAY_SQL_PLAN_BASELINE - Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
  • DISPLAY_PLAN - Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.

Reading Execution Plans

Execution plans can look very confusing, but reading them is reasonably simple provided you follow three simple rules:

  1. The first operation, or starting point, is the first leaf node, when reading from the top to the bottom. That is, the first element without an indented entry below it. You read from that point backwards.
  2. Join operations always require two sets. The order you read the sets is top down, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table.
  3. One join is performed at a time, so you only need to consider two sets and their join operation at any one time.

Looking at the following execution plan, the order of the operations is 4, 5, 3, 6, 2, 9, 10, 8, 7, 1, 0.

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |    10 |   570 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                    |                   |    10 |   570 |     7  (15)| 00:00:01 |
|   2 |   NESTED LOOPS                |                   |       |       |            |          |
|   3 |    NESTED LOOPS               |                   |    10 |   380 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
|   7 |   VIEW                        | index$_join$_004  |   107 |  2033 |     3  (34)| 00:00:01 |
|*  8 |    HASH JOIN                  |                   |       |       |            |          |
|   9 |     INDEX FAST FULL SCAN      | EMP_NAME_IX       |   107 |  2033 |     1   (0)| 00:00:01 |
|  10 |     INDEX FAST FULL SCAN      | EMP_EMP_ID_PK     |   107 |  2033 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Using Adrian Billington's utility, described below, we can show the plan including the order of the operations in the "Ord" column.

---------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 |     |  11 | SELECT STATEMENT              |                   |    10 |   570 |     7  (15)| 00:00:01 |
|*  1 |   0 |  10 |  HASH JOIN                    |                   |    10 |   570 |     7  (15)| 00:00:01 |
|   2 |   1 |   5 |   NESTED LOOPS                |                   |       |       |            |          |
|   3 |   2 |   3 |    NESTED LOOPS               |                   |    10 |   380 |     4   (0)| 00:00:01 |
|*  4 |   3 |   1 |     TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|*  5 |   3 |   2 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   6 |   2 |   4 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
|   7 |   1 |   9 |   VIEW                        | index$_join$_004  |   107 |  2033 |     3  (34)| 00:00:01 |
|*  8 |   7 |   8 |    HASH JOIN                  |                   |       |       |            |          |
|   9 |   8 |   6 |     INDEX FAST FULL SCAN      | EMP_NAME_IX       |   107 |  2033 |     1   (0)| 00:00:01 |
|  10 |   8 |   7 |     INDEX FAST FULL SCAN      | EMP_EMP_ID_PK     |   107 |  2033 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Extending DBMS_XPLAN

Adrian Billington has created an "XPlan Utility", available here, to extend the output of DBMS_XPLAN to include the execution order of the steps. The following output shows the difference between the default output and that produced by Adrian's XPlan Utility.

CONN scott/tiger

EXPLAIN PLAN FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130

-- Default Output
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     1 |    58 |     3	 (0)| 00:00:53 |
|   1 |  NESTED LOOPS		     |	       |       |       |	    |	       |
|   2 |   NESTED LOOPS		     |	       |     1 |    58 |     3	 (0)| 00:00:53 |
|*  3 |    TABLE ACCESS FULL	     | EMP     |     1 |    38 |     2	 (0)| 00:00:35 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |     1 |       |     0	 (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1	 (0)| 00:00:18 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

SQL>


-- XPlan Utility output
@xplan.display.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation			 | Name    | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 |     |	6 | SELECT STATEMENT		 |	   |	 1 |	58 |	 3   (0)| 00:00:53 |
|   1 |   0 |	5 |  NESTED LOOPS		 |	   |	   |	   |		|	   |
|   2 |   1 |	3 |   NESTED LOOPS		 |	   |	 1 |	58 |	 3   (0)| 00:00:53 |
|*  3 |   2 |	1 |    TABLE ACCESS FULL	 | EMP	   |	 1 |	38 |	 2   (0)| 00:00:35 |
|*  4 |   2 |	2 |    INDEX UNIQUE SCAN	 | PK_DEPT |	 1 |	   |	 0   (0)| 00:00:01 |
|   5 |   1 |	4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |	 1 |	20 |	 1   (0)| 00:00:18 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

About
------
  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)


18 rows selected.

SQL>

In the extended output, the "Ord" column displays the execution order of the plan steps.

QB_NAME Hint

Sometimes the same table is included in a query multiple times, so it is hard to know which operation in the execution plan refers to which reference to the table. The QB_NAME solves this problem by allowing you to name, or alias, individual query blocks. The alias information is displayed when the FORMAT parameter of the DISPLAY% functions is set to "ALL", or the " +ALIAS" value is added to the FORMAT parameter in 10gR2 onwards.

The following query references the same table twice, so we cannot easily tell from the execution plan which reference is which.

SELECT (SELECT COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,
       (SELECT COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count
FROM   dual;

SET LINESIZE 100
SET PAGESIZE 50
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   5 |  FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Next, we add the QB_NAME hint to the subqueries, giving each a recognizable alias. When the correct format is selected, the output from the DISPLAY% functions now includes a table giving us the relevant alias for each operation.

SELECT (SELECT /*+ QB_NAME(salesman) */ COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,
       (SELECT /*+ QB_NAME(manager) */  COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count
FROM   dual;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALL));

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   5 |  FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SALESMAN
   2 - SALESMAN / EMP@SALESMAN
   3 - MANAGER
   4 - MANAGER  / EMP@MANAGER
   5 - SEL$1    / DUAL@SEL$1

GATHER_PLAN_STATISTICS Hint

Using the GATHER_PLAN_STATISTICS hint makes the optimizer gather the actual cardinalities in addition to the expected cardinalities in the execution plan. This can then be reported by the DISPLAY_CURSOR function if the format is set to 'ALLSTATS'.

CONN scott/tiger

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |         |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

Now the "Rows" column has been replaced by the original cardinality estimate (E-Rows) and the actual cardinality (A-Rows).

For more information see:

分享到：
| Mocking JMS infrastructure with MockRunn ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle中使用DBMS_XPLAN处理执行计划详解

    DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包；在以前查看SQL执行计划的时候，我都是使用set autotrace命令，不过现在看来，DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...

    ORACLE SYS.DBMS_REGISTRY_SYS has errors

    标题中的问题“ORACLE SYS.DBMS_REGISTRY_SYS has errors”指的是在Oracle数据库系统中，系统包BODY `SYS.DBMS_REGISTRY_SYS` 出现错误，导致了一系列的PL/SQL调用失败。这种错误通常与数据库的元数据注册功能有关，...

    Oracle运维最佳实践-下.pdf 带书签

    - 使用`DBMS_XPLAN.DISPLAY_CURSOR`显示执行计划，查找`dynamic sampling`关键字。 - 使用`DBMS_STATS.SET_DYNAMIC_SAMPLING`设置动态采样的级别。 - **2.1.12 Oracle AIO（异步IO）** - 异步IO是一种高级技术，...

    dbms_obfuscation_toolkit加密解密数据

    ### DBMS_OBFUSCATION_TOOLKIT：Oracle 数据库中的加密与解密工具包 DBMS_OBFUSCATION_TOOLKIT是Oracle数据库提供的一种用于数据加密解密的强大工具包，自Oracle 8i版本开始引入。它支持多种加密算法，如DES、...

    oracle dbms_lob

    Oracle数据库系统中，`DBMS_LOB`是一个重要的PL/SQL包，专门用于处理大型对象（LOBs，Large Object）。LOBs是Oracle提供的一种数据类型，用于存储大量数据，如文本、图像、音频或视频文件等。这个包包含了各种过程和...

    DBMS_XMLDOM DBMS_XMLPARSER DBMS_XMLQUERY 文档

    Oracle数据库系统提供了强大的XML处理能力，这主要体现在其内置的几个PL/SQL包上，如DBMS_XMLDOM、DBMS_XMLPARSER和DBMS_XMLQUERY。这些包为开发者提供了处理XML文档的一整套工具，使得在数据库环境中进行XML数据的...

    ORACLE DBMS STATS ERROR

    在Oracle数据库中，`DBMS_STATS` 是一个重要的包，它用于收集和管理表、索引和其他数据库对象的统计信息，这些信息用于优化器选择执行查询的最佳执行计划。然而，当出现错误“ORA-04063: package body 'SYS.DBMS_...

    DBMS_STATS.GATHER_TABLE_STATS详解.pdf

    ### DBMS_STATS.GATHER_TABLE_STATS详解 #### 一、概述 `DBMS_STATS.GATHER_TABLE_STATS` 是 Oracle 数据库中的一个重要过程，主要用于收集表、列和索引的统计信息，这些统计信息对于优化器选择合适的执行计划至关...

    使用dbms_stats包手工收集统计信息

    Oracle 数据库中使用 dbms_stats 包手动收集统计信息 在 Oracle 数据库中，dbms_stats 包提供了一种手动收集统计信息的方式，包括基于表、用户和索引的统计信息。通过使用 dbms_stats 包，我们可以手动收集统计信息...

    CLOB 字段类型报错 ORA-01704: 文字字符串过长的解决

    在Oracle数据库中，CLOB（Character Large Object）字段类型用于存储大量的文本数据，如XML文档、长篇文章或者大量字符数据。然而，当你尝试向CLOB字段插入数据时，如果超过了Oracle规定的最大限制，就会遇到“ORA-...

    DBMS_SQL的使用

    ### Oracle DBMS_SQL 使用详解 #### 一、概述 在Oracle数据库中，`DBMS_SQL`包是一个功能强大的工具，用于执行动态SQL语句。它提供了处理动态SQL语句的能力，使得开发人员能够灵活地构建和执行SQL语句，而不需要...

    javax.activation.UnsupportedDataTypeException: no object DCH for MIME type

    在Oracle 11.2.0.4中调用 javax.mail 发送邮件时可能会遇到以下错误： ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: IOException while sending message; nested ...

    unixODBC-2.2.11-7.1-i386_x64.zip

    UnixODBC（Open Database Connectivity for Unix）是一个开源的数据库连接器，它为各种数据库管理系统（DBMS）提供了一个统一的接口，使得应用程序可以在Unix、Linux以及macOS等操作系统上与不同的数据库进行交互。...

    ORA-00060: 等待资源时检测到死锁--oracle 数据库表死锁异常

    诊断死锁的方法主要包括使用Oracle的DBA视图如V$SESSION_WAIT和V$LOCKED_OBJECT，以及使用DBMS_XTRACE包进行死锁跟踪。在描述中提到的博文链接中，可能包含了通过这些视图分析死锁问题的实例。"表死锁反馈.doc"文件...

    怎样禁用及回收java的授权dbms_java

    在Oracle数据库环境中，`dbms_java`包提供了一系列功能强大的工具，用于管理和控制Java应用程序的安全性。这对于那些在Oracle环境中部署了Java应用程序的企业来说尤为重要。本文将详细介绍如何利用`dbms_java`包来...

    ORACLE NO_DATA_FOUND的三种处理办法

    在Oracle数据库中，`NO_DATA_FOUND`异常是当你执行一个SQL查询时，没有找到匹配的记录时抛出的。这个异常通常与PL/SQL编程有关，因为SQL查询在PL/SQL块（如游标、存储过程或函数）中运行。在处理`NO_DATA_FOUND`异常...

    dbms- III.rar_dbms_successfulpqn

    在“dbms-III.rar_dbms_successfulpqn”这个压缩包中，我们重点关注的是第三部分的DBMS内容，特别是与成功实践相关的知识点。其中包含的“dbms-III.pdf”文件很可能是详细阐述这些主题的文档。 数据库管理系统的...

    DBMS_SQL.rar_dbms_oracle

    在Oracle数据库系统中，DBMS_SQL是一个非常重要的包，它提供了动态执行SQL语句的功能，这对于开发复杂的数据库应用或者需要在运行时构建SQL语句的情况非常有用。DBMS_SQL允许我们处理那些在编译时未知的SQL语句，极...

Magicbox
Global site tag (gtag.js) - Google Analytics