《Pro Oracle SQL》Chapter 10.2.2 Testing Over Multiple Executions

Testing Over Multiple Executions  测试多个执行   (page 312)
    What would happen if each query were run multiple times simultaneously from several different
database sessions?  Using a modified version of Tom Kyte’s run_stats6 queries, each query was run in
twenty sessions, each session running the query twenty times.  The results seem to indicate that using
the INLINE hint may offer a performance advantage.  Of course, making such a judgment would require
testing in your own test environment, preferably while the application in question is running a normal
load.  
    如果从几个不同的数据库会话运行每个查询是多次，会发生什么？使用修改版的Tom Kyte的run_stats查询，每个查询运行于二十个会话中，每个会话运行二十次。结果似乎指出使用INLINE提示可能提供更佳的性能。当然，做出这样的判断需要测试你自己的测试环境，最好让有问题的应用运行在正常负载上。
    In this case, the tests were run both without and with a load running on the database server.  The
load consisted of 10 other sessions executing queries against another set of tables in the database. While
the runtimes did increase for both MATERIALIZED and INLINE, the ratios remained about the same, so
keeping the INLINE hint in this query seems to be a good idea.
    在本例中，测试运行于没有和带有负载运行的数据库服务器上。负载由10个其它的会话执行查询数据库中另外的表集。当运行时MATERIALIZED 和INLINE都增加了，比率保持一样，因此在本查询中保持INLINE提示似乎是个好主意。
    Listing 10-5 shows some significant differences in the statistics reported for the two tests. Total
elapsed time for 400 executions by 20 sessions using 20 database sessions required 68.4 seconds when
the MATERIALIZED hint was used.  When the INLINE hint was used, the elapsed time of 30.8 seconds was
an improvement in elapsed time of over 100%.  
    列表10-5展示两个测试统计报告中一些显著的差别。使用MATERIALIZED提示，20个会话执行400次，使用20个数据库会话需要68.4s。使用INLINE提示，消耗时间30.8s，时间性能上提升100%。
    The physical IO rates between the two tests stand in stark contrast to each other.  While the tests
using the INLINE hint performed approximately 1GB of physical IO, the test using the MATERIALIZE hint
performed nearly twice as much. That could certainly account for the few extra seconds of time.
Possibly even more telling are the values shown by the statistics gathered from v$session_event. The
queries with the MATERIALIZED hint spent 1308 seconds in wait time compared to 306 seconds for INLINE.
    两个测试间的物理IO吞吐率形成鲜明的 对 比。使用INLINE提示执行大约1GB的物理IO,使用MATERIALIZE提示执行将近(前者)2倍。当然还要考虑到一些额外的时间。可能更具说服力是统计信息从v$session_event中抓取的值。 带有MATERIALIZED提示的查询花费1308秒用于等待，相比INLINE只有 306s。
----------------------------
6 Available at www.oracle-developer.net/content/utilities/runstats.zip.  The modified version used here is available with the source code for this chapter.  It does require a UNIX or Linux environment to operate.
可从 www.oracle-developer.net/content/utilities/runstats.zip下载。这里使用的修改版可从本章的源代码中获取。它需要一个UNIX或者Linux环境运行。
-----------------------------

Listing 10-5. Run_stats Comparison
NO LOAD TEST
SQL> @test_harness_m

MATERIALIZE
68.446 secs
avg response time: 0.171115

INLINE
30.774 secs
avg response time: 0.076935
SQL> @sr
NAME                                                     MATERIALIZE                INLINE                         DIFF
---------------------------------------                 ----------------------            ----------------------         ----------------
STAT...user I/O wait time                         122,096                           8,687                           -113,409
...
STAT...physical writes direct temporary     118,000                           0                                 -118,000
tablespace
...
STAT...physical writes                              118,040                           0                                 -118,040
STAT...db block gets                               122,460                           104                              -122,356
STAT...DB time                                       0                                     147,349                       147,349
STAT...free buffer requested                    166,475                            2                                -166,473
LATCH.cache buffers lru chain                  215,377                           1                                -215,376
LATCH.object queue header operation      340,352                           27                               -340,325
STAT...file io wait time                             588,347,559                     36,628                        -588,310,931
STAT...physical read bytes                       966,656,000                    8,192                          -966,647,808
STAT...physical write bytes                      966,983,680                     0                                -966,983,680
STAT...physical read total bytes               967,491,584                    499,712                       -966,991,872
STAT...physical write total bytes              967,976,960                    400,896                        -967,576,064
STAT...cell physical IO interconnect byt   1,935,468,544                  900,608                       -1,934,567,936
es

