Table with 255 columns or more…

March 4, 2015 Leave a comment

I wrote a BLOG on the Intra Row Chaining and the issues around this for a table that has more than 255 columns. Jonathan Lewis has written a very fantastic blog on this subject with some more explanation and a real life example. Worth Reading.

Jonathan’s Link

During my recent AIOUG OTN Yathra Tech Day in Chandigarh, I also demonstrated the CPU Cycles required for a Column Skip. This means, apart from the extra I/O required to read a column of a larger table, the CPU consumption will also be higher. Since this topic has come up and Jonathan nicely touching this subject, I would post the CPU calculation Blog in a day or so.

While the issue due to Intra Block Chaining is already published, the next blog will focus more on the CPU Cycles for column skipping for a small table (say with 14 columns) and then, this can be further co-related for a table with more columns. This will help DBA’s predict or evaluate the impact of the change (adding a new column) before being implemented on production. Also, a reason to have frequently queried on the top (Storage Space v/s CPU Consumption).

Filed under Performance Tagged with , , ,

Intra Block Chaining ! Impact on Logical Reads

November 8, 2012 5 Comments

I presented this during Sangam 12 (User Group Conference) and many participants wanted me to post a Blog on this. It is important to know the impact of Intra Block Chaining, which occurs, if the number of columns in a table exceed 255. If a table has more than 255 columns, the row pieces are stored in multiple row pieces of 255 each. Now, whether it stores columns 1 to 255 in one Row Piece and 256 to the remaining columns in next row piece, is an interesting fact that everyone wants to know. Through this blog, I will try to demonstrate and answer following facts :

  1. If a table consists of 300 Columns, which all columns will constitute the 2 row pieces ? i.e. Row Piece 1 : 1-255 Columns and Row Piece 2 : 256-300 Columns, or will it be something else ?
  2. What will be the performance impact of Intra Block Chaining ?

The reason, I picked this as one of the topic that the Developers and the DBA’s should know, is based on a Real Life Example. One of my customer complained of a Severe Performance issues, post applying an Application Patch. This patch was to introduce certain new functionality for their end-users. Further, this functionality change modified many of the critical tables used by the application. This change was : introduction of new columns. Few tables, which had less than 255 columns, were altered and new columns were added, which grew these numbers to more than 255. This was enough to cause the performance issues, which went unnoticed, during a test run, which was with done with less user load. On production, the user load was way high, and therefore, any increase in the I/O was capable enough to increase the CPU Utilization.

Let us start with a Demonstration, which is an easiest way to understand the facts. We will first create a table, with required number of columns. First, a table with 300 Columns and then I will insert single row into this table. Following two pl/sql blocks uses a Dynamic SQL to accomplish the task.

variable b1 number;
exec :b1:=&number_of_columns; -- Pass the Number of Columns to be required in a Table 

-- Dynamically Create Intra_Block table with required number of rows. This block constructs the CREATE TABLE Statement.

declare
l_statement	long:='create table intra_block (';
begin
  for i in 1..:b1
  loop
    if i<:b1 then
    l_statement:=l_statement||' A'||i||' number(3),';
    else
    l_statement:=l_statement||' A'||i||' number(3))';
    end if;
  end loop;
  execute immediate l_statement;
end;
/

-- Insert Single Row into the table. This block constructs the INSERT Statement.

declare
l_statement	long:='insert into intra_block(';
begin
   for i in 1..:b1
   loop
     if i<:b1 then
     l_statement:=l_statement||'A'||i||',';
     else
     l_statement:=l_statement||'A'||i||') values(';
     end if;
   end loop;
   for i in 1..:b1
   loop
     if i<:b1 then
     l_statement:=l_statement||i||',';
     else
     l_statement:=l_statement||i||')';
     end if;
   end loop;
   execute immediate l_statement;   
   commit;
end;
/
-- Gather Statistics
exec dbms_stats.gather_table_stats(user,'INTRA_BLOCK');

select table_name, blocks, num_rows from dba_tables where table_name='INTRA_BLOCK';

OWNER           PAR   NUM_ROWS     BLOCKS
--------------- --- ---------- ----------
VIVEK           NO           1          5

