Cache In oracle


$sqlplus "/ as sysdba";
SQL*Plus: Release 10.2.0.1.0 - Production on ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set autotrace traceonly statistics;
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from table_a where column_name = 2;--first call

Statistics
----------------------------------------------------------
 2224 recursive calls
 0 db block gets
 638 consistent gets
 49 physical reads
 0 redo size
 335 bytes sent via SQL*Net to client
 376 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 57 sorts (memory)
 0 sorts (disk)
 1 rows processed
SQL> select count(*) from table_a where column_name = 2; --second call, cache affect

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: