?
Solved

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

Posted on 2006-03-24
18
Medium Priority
?
1,702 Views
Last Modified: 2011-10-03
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...
0
Comment
Question by:ValeneSue
  • 5
  • 5
  • 3
  • +2
17 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 16282087
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16282251
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
 

Author Comment

by:ValeneSue
ID: 16282331
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ValeneSue
ID: 16282360
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16282599
Problems in an Oracle-supplied package (like: SYS.DBMS_STATS) are usually best handled by Oracle Technical Support.  Have you tried that?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16282699
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
 

Author Comment

by:ValeneSue
ID: 16284208
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16284508
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
 
LVL 7

Expert Comment

by:Fayyaz
ID: 16292565
you can use this

exec dbms_stats.gather_schema_stats(ownname=>'pppp', options=>'gather auto');
0
 

Author Comment

by:ValeneSue
ID: 16297331
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
 
LVL 7

Expert Comment

by:Fayyaz
ID: 16297598
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
 
LVL 7

Expert Comment

by:Fayyaz
ID: 16297633
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
 

Author Comment

by:ValeneSue
ID: 16348517
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
 
LVL 7

Expert Comment

by:Fayyaz
ID: 16351497
can you send the exact command you r using before getting the above error. showing sql prompt also
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 16433534
using which ORACLE user you are logged in ?
can you send more details ?
can you send exact command you are using?

0
 
LVL 35

Expert Comment

by:johnsone
ID: 16434995
Looking at  your last post again, any space issues would show up in the alert log.
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16732358
PAQed with points refunded (500)

GranMod
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

807 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