71 rows selected.
SQL> @mse
                                                                          RUN 1                     RUN 2
                                                                          TIME                      TIME
                                                                          WAITED                  WAITED             TIME
 EVENT                                                              SECONDS               SECONDS         DIFF
--------------------------------------------------------        ----------------           ----------------        -------------------
enq: TM - contention                                           74.63                      175.72                -101.09
latch: cache buffers chains                                  .00                           38.89                 -38.89
Disk file operations I/O                                        60.97                      86.83                  -25.86
SQL*Net message to client                                 .00                          .00                      -.00
library cache: mutex X                                        .00                          .00                       .00
cursor: pin S wait on X                                       .21                           .00                       .21
SQL*Net message from client                             5.57                        4.96                      .62
buffer busy waits                                                2.24                       .00                         2.24
events in waitclass Other                                    3.90                       .01                         3.90
db file sequential read                                         8.32                      .00                         8.32
direct path write temp                                         572.13                   .00                         572.13
db file scattered read                                         579.60                   .00                         579.60
                                                                        ---------                  ---------                    ---------
sum                                                                  1307.59                 306.41                    1001.18
12 rows selected.

LOAD TEST
SQL> @test_harness_m

MATERIALIZE
310.908 secs
avg response time: 0.777270

INLINE
144.683 secs
avg response time: 0.361708

Run Stats omitted – they are very similar to NO LOAD TEST.

SQL> @mse
                                                                                    RUN 1                    RUN 2
                                                                                    TIME                      TIME
                                                                                    WAITED                 WAITED              TIME
EVENT                                                                         SECONDS              SECONDS           DIFF
--------------------------------------------------------                  -------------------        ---------------         --------------
enq: TM - contention                                                     721.66                     989.28                -267.62
latch: cache buffers chains                                            22.04                       34.70                  -12.66
SQL*Net message to client                                           .00                          .00                       -.00
SQL*Net message from client                                       .80                          .77                       .03
buffer busy waits                                                          4.61                        .00                        4.61
events in waitclass Other                                              21.05                       8.69                     12.36
db file sequential read                                                  33.71                       .00                        33.71
direct path write temp                                                  95.53                       .00                        95.53
Disk file operations I/O                                                1995.29                    814.54                  1180.76
db file scattered read                                                  1418.30                    .00                        1418.30
                                                                                 ------------------------    --------------------     ------------------
sum                                                                           4313.00                    1847.97                 2465.02

    From these tests you might feel safe using the INLINE hint in this bit of code, convinced that it will
perform well. The amount of physical IO required in the first test outweighs the memory usage and
logical IO required for the second test. If you know for sure that the size of the data sets will not grow
and that the system load will remain fairly constant, using the INLINE hint in this query is probably a
good idea. The problem, however, is that data is rarely static; often, data grows to a larger size than what was originally intended when developing a query. In that event, re-testing these queries would be in
order to see if the use of the INLINE hint is still valid.
    从这些测试你可能对在这段代码中使用INLINE提示感觉放心，确信它执行的很好。(性能比较上)在第一个测试中所需的物理IO总量压倒了第二个测试所需的内存使用和逻 辑IO。如果你确信数据集大小将不再增长且系统负载将保持相对恒定，使用本查询中的INLINE提示可能是个好主意。 然而，问题是数据很少是静态的；通 常，数据集增长到相当的规模，相比于当初开发查询时的初衷。如果是这种情况，重新测试这些查询，查看是否INLINE提示的使用还是有效的。