I created this table with 300 columns and inserted one row. DBA_Tables shows 5 blocks in the table. Next, the following queries show that the entire 300 columns fit into 1 block, block#182 in my case.

select dbms_rowid.rowid_block_number(rowid) bno, count(*) from intra_block group by dbms_rowid.rowid_block_number(rowid);

       BNO   COUNT(*)
---------- ----------
       182          1

Next, we shall execute a Query against the first column, which is A1, and then on the last column, which is A300. We will compare the I/O’s done by each of these queries.

set autot on stat

select a1 from intra_block;

        A1
----------
         1

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets <--- 7 Logical I/O's

select A300 from intra_block;

      A300
----------
       300

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets <-- One Additional Logical I/O's

Query on the last column (for a table with more than 255 columns) incurred one Additional Logical I/O. Let us now check, row piece structure. We now know that with 300 columns, the Rows will be inserted into 2 Row Pieces. One Row Piece will incur less I/O as compared to the another Row Piece. I want to check the columns in Row Piece 1 and Columns in Row Piece 2, which will help us know, which column onwards the query will start incurring additional I/O. Following pl/sql executes a query on each of the columns and we shall check for the I/O’s (from buffer_gets column of v$sqlarea).

 declare
l_statement varchar2(132);
l_value number;
begin
  for i in 1..:b1
  loop
    l_statement:='select /*+ intra_'||lpad(i,3,0)||' */ A'||i||' from intra_block';
    execute immediate l_statement into l_value;
  end loop;
end;
/

