Oracle Database Discussions

hi ,

i'm trying to see if gather incremental stats is good for my huge table
i did a test case with subpartition but didn't see that stats were incremental
i tried with partition table and the results are the same
my version
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production


here is the test case:

CREATE TABLE "ZVIKA"."BIG2"
  (
    "OWNER"          VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "OBJECT_NAME"    VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "SUBOBJECT_NAME" VARCHAR2(30 BYTE),
    "OBJECT_ID"      NUMBER NOT NULL ENABLE,
    "DATA_OBJECT_ID" NUMBER,
    "OBJECT_TYPE"    VARCHAR2(19 BYTE),
    "CREATED" DATE NOT NULL ENABLE,
    "LAST_DDL_TIME" DATE NOT NULL ENABLE,
    "TIMESTAMP"    VARCHAR2(19 BYTE),
    "STATUS"       VARCHAR2(7 BYTE),
    "TEMPORARY"    VARCHAR2(1 BYTE),
    "GENERATED"    VARCHAR2(1 BYTE),
    "SECONDARY"    VARCHAR2(1 BYTE),
    "NAMESPACE"    NUMBER NOT NULL ENABLE,
    "EDITION_NAME" VARCHAR2(30 BYTE)
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
  (
    BUFFER_POOL DEFAULT
  )
  TABLESPACE "USERS" PARTITION BY RANGE
  (
    "CREATED"
  )
 
  (
    PARTITION "P100401" VALUES LESS THAN (TO_DATE(' 2010-04-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT) TABLESPACE "USERS" ,
    PARTITION "P100402" VALUES LESS THAN (TO_DATE(' 2010-04-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT) TABLESPACE "USERS" ,
    PARTITION "P100403" VALUES LESS THAN (TO_DATE(' 2010-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT) TABLESPACE "USERS"
  )
;


 BEGIN
 DBMS_STATS.SET_TABLE_PREFS(OWNNAME=>'ZVIKA',TABNAME=>'BIG2',PNAME=>'INCREMENTAL',PVALUE=>'TRUE') ;
 END;
 /



INSERT    INTO ZVIKA.BIG2  b
  SELECT    OWNER,
  OBJECT_NAME,
  SUBOBJECT_NAME,
  OBJECT_ID,
  DATA_OBJECT_ID,
  OBJECT_TYPE,
  TO_DATE('2009/04/03 13:00','YYYY/MM/DD HH24:MI'),
  LAST_DDL_TIME,
  TIMESTAMP,
  'VALID',
  TEMPORARY,
  GENERATED,
  SECONDARY,
  NAMESPACE,
  EDITION_NAME
FROM SYSTEM.Z1_DBA_OBJECTS1 a ;






 select 
 dbms_stats.get_prefs('CASCADE',ownname=>'ZVIKA', tabname=>'BIG2') AS CASCADE, 
 dbms_stats.get_prefs('DEGREE',ownname=>'ZVIKA', tabname=>'BIG2') AS DEGREE, 
 dbms_stats.get_prefs('ESTIMATE_PERCENT',ownname=>'ZVIKA', tabname=>'BIG2') AS ESTIMATE_PERCENT, 
 dbms_stats.get_prefs('METHOD_OPT',ownname=>'ZVIKA', tabname=>'BIG2') AS METHOD_OPT, 
 dbms_stats.get_prefs('GRANULARITY',ownname=>'ZVIKA', tabname=>'BIG2') AS GRANULARITY,
 dbms_stats.get_prefs('STALE_PERCENT',ownname=>'ZVIKA', tabname=>'BIG2') AS STALE_PERCENT,
 dbms_stats.get_prefs('INCREMENTAL',ownname=>'ZVIKA', tabname=>'BIG2') AS INCREMENTAL,
  dbms_stats.get_prefs('PUBLISH',ownname=>'ZVIKA', tabname=>'BIG2') AS publish
  from dual; 


"CASCADE"	"DEGREE"	"ESTIMATE_PERCENT"	"METHOD_OPT"	"GRANULARITY"	"STALE_PERCENT"	"INCREMENTAL"	"PUBLISH"
"DBMS_STATS.AUTO_CASCADE"	"NULL"	"DBMS_STATS.AUTO_SAMPLE_SIZE"	"FOR ALL COLUMNS SIZE AUTO"	"AUTO"	"10"	"TRUE"	"TRUE"




BEGIN
dbms_stats.GATHER_TABLE_STATS(ownname=>'ZVIKA',TABNAME=>'BIG2');
END;



BEGIN
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
END;
/ 

SELECT A.LAST_ANALYZED,SYSDATE, a.stale_stats, A.owner,a.table_name,a.partition_name,a.global_stats
FROM DBA_TAB_STATISTICS A WHERE owner = 'ZVIKA' AND TABLE_NAME = 'BIG2'
;

"LAST_ANALYZED"	"SYSDATE"	"STALE_STATS"	"OWNER"	"TABLE_NAME"	"PARTITION_NAME"	"GLOBAL_STATS"
04/04/10 15:35	04/04/10 15:38	"NO"	"ZVIKA"	"BIG2"	""	"YES"
04/04/10 15:35	04/04/10 15:38	"NO"	"ZVIKA"	"BIG2"	"P100401"	"YES"
04/04/10 15:35	04/04/10 15:38	"NO"	"ZVIKA"	"BIG2"	"P100402"	"YES"
04/04/10 14:52	04/04/10 15:38	"NO"	"ZVIKA"	"BIG2"	"P100403"	"YES"

i saw that the stats are GLOBAL and the last analyzed is almost identical.
i loaded anoter partition and checked again that only one partition and the global stats are stale.
tried to gather again with the same results.
i saw that only one partition didn't get analyzed (P100403) while all other partitions and global stats got analyzed.

what am i missing here ?




Zvika