sakthikumar
asked on
DBMS_AUTO_TASK_ADMIN.ENABLE does not collect statistics automatically?
Hi,
I executed the below in my oracle 11g, but still no automatic statistics.
I am checking the analyzed column in user_tables, still showing old date.
Is there any other parameter I need to change/set?
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABL E( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;
Also the flag "GATHER_STATS_JOB" is diabled, should I need to make it true.?
I executed the below in my oracle 11g, but still no automatic statistics.
I am checking the analyzed column in user_tables, still showing old date.
Is there any other parameter I need to change/set?
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABL
END;
Also the flag "GATHER_STATS_JOB" is diabled, should I need to make it true.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL> SELECT COUNT(1) FROM GENCARGO.YARD_VEHICLE_ROWS ;
and reply with results to this record count.
How is this table being loaded (import, datapump, SQLloader?) and what is the expected row count?
and reply with results to this record count.
How is this table being loaded (import, datapump, SQLloader?) and what is the expected row count?
ASKER
This table is not currently used. it is an old table.
This table is populated from java front end.
It is having only 6 rows. I am just telling from last_analyzed time in user_tables, which it is an old date.
Like this there are many tables. But I dont know the exact reason why these were not analyzed.
This table is populated from java front end.
It is having only 6 rows. I am just telling from last_analyzed time in user_tables, which it is an old date.
Like this there are many tables. But I dont know the exact reason why these were not analyzed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GREAT. Thankz for the explanation. Now, I have some clarity.
ASKER
Now I can check the status using the query. It is enabled.
But some of the tables are not analyzed, Unable to find the reason.
Here is one table which is not analyzed.
CREATE TABLE "GENCARGO"."YARD_VEHICLE_R
( "ROW_NUMBER" NUMBER(4,0),
"BLOCK_CODE" VARCHAR2(6),
"YARD_CODE" VARCHAR2(6),
"ROW_LENGTH" NUMBER(4,0),
"ROW_WIDTH" NUMBER(4,0),
"NO_OF_VEHICLES" NUMBER(4,0),
"USED_SPACE" NUMBER(4,0),
"FREE_SPACE" NUMBER(4,0),
"IS_VALID" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE,
"CCN_NUMBER" NUMBER(8,0) DEFAULT 1 NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(50) NOT NULL ENABLE,
"CREATED_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"OU_ID" NUMBER DEFAULT 100100000000 NOT NULL ENABLE,
"LAST_MODIFIED_BY" VARCHAR2(50),
"LAST_MODIFIED_DATE" DATE,
"VEHICLES_UOM_ID" NUMBER(10,0),
"PORT_CODE" VARCHAR2(1),
"TERMINAL_ID" VARCHAR2(2),
CONSTRAINT "XPKBB_YARD_BLOCK_ROW_PK" PRIMARY KEY ("YARD_CODE", "BLOCK_CODE", "ROW_NUMBER")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GCEXP_DATA" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GCEXP_DATA" ;
CREATE UNIQUE INDEX "GENCARGO"."XPKBB_YARD_BLO
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GCEXP_DATA" ;
ALTER TABLE "GENCARGO"."YARD_VEHICLE_R
ALTER TABLE "GENCARGO"."YARD_VEHICLE_R
ALTER TABLE "GENCARGO"."YARD_VEHICLE_R
ALTER TABLE "GENCARGO"."YARD_VEHICLE_R
ALTER TABLE "GENCARGO"."YARD_VEHICLE_R
ALTER TABLE "GENCARGO"."YARD_VEHICLE_R
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GCEXP_DATA" ENABLE;