We generally analyze the data schema.
Is it required the analyze the SYSTEM and SYS schema in Oracle 9i(9.2).?
We generally analyze the data schema.
Is it required the analyze the SYSTEM and SYS schema in Oracle 9i(9.2).?
Required? No.
Recommended? No.
You should not use the ANALYZE command to collect statistical information on the tables owned by user SYS, as using the Cost-Base Optimizer (CBO) is not supported against dictionary views. This has always been the case and will be in the forseeable future.
Look at what I found in Metalink:Quote:
Originally posted by stecal
Required? No.
Recommended? No.
You should not use the ANALYZE command to collect statistical information on the tables owned by user SYS, as using the Cost-Base Optimizer (CBO) is not supported against dictionary views. This has always been the case and will be in the forseeable future.
From: Oracle, Rowena Serna 24-Mar-03 18:01
Subject: Re : Analyzing Statistics for CBO in Oracle 9i R2
Prior to Oracle9i the advice of support was that users should "NOT analyze the data dictionary tables (SYS tables) unless you have a very strong reason to do so". With the desupport of the RBO pending in 10i there will only be a CBO so the dictionary will need to be analyzed. The dictionary can now be analyzed without any problems. Note that whilst DBMS_UTILITY.ANALYZE_SCHEMA can be used against the SYS schema Oracle does not perform any regression tests with these tables analyzed and so it is possible that deadlocks or performance problems may be encountered. Also note that a significant number of large databases run extremely succesfully with an analyzed dictionary.
see full article
" Cost Based Optimizer - Common Misconceptions and Issues "
Regards,
Rowena Serna
Oracle Corporation
Now thas news. Atleast for me.Quote:
Originally posted by julian
With the desupport of the RBO pending in 10i there will only be a CBO so the dictionary will need to be analyzed.
MetaLink: That's where I looked for the quote I posted - question has come up before. But Rowena's statement looks wishy-washy.
Note:35934.1
Subject: Cost Based Optimizer - Common Misconceptions and
Dated 8 Aug 2002
That's where she got the info for her posting on MetaLink.