Solved

DBMS_AUTO_TASK_ADMIN.ENABLE does not collect statistics automatically?

Posted on 2014-03-08
6
583 Views
Last Modified: 2014-03-15
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.ENABLE( 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.?
0
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 23

Accepted Solution

by:
David earned 500 total points
ID: 39916275
You understand that the automatic part is based upon service windows, yes?

Run and post the results please:

-- reenable collection of optimizer statistics
desc dba_autotask_client

col client_name format a35
col mean_job_duration format a30

SELECT client_name, status, mean_job_duration
FROM dba_autotask_client;

BEGIN
  dbms_auto_task_admin.enable('auto optimizer stats collection', NULL,
  NULL);
END;
/

SELECT client_name, status, mean_job_duration
FROM dba_autotask_client;

And read more from the source, http://psoug.org/reference/dbms_auto_task_admin.html
0
 

Author Comment

by:sakthikumar
ID: 39922776
Many Thanks DVZ.

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_ROWS"
   (      "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_BLOCK_ROW_PK" ON "GENCARGO"."YARD_VEHICLE_ROWS" ("YARD_CODE", "BLOCK_CODE", "ROW_NUMBER")
  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_ROWS" MODIFY ("OU_ID" NOT NULL ENABLE);
  ALTER TABLE "GENCARGO"."YARD_VEHICLE_ROWS" MODIFY ("CREATED_DATE" NOT NULL ENABLE);
  ALTER TABLE "GENCARGO"."YARD_VEHICLE_ROWS" MODIFY ("CREATED_BY" NOT NULL ENABLE);
  ALTER TABLE "GENCARGO"."YARD_VEHICLE_ROWS" MODIFY ("CCN_NUMBER" NOT NULL ENABLE);
  ALTER TABLE "GENCARGO"."YARD_VEHICLE_ROWS" MODIFY ("IS_VALID" NOT NULL ENABLE);
  ALTER TABLE "GENCARGO"."YARD_VEHICLE_ROWS" ADD 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;
0
 
LVL 23

Expert Comment

by:David
ID: 39924274
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sakthikumar
ID: 39925732
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.
0
 
LVL 23

Assisted Solution

by:David
David earned 500 total points
ID: 39926254
Well, the approximate reason (cannot point to the exact doc) is that the auto-analyzer recognizes the table is stale and extremely small; so it's highly unlikely that a six-row table will need an index, nor offer any difficultly to the CBO.  Does that satisfy you?
0
 

Author Closing Comment

by:sakthikumar
ID: 39932146
GREAT. Thankz for the explanation. Now, I have some clarity.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question