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 :
(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.
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.
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 ";")
.
.
.
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:
Post a Comment