Gather Schema Statistics How to Use it !!!!

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

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
);