Skip to Main Content
  • Questions

  • “Consistent gets” is higher when query is executed for the first time

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sujit.

Asked: February 01, 2017 - 1:03 pm UTC

Last updated: February 06, 2017 - 2:45 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

I have the following query which when executed for the first time returns "consistent gets" 628066. When I re-execute it, it returns "consistent gets" as 270923.

The table itself has 272446 blocks (taken from ALL_TABLES).

It goes for a table scan in both cases and there are no indexes on the columns.

Any reason ? Is the statistics lookup adds to the consistent gets when executing for the first time.

select count(*)
from TRANSACTIONTABLE
where BEGDATE <= to_date('2018-03-01 09:00:00' , 'YYYY-MM-DD HH:MI:SS') and
ENDDATE > to_date('2018-03-01 09:00:00' , 'YYYY-MM-DD HH:MI:SS')

and Chris said...

The first time you execute a query Oracle Database has to extra work to parse it, optimize it, etc. These appear as recursive calls:

SQL> create table t as
  2    select rownum x, sysdate y from dual connect by level <= 1000;

Table created.

SQL>
SQL> set autotrace trace stat
SQL> select * from t;

1000 rows selected.


Statistics
----------------------------------------------------------
          7  recursive calls     <=========== 7 calls
          0  db block gets
         25  consistent gets     <=========== 25 gets
          3  physical reads
          0  redo size
      18275  bytes sent via SQL*Net to client
        599  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> select * from t;

1000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls     <=========== 0 calls
          0  db block gets
         15  consistent gets     <=========== 15 gets
          0  physical reads
          0  redo size
      18275  bytes sent via SQL*Net to client
        599  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed


How do the "recursive calls" compare between your executions?

Rating

  (4 ratings)

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

Comments

recursive calls

Sujit Pandey, February 02, 2017 - 4:31 am UTC

recursive calls were 393. re-executing the same query returns 0 for recursive calls. I think i got my answer through some testing. Its through turning off the dynamic sampling which returns "consistent reads" close to the number of blocks for the table. Still my question is the "consistent reads" higher due to the recursive calls which hard parses the query? Is it due to the reading of oracle statistics tables for the parsing?
Connor McDonald
February 03, 2017 - 7:18 pm UTC

A dynamic sample is reading the tables *within the query* not (just) the dictionary statistics. So this could definitely account for increased logical io

recursive calls

Sujit Pandey, February 02, 2017 - 5:17 pm UTC

Recursive calls were 393. re-executing the same query returns 0 for recursive calls. I think i got my answer through some testing. Its through turning off the dynamic sampling which returns "consistent reads" close to the number of blocks for the table. Still my question is the "consistent reads" higher due to the recursive calls which hard parses the query? Is it due to the reading of oracle statistics tables for the parsing?
Chris Saxon
February 03, 2017 - 4:19 pm UTC

Yes, the hard parsing and reading the stats is part of this. If you're interested, trace the session. You can see what the recursive SQL statements are and how much work they do from this.

But..

A reader, February 04, 2017 - 6:54 am UTC

But in the example showed when creating table T, you didn't run any Stat. So how this be coherent with the last post?

Thanks Chris and Connor for your inputs

Sujit, February 06, 2017 - 7:02 am UTC

Thanks Chris and Connor for your inputs
Connor McDonald
February 06, 2017 - 2:45 pm UTC

thanks for the feedback
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.