-
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.
-
I don't think so..... but I am not sure.
Regards
Thomas
-
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
-
Thanks for that. But do these update statements run for every row of every table or for the table as a whole?
-
Hi
Number of statements executed = number of columns + 1 (I experienced form my testing)
Regards
Thomas
-
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"
-
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
-
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"
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|