Oracle – Pinning table data in the
Buffer Cache
Database
buffer cache:
The largest
component of the SGA is usually the database buffer cache, which is the part of
the SGA that holds copies of blocks of data read from the Oracle datafiles on
disk. The size of the database buffer cache is controlled by the INIT.ORA parameter DB_BLOCK_BUFFERS, which
specifies the number of database blocks that will be contained in the database
buffer cache.
Pinning table data in buffer cache:
Step
1: connect and start the database.
SQL> startup
ORACLE instance
started.
Total System Global
Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 1677721600 bytes
Database Buffers
452984832 bytes
Redo Buffers
7876608 bytes
Database mounted.
Database opened.
Step
2: first make sure that there’s
nothing in the cache by running and clear the cache file using following
command
SQL> alter system
flush buffer_cache;
System altered.
SQL> alter system
flush buffer_cache;
System altered.
Step
3: Now, with the help of autotrace,
we can have a look at the difference between retrieving cached and uncached
data.
Auto trace
The autotrace utility is a very
underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT,
INSERT, UPDATE or DELETE statement. The autotrace provides instantaneous
feedback including the returned rows, execution plan, and statistics.
Timing
Sometimes when working on SQL command optimizations, all that is
desired is a rough timing estimate; namely, the SQL*Plus client elapsed
execution time, or simple clock time. Often that simple metric is sufficient
for some very basic tuning needs. SQL*Plus has a built-in capability to do
exactly this - it is the SET TIMING command.
SQL>
set autotrace on
SQL> set timing on
Step
4:run the sql select statement to check the result
SQL> select * from
rman.rman;
The
first time we execute this query, the timing and statistics output will be
something like
ID NAME
----------
--------------------
7 viki
10 vignesh
10 vijay
3 ajay
4 pradeep
5 chokku
6 ram
8 si
8 rows selected.
Elapsed: 00:00:00.16 (run this query on 00.16)
Execution Plan
----------------------------------------------------------
Plan hash value:
557262021
--------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 6 | 54 |
3 (0)| 00:00:01 |
| 1 |
TABLE ACCESS FULL| RMAN | 6
| 54 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
56 consistent gets
22
physical reads(and this count as 22)
0 redo size
774 bytes sent via
SQL*Net to client
385 bytes received
via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
8 rows processed
run
the same query again, we can see that things have changed a bit
SQL> select * from
rman.rman;
ID NAME
----------
--------------------
7 viki
10 vignesh
10 vijay
3 ajay
4 pradeep
5 chokku
6 ram
8 si
8 rows selected.
Elapsed:
00:00:00.01(time has been changed)
Execution
Plan
Plan hash value: 557262021
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT |
| 6 | 54 |
3 (0)| 00:00:01 |
| 1 |
TABLE ACCESS FULL| RMAN | 6
| 54 | 3
(0)| 00:00:01 |
Statistics
0 recursive calls
0 db block gets
6 consistent gets
0
physical reads(its also changed)
0 redo size
774 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)
8 rows processed
The second run was a fair bit faster. This is mainly because
the data required to resolve the query was cached after the first run.
Therefore, the second execution required no
Physical I/O to retrieve the result set.
current
size of the Buffer Cache:
SQL> select
component, current_size from v$memory_dynamic_components where component =
'DEFAULT buffer cache';
COMPONENT
CURRENT_SIZE
----------------------------------------------------------------
------------
DEFAULT buffer cache 419430400
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2402317937
-----------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 |
31 |
0 (0)| 00:00:01 |
|* 1 |
FIXED TABLE FULL| X$KMGSCT |
1 |
31 |
0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information
(identified by operation id):
--------------------------------------------------
1 -
filter("ST"."COMPONENT"='DEFAULT buffer cache' AND
"ST"."INST_ID"=USERENV('INSTANCE'))
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
657 bytes sent via
SQL*Net to client
466 bytes received
via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
No comments:
Post a Comment