《Pro Oracle SQL》 读书笔记ITeye - 威尼斯人

《Pro Oracle SQL》 读书笔记ITeye

2019年02月25日12时43分06秒 | 作者: 辰阳 | 标签: 函数,信息,运用 | 浏览: 1130

(Page 174)
    The DBMS_XPLAN package is supplied by Oracle and can be used to simplify the retrieval and display of plan output, as I have demonstrated.  In order to use all the procedures and functions in this package fully, you’ll need to have privileges to certain fixed views.  A single grant on SELECT_CATALOG_ROLE will ensure you have access to everything you need, but at a minimum, you should have select privileges for V$SQL, V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL in order to properly execute just the display and display_cursor functions.  In this section, I want to cover a few more details about the use of this package and, in particular, the format options for the display and display_cursor functions.

    The dbms_xplan package has grown since it first appeared in Oracle version 9.  At that time, it contained only the display function.  In Oracle 11 release 2, the package includes 21 functions, although only six of them are included in the documentation.  These functions can be used to display not only explain plan output, but plans for statements stored in the AWR (Automatic Workload Repository), SQL tuning sets, cached SQL cursors, and SQL plan baselines.  The five main table functions used to display plans from each of these areas are:
•  DISPLAY
•  DISPLAY_CURSOR
•  DISPLAY_AWR
•  DISPLAY_SQLSET
•  DISPLAY_SQL_PLAN_BASELINE
正如我之前所述,DBMS_XPLAN包是由ORACLE供给的,用于简化检索和展现方案输出。为了运用这个包中的一切存储进程和函数,你需求有检查某些 固化视图(fixed views)的权限。独自的颁发SELECT_CATALOG_ROLE将保证你有拜访你所需求的一切的(权力),可是最根本,为了恰当的履行 display和display_cursor函数,你应该要有对 V$SQL, V$SQL_PLAN, V$SESSION和V$SQL_PLAN_STATISTICS_ALL的select权限。在本节中,我将更具体的解说运用这个包,特别 是,display和display_cursor函数的格局化选项。
   dbms_xplan 包在Oracle 9开端发布,那时只是包括display函数。在Oracle 11.2,该包包括了21个函数,而只是只要6个包括在文档中。这些函数不只能用于解说方案输出,并且能用于输出存储于AWR(主动作业负载加载)中的语 句的,SQL调优集的,缓存的SQL游标的,以及SQL方案基线的方案。五个主表函数用于展现各自规模的方案。
•  DISPLAY
•  DISPLAY_CURSOR
•  DISPLAY_AWR
•  DISPLAY_SQLSET
•  DISPLAY_SQL_PLAN_BASELINE
     These five table functions all return the DBMS_XPLAN_TYPE_TABLE type, which is made up of 300 byte strings.  This type accommodates the variable formatting needs of each table function to display the plan table columns dynamically as needed.  The fact that these are table functions means that in order to call them you must use the TABLE function to cast the return type properly when used in a SELECT statement.  A table function is simply a stored PL/SQL function that behaves like a regular query to a table would.  The benefit is that you can write code in the function that performs transformations to data before it is returned in the result set.  In the case of queries against the PLAN_TABLE or V$SQL_PLAN, the use of a table function makes it possible to do all the dynamic formatting needed to output only the columns pertinent for a given SQL statement instead of having to try and create multiple queries to handle different needs.
这五个表函数全都回来 DBMS_XPLAN_TYPE_TABLE类型,它就是300字节的字符串。这种类型适应于各个表函数变量格局的需求,动态的按需展现方案的表列。正是 由于他们是表函数,意味着当你运用SELECT句子时,为了调用他们,你有必要用表函数强制转化(cast)恰当的回来类型。一个表函数其实就是一个 PL/SQL函数,用起来就像一般的表查询。优点在于你能在成果集回来之前,在函数中写代码(调用)履行数据转化。比方查询PLAN_TABLE或许 V$SQL_PLAN,运用表函数就能按需求动态的格局化输出给定SQL句子的相关列,而不是有必要企图和创立多个查询来处理不同的需求。
    Each of the table functions accepts a FORMAT parameter as input.  The FORMAT parameter controls
what information is included in the display output.  The following is a list of documented values for this
parameter:

•  BASIC displays only the operation name and its option.
•  TYPICAL displays the relevant information and variably displays options like partition and
parallel usage only when applicable.  This is the default.
•  SERIAL is the same as TYPICAL but always excludes parallel information.
•  ALL displays the maximum amount of information in the display.
    每个表函数接纳一个FORMAT的输入参数。FORMAT参数控制在展现输出中包括什么信息。下面的列表列出了该参数的文档值:

•  BASIC展现操作名和它的选项。
•  TYPICAL展现了相关的信息和可变的展现选项,比方分区和并行运用,仅当可用时。这是默许值。
•  SERIAL同TYPICAL,但扫除并行信息。
•  ALL展现悉数信息。
    In addition to the basic format parameter values, there are several additional more granular
