Solved

DBMS_AUTO_TASK_ADMIN.ENABLE does not collect statistics automatically?

Posted on 2014-03-08
6
580 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Developer 6 75
Use of Exception to end a Loop 3 54
use lov values 2 72
Oracle DBLINKS From 11g to 8i 3 67
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

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