select sql_id, sql_text, buffer_gets, executions, rows_processed
from v$sqlarea where sql_text like 'select /*+ intra%'
order by to_number(substr(sql_text,instr(sql_text,'i',1)+6,3));

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS ROWS_PROCESSED
------------- -------------------------------------------------- ----------- ---------- --------------
47u9afvh40vqf select /*+ intra_001 */ A1 from intra_block                  7          1              1
cvjvxgnfz7h78 select /*+ intra_002 */ A2 from intra_block                  7          1              1
cbdajswujwnyf select /*+ intra_003 */ A3 from intra_block                  7          1              1
8g18r5syanmbx select /*+ intra_004 */ A4 from intra_block                  7          1              1
d5kyqtgd2d4q3 select /*+ intra_005 */ A5 from intra_block                  7          1              1
c0yvymb9p7gj0 select /*+ intra_006 */ A6 from intra_block                  7          1              1
.....
..... (some entries trimmed)
.....
f1rrwb22u89gf select /*+ intra_029 */ A29 from intra_block                 7          1              1
1556fp9466r4x select /*+ intra_030 */ A30 from intra_block                 7          1              1
3sk2zpnyqanmc select /*+ intra_031 */ A31 from intra_block                 7          1              1
asp7vk16b07sb select /*+ intra_032 */ A32 from intra_block                 7          1              1
9ts2g9w5dra0q select /*+ intra_033 */ A33 from intra_block                 7          1              1
c9dnc235v6w00 select /*+ intra_034 */ A34 from intra_block                 7          1              1
a532dxfj0d0w6 select /*+ intra_035 */ A35 from intra_block                 7          1              1
1p2yzsxax20qm select /*+ intra_036 */ A36 from intra_block                 7          1              1
6zb16tkbyrbhm select /*+ intra_037 */ A37 from intra_block                 7          1              1
38zqdzgvvg70w select /*+ intra_038 */ A38 from intra_block                 7          1              1
51f39r5tnw73d select /*+ intra_039 */ A39 from intra_block                 7          1              1
f1fywzj4avnz0 select /*+ intra_040 */ A40 from intra_block                 7          1              1
46015j3s4trsk select /*+ intra_041 */ A41 from intra_block                 7          1              1
1sdrag4sxbcjh select /*+ intra_042 */ A42 from intra_block                 7          1              1
a0t2nrpb8r83s select /*+ intra_043 */ A43 from intra_block                 7          1              1
aj2w0gvj5zy1g select /*+ intra_044 */ A44 from intra_block                 7          1              1
6nk7x7430k9uk select /*+ intra_045 */ A45 from intra_block                 7          1              1
ahdr7bqsm18x8 select /*+ intra_046 */ A46 from intra_block                 8          1              1
1vsus8qg2rsft select /*+ intra_047 */ A47 from intra_block                 8          1              1
86njp3z8adan3 select /*+ intra_048 */ A48 from intra_block                 8          1              1
6v6u6hp71vtwb select /*+ intra_049 */ A49 from intra_block                 8          1              1
7a9a7gbyhrvkr select /*+ intra_050 */ A50 from intra_block                 8          1              1
8wp83m0fn4kgw select /*+ intra_051 */ A51 from intra_block                 8          1              1
7q464wb184tpu select /*+ intra_052 */ A52 from intra_block                 8          1              1
.....
..... (some entries trimmed)
.....
9dmv986638rk4 select /*+ intra_235 */ A235 from intra_block                8          1              1
d2ks041mqfv1b select /*+ intra_236 */ A236 from intra_block                8          1              1
b0muufu2qqs7y select /*+ intra_237 */ A237 from intra_block                8          1              1
fbd5x0z45udvr select /*+ intra_238 */ A238 from intra_block                8          1              1
c31yuufwr2wzc select /*+ intra_239 */ A239 from intra_block                8          1              1
2aczq7gsf0680 select /*+ intra_240 */ A240 from intra_block                8          1              1
7us824yhm9c4s select /*+ intra_241 */ A241 from intra_block                8          1              1
c26kyzhmm1ad1 select /*+ intra_242 */ A242 from intra_block                8          1              1
dqrp4n7a5uzjp select /*+ intra_243 */ A243 from intra_block                8          1              1
4wzw76dh7sa6h select /*+ intra_244 */ A244 from intra_block                8          1              1
b4qwud4k9j9qg select /*+ intra_245 */ A245 from intra_block                8          1              1
6cyzxr6c7t4km select /*+ intra_246 */ A246 from intra_block                8          1              1
7u8krqjy6aq0k select /*+ intra_247 */ A247 from intra_block                8          1              1
8sxmn5ukxzqad select /*+ intra_248 */ A248 from intra_block                8          1              1
a66frsvy8q8vq select /*+ intra_249 */ A249 from intra_block                8          1              1
9htnkqmfchypc select /*+ intra_250 */ A250 from intra_block                8          1              1
058f3hvkf2y6m select /*+ intra_251 */ A251 from intra_block                8          1              1
1m9wfv39zmn6v select /*+ intra_252 */ A252 from intra_block                8          1              1
23mmdngutgbc6 select /*+ intra_253 */ A253 from intra_block                8          1              1
bnxt1ujafku6s select /*+ intra_254 */ A254 from intra_block                8          1              1
a4gv26ujgjhjv select /*+ intra_255 */ A255 from intra_block                8          1              1
draytdg79hc5c select /*+ intra_256 */ A256 from intra_block                8          1              1
4gr6ntv7zx764 select /*+ intra_257 */ A257 from intra_block                8          1              1
csfz96fkfz2vj select /*+ intra_258 */ A258 from intra_block                8          1              1
1v4sjc8740kdc select /*+ intra_259 */ A259 from intra_block                8          1              1
b38nmwf9bnfv0 select /*+ intra_260 */ A260 from intra_block                8          1              1
b52d8sd512zpr select /*+ intra_261 */ A261 from intra_block                8          1              1
6s8963hfnpt9b select /*+ intra_262 */ A262 from intra_block                8          1              1
5h19qru6dsudz select /*+ intra_263 */ A263 from intra_block                8          1              1
fpnvs4ry55qqg select /*+ intra_264 */ A264 from intra_block                8          1              1
2us1f52hmqz4j select /*+ intra_265 */ A265 from intra_block                8          1              1
g7xbq7nvg119d select /*+ intra_266 */ A266 from intra_block                8          1              1
78z5hjw7nam1n select /*+ intra_267 */ A267 from intra_block                8          1              1
82w5nq265jyfp select /*+ intra_268 */ A268 from intra_block                8          1              1
fh0hnjydm2unq select /*+ intra_269 */ A269 from intra_block                8          1              1
39tqqf1p9jm1s select /*+ intra_270 */ A270 from intra_block                8          1              1
cn4np8vkgk4mc select /*+ intra_271 */ A271 from intra_block                8          1              1
.....
..... (some entries trimmed)
.....
19m0kx9w80afs select /*+ intra_296 */ A296 from intra_block                8          1              1
1q9kg224txpqu select /*+ intra_297 */ A297 from intra_block                8          1              1
36dya8xmc8a0a select /*+ intra_298 */ A298 from intra_block                8          1              1
abn6s5jr2bac3 select /*+ intra_299 */ A299 from intra_block                8          1              1
grnbn11qw3xjk select /*+ intra_300 */ A300 from intra_block                8          1              1