options that can be used to customize the default behavior of the base values.  You can specify multiple keywords separated by a comma or a space and use the prefix of a plus sign (+) to indication inclusion or a minus sign (-) to indicate exclusion of that particular display element.  All of these options will display the information only if relevant.  The following is a list of optional keywords:
     除了根本的格局化参数值,还有几个更细粒度的选项能用来自界说根本值的默许行为。你能够指定多个关键字,运用逗号,空格,用加号(+)表明包括,减号(-)表明扫除,(衔接)特别展现的元素。所的有的这些选项将只是展现相关信息。下面列出了可选的关键字:
•  ADVANCED shows the same as ALL plus the Outline section and the peeked binds section.
•  ALIAS shows the Query Block Name/Object Alias section.
•  ALL shows the Query Block Name/Object Alias section, the predicate section, and the column
projection section.
•  ALLSTATS* is equivalent to IOSTATS LAST.
•  BYTES shows the estimated number of bytes. 
•  COST is the cost information computed by the optimizer.
•  IOSTATS* show IO statistics for executions of the cursor.
•  LAST* shows only the plan statistics for the last execution of the cursor (the default is ALL and
is cumulative).
•  MEMSTATS* shows the memory management statistics for memory intensive operation like
hash-joins, sorts, or some bitmap operators.
•  NOTE shows the Note section.
•  OUTLINE shows the Outline section (set of hints that will reproduce the plan).
•  PARALLEL shows parallel execution information.
•  PARTITION shows partition pruning information.
•  PEEKED_BINDS shows bind variable values.
•  PREDICATE shows the predicate section.
•  PROJECTION shows the column projection section (which columns have been passed to their
parent by each line and the size of those columns).
•  REMOTE shows distributed query information.

•  ADVANCE展现同ALL,还加上纲要(Outline)段和监督的绑定(变量)段。
•  ALIAS展现查询块名/目标别号段。
•  ALL展现查询块名/目标别号段,谓词段,还有列投影段。
•  ALLSTATS*等同于IOSTATS LAST。
•  BYTES展现评价的字节数。
•  COST是优化器核算的本钱信息。
•  IOSTATS*显现游标运转的IO计算(信息)。
•  LAST*只是展现前次游标运转的方案计算(默许是ALL且是累计的)。
•  MEMSTATS* 展现像哈希-衔接,排序,或一些位图操作等内存密布操作的内存办理计算(信息)。
•  NOTE展现留意段。
•  OUTLINE展现纲要段(提示集用于仿制方案)。
•  PARALLEL展现并行履行信息。
•  PARTITION展现分区修剪信息。
•  PEEKED_BINDS展现绑定变量的值。
•  PREDICATE展现谓词段。
•  PROJECTION展现列映射段(一行中的哪些列传递给他的上一级了以及这些列的巨细)。
•  REMOTE展现散布查询信息。
 
    The keywords followed by an asterisk are not available for use with the DISPLAY function as they
utilize information from V$SQL_PLAN_STATISTICS_ALL that only exists after a statement has been
executed.  Listing 6-13 shows several examples of the various options in use.
    后面带星号的关键字关于DISPLAY函数不是有用的。由于它们运用来自V$SQL_PLAN_STATISTICS_ALL的信息,只在句子履行之后存在。列表6-13显现了各选项运用的几个比如。

Listing 6-13. Display Options Using the FORMAT Parameter   运用格局化参数的展现选项
SQL explain plan for
  2  select * from emp e, dept d
  3  where e.deptno = d.deptno
  4  and e.ename = JONES ;
 
Explained.
 
SQL select * from table(dbms_xplan.display(format= ALL));
(在ORACLE 10g Express版别上运转上述句子报错“报错ORA-00907:丢掉右括号”,只能用select * from table(dbms_xplan.display(PLAN_TABLE,,ALL)); ) PLAN_TABLE_OUTPUT
-
Plan hash value: 3625962092
 

| Id  | Operation                                                | Name       | Rows  | Bytes | Cost (%CPU)  | Time     |

|   0 | SELECT STATEMENT                                 |                  |     1    |    59    |     4   (0)        | 00:00:01 |
1 |   NESTED LOOPS                                      |                  |            |            |                       |               |
2 |      NESTED LOOPS                                   |                  |     1     |    59   |     4   (0)         | 00:00:01 |
|*  3 |        TABLE ACCESS FULL                        | EMP           |     1    |    39    |     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 |

 
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

Predicate Information (identified by operation id):
-
 
   3 - filter("E"."ENAME"=JONES)
   4 - access("E"."DEPTNO"="D"."DEPTNO")
 
