《Pro Oracle SQL》Chapter 2--2.4 Identical Statements

2.4 Identical Statements  同样的语句   (page56)

    In order for Oracle to determine if a statement has been previously executed, it will check the library
cache for the identical statement.  You can see what statements are currently stored in the library cache
by querying the v$sql view.  This view lists statistics on the shared SQL area and contains one row for
each child of the original SQL text entered.  Listing 2-1 shows three different executions of a query
against the employees table followed by a query against v$sql showing information about the three
queries that have been stored in the library cache.
    为了让Oracle判断一条语句是否之前执行过，它将检查库缓存中是否有同样的语句。你可以通过查询视图v$sql知道当前在库缓存中存有什么语句。 该视图列出共享SQL区域的统计信息且对于每个原始(输入)的SQL文本各占一行。列表2-1显示同一对employees表的查询的三个不同的执行，接着是对v$sql的查询，信息显示三条查询都被存入了库缓存中。
Listing 2-1. Queries Against Employees and v$sql Contents
SQL> select * from employees where department_id = 60;
 
    EMPLOYEE_ID  FIRST_NAME             LAST_NAME                 EMAIL ...
---------------   -------------------- ------------------------- -----------  ...
            103        Alexander                 Hunold                     AHUNOLD      ...
            104        Bruce                      Ernst                       BERNST       ...
            105        David                      Austin                      DAUSTIN      ...
            106        Valli                        Pataballa                  VPATABAL     ...
            107        Diana                      Lorentz                    DLORENTZ     ...
            
SQL> SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
 
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL        ...
--------------- -------------------- ------------------------- -----------  ...
            103      Alexander                Hunold                     AHUNOLD      ...
            104      Bruce                     Ernst                       BERNST       ...
            105      David                     Austin                      DAUSTIN      ...
            106      Valli                       Pataballa                  VPATABAL     ...
            107      Diana                     Lorentz                    DLORENTZ     ...

SQL> select /* a_comment */ * from employees where department_id = 60;
 
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL        ...
--------------- -------------------- --------------------- -----------  ...
            103        Alexander              Hunold                    AHUNOLD      ...
            104        Bruce                   Ernst                      BERNST       ...
            105        David                   Austin                     DAUSTIN      ...
            106        Valli                     Pataballa                 VPATABAL     ...
            107        Diana                   Lorentz                   DLORENTZ     ...
            
 
SQL> select sql_text, sql_id, child_number, hash_value, address, executions 
 2   from v$sql where upper(sql_text) like '%EMPLOYEES%';

SQL_TEXT                         SQL_ID             CHILD_NUMBER      HASH_VALUE     ADDRESS  EXECUTIONS
---------------------------   -------------   ------------        ----------        --------   ----------
select * from employees        0svc967bxf4yu       0                3621196762          67197BC4          1
 where department_id = 60                              
SELECT * FROM EMPLOYEES     cq7t1xq95bpm8    0                2455098984          671A3034          1
 WHERE DEPARTMENT_ID = 60                              
select /* a_comment */ *      2dkt13j0cyjzq        0                1087326198          671A2E18          1
  from employees  
 where department_id = 60
 
    Although all three statements return the exact same result, Oracle considers them to be different. 
This is because when a statement is executed, Oracle first converts the string to a hash value.  That hash value is used as the key for that statement when it is stored in the library cache.  As other statements are executed, their hash values are compared to the existing hash values to find a match.
     尽管所有的三条语句返回完全一样的结果，Oracle认为他们是不同的。这是因为当一条语句执行时，Oracle首先转换字符串成哈希值。哈希值作为那条存入库缓存语句的键。 当其他语句执行时，他们的哈希值用来比较存在的哈希值，来查找匹配。

    So, why would these three statements produce different hash values, even though they return the
same result?  It’s because the statements are not exactly identical.  Lower case text is different from
upper case text.  Adding a comment into the statement makes it different from the statements that don’t
have a comment.  Any differences will cause a different hash value for the statement and cause Oracle to
hard parse the statement.

    如此，为什么这三条语句产生了不同的哈希值，即使他们返回相同的结果呢？因为这些语句实际上不是完全的相同。小写文本是不同于大写文本的。在语句中加了注释也有别于语句中没有注释的。任何区别都将产生一个不同的语句哈希值且让Oracle去硬解析这条语句。
    This is why using bind variables instead literals in your SQL statements is so important.  When you
use a bind variable, Oracle will be able to share the statement even as you change the values of the bind
variables , as shown in Listing 2-2.
    这就是为什么在你的SQL语句中使用绑定变量替代文字是如此重要了。当你使用绑定变量，Oracle将能共享语句即使你改变了绑定变量的值 ，如列表2-2所示。
    Listing 2-2. The Effect of Using Bind Variables on Parsing     使用绑定变量在解析上的效果
SQL> variable v_dept number
SQL> exec :v_dept := 10
SQL> select * from employees where department_id = :v_dept;
 
    EMPLOYEE_ID         FIRST_NAME            LAST_NAME                 EMAIL       ...
    ---------------       --------------------    -------------------------      ----------- ...
            200               Jennifer                      Whalen                        JWHALEN     ...
 
1 row selected.
 
SQL> exec :v_dept := 20
 
PL/SQL procedure successfully completed.

SQL> select * from employees where department_id = :v_dept;
 
    EMPLOYEE_ID      FIRST_NAME           LAST_NAME                 EMAIL       ...
 ---------------      --------------------     -------------------------      ----------- ...
            201                 Michael                       Hartstein                     MHARTSTE    ...
            202                 Pat                             Fay                             PFAY        ...
 
2 rows selected.
 
SQL> exec :v_dept := 30
 
PL/SQL procedure successfully completed.
 
