1.继续前一篇的内容,查看db_file_multiblock_read_count对全表扫描读取blocks的变化

SQL> create tablespace aluocp datafile '/disk3/oradata/oradata/ora7a/aluocp.dbf' size 100M 
  2  extent management local uniform size 256K;                                            
                                                                                           
Tablespace created.                                                                        

SQL> alter table t move tablespace aluocp;  
                                            
Table altered.                              

SQL> select file_id,block_id,blocks from dba_extents where segment_name='T';  
                                                                              
   FILE_ID   BLOCK_ID     BLOCKS                                              
---------- ---------- ----------                                              
         4          9         32                                              
         4         41         32                                              
         4         73         32                                              

SQL> show parameter read_count                                                            
                                                                                          
NAME                                 TYPE                   VALUE                         
------------------------------------ ---------------------- ------------------------------
db_file_multiblock_read_count        integer                8                             


SQL> alter session set db_file_multiblock_read_count=16; 
                                                         
Session altered.                                         

SQL> show parameter read_count                                                            
                                                                                          
NAME                                 TYPE                   VALUE                         
------------------------------------ ---------------------- ------------------------------
db_file_multiblock_read_count        integer                16                            


SQL> alter session set events '10046 trace name context forever,level 12';  
                                                                            
Session altered.                                                            

SQL> select count(*) from t;  
                              
  COUNT(*)                    
----------                    
      6279                    

SQL> alter session set events '10046 trace name context off'; 
                                                              
Session altered.                                              

SQL> ! grep scatt /disk3/oradata/admin/ora7a/udump/ora7a_ora_6128.trc 
WAIT #1: nam='db file scattered read' ela= 311 p1=3 p2=5026 p3=7      
WAIT #1: nam='db file scattered read' ela= 211 p1=3 p2=5033 p3=8      
WAIT #1: nam='db file scattered read' ela= 302 p1=3 p2=5041 p3=8      
WAIT #1: nam='db file scattered read' ela= 226 p1=3 p2=5049 p3=8      
WAIT #1: nam='db file scattered read' ela= 219 p1=3 p2=5057 p3=8      
WAIT #1: nam='db file scattered read' ela= 300 p1=3 p2=5065 p3=8      
WAIT #1: nam='db file scattered read' ela= 501 p1=3 p2=5073 p3=8      
WAIT #1: nam='db file scattered read' ela= 466 p1=3 p2=5081 p3=8      
WAIT #1: nam='db file scattered read' ela= 470 p1=3 p2=5089 p3=8      
WAIT #1: nam='db file scattered read' ela= 139 p1=3 p2=5097 p3=6      
WAIT #1: nam='db file scattered read' ela= 436 p1=4 p2=10 p3=16       
WAIT #1: nam='db file scattered read' ela= 376 p1=4 p2=26 p3=15       
WAIT #1: nam='db file scattered read' ela= 518 p1=4 p2=41 p3=16       
WAIT #1: nam='db file scattered read' ela= 305 p1=4 p2=57 p3=16       
WAIT #1: nam='db file scattered read' ela= 242 p1=4 p2=73 p3=14  

红色为db_file_multiblock_read_count=8时的结果,蓝色为db_file_multiblock_read_count=16时的结果。
注:db_file_multiblock_read_count<=128,当db_file_multiblock_read_count>32时,效果不会明显。

2.执行计划的变化

SQL> analyze table t compute statistics for table for all indexes for all indexed columns;
                                                                                          
Table analyzed.                                                                           

SQL> set autotrace traceonly explain


SQL> alter session set db_file_multiblock_read_count=16; 
                                                         
Session altered.                                         

SQL> select * from t where owner='SYSTEM';                                     
                                                                               
Execution Plan                                                                 
-------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=453 Bytes=38958)      
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=8 Card=453 Bytes=38958) 
   2    1     INDEX (RANGE SCAN) OF 'I_T' (NON-UNIQUE) (Cost=2 Card=453)       


SQL> alter session set db_file_multiblock_read_count=32;   
                                                           
Session altered.                                           

SQL> select * from t where owner='SYSTEM';                                    
                                                                              
Execution Plan                                                                
------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=453 Bytes=38958)     
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=6 Card=453 Bytes=38958)          
Trackback

no comment untill now

Add your comment now

切换到手机版