How to bypass buffer cache for full table scans.
How to reduce impact of full table scans:Full table scans (FTS) have their place, even on OLTP databases, and they are not evil when used properly. FTS are unavoidable on most DW databases. So apart from popular belief of possible increased response time and increased I/O, is there any impact on the database?
FTS on a large table can effectively pollute the buffer cache by aging out blocks in the buffer cache otherwise needed by other sessions, even though the FTS blocks are placed at LRU end. It will also have increased latch activity (cache buffer chains, cache buffer lru, etc.). It can also create many CR cloned buffers, if there are any blocks for that table already in the cache.
All these problems can be solved by setting one hidden parameter "_serial_direct_read = true" for that session or at the system level. The beauty of this parameter is that once execution plan is generated for any sql with this setting on, same behavior will be exhibited even when it is not set as long as the cursor is not re-parsed.
"_serial_direct_read = true" basically use "direct path reads" for single threaded multi block read operations like FTS. Oracle will first issue fast checkpoint at object level to write all dirty buffers of that object to the disk and perform direct path reads in to the PGA of shadow process.
This will be especially useful for batch jobs.
Oracle introduced event 10379 to do the same for rowid range scans but never really implemented it fully. Of course you can always use parallel query option to achieve the same thing with multiple threads, but you will be consuming additional resources on the system.
00:50:19 SQL> alter session set "_serial_direct_read" = true;
Session altered.
00:50:19 SQL> select avg(id) from test;
00:50:32 SQL> select event, total_waits from v$session_event
00:50:40 2 where sid = (select sid from v$mystat where rownum = 1)
00:50:40 3 and event like '%read%';
EVENT TOTAL_WAITS
------------------------------ -----------
direct path read 124
Labels: buffer cache _serial_direct_read direct path read full table scan parallel
posted by Saibabu Devabhaktuni @ 12:15 AM
8 Comments:
On 10g Enterprise Edition Release 10.2.0.3.0 I tried this unsupported parameter for both serial and parallel fts, but there were still db file sequential read and db file scattered read SGA waits for both options after parameter set.
Below is my test case, which release did you do your tests and is there any references you used for this parameter?
Thank you for sharing,
Best regards.
COLUMN parameter FORMAT a37
COLUMN description FORMAT a30 WORD_WRAPPED
COLUMN "Session VALUE" FORMAT a10
COLUMN "Instance VALUE" FORMAT a10
SET LINES 100
SELECT a.ksppinm "Parameter", a.ksppdesc "Description",
b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND
a.ksppinm LIKE '/_serial_direct_read%' escape '/' ;
drop table test_nopar purge ;
drop table test_par purge ;
create table test_nopar nologging as select * from dba_source union all
select * from dba_source union all select * from dba_source union all select * from dba_source ;
select avg(line) from test_nopar;
select event, total_waits from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';
create table test_par nologging parallel 4 as select * from test_nopar ;
select /*+ parallel(t,4) */ avg(line) from test_par t;
select event, total_waits from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';
alter session set "_serial_direct_read" = TRUE;
select avg(line) from test_nopar ;
select event, total_waits from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';
select /*+ parallel(t,4) */ avg(line) from test_par t;
select event, total_waits from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';
Hi,
Thanks for your comment. I'm also using 10203.
19:05:02 SQL> show rel
release 1002000300
19:05:02 SQL> SELECT a.ksppinm "Parameter", a.ksppdesc "Description",
19:05:02 2 b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
19:05:02 3 FROM x$ksppi a, x$ksppcv b, x$ksppsv c
19:05:02 4 WHERE a.indx = b.indx AND a.indx = c.indx AND
19:05:02 5 a.ksppinm LIKE '/_serial_direct_read%' escape '/' ;
_serial_direct_read
enable direct read in serial
FALSE
FALSE
19:05:02 SQL>
19:05:02 SQL> connect /
Connected.
19:05:02 SQL>
19:05:02 SQL> create table test_nopar nologging as select * from dba_source union all
19:05:02 2 select * from dba_source union all select * from dba_source union all select * from dba_source ;
Table created.
19:05:05 SQL>
19:05:05 SQL> select event, total_waits from v$session_event
19:05:05 2 where sid = (select sid from v$mystat where rownum = 1)
19:05:05 3 and event like '%read%';
EVENT TOTAL_WAITS
------------------------------ -----------
control file sequential read 3
db file sequential read 6
19:05:05 SQL>
19:05:05 SQL> alter session set "_serial_direct_read" = true;
Session altered.
19:05:05 SQL>
19:05:05 SQL> select avg(line) from test_nopar;
AVG(LINE)
----------
797.444857
19:05:06 SQL>
19:05:06 SQL> select event, total_waits from v$session_event
19:05:06 2 where sid = (select sid from v$mystat where rownum = 1)
19:05:06 3 and event like '%read%';
EVENT TOTAL_WAITS
------------------------------ -----------
control file sequential read 3
db file sequential read 6
direct path read 1463
Those 6 db file sequential read wait events are coming from other queries or hard parsing, etc.
Thanks,
Sai.
Thanks for sharing. It's good to know.
Even parallel query is going to consume more resource but it will be faster than this method right?
thanks a lot, i've just know that you could bypass buffer cache.
nice tips. it is always good to have dw specific dbas who understand the basics of data-warehouse and can apply their dba skills to it.
-Nilesh
dashboards
The information here is great. I will invite my friends here.
Thanks
i absolutely love your own posting type, very helpful,
don't give up as well as keep creating in all honesty , because it just well worth to follow it,
excited to see a whole lot more of your own well written articles, thanks :)
This post describes how to bypass buffer cache for full table scan. The method is not easy and I don't even find the syntax simple. This is difficult for me to understand. I just don't get the logic for the same. I need a better description for this.
Post a Comment
<< Home