Oracle 11g Adaptive direct path read的几篇链接.

11g and direct path reads
http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html
一篇关于Adaptive direct path reads的基本介绍.

http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html

11G’s ability to do direct path reads during full table scans without utilizing PQ was covered in a number of places already (see this post by Doug Burns for example).

When direct path reads starts to happen?

It is known that somewhat reliable figure is your _small_table_threshold multiplied by 5 (mentioned by Tanel Poder on oracle-l recently). You can discover it using quick and dirty test case similar to this:

SQL> create tablespace adr_test datafile size 64m segment space management manual;

Tablespace created

SQL> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace adr_test;

Table created

SQL> create or replace function get_adr_trsh(
2 p_step in number,
3 p_start in number default 0,
4 p_stop in number default null
5 ) return number is
6 l_prd number;
7 l_blocks number:=0;
8 l_start number:=p_start;
9 begin
10 execute immediate ‘truncate table t’;
11
12 loop
13 insert /*+ append */ into t
14 select rpad(‘*’, 100, ‘*’)
15 from dual
16 connect by level <= p_step + l_start;
17 commit;
18
19 l_blocks:=l_blocks + p_step + l_start;
20 l_start:=0;
21
22 execute immediate 'alter system flush buffer_cache';
23
24 select /*+ full(t) */ count(*) into l_cnt from t;
25
26 select value into l_prd
27 from v$segment_statistics
28 where owner=user
29 and object_name='T'
30 and statistic_name='physical reads direct';
31
32 exit when (l_prd > 0 or l_blocks > nvl(p_stop, l_blocks));
33
34 end loop;
35
36 return l_blocks – p_step;
37 end;
38 /

Function created

My _small_table_threshold is:

SQL> select ksppstvl
2 from x$ksppi x, x$ksppcv y
3 where (x.indx = y.indx)
4 and ksppinm=’_small_table_threshold’;

KSPPSTVL
——————————————————————————–
314

which is about 2% of my buffer cache (128MB) so you may expect 11G switch to direct path reads once table goes beyond 1570 blocks. Let’s check it:

SQL> declare
2 l_trsh number;
3 begin
4 l_trsh:=get_adr_trsh(10, 1500, 2000);
5
6 dbms_output.put_line(l_trsh);
7 end;
8 /

1570

PL/SQL procedure successfully completed

Note that that number is somewhat “about” and you can get different results depending on stuff like using ASSM/MSSM.

What is the cached blocks threshold?

Direct path reads stops happening after certain amount of your table’s blocks are in the buffer cache already. Discovering it is fairly easy as well:

SQL> –need this so we can do irs and cache table blocks
SQL> create index i_t on t (1);

Index created

SQL> create or replace function get_cached_trsh(
2 p_start in number default 0,
3 p_step in number default 1
4 ) return number is
5 cursor l_cur is select /*+ index(t i_t) */ * from t;
6 l_v varchar2(100);
7 l_trsh number:=0;
8 l_prd number:=0;
9 l_cnt number:=0;
10 l_start number:=p_start;
11 begin
12 execute immediate ‘alter system flush buffer_cache’;
13 open l_cur;
14
15 loop
16 for i in 1 .. p_step+l_start
17 loop
18 fetch l_cur into l_v;
19 end loop;
20 l_trsh:=l_trsh+p_step+l_start;
21 l_start:=0;
22
23 select /*+ full(t) */ count(*) into l_cnt from t;
24
25 select value into l_cnt
26 from v$segment_statistics
27 where owner=user
28 and object_name=’T’
29 and statistic_name=’physical reads direct’;
30
31 exit when l_cnt=l_prd or l_cur%notfound;
32
33 l_prd:=l_cnt;
34
35 end loop;
36
37 close l_cur;
38 return l_trsh;
39 end;
40 /

Function created

Now, we can see after how many blocks 11G will stop doing direct path reads:

SQL> declare
2 l_trsh number;
3 begin
4 l_trsh:=get_cached_trsh(500, 1);
5
6 dbms_output.put_line(l_trsh);
7 end;
8 /

789

PL/SQL procedure successfully completed

Which happens to be half of the table’s blocks. I’ve repeated the above test with 256MB buffer cache and got 3140 blocks (number of blocks for direct read to start happening) and 1568 (number of cached blocks) respectively. Please note that cached blocks threshold seems to be not dependent on your buffer cache size (to a degree where it can find space of course).

What is the dirty blocks threshold?

Doing direct path reads requires segment level checkpoint which may not be something you would like to do if you have a lot of these for the sake of direct read alone.

Something we can start with:

SQL> create or replace function get_dirty_trsh(
2 p_step in number,
3 p_start in number default 0,
4 p_stop in number default null
5 ) return number is
6 l_trsh number:=0;
7 l_prd number:=0;
8 l_cnt number:=0;
9 l_start number:=p_start;
10 begin
11 execute immediate ‘alter system flush buffer_cache’;
12
13 loop
14 l_trsh:=l_trsh+p_step+l_start;
15 update t set v=v where rownum <= l_trsh;
16 commit;
17 l_start:=0;
18
19 select /*+ full(t) */ count(*) into l_cnt from t;
20
21 select value into l_cnt
22 from v$segment_statistics
23 where owner=user
24 and object_name='T'
25 and statistic_name='physical reads direct';
26
27 exit when l_cnt=l_prd or l_trsh > nvl(p_stop, l_trsh);
28
29 l_prd:=l_cnt;
30
31 end loop;
32
33 return l_trsh;
34 end;
35 /

Function created

SQL> declare
2 l_trsh number;
3 begin
4 l_trsh:=get_dirty_trsh(1, 350, 400);
5
6 dbms_output.put_line(l_trsh);
7 end;
8 /
384

PL/SQL procedure successfully completed.

Which turns out to be 1/4 of a table size.

Quick and dirty

Please note that adaptive direct path reads could (and most probably do) have much more variables to make a decision. The above test were done using ad-hoc approach to at least have an idea what could be potential factors there. Things like system statistics, tablespace block sizes, delayed blocks cleanouts, etc. has a potential to interfere over there.

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production

http://www.os2ora.com/11g-new-feature-adaptive-direct-read/
11g新特性: OLTP中的Adaptive Direct Read算法
国内Oracle Real Performance Team的一个工程师写的关于此的介绍, 细节上不如上一个.

Disabling direct path read for the serial full table scan – 11g
http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/
韩国的Dion Cho写的关于如何关闭Adaptive direct path read的文章, 比较全面.
But I don’t like this kind of one-way optimization. What I want is to enable/disable it whenever I want it – putting Oracle under my control.

So I had a research for a couple of hours and found how to disable it. Oracle 11g has introduced 10949 event to control this.
view source
print?
1 UKJA@ukja116> @oerr 10949
2 10949
3 “Disable autotune direct path read for full table scan”
4 // *Cause:
5 // *Action: Disable autotune direct path read for serial full table scan.
6 //

Here is a simple and dirty example how to use this event.

1. Create a table big enough to meet the threshold which seems to be 5 * _small_table_threshold.
view source
print?
01 UKJA@ukja116> col value new_value sth
02 UKJA@ukja116> @para small_table
03 old 9: and i.ksppinm like ‘%&1%’
04 new 9: and i.ksppinm like ‘%small_table%’
05
06 NAME VALUE IS_DEFAUL SES_MODIFI
07 —————————— ——————– ——— ———-
08 SYS_MODIFI
09 ———-
10 DESCRIPTION
11 ——————————————————————————–
12 _small_table_threshold 637 TRUE true
13 deferred
14 threshold level of table size for direct reads
15
16 Elapsed: 00:00:00.04
17 UKJA@ukja116>
18 UKJA@ukja116> create table t1(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));
19
20 Table created.
21
22 Elapsed: 00:00:00.21
23 UKJA@ukja116> insert into t1
24 2 select level, ‘x’, ‘x’, ‘x’
25 3 from dual connect by level <= 10 + 5*&sth;
26 old 3: from dual connect by level <= 10 + 5*&sth
27 new 3: from dual connect by level <= 10 + 5*637
28
29 3195 rows created.
30
31 Elapsed: 00:00:01.14
32 UKJA@ukja116> commit;
33
34 Commit complete.
35
36 Elapsed: 00:00:00.00
37 UKJA@ukja116> @gather t1
38 UKJA@ukja116> exec dbms_stats.gather_table_stats(user, ‘&1′, no_invalidate=>false);
39
40 PL/SQL procedure successfully completed.
41
42 Elapsed: 00:00:00.68

2. Now I compare the wait event(direct path read vs. db file scattered read) of the two queries – one with 10949 disabled(default) and the other with 10949 enabled.
view source
print?
01 – case#1
02 alter system flush buffer_cache;
03 alter session set events ’10046 trace name context forever, level 8′;
04 select count(*) from t1;
05
06 – case#2
07 alter system flush buffer_cache;
08 alter session set events ’10046 trace name context forever, level 8′;
09 alter session set events ’10949 trace name context forever, level 1′;
10 select count(*) from t1;

3. Do you see that 10949 enabled one behaves just like the previous version? The famous db file scattered read event!
view source
print?
01 SQL ID : 5bc0v4my7dvr5
02 select count(*)
03 from
04 t1
05
06 – Case #1
07 Elapsed times include waiting on following events:
08 Event waited on Times Max. Wait Total Waited
09 —————————————- Waited ———- ————
10 SQL*Net message to client 2 0.00 0.00
11 db file sequential read 3 0.02 0.04
12 db file scattered read 1 0.02 0.02
13 direct path read 231 0.29 1.67
14 SQL*Net message from client 2 0.03 0.03
15
16 – Case #2
17 Elapsed times include waiting on following events:
18 Event waited on Times Max. Wait Total Waited
19 —————————————- Waited ———- ————
20 SQL*Net message to client 2 0.00 0.00
21 db file scattered read 213 0.26 1.77
22 SQL*Net message from client 2 0.00 0.00