300 rows selected.

From the output above, it can be seen that for 300 Columns, the I/O’s increased from 46th Column (A46), which is 300-255=45. This means, columns A300-A46 (255 columns) are together stored in One Row Piece, whereas, A45-A1 are stored in another Row Piece. This information was a bit interesting. Initially I thought, any query that queries columns A256 and above, would incur additional I/O, but the rows are stored backward and 255 limit also starts from back i.e.last column onwards.

For curiosity, I ran the entire test again for 256 columns. The table was dropped and recreated to have A1..A256 columns. With this, I could see that the I/O’s increased by 1 from column A2 onwards (256-255=1). Again, Columns A2..A256 were stored in one Row Piece and A1 in another. This also means, if you have a table with 255 columns, you add one column and would immediately see I/O’s go up by 1 for any column starting 2nd Column.

You may run this for any number of columns, and should see the same results. The third test I did was on a 600 Column Table. The I/O’s were as under :

Columns A1..A90 --- 7 I/O's
Columns A91..A345 --- 8 I/O's
Columns A346 onwards --- 9 I/O's 

600-255=345
345-255=90

This test proves that the Table Design requires careful planning. There is hardly any reason, why a Table needs to be created with more than 255 columns. However, I have seen many applications that create tables with more number of columns. This blog should be an eye opener for them. Any additional (unwanted) I/O’s that are saved will have dramatic impact on Application performance. This will improve the Scalability of the Application.

Filed under Performance Tagged with , , , ,

Consistent Gets Myth…..

March 4, 2010 25 Comments

Based on the request from some of my regular readers, this is a reposting of my previous blog on viveklsharma.blogspot.com (at present unaccessible).

Any Query executed against a database gets the required rows, which are then fetched to the end user. Everyone is aware with the fact that a table row(s) is/are stored in Oracle Database block(s), hence, when oracle has to get even a single row, a block containing that row has to be read into the buffer cache. From this, it is quite evident that Oracle reads a block at a minimum. Also, as an Optimization Feature, for every query, blocks are fetched to a user from the buffer cache (Parallel Queries are exception). Hence, if a block is not found in the buffer cache, these are reads from the disk into the cache and then given to the user.

In Oracle terminology, a read from disk is termed as a Physical Read or a Disk Reads and, a read from Cache is termed as a Consistent Get or a Logical I/O. As mentioned in my previous paragraph, if a block is not found in the Cache, it is first read from the disk into the cache and fetched to the user. This also means that every logical read includes the count of disk reads.

In this blog, I am going to write on a myth about the calculation assumed and derived by the DBA’s from the value of Consistent Gets.

At one customer site, we were discussing about Application tuning wherein a dba gave a list of Top Queries to the Vendor. The list contained the queries alongwith the statistics called Total I/O in GB and the values in this column was in the range of hundreds of GB to tens of TB. I was surprised to see these values and after the discussion, out of the curiosity, asked the dba about the values calculated in this column. The dba immediately gave me the access to his query that generated the output. The calculation in this column was (buffer_gets*db_block_size)/(1024*1024*1024). It seems that the dba’s assumed that each logical I/O to be a read to a new block and hence, these high values. Hence, if a query does a logical I/O of 1549087, then based on the calculation, the dba’s assumed that a query has read 11 gb (assuming 8k block size) worth of data into the cache. This is not true.

BLOCKS READ ALGORITHM

