Home » RDBMS Server » Performance Tuning » Auto Stats gathering not including my schema? (oracle 11g solaris 10)
Auto Stats gathering not including my schema? [message #507526] Mon, 16 May 2011 12:07 Go to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Hi all,
I have several databases that i've recently upgraded from 9i to 11g. With all of them, the automatic stats gathering process has worked just fine every night during the maintenance window.

However, i have this other database that i created and it seems that the only stats being gathered are on the sys and system schemas and not the actual schema that holds all of our tables.

I did some searching, but i'm not sure i was using the right search terms, because i came up empty.

Could someone please offer some guidance on where to find out if i have a setup issue or whatever?

Thanks

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Re: Auto Stats gathering not including my schema? [message #507527 is a reply to message #507526] Mon, 16 May 2011 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it seems that the only stats being gathered are on the sys and system schemas and not the actual schema that holds all of our tables.
post SQL & results that lead you to your conclusion.

While default behavior is for job to collect statistics runs once every 24 hours; this does NOT mean that LAST_ANALYZED will contain recent date.

actual new statistics are only really collected after about 10% of rows change; otherwise existing statistics are close enough.
LAST_ANALYZED changes only after actual new collection & not daily.

[Updated on: Mon, 16 May 2011 12:24]

Report message to a moderator

Re: Auto Stats gathering not including my schema? [message #507528 is a reply to message #507527] Mon, 16 May 2011 12:15 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member

  1  select owner, trunc(last_analyzed),count(*) from dba_tab_statistics
  2  where owner in ('S','SYS','SYSTEM')
  3  group by owner, trunc(last_analyzed)
  4* order by owner
13:11:49 >/

OWNER                          TRUNC(LAS   COUNT(*)
------------------------------ --------- ----------
S                              16-MAY-11          1
S                                                99
SYS                            18-MAR-11        880
SYS                            20-MAR-11        198
SYS                            09-MAY-11          3
SYS                                             970
SYSTEM                         18-MAR-11        181
SYSTEM                                           15

8 rows selected.


the S schema is the one i'm working on here. the only reason 1 table has stats there is because we manually gathered them on that table.
Re: Auto Stats gathering not including my schema? [message #507529 is a reply to message #507528] Mon, 16 May 2011 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
exec dbms_stats.gather_schema_stats ('S');
Re: Auto Stats gathering not including my schema? [message #507530 is a reply to message #507529] Mon, 16 May 2011 12:29 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
right. i know how to manually gather the stats. It just worried me that the automated maintenance task wasn't doing it like has been the case with every other 11g db creation i've done in the past few months.

Will gathering stats initially cause the auto stats gathering process to start filling in the gaps when stats go stale?

I appreciate your help on this. You guys are light years beyond me in this stuff.
Re: Auto Stats gathering not including my schema? [message #507532 is a reply to message #507530] Mon, 16 May 2011 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OWNER                          TRUNC(LAS   COUNT(*)
------------------------------ --------- ----------
S                              16-MAY-11          1

It appears 1 table in "S" schema has statistics.
Were statistics collected manually?
Do all the tables in S schema actually have rows NOW?
Re: Auto Stats gathering not including my schema? [message #507533 is a reply to message #507532] Mon, 16 May 2011 12:43 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
BlackSwan wrote on Mon, 16 May 2011 13:36
OWNER                          TRUNC(LAS   COUNT(*)
------------------------------ --------- ----------
S                              16-MAY-11          1

It appears 1 table in "S" schema has statistics.
Were statistics collected manually?
Do all the tables in S schema actually have rows NOW?


yes. as i said in my previous reply, the only reason that one has stats is because we manually gathered them on that table. it's a temp table that we empty and fill before doing some processing so we gathered stats after creating all the rows.

yes. the tables have rows in them.

here's just 3 of the main tables that the DB is built off of:

13:32:38 >select count(*) from s.item;

  COUNT(*)
----------
    248238

13:36:27 >select count(*) from s.item_purchase_source;

  COUNT(*)
----------
    392112

13:38:17 >select count(*) from s.inventory;

  COUNT(*)
----------
    441377

13:40:04 >;
  1  select table_name, num_rows, last_analyzed
  2  from dba_tab_statistics
  3* where table_name in ('ITEM','INVENTORY','ITEM_PURCHASE_SOURCE')
13:40:06 >/

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
INVENTORY
ITEM
ITEM_PURCHASE_SOURCE



Re: Auto Stats gathering not including my schema? [message #507534 is a reply to message #507533] Mon, 16 May 2011 12:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
hmm, does not appear to be daily on my DB
  1  select job_name, last_start_date, next_run_date from dba_scheduler_jobs
  2  where job_name like '%STAT%'
  3* order by 2
SQL> /

JOB_NAME		       LAST_START_DATE				NEXT_RUN_DATE
------------------------------ ---------------------------------------- ----------------------------------------
MGMT_STATS_CONFIG_JOB	       01-MAY-11 07.03.22.873270 AM -07:00	01-JUN-11 01.01.01.900000 AM -07:00
BSLN_MAINTAIN_STATS_JOB        15-MAY-11 06.58.42.779470 AM -07:00	22-MAY-11 12.00.00.900000 AM -07:00
Re: Auto Stats gathering not including my schema? [message #507536 is a reply to message #507534] Mon, 16 May 2011 13:14 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
my experience with 11g so far is that the autotask client executes the stats gathering every night to gather stale statistics during the maintenance window defined.

JOB_NAME                  LAST_START_DATE NEXT_RUN_DATE
------------------------- --------------- --------------------------------------------------
MGMT_STATS_CONFIG_JOB                     01-APR-11 01.01.01.100000 AM -04:00
MGMT_STATS_CONFIG_JOB_1
BSLN_MAINTAIN_STATS_JOB                   20-MAR-11 12.00.00.000000 AM -04:00



i believe this ask tom artical backs that up:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1133388300346992024

however, i'm still not sure why stats aren't being gathered at all for my schema.


14:08:33 >;
  1  select status, window_group from dba_autotask_client
  2* where client_name = 'auto optimizer stats collection'
14:08:34 >/

STATUS   WINDOW_GROUP
-------- ----------------------------------------------------------------
ENABLED  ORA$AT_WGRP_OS



14:09:54 >;
  1  SELECT window_group_name, enabled, number_of_windowS
  2* FROM   dba_scheduler_window_groups
14:09:55 >/

WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
------------------------------ ----- -----------------
MAINTENANCE_WINDOW_GROUP       TRUE                  7
ORA$AT_WGRP_OS                 TRUE                  7
ORA$AT_WGRP_SA                 TRUE                  7
ORA$AT_WGRP_SQ                 TRUE                  7



14:10:37 >select window_group_name, window_name 
14:10:58   2  from dba_scheduler_wingroup_members
14:11:03   3  where window_group_name = 'ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
ORA$AT_WGRP_OS                 MONDAY_WINDOW
ORA$AT_WGRP_OS                 TUESDAY_WINDOW
ORA$AT_WGRP_OS                 WEDNESDAY_WINDOW
ORA$AT_WGRP_OS                 THURSDAY_WINDOW
ORA$AT_WGRP_OS                 FRIDAY_WINDOW
ORA$AT_WGRP_OS                 SATURDAY_WINDOW
ORA$AT_WGRP_OS                 SUNDAY_WINDOW


Re: Auto Stats gathering not including my schema? [message #507537 is a reply to message #507536] Mon, 16 May 2011 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1* select job_name, state, enabled, run_count, failure_count from dba_scheduler_jobs	where job_name like '%STAT%'
SQL> /

JOB_NAME		       STATE	       ENABL  RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ----- ---------- -------------
BSLN_MAINTAIN_STATS_JOB        SCHEDULED       TRUE	     55 	    0
MGMT_STATS_CONFIG_JOB	       SCHEDULED       TRUE	     13 	    0
Re: Auto Stats gathering not including my schema? [message #507538 is a reply to message #507537] Mon, 16 May 2011 13:26 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member

14:11:37 >select job_name, state, enabled, run_count, failure_count from dba_scheduler_jobs    
 where job_name like '%STAT%';

JOB_NAME                  STATE           ENABL  RUN_COUNT FAILURE_COUNT
------------------------- --------------- ----- ---------- -------------
MGMT_STATS_CONFIG_JOB     SCHEDULED       TRUE           0             0
MGMT_STATS_CONFIG_JOB_1   SCHEDULED       TRUE           0             0
BSLN_MAINTAIN_STATS_JOB   SCHEDULED       TRUE           0             0

Re: Auto Stats gathering not including my schema? [message #507539 is a reply to message #507538] Mon, 16 May 2011 13:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> show parameter job

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes		     integer	 1000

Re: Auto Stats gathering not including my schema? [message #507540 is a reply to message #507539] Mon, 16 May 2011 13:29 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member

14:23:21 >show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
Re: Auto Stats gathering not including my schema? [message #507693 is a reply to message #507540] Tue, 17 May 2011 09:41 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
does anyone else have any suggestions on what to check?

Thanks
Re: Auto Stats gathering not including my schema? [message #507700 is a reply to message #507693] Tue, 17 May 2011 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post SQL & results that prove DBMS_SCHEDULER works within this DB; a job, any job
Re: Auto Stats gathering not including my schema? [message #507712 is a reply to message #507700] Tue, 17 May 2011 10:12 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
yessir.

SQL> create table test_table (value date);

Table created.

SQL> ; 
  1  create or replace procedure test_proc
  2  is
  3  begin
  4     insert into s.test_table (value)
  5     select sysdate from s.dual;
  6* end;
SQL> /

Procedure created.

SQL> select * from test_table;

no rows selected

SQL> BEGIN
  2  dbms_scheduler.create_program(
  3     program_name => 'test_prog',
  4     program_type => 'STORED_PROCEDURE',
  5     program_action => 's.test_proc',
  6     number_of_arguments => 0,
  7     enabled => TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  dbms_scheduler.create_job (
  3     job_name => 'test_prog_job',
  4     program_name => 'test_prog',
  5     start_date => SYSTIMESTAMP,
  6     repeat_interval => 'freq=hourly; byminute=0',
  7     end_date => NULL,
  8     enabled => TRUE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from user_scheduler_job_run_details;

no rows selected

SQL> BEGIN
  2     dbms_scheduler.run_job('test_prog_job',TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select owner, job_name, status, actual_start_date, run_duration from user_scheduler_job_run_details
SQL> /

OWNER  JOB_NAME           STATUS     ACTUAL_START_DATE                   RUN_DURATION
------ ------------------ ---------- ----------------------------------- ---------------
S      TEST_PROG_JOB      SUCCEEDED  17-MAY-11 11.05.54.631215 AM -04:00 +000 00:00:00

1 row selected.

SQL> select * from s.test_table;

VALUE
---------
17-MAY-11

1 row selected.

SQL> 
Re: Auto Stats gathering not including my schema? [message #508344 is a reply to message #507712] Fri, 20 May 2011 07:09 Go to previous message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Sorry to bump this, just was curious if anyone else had some ideas of things to try.

Thanks
Previous Topic: Query tuning
Next Topic: Tuning sub queries with function call
Goto Forum:
  


Current Time: Thu Mar 28 16:17:17 CDT 2024