In this post, I will discuss how direct read behaviour can be influenced in 11g by means of some undocumented parameters. But before coming to that, a few words about direct reads.
Whenever a server process is looking for some data blocks and does not find them in buffer cache, it will read them from disk into memory. Blocks can be read into :
- Buffer cache (buffered read) in which case
. Latches are obtained in the buffer cache
. Some earlier blocks in buffer cache may need to be aged out to make space for new blocks
. those blocks can be accessed by other server processes also
- PGA of the server process (direct read) in which case
. Blocks are read into PGA of the server process
. those blocks cannot be accessed by other server processes
. No latches need to be obtained and hence operation is faster
. no blocks in buffer cache may need to be aged out to make space for new blocks
Hence, direct reads may be used when
. the data blocks being read will not be needed by any other process
. we don’t want a large no. of blocks in buffer cache to be aged out
The mechanism for direct path reads has been modified in 11g as compared to 10g.
IN 10g
– all the parallel queries resulted in direct reads irrespective of the size of the data as parallel queries are normally used in data warehouse where amount of data read is large.
– all the serial queries were executed using buffered reads assuming that amount of data read is not large.
Implications:
1. Parallel queries fetching even small amount of data bypassed the buffer cache so that if any other server process needed to access the same data, he had to access the disk.
2. A serial query fetching massive amount of data could age out a lot of data from buffer cache .
In 11g we can modify this behaviour using some undocumented parameters.
1. we can set a threshold on the no. of blocks read (_small_table_threshold) so that even parallel queries fetching data less than the threshold will perform bufered I/O so that the fetched data in buffer cache can be accessed by other server processes also.
2. For serial queries we can enable direct I/O (_serial_direct_read) for all full table scans irrespective of the amount of the data read so that Full Table Scans (FTS’s) read the data into PGA and data in buffer cache is not aged out.
PRACTICAL IMPLEMENTATION
OVERVIEW:
—————
SCENARIO – I
- Set
_serial_direct_read = FALSE (Default) i.e. disable direct I/O for serial queries and
_small_table_threshold = 10 (Default value = 2% of the buffer cache)
so that
. parallel queries reading less than 10 blocks of data will perform buffered I/O
. parallel queries reading more than 10 blocks of data will perform direct I/O
. serial queries always perform buffered I/O whether FTS or otherwise
———————-
SCENARIO – II
- Set _serial_direct_read = TRUE i.e. enable direct I/O for serial queries
so that
. serial FTS perform direct I/O
. serial queries not performing FTS perform buffered I/O
——————————————————————————————
———————– SCENARIO – I ————————-
– Use following query to find initial values of
. _serial_direct_read
. _small_table_threshold
– Enter name of the parameter when prompted
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
a.ksppinm “Parameter”,
decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
c.ksppstvl “Instance”,
decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
Enter value for 1: small_table
old 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%small_table%’)
Parameter Session Instance S I D Description
————- ———– ——— ——- ——-
_small_table_threshold 59 lower threshold level of table size for direct reads
SQL> /
old 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%_serial_direct%’)
Parameter Session Instance S I D Description
——————— ———- ——————- ————— ——————
_serial_direct_read FALSE enable direct read in serial
-– SET PARAMETERS
– SET _serial_direct_read = FALSE(DEFAULT) i.e. disable direct I/O for serial queries and
– Set _small_table_threshold = 10
SQL>CONN / AS SYSDBA
ALTER SYSTEM SET “_serial_direct_read” = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET “_small_table_threshold” = 10 SCOPE=SPFILE;
STARTUP FORCE;
– Use following query to verify that
. _serial_direct_read = FALSE
. _small_table_threshold = 10
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
a.ksppinm “Parameter”,
decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
c.ksppstvl “Instance”,
decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
Enter value for 1: small_table
old 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%small_table%’)
Parameter Session Instance S I D Description
————- ———– ——— ——- —————
_small_table_threshold 10 10 59 lower threshold level of table size for direct reads
SQL> /
old 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%_serial_direct%’)
Parameter Session Instance S I D Description
——————— ———- ——————- ————— ——————
_serial_direct_read TRUE FALSE enable direct read in serial
– CREATE TEST TABLE TEST of large size
SQL>conn / as sysdba
@?/sqlplus/admin/plustrce
grant plustrace to hr;
grant select on sh.sales to hr;
grant execute on dbms_stats to hr;
grant execute on dbms_monitor to hr;
conn hr/hr
HR>drop table test purge;
create table test as select * from sh.sales;
exec dbms_stats.gather_table_stats(USER, ‘TEST’);
– Check that no. of blocks in test > 10
employees < 10
HR> select table_name, blocks from user_tables
where table_name in (‘TEST’, ‘EMPLOYEES’);
TABLE_NAME BLOCKS
—————————— ———-
EMPLOYEES 5
TEST 4513
– SET CLIENT-ID FOR THE SESSION (to read the trace files)
HR>exec dbms_session.set_identifier (‘PARALLEL’);
– ENABLE TRACING FOR THE SESSION TO TRACE WAIT EVENTS AND DIRECT READS
HR>alter session set events ‘10046 trace name context forever, level 8′;
alter session set events ‘10949 trace name context forever, level 1′;
– ISSUE PARALLEL QUERY ON BOTH THE TABLES TEST(>10 BLOCKS) AND EMPLOYEES (
SET AUTOTRACE ON TO VERIFY THAT QUERY IS EXECUTED IN PARALLEL
HR>set autotrace traceonly
select /*+ parallel(2) */ * from employees;
select /*+ parallel(2) */ * from test;
–ISSUE SERIAL FTS ON BOTH THE TABLES
SERIAL non FTS (INDEXED READ) on EMPLOYEES
HR>select * from employees;
select * from test;
select * from employees where employee_id=100;
–CHECK THE WAIT EVENTS FOR ABOVE STATEMENTS IN THE TRACE FILE
$cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
trcsess output=parallel.out clientid=’PARALLEL’ *.trc
tkprof parallel.out parallel.txt sys=no
vi parallel.txt
– PARALLEL QUERY ON EMPLOYEES DOES NOT RESULT IN DIRECT READS AS BLOCKS < _SMALL_TABLE_THRESHOLD(10)
select /*+ parallel(2) */ * from employees
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
PX Deq: Execution Msg 1 0.01 0.01
Disk file operations I/O 2 0.00 0.00
db file sequential read 6 0.04 0.04
os thread startup 2 0.06 0.12
PX Deq: Join ACK 2 0.00 0.01
PX Deq: Parse Reply 2 0.01 0.01
SQL*Net message to client 9 0.00 0.00
PX Deq: Execute Reply 8 0.04 0.05
SQL*Net message from client 9 0.00 0.00
PX Deq: Signal ACK RSG 1 0.00 0.00
PX Deq: Signal ACK EXT 4 0.01 0.01
PX qref latch 1 0.00 0.00
PX Deq: Slave Session Stats 3 0.00 0.00
enq: PS – contention 1 0.00 0.00
***************************************************************
- PARALLEL QUERY ON test RESULTs IN DIRECT READS AS BLOCKS > _SMALL_TABLE_THRESHOLD(10)
select /*+ parallel(2) */ * from test
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
PX Deq: Join ACK 2 0.04 0.05
PX Deq: Parse Reply 2 0.00 0.01
SQL*Net message to client 61258 0.00 0.21
PX Deq: Execute Reply 1021 0.01 1.36
SQL*Net message from client 61258 0.78 27.58
Disk file operations I/O 1 0.00 0.00
direct path read 1143 0.00 0.07
PX Deq Credit: send blkd 1104 0.92 38.77
PX qref latch 1115 1.36 56.25
PX Deq Credit: need buffer 15 0.15 0.49
PX Deq: Execution Msg 26 0.10 0.73
asynch descriptor resize 36 0.00 0.00
********************************************************************************
- ALL THE SERIAL QUERIES RESULT IN BUFFERED READS (WAIT EVENT ON DIRECT READS IS NOT THERE)
AS (_SERIAL_DIRECT_READ=FALSE)
select *
from
employees
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 9 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 1 0.00 0.00
SQL*Net message from client 9 0.02 0.02
********************************************************************************
select * from test
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.14 0.14
********************************************************************************
select * from employees where employee_id=100
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 2 0.00 0.00
db file sequential read 2 0.03 0.03
SQL*Net message from client 2 0.00 0.00
********************************************************************************
—————————————————————————————————–
———————– SCENARIO – II ————————-
- SET _serial_direct_read = TRUE i.e. enable direct I/O for serial queries so that
. serial FTS perform direct I/O
. serial queries not performing FTS perform buffered I/O
— SET PARAMETERS
– SET _serial_direct_read = true i.e. Enable direct I/O for serial queries
SQL>CONN / AS SYSDBA
ALTER SYSTEM SET “_serial_direct_read” = TRUE SCOPE=SPFILE;
STARTUP FORCE;
– Use following query to verify that
. _serial_direct_read = TRUE
. _small_table_threshold = 10
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
a.ksppinm “Parameter”,
decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
c.ksppstvl “Instance”,
decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
– SET CLIENT-ID FOR THE SESSION
sql>CONN hr/hr
HR>exec dbms_session.set_identifier (‘SERIAL’);
-- ENABLE TRACING FOR THE SESSION TO TRACE WAIT EVENTS(10046) AND DIRECT READS(10949)
HR>alter session set events ‘10046 trace name context forever, level 8′;
alter session set events ‘10949 trace name context forever, level 1′;
--ISSUE SERIAL FTS ON BOTH THE TABLES
SERIAL non FTS on EMPLOYEES
HR>select * from employees;
select * from test;
select * from employees where employee_id=100;
--CHECK THE WAIT EVENTS FOR ABOVE STATEMENTS IN THE TRACE FILE
$cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
trcsess output=parallel.out clientid=’SERIAL’ *.trc
tkprof parallel.out parallel.txt sys=no
vi parallel.txt
- FTS ON EMPLOYEES/TEST rESULTS IN DIRECT READS (WAIT EVENT ON DIRECT READS IS THERE)AS (_SERIAL_DIRECT_READ=truE)
select * from employees
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 9 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
direct path read 2 0.00 0.00
SQL*Net message from client 9 0.00 0.00
********************************************************************************
select * from test;
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 61258 0.00 0.17
Disk file operations I/O 1 0.00 0.00
asynch descriptor resize 2 0.00 0.00
direct path read 1129 0.00 0.07
SQL*Net message from client 61258 0.14 17.70
********************************************************************************
- non FTS on EMPLOYES DOES NOT RESULT IN DIRECT READS (WAIT EVENT ON DIRECT READS IS NOT THERE)
select * from employees where employee_id=101
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 2 0.00 0.00
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
—- CLEANUP —-
SYS>alter system reset _serial_direct_read scope=spfile;
alter system reset _small_table_threshold scope=spfile;
drop table hr.test purge;
References:
—————————————————————————————————–
Related links :
——————–
Hi Maam,
Thanks for this post now i know how to trace parallel query session.
Can you please help me in understanding
1)which one is faster direct path read or serial read
2)Sometime we see direct path read/write temp that means session is reading or writing to temp as sort area in PGA is small to accommodate
So my question is suppose i have to sort 5mb of data but my pga sort area size if 2mb now it has to divide in set in 2-2-1 mb now how Oracle works here whether it read all 5mb data from datafile to tempfiles first and then take 2 mb of data sort it and send to client and then take other 2 mb.
Thanks
Jamsher
Hi Jamsher,
Pls read my posts on Tuning PGA
Regards
Anju
Hi Maam,
Sure i will read thanks for reply back.
Thanks & Regards
Jamsher