Saturday, December 08, 2007

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:

9 Comments:

At December 9, 2007 at 1:17:00 PM PST, Blogger Tonguç said...

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%';

 
At December 9, 2007 at 7:11:00 PM PST, Blogger Saibabu Devabhaktuni said...

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.

 
At January 23, 2008 at 9:57:00 PM PST, Blogger Yingkuan Liu said...

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?

 
At May 4, 2008 at 12:50:00 PM PDT, Blogger phenix said...

thanks a lot, i've just know that you could bypass buffer cache.

 
At May 6, 2008 at 8:14:00 PM PDT, Blogger Nilesh Jethwa said...

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

 
At November 12, 2008 at 12:06:00 AM PST, Anonymous Beulah said...

Great work.

 
At January 25, 2010 at 7:58:00 AM PST, Anonymous Anonymous said...

The information here is great. I will invite my friends here.

Thanks

 
At March 23, 2010 at 1:35:00 PM PDT, Anonymous Anonymous said...

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 :)

 
At December 12, 2011 at 9:11:00 AM PST, Anonymous sap upgrades said...

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

Links to this post:

Create a Link

<< Home