Oracle schema, database & system statistics - set to auto gather seems to have stopped auto gathering. What table is the stats actually stored?

Help, It seems like just this week that our production database has quit collecting stats. after our flow is ran.
I'm concerned that space might be low is why.. But not sure.  What sys table is stats. stored?  How can I check and see if this is the issue.

Use to be when checking and running looking for stale the sql came right back but now it just runs and runs after a flow.
Then refreshes of mviews is taking much longer also.
I need to get the stats back on track.

The only thing I could think of was storage.

any suggestions.

Thanks...
ValeneSueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
DBA_TABLES has a LAST_ANALYZED field which should tell you the last time the table was analyzed.

Most of the table statistics are stored in DBA_TABLES.  You can also look at DBA_HISTOGRAMS for more information.

Any space issues should be logged in the alert log for the database.

I have not worked with auto gathering of stats, so I don't know how much more information I can give.
0
Mark GeerlingsDatabase AdministratorCommented:
And the index statistics are in: DBA_INDEXES.  If you use table partitions, you will also need to check: DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS.

If you don't have DBA priviliege, these views have corresponding "ALL..". or "USER..." views that also contain the LAST_ANALYZED column.

Do you know which job or process used to collect statistics in your database?
Which Oracle version do you have?
0
ValeneSueAuthor Commented:
Oracle Version is 9.2.7.0 and
Exec dbms_stats.gather_schema_stats(ownname=> ' pppp',-
options=>'gather auto');

exec dbms_stats.gather_database_stats(options=>'auto gather');


Has worked great for almost a year now it seems we have a problem.

It doesn't seem like stats is being auto gathered after nightly flow.

We do have one huge table that I partitioned.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ValeneSueAuthor Commented:
O.K., looking at stale stats findally got an error take look.. Help .. Every see something like this?

SQL> exec dbms_stats.gather_schema_stats(ownname=>'pppp',-
> options=>'gather stale');
BEGIN dbms_stats.gather_schema_stats(ownname=>'pppp', options=>'gather stale'); END;

*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 10301
ORA-06512: at "SYS.DBMS_STATS", line 10784
ORA-06512: at "SYS.DBMS_STATS", line 10987
ORA-06512: at "SYS.DBMS_STATS", line 11036
ORA-06512: at "SYS.DBMS_STATS", line 11013
ORA-06512: at line 1
0
Mark GeerlingsDatabase AdministratorCommented:
Problems in an Oracle-supplied package (like: SYS.DBMS_STATS) are usually best handled by Oracle Technical Support.  Have you tried that?
0
johnsoneSenior Oracle DBACommented:
I did a quick MetaLink search for that error from DBMS_STATS and found nothing.

Since Oracle will not give us the code, we cannot trace down what it is trying to do at that line number.

Your best bet at this point is going to be Oracle support.  Hopefully they can tell you what it is trying to do.
0
ValeneSueAuthor Commented:
I searched on metalink also.
Statistics is definately our issue.   I'm thinking I'm going to drop the schema stats this weekend and re-gather everything.  Just start from scratch.

Things were going good until Monday then things declined more and more.
I do have a Oracle TAR open on the subject.
I tell you a lot of time I get better answer on here.
0
Mark GeerlingsDatabase AdministratorCommented:
I agree that usually the responses here are a lot faster than on MetaLink, but in this case the problem is internal to one of Oracle's stored proceduers that they distribute "wrapped" (or encrypted) so we can't help you troubleshoot it.
0
FayyazCommented:
you can use this

exec dbms_stats.gather_schema_stats(ownname=>'pppp', options=>'gather auto');
0
ValeneSueAuthor Commented:
This is what I use.  It's worked for almost a year and now it's not.  It's not auto gathering for all the tables / indexes.
0
FayyazCommented:
The gather auto option, gathers stats for those tables, which has more than 10% changes since last stats were gathered. You have to write some procedure to automate this.


I use this dynamix SQL to gather stats on tables.


SELECT 'execute sys.DBMS_STATS.gather_table_stats(ownname=>'||''''||owner||''''||',tabname=>'||''''||table_name||''''||',estimate_percent=> 15 );'
from all_tables where owner not in ('SYS','SYSTEM','OUTLN') order by owner,table_name

If you want to use the above sql for schema, you can modify it.

sqlplus -s system/$PWDSYSTEM@DB  << .
set head off
set linesize 200

spool /home/orasoft/all_stats/stat_db.sql
SELECT 'execute sys.DBMS_STATS.gather_schema_stats('||''''||username||''''||',options => '||''''||'GATHER AUTO'||''''||');'
from all_users where username not in ('SYS','SYSTEM','OUTLN') order by username;
spool off;

set timing on
spool /home/orasoft/stats_all/log/stat_compl_db_${date1}.log
@/home/orasoft/all_stats/stat_db.sql
spool off;
exit
.

But what i suggest is, always collect table level stats.
0
FayyazCommented:
The relevant tables from which oracle checks stats to be gathered for which tables and leave others ones are

dba_tab_modifications, user_tab_modifications, all_tab_modifcations

But data only goes to above tables, if monitoring is on for tables
0
ValeneSueAuthor Commented:
Stats run and work wonderful on our Dev Server but on our Production server I keep getting errors.
ERROR at line 1:
ORA-06512: at "SYS.DBMS_STATS", line 8426
ORA-06512: at "SYS.DBMS_STATS", line 10803
ORA-06512: at "SYS.DBMS_STATS", line 10958
ORA-06512: at "SYS.DBMS_STATS", line 11076
ORA-06512: at "SYS.DBMS_STATS", line 11056
ORA-06512: at line 1

I look at the package and its valid.  Not sure what the issue is.  Wondering really if
we have enough space to store the stats in the data dictionary.
0
FayyazCommented:
can you send the exact command you r using before getting the above error. showing sql prompt also
0
FayyazCommented:
using which ORACLE user you are logged in ?
can you send more details ?
can you send exact command you are using?

0
johnsoneSenior Oracle DBACommented:
Looking at  your last post again, any space issues would show up in the alert log.
0
GranModCommented:
PAQed with points refunded (500)

GranMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.