Tuning de la Base de Datos

-------- Tuning de la BD-------------------------------------------------------------------------------------------------------SET FEEDBACK OFF;SET HEADING ON;SET ECHO OFF;SET PAGESIZE 50000;SET LINESIZE 122;CLEAR SCREEN;spool tune.log;
prompt =================PARAMETERS
prompt ======================
prompt DATABASE INFO
column date_of_run format a30;
column open_time format a30;
column block_size_bytes format a20;
select d.name DATABASE, to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN, to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME, value BLOCK_SIZE_BYTES from v$database d, v$parameter p, v$thread t where p.name = 'db_block_size';
prompt =============================
prompt DB BUFFER CACHE HIT RATIO > 80%prompt else increase DB_BLOCK_BUFFERScolumn "DB BUFFER CACHE HIT RATIO" format 999,999,999,999,999,999.9999
select (1-c.value/(a.value+b.value+0.000001))*100 "DB BUFFER CACHE HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d where a.name = 'db block gets' -- 'logical_reads' = 'db block gets' + 'consistent gets' and b.name = 'consistent gets' and c.name = 'physical reads' and d.name = 'physical writes';
prompt =================================
prompt DICTIONARY CACHE HIT RATIO > 99%prompt else increase SHARED_POOL_SIZEcolumn "DICTIONARY CACHE HIT RATIO" format 999.9999
select (1-sum(getmisses)/sum(gets))*100 "DICTIONARY CACHE HIT RATIO" from v$rowcache;
prompt ======================================
prompt LIBRARY CACHE HIT RATIO > 99%prompt else increase SHARED_POOL_SIZE, OPEN_CURSORScolumn "LIBRARY CACHE HIT RATIO" format 999.9999
select (1-sum(reloads)/sum(pins))*100 "LIBRARY CACHE HIT RATIO" from v$librarycache;
prompt ======================================================================================================= RBS SEGMENT CONTENTION RATIO < 1%prompt else more RBS are neededcolumn "RBS SEGMENT CONTENTION RATIO" format 999.9999
select max(waits/gets)*100 "RBS SEGMENT CONTENTION RATIO" from v$rollstat;
prompt ======================================================================================================= MAX SESSION EVENT AVERAGE WAIT = 0prompt else contention existscolumn "MAX SESSION EVENT AVERAGE WAIT" format 999,999,999.9999;
select max(average_wait) "MAX SESSION EVENT AVERAGE WAIT" from v$session_event;
prompt ======================================================================================================= I/O BALANCE RATIOS < 100%prompt else re-locate datafilescolumn FS_SIZE_STDDEV_RATIO format 999.999 heading "FILESYSTEMSIZESSTANDARDDEVIATIONRATIO"column PHYS_READS_STDDEV_RATIO format 999.999 heading "PHYSICALREADSSTANDARDDEVIATIONRATIO"column PHYS_WRITES_STDDEV_RATIO format 999.999 heading "PHYSICALWRITESSTANDARDDEVIATIONRATIO"
select stddev(sum(a.bytes))/avg(sum(a.bytes))*100 FS_SIZE_STDDEV_RATIO, stddev(sum(b.phyrds))/avg(sum(b.phyrds))*100 PHYS_READS_STDDEV_RATIO, stddev(sum(b.phywrts))/avg(sum(b.phywrts))*100 PHYS_WRITES_STDDEV_RATIO from v$datafile a, v$filestat b where a.file# = b.file# group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1));
prompt ======================================================================================================= I/O CONTENTION =======================================================================================================promptcolumn TOTALS format a44 heading "DATABASE";column file_system_name format a44 heading "File SystemName";column tablespace_name format a44 heading "TableSpaceName";column file_name format a44 heading "FileName";column MBytes format 9,999,999 heading "Size(MBytes)";column phyblkrd format 9,999,999 heading "NumberBlocksRead(000's)";column phyblkwrt format 9,999,999 heading "NumberBlocksWritten(000's)";column read_time_per_block_msec format 9,999,999 heading "TimetoRead1 Block(msecs)";column write_time_per_block_msec format 9,999,999 heading "TimetoWrite1 Block(msecs)";column avg_access_time_tot format 9,999,999 heading "AverageTimetoRead/Write1 Block(msecs/blk)";column avg_access_time format 9,999,999 heading "SORTEDON THISCOLUMN:AverageTimetoRead/Write1 Block(msecs/blk)";column avg_access_speed format 9,999,999 heading "AverageSpeedtoRead/Write1 Second(blks/sec)";column avg_estimated_srcer_speed format 9,999.999 heading "EstimatedAverageSRCERPerform.(recs/sec)";column avg_estimated_rlser_speed format 9,999.999 heading "EstimatedAverageRLSERPerform.(recs/sec)";column tps format 9,999.999 heading "ORACLE TRANSACTIONS PER SECOND";column spt format 9,999.999 heading " SECONDS PER ORACLE TRANSACTION";
select d.name DATABASE, to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN, to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME, value BLOCK_SIZE_BYTES from v$database d, v$parameter p, v$thread t where p.name = 'db_block_size';
SET FEEDBACK ON;
select 'TOTALS' TOTALS , sum(bytes)/(1024*1024) MBytes, sum(phyblkrd)/1000 phyblkrd, sum(phyblkwrt)/1000 phyblkwrt, (sum(readtim)*10)/(sum(phyblkrd)+1) read_time_per_block_msec, (sum(writetim)*10)/(sum(phyblkwrt)+1) write_time_per_block_msec, (((sum(readtim)*10)/(sum(phyblkrd)+1))*(sum(phyblkrd)) + ((sum(writetim)*10)/(sum(phyblkwrt)+1))*(sum(phyblkwrt))) / (sum(phyblkrd) + sum(phyblkwrt) + 1) avg_access_time_tot, (sum(phyblkrd) + sum(phyblkwrt)) / (((((sum(readtim)*10)/(sum(phyblkrd)+1))*(sum(phyblkrd)) + ((sum(writetim)*10)/(sum(phyblkwrt)+1))*(sum(phyblkwrt))) + 1)) * 1000 avg_access_speed from v$filestat, dba_data_files where file_id = file#;
select substr(name,1,instr(name,'/',-1))substr(name,1,instr(name,'\',-1)) file_system_name, sum(bytes)/(1024*1024) MBytes, sum(phyblkrd)/1000 phyblkrd, sum(phyblkwrt)/1000 phyblkwrt, avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec, avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec, (avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) / (sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1) avg_access_time, ((sum(phyblkrd) + sum(phyblkwrt)) / (avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000 avg_access_speed from v$datafile a, v$filestat b where a.file# = b.file# group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1)) order by 7 desc, 6 desc;
select t.tablespace_name, sum(bytes)/(1024*1024) MBytes, sum(phyblkrd)/1000 phyblkrd, sum(phyblkwrt)/1000 phyblkwrt, avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec, avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec, (avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) / (sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1) avg_access_time, ((sum(phyblkrd) + sum(phyblkwrt)) / (avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000 avg_access_speed from dba_tablespaces t, v$filestat, dba_data_files d where t.tablespace_name = d.tablespace_name and d.file_id = file# group by t.tablespace_name, t.status order by 7 desc, 6 desc;
select file_name, bytes/(1024*1024) MBytes, phyblkrd/1000 phyblkrd, phyblkwrt/1000 phyblkwrt, (readtim*10)/(phyblkrd+1) read_time_per_block_msec, (writetim*10)/(phyblkwrt+1) write_time_per_block_msec, (((readtim*10)/(phyblkrd+1))*(phyblkrd) + ((writetim*10)/(phyblkwrt+1))*(phyblkwrt)) / (phyblkrd + phyblkwrt + 1) avg_access_time, (phyblkrd + phyblkwrt) / (((((readtim*10)/(phyblkrd+1))*(phyblkrd) + ((writetim*10)/(phyblkwrt+1))*(phyblkwrt)) + 1)) * 1000 avg_access_speed from v$filestat, dba_data_files where file_id = file# order by 7 desc, 6 desc;
prompt
spool off;-------------------------------------------------------------------------------------------------------