Oracle 11g Direct IO Auto Tune

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2012-01-04 05:25:31

Share to 

Generally, Direct IO faster than Read into Cache because it does not require to allocate memory. In 11g, Oracle intruduced a new feature --- Direct IO Auto Tuning --- to improve performance of reading/writing large size objects. It will determine whether to read data blocks directly from disk bypassing buffer cache or not, basing on the size of object and size of buffer cache. To demostrate how it works, we retrieve the principle parameters relating to this feature. SQL代码 (11.2.0.1)   HELLODBA.COM>select name, value from all_parameters where name in ('_very_large_object_threshold', '_small_table_threshold', '_db_block_buffers','_serial_direct_read');      NAME                                               VALUE   -------------------------------------------------- ------------------------------   _db_block_buffers                                  10912   _small_table_threshold                             218   _very_large_object_threshold                       500   _serial_direct_read                                FALSE   And then, create a testing table as below, SQL代码 HELLODBA.COM>create table dummy_obj as  select a.* from t_objects a, t_objects b where rownum<=18000;      Table created.      HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO','DUMMY_OBJ');      PL/SQL procedure successfully completed.      HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');   Total Blocks............................256   Total Bytes.............................2097152   Unused Blocks...........................46   Unused Bytes............................376832   Last Used Ext FileId....................5   Last Used Ext BlockId...................20608   Last Used Block.........................82   Blocks under HWM........................210    *************************************************   The segment is analyzed   0% -- 25% free space blocks.............0   0% -- 25% free space bytes..............0   25% -- 50% free space blocks............0   25% -- 50% free space bytes.............0   50% -- 75% free space blocks............0   50% -- 75% free space bytes.............0   75% -- 100% free space blocks...........0   75% -- 100% free space bytes............0   Unused Blocks...........................0   Unused Bytes............................0   Total Blocks............................198   Total bytes.............................1622016      PL/SQL procedure successfully completed.      HELLODBA.COM>alter table dummy_obj move;      Table altered.      HELLODBA.COM>alter system flush buffer_cache;      System altered.   Here, we created table with 210 blocks under HWM and flushed out from buffer cache (Note: Moving table will lead the data blocks in buffer to be obsolete.). Then we perform a full table scan on it. SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 16  Process ID: 4960   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...   SQL_ID  81tza277w8r6h, child number 0   -------------------------------------   select * from dummy_obj      Plan hash value: 18537087      -------------------------------------------------------------------------------   | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |   -------------------------------------------------------------------------------   |   0 | SELECT STATEMENT  |           |       |       |    60 (100)|          |   |   1 |  TABLE ACCESS FULL| DUMMY_OBJ | 18000 |  1283K|    60   (2)| 00:00:01 |   -------------------------------------------------------------------------------      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 748   consistent gets direct: 0   consistent gets from cache: 748   db block changes: 2   db block gets: 1   db block gets direct: 0   db block gets from cache: 1   physical reads: 202   physical reads cache: 202   physical reads direct: 0   physical writes: 0   physical writes direct: 0   recursive calls: 20   session logical reads: 749   The behaviors just like in 10g --- Data blocks were read from disk into buffer cache. Now, we increase number of blocks to 219 ( just larger than _small_table_threshold), and see what will happen. SQL代码 HELLODBA.COM>insert /*+append*/into dummy_obj select a.* from t_objects a, t_objects b where rownum<=18800;      18800 rows created.      HELLODBA.COM>commit;      Commit complete.      HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');   Total Blocks............................256   Total Bytes.............................2097152   Unused Blocks...........................37   Unused Bytes............................303104   Last Used Ext FileId....................5   Last Used Ext BlockId...................20608   Last Used Block.........................91   Blocks under HWM........................219   ...      PL/SQL procedure successfully completed.   TRUNCATE will cause the data object be re-allocated. Perform full table scan on it again. SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 16  Process ID: 3260   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 780   consistent gets direct: 770   consistent gets from cache: 10   db block changes: 1   db block gets: 1   db block gets direct: 0   db block gets from cache: 1   physical reads: 212   physical reads cache: 5   physical reads direct: 207   physical writes: 0   physical writes direct: 0   recursive calls: 20   session logical reads: 781   Now, Direct IO auto tuning is effective. Data blocks were read directly from disk bypassing buffer cache, and only the segment header was read into buffer cache. But, if we perform FTS again ... SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 16  Process ID: 4104   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 775   consistent gets direct: 0   consistent gets from cache: 775   db block changes: 1   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 207   physical reads cache: 207   physical reads direct: 0   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 775   Noted that the data blocks were read into buffer cache. Oracle keep on operating such behaviors untill we increased the number of blocks to 1091 ( just larger than _small_table_threshold*5). SQL代码 HELLODBA.COM>truncate table dummy_obj;      Table truncated.      HELLODBA.COM>insert /*+append*/into dummy_obj select a.* from t_objects a, t_objects b where rownum<=96300;      96300 rows created.      HELLODBA.COM>commit;      Commit complete.      HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');   Total Blocks............................1152   Total Bytes.............................9437184   Unused Blocks...........................61   Unused Bytes............................499712   Last Used Ext FileId....................5   Last Used Ext BlockId...................22784   Last Used Block.........................67   Blocks under HWM........................1091   ...      PL/SQL procedure successfully completed.   We could note thing is changed. SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 16  Process ID: 4056   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 3955   consistent gets direct: 3951   consistent gets from cache: 4   db block changes: 0   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 1065   physical reads cache: 0   physical reads direct: 1065   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 3955   No matter how many times we performed FTS on it, data blocks were always read diretly bypassing buffer cache. And now, even if we set the "_small_table_threshold" to a large number (less than Number of Blocks), the behavior will not change. SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   Pre-executed SQLs:   alter session set "_small_table_threshold" = 1090... ok   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 142 Process ID: 5760   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 3955   consistent gets direct: 3951   consistent gets from cache: 4   db block changes: 0   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 1065   physical reads cache: 0   physical reads direct: 1065   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 3955   I suppose it because oracle has marked this segment as "NOCACHE" object in the buffer when found it's a "large" table. But wait... Let's turn off Direct IO Auto Tunning off (set event 10949 as level 1), which will force the data blocks cached. SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   Pre-executed SQLs:   alter session set events '10949 trace name context forever, level 1'... ok   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 141 Process ID: 5016   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 3957   consistent gets direct: 0   consistent gets from cache: 3957   db block changes: 1   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 1065   physical reads cache: 1065   physical reads direct: 0   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 3957   Then turn this feature on again, SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   Pre-executed SQLs:   alter session set events '10949 trace name context forever, level 0'... ok   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 136 Process ID: 5176   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 3957   consistent gets direct: 0   consistent gets from cache: 3957   db block changes: 1   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 0   physical reads cache: 0   physical reads direct: 0   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 3957   We could find that data blocks were read from buffer cache. However, the story is not end. Let's change the very large object size percent setting (_very_large_object_threshold, also introduced in 11g) just below number of blocks of our testing table (1091/10912 = 0.0999) in the session. SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   Pre-executed SQLs:   alter session set "_very_large_object_threshold" = 9... ok   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 141 Process ID: 4256   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 3955   consistent gets direct: 3951   consistent gets from cache: 4   db block changes: 0   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 1065   physical reads cache: 0   physical reads direct: 1065   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 3955   DUMMY_OBJ is now treated as "very large object" by oracle, and its data blocks will not be read into/from buffer cache. Note: Even though data blocks were read directly due to the very large object threshold, those cached data blocks were not flushed out from buffer cache. Once we change the threshold and the table become a normal size table again, oracle will still read those cached data blocks. Note(2): It will NOT be treated as "very large object" if its block number less than _small_table_threshold*5. Now, is it the end? Nope, we'd like to involve another feature --- Serial Direct Read (who is not new). We reduce the size of testing to a very small figure, SQL代码 HELLODBA.COM>drop table dummy_obj;      Table dropped.      HELLODBA.COM>create table dummy_obj as  select a.* from t_objects a, t_objects b where rownum<=1000;      Table created.      HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO','DUMMY_OBJ');      PL/SQL procedure successfully completed.      HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO','DUMMY_OBJ');      PL/SQL procedure successfully completed.      HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');   Total Blocks............................16   Total Bytes.............................131072   Unused Blocks...........................2   Unused Bytes............................16384   Last Used Ext FileId....................5   Last Used Ext BlockId...................20280   Last Used Block.........................6   Blocks under HWM........................14    *************************************************   The segment is analyzed   0% -- 25% free space blocks.............0   0% -- 25% free space bytes..............0   25% -- 50% free space blocks............0   25% -- 50% free space bytes.............0   50% -- 75% free space blocks............0   50% -- 75% free space bytes.............0   75% -- 100% free space blocks...........0   75% -- 100% free space bytes............0   Unused Blocks...........................0   Unused Bytes............................0   Total Blocks............................11   Total bytes.............................90112      PL/SQL procedure successfully completed.   Then turn on this feature in the session of FTS, SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   Pre-executed SQLs:   alter session set "_serial_direct_read" = true... ok   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 136 Process ID: 2152   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 44   consistent gets direct: 41   consistent gets from cache: 3   db block changes: 0   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 11   physical reads cache: 0   physical reads direct: 11   physical writes: 0   physical writes direct: 0   recursive calls: 1   session logical reads: 44   What happed? No matter how many blocks in the table, they were read directly where performing full table scan on it. And this feature has higher priority than Direct IO Auto Tune. Now, let's terminal our testing and recovery environment of the session (turn Serial Direct Read off). SQL代码 D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl   tnsname (default is orcl):   user name (default is demo):   password:   Array size (Default is 32):   Execution plan format (Default is TYPICAL):   Pre-executed SQLs:   alter session set "_serial_direct_read" = false... ok   SQL to be executed (without ;):   select * from dummy_obj      Seesion ID: 140 Process ID: 2792   SQL Prepared. Process paused, press ENTER to continue...   SQL is executing ...   SQL Executed. Process Paused, press ENTER to continue...      statistics   -----------------------------------------   consistent changes: 0   consistent gets: 44   consistent gets direct: 41   consistent gets from cache: 3   db block changes: 0   db block gets: 0   db block gets direct: 0   db block gets from cache: 0   physical reads: 11   physical reads cache: 0   physical reads direct: 11   physical writes: 0   physical writes direct: 0   recursive calls: 0   session logical reads: 44   Oops, data blocks can not be cached any longer. Perhaps the segment has been marked as special type, which will cause oracle read data blocks directly. And once we flush the segment out from buffer cache, the behavior became normal. To summary this feature, I write the procedure in pseudo-code. SQL代码 Read segment header;   Get #blocks under high water mark;   IF (Serial Direct Read is on)     read data blocks from disk directly;   ELSE IF (#blocks <= Small Table Threshold) THEN     IF (data blocks are cached) THEN       read from cache;     ELSE       read data blocks from disk into cache;     END IF   ELSE IF (#blocks > Small Table Threshold AND #blocks <= Small Table Threshold * 5) THEN     IF segment header is not cached THEN       IF (data blocks are cached) THEN         read from cache;       ELSE         read data blocks from disk directly       END IF     ELSE        read data blocks from disk into cache     END IF   ELSE     IF (data blocks are not cached AND #blocks <= Very Large Object Threshold/100 * Number of Buffer Blocks) THEN       read from cache;     ELSE       read data blocks from disk directly;     END IF   END IF   --- Fuyuncat ---