12 Replies Latest reply: Jan 24, 2013 2:17 AM by Karan RSS

    table analyze

    Harsh_v
      Hi folks..


      Can you tell me we about analyse tables in oracle. please resolve my query...


      1) why required table analyze.
      2) how to do table analyze.
      3) and after analyze what taking steps.
        • 1. Re: table analyze
          Karan
          Use analyze command to only validate and chained rows, gather statistics by dbms_stats package, dbms_stats is the recommended method of collecting statisttics, with dbms_stats you can analyze external tables and analyze cannot. you should gather statistics like dbms_stats.gather_Table_stats('OWNER','table_name'); It woudl give optimizer the clear picture/state of your object so that it can produce a good/cheaper/fast execution plan.
          • 2. Re: table analyze
            Karan
            Also dbms_stats would not the gather statistics for the number of chained rows, average free space, or number of unused data blocks for the use analyze command.
            • 3. Re: table analyze
              Karan
              Get the complete details and this will answer your question from TOM see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4347359891525
              • 4. Re: table analyze
                Harsh_v
                thanks for good suggest.

                can you please give me simple example. if possible..
                • 5. Re: table analyze
                  missymichi
                  ANALYZE TABLE FACULTY VALIDATE STRUCTURE;

                  Edited by: missymichi on Jan 23, 2013 11:18 PM
                  • 6. Re: table analyze
                    Karan
                    For a corruption check

                    The following statement analyzes the emp table:

                    ANALYZE TABLE emp VALIDATE STRUCTURE;

                    You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option. The following statement validates the emp table and all associated indexes:

                    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;


                    following statement inserts rows containing information about the chained rows in the emp_dept cluster into the CHAINED_ROWS table:

                    ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;

                    ____________________________________________________________________

                    ANALYZE TABLE order_hist LIST CHAINED ROWS;

                    SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';

                    ____________________________________________________________________

                    The following will gather statistics for scott's emp table and its dependent indexes as well

                    exec dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true);

                    Edited by: Karan on Jan 24, 2013 12:52 PM
                    • 7. Re: table analyze
                      Harsh_v
                      I am doing ..............


                      SQL> exec dbms_stats.gather_table_stats('MCB','FTP_TAB',cascade=>true);

                      PL/SQL procedure successfully completed.




                      after that how to check it ?
                      • 8. Re: table analyze
                        415289
                        select NUM_ROWS,SAMPLE_SIZE,last_analyzed from dba_tables where table_name='FTP_TAB';
                        • 9. Re: table analyze
                          Karan
                          check in ALL_TAB_STATISTICS and ALL_TAB_COL_STATISTICS views like for any column level details

                          select NUM_DISTINCT,NUM_NULLS, last_analyzed, hitogram from ALL_TAB_COL_STATISTICS
                          • 10. Re: table analyze
                            Harsh_v
                            thanks for support Kuljeet Pal Singh and Karan.....

                            can you please send some good link where i understand Analyze more deep.
                            • 11. Re: table analyze
                              Girish Sharma
                              Harshit -{A> wrote:
                              thanks for support Kuljeet Pal Singh and Karan.....

                              can you please send some good link where i understand Analyze more deep.
                              This link is as much deep as it requires by production DBAs :
                              http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4005.htm#SQLRF01105

                              Regards
                              Girish Sharma
                              • 12. Re: table analyze
                                Karan
                                Let oracle doc be your friend http://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm