How to run analyze from a point in time in past? (218512)

How to run analyze from a point in time in past?

Normally when analyze is run, it starts analyzing the config file from the current time to the time interval specified. It gathers the transactions, the objects involved, the type of DML, FK relationship, etc., for the duration specified. To get meaningful statistics the analyze needs to be run for a reasonable time to collect transaction details representative of the transaction mix. At times it may be beneficial to collect statistics from the past if the system was active back then and is currently not so busy. The analyze allows such gathering by specifying the SCN or redo log sequence # from the past. The only pre-requisite is, the archive logs starting with the log sequence # specified in the command are available in the LOG_ARCHIVE_DEST or the designated archive location. The same goes for the SCN option whereby the archive logs starting with the one that has the specified SCN # are available in archive destination. The syntax for analyze with SCN option is:

sp_ctrl> analyze config <config name> SCN=<value> <time specification> 

The syntax is given in the Additional Information field below.

Here is a sample test on using SCN # with analyze:

1.  Retrieved the current SCN from source database:

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN

-----------

 2327911659

2.  This is the config file:

sp_ctrl (alvsupu15:5438)> view config config1

datasource:o.ORA11GR2

#source tables      target tables           routing map

sha86.table1 sha86.table1 alvsupu07@o.syam

3.  Wait for some time so that the SCN is not current. I then ran analyze for 5 minutes using that SCN since it is not current but is in past:

sp_ctrl (alvsupu15:5438)> analyze config config1 SCN=2327911659 5 minutes

sp_ctrl (alvsupu15:5438)> status

Brief Status for alvsupu15

Process          State                             PID     Running   Since    

---------------  ------------------------------  --------  --------------------

Cop              Running                            23360  06-Dec-16 18:06:02 

Cmd & Ctrl       Running                            23364  06-Dec-16 18:06:08 

Capture          Running                            23376  06-Dec-16 18:06:30 

Analyze          Running                            23378  06-Dec-16 18:06:31

4.  Starting with time corresponding to SCN 2327911659 in the source database and processing transactions for next 5 minutes of the source database time, the analyze would end. For example, if the source database time was 12:00 noon corresponding to the above SCN, the analyze will be done for all transactions between 12:00 noon and 12:05 noon. The analyze process will then exit and create the file named o.SID-analysis.actid  (where SID refers to the SID of the source database, the actid refers to the activation id of the analyze). The file is created in log subdirectory of the SharePlex variable directory. Note that the SharePlex internal table shareplex_actid has a field called actid that increments by 1 every time a new config is activated or a new analyze is run.

Similar test can be run by specifying the SEQNO (the log sequence #) instead of the SCN. For example, the following command option allows analyzing transactions from a log sequence # in the past:

sp_ctrl (alvsupu15:5438)> analyze config config1 SEQNO=<log sequence #> <n> minutes