Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sajid.

Asked: July 25, 2002 - 7:58 am UTC

Last updated: February 23, 2011 - 8:17 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

Could you please explanation(very brief) the result:
e.g Whats recursive calls? whats db block gets? and so on.

Statistics
----------------------------------------------------------
0 recursive calls
202743 db block gets
84707 consistent gets
0 physical reads
0 redo size
2010 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

Thanks in advance.

Regards,
Sajid Anwar
London


and we said...

recursive calls = basically sql performed on behalf of your sql. So, if we had to PARSE the query for example, we might have had to run some other queries to get data dictionary info. that would be recursive calls.

db block gets = blocks gotten in "current" mode. That is, blocks gotten as they exist right now. You'll see these for full table scans (segment headers are read in current mode) and modification statements (we modify the block as it exists "right now")

consistent gets = blocks gotten in consistent read mode. This is the mode we read blocks in with a select for example. Also, when you do a searched UPDATE/DELETE, we read the blocks in consistent read mode and then get the block in current mode to actually do the modification. A select for update will do this as well.

physical reads = self explanatory, physical IO

redo size = self explanatory -- amount of redo generated

sorts (memory)/(disk) -- sorts done.



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a million.

Sajid Anwar, July 26, 2002 - 4:12 am UTC

Hi Tom,
I was wondering how old questions appear in the list. I mean some are 200/300 days old or even higher. One more thing how somebody reviews the answer you post. Say if you answer to A's query and send it A and A posts his review.
Then how come C comes in and submit his review?

I know this is not relevant place for this question. Please bare me.

Thanks in advance.

Tom Kyte
July 26, 2002 - 6:49 am UTC

when I update a question, I update the timestamp and it bubbles back to the top of the list. So, the oldest quesiton in asktom could be at the top of the list.

I let anyone review a question

difference between block gets and consistent reads

Doug C, July 26, 2002 - 4:29 pm UTC

Tom - I'm not clear on the difference between block gets - those we get get as they exist right now as in a full table scan.. and consistent reads.. as in a select.
Suppose I did a select that required a full table scan? Then what?

Thanks.

Tom Kyte
July 28, 2002 - 3:19 pm UTC

if you did a select that required a full scan like this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        363  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

you would do a couple of current mode gets to read the segment header (4 in this example) and then read all of the blocks from the table in consistent read mode.  To understand that mode, read either "Expert One on One Oracle" (my book -- cover lots of these topics) or:

http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c01intro.htm#3956
which is about read consistency in Oracle. 

To elaborate my above question

Doug, July 27, 2002 - 1:47 am UTC

Tom - I've noticed you have another question updated recently called "consistent gets" - I guess my confusion is let's say we need some information as of "right now".. so for each block we have to read to get this *consistent* information including any rollback we have to get (if any), we charge a consistent get? Is there overlap between those consistent gets and db block gets and where are they mutually exlusive if ever...

Thanks..

Tom Kyte
July 28, 2002 - 3:28 pm UTC

they are totally different.

Read:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c23cnsis.htm#17882 <code>

that explains what a consistent get is -- it is a point in time GET of the data. We might rollback data to a prior point in time in order to make it consistent with the rest of your result set.

db block gets -- that is the data block as it exists right now, this instant. consistent gets -- that is the data block as it EXISTED right then, at the point in time it must be consistent as of.

redo size

Nadya, July 29, 2002 - 2:15 am UTC

Simple question: REDO SIZE - in bytes or in blocks OC?
Thank you for your site.

Tom Kyte
July 29, 2002 - 7:22 am UTC

bytes

What does redo size in select statement mean?

A reader, February 22, 2011 - 12:46 pm UTC

SQL>select * from detail_tbl where id = 5 and paid_date between date '2008-01-01' and date '2010-12-31' and department_id = 'EDCFG' and project_id = '12XXADGIS';

152 rows selected.

Elapsed: 00:00:08.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3819436546

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |     1 |   235 |   426   (0)| 00:00:08 |       |       |
|   1 |  PARTITION LIST SINGLE             |                         |     1 |   235 |   426   (0)| 00:00:08 |     5 |     5 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| RPT_EXPENSE_DETAIL      |     1 |   235 |   426   (0)| 00:00:08 |     5 |     5 |
|*  3 |    INDEX RANGE SCAN                | RPT_EXPENSE_DETAIL_IDX1 | 22815 |       |     2   (0)| 00:00:01 |     5 |     5 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LOCAL_PROJECT_CD"='XADGIS' AND "LOCAL_DEPARTMENT_CD"='CFG')
   3 - access("BATCH_ID"=5 AND "PAID_DT">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "PAID_DT"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     538801  consistent gets
       1328  physical reads
     656140  redo size
       6760  bytes sent via SQL*Net to client
        310  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        152  rows processed

What doese "656140  redo size" mean in this select statement?

Tom Kyte
February 23, 2011 - 8:17 pm UTC