《Pro Oracle SQL》-Chapter 六 SQL Execution Plans -6.2 Execution Plans-之一

Execution Plans?? 执行计划(page 166)
???? The actual execution plan for a SQL statement is produced when a statement is executed.? After the statement is hard parsed, the plan that is chosen is stored in the library cache for later reuse.? The plan operations can be viewed by querying V$SQL_PLAN.? V$SQL_PLAN has basically the same definition as the PLAN_TABLE except that it has several columns that contain the information on how to identify and find the statement in the library cache.? These additional columns are: ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS, and CHILD_NUMBER.? You can find any SQL statement using one or more of these values.??
??? SQL语句的实际执行计划是在语句执行时生成的。在语句硬解析之后，被选择的计划存储在库缓存(library cache)中以备重用。可以通过V$SQK_PLAN查看计划操作。V$SQK_PLAN的基本定义于PLAN_TABLE相同，除了它有几列包含如何标示和找出在库缓存中语句的信息。这些额外的列?是：ADDRESS,HASH_VALUE,SQL_ID,PLAN_HASH_VALUE,CHILD_ADDRESS,和CHILD_NUMBER。你将会发现任何SQL语句或多或少使用了这些值。
Viewing Recently Generated SQL?? 查看最近生成的SQL
Listing 6-7 shows a query against V$SQL for recently executed SQL for the SCOTT user and the
identifying values for each column.
列表6-7展示对V$SQL的查询，获取SCOTT用户最近执行的SQL，标示了每列的值。
Listing 6-7. V$SQL Query to Get Recently Executed SQL????
SQL>select /* recentsql */ sql_id, child_number, hash_value, address, executions, sql_text
? 2??? from v$sql
? 3?? where parsing_user_id = (select user_id
? 4????????????????????????????? from all_users
? 5???????????????????????????? where username = 'SCOTT')
? 6???? and command_type in (2,3,6,7,189)
? 7???? and UPPER(sql_text) not like UPPER('%recentsql%')
? 8? /
?
SQL_ID??????????????? CHILD_NUMBER?????? HASH_VALUE ADDRESS??? EXECUTIONS?? SQL_TEXT
---------------------- ???? ----------??????? -------------------------------????? ------------? ?? --------------------
g5wp7pwtq4kwp??????????? 0??????????? 862079893 3829AE54??????????????? 1???????????? select * from emp
1gg46m60z7k2p??????????? 0???????????? 2180237397 38280AD0???????????? 1????????????? select * from bonus
4g0qfgmtb7z70???????????? 0???????????? 4071881952 38281D68???????????? 1????????????? select * from dept
?
3 rows selected.

??? After connecting as user SCOTT, you execute the three queries shown.? Then, when you run the
query against V$SQL, you can see that they are now loaded into the library cache and each has
identifiers associated with it.? The SQL_ID and CHILD_NUMBER columns contain the identifying
information that you’ll use most often to retrieve a statement’s plan and execution statistics.??
??? 以SCOTT用户连接，执行上面三个查询。然后再查V$SQL,你将看到它们已经加载到库缓存中了，且每一个都有唯一的标示关联着它们。SQL_ID和CHILD_NUMBER列包含标示信息，你将经常使用它们去检索一条语句的计划和执行的统计。