Don’t forget 10949 event when you want to disable this great feature for any reason!

Footnote1:What you should keep in mind is that this is not a CBO feature but a runtime execution engine feature. It would always show the same execution plan – TABLE ACCESS FULL. When the execution engine runs this operation, it determines how to read the table(direct path read or conventional path read) considering various factors.

Footnote2:There is “_serial_direct_read” parameter which forces the full scan on the big table to be a direct read – The opposite of 10949 event.

Impact of Direct Reads on Delayed Block Cleanouts
http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/
Christian Antognini 写的关于Adaptive Direct path Read会导致Delayed Block Cleanout 会重复cleanout的情况.

As of Oracle Database 11g, serial full table scans can use direct reads (see Metalink note 793845.1). This is basically done for improving the performance of large full table scans. In my opinion, the most important pros and cons of this feature are the following…

Pros (which are related to the fact that the buffer cache is not used):

* Reduced latch utilization
* The size of the physical I/Os does not depend on the blocks that are in the buffer cache

Cons:

* A segment checkpoint must precede the first direct read
* Repeated delayed block cleanouts

In this post, as the title suggests, I would like to focus on the last one. The reason is simple. I cannot remember having already read something about that topic.

Let’s start by showing you an example where delayed block cleanouts are performed…

* Create a test table and insert some data into it (note that the table uses 10000 database blocks):

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(500)) TABLESPACE test_8k;

SQL> INSERT INTO t
2 SELECT rownum, rpad(‘*’,500,’*')
3 FROM dual
4 CONNECT BY level <= 140000;

SQL> COMMIT;

* Generate some blocks that require delayed block cleanout:

SQL> UPDATE t SET id=id*10;

SQL> COMMIT;

* Flush the buffer cache (this is necessary because many blocks are in the buffer cache and, therefore, direct reads would not be used for reading the test table):

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

* Execute two queries on the test table and store the session statistics in a helper table:

SQL> CREATE TABLE s AS
2 SELECT 0 AS run, name, value
3 FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

COUNT(*)
———-
140000

SQL> INSERT INTO s
2 SELECT 1, name, value
3 FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

COUNT(*)
———-
140000

SQL> INSERT INTO s
2 SELECT 2, name, value
3 FROM v$statname NATURAL JOIN v$mystat;

* Show some statistics for the two executions:

SQL> SELECT *
2 FROM (
3 SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
4 FROM s
5 WHERE name IN (‘cleanouts only – consistent read gets’,
6 ‘session logical reads’,
7 ‘physical reads’,
8 ‘physical reads direct’)
9 )
10 PIVOT (sum(value) FOR run IN (1 AS RUN_1, 2 AS RUN2_2))
11 ORDER BY name;

NAME RUN_1 RUN2_2
—————————————- ———- ———-
cleanouts only – consistent read gets 9466 9399
physical reads 10112 10004
physical reads direct 10003 10003
session logical reads 19850 19538

As you can see from the execution statistics, both runs did almost the same amount of (direct) reads, logical reads and cleanouts. Especially the last is strange. In fact, usually, the cleanouts are performed only once for each block. In this case, however, the same cleanouts are performed for every execution of the query.

This is clearly due to the direct reads.

To confirm that, let’s rerun the test without using serial direct reads. For that purpose, event 10949 is enabled.

SQL> TRUNCATE TABLE s;

SQL> ALTER SESSION SET EVENTS ’10949 TRACE NAME CONTEXT FOREVER’;

SQL> INSERT INTO s
2 SELECT 0 AS run, name, value
3 FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

COUNT(*)
———-
140000

SQL> INSERT INTO s
2 SELECT 1, name, value
3 FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

COUNT(*)
———-
140000

SQL> INSERT INTO s
2 SELECT 2, name, value
3 FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT *
2 FROM (
3 SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
4 FROM s
5 WHERE name IN (‘cleanouts only – consistent read gets’,
6 ‘session logical reads’,
7 ‘physical reads’,
8 ‘physical reads direct’)
9 )
10 PIVOT (sum(value) FOR run IN (1 AS RUN_3, 2 AS RUN2_4))
11 ORDER BY name;

NAME RUN_3 RUN2_4
—————————————- ———- ———-
cleanouts only – consistent read gets 9399 0
physical reads 9919 0
physical reads direct 0 0
session logical reads 19592 10228

As you can see from the statistics, the major difference between the second and the third run is the number of direct reads. The interesting thing is to notice how the third run finally did a “regular” cleanout and, therefore, the subsequent run, the fourth one, was able to directly read the (cleaned) blocks. Because of that the number of logical reads of the fourth run was much lower.

No related posts.