Tag: Gather Schema Statistics

Gather Schema Statistics How to Use it !!!!

Posted on by Osama Mustafa in Database
Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

As a general rule, run Gather Schema Statistics under the following circumstances:
1.  After there has been a significant change in data in either content or volume.
2.  After importing data.
3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.

 4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.

Type :

  1. GATHER_INDEX_STATS, Index statistics
  2. GATHER_TABLE_STATS, Table, column, and index statistics
  3. GATHER_SCHEMA_STATS,Statistics for all objects in a schema
  4. GATHER_DICTIONARY_STATS,Statistics for all dictionary objects
  5. GATHER_DATABASE_STATS,Statistics for all objects in a database

Example :

begin
 dbms_stats.gather_schema_stats
 (ownname => 'Scott', 
 estimate_percent => dbms_stats.auto_sample_size E "sample_size",
 options => 'GATHER EMPTY'
 );
end;
 
 
 

The above call starts the gather_schema_stats for a schema called SCOTT with estimate_percent at default sample size and options gather empty which gathers statistics on objects which currently have no statistics.

You can disable automated statistics collection job using the code below :

EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’);
 
 To re-enable the job:
 
 
EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’);
 To check if it job running or not 




SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';




 


 


Thank you 


Osama mustafa 


 


 






 

	

	

	




	
	

		
Gather Schema Statistics fails with Ora-20001
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Cause: FDPSTP failed due to ORA-06550: line 1, column 7:
PLS-00307: too many  declarations of ‘ GATHER_ALL_COLUMN_STATS’ match this call
ORA-06550: line 1,  column 7:
PL/SQL: Statement ignored.




Error #1:  ERROR: While GATHER_TABLE_STATS:


object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column  name or duplicate columns/column groups/expressions in method_opt***


Error #2:  ERROR: While GATHER_TABLE_STATS:


object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or  duplicate columns/column groups/expressions in method_opt***


Error #3:  ERROR: While GATHER_TABLE_STATS:


object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column  name or duplicate columns/column groups/expressions in method_opt***






Solution :








To Check  which objects or tables are locked  






sql >  SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED


FROM DBA_TAB_STATISTICS


WHERE STATTYPE_LOCKED IS NOT NULL;










TO  Unlock all the tables in a schema at once :






sql>  exec dbms_stats.unlock_schema_stats(‘schema_owner’);


e.g :  sql> exec dbms_stats.unlock_schema_stats(‘apps’);






TO  Unlock all Individual  tables in a schema at once  






sql>  exec dbms_stats.unlock_schema_stats(‘table_owner’,’table_name’);


e.g : sql  > exec dbms_stats.unlock_schema_stats(‘AR’,’AR_REV_REC_QT’); 








There  are two reasons for that error message:




1 ) There  are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP  table.


Because of  this problem, FND_STATS tries to gather histogram information using wrong  command and it fails with ora-20001 errors.




Following  SQL should have returned one row , not two.






SQL>  select a.column_name, nvl(a.hsize,254) hsize


from  FND_HISTOGRAM_COLS a


where  table_name = ‘JE_BE_LINE_TYPE_MAP’


order by  column_name;








COLUMN_NAME  HSIZE


—————————— ———-


SOURCE  254


SOURCE  254






2) Column  does not exist on the table but still listed in FND_HISTOGRAMS_COL table.




Solution:




Find out  all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of  them.


Remember to  take backup of the FND_HISTOGRAM_COLS table before deleting any data.




— identify  duplicate rows




select  table_name, column_name, count(*)


from  FND_HISTOGRAM_COLS


group by  table_name, column_name


having  count(*) > 1;








— Use  above results on the following SQL to delete duplicates




delete from  FND_HISTOGRAM_COLS


where  table_name = ‘&TABLE_NAME’


and   column_name = ‘&COLUMN_NAME’


and  rownum=1;








— Use  following SQL to delete obsoleted rows






delete from  FND_HISTOGRAM_COLS


where  (table_name, column_name) in 


(


select hc.table_name, hc.column_name


from FND_HISTOGRAM_COLS hc , dba_tab_columns tc


where hc.table_name  =’&TABLE_NAME’


and hc.table_name= tc.table_name (+)


and hc.column_name = tc.column_name (+)


and tc.column_name is null


);