Solved

DBMS_AUTO_TASK_ADMIN.ENABLE does not collect statistics automatically?

Posted on 2014-03-08
6
578 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

808 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