OraFAQ Forum: Server Administration » _serial_direct

Home » RDBMS Server » Server Administration » _serial_direct_read

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

_serial_direct_read [message #580125]

Wed, 20 March 2013 10:50

John Watson
Messages: 8966
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.

Code: [Select all] [Show/ hide]

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Unfortunately 10.2 does not display the valid values:

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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

John Watson
Messages: 8966
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.

Report message to a moderator

Re: _serial_direct_read [message #580198 is a reply to message #580194]

Thu, 21 March 2013 04:25

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe it is Windows specific, maybe this parameter is not supported on this platform and so the NULL.

Regards
Michel

Report message to a moderator

Re: _serial_direct_read [message #580202 is a reply to message #580194]

Thu, 21 March 2013 05:46

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.

Code: [Select all] [Show/ hide]

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
/

Report message to a moderator

Re: _serial_direct_read [message #580227 is a reply to message #580202]

Thu, 21 March 2013 10:47

John Watson
Messages: 8966
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.

Report message to a moderator

Re: _serial_direct_read [message #580230 is a reply to message #580227]

Thu, 21 March 2013 11:40

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: _serial_direct_read [message #580232 is a reply to message #580230]

Thu, 21 March 2013 12:40

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

Looks like the sanme counter:

Code: [Select all] [Show/ hide]

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)

Report message to a moderator

Re: _serial_direct_read [message #580233 is a reply to message #580232]

Thu, 21 March 2013 12:49

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks again.

Michel

Report message to a moderator

Re: _serial_direct_read [message #580234 is a reply to message #580227]

Thu, 21 March 2013 12:56

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

Report message to a moderator

Re: _serial_direct_read [message #580235 is a reply to message #580234]

Thu, 21 March 2013 13:04

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

Report message to a moderator

Re: _serial_direct_read [message #637811 is a reply to message #580235]

Mon, 25 May 2015 16:40

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

Report message to a moderator

Previous Topic:	Oracle Sizing
Next Topic:	File system recommendations for database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 08 12:14:21 CST 2025