Auto Stats gathering not including my schema? [message #507526] |
Mon, 16 May 2011 12:07 |
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 |
|
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 |
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 #507530 is a reply to message #507529] |
Mon, 16 May 2011 12:29 |
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 #507533 is a reply to message #507532] |
Mon, 16 May 2011 12:43 |
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 #507536 is a reply to message #507534] |
Mon, 16 May 2011 13:14 |
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 #507538 is a reply to message #507537] |
Mon, 16 May 2011 13:26 |
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 #507712 is a reply to message #507700] |
Tue, 17 May 2011 10:12 |
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>
|
|
|
|