As of Oracle 11g, you can use new
initialization parameters such as MEMORY_TARGET to further automate the overall memory used
by Oracle??”helping you tune the database automatically when you don??™t have time to read the
AWR reports!
The data block buffer cache and the shared pool are managed via a least recently used (LRU)
algorithm. A preset area is set aside to hold values; when it fills, the least recently used data is
eliminated from memory and written back to disk. An adequately sized memory area keeps the
most frequently accessed data in memory; accessing less frequently used data requires physical
reads.
Chapter 8: Database Tuning 253
You can see the queries performing the logical and physical reads in the database via the
V$SQL view. V$SQL reports the cumulative number of logical and physical reads performed for
each query currently in the shared pool, as well as the number of times each query was executed.
The following script shows the SQL text for the queries in the shared pool, with the most I/O
intensive queries listed first. The query also displays the number of logical reads (buffer gets)
per execution:
select Buffer_Gets,
Disk_Reads,
Executions,
Buffer_Gets/Executions B_E,
SQL_Text
from V$SQL where executions != 0
order by Disk_Reads desc;
If the shared pool has been flushed, queries executed prior to the flush will no longer be
accessible via V$SQL. However, the impact of those queries can still be seen, provided the users
are still logged in.
Pages:
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426