Is it required the analyze the SYSTEM and SYS schema in Oracle 9i..?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Is it required the analyze the SYSTEM and SYS schema in Oracle 9i..?

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    Is it required the analyze the SYSTEM and SYS schema in Oracle 9i..?

    We generally analyze the data schema.

    Is it required the analyze the SYSTEM and SYS schema in Oracle 9i(9.2).?

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Look at what I found in Metalink:

    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
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    Now thas news. Atleast for me.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    MetaLink: That's where I looked for the quote I posted - question has come up before. But Rowena's statement looks wishy-washy.

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width