| _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.
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>
--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:
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 |
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.
|
|
|
|
|
|
| Re: _serial_direct_read [message #580202 is a reply to message #580194] |
Thu, 21 March 2013 05:46 |
|
|
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 |
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.
|
|
|
|
|
|
| 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: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> |
|
|
|
|
|
|
|
| Re: _serial_direct_read [message #580235 is a reply to message #580234] |
Thu, 21 March 2013 13:04 |
|
|
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 |
|
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
