Solved

DBMS_AUTO_TASK_ADMIN.ENABLE does not collect statistics automatically?

Posted on 2014-03-08
6
567 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.

Join & Write a Comment

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now