?
Solved

DBMS_AUTO_TASK_ADMIN.ENABLE does not collect statistics automatically?

Posted on 2014-03-08
6
Medium Priority
?
587 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

765 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