Results 1 to 10 of 10

Thread: Analyze

  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Unanswered: Analyze

    I'm performing data import into several tables. I know I should rebuild the indexes after the import, but what type of analyze should I perform to improve my performance?

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    The following command will list tables & when they were last analyzed:

    select table_name, last_analyzed from dba_tables where owner = 'xxx';

    You can then generate stats by either using the ANALYZE TABLE command, or if you're running Oracle9i by running DBMS_STATS. Oracle recommends using DBMS_STATS, partly because the Db uses parallel processes to gather the stats much more quickly than ANALYZE (apparently!). Also, with DBMS_STATS you can leave it to Oracle to decide on a good sample size.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Feb 2004
    Posts
    86
    Yes, I'm using 9i. What is the syntax to run DBMS_STATS?

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hi,

    I suggest you download tha whole Oracle documentation for Oracle 9i :
    "Oracle9i Database Release 2 Documentation Library" which can be found at "http://www.oracle.com/technology/documentation/oracle9i.html". Once the download is done, you will find a complete list of the PL/SQL packages shipped with Oracle, including DBMS_STATS, in the "Supplied PL/SQL Packages and Types Reference" document. (open "booklist.pdf" for links to every documents you've downloaded)

    I'm sure these documents will greatly help you, as they did for me.

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    I'd go along with what RBARAER said: download some documentation & read it. DBMS_STATS is a package that has several procedures for gathering stats at the Db level, the schema level, & the table level, so it's important to know what it is you want before gathering the stats. The level you gather at determines the syntax to use, so here is not really a good place to show you the general syntax; however, if you get stuck with a specific procedure then we'll be glad to help you.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Feb 2004
    Posts
    86
    I can read the documentation that is good, however a kick start would be helpful, I don't have too much time to sort this out. Can anybody help with what I should be looking for?

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Please don't be so lazy : everyone here does not have too much time. We take time to help each other, but it is up to you to do what you can on your own. And I can tell you for sure that you will learn much more by reading the manual by yourself. Then, if there are still dark areas, we will be glad to help you. Again, download "Oracle9i Database Release 2 Documentation Library" which can be found at "http://www.oracle.com/technology/documentation/oracle9i.html". Then look at "Supplied PL/SQL Packages and Types Reference" (you will find DBMS_STATS very easily). You can also have a look at "Database Performance Tuning Guide and Reference", section 3 "Gathering Optimizer Statistics" to have more details on when to use one procedure or the other from DBMS_STATS.

    Do that, read as much documentation as you can when you encounter a problem, and you will not regret it : you will earn so much time by knowing where to look at in the documentation when you fall upon a new issue.

    Trust me.

    RBARAER

  8. #8
    Join Date
    Feb 2004
    Posts
    86
    Thanks for you HELP!

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I didn't mean to be rude at all, simply to make you understand that you will learn much much more by yourself.

    You can execute the following in order to gather all stats in all user schemas :

    EXECUTE dbms_stats.gather_database_stats();

    And for System stats :

    EXECUTE dbms_stats.gather_system_stats();

    If you want something more fine-grained, please refer to the documentation I mentioned.

    Regards,

    RBARAER

  10. #10
    Join Date
    Feb 2004
    Posts
    86
    Thanks, I'm aware I need to read and fully understand and when I'm not under the pump I will, however like I mentioned, I just wanted a quick kick start (which you have given me and believe me, I really appreciate the time you have taken to answer my question). Thanks once again.

Posting Permissions

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