Optimizer Statistical Information

Statistics plays a key role in providing the correct information to the Optimizer while the queries are executed.

For example if we run a query after enabling the optimizer trace

select id from test where id=5

In the trace info we can find

Below is the optimizer statistics about the query

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FIRST  Alias: FIRST
    #Rows: 1429  #Blks:  5  AvgRowLen:  5.00  ChainCnt:  0.00
Index Stats::
  Index: IDX  Col#: 1
    LVLS: 1  #LB: 4  #DK: 10  LB/K: 1.00  DB/K: 1.00  CLUF: 7.00
Access path analysis for FIRST

After Inserting 50000 records into the table and without running the statistics we can see still see the same info and the explain plan is proportional to the above statistics.

After we run the stats on the table below is the change happened in the statistics information.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FIRST  Alias: FIRST
    #Rows: 51429  #Blks:  88  AvgRowLen:  7.00  ChainCnt:  0.00
Index Stats::
  Index: IDX  Col#: 1
    LVLS: 1  #LB: 104  #DK: 50536  LB/K: 1.00  DB/K: 1.00  CLUF: 99.00

Now it has considered all the info.

Redo Logs Recovery

Recover Steps when Redo logs are Corrupted or Missed

1. One of the Redo Logs which is in Inactive State is corrupted or Removed
2. Active Redo Log is corrupted
3. All the Redo Logs are corrupted or Removed

In case if the Inactive Redo Logs were deleted just create the redo logs and clear the unarchived logfile.

Example

1. Removed the redo log at OS Level.
2. Switch the Log and we can see the errors in the alert log.
3. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP groupnumber;
4. Switch the logs.
5. Take Backup again.

Incase if ONLINE Redo Log is removed or corrupted or All the redo logs were removed

We need to restore the database and need to perform incomplete Recovery.

RMAN> restore database;

RMAN> recover database;

or

SQL> recover database until cancel;

SQL> alter database open resetlogs;