《Pro Oracle SQL》--Chapter 6--6.2 Execute Plans--之三

Identifying SQL Statements for Later Plan Retrieval    标示SQL语句便于之后计划检索 (page 171)
    If you want to retrieve a statement that was executed in the past, you can retrieve the SQL_ID and
CHILD_NUMBER from V$SQL as demonstrated in Listing 6-7.  To simplify finding the correct statement
identifiers, especially when I’m testing, I add a unique comment that identifies each statement I
execute.  Then, whenever I want to grab that plan from the library cache, all I have to do is query V$SQL to locate the statement text that includes the comment I used.  Listing 6-11 shows an example of this and the query I use to subsequently find the statement I want. 
    如果你想要检索一条之前执行过的语句，你可像列表6-7示例的，查询V$SQL的SQL_ID和CHILD_NUMBER列。为了容易找出正确的语句标 示，尤其在我测试时，在每条语句上加上唯一的注释。这样无论何时，我要从库缓存中取出计划，所要做的只是查询V$SQL，定位包含我所使用的注释文本的语 句。列表6-11展示了这个例子和之后我要找出想要的语句的查询。

Listing 6-11. Using a Comment to Uniquely Identify a SQL Statement   用唯一注释标示SQL语句
SQL>select /* KM-EMPTEST1 */
  2        empno, ename
  3   from emp
  4  where job = 'MANAGER' ;
      EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK
 
SQL>select sql_id, child_number, sql_text
  2  from v$sql
  3  where sql_text like '%KM-EMPTEST1%';
 
SQL_ID                     CHILD_NUMBER                       SQL_TEXT
-------------                 ------------                                    -------------------------------------------
9qu1dvthfcqsp            0                                              select /* KM-EMPTEST1 */     empno, ename  
                                                                                    from emp where job = 'MANAGER'
a7nzwn3t522mt           0                                             select sql_id, child_number, sql_text from 
                                                                                   v$sql where sql_text like '%KM-EMPTEST1%'
 
SQL>select * from table(dbms_xplan.display_cursor('9qu1dvthfcqsp',0,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  9qu1dvthfcqsp, child number 0
-------------------------------------
select /* KM-EMPTEST1 */     empno, ename  from emp where job =
'MANAGER'
 
Plan hash value: 3956160932

------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Starts | E-Rows | A-Rows |   A-Time       | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1    |              |      3      |00:00:00.01  |       8     |
|*  1 |  TABLE ACCESS FULL     | EMP    |      1    |      3       |      3      |00:00:00.01  |       8     |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("JOB"='MANAGER')

    You’ll notice that when I queried V$SQL, two statements showed up.  One was the SELECT statement I was executing to find the entry in V$SQL and one was the query I executed.  While this set of steps gets the job done, I find it easier to automate the whole process into a single script.  In that script, I find the statement I want in V$SQL by weeding out the query I’m running to find it and also by ensuring that I find the most recently executed statement that uses my identifying comment.  Listing 6-12 shows the script I use in action.
    如你所见，我查询V$SQL展现了两条语句。一条SELECT语句是我执行查询V$SQL的，一条是我之前执行的查询。既然这套步骤有效，我发现用一个脚本自动化处理整个过程更爽。在这个脚本中，我找出想要的语句，清除查询V$SQL的那条语句，确保找到最近执行的且使用注释标示的语句。列表6-12展示了这个脚本的使用过程。

Listing 6-12. Automating Retrieval of an Execution Plan for any SQL Statement
SQL>select /* KM-EMPTEST2 */
  2         empno, ename
  3    from emp
  4   where job = 'CLERK' ;
 
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7876 ADAMS
      7900 JAMES
      7934 MILLER
SQL>
SQL>get pln.sql
  1  SELECT xplan.*
  2  FROM
  3     (
  4     select max(sql_id) keep
  5            (dense_rank last order by last_active_time) sql_id
  6          , max(child_number) keep
  7            (dense_rank last order by last_active_time) child_number
  8       from v$sql
  9      where upper(sql_text) like '%&1%'
 10        and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'
 11      ) sqlinfo,
 12     table(DBMS_XPLAN.DISPLAY_CURSOR(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS
LAST')) xplan
 13* /
 
SQL>@pln KM-EMPTEST2
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------SQL_ID 
bn37qcafkwkt0, child number 0
-------------------------------------
select /* KM-EMPTEST2 */        empno, ename   from emp  where job =
'CLERK'
Plan hash value: 3956160932
 
------------------------------------------------------------------------------------
| Id  | Operation                                | Name | Starts | E-Rows | A-Rows        |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1   |            |      4     |00:00:00.01  |       8       |
|*  1 |  TABLE ACCESS FULL| EMP   |      1   |      3   |      4      |00:00:00.01  |       8        |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("JOB"='CLERK')
    This script will return the execution plan associated with the most recently executed SQL
statement that matches the pattern you enter.  As I mentioned, it is easier to find a statement if you’ve made an effort to use a comment to identify it, but it will work to find any string of matching text you enter.  However, if there are multiple statements with matching text, this script will only display the most recently executed statement matching the pattern.  If you want a different statement, you’ll have to issue a query against V$SQL such as the one in Listing 6-11 and then feed the correct SQL_ID and CHILD_NUMBER to the dbms_xplan.display_cursor call.
    这个脚本将返回，关联最近执行的SQL语句的，匹配你输入(文本)模式的执行计划。正如我所提到的，如果你精心使用一注释标识一语句，你将很容易找到它。 但是它也能找出任何匹配你输入文本串(的执行计划)。然而，如果有多个语句匹配(你输入的文本)，脚本只返回最近执行的匹配语句。如果你想要不同的语句， 你必须查询V$SQL，像列表6-11那样。然后填上正确的SQL_ID和CHILD_NUMBER调用 dbms_xplan.display_curor。