Hello,
Will Gather stats will generates redo?
How much redo it will generate?
It depends on the size of database?
I need some thoughts on this from experts on how much amount of redo being generated while performing Gather stats.
Lets suppose an example:-
1) 23-Jan-2012 Table(EMP) has rows:50000000
performed gather stats on that day. & by querying
SQL> select num_rows from dba_tables where table_name='EMP'; -- Output is exact 50000000
2) I have disabled Gather stats.
3) after several days on 27th March, I performed delete operations for rows 1000000,
But due to old stats the NUM_ROWS was still results old value.
4) now i connected to database with only one session(after bounce) - No other sessions connected(that mean no other transactions) & performed gather stats.
5) After gather stats, The table analysis also updated in dba_tables, I mean num_rows showing the latest value i.e. 49000000
Now i can see there is almost 3 archive logs been generated of size around 50mb = 3* 50mb = ~150mb size.
So My question is to update the base tables, its been generated Redo?
Is this redo log switches will depends on database size specific to gather stats (or) only less amount of redo ?
Database Version:-
sys@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
sys@ORCL>
Small Test:-
23:14:19 sys@ORCL> select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS 63 5
23:14:37 sys@ORCL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
31
23:14:59 sys@ORCL>
23:18:09 sys@ORCL>
23:22:38 sys@ORCL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
23:22:39 sys@ORCL> select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS 63 5
23:22:52 sys@ORCL> select sequence#,first_change#,first_time,next_change#,next_time,completion_time from v$archived_log where resetlogs_change#=1520568;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME COMPLETION_TIME
---------- ------------- -------------------- ------------ -------------------- --------------------
1 1520568 01-MAR-2012 14:36:37 1545233 01-MAR-2012 22:00:50 01-MAR-2012 22:00:55
2 1545233 01-MAR-2012 22:00:50 1555321 02-MAR-2012 12:06:34 02-MAR-2012 12:06:37
3 1555321 02-MAR-2012 12:06:34 1576025 03-MAR-2012 14:06:58 03-MAR-2012 14:07:05
4 1576025 03-MAR-2012 14:06:58 1591033 03-MAR-2012 17:38:07 03-MAR-2012 17:38:10
5 1591033 03-MAR-2012 17:38:07 1628041 05-MAR-2012 23:22:35 05-MAR-2012 23:22:40
6 1628041 05-MAR-2012 23:22:35 1663616 07-MAR-2012 14:12:17 07-MAR-2012 14:12:22
7 1663616 07-MAR-2012 14:12:17 1670519 07-MAR-2012 16:30:02 07-MAR-2012 16:30:03
8 1670519 07-MAR-2012 16:30:02 1697954 07-MAR-2012 19:16:12 07-MAR-2012 19:16:14
9 1697954 07-MAR-2012 19:16:12 1732166 08-MAR-2012 08:04:17 08-MAR-2012 08:04:22
10 1732166 08-MAR-2012 08:04:17 1758493 08-MAR-2012 22:00:56 08-MAR-2012 22:01:03
11 1758493 08-MAR-2012 22:00:56 1789017 12-MAR-2012 16:54:36 12-MAR-2012 16:54:41
12 1789017 12-MAR-2012 16:54:36 1798691 12-MAR-2012 22:38:04 12-MAR-2012 22:38:08
13 1798691 12-MAR-2012 22:38:04 1827281 15-MAR-2012 12:25:39 15-MAR-2012 12:25:43
14 1827281 15-MAR-2012 12:25:39 1862680 16-MAR-2012 11:51:30 16-MAR-2012 11:51:34
15 1862680 16-MAR-2012 11:51:30 1896206 16-MAR-2012 22:01:06 16-MAR-2012 22:01:10
16 1896206 16-MAR-2012 22:01:06 1907034 17-MAR-2012 00:25:50 17-MAR-2012 00:25:53
17 1907034 17-MAR-2012 00:25:50 1927283 17-MAR-2012 16:44:07 17-MAR-2012 16:44:11
18 1927283 17-MAR-2012 16:44:07 1940012 17-MAR-2012 22:43:05 17-MAR-2012 22:43:08
19 1940012 17-MAR-2012 22:43:05 1969596 19-MAR-2012 15:35:06 19-MAR-2012 15:35:13
20 1969596 19-MAR-2012 15:35:06 1989872 19-MAR-2012 15:35:45 19-MAR-2012 15:35:46
21 1989872 19-MAR-2012 15:35:45 2012761 19-MAR-2012 22:00:56 19-MAR-2012 22:00:58
22 2012761 19-MAR-2012 22:00:56 2040016 20-MAR-2012 20:30:56 20-MAR-2012 20:30:59
23 2040016 20-MAR-2012 20:30:56 2052958 20-MAR-2012 22:00:40 20-MAR-2012 22:00:43
24 2052958 20-MAR-2012 22:00:40 2088300 21-MAR-2012 22:01:00 21-MAR-2012 22:01:08
26 2117855 22-MAR-2012 17:56:56 2135832 22-MAR-2012 22:06:33 24-MAR-2012 22:26:35
27 2135832 22-MAR-2012 22:06:33 2168769 23-MAR-2012 21:30:23 24-MAR-2012 22:26:38
28 2168769 23-MAR-2012 21:30:23 2200992 24-MAR-2012 22:26:37 24-MAR-2012 22:26:43
29 2200992 24-MAR-2012 22:26:37 2216168 24-MAR-2012 22:53:51 24-MAR-2012 22:53:56
30 2216168 24-MAR-2012 22:53:51 2227379 24-MAR-2012 22:55:01 24-MAR-2012 22:55:05
31 2227379 24-MAR-2012 22:55:01 2236338 24-MAR-2012 22:56:13 24-MAR-2012 22:56:16
32 2236338 24-MAR-2012 22:56:13 2247596 24-MAR-2012 23:20:14 24-MAR-2012 23:20:19
31 rows selected.
23:23:07 sys@ORCL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
32
23:23:21 sys@ORCL> select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS 63 5
"23:23:30 sys@ORCL> EXEC DBMS_STATS.gather_database_stats;"
PL/SQL procedure successfully completed.
23:27:38 sys@ORCL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
35
23:27:45 sys@ORCL> select sequence#,first_change#,first_time,next_change#,next_time,completion_time from v$archived_log where resetlogs_change#=1520568;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME COMPLETION_TIME
---------- ------------- -------------------- ------------ -------------------- --------------------
1 1520568 01-MAR-2012 14:36:37 1545233 01-MAR-2012 22:00:50 01-MAR-2012 22:00:55
2 1545233 01-MAR-2012 22:00:50 1555321 02-MAR-2012 12:06:34 02-MAR-2012 12:06:37
3 1555321 02-MAR-2012 12:06:34 1576025 03-MAR-2012 14:06:58 03-MAR-2012 14:07:05
4 1576025 03-MAR-2012 14:06:58 1591033 03-MAR-2012 17:38:07 03-MAR-2012 17:38:10
5 1591033 03-MAR-2012 17:38:07 1628041 05-MAR-2012 23:22:35 05-MAR-2012 23:22:40
6 1628041 05-MAR-2012 23:22:35 1663616 07-MAR-2012 14:12:17 07-MAR-2012 14:12:22
7 1663616 07-MAR-2012 14:12:17 1670519 07-MAR-2012 16:30:02 07-MAR-2012 16:30:03
8 1670519 07-MAR-2012 16:30:02 1697954 07-MAR-2012 19:16:12 07-MAR-2012 19:16:14
9 1697954 07-MAR-2012 19:16:12 1732166 08-MAR-2012 08:04:17 08-MAR-2012 08:04:22
10 1732166 08-MAR-2012 08:04:17 1758493 08-MAR-2012 22:00:56 08-MAR-2012 22:01:03
11 1758493 08-MAR-2012 22:00:56 1789017 12-MAR-2012 16:54:36 12-MAR-2012 16:54:41
12 1789017 12-MAR-2012 16:54:36 1798691 12-MAR-2012 22:38:04 12-MAR-2012 22:38:08
13 1798691 12-MAR-2012 22:38:04 1827281 15-MAR-2012 12:25:39 15-MAR-2012 12:25:43
14 1827281 15-MAR-2012 12:25:39 1862680 16-MAR-2012 11:51:30 16-MAR-2012 11:51:34
15 1862680 16-MAR-2012 11:51:30 1896206 16-MAR-2012 22:01:06 16-MAR-2012 22:01:10
16 1896206 16-MAR-2012 22:01:06 1907034 17-MAR-2012 00:25:50 17-MAR-2012 00:25:53
17 1907034 17-MAR-2012 00:25:50 1927283 17-MAR-2012 16:44:07 17-MAR-2012 16:44:11
18 1927283 17-MAR-2012 16:44:07 1940012 17-MAR-2012 22:43:05 17-MAR-2012 22:43:08
19 1940012 17-MAR-2012 22:43:05 1969596 19-MAR-2012 15:35:06 19-MAR-2012 15:35:13
20 1969596 19-MAR-2012 15:35:06 1989872 19-MAR-2012 15:35:45 19-MAR-2012 15:35:46
21 1989872 19-MAR-2012 15:35:45 2012761 19-MAR-2012 22:00:56 19-MAR-2012 22:00:58
22 2012761 19-MAR-2012 22:00:56 2040016 20-MAR-2012 20:30:56 20-MAR-2012 20:30:59
23 2040016 20-MAR-2012 20:30:56 2052958 20-MAR-2012 22:00:40 20-MAR-2012 22:00:43
24 2052958 20-MAR-2012 22:00:40 2088300 21-MAR-2012 22:01:00 21-MAR-2012 22:01:08
26 2117855 22-MAR-2012 17:56:56 2135832 22-MAR-2012 22:06:33 24-MAR-2012 22:26:35
27 2135832 22-MAR-2012 22:06:33 2168769 23-MAR-2012 21:30:23 24-MAR-2012 22:26:38
28 2168769 23-MAR-2012 21:30:23 2200992 24-MAR-2012 22:26:37 24-MAR-2012 22:26:43
29 2200992 24-MAR-2012 22:26:37 2216168 24-MAR-2012 22:53:51 24-MAR-2012 22:53:56
30 2216168 24-MAR-2012 22:53:51 2227379 24-MAR-2012 22:55:01 24-MAR-2012 22:55:05
31 2227379 24-MAR-2012 22:55:01 2236338 24-MAR-2012 22:56:13 24-MAR-2012 22:56:16
32 2236338 24-MAR-2012 22:56:13 2247596 24-MAR-2012 23:20:14 24-MAR-2012 23:20:19
"33 2247596 24-MAR-2012 23:20:14 2260721 24-MAR-2012 23:24:39 24-MAR-2012 23:24:43"
"34 2260721 24-MAR-2012 23:24:39 2272348 24-MAR-2012 23:25:37 24-MAR-2012 23:25:42"
"35 2272348 24-MAR-2012 23:25:37 2281162 24-MAR-2012 23:26:40 24-MAR-2012 23:26:43"
34 rows selected.
23:27:53 sys@ORCL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\oracle\flash_recovery_area
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
23:28:00 sys@ORCL>
Expecting more thoughts on this from Experts.
Thanks.