Oracle database tuning basic Q&A

1)Version information:

SELECT * FROM product_component_version ;

2)List free and used space in database :


SELECT sum(bytes)/1024 "free space in KB"
FROM dba_free_space;

SELECT sum(bytes)/1024 "used space in KB"
FROM dba_segments;

3)List tablespace fragmentation information :



SELECT tablespace_name,COUNT(*) AS fragments,
SUM(bytes) AS total,
MAX(bytes) AS largest
FROM dba_free_space
GROUP BY tablespace_name;


4)Check the current number of extents and blocks allocated to a seg


SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS


5)Extent information


SELECT segment_name, extent_id, blocks, bytes
FROM dba_extents
WHERE segment_name = TNAME ;



6)List table blocks, empty blocks, extent count, and chain block count

SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;

SELECT chain_cnt AS CHAINED_BLOCKS
FROM dba_tables
WHERE table_name=TNAME;

SELECT COUNT(*) AS EXTENT_COUNT
FROM dba_extents
WHERE segment_name=TNAME;

7)Information about all rollback segments in the database


SELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS
FROM DBA_ROLLBACK_SEGS;


8)Index & constraint information


SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
(SELECT constraint_name
FROM dba_constraints
WHERE table_name = TNAME
AND constraint_type in ('P','U')) ;

9)List tables and synonyms

set pagesize 0;

select 'TABLE:',table_name,'current' from user_tables
union
select 'SYNONYM:',synonym_name,table_owner from user_synonyms
order by 1,2 ;

10)Tuning: library cache


Glossary:

pins = # of time an item in the library cache was executed

reloads = # of library cache misses on execution

Goal:

get hitratio to be less than 1

Tuning parm:

adjust SHARED_POOL_SIZE in the initxx.ora file, increasing by small increments



SELECT SUM(PINS) EXECS,
SUM(RELOADS)MISSES,
SUM(RELOADS)/SUM(PINS) HITRATIO
FROM V$LIBRARYCACHE ;







11)Tuning: buffer cache



Calculation:

buffer cache hit ratio = 1 - (phy reads/(db_block_gets + consistent_gets))

Goal:

get hit ratio in the range 85 - 90%

Tuning parm:

adjust DB_BLOCK_BUFFERS in the initxx.ora file, increasing by small increments




SELECT NAME, VALUE
FROM V$SYSSTAT WHERE NAME IN
('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS');


12)Archive Log Mode Status



/* Status of Archive Log Subsystem */

ARCHIVE LOG LIST


/* log mode of databases */

SELECT name, log_mode FROM v$database;


/* log mode of instance */

SELECT archiver FROM v$instance;