Sanjay Forum

set pagesize 86 ttitle skip 2 center 'FREE - Free space by Tablespace' skip 2 column dummy noprint column pct_used format 999.9 heading "Percentage_Used" column name format a16 heading "Tablespace Name" column bytes format 999,999,999,999,999 heading "Total MBytes" column used format 999,999,999,999,999 heading "Used MB" column free format 999,999,999,999,999 heading "Free MB" break on report compute sum of bytes on report compute sum of free on report compute sum of used on report spool freespace.lst set termout on select a.tablespace_name name, sum(b.bytes/1024/1024)/count( distinct a.file_id||'.'||a.block_id ) Megabytes, sum(b.bytes/1024/1024)/count( distinct a.file_id||'.'||a.block_id ) - sum(a.bytes/1024/1024)/count( distinct b.file_id ) usedSpace, sum(a.bytes/1024/1024)/count( distinct b.file_id ) ...

Accessing parts of the database already loaded into memory is much faster than accessing from disk. The other important factor is that of distribution of available virtual memory resources to the different memory structures available in Oracle. Memory should be tuned after SQL and application code and before I/O access speed. Oracle divides memory resources into separate buffers. The Buffer Cache The database buffer cache needs to be large enough such that neccessary data is not purged from memory and not too large such that performance is affected. The init.ora parameters file contains four parameters affecting memory assigned to an Oracle database instance. 1. DB_BLOCK_SIZE. 2. DB_BLOCK_BUFFERS. 3. SHARED_POOL_SIZE. 4. SORT_AREA_SIZE. The following query shows the values for the above parameters. select name, value from v$parameter where name in ('db_block_buffers','db_block_size','shared_pool_size','sort_area_size'); NAME VALUE ---------------------...