A Concepual Knowledge on Oracle Blocks Read Algorithm is as under :

  1. User Issues a Query.
  2. Query is Parsed and Optimal Execution path is generated and stored in the Library Cache.
  3. Based on the Execution Path, required Index or Table block is searched in the the Cache.
  4. If the block is not found in the cache, a disk read request is made, and the block is read into the Cache.
  5. The block, from the Cache is then read into a private memory area (UGA) of the User.
  6. Once the Block is read into the private memory of the User, the required row is fetched.

The value of Consistent Read is incremented each time a new block is read into a private memory area of the User, which also means that, a single block, if read multiple times, affect the value of the Consistent Read. While an in-efficient SQL contributes largely to Consistent Read, but one factor that also have significant impact is ARRAY SIZE. I am not going to discuss about In-efficient Queries, but will write on the calculation that undergoes and impact Consistent Gets.

A better way to understand the calculation is by way of an Example and as always, I will run through some of the simple queries and see the impact of the change in ARRAYSIZE to check its impact on Consistent Gets.

I will create a table with 3000 rows and will run a simple query in SQLPLUS prompt. Without an Index on this table, it will be a full table scan.

SQL> create table test_cg  as select * from all_objects where rownum between 1 and 3000;

Table created.

Elapsed: 00:00:01.82

SQL> exec dbms_stats.gather_table_stats(user,'TEST_CG');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.46

SQL> @table_stats TEST_CG
old   2: where table_name='&1'
new   2: where table_name='TEST_CG'

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
VIVEK                          NO        3000         38 02-MAR-10 YES

1 row selected.

SQL> set autot trace
SQL> select * from test_cg;

3000 rows selected.

Elapsed: 00:00:00.17

Execution Plan
----------------------------------------------------------
Plan hash value: 2626677675

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  3000 |   249K|    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST_CG |  3000 |   249K|    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        238  consistent gets
          0  physical reads
          0  redo size
     300791  bytes sent via SQL*Net to client
       2608  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

A simple query with 38 blocks accounted for 238 consistent gets. Does it mean 238*8192 i.e.1.8 MB of data read into the cache ? The answer is plain NO. As mentioned earlier, the blocks are read only once into the cache and is sent to the PGA of the user that require this block. Internally, the calculation for consistent gets is (NUM_ROWS / ARRAYSIZE)+NUM_BLOCKS. In my case, when I executed this query, the arraysize was unchanged and therefore was default, which is 15.

Rows in my Table	: 3000
Blks in my Table	: 38
Default Arraysize	: 15

(3000/15)+38 = 200 + 38 = 238

The calculation matches the Consistent Gets. One value worth discussing here is the number 200 derived from (3000/15). This value means that each of the blocks will be touched multiple times. There are 38 blocks in the table, each of these 38 blocks will be read only once in the cache, but will be touched more than once and therefore, the consistent gets for a single block access will based on the number of times, it will be visited to read all the required rows. Incidently, another statistics that matches our calculation is the “SQL*Net roundtrips to/from client” from the Autotrace output above. The value, with arrays 15 is 201, which means, 200 visits or touch to the block to fetch 3000 rows, and last visit required to check and confirm whether more rows are to be fetched.

Back to our example, let us get into more details of the calculation of Consistent Gets. The table has 3000 rows and 38 blocks. The count of rows in each of these 38 blocks is shown below. 

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) cnt
from test_cg
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid) order by 1;
SQL> /

     BLKNO        CNT
---------- ----------
       641         88
       642         84
       643         81
       644         76
       645         81
       646         80
       647         82
       648         77
.... some lines deleted
.... some lines deleted
       677         78
       678         82
           ----------
sum              3000

The Sequence of Consistent Gets calculation is as under (with default arraysize) :

  1. Get Block 641 in the PGA, fetch 15 Rows – Consistent Gets = 1
  2. Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 2
  3. Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 3
  4. Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 4
  5. Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 5
  6. Get Block 641 again in the PGA, fetch another 13 rows – Consistent Gets = 6
  7. Get Block 642 in the PGA, fetch 2 rows – Consistent Gets = 7
  8. Get Block 642 again in the PGA, fetch another 15 rows – Consistent Gets = 8

    9. and so on….

It is clear from this explanation that a single block is read is multiple times, in our case at at average 6 times and therefore, consistent gets for each of these blocks was around 6 to 7. Run this test case with different arraysize and the calculation should match that shown in this blog. Let us run the query with different values of Arraysize and validate the calculation.

