Database Reference
In-Depth Information
operation, you can see how many rows were returned ( A-Rows column) and how many logical reads were performed
( Buffers column) in order to return them:
SELECT * FROM t WHERE n1 BETWEEN 6000 AND 7000 AND n2 = 19
-----------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 28 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 3 | 28 |
|* 2 | INDEX RANGE SCAN | T_N2_I | 24 | 4 |
-----------------------------------------------------------------
1 - filter(("N1">=6000 AND "N1"<=7000))
2 - access("N2"=19)
The second method is to make use of the information provided by SQL trace (Chapter 3 fully describes this technique).
The following is an excerpt of the output generated by TKPROF for the very same query as in the previous example.
Note that the number of returned rows ( Rows column) and logical reads ( cr attribute) match the previous figures:
Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID T (cr= 28 pr=0 pw=0 time=80 us)
24 INDEX RANGE SCAN T_N2_I (cr=4 pr=0 pw=0 time=25 us)(object id 39684)
Based on the rules of thumb mentioned earlier, the execution plan used as an example is acceptable. In fact, the
number of logical reads per returned row for the access path is about 9 (28/3). Let's see what a bad execution plan
looks like for the very same SQL statement. Note that it's bad because the number of logical reads per returned row for
the access path is 130 (390/3), not because it contains a full table scan!
-----------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 390 |
|* 1 | TABLE ACCESS FULL| T | 3 | 390 |
-----------------------------------------------------
1 - filter(("N2"=19 AND "N1">=6000 AND "N1"<=7000))
It's important to stress that this section is about access paths. So, you must consider the figures at the access-
path level only, not for the whole SQL statement. In fact, the figures at the SQL statement level might be misleading.
To understand what the problem might be, let's examine the following query. If only the figures at the SQL statement
level (provided by operation 0) are erroneously taken into consideration, 387 logical reads are executed to return
a single row. In other words, it would be erroneously classified as inefficient. However, if the figures of the access
operation (operation 2) are correctly taken into consideration instead, the ratio between the number of logical reads
(387) and the number of returned rows (160) classifies this access path as efficient. The problem in this case is that
operation 1 is used to apply the sum function to the rows returned by operation 2. As a result, it always returns a single
row and “hides” the access path performance figures:
SELECT sum(n1) FROM t WHERE n2 > 246
 
Search WWH ::




Custom Search