Home » RDBMS Server » Server Administration » _serial_direct_read
_serial_direct_read [message #580125] Wed, 20 March 2013 10:50 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hi, people - can anyone help me out with a quick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only 11.2.0.3 available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.

Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my 11.2.0.3. Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.
orcl>
orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';

KSPPSTVL
--------------------------------------------------------------------------------
auto

orcl>
orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
*
ERROR at line 1:
ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be
from among false, true, never, auto, always


orcl>
orcl>
Thankyou for any help.

--update: added 11.1.x.x to my wishlist

[Updated on: Wed, 20 March 2013 10:57]

Report message to a moderator

Re: _serial_direct_read [message #580131 is a reply to message #580125] Wed, 20 March 2013 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unfortunately 10.2 does not display the valid values:
SYS> @v

Version Oracle : 10.2.0.4.0

SYS> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
                                       *
ERROR at line 1:
ORA-00922: missing or invalid option

And x$ table does not contain them.

same thing for 11.2.0.1 (at least on Windows):
SYS> @v

Version Oracle : 11.2.0.1.0

SYS> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
                                       *
ERROR at line 1:
ORA-00922: missing or invalid option

Edit: Forgot to mention that both parameter values are null on the databases (so default value is null).

Regards
Michel

[Updated on: Wed, 20 March 2013 12:08]

Report message to a moderator

Re: _serial_direct_read [message #580194 is a reply to message #580131] Thu, 21 March 2013 03:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. I remember guessing values a few years ago, and finding that true and false were the only acceptable 10.2 values. I didn't know about null! I would think that is equivalent to false, I don't remember ever seeing a serial direct read in 10g by default.

Null as a default for 11.2.0.1 is interesting, and implies that the default behaviour may change hugely through the 11.2 patchsets. I wonder what it means in that release?

I don't suppose anyone can check 11.1.x or 11.2.0.2 for me?

Thank you again for your time.
Re: _serial_direct_read [message #580198 is a reply to message #580194] Thu, 21 March 2013 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it is Windows specific, maybe this parameter is not supported on this platform and so the NULL.

Regards
Michel
Re: _serial_direct_read [message #580202 is a reply to message #580194] Thu, 21 March 2013 05:46 Go to previous messageGo to next message
xtender
Messages: 34
Registered: January 2011
Location: Russia
Member

Before 11.2.0.2 _serial_direct_read parameter was a boolean, and start from 11.2.0.2 it is a "string".
In 11.2.0.1 default value was = false, since 11.2.0.2 - auto.
SQL> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
SQL>
SQL> @xparam_ ser%dire

NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_serial_direct_read                      auto         TRUE         string       enable direct read in serial

SQL>
SQL> ho cat xparam_.sql
col name        format a40
col value       format a12
col deflt       format a12
col type        format a12
col description format a60

select
        a.ksppinm name
       ,b.ksppstvl value
       ,b.ksppstdf deflt
       ,decode
                (a.ksppity
                ,1,'boolean'
                ,2,'string'
                ,3,'number'
                ,4,'file'
                ,a.ksppity) type
       ,a.ksppdesc description
from
       sys.x$ksppi a
      ,sys.x$ksppcv b
where
        a.indx = b.indx
and a.ksppinm like '%&1%' escape '\'
order by name
/
Re: _serial_direct_read [message #580227 is a reply to message #580202] Thu, 21 March 2013 10:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you the replies. Just for completeness:

The parameter is boolean up to 11.2.0.1, string subsequently. The default is false (or null, which I believe would be the same) up to 11.2.0.1. 11.2.0.2 and later default to auto.

No-one asked why I want to know this! It is because I have seen changes in performance by setting this parameter various ways, for a session or for an instance. The auto behaviour has sometimes given erratic performance. The changes can be for better or for worse, for both the SQL that is doing the scan and also the effect on other sessions. In summary, if you scan a table once, direct read is usually going to be much faster than indirect. But since the segment has to be checkpointed first, the scan may cause a lot DBWn activity - in all instances. If you scan it many times, then direct read is not so good. My investigation is related to the fact the parallel scans can now be indirect, if you set parallel_degree_policy=auto.

To conclude, both serial and parallel scans can now be direct or indirect, and the auto bahaviour for either may have some unexpected side effects.
Re: _serial_direct_read [message #580230 is a reply to message #580227] Thu, 21 March 2013 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the information.
Do you know if in this case direct reads are still recorded in "table scans (direct read)" or put in another statistic?

Regards
Michel
Re: _serial_direct_read [message #580232 is a reply to message #580230] Thu, 21 March 2013 12:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Looks like the sanme counter:
orcl> select value from v$mystat where statistic#=
  2  (select statistic# from v$statname where name='table scans (direct read)');

     VALUE
----------
         0

orcl> alter session set "_serial_direct_read"=always;

Session altered.

orcl> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

orcl> select value from v$mystat where statistic#=
  2  (select statistic# from v$statname where name='table scans (direct read)');

     VALUE
----------
         1

orcl> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

orcl>
(be sure to flush the shared_pool between tests with different settings)
Re: _serial_direct_read [message #580233 is a reply to message #580232] Thu, 21 March 2013 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks again.

Michel
Re: _serial_direct_read [message #580234 is a reply to message #580227] Thu, 21 March 2013 12:56 Go to previous messageGo to next message
xtender
Messages: 34
Registered: January 2011
Location: Russia
Member

John,

11.2.0.1 adds adaptive direct path reads, it based on number of blocks in segment which stored in segment header.
It controls by parameters: _adaptive_direct_read and _small_table_threshold

But since 11.2.0.3 number of blocks can be taken from statistics. This behavior enables parameter - _direct_read_decision_statistics_driven.

More info youcan get here:
http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decisio n_statistics_driven/
http://enkitec.tv/2012/05/19/oracle-full-table-scans-direct-path-reads-object-level-checkpoints-ora-8103s/

Also as answer to question by Timur Akhmadeev - "how to force/disable direct path reads on query level", i recently wrote example with hints INDEX_STATS/TABLE_STATS:
http://orasql.org/2013/03/18/example-of-controlling-direct-path-reads-decision-through-sql-profile-hints/

Regards,
Sayan Malakshinov
Re: _serial_direct_read [message #580235 is a reply to message #580234] Thu, 21 March 2013 13:04 Go to previous messageGo to next message
xtender
Messages: 34
Registered: January 2011
Location: Russia
Member

In short:
in 11.2.0.1-11.2.0.2 direct path reads will be on segments with number of block more than _small_table_threshold * 5.
In 11.2.0.3:
1) if number of blocks < _small_table_threshold * 5 - without direct path reads
2) if number of blocks > _very_large_object_threshold(in percents)* _db_block_buffers - always with direct path reads
3) between "_small_table_threshold * 5 - without direct path reads" and "_very_large_object_threshold(in percents)* _db_block_buffers - always with direct path reads" - it depends on number object blocks in buffer cache and buffer cache state
Re: _serial_direct_read [message #637811 is a reply to message #580235] Mon, 25 May 2015 16:40 Go to previous message
xtender
Messages: 34
Registered: January 2011
Location: Russia
Member

I have fixed mistake: _small_table_threshold without multiplication
xtender wrote on Thu, 21 March 2013 22:04

>=11.2.0.3:
1) if number of [ blocks < _small_table_threshold ] - without direct path reads
2) if number of [ blocks > (_very_large_object_threshold/100) * _db_block_buffers ] - always with direct path reads
3) between "_small_table_threshold" and "(_very_large_object_threshold/100) * _db_block_buffers" - it depends on number of object blocks in buffer cache and buffer cache state(fullness and occupancy)


_very_large_object_threshold - percentage of _db_block_buffers
Previous Topic: Oracle Sizing
Next Topic: File system recommendations for database
Goto Forum:
  


Current Time: Thu Mar 28 19:06:01 CDT 2024