SQL> select * from employees where department_id = :v_dept;
    EMPLOYEE_ID        FIRST_NAME           LAST_NAME                 EMAIL       ...
     ---------------             --------------------         -------------------------      ----------- ...
            114                   Den                          Raphaely                      DRAPHEAL    ...
            115                   Alexander                  Khoo                            AKHOO       ...
            116                   Shelli                         Baida                           SBAIDA      ...
            117                   Sigal                         Tobias                          STOBIAS     ...
            118                   Guy                           Himuro                         GHIMURO     ...
            119                   Karen                        Colmenares                  KCOLMENA    ...
 
6 rows selected.
 
SQL> select sql_text, sql_id, child_number, hash_value, address, executions 
 2   from v$sql where sql_text like '%v_dept';
 
SQL_TEXT            SQL_ID       CHILD_NUMBER   HASH_VALUE     ADDRESS     EXECUTIONS
-------------------------------  -------------   ------------   -----------  --------    ----------
select * from employees     72k66s55jqk1j     0     1260079153     6726254C     3
 where department_id = :v_dept 
 
1 row selected.
 
    Notice how there is only one statement stored in the library cache with three executions.  If I had
executed the queries using the literal values (10, 20, 30), there would have been three different
statements.  Always keep this in mind and try to write SQL that takes advantage of bind variables and
uses exactly the same SQL.  The less hard parsing that is required will mean your applications will
perform better and be more scalable.
    注意三次运行如何却只有一条语句存于库缓存中。如果我使用文本值(10,20,30)执行查询,将生成3条不同的语句。牢记写SQL时尽量使用绑定变量且使用完全相同的SQL。所需的硬解析越少将意味着你的应用将性能更好且越可扩展。
    One last mechanism that is important to understand is something called a latch.  A latch is a type of
lock that Oracle must acquire in order to read information stored in the library cache as well as other
memory structures.  Latches protect the library cache from becoming corrupted by concurrent
modifications by two sessions or by one session trying to read information that is being modified by
another one.  Prior to reading any information from the library cache, Oracle will acquire a latch that will
then cause all other sessions to have to wait until that latch is released before they can acquire the latch
and do the work they need to complete.  
    最后还有被称之为“闩”的机制需要重点理解。闩是一种锁，Oracle必须获取它才能读取库缓存以及其他内存结构中的信息。闩防止库缓存被两个进程并发修改占据或者一个进程在修改某信息的时候另一进程试图读取它。在从库缓存中读取任何信息之前，Oracle将获取闩，从而使得其他进程在他们得到闩之前必须等待闩释放，然后完成他们的工作。
    Latches, unlike typical locks, are not queued.  In other words, if Oracle attempts to acquire a latch
on the library cache in order to check to see if the statement you are executing already exists, it will
check to see if the latch is available.  If the latch is available, it will acquire the latch, do the work it needs to, then release the latch.  However, if the latch is already in use, Oracle will do something called
spinning.  Think of spinning like a kid in the backseat of a car that asks “Are we there yet?” over and over
and over.  Oracle will basically iterate in a loop and continue to check to see if the latch is available. 
During this time, Oracle is actively using CPU to do these checks, but your query is actually “on hold”
and not really doing anything until the latch can be acquired.  
    闩，不像典型的锁，是没有队列的。 换句话说，如果Oracle企图获取库缓存中的一个闩，为了检查看是否你所执行的语句是否已经存在，它将先检查闩是否有效。如果闩有效，它将获取闩，做它要做的工作，然后释放它。然而如果闩已经在使用中，Oracle将做被称之为“自旋”的动作。想象自旋就像一个坐在一辆汽车后座上的小孩不停的一遍又一遍的问“我们到了么？”。Oracle根本上就是在一个循环中不断的迭代检查看闩是否有效了。这期间，Oracle积极的使用CPU做这些检查，但是你的查询实际上hold住了(冻结了)，真正没有做任何事情，直到闩获取了。
    If the latch is not acquired after spinning for a while (Oracle will spin up to the number of times
indicated by the _spin_count hidden parameter, which is set to 2000 by default), then the request will be
halted temporarily and your session will have to get in line behind other sessions that need to use the
CPU. It must wait its turn to use the CPU again in order to check to see if the latch is available.  This
iterative process will continue until the latch can be acquired.  You don’t just get in line and wait on the
latch to become available, so it’s entirely possible that another session can acquire the latch while your
session is waiting in line to get back on the CPU to check the latch again.  As you can imagine, this could
be quite time-consuming if many sessions all need to acquire the latch concurrently.
     如果闩在自旋一会后还没有获取(Oracle将旋转到一次数上限，由隐式参数_spin_count设定，默认值2000)，请求将被临时的挂起且你的进程将被排到其他需要使用CPU的进程的后面。必须等到轮到他再次使用CPU时检查看是否闩是有效的了。这个迭代过程将继续下去直到闩被获取。你并不是仅按次序等待闩变得有效，因为完全有可能其它的进程先获得闩而当你的进程在排队等待重新获得CPU来检查闩(是否有效)时。你可以想象，如果很多进程并发的需要获得闩这将是非常耗时的。
    The main thing to remember is that latches are serialization devices.  The more frequently Oracle
needs to acquire a latch, the more likely it is that contention will occur, and the longer you’ll have to
wait.  The effects on performance and scalability can be dramatic.  So, writing your code in such a way as to require fewer latches (i.e. less hard parsing) is critical.
    需要记住的要点是闩是序列化装置。Oracle越是频繁的获取一个闩，越可能发生竞争，你就将必须等的越久。对性能和扩展性的效果的影响是巨大的。如此以来，你写代码方式须获取较少的闩(例如：较少的硬解析)是至关重要的。