Viewing the Associated Execution Plan?? 查看相关的执行计划
??? There are several ways to view the execution plan for any SQL statement that has been previously
executed and still remains in the library cache.? The easiest way is to use the
dbms_xplan.display_cursor function.? Listing 6-8 shows how to use dbms_xplan.display_cursor to show the execution plan for the most recently executed SQL statement.?
???? 有几种方法查看之前执行的任何SQL语句(所生成的)还保留在库缓存中的执行计划。最简单的方式就是使用dbms_xplan.display_curor函数。列表6-8展现了如何使用dbms_xplan.display_cursor展示最近执行SQL语句的执行计划。
???? Listing 6-8. Using? dbms_xplan.display_cursor
SQL>select /*+ gather_plan_statistics */ empno, ename from scott.emp where ename = 'KING' ;
?
???? EMPNO ENAME
---------- ----------
????? 7839 KING
SQL>
SQL>set serveroutput off
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
?
PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID? 2dzsuync8upv0, child number 0
-------------------------------------
select empno, ename from scott.emp where ename = 'KING'
?
Plan hash value: 3956160932
?
------------------------------------------------
| Id? | Operation?????????????????????????? | Name | Starts | E-Rows | A-Rows |?? A-Time? ? ? | Buffers |
------------------------------------------------
|?? 0 | SELECT STATEMENT??????????? |?????????? |????? 1?? |?????? ? ? ? ? |????? 1 ? ?? |00:00:00.01? |?????? 8??? |
|*? 1 |??? TABLE ACCESS FULL?????? | EMP??? |????? 1?? |????? 1?????? |????? 1????? |00:00:00.01?? |?????? 8??? |
------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 1 - filter("ENAME"='KING')

???? First, note the use of the gather_plan_statistics hint in the query.? In order to capture rowsource
execution statistics for the plan, you must tell Oracle to gather this information as the statement
executes.? The rowsource execution statistics include the number of rows, number of consistent reads,
number of physical reads, number of physical writes, and the elapsed time for each operation on a row.? This information can be gathered using this hint on a statement-by-statement basis, or you can set the STATISTICS_LEVEL instance parameter to ALL.? Capturing these statistics does add some overhead to the execution of a statement and so you may not want to have it “always on.”? The hint allows you to use it when you need to—and only for the individual statements you choose.? The presence of this hint collects the information and shows it in the Starts, A-Rows, A-Time, and Buffers columns.? Listing 6-9 shows how the plan output would appear if you didn’t use the hint (or set the parameter value to ALL).
????? 首先，注意在查询中gather_plan_statistics提示的使用。为了获取计划的行源的执行统计(信息)，你必须告诉Oracle在语句执行时收集这些信息。行源的执行统计(信息)包括行数，一致读(逻辑读)数，物理读数，物理写数，和在每行上每步操作耗费的时间。通过使用这种建立在语句基础上的语句提示，或者你设定STATISTICS_LEVEL实例参数为ALL，就能收集这些信息。捕获这些统计加重了执行语句的开销，所以你可能不想要它“一直开着”。提示允许你在需要的时候才用它，且只对你所选的单个语句。(在语句中)提示的呈现将收集信息，在列Start,A-Rows,A-Time,和Buffers展现。列表6-9展现了如果你不使用提示(或者设定参数为ALL)计划将怎样输出。
Listing 6-9. Using? dbms_xplan.display_cursor without the gather_plan_statistics hint
SQL>select ename from scott.emp where ename = 'KING' ;
?
ENAME
----------
KING
?
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
?
PLAN_TABLE_OUTPUT
----------------------------------------------
SQL_ID? dgvds8td66zvk, child number 1
-------------------------------------
select ename from scott.emp where ename = 'KING'
?
Plan hash value: 3956160932
?
-------------------------------------------
| Id? | Operation????????????????????????? | Name???? | E-Rows |
-------------------------------------------
|?? 0 | SELECT STATEMENT?????????? |???? ? ? ? ? ? |???? ? ? ? ? ? |
|*? 1 |? TABLE ACCESS FULL??????? | EMP??? ??? |????? 1?????? |
-------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 1 - filter("ENAME"='KING')

Note
-----
?? - Warning: basic plan statistics not available. These are only collected when:
?????? * hint 'gather_plan_statistics' is used for the statement or
?????? * parameter 'statistics_level' is set to 'ALL', at session or system level
?
As you can see, a Note is displayed indicating that the plan statistics aren’t available and tells you
what to do to collect them.??
如你所见，显示了一提示(Note)指出计划统计信息不是有效的，还告诉你如何收集他们。