dbms_stats.gather_schema_stats
Cristian Perez, March 07, 2003 - 10:01 am UTC
Just one clarification schema A loads the tables in schema B. Then Schema B runs a process that works with the data in schema B only. But I guess the same applies, table not analyzed that changed a lot because they were completely loaded.
March 07, 2003 - 10:14 am UTC
yes, if the tables in b were analyzed, then you loaded them with new stuff, and then you ran stuff against them -- the optimizer was working off of the old -- totally not relevant and wrong statistics.
garbage in, garbage out.
A reader, March 07, 2003 - 1:03 pm UTC
UB from Domingo
Insufficient privileges to analyze an object in Schema
Kamal Kishore, June 16, 2003 - 10:58 am UTC
Hi Tom,
What could be the cause of following error?
IS there a way to find the object that is causing the error?
Thanks,
SQL> exec dbms_stats.gather_schema_stats(user, estimate_percent => 20, method_opt => 'FOR ALL INDEXED COLUMNS', degree => 1, cascade => true) ;
BEGIN dbms_stats.gather_schema_stats(user, estimate_percent => 20, method_opt => 'FOR ALL INDEXED COLUMNS', degree => 1, cascade => true) ; END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 9858
ORA-06512: at "SYS.DBMS_STATS", line 10023
ORA-06512: at "SYS.DBMS_STATS", line 10077
ORA-06512: at "SYS.DBMS_STATS", line 10054
ORA-06512: at line 1
SQL>
SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>
June 16, 2003 - 11:22 am UTC
make sure you have the ANALYZE ANY priv.
What about any data locking issues?
Matt, June 16, 2003 - 9:05 pm UTC
Obviously the best time to generate optimizer stats might be after a major data load. Sometimes though it might be necessary to generate stats whilst the data is changing (although at a quiet time).
What kind of approach does dbms_stats take to make sure that the stats generated are accurate given the fact that data may be changing whilst the stats are being generated. Is there any significant impact to processes that are concurrently changing the data being analysed.
Thanks and Regards,
June 17, 2003 - 7:13 am UTC
the stats will be current as of the time dbms_stats started reading the data. If the data is changing, the stats will reflect only what dbms_stats sees (it mostly uses SQL which gets a read consistent, non blocking read).
stats are never 100% accurate except for read only data.
privilege on GATHER_SCHEMA_STATS
VIVIAN, June 17, 2003 - 2:26 pm UTC
Hi Tom,
My database is 9.2.0.3.0 on HP UNIX. With ANALYZE ANY privilege, I can run stats for other schema, says SCOTT;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'SCOTT',
estimate_percent => 10,
degree => 4,
cascade => TRUE);
END;
/
PL/SQL procedure successfully completed
However, when I create a procedure in my schema to gather stats and run the procedure, I got the error message, see the following:
create or replace procedure run_stat as
begin
EXECUTE IMMEDIATE 'alter session SET SORT_AREA_SIZE = 10485760';
EXECUTE IMMEDIATE 'alter session SET SORT_AREA_RETAINED_SIZE = 10485760';
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'RFRAM',
estimate_percent => 10,
degree => 4,
cascade => TRUE);
EXCEPTION
WHEN others THEN
raise_application_error (-20000, 'stat failed' || SQLERRM);
end;
Procedure created
SQL> exec run_stat;
begin run_stat; end;
ORA-20000: stat failed ORA-01031: insufficient privileges
ORA-06512: at "TESTER.RUN_STAT", line 16
ORA-06512: at line 1
What else privilege needed for analyze other schema in 9.2.03?
The same procedure can run fine in oracle version 9.0.1 before.
privilege in DBMS_STATS.GATHER_SCHEMA_STATS
VIVIAN, June 18, 2003 - 10:48 am UTC
ANALYZE ANY priv is directly granted.
SQL> GRANT ANALYZE ANY TO TESTER;
Grant succeeded
-- lOGIIN AS TESTER;
SQL> EXECUTE RUN_STAT;
begin RUN_STAT; end;
ORA-20000: stat failed ORA-01031: insufficient privileges
ORA-06512: at "TESTER.RUN_STAT", line 16
ORA-06512: at line 1
June 19, 2003 - 7:40 am UTC
so, compare the set of privs from the system where it works to the one that does not.
You know what -- run a script like this and cut and paste the ACTUAL output:
ops$tkyte@ORA920LAP> drop user a cascade;
User dropped.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create user a identified by a;
User created.
ops$tkyte@ORA920LAP> grant create session to a;
Grant succeeded.
ops$tkyte@ORA920LAP> grant analyze any to a;
Grant succeeded.
ops$tkyte@ORA920LAP> grant create procedure to a;
Grant succeeded.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select * from dba_sys_privs where grantee in ( 'A', 'PUBLIC' );
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
A ANALYZE ANY NO
A CREATE SESSION NO
A CREATE PROCEDURE NO
ops$tkyte@ORA920LAP> @connect a/a
ops$tkyte@ORA920LAP> set termout off
a@ORA920LAP> REM GET afiedt.buf NOLIST
a@ORA920LAP> set termout on
a@ORA920LAP>
a@ORA920LAP>
a@ORA920LAP> create or replace procedure run_stat
2 as
3 begin
4 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_SIZE = 10485760';
5 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_RETAINED_SIZE = 10485760';
6
7 DBMS_STATS.GATHER_SCHEMA_STATS (
8 ownname => 'SCOTT',
9 estimate_percent => 10,
10 degree => 4,
11 cascade => TRUE);
12 EXCEPTION
13 WHEN others THEN
14 raise_application_error (-20000, 'stat failed' || SQLERRM);
15 end;
16 /
Procedure created.
a@ORA920LAP>
a@ORA920LAP> exec run_stat
PL/SQL procedure successfully completed.
that was 9203
privilege in DBMS_STATS.GATHER_SCHEMA_STATS
VIVIAN, June 19, 2003 - 12:44 pm UTC
Thanks Tom for your time. However I still got same error.
Before my database migrate to 9.2.0.3, the procedure works fine in 9.0.1.
Connected to Oracle9i Enterprise Edition Release 9.2.0.3.0
Connected as system
SQL> create user a identified by a;
User created
SQL> grant create session to a;
Grant succeeded
SQL> grant analyze any to a;
Grant succeeded
SQL> grant create procedure to a;
Grant succeeded
SQL>
SQL> select * from dba_sys_privs where grantee in ( 'A',
2 'PUBLIC' );
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
A ANALYZE ANY NO
A CREATE SESSION NO
A CREATE PROCEDURE NO
SQL> connect a/a;
Connected to Oracle9i Enterprise Edition Release 9.2.0.3.0
Connected as a
SQL> set termout on
SQL>
SQL> create or replace procedure run_stat as
2
3 begin
4 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_SIZE = 10485760';
5 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_RETAINED_SIZE = 10485760';
6
7
8 DBMS_STATS.GATHER_SCHEMA_STATS (
9 ownname => 'SCOTT',
10 estimate_percent => 10,
11 degree => 4,
12 cascade => TRUE);
13
14 EXCEPTION
15 WHEN others THEN
16 raise_application_error (-20000, 'stat failed ' || SQLERRM);
17 end;
18 /
Procedure created
SQL> exec run_stat
begin run_stat; end;
ORA-20000: stat failed ORA-01031: insufficient privileges
ORA-06512: at "A.RUN_STAT", line 16
ORA-06512: at line 1
June 19, 2003 - 1:21 pm UTC
remove the exception block, lets see what the real call stack it. It is not useful to catch an exception like that, only removes useful information for the debug process.
Isn't that a BUG?
Peter, June 19, 2003 - 3:28 pm UTC
Hi Tom,
I followed the same steps as you did and tested two Oracle9 database (one is 9.2.0.1 and the other is 9.2.0.3 ), both showed the same result. That is if you run the run_stat to against any schema with no object, or only have tables, the procedure will be executed sucessfully, however if you have INDEX in the schedma, you will have "ORA-01031: insufficient privileges". This rule applies to all normal users (including users have dba privilege) except SYS (or users have SYSBDA privilege and login as sysdba).
This problem only applies to Oracle9.2, the procedure run well both in Oracle8.1.7 or Oracle9.0.
Please let us know if this is known bug in Oracle9.2. The following is the test I result from my test against Oracle9.2.0.1. Thanks.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> create user a identified by a;
User created.
SQL>
SQL> grant create session to a;
Grant succeeded.
SQL> grant analyze any to a;
Grant succeeded.
SQL> grant create procedure to a;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee in ( 'A',
2 'PUBLIC' );
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
A ANALYZE ANY NO
A CREATE SESSION NO
A CREATE PROCEDURE NO
SQL> connect a/a@ora92
Connected.
SQL> create or replace procedure run_stat as
2 begin
3 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_SIZE = 10485760';
4 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_RETAINED_SIZE = 10485760';
5
6
7 DBMS_STATS.GATHER_SCHEMA_STATS (
8 ownname => 'SCOTT',
9 estimate_percent => 10,
10 degree => 4,
11 cascade => TRUE);
12
13 end;
14 /
Procedure created.
SQL> exec run_stat
PL/SQL procedure successfully completed.
SQL> connect /@ora92 as sysdba
Connected.
SQL> select count(*) from dba_objects
2 where owner='SCOTT';
COUNT(*)
----------
0
SQL>
SQL> connect scott/tiger@ora92
Connected.
SQL> create table t1 (col1 varchar2(10));
Table created.
SQL>
SQL> connect a/a@ora92
Connected.
SQL> exec run_stat;
PL/SQL procedure successfully completed.
SQL>
SQL> connect scott/tiger@ora92
Connected.
SQL> create index ind1 on t1 (col1);
Index created.
SQL>
SQL> connect a/a@ora92
Connected.
SQL> exec run_stat
BEGIN run_stat; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at "A.RUN_STAT", line 7
ORA-06512: at line 1
SQL>
SQL> connect /@ora92 as sysdba
Connected.
SQL> create or replace procedure run_stat as
2 begin
3 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_SIZE = 10485760';
4 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_RETAINED_SIZE = 10485760';
5
6
7 DBMS_STATS.GATHER_SCHEMA_STATS (
8 ownname => 'SCOTT',
9 estimate_percent => 10,
10 degree => 4,
11 cascade => TRUE);
12
13 end;
14 /
Procedure created.
SQL> exec run_stat;
PL/SQL procedure successfully completed.
SQL>
June 20, 2003 - 3:47 pm UTC
9.2.0.1?? you said 9.2.0.3 before so that is what I was using. Anyway, does not reproduce in 9.2.0.3, was an issue in 9.2.0.1, fixed in 9.2.0.2 and above
Roles for analyze
Emmett Cleveland, June 19, 2003 - 4:55 pm UTC
Peter, their is an issue with 9.2 and the gather_system_statistics role, Try granting that role to your executing user and run the procedure. If it does not work the work around is to create a new gather_system_statistics role and grant DBA to it.
The Real Answer
Mark A. Williams, June 19, 2003 - 5:01 pm UTC
Please review MetaLink Note: 203220.1
It has the answer to this issue...
Briefly:
SQL> create user b identified by b
2 default tablespace users
3 temporary tablespace temp;
User created.
SQL> grant create session, create table to b;
Grant succeeded.
SQL> connect b/b
Connected.
SQL> create table t
2 (
3 c1 number
4 );
Table created.
SQL> connect a/a
Connected.
SQL> create or replace procedure run_stat as
2 begin
3 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_SIZE = 10485760';
4 EXECUTE IMMEDIATE 'alter session SET SORT_AREA_RETAINED_SIZE = 10485760';
5 DBMS_STATS.GATHER_SCHEMA_STATS (
6 ownname => 'B',
7 estimate_percent => 10,
8 degree => 4,
9 cascade => TRUE);
10 end;
11 /
Procedure created.
SQL> exec run_stat
PL/SQL procedure successfully completed.
SQL> connect b/b
Connected.
SQL> create index ind1 on t(c1);
Index created.
SQL> connect a/a
Connected.
SQL> exec run_stat
BEGIN run_stat; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at "A.RUN_STAT", line 5
ORA-06512: at line 1
SQL> connect b/b
Connected.
SQL> grant select on t to a;
Grant succeeded.
SQL> connect a/a
Connected.
SQL> exec run_stat
PL/SQL procedure successfully completed.
There secret is: SQL> grant select on t to a;
HTH,
Mark
privileges required to run only dbms_stats.gather_schema_stats on other schemas.
Y SREENIVASA RAO, October 29, 2004 - 6:25 am UTC
Oracle version Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
Production database has one user called x with 6GB of (TEMP) temporary tablespace.I need to gather schema level statistics for x.but i dont have passwords of SYS OR X.So i have created user called STATS assigned with TEMP and trying to execute dbms_stats.
sql> grant create session,analyze any to stats;
SQL> connect stats/stats;
SQL> SQL> BEGIN dbms_stats.gather_schema_stats(ownname => 'X',estimate_percent => dbms_stats.auto_sample_size,granular
ity => 'ALL',method_opt => 'for all columns size AUTO',degree => dbms_stats.default_degree,options => 'GATHER AUTO',casc
ade => TRUE); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 7684
ORA-06512: at "SYS.DBMS_STATS", line 9624
ORA-06512: at "SYS.DBMS_STATS", line 9777
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 1
so what are the privileges required to run only gether_shema_stats for other schema.
Our security department doesn't give SYSDBA priv or DBA ROLE or overall control on x user.So dbms_stats should run under seperate user by DATACENTER OPERATIONS team.
Thanks in advance for ur great suggestion.
October 29, 2004 - 8:26 am UTC
ops$tkyte@ORA9IR2> drop user a cascade;
User dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by a;
User created.
ops$tkyte@ORA9IR2> <b>grant create session, analyze any to a;</b>
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> exec dbms_stats.gather_schema_stats( 'SCOTT' );
PL/SQL procedure successfully completed.
dbms_stats.method_opt question
A reader, May 20, 2006 - 9:13 pm UTC
Tom,
Does this analyze command gather histograms (for all columns) :
dbms_stats.gather_schema_stats(user,
estimate_percent => dbms_stats.auto_sample_size,
cascade => true,
degree => dbms_stats.default_degree
) ;
Does the above command gather histogram on all columns if method_opt parameter is not given ?
In other words, "method_opt parameter default value of "for all columns size 1" " will be used if it is omitted in the command ?
If it is, How to check the data dictionary to confirm it gathered histograms for all the columns ?
Thank you.
May 21, 2006 - 10:32 am UTC
depends on what method_opt defaults to which varies by release.
So, consult the documentation for your release.
If it uses for all columns size 1, it'll get just the high/low values and number of distinct values.
user_tab_histograms
A reader, May 21, 2006 - 11:19 am UTC
Thanks Tom.
DB release 9.2.0.5. Per Oracle documentation, method_opt parameter is using the default value of "FOR ALL COLUMNS SIZE 1".
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT FOR ALL COLUMNS SIZE 1,
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT DEFAULT,
cascade BOOLEAN DEFAULT FALSE,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
gather_temp BOOLEAN DEFAULT FALSE);
May 21, 2006 - 8:12 pm UTC
then, well, see right above... where I said what'll do with size 1.
A reader, May 21, 2006 - 8:28 pm UTC
Tom,
Thank you.
Would it be sufficient to use default value "for all columns size 1" for method_opt when gathering schema stats histograms.
Under what other circumstances, it is necessary to play with different "method_opt" size values.
Also, Would it be more effecient to use "for all columns size auto" rather than "for all columns size 1" or different size values.
May 21, 2006 - 8:33 pm UTC
I said you don't get histograms, you are getting "high/low values" and that be about it.
In general, gather histograms only when you need them - typically not in a transactional system - but rather in a data warehouse where you are likely not using binds on the final queries against the system.
A reader, June 17, 2008 - 9:56 pm UTC
Hi Tom, This is our current situation. Can you see what is wrong with the following.
Before doing a bulk load, we did a gather stats on the source system using the following command for each table
DBMS_STATS.gather_table_stats(<schema name>,<table name>,null,null,null,null,8);
Then at 3 AM everyday we have a job that gathers stats using the following command
DBMS_STATS.GATHER_SCHEMA_STATS(<schema name>, DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.AUTO_DEGREE,Method_Opt=>'FOR ALL INDEXED COLUMNS SIZE 1',cascade=>TRUE);
After doing this some of the queries are behaving badly. Even though we have certain columns indexed, those indexes are not showing up in the explain plan and only table access is being done. Does the gather_schema_stats command above pose any issues ? If it is, what is the best way to gather the stats ?
what are frequency required for Gather Schema Statistics
Amr Rizk, March 14, 2010 - 8:05 am UTC
we have Point of sales system. we are inserting daily round 200,000 rows into Oracle E-Business Suite R12 database (10g R2 10.2.0.3.0) . we are running Gather Schema Statistics for all DB schema weekly . please tell me if this is proper way or we should not run Gather Schema Statistics on regular basis . also if we should change this schedule please tell me how can we schedule it
March 15, 2010 - 11:02 am UTC
In 10g, if you don't know how often you want to gather, you could use the automated job that will gather 'stale'. We monitor tables and when about 10% of the table has been modified - we gather statistics on the related segments.
Probably - this is already happening, check with your DBA.
Gather Schema Statistics options
A reader, March 17, 2010 - 3:42 am UTC
Dear Sir , thank you for your answer . please clarify this word " you could use the automated job that will gather 'stale'" . i'll expalin what we are doing . we are schdule concurrent request " Gather Schema Statistics" with default options to gather all objects in DB weekly but now it takes 5 hours to finish .we will plan to use option "GATHER AUTO" to gather only changed objects . is this what you mean ?
March 17, 2010 - 8:40 am UTC
if you are using Oracle apps - as it appears you are but have neglected to even think about mentioning - I assume that from "concurrent request" - then you shall use the method Oracle apps says to use and you shall use the job they provide to do so (as would be true for all 3rd party applications - if they tell you "gather stats this way", you will gather stats that way)..
A reader, March 21, 2010 - 8:51 am UTC
Dear ,
yes I’m using Oracle Apps (12.0.6).as my understand from your answer . you asked me to gather schema with " Gather Auto " option which will use table monitoring .my questions are
1- " should I enable table monitoring for all schemas in DB before i run gather using " Gather Auto " option or table monitoring feature is enable by default ?
2- Is there any impact of running "Gather Schema " with Gather Auto option because I asked also Oracle Forums in OTN and they replied i should not use this option and run gather schema with default option but not for all schemas only for used schemas (HR,Applsys,....etc)
March 22, 2010 - 8:47 am UTC
No, I said "you shall gather stats the way Oracle Apps tells you to"
they have a documented procedure, fnd_stats, that you use - read up on it, do what they tell you to do, in the way they tell you to do it.
How to find out what triggered 'analyze of all tables'
Anto, May 20, 2010 - 9:53 am UTC
Hi Tom,
I find that all tables in our schema have been analyzed around 30 minutes back, by querying last_analyzed_date of user_tables view.
Question : How to find out what kicked off this analyze - whether this was kicked manually by someone or it was triggered by some daily scheduled job or it was triggered due to DML changes beyond the cutoff(monitoring is ON for these tables) ?
I queried v$sql but could not find any dbms_stats or analyze tables in there which was run recently. I also queried ba_scheduler_job_run_XXX views but no job ran within the past few hours.
thanks
Anto
Anto, May 20, 2010 - 9:53 am UTC
Sorry - version is Oracle 10g
thanks
Anto
Stats gatherd for More tables than expected
Pugal Rangasamy, February 07, 2012 - 6:47 pm UTC
Hi Tom,
We are currently running exec dbms_stats.gather_schema_stats('MART',estimate_percent=>40,degree=>10,cascade=>TRUE,options=>'GATHER AUTO'); every day.
When I run the following command before running the stats, I get 7 eligible tables for gathering Stats
SQL> select count(*)
2 from (
3 select a.table_owner,A.table_name,total_mod,num_rows,
4 case
5 when total_mod > round(num_rows*0.1) then 0
6 else round(num_rows*0.1) - total_mod
7 end rows_to_go
8 from (select table_owner, table_name, sum(inserts+updates+deletes)total_mod
9 from sys.DBA_TAB_MODIFICATIONS
10 group by table_owner, table_name) a ,
11 dba_tables b
12 where a.table_name = B.TABLE_NAME
13 and A.TABLE_OWNER = B.OWNER
14 and b.owner in ('MART'))
15 where rows_to_go = 0;
COUNT(*)
----------
7
After gathering the stats, when I run the following query I get 503
SQL> select count(*)
2 from dba_tab_stats_history
3 where owner = 'MART'
4 and trunc(stats_update_time) = trunc(sysdate-1);
COUNT(*)
----------
503
Could you please explain how stats got collected for 503 tables? All the tables in that schema have existing statistics so I cannot think the rest are gathered due to non availability of the stats.
February 08, 2012 - 1:36 am UTC
you didn't ask for gather stale, you asked for gather auto, auto is not the same as stale. You seemed to have wanted stale since your query was looking at dba_tab_modifications.
also, you should use list stale to get a count of stale objects since the dba_tab_modifications view is not maintained in real time - the "real" values are in the SGA and get flushed to the monitoring tables when you gather stats (so, there are likely tables you cannot "see" that exceed the 10% threshold).
Also, 10% might NOT be the threshold on your system, it is configurable.
dbms_stats.auto_sample_percent.........
Pugal Rangasamy, February 14, 2012 - 11:56 am UTC
Thanks for explaining it Tom. I’m trying to see if the gather stats time can be reduced as its taking too much time. This database is “Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi”.
Following is the code I use to gather the Table stats.
DECLARE
lt_obj_list dbms_stats.ObjectTab;
CURSOR cu1 IS
SELECT DISTINCT owner
FROM dba_tables
WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP');
BEGIN
dbms_output.put_line('Stats Start time - '||to_char(sysdate,'dd-mon-yyyy hh:mi:ss am'));
-- Get Schema with Tables
FOR cr1 IN cu1 LOOP
dbms_stats.gather_schema_stats(ownname => cr1.owner, objlist => lt_obj_list, options => 'LIST STALE'); --LIST STALE/LIST AUTO
-- Check to see if the schema has any eligible tables
IF lt_obj_list.count > 0 THEN
FOR i IN lt_obj_list.FIRST .. lt_obj_list.LAST LOOP
dbms_output.put_line(lt_obj_list(i).ownname || '.' || lt_obj_list(i).ObjName || ' ' || lt_obj_list(i).ObjType || ' ' || lt_obj_list(i).partname||to_char(sysdate,'dd-mon-yyyy hh:mi:ss am'));
IF lt_obj_list(i).ObjType = 'TABLE' THEN
DBMS_STATS.GATHER_TABLE_STATS(ownname => lt_obj_list(i).ownname,
tabname => lt_obj_list(i).ObjName,
estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
degree => 5,
--granularity => dbms_stats.GET_PARAM('GRANULARITY'),
cascade => TRUE);
END IF;
END LOOP;
END IF;
END LOOP;
dbms_output.put_line('Stats End time - '||to_char(sysdate,'dd-mon-yyyy hh:mi:ss am'));
END;
I wanted to set a standard estimate_percent to 20% but I tried to use AUTO_SAMPLE_SIZE to getter better sampling percentage. When I look at the num_rows and sample size to see the percentage used for estimation, I see most of the table have used 100%
SQL> SELECT TRUNC(last_analyzed),COUNT(*)
2 FROM dba_tables
3 WHERE TRUNC(last_analyzed) = TRUNC(SYSDATE-1)
4 GROUP BY TRUNC(last_analyzed);
TRUNC(LAST_ANALYZED) COUNT(*)
-------------------- ----------
2/13/2012 365
SQL> SELECT sample_percent, COUNT(*)
2 FROM (SELECT owner,
3 table_name,
4 num_rows,
5 sample_size,
6 last_analyzed,
7 decode(num_rows, 0, 0, ROUND((sample_size / num_rows) * 100, 2)) sample_percent
8 FROM dba_tables
9 WHERE trunc(last_analyzed) = TRUNC(SYSDATE - 1)
10 AND num_rows > 0)
11 GROUP BY sample_percent
12 ORDER BY sample_percent DESC;
SAMPLE_PERCENT COUNT(*)
-------------- ----------
100 327
25 3
23.7 1
19.89 1
18.83 1
18.59 1
18.47 1
15.84 1
15.78 1
15 1
13.53 1
13.5 1
11.61 1
9.09 1
8.75 1
3.07 1
2.85 1
2.74 1
2.52 1
2.41 1
2.17 1
1.21 1
1.05 1
0.92 1
0.06 1
25 rows selected
What is the reason 100% sampling is used for stats collection? Is there anything that contributes to the stats being stale other than Insert, Update, Delete. The load program collects all the table data modifications and sends us a report. Based on that, I cannot explain how half of the tables are eligible for stats gathering.
You mentioned before “Also, 10% might NOT be the threshold on your system, it is configurable” Where do I look for that value?
gather_schem_statistics and stale statistics
varun bahl, October 29, 2021 - 10:19 am UTC
Hi Tom,
we recently moved to oracle exadata 19c on GOS. There are 2 issues i am facing regarding huge partitioned tables.
First, the list stale option of gather_schema_schema stats of showing more partitions (eg 10 partitions) are stale while the dba_tab_statistics column stale_stats is showing only few partitions (e.g. only 2 ) are stale.
Second, the extra partitions which appear stale according to stale list option of gather_schema_stats are not even touched (no dml) after they were last analyzed. so why are they becoming stale.
Confusion is why the stale list of gather_schema_stats is more and not matching with the list from dba_tab_statistics.
Impact of the above issue is, when we gather the stats through gather_table_statistics, it takes hours because it gather stats on those partitions as well which were never touched after they were last analyzed. We have daily partitions of approx 200 million in each partition.
November 01, 2021 - 4:09 am UTC
We need some *data*
- show us the stale listing before gather
- show us the stale listing after gather
- show us dba_tab_modifications before and after
etc etc
We can't help you without data to look at