')) error=3; //HTML report if(!text.match('~~~')) error=3; //HTML report } /****** Check for statspack with every-other blank lines ******/ if(error==0){ while(++i<end&&!input[i].match('----------')); if(newParse(input[i+1],1)=='?'||newParse(input[i+1],1).match('')){ //If line i+1 is blank, recreate array for(i=0;i<len;i++) if(temp[i].match(' ')||temp[i]>' ') input[x++]=temp[i]; //This line is very time-costly (deletes blank lines) } } /****** If no error, capture data ******/ if(error==0) error = capture_data(input,end,text); /****** End program timer ******/ var end_timer = new Date(); //alert((end_timer-start_timer)+' ms'); /****** Display error message and time ******/ if(input[0].match('show debug!')){ if(error) alert('Error: '+error+'\n'+(end_timer-start_timer)+' ms'); else alert('No errors\n'+(end_timer-start_timer)+' ms'); } /****** Display end-user error messages ******/ if(error) error_message(error); } function capture_data(input,end,text){ var pointer = 0; var MI = new Object(); /********************************************************************************/ /* Database Information */ /********************************************************************************/ var timer1 = new Date(); var db_info = new Object(); var i = -1; var save_my_life = 0; var exit_this_loop = 0; db_info.awr = 0; /****** Scan for txt ' report for' then determine whether AWR ******/ while(++i<end&&!input[i].match(' report for')); if(i==end) return 'end - missing line that says whether Statspack or AWR'; if(input[i].match('STATSPACK report for')) db_info.awr = 0; else if(input[i].match('WORKLOAD REPOSITORY report for')) db_info.awr = 1; /****** Scan for beginning of database information ******/ while(++i<end&&!input[i].match('DB Id')); while(++i<end&&!input[i].match('----------')); /****** indicates wrapped report, will not parse ******/ //if(input[i+1].match('----------')) return 5; //common error in 10.2.0.2 and 10.2.0.3 removed header formatting //important to read header, especially the release number if(input[i+1].match('----------')){ //read scrambled 10.2 headers while(++i<end&&input[i].match('--')); temp = input[i]+' '+input[i+1]; if(temp.match('\\..\\..\\..\\.')){ db_info.id = newParse(temp,1); db_info.instance = newParse(temp,3); db_info.release = newParse(temp,6); db_info.rac = newParse(temp,7); } else return 5; } else{ if(!input[i+1].match(' ')) return 5; //other wrapped header /****** Read section header (input,end,i,# of rows) ******/ var header = read_header(input,end,i,1); var a = header.delim; var column_name = header.names; /****** Use column names and delineators to parse data ******/ var x = 0; var temp; i++; while(++x<column_name.length){ temp = input[i].substring(a[x*2],a[(x*2)+1]); if(column_name[x].match('DB Id')) db_info.id = temp; if(column_name[x].match('Instance')) db_info.instance = temp; if(column_name[x].match('Release')) db_info.release = temp; if(column_name[x].match('RAC')) db_info.rac = temp; if(column_name[x].match('Cluster')) db_info.rac = temp; if(column_name[x].match('OPS')) db_info.rac = 'NO'; if(column_name[x].match('Host')) db_info.host = temp; } } /****** Use release number to determine statspack versions ******/ if(db_info.release.match('8.0.5')) db_info.version = 8050; else if(db_info.release.match('8.1.6.0')) db_info.version = 8160; else if(db_info.release.match('8.1.6.1')) db_info.version = 8161; else if(db_info.release.match('8.1.6.2')) db_info.version = 8162; else if(db_info.release.match('8.1.6.3')) db_info.version = 8163; else if(db_info.release.match('8.1.7')) db_info.version = 8170; else if(db_info.release.match('9.0.1')) db_info.version = 9010; else if(db_info.release.match('9.2.0')) db_info.version = 9200; else if(db_info.release.match('10.1.0')) db_info.version = 10100; else if(db_info.release.match('10.2.0')) db_info.version = 10200; else if(db_info.release.match('11.1.0.1')) db_info.version = 11101; else if(db_info.release.match('11.1.0.2')) db_info.version = 11102; else if(db_info.release.match('11.1.0.6')) db_info.version = 11106; else db_info.version = 9200; /****** Versions 10.2.0.1.0 and Newer have additional host info (except AWR) ******/ if(db_info.version>=10200&&db_info.awr==0){ if(db_info.version<11106){ if(text.match('Host Name:')){ while(++i<end&&!input[i].match('Host Name:')); if(i==end) return 'end - 10g host information'; /****** Read host name ******/ x = input[i].indexOf('Name:')+5; db_info.host = input[i].substring(x,x+20); /****** Read number of CPUs ******/ x = input[i].indexOf('Num CPUs:')+9; db_info.cpus = input[i].substring(x,x+10); /****** Read amount of physical memory ******/ x = input[i].indexOf('(MB):')+5; db_info.memory = input[i].substring(x,x+9); } } else{ while(++i<end&&!input[i].match('Host Name')); if(i==end) return 'end - 10g host information'; i++; /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,1); a = header.delim; column_name = header.names; temp = input[i+1]; if(column_name[1].match('Name')) db_info.host = temp.substring(a[2],a[3]); if(column_name[2].match('Platform')) db_info.plat = temp.substring(a[4],a[5]); if(column_name[3].match('CPUs')) db_info.cpus = temp.substring(a[6],a[7]); if(column_name[6].match('Memory')) db_info.memory = temp.substring(a[12],a[13]); } } /****** Find where elapsed time is shown ('Start Id' in old versions) ******/ while(++i<end&&!input[i].match('Start Id')&&!input[i].match('Elapsed')); if(input[i].match('Start Id')) db_info.elapsed = newParse(input[i+2],7); if(input[i].match('Elapsed')) db_info.elapsed = newParse(input[i],2); db_info.elapsed = Math.round(100*db_info.elapsed*60)/100; /****** Find 'Buffer Cache:' ('db_block_buffers' in old versions) ******/ while(++i<end&&!input[i].match('Buffer Cache:')&&!input[i].match('db_block_buffers')); if(input[i].match('Buffer Cache:')) db_info.cache = newParse(input[i],3); if(input[i].match('db_block_buffers')) db_info.cache = newParse(input[i],2); /****** Remove the "M" from the end of 512M etc. ******/ if(db_info.version>=9010) db_info.cache = comma(db_info.cache.substring(0,db_info.cache.length-1)); /********************************************************************************/ /* Load Profile */ /********************************************************************************/ var timer2 = new Date(); var load_profile = new Object(); /****** Logical reads ******/ while(++i<end&&!input[i].match('Logical reads:')); load_profile.logreads = newParse(input[i],3,1); /****** Physical reads ******/ while(++i<end&&!input[i].match('Physical reads:')); load_profile.reads = newParse(input[i],3,1); /****** Physical writes ******/ while(++i<end&&!input[i].match('Physical writes:')); load_profile.writes = newParse(input[i],3,1); /****** Parses ******/ while(++i<end&&!input[i].match('Parses:')); load_profile.parses = newParse(input[i],2,2); /****** Hard parses ******/ while(++i<end&&!input[i].match('Hard parses:')); load_profile.hardparses = newParse(input[i],3,2); /****** Transactions ******/ while(++i<end&&!input[i].match('Transactions:')); load_profile.trans = newParse(input[i],2,2); /****** Rollback per transaction (format change in v11) ******/ i=i-2; //Oracle 11 puts rollback before transactions while(++i<end&&!input[i].match('Rollback')); if(db_info.version>=11100) load_profile.rollback = newParse(input[i],3,2); else load_profile.rollback = newParse(input[i],5,2); /********************************************************************************/ /* Instance Efficiency */ /********************************************************************************/ var timer3 = new Date(); var inst_efficiency = new Object(); /****** Buffer Nowait ******/ while(++i<end&&!input[i].match('Buffer Nowait')); if(i==end) return 'end - instance efficiency'; inst_efficiency.buffernowait=newParse(input[i],4,2); /****** Buffer Hit ******/ while(++i<end&&!input[i].match('Buffer Hit')); inst_efficiency.bufferhit=newParse(input[i],4,2); /****** In-memory Sort ("Optimal W/A Exec" in v11) ******/ if(input[i].match('In-memory Sort')) inst_efficiency.inmemorysort=newParse(input[i],8,2); if(input[i].match('Optimal W/A Exec')) inst_efficiency.inmemorysort=newParse(input[i],9,2); if(input[i+1].match('In-memory Sort')) inst_efficiency.inmemorysort=newParse(input[i],4,2); //reports with messed up formatting in instance efficiency /****** Library Hit ******/ while(++i<end&&!input[i].match('Library Hit')); inst_efficiency.libraryhit=newParse(input[i],4,2); /****** Latch Hit (format different in 8.1.6.2.0 and earlier) ******/ while(++i<end&&!input[i].match('Latch Hit')); if(db_info.version<=8162) inst_efficiency.latchhit=newParse(input[i],4,2); else{ if(input[i].match('Execute to Parse')) inst_efficiency.latchhit=newParse(input[i],9,2); else inst_efficiency.latchhit=newParse(input[i],4,2); //reports with messed up formatting in instance efficiency } /****** In-memory Sort (ver 8.1.6.2.0 and earlier) ******/ if(db_info.version<=8162){ i=i-3; while(++i<end&&!input[i].match('In-memory Sort')); inst_efficiency.inmemorysort=newParse(input[i],4,2); } /****** Shared Pool Statistics ******/ while(++i<end&&!input[i].match('Shared Pool Statistics')&&!input[i].match('Top 5 ')); if(input[i].match('Shared Pool Statistics')){ /****** Memory Usage ******/ while(++i<end&&!input[i].match('Memory Usage %')); inst_efficiency.memoryusage=newParse(input[i],5,2); /****** Memory for SQL ******/ while(++i<end&&!input[i].match('% Memory for SQL')); inst_efficiency.memoryforsql=newParse(input[i],7,2); } else{ inst_efficiency.memoryusage='?' inst_efficiency.memoryforsql='?'; } /********************************************************************************/ /* Top 5 Events */ /********************************************************************************/ var timer4 = new Date(); var top_event = new Array(); /****** Scan for Top 5 section if not there already ******/ if(!input[i].match('Top 5\.*Events')&&i<end){ while(++i<end&&!input[i].match('Top 5\.*Events')); if(i==end) return 'end - top 5 events'; } while(++i<end&&!input[i].match('----------')); /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,3); a = header.delim; column_name = header.names; /****** Find which colum has percent wait time ******/ var y = 0; x = 0; while(++x<column_name.length) if(column_name[x].match('%')) y = x; i++; /****** If percent column is blank, Top 5 Events data is missing ******/ if(input[i].substring(a[y*2],a[y*2+1])=='') return 'end - top 5 events'; /****** Read Top 5 Events ******/ for(x=0;x<5;x++){ top_event[x] = new Object(); top_event[x].name = input[i+x].substring(a[0],a[1]); top_event[x].percent = input[i+x].substring(a[y*2],a[y*2+1]); } /****** Calculate db time ******/ temp = input[i].substring(a[4],a[5]); temp = noComma(temp,1); db_info.dbtime = temp/top_event[0].percent*100; if(db_info.version<=8170) db_info.dbtime = db_info.dbtime/100; //old versions used (cs) db_info.dbtime = Math.round(100*db_info.dbtime)/100; /********************************************************************************/ /* Wait Events */ /********************************************************************************/ var timer5 = new Date(); var wait_event = new Array(); /****** Find beginning of section ******/ while(++i<end&&!input[i].match('Wait Events\.*DB')); //"\.*" means any number of characters between while(++i<end&&!input[i].match('----------')); /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,3); a = header.delim; column_name = header.names; /****** Read wait events ******/ x = 0; while(++i<end&&!input[i].match('Background Wait Events')){ temp = input[i]; wait_event[x] = new Object(); wait_event[x].name = temp.substring(a[0],a[1]); wait_event[x].waits = temp.substring(a[2],a[3]); wait_event[x].total = temp.substring(a[6],a[7]); wait_event[x].avg = temp.substring(a[8],a[9]); wait_event[x].pertxn = temp.substring(a[10],a[11]); /****** Convert cs to seconds on old versions ******/ if(db_info.version<=8170) wait_event[x].total = comma(Math.round(noComma(wait_event[x].total))/100); /****** Link wait events to master index (MI) ******/ if(wait_event[x].name.match('db file sequential read')) MI.sequential = x; if(wait_event[x].name.match('db file scattered read')) MI.scattered = x; x++; } /********************************************************************************/ /* SQL Statistics */ /********************************************************************************/ var timer6 = new Date(); var sql_data = new Array(); var h, z, index, match; save_my_life = i; exit_this_loop = 0; if(text.match('SQL ordered by ')&&db_info.version>=8170){ do{ /****** Find beginning of "ordered by" section ******/ while(++i<end&&!input[i].match('SQL ordered by ')); if(i==end) exit_this_loop = 1; else{ while(++i<end&&!input[i].match('----------')); if(!input[i+1].match('----------')){ //make sure not wrapped if(input[i-1].match('No data exists')){ i++; } else{ /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,2); a = header.delim; column_name = header.names; /****** Make blank column names for sections with less than 7 columns ******/ x = column_name.length; while(x<7) column_name[x++]=''; /****** Loop through section ******/ x = 0; //0 indicates beginning of a new section do{ /****** Find sql metrics ******/ do{ x=0; i++; temp = noComma(input[i].substring(a[0],a[1]),2); // Read first column if(input[i].charAt(0) == ' ' && temp > 0) x = 1; // If contains number, found statement if(input[i].match(' --')) x = 2; // If contains " --", found section end if(x==0){ if(sql_data[index]){ // Avoids error caused by 0 value in first column if(!input[i].match('SQL ordered by ')) sql_data[index].SQL_statement += input[i]+''; //Between statements, read the SQL code else while(!input[++i].match('------')); } else{ // Escape from SQL section x = 2; exit_this_loop = 1; } } }while(x==0&&i<end); /****** Read sql metrics ******/ if(x == 1){ index = 0; match = 0; z = 0; /****** Read hash value ******/ y = a.length - 1; h = input[i].substring(a[y-1],a[y]); /****** Look for previous occurence ******/ while((match==0)&&(z<sql_data.length)){ if(h==sql_data[z].hash) index = z, match = 1; //if hash matches, point index to that statement else index = sql_data.length; //otherwise, point to end of array z++; } /****** Create new sql statement ******/ if(match==0){ sql_data[index] = new SQL_Statement(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0); sql_data[index].hash = h; } /****** Collect data ******/ if(column_name[0].match('Buffer Gets')){ sql_data[index].gets = noComma(input[i].substring(a[0],a[1]),1); sql_data[index].pct_total_gets = noComma(input[i].substring(a[6],a[7]),2); //assumes if(column[3].match('%Total')) } if(column_name[0].match('Physical\.*Reads')){ sql_data[index].phys_reads = noComma(input[i].substring(a[0],a[1]),1); sql_data[index].pct_total_reads = noComma(input[i].substring(a[6],a[7]),2); //assumes if(column[3].match('%Total')) } if(column_name[0].match('Executions')){ sql_data[index].executions = noComma(input[i].substring(a[0],a[1]),1); } if(column_name[0].match('Parse Calls')){ sql_data[index].parse_calls = noComma(input[i].substring(a[0],a[1]),1); sql_data[index].pct_total_parses = noComma(input[i].substring(a[4],a[5]),2); //assumes if(column[2].match('%Total')) } if(column_name[0].match('Elapsed')){ sql_data[index].elap_time = noComma(input[i].substring(a[0],a[1]),1); if(column_name[3].match('%Total')) sql_data[index].pct_total_elap = noComma(input[i].substring(a[6],a[7]),2); } if(column_name[0].match('CPU')){ sql_data[index].cpu_time = noComma(input[i].substring(a[0],a[1]),1); if(column_name[3].match('%Total')) sql_data[index].pct_total_cpu = noComma(input[i].substring(a[6],a[7]),2); } if(column_name[0].match('Sharable Mem')){ sql_data[index].shareable_mem = noComma(input[i].substring(a[0],a[1]),1); sql_data[index].pct_total_shareable = noComma(input[i].substring(a[4],a[5]),2); //assumes if(column[2].match('%Total')) } if(column_name[0].match('Version')){ sql_data[index].version_count = noComma(input[i].substring(a[0],a[1]),1); } if(column_name[0].match('Rows')){ sql_data[index].rows_processed = noComma(input[i].substring(a[0],a[1]),1); } if(column_name[1].match('Executions')||column_name[1].match('Executes'))sql_data[index].executions = noComma(input[i].substring(a[2],a[3]),1); if(column_name[1].match('Rows Processed')) sql_data[index].rows_processed = noComma(input[i].substring(a[2],a[3]),1); if(column_name[1].match('CPU')) sql_data[index].cpu_time = noComma(input[i].substring(a[2],a[3]),2); if(column_name[1].match('Elapsed')) sql_data[index].elap_time = noComma(input[i].substring(a[2],a[3]),1); if(column_name[2].match('Gets')&&column_name[2].match('per Exec')) sql_data[index].gets_per_exec = noComma(input[i].substring(a[4],a[5]),2); if(column_name[2].match('Reads')&&column_name[2].match('per Exec')) sql_data[index].reads_per_exec = noComma(input[i].substring(a[4],a[5]),2); if(column_name[2].match('Rows')&&column_name[2].match('per Exec')) sql_data[index].rows_per_exec = noComma(input[i].substring(a[4],a[5]),2); if(column_name[2].match('Executions')) sql_data[index].executions = noComma(input[i].substring(a[4],a[5]),1); if(column_name[2].match('CPU per')) sql_data[index].cpu_per_exec = noComma(input[i].substring(a[4],a[5]),2); if(column_name[3].match('CPU per')) sql_data[index].cpu_per_exec = noComma(input[i].substring(a[6],a[7]),2); if(column_name[3].match('Elap per')) sql_data[index].elap_per_exec = noComma(input[i].substring(a[6],a[7]),2); if(column_name[4].match('Elap per')) sql_data[index].elap_per_exec = noComma(input[i].substring(a[8],a[9]),2); if(column_name[4].match('CPU')) sql_data[index].cpu_time = noComma(input[i].substring(a[8],a[9]),2); if(column_name[4].match('Elapsd')||column_name[4].match('Elapsed')) sql_data[index].elap_time = noComma(input[i].substring(a[8],a[9]),2); if(column_name[4].match('% Total')&&column_name[4].match('DB Time')) sql_data[index].pct_total_dbtime = noComma(input[i].substring(a[8],a[9]),2); if(column_name[5].match('Elapsd')||column_name[5].match('Elapsed')) sql_data[index].elap_time = noComma(input[i].substring(a[10],a[11]),2); if(column_name[5].match('Buffer Gets')) sql_data[index].gets = noComma(input[i].substring(a[10],a[11]),1); sql_data[index].SQL_statement = ''; } }while(x==1&&i<end); } } else exit_this_loop = 1; //supposed to get out of SQL section gracefully if wrapped data, but also exits when "ordered by" section is empty (two ----------- lines in a row) } }while(!exit_this_loop&&!input[i+1].match('Instance Activity Stats')&&!input[i+2].match('Instance Activity Stats')); } if(exit_this_loop){ i = save_my_life; exit_this_loop = 0; } /********************************************************************************/ /* Instance Activity Stats */ /********************************************************************************/ var timer7 = new Date(); var instance_activity = new Array(); /****** Find beginning of section (if not there already) ******/ if(!input[i].match('Instance Activity Stats')&&i<end){ while(++i<end&&!input[i].match('Instance Activity Stats')); if(i==end) return 'end - instance activity stats'; } while(++i<end&&!input[i].match('----------')); /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,1); a = header.delim; column_name = header.names; /****** Read statistics ******/ x = 0; while(++i<end&&!input[i].match('write clones created')&&!input[i].match('Tablespace IO ')){ temp = input[i]; instance_activity[x] = new Object(); instance_activity[x].name = temp.substring(a[0],a[1]); instance_activity[x].total = temp.substring(a[2],a[3]); instance_activity[x].persec = temp.substring(a[4],a[5]); instance_activity[x].pertxn = temp.substring(a[6],a[7]); /****** Link statistics to master index (MI) ******/ if(instance_activity[x].name.match('parse count \\(hard')) MI.hardparse = x; if(instance_activity[x].name.match('parse count \\(total')) MI.totalparse = x; if(instance_activity[x].name.match('session cursor cache hits'))MI.cursorcachehits = x; if(instance_activity[x].name.match('sorts \\(disk')) MI.disksorts = x; if(instance_activity[x].name.match('sorts \\(memory')) MI.memorysorts = x; x++; } /********************************************************************************/ /* Tablespace IO Stats */ /********************************************************************************/ var timer8 = new Date(); var tablespace_io = new Array(); /****** Find beginning of section (if not there already) ******/ if(i<end&&!input[i].match('Tablespace IO ')){ while(++i<end&&!input[i].match('Tablespace IO ')); if(input[i].match('Summary')) db_info.version=8162; //Tablespace IO Summary means old format, Tablespace IO Stats means new format, possible crosssover on 8.1.6.3 } if(i==end) return 'end - tablespace io'; while(++i<end&&!input[i].match('----------')); if(db_info.version>=8163) while(++i<end&&!input[i].match('----------')); //Versions older than 8.1.6.3 have single line formatting if(input[i+1].match('Av Buf')){ /****** Error out if wrapped tablespace section ******/ return 5; tablespace_io[0] = new Object(); tablespace_io[0].total_reads = 0; tablespace_io[0].total_writes = 0; tablespace_io[0].total_rps = 0; tablespace_io[0].total_wps = 0; } else{ /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,2); a = header.delim; column_name = header.names; /****** Loop through multiple pages ******/ x = 0; i++; do{ /****** On pages after first, find first line of data ******/ if(x > 0){ while(!input[++i].match('----------')); if(db_info.version>=8163) while(!input[++i].match('----------')); i++; } /****** Added condition to make sure that haven't come to end of section ******/ if(!input[i].match('----------')){ //occurs if section has page break followed by no more tablespaces /****** Loop through page ******/ do{ tablespace_io[x] = new Object(); tablespace_io[x].total_reads = 0; tablespace_io[x].total_writes = 0; tablespace_io[x].total_rps = 0; tablespace_io[x].total_wps = 0; /****** Oracle 8.1.6.3 and Newer, two lines ******/ if(db_info.version>=8163){ /****** Read tablespace name ******/ tablespace_io[x].name = input[i++]; temp = input[i++]; /****** Read tablespace data ******/ tablespace_io[x].reads = noComma(temp.substring(a[0],a[1]),1); tablespace_io[x].rps = noComma(temp.substring(a[2],a[3]),1); tablespace_io[x].avg = noComma(temp.substring(a[4],a[5]),2); tablespace_io[x].blks = noComma(temp.substring(a[6],a[7]),2); tablespace_io[x].writes = noComma(temp.substring(a[8],a[9]),1); tablespace_io[x].wps = noComma(temp.substring(a[10],a[11]),1); tablespace_io[x].time = Math.round((tablespace_io[x].reads + tablespace_io[x].writes) * tablespace_io[x].avg / 1000); } /****** Oracle 8.1.6.2 and Older, single line ******/ else{ /****** Read tablespace name ******/ tablespace_io[x].name = input[i].substring(a[0],a[1]); temp = input[i++]; /****** Read tablespace data ******/ tablespace_io[x].reads = noComma(temp.substring(a[2],a[3]),1); tablespace_io[x].rps = ' '; tablespace_io[x].avg = noComma(temp.substring(a[4],a[5]),2); tablespace_io[x].blks = ' '; tablespace_io[x].writes = noComma(temp.substring(a[6],a[7]),1); tablespace_io[x].wps = ' '; tablespace_io[x].time = (tablespace_io[x].reads + tablespace_io[x].writes) * tablespace_io[x].avg; } /****** Capture total tablespace reads/writes ******/ tablespace_io[0].total_reads += tablespace_io[x].reads; tablespace_io[0].total_writes += tablespace_io[x].writes; /****** Capture total tablespace reads/writes per second ******/ if(db_info.version>=8163){ tablespace_io[0].total_rps += tablespace_io[x].rps; tablespace_io[0].total_wps += tablespace_io[x].wps; } if((tablespace_io[x].reads+tablespace_io[x].writes)>0){} else return 'end - tablespace formatting'; x++; }while(!((input[i].match('----------'))||(input[i+1].match('Tablespace IO '))||(input[i].match('Tablespace IO ')))); } }while(input[i].match('Tablespace IO Stats')||input[i+1].match('Tablespace IO Stats')); } pointer = i; //if buffer and pga pool don't exist, shared pool will use this pointer /********************************************************************************/ /* Buffer Pool Advisory */ /********************************************************************************/ var timer9 = new Date(); var buffer_pool = new Object(); if(text.match('Buffer Pool Advisory')){ if(db_info.version>=9010){ /****** Find beginning of section ******/ while(++i<end&&!input[i].match('Buffer Pool Advisory')); if(i==end) return 'end - buffer pool'; while(++i<end&&!input[i].match('----------')); if(!input[i-1].match('No data exists')){ //Make sure data exists /****** Read number of physical reads at size factors 1.0 and 2.0 ******/ do{ temp=input[i]; if(newParse(temp,3,2)==1&&input[i].charAt(0)=='D') buffer_pool.current = newParse(temp,6,1); //reads the default buffer pool, change to 'K' to read the Keep buffer pool if(newParse(temp,3,2)>=1.5&&input[i].charAt(0)=='D') buffer_pool.double = newParse(temp,6,1); i++; }while((!input[i].match('----------'))&&(input[i].charAt(0)=='D'||input[i].charAt(0)=='K')); /****** Statspack releases 10.1.0 and newer show reads in thousands ******/ if(db_info.version>=10100&&db_info.awr==0) buffer_pool.current*=1000, buffer_pool.double*=1000; } } pointer = i; } /********************************************************************************/ /* PGA Memory Advisory */ /********************************************************************************/ var timer10 = new Date(); var pga_memory = new Object(); if(text.match('PGA Memory Advisory')){ if(db_info.version>=9010){ /****** Find beginning of section ******/ while(++i<end&&!input[i].match('PGA Memory Advisory')); if(i==end) return 'end - pga memory'; while(++i<end&&!input[i].match('----------')); if(!input[i-1].match('No data exists')){ //Make sure data exists /****** Read estimated cache hit at size factors 1.0 and 2.0 ******/ do{ temp=input[i]; if(newParse(temp,2,2)==1) pga_memory.current = newParse(temp,5,1); if(newParse(temp,2,2)==2) pga_memory.double = newParse(temp,5,1); i++; }while(!input[i].match('----------')); } } pointer = i; } /********************************************************************************/ /* Latch Activity */ /********************************************************************************/ var timer11 = new Date(); var latch_activity = new Array(); /****** Find beginning of section ******/ while(++i<end&&!input[i].match('Latch Activity ')); if(i==end) return 'end - latch activity'; while(++i<end&&!input[i].match('----------')); /****** Read section header (input,end,i,# of rows) ******/ header = read_header(input,end,i,3); a = header.delim; column_name = header.names; /****** Read latches ******/ x = 0; while(++i<end&&!input[i].match('Latch Sleep')&&!input[i].match('user lock ')&&!input[i].match('Dictionary Cache Stats')){ temp = input[i]; /****** Read latch if Get Requests is greater than 0 ******/ if(noComma(temp.substring(a[2],a[3]))>0){ //this skips the page breaks latch_activity[x] = new Object(); latch_activity[x].name = temp.substring(a[0],a[1]); latch_activity[x].getreq = temp.substring(a[2],a[3]); latch_activity[x].getmiss = temp.substring(a[4],a[5]); if(db_info.version>=9010){ latch_activity[x].waittime = temp.substring(a[8],a[9]); latch_activity[x].nowaitreq = temp.substring(a[10],a[11]); latch_activity[x].nowaitmiss = temp.substring(a[12],a[13]); } else{ //Release 8 doesn't have "wait time" column latch_activity[x].waittime = ' '; latch_activity[x].nowaitreq = temp.substring(a[8],a[9]); latch_activity[x].nowaitmiss = temp.substring(a[10],a[11]); } /****** Link latches to master index (MI) ******/ if(latch_activity[x].name.match('cache buffers chains')) MI.chains = x; if(latch_activity[x].name.match('cache buffers lru chain')) MI.lru = x; if(latch_activity[x].name.match('library cache ')) MI.librarycache = x; if(latch_activity[x].name.match('library cache load lock')) MI.loadlock = x; if(latch_activity[x].name.match('library cache lock ')) MI.lock = x; if(latch_activity[x].name.match('library cache pin ')) MI.pin = x; if(latch_activity[x].name.match('redo allocation')) MI.allocation = x; if(latch_activity[x].name.match('redo copy')) MI.copy = x; if(latch_activity[x].name.match('shared pool')) MI.shared = x; x++; } } if(i==end) return 'end - latch sleep/user lock'; /********************************************************************************/ /* Shared Pool Advisory */ /********************************************************************************/ var timer12 = new Date(); var shared_pool = new Object(); shared_pool.esttimesaved = 1; shared_pool.estobjects = 1; if(text.match('Shared Pool Advisory')){ i = pointer; if(db_info.version>=9010){ /****** Find beginning of section ******/ while(++i<end&&!input[i].match('Shared Pool Advisory')); if(i==end) return 'end - shared pool'; while(++i<end&&!input[i].match('----------')); /****** Read number of cached objects and time saved at size factors 1.0 and 2.0 ******/ do{ temp=input[++i]; if(newParse(temp,2,2)==1) shared_pool.currentobjects = newParse(temp,4,1), shared_pool.currenttime = newParse(temp,5,1); if(newParse(temp,2,2)>=2) shared_pool.doubleobjects = newParse(temp,4,1), shared_pool.doubletime = newParse(temp,5,1); }while(!input[i].match('-----------------------------')); shared_pool.esttimesaved = Math.round(100*((100*shared_pool.doubletime/shared_pool.currenttime)-100))/100; shared_pool.estobjects = Math.round((100*shared_pool.doubleobjects/shared_pool.currentobjects)-100); } } /********************************************************************************/ /* init.ora Parameters */ /********************************************************************************/ var timer13 = new Date(); var init_param = new Array(); MI.obsolete = new Array(); /****** Find beginning of section ******/ while(++i<end&&!input[i].match('init.ora Parameters')); while(++i<end&&!input[i].match('----------')); /****** Read parameters ******/ x = 1; while(++i<end&&!input[i].match('----------')){ temp = input[i]; init_param[x] = new Object(); init_param[x].name = newParse(temp,1); init_param[x].value = newParse(temp,2).toLowerCase(); /****** Link parameters to master index (MI) ******/ if(init_param[x].name.match('_optimizer_cost_model')) MI.costmodel = x; else if(init_param[x].name.match('buffer_pool_keep')) MI.bufferpoolkeep = x; else if(init_param[x].name.match('cursor_sharing')) MI.cursorsharing = x; else if(init_param[x].name.match('db_16k_cache_size')) MI.db16kcachesize = x; else if(init_param[x].name.match('db_32k_cache_size')) MI.db32kcachesize = x; else if(init_param[x].name.match('db_block_size')) MI.dbblocksize = x; else if(init_param[x].name.match('db_cache_size')) MI.dbcachesize = x; else if(init_param[x].name.match('db_file_multiblock_read_count')) MI.dbmultiblockreadcount = x; else if(init_param[x].name.match('db_keep_cache_size')) MI.dbkeepcachesize = x; else if(init_param[x].name.match('hash_area_size')) MI.hashareasize = x; else if(init_param[x].name.match('hash_join_enabled')) MI.hashjoinenabled = x; else if(init_param[x].name.match('mts_servers')) MI.mtsservers = x; else if(init_param[x].name.match('optimizer_index_caching')) MI.optimizerindexcaching = x; else if(init_param[x].name.match('optimizer_index_cost_adj')) MI.optimizerindexcostadj = x; else if(init_param[x].name.match('optimizer_mode')) MI.optimizermode = x; else if(init_param[x].name.match('parallel_automatic_tuning')) MI.parallelautomatictuning = x; else if(init_param[x].name.match('pga_aggregate_target')) MI.pgaaggregatetarget = x; else if(init_param[x].name.match('query_rewrite_enabled')) MI.queryrewriteenabled = x; else if(init_param[x].name.match('session_cached_cursors')) MI.sessioncached = x; else if(init_param[x].name.match('shared_pool_size')) MI.sharedpoolsize = x; else if(init_param[x].name.match('shared_servers')) MI.sharedservers = x; else if(init_param[x].name.match('sort_area_size')) MI.sortareasize = x; else if(init_param[x].name.match('workarea_size_policy')) MI.workareasizepolicy = x; if(db_info.version>=9010){ if(init_param[x].name.match('dblink_encrypt_login')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('db_block_buffers')) MI.obsolete[MI.obsolete.length] = x; } if(db_info.version>=10100){ if(init_param[x].name.match('global_context_pool_size')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('log_archive_start')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('log_parallelism')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('max_enabled_roles')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('max_rollback_segments')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_circuits')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_dispatchers')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_listener_address')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_max_dispatchers')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_max_servers')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_multiple_listeners')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_service')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('mts_sessions')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('optimizer_max_permutations')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('oracle_trace_collection_path')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('oracle_trace_collection_size')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('oracle_trace_enable')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('oracle_trace_facility_name')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('oracle_trace_facility_path')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('partition_view_enabled')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('plsql_compiler_flags')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('row_locking')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('serializable')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('transaction_auditing')) MI.obsolete[MI.obsolete.length] = x; else if(init_param[x].name.match('undo_suppress_errors')) MI.obsolete[MI.obsolete.length] = x; } x++; } /****** If parameters not found, assign defaults ******/ if(!MI.dbblocksize){ init_param[x] = new Object(); init_param[x].name = 'db_block_size'; init_param[x].value = 8192; MI.dbblocksize = x++; } if(!MI.costmodel){ init_param[x] = new Object(); init_param[x].name = '_optimizer_cost_model'; if(db_info.version>=10100) init_param[x].value='choose'; else if(db_info.version>=9010) init_param[x].value='choose'; else if(db_info.version>=8160) init_param[x].value='io'; MI.costmodel = x++; } if(!MI.sessioncached){ init_param[x] = new Object(); init_param[x].name = 'session_cached_cursors'; init_param[x].value = 50; MI.sessioncached = x++; } if(!MI.cursorsharing){ init_param[x] = new Object(); init_param[x].name = 'cursor_sharing'; init_param[x].value = 'exact'; MI.cursorsharing = x++; } /********************************************************************************/ /* Bundle all data into array */ /********************************************************************************/ var timer14 = new Date(); var DATA_BUNDLE = new Array(); DATA_BUNDLE[0] = db_info; DATA_BUNDLE[1] = load_profile; DATA_BUNDLE[2] = inst_efficiency; DATA_BUNDLE[3] = top_event; DATA_BUNDLE[4] = wait_event; DATA_BUNDLE[5] = sql_data; DATA_BUNDLE[6] = instance_activity; DATA_BUNDLE[7] = tablespace_io; DATA_BUNDLE[8] = buffer_pool; DATA_BUNDLE[9] = pga_memory; DATA_BUNDLE[10] = latch_activity; DATA_BUNDLE[11] = shared_pool; DATA_BUNDLE[12] = init_param; DATA_BUNDLE[13] = MI; create_report(DATA_BUNDLE); /* alert( (timer2-timer1)+': database info\n'+ (timer3-timer2)+': load profile\n'+ (timer4-timer3)+': instance efficiency\n'+ (timer5-timer4)+': top 5\n'+ (timer6-timer5)+': wait events\n'+ (timer7-timer6)+': sql\n'+ (timer8-timer7)+': instance activity\n'+ (timer9-timer8)+': tablespace\n'+ (timer10-timer9)+': buffer\n'+ (timer11-timer10)+': pga\n'+ (timer12-timer11)+': latch\n'+ (timer13-timer12)+': shared\n'+ (timer14-timer13)+': init parameters');*/ return 0; } function SQL_Statement(hash, gets, executions, gets_per_exec, pct_total_gets, cpu_time, elap_time, phys_reads, reads_per_exec, pct_total_reads, rows_processed, rows_per_exec, cpu_per_exec, elap_per_exec,parse_calls, pct_total_parses,shareable_mem, pct_total_shareable,version_count,pct_total_sql,pct_total_cpu,pct_total_elap,pct_total_dbtime,SQL_statement,phys_reads_per_min,gets_per_min,executions_per_min,pct_total_exec){ this.hash = hash; this.gets = gets; this.executions = executions; this.gets_per_exec = gets_per_exec; this.pct_total_gets = pct_total_gets; this.cpu_time = cpu_time; this.elap_time = elap_time; this.phys_reads = phys_reads; this.reads_per_exec = reads_per_exec; this.pct_total_reads = pct_total_reads this.rows_processed = rows_processed; this.rows_per_exec = rows_per_exec; this.cpu_per_exec = cpu_per_exec; this.elap_per_exec = elap_per_exec; this.parse_calls = parse_calls; this.pct_total_parses = pct_total_parses; this.shareable_mem = shareable_mem; this.pct_total_shareable = pct_total_shareable; this.version_count = version_count; this.pct_total_sql = pct_total_sql; this.pct_total_cpu = pct_total_cpu; this.pct_total_elap = pct_total_elap; this.pct_total_dbtime = pct_total_dbtime; this.SQL_statement = SQL_statement; this.phys_reads_per_min = phys_reads_per_min; this.gets_per_min = gets_per_min; this.executions_per_min = executions_per_min; this.pct_total_exec = pct_total_exec; } function read_header(input,end,i,row){ //row = number of rows to read above the ------ line var temp = input[i]; var a = new Array(); var x = 0; var y = 1; while(x<temp.length-3){ if(temp.charAt(x)==' '){ a[y++] = x; if(temp.charAt(x+1)==' ') a[y++] = ++x; else a[y++] = x; } x++ } a[0] = 0; a[y]=temp.length; var column_name = new Array(); for(x=0;x<(y+1)/2;x++) column_name[x] = ''; while(row>0){ x=0; y=0; do{ column_name[x++]+=' '+input[i-row].substring(a[y++],a[y++]); }while(x<column_name.length); --row; } var header = new Object(); header.delim = a; header.names = column_name; return(header); } function newParse(line,indx,f) //f: 0 - string, 1 - integer, 2 - float { var temp=line.split(' '); var data = new Array(); var output =''; var j,i; indx--; j=0; for(i=0;i<temp.length;i++) if(temp[i]!='') data[j++]=temp[i]; if((data.length)<(indx+1)) return('?'); output=data[indx].replace(/,/g,''); if(f==-1) output=parseInt(data[(data.length)-(indx+1)].replace(/,/g,'')); if(f==-2) output=parseFloat(data[(data.length)-(indx+1)].replace(/,/g,'')); //if(output.charAt(0).match('-')) output*=-1; if(f==1) output=(output>-1)? parseInt(output):'?'; if(f==2) output=(output>-1)? parseFloat(output):'?'; return(output); } function noComma(data,f) { if(!data.match('\\S')) return('?'); //If data does not contain any non-space character, return "?" var output = data.replace(/,/g,''); //if(output.match('####')) alert('####'); if(f==1) output=(output!=null)? parseInt(output):'?'; if(f==2) output=(output!=null)? parseFloat(output):'?'; return(output); }