Column Projection Information (identified by operation id):
-
 
   1 - (#keys=0) "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 - (#keys=0) "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]
 
SQL select empno, ename from emp e, dept d
  2  where e.deptno = d.deptno
  3  and e.ename = JONES ;

     EMPNO ENAME

      7566 JONES
 
1 row selected.
 
SQL select * from table(dbms_xplan.display_cursor(null,null,format= ALLSTATS LAST -COST -
BYTES));
(在ORACLE 10g Express版别上运转上述句子报错“报错ORA-00907:丢掉右括号”

所以只能运转:select * from table(dbms_xplan.display_cursor(null,null,ALLSTATS LAST -COST -BYTES));

拜见附件检查实践运转成果) PLAN_TABLE_OUTPUT

SQL_ID  3mypf7d6npa97, child number 0
-
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = JONES
 (page 179)

Plan hash value: 2949544139
 

| Id  | Operation                                              | Name       | Rows   | Bytes  | Cost (%CPU)  | Time       |

0 | SELECT STATEMENT                               |                  |            |            |     1 (100)       |                |
|   1 |   TABLE ACCESS BY INDEX ROWID         | EMP           |     1     |    39    |     1   (0)        | 00:00:01 |
|*  2 |      INDEX UNIQUE SCAN                       | PK_EMP     |     1     |            |     0   (0)        |                |

 
Peeked Binds (identified by position):

 
   1 - :V_EMPNO (NUMBER): 7566
 
Predicate Information (identified by operation id):
-
 
   2 - access("EMPNO"=:V_EMPNO)

SQL select /*+ parallel(d, 4) parallel (e, 4) */
  2  d.dname, avg(e.sal), max(e.sal)
  3  from dept d, emp e
  4  where d.deptno = e.deptno
  5  group by d.dname
  6  order by max(e.sal), avg(e.sal) desc;
 
DNAME               AVG(E.SAL)               MAX(E.SAL)
           -                  -
SALES               1723.3333333333            3135
RESEARCH                2392.5                     3300
ACCOUNTING     3208.3333333333          5500
SQL select * from table(dbms_xplan.display_cursor(null,null,TYPICAL -BYTES -COST));
  (在ORACLE 10g Express版别上运转不出下列作用,拜见附件检查实践运转成果) PLAN_TABLE_OUTPUT

SQL_ID  gahr597f78j0d, child number 0
-
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc
 (page 180)
Plan hash value: 3078011448

Id  | Operation                                                             | Name           | Rows   |   TQ       |IN-OUT  |  PQ Distrib     |

0 | SELECT STATEMENT                                           |                      |             |               |              |                       |
|   1 |    PX COORDINATOR                                           |                      |             |               |              |                       |
|   2 |       PX SEND QC (ORDER)                                  | :TQ10004     |     4      | Q1,04    | P- S     | QC (ORDER) |
|   3 |          SORT ORDER BY                                       |                      |     4      | Q1,04   | PCWP   |                        |
|   4 |             PX RECEIVE                                            |                      |     4      | Q1,04   | PCWP   |                       |
5 |                PX SEND RANGE                                 | :TQ10003    |     4      | Q1,03    | P- P     | RANGE          |
6 |                   HASH GROUP BY                               |                      |     4      | Q1,03   | PCWP    |                        |
|   7 |                      PX RECEIVE                                  |                     |    14      | Q1,03   | PCWP    |                        |
|   8 |                         PX SEND HASH                           | :TQ10002    |    14      | Q1,02   | P- P      | HASH             |
9 |                            HASH JOIN BUFFERED            |                     |    14      | Q1,02    | PCWP  |                       |
|  10 |                               PX RECEIVE                          |                    |     4        | Q1,02   | PCWP   |                       |
|  11 |                                  PX SEND HASH                  | :TQ10000   |     4        | Q1,00   | P- P     | HASH             |
12 |                                      PX BLOCK ITERATOR     |                     |     4        | Q1,00   | PCWC  |                      |
13 |                                         TABLE ACCESS FULL  | DEPT          |     4        | Q1,00   | PCWP  |                      |
14 |                                PX RECEIVE                         |                     |    14       | Q1,02   | PCWP  |                     |
|  15 |                             PX SEND HASH                       | :TQ10001    |    14       | Q1,01   | P- P    | HASH          |
|  16 |                           PX BLOCK ITERATOR                |                      |    14       | Q1,01  | PCWC  |                     |
|* 17 |                        TABLE ACCESS FULL                  | EMP             |    14       | Q1,01  | PCWP  |                     |

Predicate Information (identified by operation id):

-
   9 - access("D"."DEPTNO"="E"."DEPTNO")
  13 - access(:Z =:Z AND :Z =:Z)
  17 - access(:Z =:Z AND :Z =:Z)

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    MySQL数据交换ITeye

    文件,内容,李四
  • 2
  • 3

    PLITeye

    参数,成果,函数
  • 4
  • 5

    ORACLE DBMSITeye

    参数,用法,检查
  • 6
  • 7

    Oracle和Informix中load、unloadITeye

    方法,状况,分隔符
  • 8
  • 9
  • 10

    一条SQL删去一切表中数据ITeye

    数据,数据库,束缚