CREATE OR REPLACE PACKAGE BODY OptimizerStatistics AS debugFlag BOOLEAN:= FALSE; PROCEDURE debug(str IN VARCHAR2,value IN VARCHAR2) IS BEGIN if (debugFlag) then --if (ServerLog.isOpen()) then -- ServerLog.writeLine(str || value); --end if; dbms_output.put_line(str || value); end if; END debug; FUNCTION getRepositoryTrendTableRowCnt(owbRepos IN VARCHAR2, trendtable IN VARCHAR2) RETURN NUMBER IS realCnt NUMBER := 0; sql_stmt VARCHAR2(512); BEGIN sql_stmt := 'select /' || '*+PARALLEL(' || trendtable || ')*' || '/ COUNT(1) FROM ' || owbRepos || '.' || trendtable; execute immediate sql_stmt INTO realCnt; return realCnt; END getRepositoryTrendTableRowCnt; FUNCTION getRepositoryTrendTableRowStat(owbRepos IN VARCHAR2,trendtable IN VARCHAR2) RETURN NUMBER IS statCnt NUMBER := 0; BEGIN select NVL(num_rows, 0) into statCnt from all_tables where owner = owbRepos and table_name = trendtable; return statCnt; END getRepositoryTrendTableRowStat; FUNCTION isAutoGatherEnabled RETURN BOOLEAN IS oVersion VARCHAR2(64) := ''; oEnabled VARCHAR2(5) := 'FALSE'; oJob VARCHAR2(16) := 'GATHER_STATS_JOB'; sql_stmt VARCHAR2(512); BEGIN BEGIN --Use product_component_version: select on sys.v_$instance is not granted until later in install. --select version into oVersion from sys.v_$instance; select version into oVersion from product_component_version where instr(product,'Oracle') != 0; --debug('isAutoGatherEnabled: DB instance version = ', oVersion); if (substr(oVersion, 1, 3) = '11.' or substr(oVersion, 1, 3) = '10.') then sql_stmt := 'select enabled from dba_scheduler_jobs where job_name = :oJob'; execute immediate sql_stmt INTO oEnabled USING oJob; --debug('isAutoGatherEnabled: ', oEnabled); if (oEnabled = 'TRUE') then return TRUE; end if; end if; EXCEPTION when NO_DATA_FOUND then --debug('isAutoGatherEnabled: NO_DATA_FOUND, should return FALSE. Returning ', oEnabled); return FALSE; END; return FALSE; END isAutoGatherEnabled; -- Determine if optimizer statistics are stale for the design repository. -- Track the actual row count of a 'trend table' versus its row count statistic as a trigger. -- For large repositories, avoid frequent row counting and statistics gathering. -- Therefore as size grows, limit both row counting and statistics gathering to once per day, but -- trigger statistics gathering based on an ever smaller percentage change in row count. -- For small repositories, permit multiple row counting and statistics gathering per day, but -- trigger statistics gathering only when a large percentage change in row count is detected. FUNCTION staleSchemaStatistics(owbRepos IN VARCHAR2, owbUser IN VARCHAR2) RETURN NUMBER IS statCnt NUMBER := 0; realCnt NUMBER := 0; delta NUMBER := 0; trendtable VARCHAR2(6) := 'PCTREE'; lastmsg VARCHAR2(100) := ''; curDate VARCHAR2(8); updDate VARCHAR2(8); stale NUMBER := 0; updatedToday BOOLEAN := FALSE; oVersion VARCHAR2(64) := ''; BEGIN --if (NOT(ServerLog.isOpen())) then -- ServerLog.open('/data/temp','OptimizerStatistics.log'); --end if; BEGIN -- Bug 4417103: Some DB Bugs on some 9.2 versions cause problems gathering statistics on indexes if -- user does not have 'select table' privilege on all tables. See RDBMS bug 2481448, bug 2876699. Due -- to APPS complaints (a 9.2.0.5 user), decided to skip gather_schema_stats for repos users on 9.2 DBs. if (owbUser != owbRepos) then select version into oVersion from product_component_version where instr(product,'Oracle') != 0; if (substr(oVersion, 1, 3) = '9.2') then return 0; end if; end if; --debug('staleSchemaStatistics: owbRepos = ', owbRepos); --debug('staleSchemaStatistics: trendtable = ', trendtable); lastmsg := 'Before getRepositoryTrendTableRowStat'; statCnt := getRepositoryTrendTableRowStat(owbRepos, trendtable); -- If no statistics available, always gather statistics. if (statCnt = 0) then --debug('staleSchemaStatistics: ','StatCnt = 0, returning 1 (TRUE)'); return 1; end if; -- If instance is 10g and GATHER_STATS_JOB enabled, rely on it to gather statistics. lastmsg := 'Before isAutoGatherEnabled'; if (isAutoGatherEnabled() = TRUE) then --debug('staleSchemaStatistics: ','AutoGatherEnabled. Returning 0 (FALSE)'); return 0; end if; -- Statistics available, gathered by OWB, but are they flagged as gathered today? lastmsg := 'Before select ... from cmpinstallation_v'; select to_char(sysdate,'YYYY-DDD'), to_char(statsGatheredOn,'YYYY-DDD') into curDate, updDate from cmpinstallation_v; if (curDate = updDate) then --debug('staleSchemaStatistics: ','OWB Stats dated today.'); updatedToday := TRUE; end if; -- If trend table is large or medium, only count trend table rows once a day. if (statCnt > 200000) then -- Large. if (updatedToday = TRUE) then -- No pressing need to update statistics. --debug('staleSchemaStatistics: ','Large, OWB stats dated today. Returning 0 (FALSE)'); null; else lastmsg := 'Before getRepositoryTrendTableRowCnt, Large'; realCnt := getRepositoryTrendTableRowCnt(owbRepos, trendtable); delta := abs(realCnt - statCnt); if (delta > (0.1 * statCnt)) then -- Flag as stale if more than 10% change --debug('staleSchemaStatistics: ','Large, OWB stats outdated, delta reached. Returning 1 (TRUE)'); stale := 1; else -- Flag as 'Gathered'. This prevents repeated large same day recounts on the trend table. --debug('staleSchemaStatistics: ','Large, OWB stats outdated, delta too small. Returning 0 (FALSE)'); null; end if; end if; elsif (statCnt > 40000) then -- Medium. if (updatedToday = TRUE) then -- No pressing need to update statistics. --debug('staleSchemaStatistics: ','Medium, OWB stats dated today. Returning 0 (FALSE)'); null; else lastmsg := 'Before getRepositoryTrendTableRowCnt, Medium'; realCnt := getRepositoryTrendTableRowCnt(owbRepos, trendtable); delta := abs(realCnt - statCnt); if (delta > (0.2 * statCnt)) then -- Flag as stale if more than 20% change --debug('staleSchemaStatistics: ','Medium, OWB stats outdated, delta reached. Returning 1 (TRUE)'); stale := 1; else -- Flag as 'Gathered'. This prevents repeated large same day recounts on the trend table. --debug('staleSchemaStatistics: ','Medium, OWB stats outdated, delta too small. Returning 0 (FALSE)'); null; end if; end if; else -- Repeated small same day recounts on the trend table are OK. lastmsg := 'Before getRepositoryTrendTableRowCnt, Small'; realCnt := getRepositoryTrendTableRowCnt(owbRepos, trendtable); delta := abs(realCnt - statCnt); if (delta > (0.4 * statCnt)) then -- Flag as stale if more than 40% change --debug('staleSchemaStatistics: ','Small, ignoring OWB stats date, delta reached. Returning 1 (TRUE)'); stale := 1; else -- Flag as 'Gathered'. This prevents repeated large same day recounts on the trend table. --debug('staleSchemaStatistics: ','Small, ignoring OWB stats date, delta too small. Returning 0 (FALSE)'); null; end if; end if; EXCEPTION when NO_DATA_FOUND then --debug('staleSchemaStatistics: NO_DATA_FOUND, Returning 0 (FALSE) ', lastmsg); return stale; END; return stale; END staleSchemaStatistics; PROCEDURE gatherSchemaStatistics(owbRepos IN VARCHAR2) IS oVersion VARCHAR2(30) := ''; oCursorSharing VARCHAR2(30) := ''; oSamplePercent VARCHAR(3) := '10'; sql_stmt VARCHAR2(512); BEGIN BEGIN --debug('gatherSchemaStatistics: ','Before select on compatible system_parameter'); select value into oVersion from sys.v_$system_parameter where name ='compatible'; --debug('gatherSchemaStatistics: ','Before select on cursor_sharing system_parameter'); select value into oCursorSharing from sys.v_$system_parameter where name='cursor_sharing'; -- Workaround for bug when gathering schema stats when cursor_sharing not 'EXACT' -- Not sure, but may only affect schemas containing IOT's with overflow segments. --debug('gatherSchemaStatistics: ','Check if required: alter session cursor_sharing exact (1)'); if ((oCursorSharing is not null) and NOT(oCursorSharing = 'EXACT')) then --debug('gatherSchemaStatistics: ','Before alter session cursor_sharing exact'); sql_stmt := 'alter session set cursor_sharing = EXACT'; execute immediate sql_stmt; end if; -- Take advantage of AUTO_SAMPLE_SIZE parameter if running in 9.2 compatible mode or higher. --debug('gatherSchemaStatistics: ','Check if required: auto_sample_size'); if ((oVersion is not null) and (substr(oVersion, 1, 3) = '11.' or substr(oVersion, 1, 3) = '10.' or substr(oVersion, 1, 3) = '9.2')) then --debug('gatherSchemaStatistics: ','Set auto_sample_size'); oSamplePercent := DBMS_STATS.AUTO_SAMPLE_SIZE; end if; -- Collect the statistics --debug('gatherSchemaStatistics: ','Before call to dbms_stats.gather_schema_statistics'); dbms_stats.gather_schema_stats(ownname=>owbRepos,estimate_percent=>oSamplePercent, options=>'GATHER',cascade=>TRUE); --debug('gatherSchemaStatistics: ','After call to dbms_stats.gather_schema_statistics'); -- Revert back to original value of cursor_sharing after gather schema stats --debug('gatherSchemaStatistics: ','Check if required: alter session cursor_sharing exact (2)'); if ((oCursorSharing is not null) and NOT(oCursorSharing = 'EXACT')) then --debug('gatherSchemaStatistics: ','Before reset of alter session cursor_sharing'); sql_stmt := 'alter session set cursor_sharing = ' || oCursorSharing; execute immediate sql_stmt; end if; EXCEPTION when NO_DATA_FOUND then --debug('gatherSchemaStatistics: ','NO_DATA_FOUND'); null; when OTHERS then --debug('gatherSchemaStatistics: ','OTHERS'); null; END; --debug('gatherSchemaStatistics: ','Normal exit'); --ServerLog.close(); END gatherSchemaStatistics; PROCEDURE deleteSchemaStatistics(owbRepos IN VARCHAR2) IS BEGIN dbms_stats.delete_schema_stats(ownname=>owbRepos); END deleteSchemaStatistics; PROCEDURE forceMCMStatistics(owbRepos IN VARCHAR2) IS BEGIN -- Bug 7035608: Delete/lock statistics for MCM tables with volatile row counts. dbms_stats.delete_table_stats( ownname=>owbRepos, tabname=>'ALLASSOCTABLE' , force=>TRUE); dbms_stats.delete_table_stats( ownname=>owbRepos, tabname=>'ALLCHANGELOG' , force=>TRUE); dbms_stats.delete_table_stats( ownname=>owbRepos, tabname=>'ALLCOMPARESTORE', force=>TRUE); dbms_stats.delete_table_stats( ownname=>owbRepos, tabname=>'ALLTEMPTABLE' , force=>TRUE); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLASSOCTABLE'); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLCHANGELOG'); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLCOMPARESTORE'); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLTEMPTABLE'); -- Bug 7035608: Default/lock statistics for MCM "version" tables where gather stats is premature after seeding. -- Note: Use V view base table stats: repos populated with Paris/Tokyo SRG metadata. dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLINTERLINKSTORAGE' ,numrows => 77241,numblks => 874,avgrlen => 70); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTERLINK_S_COMPUOID' ,numrows => 77241,numlblks =>180,numdist => 1144,avglblk => 1,avgdblk => 5,clstfct => 5758,indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTERLINK_S_LINKFROM' ,numrows => 77241,numlblks =>326,numdist => 52483,avglblk => 1,avgdblk => 1,clstfct => 69144,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTERLINK_S_LINKTO' ,numrows => 77241,numlblks =>183,numdist => 5540,avglblk => 1,avgdblk => 1,clstfct => 7545,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTERLINK_S_SNAPID' ,numrows => 77241,numlblks =>171,numdist => 14,avglblk => 12,avgdblk => 58,clstfct => 825,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLINTERLINKSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLINTRALINKSTORAGE' ,numrows => 27212,numblks => 370,avgrlen => 77); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTRALINK_S_COMPUOID' ,numrows => 27212,numlblks => 64,numdist => 585,avglblk => 1,avgdblk => 1,clstfct => 1067,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTRALINK_S_LINKFROM' ,numrows => 27212,numlblks =>105,numdist => 14969,avglblk => 1,avgdblk => 1,clstfct => 14058,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTRALINK_S_LINKTO' ,numrows => 27212,numlblks => 84,numdist => 5565,avglblk => 1,avgdblk => 1,clstfct => 5813,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTRALINK_S_SNAPID' ,numrows => 27212,numlblks => 62,numdist => 12,avglblk => 5,avgdblk => 26,clstfct => 319,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLINTRALINKSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLPCTREESTORAGE' ,numrows => 52395,numblks => 622,avgrlen => 77); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_PCTREESTORAGE_CID' ,numrows => 52395,numlblks =>197,numdist => 28150,avglblk => 1,avgdblk => 1,clstfct => 44670,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_PCTREESTORAGE_COMPUOID' ,numrows => 52395,numlblks =>118,numdist => 485,avglblk => 1,avgdblk => 7,clstfct => 3431,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_PCTREESTORAGE_SNAPID' ,numrows => 52395,numlblks =>104,numdist => 14,avglblk => 7,avgdblk => 43,clstfct => 608,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLPCTREESTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPFCOSTORAGE' ,numrows => 3684,numblks => 370,avgrlen => 529); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOACTIVECLASSNAME_VER' ,numrows => 3684,numlblks => 9,numdist => 46,avglblk => 1,avgdblk => 15,clstfct => 732,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOCOMPUOID_VER' ,numrows => 3684,numlblks => 10,numdist => 94,avglblk => 1,avgdblk => 8,clstfct => 833,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOELEMENTID_VER' ,numrows => 3684,numlblks => 23,numdist => 3684,avglblk => 1,avgdblk => 1,clstfct => 3613,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOFCOCLASS_VER' ,numrows => 3684,numlblks => 9,numdist => 46,avglblk => 1,avgdblk => 15,clstfct => 732,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOLOADINGPARENT_VER' ,numrows => 3684,numlblks => 9,numdist => 2,avglblk => 4,avgdblk => 143,clstfct => 287,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOOWNINGFOLDER_VER' ,numrows => 3684,numlblks => 9,numdist => 107,avglblk => 1,avgdblk => 8,clstfct => 874,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOSNAPID_VER' ,numrows => 3684,numlblks => 9,numdist => 16,avglblk => 1,avgdblk => 18,clstfct => 303,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCOUOID_VER' ,numrows => 3684,numlblks => 32,numdist => 2760,avglblk => 1,avgdblk => 1,clstfct => 3665,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPFCOSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSCOCFGSTORAGE' ,numrows => 7860,numblks => 748,avgrlen => 586); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGACTIVECLASSNAME_VER',numrows => 7860,numlblks => 17,numdist => 32,avglblk => 1, avgdblk => 48,clstfct => 1538,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGCOMPUOID_VER' ,numrows => 7860,numlblks => 19,numdist => 243,avglblk => 1, avgdblk => 12,clstfct => 2971,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGELEMENTID_VER' ,numrows => 7860,numlblks => 52,numdist => 7860,avglblk => 1, avgdblk => 1,clstfct => 7308,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGFCOCLASS_VER' ,numrows => 7860,numlblks => 22,numdist => 683,avglblk => 1, avgdblk => 4,clstfct => 3100,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGLOADINGPARENT_VER' ,numrows => 7860,numlblks => 17,numdist => 1,avglblk => 17, avgdblk => 689,clstfct => 689,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGOWNINGFOLDER_VER' ,numrows => 7860,numlblks => 17,numdist => 1,avglblk => 17, avgdblk => 689,clstfct => 689,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGSNAPID_VER' ,numrows => 7860,numlblks => 18,numdist => 11,avglblk => 1, avgdblk => 63,clstfct => 699,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCFGUOID_VER' ,numrows => 7860,numlblks => 47,numdist => 3256,avglblk => 1, avgdblk => 2,clstfct => 7860,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOCFGSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSCOSTORAGE' ,numrows => 22664,numblks =>1504,avgrlen => 431); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOACTIVECLASSNAME_VER' ,numrows => 22664,numlblks => 54,numdist => 26, avglblk => 2, avgdblk => 101,clstfct => 2651,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOCOMPUOID_VER' ,numrows => 22664,numlblks => 55,numdist => 198, avglblk => 1, avgdblk => 10,clstfct => 2106,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOELEMENTID_VER' ,numrows => 22664,numlblks =>127,numdist => 22664, avglblk => 1, avgdblk => 1,clstfct => 22601,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOFCOCLASS_VER' ,numrows => 22664,numlblks => 75,numdist => 4013, avglblk => 1, avgdblk => 2,clstfct => 9677,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOLOADINGPARENT_VER' ,numrows => 22664,numlblks => 53,numdist => 2, avglblk =>26, avgdblk => 746,clstfct => 1493,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOOWNINGFOLDER_VER' ,numrows => 22664,numlblks => 53,numdist => 2, avglblk =>26, avgdblk => 746,clstfct => 1493,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOSNAPID_VER' ,numrows => 22664,numlblks => 53,numdist => 13, avglblk => 4, avgdblk => 115,clstfct => 1502,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOUOID_VER' ,numrows => 22664,numlblks =>239,numdist => 21784, avglblk => 1, avgdblk => 1,clstfct => 22595,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSCOMAPSTORAGE' ,numrows => 10648,numblks => 748,avgrlen => 436); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPACTIVECLASSNAME_VER',numrows => 10648,numlblks => 24,numdist => 5, avglblk => 4, avgdblk => 308,clstfct => 1542,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPCOMPUOID_VER' ,numrows => 10648,numlblks => 22,numdist => 44, avglblk => 1, avgdblk => 20,clstfct => 911,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPELEMENTID_VER' ,numrows => 10648,numlblks => 66,numdist => 10648, avglblk => 1, avgdblk => 1,clstfct => 8740,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPFCOCLASS_VER' ,numrows => 10648,numlblks => 25,numdist => 281, avglblk => 1, avgdblk => 5,clstfct => 1682,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPLOADINGPARENT_VER' ,numrows => 10648,numlblks => 23,numdist => 1, avglblk =>23, avgdblk => 717,clstfct => 717,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPOWNINGFOLDER_VER' ,numrows => 10648,numlblks => 23,numdist => 1, avglblk =>23, avgdblk => 717,clstfct => 717,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPSNAPID_VER' ,numrows => 10648,numlblks => 23,numdist => 11, avglblk => 2, avgdblk => 65,clstfct => 723,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPUOID_VER' ,numrows => 10648,numlblks => 63,numdist => 3676, avglblk => 1, avgdblk => 2,clstfct => 10646,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOMAPSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSCOPRPSTORAGE' ,numrows => 30627,numblks => 1504,avgrlen => 288); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPACTIVECLASSNAME_VER',numrows => 30627,numlblks => 65,numdist => 3, avglblk =>21, avgdblk => 510,clstfct => 1531,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPCOMPUOID_VER' ,numrows => 30627,numlblks => 69,numdist => 140, avglblk => 1, avgdblk => 21,clstfct => 3031,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPELEMENTID_VER' ,numrows => 30627,numlblks => 189,numdist => 30627, avglblk => 1, avgdblk => 1,clstfct => 25427,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPFCOCLASS_VER' ,numrows => 30627,numlblks => 71,numdist => 324, avglblk => 1, avgdblk => 9,clstfct => 3068,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPLOADINGPARENT_VER' ,numrows => 30627,numlblks => 66,numdist => 1, avglblk =>66, avgdblk => 1406,clstfct => 1406,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPOWNINGFOLDER_VER' ,numrows => 30627,numlblks => 66,numdist => 1, avglblk =>66, avgdblk => 1406,clstfct => 1406,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPSNAPID_VER' ,numrows => 30627,numlblks => 66,numdist => 11, avglblk => 6, avgdblk => 128,clstfct => 1415,indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPUOID_VER' ,numrows => 30627,numlblks => 158,numdist => 10722, avglblk => 1, avgdblk => 2,clstfct => 30611,indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOPRPSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSYSSTORAGE' ,numrows => 5,numblks => 5,avgrlen => 220); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSACTIVECLASSNAME_VER' ,numrows => 5,numlblks => 1,numdist => 3, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSCOMPUOID_VER' ,numrows => 5,numlblks => 1,numdist => 1, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSELEMENTID_VER' ,numrows => 5,numlblks => 1,numdist => 5, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSFCOCLASS_VER' ,numrows => 5,numlblks => 1,numdist => 3, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSLOADINGPARENT_VER' ,numrows => 5,numlblks => 1,numdist => 1, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSOWNINGFOLDER_VER' ,numrows => 5,numlblks => 1,numdist => 1, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSSNAPID_VER' ,numrows => 5,numlblks => 1,numdist => 1, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SYSUOID_VER' ,numrows => 5,numlblks => 1,numdist => 5, avglblk => 1, avgdblk => 1, clstfct => 1, indlevel => 0); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSYSSTORAGE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLFCOFOLDERNAMESPACESTORAGE',numrows => 3728,numblks => 43, avgrlen => 66); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'FCOFOLDERNSSTORAGE_PK' ,numrows => 3728,numlblks => 54, numdist => 3728, avglblk => 1, avgdblk => 1, clstfct => 401, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCONSSTORAGE_AGGPARENT' ,numrows => 3728,numlblks => 30, numdist => 3728, avglblk => 1, avgdblk => 1, clstfct => 198, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_FCONSSTORAGE_ELEMID' ,numrows => 3728,numlblks => 24, numdist => 3728, avglblk => 1, avgdblk => 1, clstfct => 287, indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLFCOFOLDERNAMESPACESTORAGE'); -- Bug 7035608: Default/lock statistics for MCM "design" tables where gather stats is premature after seeding. dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLINTERLINK' , numrows => 315751, numblks => null, avgrlen => 34); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'ALLINTERLINK_PK' , numrows => 294546, numlblks =>3502, numdist =>294546, avglblk => 1, avgdblk => 1, clstfct => 0, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTERLINK_LINKTO' , numrows => 294546, numlblks =>1369, numdist => 19628, avglblk => 1, avgdblk => 6, clstfct =>135495, indlevel => 2); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLINTERLINK'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLINTRALINK' , numrows => 82565, numblks => null, avgrlen => 41); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'ALLINTRALINK_PK' , numrows => 82565, numlblks =>1054, numdist => 82565, avglblk => 1, avgdblk => 1, clstfct => 0, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_INTRALINK_LINKTO' , numrows => 82565, numlblks => 528, numdist => 16039, avglblk => 1, avgdblk => 2, clstfct => 39996, indlevel => 2); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLINTRALINK'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLPCTREE' , numrows => 171514, numblks => null, avgrlen => 42); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'ALLPCTREE_PK' , numrows => 171514, numlblks =>2043, numdist =>171514, avglblk => 1, avgdblk => 1, clstfct => 0, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_PCTREE_PARENTID' , numrows => 171514, numlblks => 871, numdist => 31171, avglblk => 1, avgdblk => 2, clstfct => 76758, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_PCTREE_PARENTID_CHILDID' , numrows => 171514, numlblks =>1042, numdist =>171514, avglblk => 1, avgdblk => 1, clstfct => 76238, indlevel => 2); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLPCTREE'); dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'ALLLWCONTEXT' , numrows => 53674, numblks => 2512, avgrlen => 328); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_LWCONTEXT_ASSOCID' , numrows => 53674, numlblks => 132, numdist => 5299, avglblk => 1, avgdblk => 2, clstfct => 10122, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_LWCONTEXT_CLASSNAME' , numrows => 53674, numlblks => 124, numdist => 146, avglblk => 1, avgdblk => 31, clstfct => 4568, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_LWCONTEXT_ELEMENTID' , numrows => 53674, numlblks => 186, numdist => 17694, avglblk => 1, avgdblk => 2, clstfct => 46791, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_LWCONTEXT_SNAPID' , numrows => 53674, numlblks => 558, numdist => 40533, avglblk => 1, avgdblk => 1, clstfct => 31713, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_LWCONTEXT_UOID_COMPUOID' , numrows => 53674, numlblks => 120, numdist => 273, avglblk => 1, avgdblk => 16, clstfct => 4411, indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'ALLLWCONTEXT'); -- Bug 7046881: Default/lock statistics for SCOMap "design" table. Has no seeded rows! -- Note: Gathering zero values (vs. leaving them null) negatively impacts many common -- lookups that union on CMPSCOMapClasses, e.g., CMPElement_V, SecondClassObject. dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSCOMAPCLASSES' , numrows => 32627, numblks => 2008, avgrlen => 372); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPCLASSNAMEELEMID' , numrows => 31063, numlblks => 343, numdist => 31063, avglblk => 1, avgdblk => 1, clstfct => 19307, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPELEMENTID' , numrows => 31063, numlblks => 195, numdist => 31063, avglblk => 1, avgdblk => 1, clstfct => 23494, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPFCOCLASS' , numrows => 31063, numlblks => 78, numdist => 1255, avglblk => 1, avgdblk => 3, clstfct => 4663, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPMAPATTR' , numrows => 31063, numlblks => 93, numdist => 4537, avglblk => 1, avgdblk => 1, clstfct => 7999, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPMAPOPREF' , numrows => 31063, numlblks => 124, numdist => 7773, avglblk => 1, avgdblk => 1, clstfct => 9441, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPNAME' , numrows => 31063, numlblks => 71, numdist => 1136, avglblk => 1, avgdblk => 21, clstfct => 24210, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOMAPUOID' , numrows => 31063, numlblks => 363, numdist => 31063, avglblk => 1, avgdblk => 1, clstfct => 21009, indlevel => 1); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOMAPCLASSES'); -- Bug 7046881: Default/lock statistics for SCOPrp "design" table. Few rows are seeded, but count grows quickly! dbms_stats.set_table_stats( ownname=>owbRepos, tabname=>'CMPSCOPRPCLASSES' , numrows => 64953, numblks => 2638, avgrlen => 247); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPCLASSNAMEELEMID' , numrows => 64953, numlblks => 621, numdist => 64953, avglblk => 1, avgdblk => 1, clstfct => 52235, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPELEMENTID' , numrows => 64953, numlblks => 306, numdist => 64953, avglblk => 1, avgdblk => 1, clstfct => 53265, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPFCOCLASS' , numrows => 64953, numlblks => 158, numdist => 894, avglblk => 1, avgdblk => 4, clstfct => 4158, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPNAME' , numrows => 64953, numlblks => 149, numdist => 292, avglblk => 1, avgdblk => 15, clstfct => 4434, indlevel => 1); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPOWNERCLASSBUSNAME' , numrows => 64953, numlblks => 1083, numdist => 64294, avglblk => 1, avgdblk => 1, clstfct => 58938, indlevel => 2); dbms_stats.set_index_stats( ownname=>owbRepos, indname=>'IDX_SCOPRPUOID' , numrows => 64953, numlblks => 744, numdist => 64953, avglblk => 1, avgdblk => 1, clstfct => 39105, indlevel => 2); dbms_stats.lock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOPRPCLASSES'); END forceMCMStatistics; PROCEDURE unlockMCMStatistics(owbRepos IN VARCHAR2) IS BEGIN -- Bug 7035608: Unlock statistics for MCM "version" tables where gather stats is premature after seeding. dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLINTERLINKSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLINTRALINKSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLPCTREESTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPFCOSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOCFGSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOMAPSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOPRPSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSYSSTORAGE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLFCOFOLDERNAMESPACESTORAGE'); -- Bug 7035608: Unlock statistics for MCM "design" tables where gather stats is premature after seeding. dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLINTERLINK'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLINTRALINK'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLPCTREE'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'ALLLWCONTEXT'); -- Bug 7046881: Unlock statistics for SCOMap/SCOPrp "design" tables dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOMAPCLASSES'); dbms_stats.unlock_table_stats( ownname=>owbRepos, tabname=>'CMPSCOPRPCLASSES'); END unlockMCMStatistics; END OptimizerStatistics; /