I think in 10g some automation in this regard has been put in to happen when there is not too much activity on the database. However, you can still control many aspects of the schedule you want oracle to follow.
In and upto oracle 8i I assume you don't analyze sys schema as it is considered against your interest. Oracle recommends to not use stats - assumption is that upto oracle 8i the sys schema works on the rule based architecture [both rule and cost optimizers are availble in oracle 8 and 8i]. But in 10g it is only cost based optimizing. I just checked with a friend and he noted that in 10g he thinks you can gather stats on sys schema also. The best would be to experiment runing stats on sys in your development database and know it for yourself. If not allowed, it will readily throw an error on you.
Gurmohan
www.onlymath.com - The ultimate tool in learning and teaching math skills.
-----Original Message-----
From: henry Wollman
[mailto:oracledba-ezmlmshield-x50238472.[Email address protected]
Sent: Thursday, September 29, 2005 1:44 PM
To: LazyDBA Discussion
Subject: RE: dbms_stats
I don't know what 10g does, someone else will have to chime in.
| Henry A.L. Wollman Rejoice and be glad!
|_____________________________________________________
| Moody Bible Institute, Database Administrator, [Email address protected]
| 820 N. LaSalle Blvd, Chicago, IL 60610 312-329-2291
|_____________________________________________________
-----Original Message-----
From: Rhoades Mark A SPL Contractor
[mailto:oracledba-ezmlmshield-x49803462.[Email address protected]
Sent: Thursday, September 29, 2005 11:49 AM
To: LazyDBA Discussion
Subject: RE: dbms_stats
Does Oracle 10g automatically recompute stats or do you still need to setup
a
job to do it?
Mark
-----Original Message-----
From: henry Wollman
[mailto:oracledba-ezmlmshield-x33448643.[Email address protected]
Sent: Thursday, September 29, 2005 9:18 AM
To: LazyDBA Discussion
Subject: RE: dbms_stats
This computes stats on the sys schema, which the oracle optimizer uses to
optimize queries against sys tables, which are probably queried a lot during
the course of activity.
It was recommended in an oracle class that stats should be gathered
regularly on the SYS schema.
As to frequency, we run it weekly. How often would depend on the amount of
update activity you db experiences in the sys schema.
| Henry A.L. Wollman Rejoice and be glad!
|_____________________________________________________
| Moody Bible Institute, Database Administrator, [Email address protected]
| 820 N. LaSalle Blvd, Chicago, IL 60610 312-329-2291
|_____________________________________________________
-----Original Message-----
From: James Oddo [mailto:[Email address protected]
Sent: Thursday, September 29, 2005 11:04 AM
To: [Email address protected]
Subject: RE: dbms_stats
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Can you elaborate on why it should be run on the sys schema and what is
the frequency......Thanks!
-----Original Message-----
From: henry Wollman
[Email address protected]
Sent: Thursday, September 29, 2005 11:30 AM
To: LazyDBA Discussion
Subject: RE: dbms_stats
You can also
DBMS_STATS.GATHER_SCHEMA_STATS
And this ought to be run regularly on the SYS schema.
| Henry A.L. Wollman Rejoice and be glad!
|_____________________________________________________
| Moody Bible Institute, Database Administrator, [Email address
protected]
| 820 N. LaSalle Blvd, Chicago, IL 60610 312-329-2291
|_____________________________________________________
-----Original Message-----
From: Sachidananda_T
[mailto:oracledba-ezmlmshield-x16343912.[Email address protected]
Sent: Wednesday, September 28, 2005 11:29 PM
To: LazyDBA Discussion
Subject: RE: dbms_stats
Hi,
DBMS_STATS.GATHER_TABLE_STATS should be used on large tables.
It would gather the statistics of the table and store it in the data
dictionary.
Hence further DML statements would be faster.
It can be used from any schema user.
Regards
Sachi
-----Original Message-----
From: Vishal Agarwal
[mailto:oracledba-ezmlmshield-x15287681.[Email address protected]
Sent: Thursday, September 29, 2005 10:05 AM
To: LazyDBA Discussion
Subject: dbms_stats
hello all
can anybody tell me what is the advantage of
using dbms_stats package for gathering table stats
and whether I should run it from sys or from
users scheme which has the table
thanks
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying
or
distribution or forwarding of any or all of the contents in this message
is
STRICTLY PROHIBITED. If you are not the intended recipient, please
contact
the sender by email and delete all copies; your cooperation in this
regard
is appreciated.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page