Home » RDBMS Server » Performance Tuning » what's the relationship between the number of blocks and consistent gets ?
what's the relationship between the number of blocks and consistent gets ? [message #558185] Wed, 20 June 2012 01:48 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member

SQL> CREATE TABLE TEST(ID INT ,NAME VARCHAR2(10));
 
SQL> CREATE INDEX IND_IDN ON TEST(ID);
 
SQL> BEGIN
  2  FOR I IN 1 .. 1000
  3  LOOP
  4  EXECUTE IMMEDIATE 'INSERT INTO TEST VALUES('||I||',''LONION'')';
  5  END LOOP;
  6  COMMIT;
  7  END;
  8  / 
 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE);
 
SQL> SELECT DISTINCT DBMS_ROWID.rowid_block_number(ROWID) BLOCKS FROM TEST;
BLOCKS
-----------
61762
61764
61763         
 
                    >>above , there have 3 blocks in table TEST .</div></div>
 
SQL> SET AUTOTRACE TRACEONLY;                                     
 
SQL> SELECT * FROM TEST;
 
Execution Plan
----------------------------------------------------------                      
Plan hash value: 1357081020                                                     
                                                                                
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |  1000 | 10000 |     2   (0)| 00:00:01 |      
|   1 |  TABLE ACCESS FULL| TEST |  1000 | 10000 |     2   (0)| 00:00:01 |      
--------------------------------------------------------------------------      
 
 
Statistics information
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
         72  consistent gets                                    >>  there have 72  consistent gets                  
          0  physical reads                                                     
          0  redo size                                                          
      24957  bytes sent via SQL*Net to client                                   
       1111  bytes received via SQL*Net from client                             
         68  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
       1000  rows processed                                                     
 
SQL> SELECT /*+ INDEX_FFS(TEST IND_IDN)*/ * FROM TEST WHERE ID IS NOT NULL;
 
Execution Plan
----------------------------------------------------------                      
Plan hash value: 1357081020                                                     
                                                                                
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |  1000 | 10000 |     2   (0)| 00:00:01 |      
|*  1 |  TABLE ACCESS FULL| TEST |  1000 | 10000 |     2   (0)| 00:00:01 |      
--------------------------------------------------------------------------      
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - filter("ID" IS NOT NULL)                                                 
 
 
Statistics information
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
         72  consistent gets                                   >>  there have 72  consistent gets                  
          0  physical reads                                                     
          0  redo size                                                          
      17759  bytes sent via SQL*Net to client                                   
       1111  bytes received via SQL*Net from client                             
         68  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
       1000  rows processed                                                     
 
                                         
 
SQL> SELECT COUNT(*) FROM TEST;
 
Execution Plan
----------------------------------------------------------                      
Plan hash value: 1950795681                                                     
                                                                                
-------------------------------------------------------------------             
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |             
-------------------------------------------------------------------             
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |             
|   1 |  SORT AGGREGATE    |      |     1 |            |          |             
|   2 |   TABLE ACCESS FULL| TEST |  1000 |     2   (0)| 00:00:01 |             
-------------------------------------------------------------------             
 
Statistics information
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
          5  consistent gets                                      >>  there have 5  consistent gets               
          0  physical reads                                                     
          0  redo size                                                          
        408  bytes sent via SQL*Net to client                                   
        385  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     
 
                                              
 
SQL> SELECT COUNT(*) FROM TEST WHERE ID IS NOT NULL;
 
Execution Plan
----------------------------------------------------------                      
Plan hash value: 735384656                                                      
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     
|                                                                               
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                
|   0 | SELECT STATEMENT      |         |     1 |     4 |     2   (0)| 00:00:01 
|                                                                               
                                                                                
|   1 |  SORT AGGREGATE       |         |     1 |     4 |            |          
|                                                                               
                                                                                
|*  2 |   INDEX FAST FULL SCAN| IND_IDN |  1000 |  4000 |     2   (0)| 00:00:01 
|                                                                               
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                                                                                            
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - filter("ID" IS NOT NULL)                                                 
 
 
Statistics information
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
          5  consistent gets                                                >>  there have 5 consistent gets     
          0  physical reads                                                     
          0  redo size                                                          
        408  bytes sent via SQL*Net to client                                   
        385  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     
 
                                          
 
SQL> SELECT COUNT(ID) FROM TEST WHERE ID IS NOT NULL;
 
Execution Plan
----------------------------------------------------------                      
Plan hash value: 735384656                                                      
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     
|                                                                               
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                
|   0 | SELECT STATEMENT      |         |     1 |     4 |     2   (0)| 00:00:01 
|                                                                               
                                                                                
|   1 |  SORT AGGREGATE       |         |     1 |     4 |            |          
|                                                                               
                                                                                
|*  2 |   INDEX FAST FULL SCAN| IND_IDN |  1000 |  4000 |     2   (0)| 00:00:01 
|                                                                               
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                                                                                          
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - filter("ID" IS NOT NULL)                                                 
 
 
Statistics information
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
          5  consistent gets                                        >>  there have 5  consistent gets             
          0  physical reads                                                     
          0  redo size                                                          
        409  bytes sent via SQL*Net to client                                   
        385  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     
 





QUESTION :


what's the relationship between the number of blocks and consistent gets ? how to calculate the consistent gets ?
Re: what's the relationship between the number of blocks and consistent gets ? [message #558215 is a reply to message #558185] Wed, 20 June 2012 05:47 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Quote:
SELECT DISTINCT DBMS_ROWID.rowid_block_number(ROWID) BLOCKS FROM TEST; BLOCKS ----------- 61762 61764 61763 >>above , there have 3 blocks in table TEST .
No, this shows that 3 blocks of the table have riws.
For your other figures, you need to consider the work of parsing the statement, as well as executing it.
Re: what's the relationship between the number of blocks and consistent gets ? [message #558484 is a reply to message #558215] Fri, 22 June 2012 15:25 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
From the 11.2 reference manual. Definition of consistent gets:

Quote:
Number of times a consistent read was requested for a block.


So there is one consistent get per single block read according to this. I believe that a consistent read is one that uses undo to maintain read consistency so that you see the version of the rows committed at the time your query started.

- Bobby

p.s. Link to relevent section of reference manual:

http://docs.oracle.com/cd/E11882_01/server.112/e25513/stats002.htm#i375475
Re: what's the relationship between the number of blocks and consistent gets ? [message #558487 is a reply to message #558484] Fri, 22 June 2012 20:09 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Lonion, you need to read the oracle doc.
Re: what's the relationship between the number of blocks and consistent gets ? [message #558488 is a reply to message #558487] Fri, 22 June 2012 20:15 Go to previous message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
http://en.wikipedia.org/wiki/Pot_calling_the_kettle_black
Previous Topic: SQL Tuning Help
Next Topic: SGA related queries
Goto Forum:
  


Current Time: Thu Apr 17 07:44:41 CDT 2014

Total time taken to generate the page: 0.29652 seconds