';
if((top5names.match('buffer busy waits')&&top5names.match('cache buffers chain'))||(inst_efficiency.buffernowait<98&&top5names.match('cache buffers chain')))
x++, loadProfile_html+='| '+getComment('load',1)+' |
';
if(top5names.match('buffer busy waits')&&(top5names.match('db file sequential read')||top5names.match('db file scattered read')))
x++, loadProfile_html+='| '+getComment('load',2)+' |
';
/****** High Logical Reads ******/
if(load_profile.logreads>20000&&top5names.match('CPU time'))
x++, loadProfile_html+='| '+getComment('load',3,comma(load_profile.logreads))+' |
';
/****** Optimizer Cost Model ******/
if(load_profile.logreads>20000&&optimizer_cost_model=='cpu'&&db_info.version>=10100&&!top5names.match('CPU time'))
x++, loadProfile_html+='| '+getComment('load',4)+' |
';
/****** High Physical Reads ******/
if(load_profile.reads>500&&(top5names.match('db file sequential read')||top5names.match('db file scattered read'))){
x++, loadProfile_html+='| '+getComment('load',5,comma(load_profile.reads),topReads)+' |
';
/****** Average Disk Read Speed ******/
if(topReads!='.')
x++, loadProfile_html+='| '+getComment('load',6,topReads)+' |
';
}
/****** High Physical Writes ******/
if(load_profile.writes>100||top5names.match('db file parallel write')){
x++, loadProfile_html+='| '+getComment('load',7,comma(load_profile.writes))+' |
';
/****** Long IO Times ******/
if(topWrites!='.')
x++, loadProfile_html+='| '+getComment('load',8,topWrites)+' |
';
}
/****** High Hard Parses ******/
if(pct_total_library>5||pct_shared_pool>3||pct_hard_parse>10){
x++, loadProfile_html+='| '+getComment('load',10,comma(parse_count_hard))+' |
';
load_profile.parseAlert = 1;
/****** Cursor Sharing = "Exact" ******/
if(cursor_sharing=='exact')
x++, loadProfile_html+='| '+getComment('load',11)+' |
';
}
/****** High Soft Parses ******/
if(pct_library_cache>3&&pct_session_cached<=10&&session_cached_cursors<=50){
x++, loadProfile_html+='| '+getComment('load',12)+' |
';
load_profile.parseAlert = 1;
}
/****** High Rollbacks per transaction ******/
if(load_profile.rollback>5)
x++, loadProfile_html+='| '+getComment('load',13,comma(load_profile.rollback))+' |
';
document.getElementById('oracleloadprofile2').style.display="";
document.getElementById('oracleloadprofile2').innerHTML=
''+
'| '+x+' Recommendations: |
'+
loadProfile_html+
'| |
';
/********************************************************************************/
/* Instance Efficiency */
/********************************************************************************/
var efficiency_html = '';
efficiency_html=
''+
'Instance Efficiency |
'+
'| Buffer Hit: | '+inst_efficiency.bufferhit+'% | | In-memory Sort: | '+inst_efficiency.inmemorysort+'% | |
';
/****** Show Memory Usage and Memory for SQL if available ******/
if(inst_efficiency.memoryusage!='?') efficiency_html+=
'| Library Hit: | '+inst_efficiency.libraryhit+'% | | Latch Hit: | '+inst_efficiency.latchhit+'% | |
'+
'| Memory Usage: | '+inst_efficiency.memoryusage+'% | | Memory for SQL: | '+inst_efficiency.memoryforsql+'% | |
'+
'
';
/****** If not available, close box after Library Hit and Latch Hit ******/
else efficiency_html+=
'| Library Hit: | '+inst_efficiency.libraryhit+'% | | Latch Hit: | '+inst_efficiency.latchhit+'% | |
'+
'';
/****** Low Library Hit Percentage ******/
if(inst_efficiency.libraryhit<90)
x++, instEfficiency_html+='| '+getComment('efficiency',1,inst_efficiency.libraryhit)+' |
';
/****** High Disk Sorts ******/
if(inst_efficiency.inmemorysort<99||sorts_disk>.5)
x++, instEfficiency_html+='| '+getComment('efficiency',2,pct_sorts_disk)+' |
';
/****** Low Latch Hit Percentage ******/
if(inst_efficiency.latchhit<95)
x++, instEfficiency_html+='| '+getComment('efficiency',3,inst_efficiency.latchhit)+' |
';
/****** High Shared Pool Memory Usage ******/
if(inst_efficiency.memoryusage>90){
x++, instEfficiency_html+='| '+getComment('efficiency',4,inst_efficiency.memoryusage)+' |
';
/****** High Memory For SQL ******/
if(inst_efficiency.memoryforsql>70)
x++, instEfficiency_html+='| '+getComment('efficiency',5,inst_efficiency.memoryusage,inst_efficiency.memoryforsql)+' |
';
}
/****** Shared Pool Advisory ******/
if(shared_pool.esttimesaved>5)
x++, instEfficiency_html+='| '+getComment('efficiency',6,shared_pool.estobjects,shared_pool.esttimesaved)+' |
';
document.getElementById('oracleinstanceefficiency2').style.display="";
document.getElementById('oracleinstanceefficiency2').innerHTML=
''+
'| '+x+' Recommendations: |
'+
instEfficiency_html+
'| |
';
/********************************************************************************/
/* SQL Statistics */
/********************************************************************************/
if(sql_data.length){
var SQL_data_html = 'SQL Statements |
| Summary: |
| |
';
SQL_data_html += '| Hash Value | Buffer Gets | Physical Reads | Executions | Parse Calls | Rows Processed | Shareable Mem | Version Count | CPU Time | Elapsed | Gets per exec | Reads per exec | Rows per exec | Cpu per exec | Elap per exec | %gets | %reads | %db | %cpu | %elap | %parses | %share | %exec |
';
color = "#FFFFFF";
i = 0;
for(i=0;i'+
sql_data[i].hash+' | '+
sql_data[i].gets+' | '+
sql_data[i].phys_reads+' | '+
sql_data[i].executions+' | '+
sql_data[i].parse_calls+' | '+
sql_data[i].rows_processed+' | '+
sql_data[i].shareable_mem+' | '+
sql_data[i].version_count+' | '+
sql_data[i].cpu_time+' | '+
sql_data[i].elap_time+' | '+
sql_data[i].gets_per_exec+' | '+
sql_data[i].reads_per_exec+' | '+
sql_data[i].rows_per_exec+' | '+
sql_data[i].cpu_per_exec+' | '+
sql_data[i].elap_per_exec+' | '+
sql_data[i].pct_total_gets+' | '+
sql_data[i].pct_total_reads+' | '+
sql_data[i].pct_total_dbtime+' | '+
sql_data[i].pct_total_cpu+' | '+
sql_data[i].pct_total_elap+' | '+
sql_data[i].pct_total_parses+' | '+
sql_data[i].pct_total_shareable+' | '+
sql_data[i].pct_total_exec+' | | '+
sql_data[i].SQL_statement+' |
';
}
SQL_data_html+='
'
document.getElementById('oraclesqlorderedby').style.display="";
document.getElementById('oraclesqlorderedby').innerHTML=
'';
global_SQL_data = SQL_data_html;
}
/********************************************************************************/
/* Wait Events */
/********************************************************************************/
var waitevents_html = '';
var waitEvents_output = '';
length = wait_event.length;
x = 0;
y = 0;
while(x < length && x < 10){
color = "#e9e9e9";
if(!(x%2)) color = "#C0C0C0";
waitevents_html += '| '+wait_event[x].name+' | '+wait_event[x].waits+' | '+wait_event[x].total+' | '+wait_event[x].avg+' | '+wait_event[x].pertxn+' |
';
/****** High Free Buffer Waits ******/
if(wait_event[x].name.match('free buffer waits')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',0,wait_event[x].avg)+' |
';
/****** High Latch Free Waits ******/
else if(wait_event[x].name.match('latch free ')&&noComma(wait_event[x].pertxn)>1)
y++, waitEvents_output+='| '+getComment('wait',1,wait_event[x].pertxn)+' |
';
/****** High Log Buffer Space Waits ******/
else if(wait_event[x].name.match('log buffer space')&&noComma(wait_event[x].pertxn)>1)
y++, waitEvents_output+='| '+getComment('wait',2,wait_event[x].pertxn)+' |
';
/****** High Log File Switch Waits ******/
else if(wait_event[x].name.match('log file switch completion')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',3,wait_event[x].avg)+' |
';
/****** High Log File Sync Waits ******/
else if(wait_event[x].name.match('log file sync')&&noComma(wait_event[x].pertxn)>1)
y++, waitEvents_output+='| '+getComment('wait',4,wait_event[x].pertxn)+' |
';
/****** High SQL*Net message from client ******/
else if(wait_event[x].name.match('message from client')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',5,wait_event[x].avg)+' |
';
/****** High db file sequential read waits ******/
else if(wait_event[x].name.match('db file sequential read')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',6,wait_event[x].avg)+' |
';
/****** High db file scattered read waits ******/
else if(wait_event[x].name.match('db file scattered read')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',7,wait_event[x].avg)+' |
';
/****** High Buffer Busy Waits ******/
else if(wait_event[x].name.match('buffer busy waits')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',8,wait_event[x].avg)+' |
';
/****** High Buffer Busy Waits ******/
else if(wait_event[x].name.match('buffer busy waits')&&noComma(wait_event[x].pertxn)>1)
y++, waitEvents_output+='| '+getComment('wait',9,wait_event[x].pertxn)+' |
';
/****** High Enqueue Waits ******/
else if(wait_event[x].name.match('enqueue')&&noComma(wait_event[x].avg)>5)
y++, waitEvents_output+='| '+getComment('wait',10,wait_event[x].avg)+' |
';
/****** High Enqueue Waits ******/
else if(wait_event[x].name.match('enqueue')&&noComma(wait_event[x].pertxn)>1)
y++, waitEvents_output+='| '+getComment('wait',11,wait_event[x].pertxn)+' |
';
x++;
}
document.getElementById('oraclewaitevents1').style.display="";
document.getElementById('oraclewaitevents1').innerHTML=
''+
'Wait Events |
'+
'| Event | Waits | Wait Time (s) | Avg Wait (ms) | Waits/txn |
'+
waitevents_html+
'
';
document.getElementById('oraclewaitevents2').style.display="";
document.getElementById('oraclewaitevents2').innerHTML=
''+
'| '+y+' Recommendations: |
'+
waitEvents_output+
'| |
';
/********************************************************************************/
/* Instance Activity Stats */
/********************************************************************************/
var stats_html = '';
var instanceActivity_output = '';
key = ['SQL\\*\\Net roundtrips to\/from client','consistent gets ','consistent gets - examination','db block changes','execute count','physical reads ','physical reads direct ','physical writes ','physical writes direct ','redo writes','sorts \\(disk\\)','table fetch continued row','table scans \\(long tables\\)','table scans \\(short tables\\)','workarea executions - onepass','session cursor cache hits','parse count \\(hard\\)','parse count \\(total\\)','sorts \\(memory\\)'];
var key_length = key.length;
length = instance_activity.length;
a = 0;
x = 0;
y = 0;
z = 0;
while(x < length){
color = "#e9e9e9";
if(!(a%2)) color = "#C0C0C0";
temp = instance_activity[x].name;
for(y=0;y'+instance_activity[x].name+' | '+instance_activity[x].total+' | '+instance_activity[x].persec+' | '+instance_activity[x].pertxn+' | ';
y=key_length;
a++;
/****** High SQL Roundtrips ******/
if(instance_activity[x].name.match('roundtrips to\/from client')&&noComma(instance_activity[x].persec)>50)
z++, instanceActivity_output+='| '+getComment('stats',0,instance_activity[x].persec)+' |
';
/****** High Consistent Gets Examination ******/
if(instance_activity[x].name.match('consistent gets - examination')&&noComma(instance_activity[x].persec)>1000)
z++, instanceActivity_output+='| '+getComment('stats',1,instance_activity[x].persec)+' |
';
/****** High DB Block Changes ******/
if(instance_activity[x].name.match('db block changes')&&noComma(instance_activity[x].persec)>50)
z++, instanceActivity_output+='| '+getComment('stats',2,instance_activity[x].persec)+' |
';
/****** High Physical Reads ******/
if(instance_activity[x].name.match('physical reads ')&&noComma(instance_activity[x].persec)>1000)
z++, instanceActivity_output+='| '+getComment('stats',3,instance_activity[x].persec)+' |
';
/****** High Physical Writes ******/
if(instance_activity[x].name.match('physical writes ')&&noComma(instance_activity[x].persec)>50)
z++, instanceActivity_output+='| '+getComment('stats',4,instance_activity[x].persec)+' |
';
/****** High Disk Sorts ******/
if(instance_activity[x].name.match('sorts \\(disk\\)')&&noComma(instance_activity[x].total)>0)
z++, instanceActivity_output+='| '+getComment('stats',5,instance_activity[x].total)+' |
';
/****** High Table Fetch Continued Row ******/
if(instance_activity[x].name.match('table fetch continued row')&&noComma(instance_activity[x].total)>1000)
z++, instanceActivity_output+='| '+getComment('stats',6,instance_activity[x].total)+' |
';
/****** High Full Table Scans (long) ******/
if(instance_activity[x].name.match('long tables')&&noComma(instance_activity[x].persec)>.5)
z++, instanceActivity_output+='| '+getComment('stats',7,instance_activity[x].persec)+' |
';
/****** High Full Table Scans (short) ******/
if(instance_activity[x].name.match('short tables')&&noComma(instance_activity[x].persec)>.5)
z++, instanceActivity_output+='| '+getComment('stats',8,instance_activity[x].persec)+' |
';
/****** High Onepass Workarea Executions ******/
if(instance_activity[x].name.match('workarea executions - onepass')&&noComma(instance_activity[x].total)>50)
z++, instanceActivity_output+='| '+getComment('stats',9,instance_activity[x].total)+' |
';
}
}
x++;
}
document.getElementById('oracleinstanceactivitystats1').style.display="";
document.getElementById('oracleinstanceactivitystats1').innerHTML=
''+
'Instance Activity Stats |
'+
'| Statistic | Total | per Second | per Trans |
'+
stats_html+
'
';
document.getElementById('oracleinstanceactivitystats2').style.display="";
document.getElementById('oracleinstanceactivitystats2').innerHTML=
''+
'| '+z+' Recommendations: |
'+
instanceActivity_output+
'| |
';
/********************************************************************************/
/* Latch Activity */
/********************************************************************************/
var latch_html = '';
var latchActivity_output = '';
length = latch_activity.length;
a = 0;
x = 0;
y = 0;
while(x < length){
color = "#e9e9e9";
if(!(a%2)) color = "#C0C0C0";
if((db_info.version>=9010&&latch_activity[x].waittime>0&&latch_activity[x].getmiss>0.0)||(db_info.version<9010&&latch_activity[x].getmiss>0.1)){
latch_html += '| '+latch_activity[x].name+' | '+latch_activity[x].getreq+' | '+latch_activity[x].getmiss+' | '+latch_activity[x].nowaitmiss+' | '+latch_activity[x].waittime+' |
';
a++;
/****** High Cache Buffer Chain Waits ******/
if(latch_activity[x].name.match('cache buffers chains'))
y++, latchActivity_output+='| '+getComment('latch',0,latch_activity[x].getmiss)+' |
';
/****** High Cache Buffer LRU Chain Waits ******/
if(latch_activity[x].name.match('cache buffers lru chain'))
y++, latchActivity_output+='| '+getComment('latch',1,latch_activity[x].getmiss)+' |
';
/****** High Library Cache Waits ******/
if(latch_activity[x].name.match('library cache '))
y++, latchActivity_output+='| '+getComment('latch',2,latch_activity[x].getmiss)+' |
';
/****** High Redo Allocation Misses ******/
if(latch_activity[x].name.match('redo allocation'))
y++, latchActivity_output+='| '+getComment('latch',3,latch_activity[x].getmiss)+' |
';
/****** High Redo Copy Latches ******/
if(latch_activity[x].name.match('redo copy'))
y++, latchActivity_output+='| '+getComment('latch',4,latch_activity[x].getmiss)+' |
';
/****** High Row Cache Object Latches ******/
if(latch_activity[x].name.match('row cache objects'))
y++, latchActivity_output+='| '+getComment('latch',5,latch_activity[x].getmiss)+' |
';
}
x++;
}
if(latch_html!=''){
document.getElementById('oraclelatchactivity1').style.display="";
document.getElementById('oraclelatchactivity1').innerHTML=
''+
'Latch Activity |
'+
'| Latch | Get Requests | % Get Miss | % NoWait Miss | Wait Time (s) |
'+
latch_html+
'
';
document.getElementById('oraclelatchactivity2').style.display="";
document.getElementById('oraclelatchactivity2').innerHTML=
''+
'| '+y+' Recommendations: |
'+
latchActivity_output+
'| |
';
}
/********************************************************************************/
/* Buffer Pool Advisory */
/********************************************************************************/
/****** Calculate percent decrease in physical disk reads ******/
temp = Math.round(10000-(10000*buffer_pool.double/buffer_pool.current))/100;
/****** Display message if difference is greater than 5% ******/
if(temp>5){
document.getElementById('oraclebufferpooladvisory').style.display="";
document.getElementById('oraclebufferpooladvisory').innerHTML=
''+
'Buffer Pool Advisory |
'+
'| Current: | '+comma(buffer_pool.current)+' disk reads | |
'+
'| Optimized: | '+comma(buffer_pool.double)+' disk reads | |
'+
'| Improvement: | '+temp+'% fewer | |
'+
'
'+
''+
'| '+getComment('buffer',0,comma(buffer_pool.current),comma(buffer_pool.double),temp)+' |
'+
'| |
';
}
/********************************************************************************/
/* PGA Memory Advisory */
/********************************************************************************/
//temp = Math.round((1000*pga_memory.double/pga_memory.current)-1000)/100;
temp = pga_memory.double - pga_memory.current;
if(temp>5){
document.getElementById('oraclepgamemoryadvisory').style.display="";
document.getElementById('oraclepgamemoryadvisory').innerHTML=
''+
'PGA Memory Advisory |
'+
'| Current: | '+pga_memory.current+'% cache hit | |
'+
'| Optimized: | '+pga_memory.double+'% cache hit | |
'+
'| Improvement: | '+temp+'% more | |
'+
'
'+
''+
'| '+getComment('pga',0,pga_memory.current,pga_memory.double,temp)+' |
'+
'| |
';
}
/********************************************************************************/
/* Shared Pool Advisor */
/********************************************************************************/
//temp = Math.round((100*shared_pool.doubleobjects/shared_pool.currentobjects)-100);
//temp2 = Math.round(100*((100*shared_pool.doubletime/shared_pool.currenttime)-100))/100;
if(shared_pool.esttimesaved>5){
document.getElementById('oraclesharedpooladvisory').style.display="";
document.getElementById('oraclesharedpooladvisory').innerHTML=
''+
'Shared Pool Advisor |
'+
'| Current: | '+comma(shared_pool.currentobjects)+' cached objects | '+comma(shared_pool.currenttime)+' seconds saved | |
'+
'| Optimized: | '+comma(shared_pool.doubleobjects)+' cached objects | '+comma(shared_pool.doubletime)+' seconds saved | |
'+
'| Improvement: | '+shared_pool.estobjects+'% more | '+shared_pool.esttimesaved+'% more | |
'+
'
'+
''+
'| '+getComment('shared',0,comma(shared_pool.currentobjects),shared_pool.estobjects,shared_pool.esttimesaved)+' |
'+
'| |
';
}
/********************************************************************************/
/* init.ora Parameters */
/********************************************************************************/
var parameters_html = '';
var initParam_output = '';
key = ['buffer_pool_keep','db_16k_cache_size','db_32k_cache_size','db_block_size','db_cache_size','db_file_multiblock_read_count','db_keep_cache_size','hash_area_size','hash_join_enabled','mts_servers','optimizer_index_caching','optimizer_index_cost_adj','optimizer_mode','parallel_automatic_tuning','pga_aggregate_target','query_rewrite_enabled','shared_pool_size','shared_servers','sort_area_size','workarea_size_policy','session_cached_cursors','cursor_sharing','_optimizer_cost_model','dblink_encrypt_login','db_block_buffers','global_context_pool_size','log_archive_start','log_parallelism','max_enabled_roles','max_rollback_segments','mts_circuits','mts_dispatchers','mts_listener_address','mts_max_dispatchers','mts_max_servers','mts_multiple_listeners','mts_service','mts_sessions','optimizer_max_permutations','oracle_trace_collection_path','oracle_trace_collection_size','oracle_trace_enable','oracle_trace_facility_name','oracle_trace_facility_path','partition_view_enabled','plsql_compiler_flags','row_locking','serializable','transaction_auditing','undo_suppress_errors'];
key_length = key.length;
length = init_param.length;
a = 0;
x = 0;
y = 0;
z = 0;
while(x < length){
temp = init_param[x].name;
for(y=0;y'+init_param[x].name+''+byteFormat(init_param[x].value)+' | | '; //byteFormat takes number of blocks and formats into Byte size incorrectly when reading db_block_buffers
y=key_length;
a++
}
}
x++;
}
document.getElementById('oracleparameters1').style.display="";
document.getElementById('oracleparameters1').innerHTML=
''+
'Init.ora Parameters | |
'+
'| Parameter | Value | |
'+
parameters_html+
'
';
var obsoleteParam = '';
x = 0;
if(MI.obsolete[0]){
while(x=9010)
z++, initParam_output+='| '+getComment('param',0,0)+' |
';
/****** Small DB Cache ******/
if(MI.dbcachesize&&init_param[MI.dbcachesize].value<536870912&&top5names.match('buffer busy waits'))
z++, initParam_output+='| '+getComment('param',1,byteFormat(init_param[MI.dbcachesize].value))+' |
';
/****** DB File Multiblock Read Count ******/
if(MI.dbmultiblockreadcount&&init_param[MI.dbmultiblockreadcount].value==16&&db_info.version<10100&&top5names.match('db file scattered read'))
z++, initParam_output+='| '+getComment('param',2,0)+' |
';
/****** KEEP Pool ******/
if((!MI.dbkeepcachesize&&db_info.version>=9010)||(!MI.bufferpoolkeep&&db_info.version<9010))
z++, initParam_output+='| '+getComment('param',3,0)+' |
';
/****** Hash Area Size ******/
if(MI.hashareasize&&init_param[MI.hashareasize].value<1048576&&!MI.pgaaggregatetarget&&!MI.mtsservers&&!MI.sharedservers)
z++, initParam_output+='| '+getComment('param',4,byteFormat(init_param[MI.hashareasize].value))+' |
';
/****** Hash Joins ******/
if(MI.hashjoinenabled&&init_param[MI.hashjoinenabled].value.match('false'))
z++, initParam_output+='| '+getComment('param',5,0)+' |
';
/****** Multi-threaded Server with PGA Aggregate Target ******/
if((MI.mtsservers||MI.sharedservers)&&MI.pgaaggregatetarget&&db_info.version<10100)
z++, initParam_output+='| '+getComment('param',6,0)+' |
';
/****** Multi-threaded Server ******/
if(MI.mtsservers||MI.sharedservers)
z++, initParam_output+='| '+getComment('param',7,0)+' |
';
/****** Optimizer Index Caching ******/
if(!MI.optimizerindexcaching)
z++, initParam_output+='| '+getComment('param',8,0)+' |
';
/****** Optimizer Index Caching ******/
if(MI.optimizerindexcaching&&init_param[MI.optimizerindexcaching].value==0)
z++, initParam_output+='| '+getComment('param',9,0)+' |
';
/****** Optimizer Index Cost Adj ******/
if(MI.optimizerindexcostadj&&init_param[MI.optimizerindexcostadj].value==100)
z++, initParam_output+='| '+getComment('param',10,0)+' |
';
/****** Optimizer Index Cost Adj ******/
if(!MI.optimizerindexcostadj&&MI.optimizerindexcaching)
z++, initParam_output+='| '+getComment('param',11,0)+' |
';
/****** Optimizer Mode ******/
if(MI.optimizermode&&((init_param[MI.optimizermode].value.match('choose')&&db_info.version<10100)||(init_param[MI.optimizermode].value.match('all_rows')&&db_info.version>=10100)))
z++, initParam_output+='| '+getComment('param',12,0)+' |
';
/****** Parallel Automatic Tuning ******/
if(MI.parallelautomatictuning&&init_param[MI.parallelautomatictuning].value.match('true')&&db_info.version<10100)
z++, initParam_output+='| '+getComment('param',13,0)+' |
';
/****** Default Sort Area Size ******/
if(!MI.pgaaggregatetarget&&MI.sortareasize&&init_param[MI.sortareasize].value==65536)
z++, initParam_output+='| '+getComment('param',14,0)+' |
';
/****** PGA Aggregate Target ******/
if(MI.pgaaggregatetarget&&init_param[MI.pgaaggregatetarget].value>2147483648)
z++, initParam_output+='| '+getComment('param',15,byteFormat(init_param[MI.pgaaggregatetarget].value))+' |
';
/****** Query Rewrite Enabled ******/
if(MI.queryrewriteenabled&&init_param[MI.queryrewriteenabled].value.match('false'))
z++, initParam_output+='| '+getComment('param',16,0)+' |
';
/****** Small Shared Pool ******/
if(MI.sharedpoolsize&&init_param[MI.sharedpoolsize].value<102400)
z++, initParam_output+='| '+getComment('param',17,byteFormat(init_param[MI.sharedpoolsize].value))+' |
';
/****** Large Shared Pool ******/
if(MI.sharedpoolsize&&init_param[MI.sharedpoolsize].value>209715200&&!load_profile.parseAlert&&shared_pool.esttimesaved<=5)
z++, initParam_output+='| '+getComment('param',18,byteFormat(init_param[MI.sharedpoolsize].value))+' |
';
/****** Small Sort Area Size ******/
if(MI.sortareasize&&init_param[MI.sortareasize].value<512000&&init_param[MI.sortareasize].value!=65536&&!MI.pgaaggregatetarget&&!MI.mtsservers&&!MI.sharedservers)
z++, initParam_output+='| '+getComment('param',19,byteFormat(init_param[MI.sortareasize].value))+' |
';
/****** Sort Area Size and PGA Aggregate Target ******/
if(MI.sortareasize&&MI.pgaaggregatetarget&&MI.workareasizepolicy&&init_param[MI.workareasizepolicy].value.match('auto')&&!MI.mtsservers&&!MI.sharedservers)
z++, initParam_output+='| '+getComment('param',20,0)+' |
';
/****** Obsolete Parameters ******/
if(MI.obsolete[0])
z++, initParam_output+='| '+getComment('param',21,obsoleteParam)+' |
';
document.getElementById('oracleparameters2').style.display="";
document.getElementById('oracleparameters2').innerHTML=
''+
'| '+z+' Recommendations: |
'+
initParam_output+
'| |
';
}
function byteFormat(num){
if(num%1024) return comma(num);
if(num>=1073741824) return comma(Math.round(100*num/1073741824)/100)+'GB';
if(num>=1048576) return comma(Math.round(100*num/1048576)/100)+'MB';
if(num>8192) return comma(Math.round(100*num/1024)/100)+'KB';
if(num<=8192) return comma(num);
return num;
}
function tableSort1(a,b){
if(a.time < b.time) return 1;
if(a.time > b.time) return -1;
return 0;
}
function tableSort2(a,b){
if(a.reads < b.reads) return 1;
if(a.reads > b.reads) return -1;
return 0;
}
function tableSort3(a,b){
if(a.writes < b.writes) return 1;
if(a.writes > b.writes) return -1;
return 0;
}
function showhide(x){
if(x==0){
document.getElementById('oracletop5timedevents1').style.display="none"
document.getElementById('oracletop5timedevents2').style.display="";
}
if(x==1){
document.getElementById('oracletop5timedevents1').style.display="";
document.getElementById('oracletop5timedevents2').style.display="none"
}
if(x==2){
document.getElementById('oracletablespace1').style.display="none"
document.getElementById('oracletablespace2').style.display="";
}
if(x==3){
document.getElementById('oracletablespace1').style.display="";
document.getElementById('oracletablespace2').style.display="none"
}
}
function comma(num)
{
var x,x1,x2,rgx;
num+=''; //convert number to string
x = num.split('.');
x1 = x[0];
x2 = x.length>1 ? '.' +x [1] : '';
rgx = /(\d+)(\d{3})/;
while(rgx.test(x1)) x1 = x1.replace(rgx, '$1' + ',' + '$2');
return x1+x2;
}
function sql_print(){
var newWin = window.open('','printable','width=800,height=600,scrollbars=1,resizable=1,menubar=1,toolbar=1,status=1');
newWin.document.open();
newWin.document.write('Statspack Analyzer'+global_SQL_data);
newWin.document.close();
}
function getComment(sec,indx,val,val2,val3){
/*******************Load Profile*****************/
/************************************************/
var LP = new Array();
/****** High Buffer Contention ******/
LP[0]='You have high contention for LRU latches, which are necessary to move blocks in and out of the buffer cache. You may want to consider setting the hidden Oracle parameter _db_block_lru_latches with the assistance of Oracle Support.';
LP[1]='You have a high buffer wait coupled with a high occurrence of cache buffers chains latches. This can be caused by contention for hot block updates within the buffer. Identify the data block (using v$bh or v$system_event) and reduce contention by increasing freelists or freelist groups on the object.';
LP[2]='It appears you have a large amount of waiting on both the physical I/O layer and in the SGA. You may want to consider tuning your SQL using broad brush techniques in order to encourage index use. In a well tuned system, you may want to consider moving to solid state disk in order to decrease the amount of waiting Oracle must do for disk resources.';
/****** High Logical IO ******/
LP[3]='Your database has relatively high logical I/O at '+val+' reads per second. Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity. CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking), using faster CPUs or adding more CPUs to your system.';
/****** Optimizer Cost Model ******/
LP[4]='Consider unsetting your CPU-based optimizer costing (the 10g default, a change from 9i). CPU costing is best if you see CPU in your Top 5 Timed Events in your STATSPACK/AWR report, and the 10g default of _optimizer_cost_model=cpu will try to minimize CPU by invoking more full scans, especially in tablespaces with large blocksizes. To return to your 9i CBO I/O-based costing, set the hidden parameter _optimizer_cost_model=io';
/****** High Physical Reads ******/
LP[5]='You are performing more than '+val+' disk reads per second. High disk latency can be caused by too-few physical disk spindles. Compare your read times across multiple datafiles to see which datafiles are slower than others. Disk read times may be improved if contention is reduced on the datafile, even though read times may be high due to the file residing on a slow disk. You should identify whether the SQL accessing the file can be tuned, as well as the underlying characteristics of the hardware devices.';
/****** Average Disk Read Speed ******/
LP[6]='Check your average disk read speed later in this report and ensure that it is under 7ms. Assuming that the SQL is optimized, the only remaining solutions are the addition of RAM for the data buffers or a switch to solid state disks. Give careful consideration these tablespaces with high read I/O: '+val;
/****** High Physical Writes ******/
LP[7]='You are performing more than '+val+' disk writes per second. Check to ensure that you are using direct I/O (non buffer) on your database and perform an IO balance and an IO timing analysis to determine if a disk or array is undergoing IO related stress, and verify your DBWR optimization. Check you average disk read speed later in this report and ensure that it is under 7 milliseconds. If not, consider SSD for faster physical reads.';
/****** Long IO Times ******/
LP[8]='Long I/O times are occuring when using the following tablespaces: '+val+' Consider placing these tablespace´s datafiles on solid state disk for faster disk access.';
/****** High Parses ******/
LP[9]='You are performing more than '+val+' SQL parses per second. A parse is the process of executing your SQL, checking for proper security authorization, checking for the existence of tables, columns, and other referenced objects, and generating an execution plan. Your high parses suggest that your system has many incoming unique SQL statements or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). Confirm that the '+val+' parses per second is reasonable and consider setting cursor_sharing=force if warranted. Setting cursor_sharing=force can cause dramatic performance improvements for systems with ad-hoc query tools such as Crystal Reports or Business Objects.';
/****** High Hard Parses ******/
LP[10]='You have more than '+val+' unique SQL statements entering your shared pool, with the resulting overhead of continuous RAM allocation and freeing within the shared pool. A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out) or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.';
/****** Cursor Sharing = "Exact" ******/
LP[11]='Set the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.';
/****** High Soft Parses ******/
LP[12]='You have a large number of soft parses that are not being cached by your user’s sessions. This behavior may be unavoidable depending on your SQL usage; however, if the code being executed against this database uses individual queries multiple times in a single session, the issue can be alleviated by setting the session cached cursors parameter. Consider benchmarking and testing an increase of the SESSION_CACHED_CURSORS parameter.';
/****** High Rollbacks ******/
LP[13]='You may have an application issue causing excessive rollbacks with '+val+'% rollbacks per transaction. Due to Oracle´s assumption of a commit, the rollback process is very expensive and should only be used when necessary. You can identify the specific SQL and user session that is executing the rollbacks by querying the v$sesstat view.
Remember that some applications may automatically perform rollback operations (commit-then-rollback or rollback-then-exit) after each commit. If this is the case, speak with your application developers to find out if there is a way to disable this. While these "empty rollbacks" do not incur performance expense, it will case this metric to appear very high.';
/*******************Instance Efficiency*****************/
/*******************************************************/
var IE = new Array();
/****** Low Buffer Hit Percentage ******/
IE[0]='Your Buffer Hit ratio is '+val+'%. The buffer hit ratio measures the probability that a data block will be in the buffer cache upon a re-read of the data block. If your database has a large number of frequently referenced table rows (a large working set), then investigate increasing your db_cache_size. For specific recommendations, see the output from the data buffer cache advisory utility (using the v$db_cache_advice utility). Also, a low buffer hit ratio is normal for applications that do not frequently re-read the same data blocks. Moving to SSD will alleviate the need for a large data buffer cache.';
/****** Low Library Hit Percentage ******/
IE[1]='Your Library Hit ratio is '+val+'%. A low library cache hit percentage could mean SQL is prematurely aging out of the shared pool as the shared pool may be small or that unsharable SQL is being used. In addition, compare it with the soft parse ratio. If they are both low, then investigate whether there is a parsing issue. Since you never know in-advance how many SQL statements need to be cached, the Oracle DBA must set shared_pool_size large enough to prevent excessive re-parsing of SQL.';
/****** High Disk Sorts ******/
IE[2]='You have more than '+val+'% of sorts being sorted to disk, which can result in a significant delay due to the physical writes and reads. You can see the impact of this later in the report as reads and writes on the temporary tablespace. If the sorts to disk are not large, increasing the pga_aggregate_target may allow Oracle to perform the sort in memory. If the application sorts are large and you must perform disk sorts, moving the temporary tablespace to SSD will improve large sort performance.';
/****** Low Latch Hit Percentage ******/
IE[3]='Your latch hit percentage is '+val+'%, below the recommended value of 99%. Investigate the specific latches in your latch activity section and tune to reduce library cache contention.';
/****** High Shared Pool Memory Usage ******/
IE[4]='Your shared pool maybe filled with non-reusable SQL with '+val+'% memory usage. The Oracle shared pool contains Oracle´s library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. You can check the dba_hist_librarycache table in Oracle10g to see your historical library cache RAM usage.';
/****** High Memory For SQL ******/
IE[5]='Your Shared Pool Memory Usage is '+val+'% and "% Memory for SQL w/exec>1" is '+val2+'%. Your shared pool size might need increasing by adjusting your shared_pool_size parameter. If server memory is available, allocate more RAM to the shared pool.';
/****** Shared Pool Advisory ******/
IE[6]='Shared pool statistics indicate that you may improve upon this statistic with a larger shared pool. Please consult the Shared Pool Advisory for details.';
/*******************Top 5 Timed Events*****************/ //definitions are on two lines, separated by | character
/******************************************************/
var T5 = new Array();
/****** CPU Time ******/
T5[0] =
'CPU time is the amount of time that the Oracle database spent processing SQL statements, parsing statements, or managing the buffer cache.'+
'|If this is the main timed event, tuning SQL statements and/or increasing server CPU resources will provide the greatest performance improvement.';
/****** DB File Scattered Read ******/
T5[1] =
'The scattered read events occur when Oracle reads multiple blocks of a table or index. Look at the tablespace IO section of the report for tablespaces with more than 2 average blocks per read, high response time, and a large percentage of the total IO. Improving the response time of these tables with faster storage will help reduce this wait event and speed up the database. Full table scan of a data tables can cause these events.'+
'|Moving the data files with largest amount of time spent waiting on multi-block reads to faster storage can significantly reduce the amount of time spent waiting on this event. By reducing the time spent waiting on this event, the database performance could increase '+Math.round(((1/(1-(val/100)))-1)*100)+'%.';
/****** DB File Sequential Read ******/
T5[2] =
'The sequential read event occurs when Oracle reads single blocks of a table or index. Look at the tablespace IO section of the report for tablespaces with less than 2 average blocks per read, high response time, and a large percentage of the total IO. Improving the response time of these tables with faster storage will help reduce this wait event and speed up the database.'+
'|Moving the data files with the largest amount of time spend waiting on single-block reads to faster storage can significantly reduce the amount of time spent waiting on this event. By reducing the time spent waiting on this event, the database performance could increase '+Math.round(((1/(1-(val/100)))-1)*100)+'%.';
/****** Enqueue ******/
T5[3] =
'High enqueue waits are related to Oracle internal locks.'+
'|Investigate by querying the v$lock, v$transaction and v$session views to drill-in and see the exact queries that are causing the locks.';
/****** SQL*Net Message From Client ******/
T5[4] =
'This event occurs when DB is idle and waiting on the client process. This is a very prevalent wait in an OLTP system, as the client process waits for user input.'+
'|If the user is not the source of these waits, client side software or the network may be causing a bottleneck. If you expect this event due to waiting on the user and response time is still a concern, then you may subtract the percent of time that this event is consuming from the wait events. Addressing the other wait events will generate a disproportionate gain to the end user than its wait percentage would suggest.';
/****** Control File Sequential Read ******/
T5[5] =
'The control file sequential read Oracle metric indicates the process is waiting for blocks to be read from a control file.'+
'|This happens in many cases. For example, you can see a "control file sequential read" when you are making a backup of the controlfiles, sharing information (between instances) from the controlfile, reading other blocks from the controlfiles, and most importantly, when you are reading the header block for a data file.';
/****** Control File Parallel Write ******/
T5[6] =
'This wait is caused by waiting on writes to the control files.'+
'|Moving the control files on to solid state disks can help alleviate this wait.';
/****** Direct Path Read ******/
T5[7] =
'Reads that skip the database buffer cause this wait event. If there are many sorts and hashes taking place, then slow access to the TEMP space can cause this.'+
'|Check the top IO tablespaces below. If the list includes the TEMP space, then there is a good chance that moving the TEMP space to solid state disks can help reduce this event.';
/****** Direct Path Write ******/
T5[8] =
'Writes that skip the database buffer cause this wait event. If there are many sorts and hashes taking place, then slow access to the TEMP space can cause this.'+
'|Check the top IO tablespaces below. If the list includes the TEMP space, then there is a good chance that moving the TEMP space to solid state disks can help reduce this event.';
/****** Log File Parallel Write ******/
T5[9] =
'This event occurs when Oracle is waiting for the completion of writes to the redo log files.'+
'|Moving some or all copies of your redo logs logs to solid state disks can reduce the amount of time spent waiting for this event.';
/****** Log File Sync ******/
T5[10] =
'This event is caused by waiting for the LGWR to post after a session performs a commit. This can be tuned by reducing the number of commits.'+
'|Moving some or all copies of your redo logs logs to solid state disks can reduce the amount of time spent waiting for this event.';
/****** Log File Single Write ******/
T5[11] =
'This event occurs when Oracle is waiting for the completion of writes to the redo log files.'+
'|Moving some or all copies of your redo logs to solid state disks can reduce the amount of time spent waiting for this event.';
/****** Free Buffer Wait ******/
T5[12] =
'This wait occurs when a session needs a free buffer and cannot find one. This can be caused by a slow DBWR process that cannot quickly flush dirty blocks from the buffer cache.'+
'|Moving the files that receive the majority of the writes to solid state disks can help to alleviate the wait event. If poor I/O write capacity does not cause this wait, you can tune your instance by increasing the buffer cache.';
/****** Buffer Busy Waits ******/
T5[13] =
'The primary cause of these waits is contention for a block that is being used in an non-sharable way (so that a read/write cannot be performed until the process that is using it is complete).'+
'|Decrease this event by increasing the free lists for the offending file, reducing the number of rows in a block (to reduce the contention for a block), or increasing the speed of the disk system (especially in cases where a slow DBWR process is causing some of the contention.)';
/****** DB File Parallel Write ******/
T5[14] =
'The DBWR process produces this wait event as it writes dirty blocks to the datafiles. This event can cause poor read performance, and the writes may interfere with reads from the data files.'+
'|Moving the tables that are experiencing the highest write activity to solid state disks may help to alleviate this wait event.';
/****** DB File Single Write ******/
T5[15] =
'This wait event occurs during checkpoints as data file headers are updated.'+
'|You can alleviate this wait by reducing the number of data files or checkpoints. If the checkpoints and data files are required, then solid state disks can help alleviate this event.';
/****** Log Buffer Space ******/
T5[16] =
'This wait event occurs when an application generates redo information faster than it can be written to the log files. Contention on the disks where the log files are stored can cause this event.'+
'|Alleviate this event by moving the log files to a storage device with a high bandwidth, such as solid state disks .';
/****** Log File Sequential Read ******/
T5[17] =
'This wait event often appears when the ARCH process waits to read from the redo log files.'+
'|Increasing the number of redo log groups or increasing the performance of the archive storage can help alleviate this event. If the archive storage device is not the bottleneck, placing all of the redo log groups onto solid state disks can help alleviate this event.';
/****** Log File Switch (archiving needed) ******/
T5[18] =
'This wait event often appears when the ARCH process cannot archive the redo logs as fast as the LGWR process creates them.'+
'|To help alleviate this event, focus on increasing the performance of the archive storage.';
/****** Log File Switch (checkpoint incomplete) ******/
T5[19] =
'Log file switch (checkpoint incomplete) occurs when a log file switch needs to take place, but the checkpoint for the log file to be switched has not completed.'+
'|Alleviate this wait by increasing the size of the redo logs or by increasing the number of log groups. Alternatively, increase the speed of completing checkpoints by improving the write speed to the data files. You can accomplish this by moving the data files with the highest write activity to solid state disks .';
/****** Global Cache CR Request ******/
T5[20] =
'Waits for this event are caused when a local instance of RAC needs a buffer in a remote instance.'+
'|In some cases, you can tune this event by increasing the speed of the high-speed interconnect. Alternatively, you can alleviate this wait event by reducing the buffer cache size of each of the servers in a RAC cluster to make it less likely for the remote instance to contain a buffer that the local instance requires. This setup is only recommended if the most accessed table spaces can be moved to solid state disks , where the access time to the storage is significantly faster than the high-speed interconnect.';
/****** Global CR Request Wait ******/
T5[21] =
'Waits for event are caused when a local instance of RAC needs a buffer in a remote instance.'+
'|In some cases, you can tune this event by increasing the speed of the high-speed interconnect. Alternatively, you can alleviate this wait event by reducing the buffer cache size of each of the servers in a RAC cluster to make it less likely for the remote instance to contain a buffer that the local instance requires. This setup is only recommended if the most accessed table spaces can be moved to solid state disks , where the access time to the storage is significantly faster than the high-speed interconnect.';
/****** Buffer Busy Global Cache ******/
T5[22] =
'This wait occurs when a local instance wants to modify a buffer that a remote instance has cached. This event may point to a bottleneck in the high speed interconnect.'+
'|Attack this wait by employing solid state disks for the most active tables (for writes) in the database and by lowering the buffer cache. This approach increases overall throughput because the penalty for physical IO to a solid state disk is lower than the penalty for fetching a block from the remote instance.';
/****** Global Cache Null to x ******/
T5[23] =
'This wait occurs when a session wants to modify a block that is either resident in a remote instance buffer cache or is outside the GCS and requires a physical read from disk.'+
'|A faster IO system, such as a solid state disk , can help improve the physical IO time.';
/****** Global Cache Null to s ******/
T5[24] =
'This wait occurs when a session wants to read a block that is either resident in a remote instance buffer cache or is outside the GCS and requires a physical read from disk.'+
'|A faster IO system, such as a solid state disk , can help improve the physical IO time.';
/****** SQL*Net Message To Client ******/
T5[25] =
'This wait event is generated by waiting for a response from a client after a message is sent.'+
'|This could point to a network bottleneck or inadequate client computing resources.';
T5[26] =
'Other | ';
/**********************Wait Events*********************/
/******************************************************/
var WE = new Array();
/****** Free Buffer Avg Wait Time ******/
WE[0]='You have high free buffer wait time of '+val+' milliseconds. Free buffer waits commonly happen when your application is insert intensive (among many other factors), and Oracle requests a new block from the freelist. With a free buffer wait, Oracle requests RAM heap space for the new block but no space is available within the data buffer cache region. The remedy is tuning your data buffer cache which might include using faster disks, rebuilding with larger blocksizes, tuning your DBWR process, segregating hot tables into separate data buffers (using the multiple blocksize feature), optimizing your SQL to reduce data block requests (using highly-selective function-based indexes or materialized views) or by increasing the speed of your back-end disks. You can also optimize your db_cache_size and moving the hot objects to high-speed solid state disks .';
/****** Latch Free Waits/Txn ******/
WE[1]='You have high latch free waits of '+val+' per transaction. The latch free wait occurs when the process is waiting for a latch held by another process. Check the later section for the specific latch waits. Latch free waits are usually due to SQL without bind variables, but buffer chains and redo generation can also cause them.';
/****** Log Buffer Space Waits/Txn ******/
WE[2]='You have a high value for log buffer space waits at '+val+' per transaction. If your log_buffer is too small then high log buffer space waits will be seen during high DML periods (redo generation). Consider moving your redo log data files to solid state disk or increase the size of your redo log buffer (log_buffer).';
/****** Log File Switch Completion Avg Wait Time ******/
WE[3]='You have a high value for log file switch waits at '+val+' milliseconds. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing. Consider allocating larger online redo log files or move your redo logs to faster disk with SSD .';
/****** High Log File Sync Waits ******/
WE[4]='You have a high value for log file sync waits at '+val+' per transaction. Check to ensure that your application does frequent commits and consider moving your redo log files to faster SSD disk. Also consider increasing your log_buffer size.';
/****** High SQL*Net message from client ******/
WE[5]='Your average wait time for SQL*Net message from client events is '+val+' milliseconds. Check your application to see if it might benefit from bulk collection (using PL/SQL "forall" or "bulk collect" operators. In addition, optimize your TNS settings in your tnsnames.ora file and investigate consolidating your Oracle requests into larger TNS packets.';
/****** High db file sequential read waits ******/
WE[6]='Your average wait time for db file sequential read is '+val+' milliseconds, which is very slow. The sequential read event occurs when Oracle reads single blocks of a table or index. Index reads or single block reads typically cause this event. In addition, check to ensure that you are using non-buffered direct I/O. If these steps fail to reduce your disk wait times, moving some of your datafiles to solid state disk will reduce the amount of time spent waiting for this event.';
/****** High db file scattered read waits ******/
WE[7]='Your average wait time for db file scattered read is '+val+' milliseconds, which is very slow. The scattered read events occur when Oracle reads multiple blocks of a table or index (a full table scan of fast full index scan). Full-table scans may indicate missing indexes. Check for missing indexes and verify that you have optimized your db_file_multiblock_read_count. If these steps fail to reduce your disk wait times, moving some of your datafiles to solid state disk will reduce the amount of time spent waiting for scattered disk reads.';
/****** High Buffer Busy Waits ******/
WE[8]='You have excessive buffer busy waits at '+val+' milliseconds. Buffer busy waits are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition. Using super-fast SSD can dramatically reduce wait times for other reads and in some cases lessen the buffer busy waits.';
/****** High Buffer Busy Waits ******/
WE[9]='You have excessive buffer busy waits with '+val+' per transaction. Buffer busy waits are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition. Using super-fast SSD can dramatically reduce wait times for other reads and in some cases lessen the buffer busy waits.';
/****** High Enqueue Waits ******/
WE[10]='You have excessive enqueue wait times at '+val+' milliseconds. Oracle locks protect shared resources and allow access to those resources via a queuing mechanism. A large amount of time spent waiting for enqueue events can be caused by various problems, such as waiting for individual row locks or waiting for exclusive locks on a table. Ensure that you are using locally-managed tables (if you see enqueue ST waits) and review your settings for INITRANS and MAXTRANS n(if you have enqueue TX waits). If you see enqueue TX waits, check for DML locks and ensure that all foreign keys are indexed.';
/****** High Enqueue Waits ******/
WE[11]='You have excessive enqueue wait times with '+val+' per transaction. Oracle locks protect shared resources and allow access to those resources via a queuing mechanism. A large amount of time spent waiting for enqueue events can be caused by various problems, such as waiting for individual row locks or waiting for exclusive locks on a table. Ensure that you are using locally-managed tables (if you see enqueue ST waits) and review your settings for INITRANS and MAXTRANS n(if you have enqueue TX waits). If you see enqueue TX waits, check for DML locks and ensure that all foreign keys are indexed.';
/*******************Instance Activity Stats*****************/
/***********************************************************/
var IA = new Array();
/****** High SQL Roundtrips ******/
IA[0]='You have high network activity with '+val+' SQL*Net roundtrips to/from client per second, which is a high amount of traffic. Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit from bulk collection by using PL/SQL "forall" or "bulk collect" operators.';
/****** High Consistent Gets Examination ******/
IA[1]='You have '+val+' consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce disk reads, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.';
/****** High DB Block Changes ******/
IA[2]='You have high update activity with '+val+' db block changes per second. The DB block changes are a rough indication of total database work. This statistic indicates (on a per-transaction level) the rate at which buffers are being dirtied and you may want to optimize your database writer (DBWR) process. You can determine which sessions and SQL statements have the highest db block changes by querying the v$session and v$sessatst views.';
/****** High Physical Reads ******/
IA[3]='You have high disk reads with '+val+' per second. Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage. You can monitor your physical disk reads by hour of the day using AWR to see when the database has the highest disk activity.';
/****** High Physical Writes ******/
IA[4]='You have high disk write activity with '+val+' per second. You should drill-down and identify the sessions that are performing the disk writes as they can cause locking contention within Oracle. Also investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process. In addition, you can dramatically improve your disk write speed by up to 300 times by moving the high-write datafiles to solid-state disk.';
/****** High Disk Sorts ******/
IA[5]='You have '+val+' disk sorts during this period. Disk sorts are very expensive and increasing your PGA (sort_area_size or pga_aggregate_target) may allow you to perform these sorts in RAM.';
/****** High Table Fetch Continued Row ******/
IA[6]='You have '+val+' table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.';
/****** High Full Table Scans (long) ******/
IA[7]='You have '+val+' long table full-table scans per second. This might indicate missing indexes, and you can run plan9i.sql to identify the specific tables and investigate the SQL to see if an index scan might result in faster execution. If your large table full table scans are legitimate, look at optimizing your db_file_multiblock_read_count parameter.';
/****** High Full Table Scans (short) ******/
IA[8]='You have high small table full-table scans, at '+val+' per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD will significantly increase the speed of small-table full-table scans.';
/****** High Onepass Workarea Executions ******/
IA[9]='You have excessive onepass PGA workarea executions with '+val+' non-optimal executions during this elapsed period. It?s better to have "workarea executions - optimal", and you might consider optimizing your pga_aggregate_target parameter.';
/*******************Tablespace IO Stats*****************/
/*******************************************************/
var IO = new Array();
IO[0]='The above tablespace(s) marked with » have a slow response time. These tablespaces might have disk enqueues, or excessive read-write head movement or disk channel contention might be causing the tablespace delay. Consider distributing the frequently-referenced objects to a less-busy disk or moving the tablespace to a faster storage device such as SSD.';
/*******************Latch Activity*****************/
/**************************************************/
var LA = new Array();
/****** High Cache Buffer Chain Waits ******/
LA[0]='You have high cache buffer chain latches with '+val+'% get miss. See MetaLink about increasing the hidden parameter _db_block_hash_buckets.';
/****** High Cache Buffer LRU Chain Waits ******/
LA[1]='You have a high value for cache buffer LRU chain waits with '+val+'% get miss, and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer. Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE.';
/****** High Library Cache Waits ******/
LA[2]='You have high library cache waits with '+val+'% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.';
/****** High Redo Allocation Misses ******/
LA[3]='You have high redo allocation misses with '+val+'% get miss. The redo allocation latch controls the allocation of space for redo entries in the redo log buffer (as defined by the log_buffer parameter). The redo allocation latch is a serialization latch that enforces the sequence of entries in the log buffer. A process can only write a redo entry after the redo allocation latch, which is why it´s a will-to-wait latch. Investigate increasing your log_buffer parameter.';
/****** High Redo Copy Latches ******/
LA[4]='You have high redo copy latches with '+val+'% get miss. Check MetaLink for increasing the hidden parameter _log_simultaneous_copies.';
/****** High Row Cache Object Latches ******/
LA[5]='You have high row cache object latches with '+val+'% get miss. The row cache objects is a serialization latch that protects the access to the data dictionary cache in the SGA. When loading, referencing and freeing objects in the data dictionary cache you will need to get this latch. Rich Niemiec notes: The row cache objects latch contention usually means there is contention in the data dictionary. This problem may also be a symptom of excessive parsing of SQL statements that depend on public synonyms. Increasing the shared pool usually solves this latch problem. You usually increase the shared pool for a library cache latch problem well before this one is a problem.';
/*******************Buffer Pool Advisory*****************/
/********************************************************/
var BP = new Array();
BP[0]='The Oracle buffer cache advisory utility indicates '+val+' disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to '+val2+', a '+val3+'% decrease.';
/*******************PGA Memory Advisory*****************/
/*******************************************************/
var PGA = new Array();
PGA[0]='The PGA advisory utility indicates '+val+'% PGA cache hit ratio during the sample interval. Oracle estimates that doubling the PGA size (by increasing pga_aggregate_target) will increase the PGA cache hit ratio to '+val2+'%, a '+val3+'% increase.';
/*******************Shared Pool Advisory*****************/
/********************************************************/
var SP = new Array();
SP[0]='The Oracle shared pool advisory utility indicates '+val+' cached memory objects during the sample interval. Oracle estimates that doubling the shared pool size (by increasing shared_pool_size) will allow for '+val2+'% more cached objects and will increase estimated time saved by '+val3+'%.';
/*******************Init.ora Parameters*****************/
/*******************************************************/
var IP = new Array();
/****** Large Blocksizes ******/
IP[0]='You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.';
/****** Small DB Cache ******/
IP[1]='Your db_cache_size is '+val+', and this may be too small to fully cache your working set of frequently referenced tables and indexes. To see if an increase is right for you, see the v$db_cache_advice utility.';
/****** DB File Multiblock Read Count ******/
IP[2]='You have the default value for db_file_multiblock_read_count at 16. The CBO uses this parameter to determine the cost of a full-table scan. The default value is sometimes too large, and you can run scripts to determine the optimal setting. If full-table scans are unavoidable, you may consider placing those tables on SSD .';
/****** KEEP Pool ******/
IP[3]='You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts.';
/****** Hash Area Size ******/
IP[4]='You have an unusually small hash_area_size of '+val+'. Setting a larger hash_area_size (or enabling pga_aggregate_target) may improve SQL performance.';
/****** Hash Joins ******/
IP[5]='You do not have hash joins enabled. Hash joins are a powerful alternative to nested loop joins.';
/****** Multi-threaded Server with PGA Aggregate Target ******/
IP[6]='If the Multi-threaded server is being used, the pga_aggregate_target setting would be ignored in all versions except Oracle10g. See pga_aggregate_target details for more.';
/****** Multi-threaded Server ******/
IP[7]='You are using the Multi-threaded Server (Shared Server). Oracle states that connections with the MTS are slower than a dedicated server, and you may consider switching to dedicated database connections.';
/****** Optimizer Index Caching ******/
IP[8]='Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.';
/****** Optimizer Index Caching ******/
IP[9]='Consider setting your optimizer_index_caching parameter to the approximate percentage of index blocks in your data buffer to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.';
/****** Optimizer Index Cost Adj ******/
IP[10]='You have the default value of optimizer_index_cost_adj of 100. Consider lowering your optimizer_index_cost_adj according to the relative costs of index vs. full-scan I/O.';
/****** Optimizer Index Cost Adj ******/
IP[11]='You have not set your optimizer_index_cost_adj parameter. The optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 10,000 and a default value of 100. You can also enable it at the session level by using the alter session set optimizer_index_cost_adj = nn syntax. The default value of 100 is too high for many databases.';
/****** Optimizer Mode ******/
IP[12]='You are using the default optimizer mode and you may consider setting it to first_rows or first_rows_n if you have an online transaction processing system.';
/****** Parallel Automatic Tuning ******/
IP[13]='You have enabled system-level parallel query. This can influence the cost-based optimizer to favor full-table scans over index access. Consider using parallel hints instead, or invoking parallelism at the session level. See parallel automatic tuning.';
/****** Default Sort Area Size ******/
IP[14]='You are using the default sort_area_size, which is usually too small. Examine your statistics for high disk sorts and low hash joins and re-set sort_area_size or enable pga_aggregate_target.';
/****** PGA Aggregate Target ******/
IP[15]='Your pga_aggregate_target is '+val+'. You may have over-allocated your PGA region. No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 2GB for sorting or hash joins. Consider increasing _pga_max_size if you are experiencing high disk sorts or low hash joins. For more information, see pga_aggregate_target details.';
/****** Query Rewrite Enabled ******/
IP[16]='You have not enabled Materialized Views and Function-based indexes, which are very powerful features that require you to set query_rewrite_integrity and query_rewrite_enabled.';
/****** Small Shared Pool ******/
IP[17]='Your shared pool is set at '+val+' and may not be large enough to hold your working set of frequently re-used SQL. For further details, see the shared pool advisory.';
/****** Large Shared Pool ******/
IP[18]='Your shared pool is set at '+val+', which is an unusually large value. Allocating excessive shared pool resource can adversely impact Oracle performance. For further details, see the shared pool advisory.';
/****** Small Sort Area Size ******/
IP[19]='You have an unusually small sort_area_size of '+val+'. Examine your statistics for high disk sorts and low hash joins and re-set sort_area_size or enable pga_aggregate_target.';
/****** Sort Area Size and PGA Aggregate Target ******/
IP[20]='Oracle ignores your sort_area_size when pga_aggregate_target is set and when workarea_size_policy = auto, unless you are using a specialized feature such as the MTS. If dedicated server connections are used, Oracle ignores the sort_area_size parameter. For more information see pga_aggregate_target details.';
/****** Obsolete Parameters ******/
IP[21]='You currently have obsolete parameters set. These parameters are: '+val+'. Oracle classifies non-current parameters as either depreciated or obsolete. Depreciated parameters will prevent your database from starting up, whereas obsolete parameters will simply display a warning and may be ignored.';
if(sec=='load') return(LP[indx]);
if(sec=='efficiency') return(IE[indx]);
if(sec=='top') return(T5[indx]);
if(sec=='wait') return(WE[indx]);
if(sec=='stats') return(IA[indx]);
if(sec==5) return(IO[indx]);
if(sec=='latch') return(LA[indx]);
if(sec=='buffer') return(BP[indx]);
if(sec=='pga') return(PGA[indx]);
if(sec=='shared') return(SP[indx]);
if(sec=='param') return(IP[indx]);
}