06 December, 2015

Trace Files -- 8d : Full Table Scans

In the previous two posts, I have demonstrated direct path read and db file scattered read being used for Full Table Scans.  Since 11g, Oracle uses an internal formula to determine the manner of reading the table segment for a Full Table Scan.

Here I use an SQL trace to compare the two behaviours.  

In session 1, I run these queries :

[oracle@ora11204 ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 6 17:59:12 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on
17:59:18 SQL> set timing on
17:59:20 SQL> alter session set tracefile_identifier='SESSION_1';

Session altered.

Elapsed: 00:00:00.00
17:59:31 SQL> exec dbms_session.session_trace_enable(waits=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:59:41 SQL>            
18:00:25 SQL> select count(*) from all_objects_many_list where owner = 'HEMANT';

  COUNT(*)
----------
       256

Elapsed: 00:00:45.67
18:01:28 SQL> select count(*) from all_objects_short_list where owner = 'HEMANT';

  COUNT(*)
----------
  3

Elapsed: 00:00:00.40
18:01:46 SQL> select /* second_run */ count(*) from all_objects_many_list where owner = 'HEMANT';

  COUNT(*)
----------
       256

Elapsed: 00:00:42.35
18:02:48 SQL> select /* second_run */ count(*) from all_objects_short_list where owner = 'HEMANT';

  COUNT(*)
----------
  3

Elapsed: 00:00:00.01
18:03:05 SQL> 
18:10:57 SQL> 
18:11:06 SQL> select /* third_run */ count(*) from all_objects_many_list where owner = 'HEMANT';

  COUNT(*)
----------
       256

Elapsed: 00:01:41.50
18:13:03 SQL> select /* third_run */ count(*) from all_objects_short_list where owner = 'HEMANT';

  COUNT(*)
----------
  3

Elapsed: 00:00:00.07
18:13:20 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora11204 ~]$ 

(I had another session running other queries between 18:03 and 18:11)

The Full Table Scan on "ALL_OBJECTS_MANY_LIST"  would have been executed as 'direct path read's.  The Full Table Scan on "ALL_OBJECTS_SHORT_LIST" (being a much smaller table) would have been executed as 'db file scattered read's.

Let's compare the queries in terms of the number of blocks read and how they were read.  First, the three executions on the larger table.

SQL ID: bpgst4ajh1wb2 Plan Hash: 2662061148

select count(*)
from
 all_objects_many_list where owner = 'HEMANT'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.05      44.33      97793      97798          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.06      44.34      97793      97798          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=97798 pr=97793 pw=0 time=44340391 us)
       256        256        256   TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97798 pr=97793 pw=0 time=30091048 us cost=26809 size=5256 card=657)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         3        0.67          0.76
  direct path read                             1542        1.81         43.21
  SQL*Net message from client                     2       16.96         16.96
********************************************************************************


SQL ID: bqhqn8rvr5139 Plan Hash: 2662061148

select /* second_run */ count(*)
from
 all_objects_many_list where owner = 'HEMANT'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.96      42.35      97792      97797          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.96      42.35      97792      97797          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=97797 pr=97792 pw=0 time=42353099 us)
       256        256        256   TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97797 pr=97792 pw=0 time=15066276 us cost=26809 size=5256 card=657)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                             1542        1.66         41.95
  SQL*Net message from client                     2       17.06         17.06
********************************************************************************


SQL ID: 2pwy1jv1uu23x Plan Hash: 2662061148

select /* third_run */ count(*)
from
 all_objects_many_list where owner = 'HEMANT'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.05     101.44      97792      97797          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.05     101.44      97792      97797          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=97797 pr=97792 pw=0 time=101440638 us)
       256        256        256   TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97797 pr=97792 pw=0 time=109050948 us cost=26809 size=5256 card=657)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                             1542        1.06        101.01
  SQL*Net message from client                     2       17.00         17.00
********************************************************************************

All three executions of the Full Table Scan (inspite of one query on another table being executed between the two) were done with 'direct path read' reading all the blocks from disk.   Note the 'disk ' column and "Times Waited" column.  For all three executions, CPU time is only about a second in each execution and the disk reads took more than 40seconds in the first two executions and more than 100seconds in the third execution..

Next, the three executions on the smaller table.

SQL ID: 9k7ff0kmd0nbw Plan Hash: 3180576180

select count(*)
from
 all_objects_short_list where owner = 'HEMANT'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.10          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.09        140        580          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.20        142        582          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=580 pr=140 pw=0 time=95405 us)
         3          3          3   TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=140 pw=0 time=95379 us cost=158 size=102 card=6)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                         21        0.03          0.08
  db file sequential read                         3        0.00          0.00
  SQL*Net message from client                     2       19.89         19.89
********************************************************************************


SQL ID: dgu2sxavbv996 Plan Hash: 3180576180

select /* second_run */ count(*)
from
 all_objects_short_list where owner = 'HEMANT'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        580          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        581          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=580 pr=0 pw=0 time=1173 us)
         3          3          3   TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=0 pw=0 time=1146 us cost=158 size=102 card=6)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2      496.19        496.19
********************************************************************************


SQL ID: 2xuyj80zj1xnt Plan Hash: 3180576180

select /* third_run */ count(*)
from
 all_objects_short_list where owner = 'HEMANT'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        580          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.02          0        582          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=580 pr=0 pw=0 time=1019 us)
         3          3          3   TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=0 pw=0 time=991 us cost=158 size=102 card=6)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        3.57          3.57
********************************************************************************

For these queries against the smaller (SHORT_LIST) table, it is only the first execution that shows Disk I/O  from the 'db file scattered read' waits and the "disk" column (140 blocks reported in the "disk" column and shown as physical reads indicated by the "pr=140" in the statistics).  The subsequent two executions were against the blocks in the Buffer Cache (as shown by 0 "disk" and "pr" statistics).  This is also evident in the third execution which occurred almost eleven minutes after the second execution.  Evidently, the buffers for the table were still evident in the Buffer Cache.  The session did not have to go back to disk to read the blocks.  This would be the behaviour for any other session also, as the Buffer Cache would be shared by multiple sessions.

Thus, we can see that for a table (the MANY_LIST table) that is large enough for Oracle to decide on direct path reads, there is no buffering of the blocks in the buffer cache.  Every Full Table Scan execution, whether by the same session or another session, has to re-read the table from disk (or filesystem buffers maintained by the OS) afresh.  This must be a consideration when you have a database application that frequently needs to do Full Table Scans.  Do you want every session to have to read from disk afresh ?  You would not take advantage of the buffer cache in the SGA.  You could choose to create a larger SGA and Buffer Cache.  There is another method -- the undocumented "_serial_direct_read" parameter  but that makes sense to use only if the table is smaller than the Buffer Cache.

Note : The 496.19 "SQL*Net message from client" wait event after the second query against the SHORT_LIST table is the elapsed time till the next query. The clock time showed at the SQLPlus prompt may not have been the time a query started because I might have waited some time before actually submitting the query (by hitting the ENTER query after the ";")

.
.
.


No comments: