DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Analyze & redo logs

  1. #1
    Join Date
    Jan 2000
    Location
    Manama, Bahrain
    Posts
    50

    Analyze & redo logs

    Does the command ANALYZE ...ESTIMATE STATISTICS generate a lot of redo entries? I have been looking for documentation that confirms the same, but haven't found anything yet.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    I don't think so..... but I am not sure.

    Regards

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Lynn,

    Sorry for the wrong info.. Yes ANALYZE recortds following stuff in the redolog (sample output)

    SQL 1:
    Code:
    update "SYS"."IND$" set "DATAOBJ#" = '5860', "TS#" = '0', "FILE#" = '1', "BLOCK#" = '48105', "INDMETHOD#" = '0', "COLS" = '1', "PCTFREE$" ='10', "INITRANS"  '2', "MAXTRANS"= '255', "PCTTHRES$" = NULL, "TYPE#" = '1', "FLAGS" = '2', "PROPERTY" = '4097', "BLEVEL" = '0', "LEAFCNT" = '1', "DISTKEY" = '14', "LBLKKEY" = '1', "DBLKKEY" = '1', "CLUFAC" = '1', "ANALYZETIME" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLESIZE"= '14', "ROWCNT" = '14', "INTCOLS" = '1', "DEGREE" = NULL, "INSTANCES" = NULL, "TRUNCCNT" = NULL, "SPARE1" = '1', "SPARE2" = NULL, "SPARE4"= NULL, "SPARE6" = TO_DATE('26-MAY-03', 'DD-MON-RR') where "BO#" = '5859' and "OBJ#" = '5860' and "DATAOBJ#" = '5860' and "TS#" = '0' and "FILE#" = '1' and "BLOCK#" = '48105' and "INDMETHOD#" = '0' and "COLS" =      
     '1' and "PCTFREE$" = '10' and "INITRANS" = '2' and "MAXTRANS" = '255' and "PCTTHRES$" IS NULL and "TYPE#" = '1' and "FLAGS" = '2' and "PROPERTY" = '4097' and "BLEVEL" = '0' and "LEAFCNT" = '1' and "DISTKEY" ='14' and "LBLKKEY" = '1' and "DBLKKEY" = '1' and "CLUFAC" = '1' and "ANALYZETIME" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLESIZE" = '14' and "ROWCNT" = '14' and "INTCOLS" = '1' and "DEGREE" IS NULL and "INSTANCES" IS NULL and "TRUNCCNT" IS NULL and "SPARE1" = '1' and "SPARE2" IS NULL and "SPARE4" IS NULL and "SPARE6" = TO_DATE('26-MAY-03', 'DD-MON-RR') and ROWID = 'AAAAACAABAAAK8sAAA';                                 
                                                                          
    SQL: 2
    update "SYS"."HIST_HEAD$" set "COL#" = '8', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '0', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '10', "MAXIMUM" = '30', "DISTCNT" = '3', "LOWVAL" = HEXTORAW('c10b'), "HIVAL"       
    = HEXTORAW('c11f'), "DENSITY" = '.333333333333333', "SPARE1" = '3', "SPARE2" = '0', "AVGCLN" = '2' where "COL#" = '8' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '0' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '10' and "MAXIMUM" = '30' and "DISTCNT" = '3' and "LOWVAL" = HEXTORAW('c10b') and "HIVAL" = HEXTORAW('c11f') and "DENSITY" = '.333333333333333' and "SPARE1" = '3' and "SPARE2" = '0' and "AVGCLN" = '2' and ROWID = 'AAAADbAABAAAAXgAAA';                                  
                                                                          
    SQL: 3
    update "SYS"."HIST_HEAD$" set "COL#" = '7', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '10', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '0', "MAXIMUM" = '1400', "DISTCNT" = '4', "LOWVAL" = HEXTORAW('80'), "HIVAL"       
    = HEXTORAW('c20f'), "DENSITY" = '.25', "SPARE1" = '4', "SPARE2" = '0', "AVGCLN" = '2' where "COL#" = '7' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '10' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '0' and "MAXIMUM" = '1400' and "DISTCNT" = '4' and "LOWVAL" = HEXTORAW('80') and "HIVAL" = HEXTORAW('c20f') and "DENSITY" = '.25' and "SPARE1" = '4' and "SPARE2" = '0' and "AVGCLN" = '2' and ROWID = 'AAAADbAABAAAAXgAAB';                                                            
                                                                           SQL: 4
    update "SYS"."HIST_HEAD$" set "COL#" = '6', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '0', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '800', "MAXIMUM" = '5000', "DISTCNT" = '12', "LOWVAL" = HEXTORAW('c209'), "HIVAL" = HEXTORAW('c233'), "DENSITY" = '.0833333333333333', "SPARE1" = '12', "SPARE2" = '0', "AVGCLN" = '3' where "COL#" = '6' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '0' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE"  = '14' and "MINIMUM" = '800' and "MAXIMUM" = '5000' and "DISTCNT" = '12' and "LOWVAL" = HEXTORAW('c209') and "HIVAL" = HEXTORAW('c233') and  "DENSITY" = '.0833333333333333' and "SPARE1" = '12' and "SPARE2" = '0' and "AVGCLN" = '3' and ROWID = 'AAAADbAABAAAAXgAAC';                      
                                                                           SQL: 5
    update "SYS"."HIST_HEAD$" set "COL#" = '5', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '0', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-R'), "SAMPLE_SIZE" = '14', "MINIMUM" = '2444591', "MAXIMUM" = '2446939', "DISTCNT" = '13', "LOWVAL" = HEXTORAW('77b40c11010101'), "HIVAL" = HEXTORAW('77bb0517010101'), "DENSITY" = '.0769230769230769', "SPARE1" = '13', "SPARE2" = '0', "AVGCLN" = '7' where "COL#" = '5' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT"       
    = '0' and "NULL_CNT" = '0' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '2444591' and "MAXIMUM" = '2446939' and "DISTCNT" = '13' and "LOWVAL" = HEXTORAW('77b40c11010101') and "HIVAL" = HEXTORAW('77bb0517010101') and "DENSITY" = '.0769230769230769' and "SPARE1" = '13' and "SPARE2" = '0' and "AVGCLN" = '7' and ROWID  'AAAADbAABAAAAXgAAD';                                      
                                                                          SQL: 6
         
    update "SYS"."HIST_HEAD$" set "COL#" = '4', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '1', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '7566',"MAXIMUM" = '7902', "DISTCNT" = '6', "LOWVAL" = HEXTORAW('c24c43'), "H      
    IVAL" = HEXTORAW('c25003'), "DENSITY" = '.166666666666667', "SPARE1" = '6', "SPARE2" = '0', "AVGCLN" = '3' where "COL#" = '4' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '1' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '7566' and "MAXIMUM" = '7902' and "DISTCNT"= '6' and "LOWVAL" = HEXTORAW('c24c43') and "HIVAL" = HEXTORAW('c25003') and "DENSITY" = '.166666666666667' and "SPARE1" = '6' and "SPARE2"= '0' and "AVGCLN" = '3' and ROWID = 'AAAADbAABAAAAXgAAE';                  
    
    SQL :7                                                                            
    update "SYS"."HIST_HEAD$" set "COL#" = '3', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '0', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '3390864      
    97213261000000000000000000000', "MAXIMUM" = '432285038678150000000000000000000000', "DISTCNT" = '5', "LOWVAL" = HEXTORAW('414e414c595354'),"HIVAL" = HEXTORAW('53414c45534d414e'), "DENSITY" = '.2', "SPARE1" = '      
    5', "SPARE2" = '0', "AVGCLN" = '7' where "COL#" = '3' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '0'and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '339086497213261000000000000000000000' and "MA      
    XIMUM" = '432285038678150000000000000000000000' and "DISTCNT" = '5' and "LOWVAL" = HEXTORAW('414e414c595354') and "HIVAL" = HEXTORAW('53414c45534d414e') and "DENSITY" = '.2' and "SPARE1" = '5' and "SPARE2" = '0' and "AVGCLN" = '7' and ROWID = 'AAAADbAABAAAAXgAAF';                      
                                                                          SQL: 8    
    update "SYS"."HIST_HEAD$" set "COL#" = '2', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '0', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '3388836      
    73419062000000000000000000000', "MAXIMUM" = '453054701071074000000000000000000000', "DISTCNT" = '14', "LOWVAL" = HEXTORAW('4144414d53'), "HIVAL" = HEXTORAW('57415244'), "DENSITY" = '.0714285714285714', "SPARE1" = '14', "SPARE2" = '0', "AVGCLN" = '5' where "COL#" = '2' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '0' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '338883673419062000000000000000000000' and "MAXIMUM" = '453054701071074000000000000000000000' and "DISTCNT" = '      
    14' and "LOWVAL" = HEXTORAW('4144414d53') and "HIVAL" = HEXTORAW('57415244') and "DENSITY" = '.0714285714285714' and "SPARE1" = '14' and "SPARE2" = '0' and "AVGCLN" = '5' and ROWID = 'AAAADbAABAAAAXgAAG';            
                                                                          
    SQL: 9
    update "SYS"."HIST_HEAD$" set "COL#" = '1', "BUCKET_CNT" = '1', "ROW_CNT" = '0', "CACHE_CNT" = '0', "NULL_CNT" = '0', "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR'), "SAMPLE_SIZE" = '14', "MINIMUM" = '7369',       
    "MAXIMUM" = '7934', "DISTCNT" = '14', "LOWVAL" = HEXTORAW('c24a46'), "HIVAL" = HEXTORAW('c25023'), "DENSITY" = '.0714285714285714', "SPARE1" = '14', "SPARE2" = '0', "AVGCLN" = '3' where "COL#" = '1' and "BUCKET_CNT" = '1' and "ROW_CNT" = '0' and "CACHE_CNT" = '0' and "NULL_CNT" = '0' and "TIMESTAMP#" = TO_DATE('02-JUN-03', 'DD-MON-RR') and "SAMPLE_SIZE" = '14' and "MINIMUM" = '7369' and "MAXIMUM" = '7934' and "DISTCNT" = '14' and "LOWVAL" = HEXTORAW('c24a46') and "HIVAL" = HEXTORAW('c25023') and "DENSITY" = '.0714285714285714' and "SPARE1" = '14' and "SPARE2" = '0' and "AVGCLN" = '3' and ROWID = 'AAAADbAABAAAAXgAAH';
    Regards


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Jan 2000
    Location
    Manama, Bahrain
    Posts
    50
    Thanks for that. But do these update statements run for every row of every table or for the table as a whole?

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Hi

    Number of statements executed = number of columns + 1 (I experienced form my testing)

    Regards
    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Thomasps
    Hi

    Number of statements executed = number of columns + 1 (I experienced form my testing)

    Regards
    Thomas
    It would be one if only Table/Index Stats are gathered.

    Or

    No of Cols + 1 if Table/Index Stats with Col Stats is gathered.

    Abhay.
    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"

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Originally posted by abhaysk
    It would be one if only Table/Index Stats are gathered.

    Or

    No of Cols + 1 if Table/Index Stats with Col Stats is gathered.

    Abhay.

    In the above example give i just run

    analyze table emp compute statistics;

    But I got 9 (8 Cols +1).

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Thomasps
    In the above example give i just run

    analyze table emp compute statistics;

    But I got 9 (8 Cols +1).

    Thomas
    If you dont specify For_Clause...defaultly all Col Statistics will be gathered as well.

    COMPUTE STATISTICS
    computes exact statistics about the analyzed object and stores them in the data dictionary. When you analyze a table, both table and column statistics are collected.
    Abhay.
    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"

  9. #9
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Thanks

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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