ArraySize = 35 Consistent Gets will be (3000/35)+38 = 86+38 = 124
ArraySize = 40 Consistent Gets will be (3000/40)+38 = 75+38 = 113
ArraySize = 50 Consistent Gets will be (3000/50)+38 = 60+38 = 98

SQL> set arrays 35
SQL>select * from test_cg;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        124  consistent gets
         87  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

SQL> set arrays 40
SQL> select * from test_cg;

3000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        113  consistent gets
         76  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

SQL> set arrays 50
SQL> select * from test_cg;

3000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         98  consistent gets
         61  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

Below is the query, that can be executed to check for the approximate value of the Consistent Gets and should help you understand the way this value is calculated. The value of bind variable is to be set to the ARRAYSIZE. The Query and the details of the columns used is as under :

variable b1 number;
exec :b1:=15;
SQL> compute sum of total_cnt on report
SQL> break on report

select	blkno, total_cnt, final_cnt, rows_remaining,
	case when rows_remaining=0 then touch_cnt+1 else touch_cnt end touch_cnt
from (
select	blkno, total_cnt, final_cnt, rows_remaining,
	case when total_cnt = final_cnt then ceil(final_cnt/:b1) else ceil(final_cnt/:b1)+1 end touch_cnt
from	(
select	blkno, cnt total_cnt, 
	case when rownum=1 or lag(rows_remaining) over (order by blkno)=0 
                     then cnt else (cnt-(:b1-lag(rows_remaining) over (order by blkno))) end final_cnt,
	rows_remaining 
from (
select blkno, cnt, rr, 
lead(rr) over(order by blkno) next_rr,
lead(blkno) over(order by blkno) next_blk,
ceil(rr/:b1) touch_cnt,
mod(rr,:b1) rows_remaining
from (
select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) cnt, 
	sum(count(*)) over(order by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) rr 
from test_cg
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid) order by 1))));

     BLKNO  TOTAL_CNT  FINAL_CNT ROWS_REMAINING  TOUCH_CNT
---------- ---------- ---------- -------------- ----------
       641         88         88             13          6
       642         84         82              7          7
       643         81         73             13          6
       644         76         74             14          6
       645         81         80              5          7
       646         80         70             10          6
       647         82         77              2          7
       648         77         64              4          6
       649         73         62              2          6
       650         79         66              6          6
       651         79         70             10          6
       652         79         74             14          6
       653         81         80              5          7
       654         82         72             12          6
       655         77         74             14          6
       656         81         80              5          7
       657         80         70             10          6
       658         81         76              1          7
       659         78         64              4          6
       660         78         67              7          6
       661         76         68              8          6
       662         78         71             11          6
       663         78         74             14          6
       664         77         76              1          7
       665         81         67              7          6
       666         79         71             11          6
       667         79         75              0          7
       668         77         77              2          6
       669         77         64              4          6
       670         76         65              5          6
       671         80         70             10          6
       672         77         72             12          6
       673         76         73             13          6
       674         75         73             13          6
       675         79         77              2          7
       676         78         65              5          6
       677         78         68              8          6
       678         82         75              0          7
           ---------- ---------- -------------- ----------
sum              3000       2744            284        238

BLKNO		: Block Number
TOTAL_CNT	: Total Rows in the Block
FINAL_CNT	: Final Number of Rows 
		(Example Block 642 has total 84 Rows, but final row count is 82 as 13 rows were read from 641 and balance 
		2 Rows were fetched from this block, this gives 84-2=82)
ROWS_REMAINING	: Incomplete Arraysize Rows from the current block
TOUCH_CNT	: Touch Count for current block and is our Consistent Gets per Block. Final total at the end.

Consistent Gets are not a measure of number of blocks that are read into the cache but number of times, a block was read into the PGA. A block is read only once in the cache and touched multiple times. Our example above showed that with arraysize of 15, an 8k block was read once but was touched 6-7 times and hence had a 6 or 7 consistent gets per block. This does not mean 6*8192 worth of data. The data was read only once.

Filed under Performance, Uncategorized Tagged with , ,